Page 1 of 1

Song play stats suggestion

Posted: Sat May 02, 2015 8:42 am
by Chumpy
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.

Re: Song play stats suggestion

Posted: Sat May 02, 2015 9:26 am
by fluffy
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.

Re: Song play stats suggestion

Posted: Sun May 03, 2015 7:00 am
by ken
Bandcamp will tell you if a song was downloaded, streamed, streamed to completion or such. Maybe something like that could be implemented.

Re: Song play stats suggestion

Posted: Sun May 03, 2015 9:37 am
by Lunkhead
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.

Re: Song play stats suggestion

Posted: Sun May 03, 2015 10:24 am
by Chumpy
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.
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.

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

Re: Song play stats suggestion

Posted: Sun May 03, 2015 12:03 pm
by Lunkhead
Ha ha ha. You think they use MySQL. LOL!!!!

Re: Song play stats suggestion

Posted: Sun May 03, 2015 1:36 pm
by Lunkhead
Actually, I should have said:
Lunkhead wrote:Ha ha ha. You think they use MySQL a database. LOL!!!!

Re: Song play stats suggestion

Posted: Sun May 03, 2015 1:38 pm
by fluffy
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.

Re: Song play stats suggestion

Posted: Sun May 03, 2015 2:02 pm
by Chumpy
fluffy wrote:The HTTP access logs are easy to get at via cron job or whatever.
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:Remember to also collate by user agent so that it's easy to filter out shit like baidu and 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.

Re: Song play stats suggestion

Posted: Sun May 03, 2015 7:13 pm
by fluffy
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.

Re: Song play stats suggestion

Posted: Sun May 03, 2015 7:29 pm
by jb
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

Re: Song play stats suggestion

Posted: Sun May 03, 2015 8:15 pm
by fluffy
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. :)

Re: Song play stats suggestion

Posted: Sun May 03, 2015 8:32 pm
by Chumpy
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!

Re: Song play stats suggestion

Posted: Sun May 03, 2015 8:40 pm
by fluffy
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.)