What, exactly, is user_agent_id in the “downloadintent” tables of PodLove Publisher? Is it a unique id for each listener listening to an episode or episodes?
No, it’s a reference to the table
wp_podlove_useragent. A “User Agent” is a description a software (for example podcatchers) provide to identify themselves. So it identifies the software, not the listener.
Thank you, Eric!
So I’m wondering if there’s a way to write a query that will identify unique listeners?
Listeners can end up playing an episode multiple times or doing a lot of stopping and starting, so I’m trying to figure out what my hard core listener numbers are.
I was trying this query, but obviously now I know it won’t work, now that I understand what user_agent_id does.
SELECT DATE_FORMAT(accessed_at, ‘%Y%m’), COUNT(DISTINCT user_agent_id) FROM
wp_podlove_downloadintentclean GROUP BY DATE_FORMAT(accessed_at, ‘%Y%m’) ORDER BY DATE_FORMAT(accessed_at, ‘%Y%m’) DESC LIMIT 0,100
Is there an alternative query I can do to achieve my goal?
What you want is the
request_id, which includes the user agent and the IP address. However for GDPR compliance, these request ids are only consistent within the same day. So on 2022-03-11 the same listener accessing the same episode a hundred times always has the same request_id. But he will have a different request id on 2022-03-10. We are using this column internally to ensure only unique downloads are present in
Oh wow! Okay, so then there’s really no way at any given time to know what the listener count is for any date range other than within one day.