Centovacast - Getting Listener Statistics via MySQL

Posted on Jan 29, 2018

We ran into a little issue with our Centovacast installation last month. It turns out, that if you have a few large radio stations using the same server, the MySQL Database tables get rather full (20 million rows), and when trying to pull the data back into the Centovacast interface was causing some issues (timeout and 500 errors etc). Which ultimately meant our customers could not retrieve the statistics they needed.

So, the only solution was to manually query the tables, and generate our own statistics to provide to our customers. I wrote a little PHP/HTML interface for this, however you can easily do this via an MySQL Client.

Here’s what the customers were requesting, and the SQL queries to get them!

SQL Time!

Replace the accountid=269 in the queries below with the account you wish to get the data for.

You can get the accountid from the acounts table:

SELECT * FROM centovacast.accounts;

# Total sessions

SELECT COUNT(*) AS sessions FROM visitorstats_sessions WHERE starttime > ‘2017-12-25’ AND starttime < ‘2017-12-31’ AND accountid=269;

# Total listener seconds

SELECT SUM(duration) as duration FROM visitorstats_sessions WHERE starttime > ‘2017-12-25’ AND starttime < ‘2017-12-31’ AND accountid=269;

# Average session length (seconds)

SELECT AVG(duration) as seconds FROM visitorstats_sessions WHERE starttime > ‘2017-12-25’ AND starttime < ‘2017-12-31’ AND accountid=269;

# Total data transfer (KB)

SELECT SUM(bandwidth) as bandwidth FROM visitorstats_sessions WHERE starttime > ‘2017-12-25’ AND starttime < ‘2017-12-31’ AND accountid=269;

# Average transfer (KB)

SELECT AVG(bandwidth) as bandwidth FROM visitorstats_sessions WHERE starttime > ‘2017-12-25’ AND starttime < ‘2017-12-31’ AND accountid=269;

# Unique Listeners

SELECT COUNT(DISTINCT ipaddress) FROM visitorstats_sessions WHERE starttime > ‘2017-12-25’ AND starttime < ‘2017-12-31’ AND accountid=269;

# Unique Countries

SELECT COUNT(DISTINCT country) FROM visitorstats_sessions WHERE starttime > ‘2017-12-25’ AND starttime < ‘2017-12-31’ AND accountid=269;

# ASCAP music sessions

SELECT COUNT(*) FROM centovacast.playbackstats_tracks WHERE starttime > ‘2017-12-25’ AND starttime < ‘2017-12-31’ AND accountid=269 GROUP BY DAY(starttime), HOUR(starttime), name;

With most of my blog posts, I just write this stuff down for future reference. However I hope it’s helped at least someone in the same situation!