Song play stats suggestion
- Chumpy
- Twilight Sparkle
- Posts: 696
- Joined: Sat Apr 18, 2015 2:06 pm
- Instruments: Vocals, guitar, bass
- Recording Method: Logic
- Submitting as: Jerkatorium, Chumpy
- Pronouns: he/him
- Location: Seattle, WA
- Contact:
Song play stats suggestion
One thing I found myself really curious about as I looked at the votes for Deep Sub-Reddit was how many times each song was listened to, both in aggregate and by unique IP address. This interesting info could be easily extracted out of the songfight.org web logs, and I'm pretty sure self that other self-absorbed Internet recording artists would love to see this data along with the votes.
"I don't recommend ending on a bad joke." --ken
- fluffy
- Eruption
- Posts: 11097
- Joined: Sat Sep 25, 2004 10:56 am
- Instruments: sometimes
- Recording Method: Logic Pro X
- Submitting as: Sockpuppet
- Pronouns: she/they
- Location: Seattle-ish
- Contact:
Re: Song play stats suggestion
It would be interesting, but not very accurate since it would only correctly count people who use the playlist stream or who listen from the site directly or whatever. A lot of people just download the whole fight and listen offline (via podcast subscription or using some other archival process) - or don't even listen at all. But I suppose those factors all cancel out if you only care about relative amounts.
- ken
- Hot for Teacher
- Posts: 3881
- Joined: Sat Sep 25, 2004 6:10 pm
- Instruments: Guitar, bass, drums, keys
- Recording Method: MOTU 828x, Cubase 10
- Submitting as: Ken's Super Duper Band 'n Stuff
- Pronouns: he/him
- Location: oakland, ca
- Contact:
Re: Song play stats suggestion
Bandcamp will tell you if a song was downloaded, streamed, streamed to completion or such. Maybe something like that could be implemented.
Ken's Super Duper Band 'n Stuff - Berkeley Social Scene - Tiny Robots - Seamus Collective - Semolina Pilchards - Cutie Pies - Explino! - Bravo Bros. - 2 from 14 - and more!
i would just like to remind everyone that Ken eats kittens - blue lang
i would just like to remind everyone that Ken eats kittens - blue lang
- Lunkhead
- You're No Good
- Posts: 8175
- Joined: Sat Sep 25, 2004 12:14 pm
- Instruments: many
- Recording Method: cubase/mac/tascam4x4
- Submitting as: Berkeley Social Scene, Merisan, Tiny Robots
- Pronouns: he/him
- Location: Berkeley, CA
- Contact:
Re: Song play stats suggestion
I've asked for this before too. It ought to be very easy to parse the web logs with a web log analyzer to get some baseline but inaccurate numbers. (That is 20+ year old technology...) Even just that would be interesting to see as a start.
- Chumpy
- Twilight Sparkle
- Posts: 696
- Joined: Sat Apr 18, 2015 2:06 pm
- Instruments: Vocals, guitar, bass
- Recording Method: Logic
- Submitting as: Jerkatorium, Chumpy
- Pronouns: he/him
- Location: Seattle, WA
- Contact:
Re: Song play stats suggestion
I think even a web log analyzer like AWStats would be overkill. I imagine the access logs get rotated by logrotate on a regular basis.Lunkhead wrote:I've asked for this before too. It ought to be very easy to parse the web logs with a web log analyzer to get some baseline but inaccurate numbers.
Code: Select all
prerotate
echo 'CREATE TABLE IF NOT EXISTS phpbb.songfight_playcounts (playcount BIGINT, filename VARCHAR(256) PRIMARY KEY) | mysql -uphpbb -pseekrit
egrep \(GET\|HEAD\).*mp3 access.log | cut -d '"' -f2 | cut -d' ' -f2 | sort | uniq -c | while read count file; do echo "UPDATE phpbb.songfight_playcounts SET playcount = playcount + $count WHERE filename = '$file';" | mysql -uphpbb -pseekrit ; done
echo 'UPDATE phpbb.phpbb_posts SET post_text = '' where post_id=194174' | mysql -uphpbb -pseekrit # clear lunk's original post
mysql -BN -e 'SELECT playcount, filename FROM phpbb.songfight_playcounts ORDER BY playcount DESC' | while read count filename; do echo "UPDATE phpbb.phpbb_posts SET post_text = CONCAT_WS(post_text, '$filename -- $count\n') where post_id=194174"; done | mysql -uphpbb -pseekrit
echo 'UPDATE phpbb.phpbb_posts SET post_checksum = MD5(post_text) WHERE post_id=194174' | mysql -uphpbb -pseekrit # update the checksum FTW
endscript
Last edited by Chumpy on Sun May 03, 2015 1:38 pm, edited 4 times in total.
"I don't recommend ending on a bad joke." --ken
- Lunkhead
- You're No Good
- Posts: 8175
- Joined: Sat Sep 25, 2004 12:14 pm
- Instruments: many
- Recording Method: cubase/mac/tascam4x4
- Submitting as: Berkeley Social Scene, Merisan, Tiny Robots
- Pronouns: he/him
- Location: Berkeley, CA
- Contact:
Re: Song play stats suggestion
Ha ha ha. You think they use MySQL. LOL!!!!
- Lunkhead
- You're No Good
- Posts: 8175
- Joined: Sat Sep 25, 2004 12:14 pm
- Instruments: many
- Recording Method: cubase/mac/tascam4x4
- Submitting as: Berkeley Social Scene, Merisan, Tiny Robots
- Pronouns: he/him
- Location: Berkeley, CA
- Contact:
Re: Song play stats suggestion
Actually, I should have said:
Lunkhead wrote:Ha ha ha. You think they useMySQLa database. LOL!!!!
- fluffy
- Eruption
- Posts: 11097
- Joined: Sat Sep 25, 2004 10:56 am
- Instruments: sometimes
- Recording Method: Logic Pro X
- Submitting as: Sockpuppet
- Pronouns: she/they
- Location: Seattle-ish
- Contact:
Re: Song play stats suggestion
The HTTP access logs are easy to get at via cron job or whatever.
Remember to also collate by user agent so that it's easy to filter out shit like baidu and whatever.
Remember to also collate by user agent so that it's easy to filter out shit like baidu and whatever.
- Chumpy
- Twilight Sparkle
- Posts: 696
- Joined: Sat Apr 18, 2015 2:06 pm
- Instruments: Vocals, guitar, bass
- Recording Method: Logic
- Submitting as: Jerkatorium, Chumpy
- Pronouns: he/him
- Location: Seattle, WA
- Contact:
Re: Song play stats suggestion
The idea is that you paste something a lot like my code above into /etc/logrotate.d/apache2 in the section that rotates /var/log/apache2/*.log or whatever. That way stats get generated whenever the access logs get rotated, which may be weekly, daily, hourly or whenever. It's somewhat cleaner than using a cron job since this way you can be pretty sure that the same HTTP request won't be counted multiple times.fluffy wrote:The HTTP access logs are easy to get at via cron job or whatever.
Yeah, shit like that bugs me too, but I think it's better to think of the numbers as just a silly approximation of how many times somebody has listened to your song. It's not like anybody is going to be collecting revenue from these numbers, they're good enough for bragging rights as search engines will inflate the counts for everyone's songs.fluffy wrote:Remember to also collate by user agent so that it's easy to filter out shit like baidu and whatever.
"I don't recommend ending on a bad joke." --ken
- fluffy
- Eruption
- Posts: 11097
- Joined: Sat Sep 25, 2004 10:56 am
- Instruments: sometimes
- Recording Method: Logic Pro X
- Submitting as: Sockpuppet
- Pronouns: she/they
- Location: Seattle-ish
- Contact:
Re: Song play stats suggestion
We don't have access to logrotate. We're on Dreamhost shared hosting. The better bet is to wait until sometime after the daily rotation and then ingest yesterday's logs.
And by "we" I mean remember that Lunkhead and I help run the site, so.
Anyway, please don't deep-dive into how we might go about implementing it since you don't actually know anything about what's already there or available to us, y'know? And posting a giant chunk of detailed mySQL isn't that useful in that context.
And by "we" I mean remember that Lunkhead and I help run the site, so.
Anyway, please don't deep-dive into how we might go about implementing it since you don't actually know anything about what's already there or available to us, y'know? And posting a giant chunk of detailed mySQL isn't that useful in that context.
- jb
- Hot for Teacher
- Posts: 4165
- Joined: Sat Sep 25, 2004 10:12 am
- Instruments: Guitar, Cello, Keys, Uke, Vox, Perc
- Recording Method: Logic X
- Submitting as: The John Benjamin Band
- Pronouns: he/him
- Location: WASHINGTON, DC
- Contact:
Re: Song play stats suggestion
Stop trying to help, Chumpy!
What an asshole, amirite?
Anyway, you should probably know that this is prooooooobably more trouble than it's worth...
Let's talk about it in Portland in Aug. Chumpy, you should come.
JB
What an asshole, amirite?
Anyway, you should probably know that this is prooooooobably more trouble than it's worth...
Let's talk about it in Portland in Aug. Chumpy, you should come.
JB
blippity blop ya don’t stop heyyyyyyyyy
- fluffy
- Eruption
- Posts: 11097
- Joined: Sat Sep 25, 2004 10:56 am
- Instruments: sometimes
- Recording Method: Logic Pro X
- Submitting as: Sockpuppet
- Pronouns: she/they
- Location: Seattle-ish
- Contact:
Re: Song play stats suggestion
Yeah totally!
Let me rephrase a bit: I appreciate the idea. But don't get too wedded to a site feature that doesn't really add much, and consider that real-life implementations of things can be a lot more difficult than the quick-and-simple notion you think it might be. :)
Let me rephrase a bit: I appreciate the idea. But don't get too wedded to a site feature that doesn't really add much, and consider that real-life implementations of things can be a lot more difficult than the quick-and-simple notion you think it might be. :)
- Chumpy
- Twilight Sparkle
- Posts: 696
- Joined: Sat Apr 18, 2015 2:06 pm
- Instruments: Vocals, guitar, bass
- Recording Method: Logic
- Submitting as: Jerkatorium, Chumpy
- Pronouns: he/him
- Location: Seattle, WA
- Contact:
Re: Song play stats suggestion
Oh shared hosting, right. You're right I have no clue, I just assumed you guys had access to the DB the phpbb forums ran on. I've had to dick with the phpbb DB in the past. Oh yeah Portland, yeah! I'm gonna be there and look forward to singing a detailed chunk of MySQL that is not useful in any context. You're gonna love it!
"I don't recommend ending on a bad joke." --ken
- fluffy
- Eruption
- Posts: 11097
- Joined: Sat Sep 25, 2004 10:56 am
- Instruments: sometimes
- Recording Method: Logic Pro X
- Submitting as: Sockpuppet
- Pronouns: she/they
- Location: Seattle-ish
- Contact:
Re: Song play stats suggestion
We have access to the forum DB, of course, but we don't have fine-grained access to the log rotation engine. And setting up a new database is a pain for various reasons, and kind of unnecessary for this anyway if it's just going to be a batch processed job. (And for something like this I'd probably use SQLite anyway, for lots of reasons.)