Song play stats suggestion

Use this forum for title suggestions, bitching at moderators, whining about phpBB, and grand ideas that will solve all of Song Fight's problems.
Post Reply
User avatar
Chumpy
Twilight Sparkle
Posts: 691
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

Post 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.
"I don't recommend ending on a bad joke." --ken
User avatar
fluffy
Eruption
Posts: 11028
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

Post 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.
User avatar
ken
Hot for Teacher
Posts: 3869
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

Post by ken »

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
User avatar
Lunkhead
You're No Good
Posts: 8104
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

Post 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.
User avatar
Chumpy
Twilight Sparkle
Posts: 691
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

Post 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
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
User avatar
Lunkhead
You're No Good
Posts: 8104
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

Post by Lunkhead »

Ha ha ha. You think they use MySQL. LOL!!!!
User avatar
Lunkhead
You're No Good
Posts: 8104
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

Post by Lunkhead »

Actually, I should have said:
Lunkhead wrote:Ha ha ha. You think they use MySQL a database. LOL!!!!
User avatar
fluffy
Eruption
Posts: 11028
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

Post 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.
User avatar
Chumpy
Twilight Sparkle
Posts: 691
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

Post 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.
"I don't recommend ending on a bad joke." --ken
User avatar
fluffy
Eruption
Posts: 11028
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

Post 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.
User avatar
jb
Hot for Teacher
Posts: 4159
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

Post 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
blippity blop ya don’t stop heyyyyyyyyy
User avatar
fluffy
Eruption
Posts: 11028
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

Post 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. :)
User avatar
Chumpy
Twilight Sparkle
Posts: 691
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

Post 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!
"I don't recommend ending on a bad joke." --ken
User avatar
fluffy
Eruption
Posts: 11028
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

Post 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.)
Post Reply