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.

Moderator: Mods

User avatar
Chumpy
Twilight Sparkle
Posts: 482
Joined: Sat Apr 18, 2015 2:06 pm
Instruments: Vocals, guitar
Recording Method: Logic
Submitting as: Jerkatorium
Location: Seattle, WA
Contact:

Song play stats suggestion

Postby Chumpy » Sat May 02, 2015 8:42 am

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.
"Nice and quirky. Like the lyrics, but Is this arty enough for Songfight?" --Spud
User avatar
fluffy
Ganon
Posts: 9316
Joined: Sat Sep 25, 2004 10:56 am
Instruments: sometimes
Recording Method: Logic Pro X
Submitting as: Sockpuppet
Location: The Plaidlands (also, Seattle)
Contact:

Re: Song play stats suggestion

Postby fluffy » Sat May 02, 2015 9:26 am

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
Notable Hylian
Posts: 3130
Joined: Sat Sep 25, 2004 6:10 pm
Instruments: Guitar, bass, drums, keys
Recording Method: BLA modded MOTU 828mk2, Cubase 5, UAD-2
Submitting as: Ken's Super Duper Band 'n Stuff
Location: berkeley, ca
Contact:

Re: Song play stats suggestion

Postby ken » Sun May 03, 2015 7:00 am

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
Lunkhead
Princess Zelda
Posts: 5042
Joined: Sat Sep 25, 2004 12:14 pm
Instruments: many
Recording Method: cubase/reason/mac/tascam4x4
Submitting as: Berkeley Social Scene, Merisan, Tiny Robots
Location: Berkeley, CA
Contact:

Re: Song play stats suggestion

Postby Lunkhead » Sun May 03, 2015 9:37 am

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: 482
Joined: Sat Apr 18, 2015 2:06 pm
Instruments: Vocals, guitar
Recording Method: Logic
Submitting as: Jerkatorium
Location: Seattle, WA
Contact:

Re: Song play stats suggestion

Postby Chumpy » Sun May 03, 2015 10:24 am

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.
"Nice and quirky. Like the lyrics, but Is this arty enough for Songfight?" --Spud
Lunkhead
Princess Zelda
Posts: 5042
Joined: Sat Sep 25, 2004 12:14 pm
Instruments: many
Recording Method: cubase/reason/mac/tascam4x4
Submitting as: Berkeley Social Scene, Merisan, Tiny Robots
Location: Berkeley, CA
Contact:

Re: Song play stats suggestion

Postby Lunkhead » Sun May 03, 2015 12:03 pm

Ha ha ha. You think they use MySQL. LOL!!!!
Lunkhead
Princess Zelda
Posts: 5042
Joined: Sat Sep 25, 2004 12:14 pm
Instruments: many
Recording Method: cubase/reason/mac/tascam4x4
Submitting as: Berkeley Social Scene, Merisan, Tiny Robots
Location: Berkeley, CA
Contact:

Re: Song play stats suggestion

Postby Lunkhead » Sun May 03, 2015 1:36 pm

Actually, I should have said:

Lunkhead wrote:Ha ha ha. You think they use MySQL a database. LOL!!!!
User avatar
fluffy
Ganon
Posts: 9316
Joined: Sat Sep 25, 2004 10:56 am
Instruments: sometimes
Recording Method: Logic Pro X
Submitting as: Sockpuppet
Location: The Plaidlands (also, Seattle)
Contact:

Re: Song play stats suggestion

Postby fluffy » Sun May 03, 2015 1:38 pm

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: 482
Joined: Sat Apr 18, 2015 2:06 pm
Instruments: Vocals, guitar
Recording Method: Logic
Submitting as: Jerkatorium
Location: Seattle, WA
Contact:

Re: Song play stats suggestion

Postby Chumpy » Sun May 03, 2015 2:02 pm

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.
"Nice and quirky. Like the lyrics, but Is this arty enough for Songfight?" --Spud
User avatar
fluffy
Ganon
Posts: 9316
Joined: Sat Sep 25, 2004 10:56 am
Instruments: sometimes
Recording Method: Logic Pro X
Submitting as: Sockpuppet
Location: The Plaidlands (also, Seattle)
Contact:

Re: Song play stats suggestion

Postby fluffy » Sun May 03, 2015 7:13 pm

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
Notable Hylian
Posts: 3515
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
Location: WASHINGTON, DC
Contact:

Re: Song play stats suggestion

Postby jb » Sun May 03, 2015 7:29 pm

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
User avatar
fluffy
Ganon
Posts: 9316
Joined: Sat Sep 25, 2004 10:56 am
Instruments: sometimes
Recording Method: Logic Pro X
Submitting as: Sockpuppet
Location: The Plaidlands (also, Seattle)
Contact:

Re: Song play stats suggestion

Postby fluffy » Sun May 03, 2015 8:15 pm

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: 482
Joined: Sat Apr 18, 2015 2:06 pm
Instruments: Vocals, guitar
Recording Method: Logic
Submitting as: Jerkatorium
Location: Seattle, WA
Contact:

Re: Song play stats suggestion

Postby Chumpy » Sun May 03, 2015 8:32 pm

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!
"Nice and quirky. Like the lyrics, but Is this arty enough for Songfight?" --Spud
User avatar
fluffy
Ganon
Posts: 9316
Joined: Sat Sep 25, 2004 10:56 am
Instruments: sometimes
Recording Method: Logic Pro X
Submitting as: Sockpuppet
Location: The Plaidlands (also, Seattle)
Contact:

Re: Song play stats suggestion

Postby fluffy » Sun May 03, 2015 8:40 pm

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

Return to “Complaints and Suggestions”

Who is online

Users browsing this forum: Bing [Bot] and 1 guest