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