Feature Request: MOAR stats! :)

Hi there!

I’ve “modded” my installation of stats to include a couple of more metrics that I find invaluable for my podcast. Unfortunately, my WP/frontend skills are so lame I can’t make it look nice, so I’ll just send the suggestions here.

1. At least here in Sweden, podcasts are regularly measured by “total listens per week”, when benchmarking for advertisers, and so on. For this, I am using the following SQL:

$sql = "SELECT COUNT(id) AS listens, FROM_DAYS(TO_DAYS(FROM_UNIXTIME(date)) -MOD(TO_DAYS(FROM_UNIXTIME(date)) -1, 7)) AS week FROM $this->_table GROUP BY FROM_DAYS(TO_DAYS(FROM_UNIXTIME(date)) -MOD(TO_DAYS(FROM_UNIXTIME(date)) -1, 7))";

2. As I post new episodes, I like to keep track of “first day listens”, to see how popular each new episode is. That is, I want to know how many listens each episode got on its first day (the day it was published). For this:

$sql = "SELECT wp_posts.post_title, COUNT(wp_ssp_stats.id) AS antal,						DATE_FORMAT(FROM_UNIXTIME(wp_ssp_stats.date), '%Y-%m-%d') AS datum
FROM
wp_posts
JOIN wp_ssp_stats
ON wp_posts.ID = wp_ssp_stats.post_id
WHERE LEFT(wp_posts.post_date, 10) = DATE_FORMAT(FROM_UNIXTIME(wp_ssp_stats.date), '%Y-%m-%d')
GROUP BY
wp_ssp_stats.post_id, DATE_FORMAT(FROM_UNIXTIME(wp_ssp_stats.date), '%Y-%m-%d')
ORDER BY antal DESC";

• This topic was modified 7 years, 1 month ago by M?ns Jonasson.