We can get the total number of “Impressions” and “Plays” for both audio and video by querying the log_media table in the Matomo database.

Video

To get the total number of “Impressions” for video media, we can use the following SQL query:

SELECT * FROM matomo_log_media WHERE idsite LIKE 'X' AND media_type LIKE '1';

As we can see from the query above, video media is stored as media_type “1” in the Matomo database. For video media, a “Play” is stored in the database in the watched_time column as a value above 1, any value above 1 means that the video was played at least in part.

To get the total number of “Plays” for video media we can use the following SQL query:

SELECT * FROM matomo_log_media WHERE idsite LIKE 'X' AND media_type LIKE '1' AND watched_time > '1';

Audio

Media type “Audio” is stored as media_type “2” in the log_media table, so for example we can retrieve all of the “Audio Impressions” with the following query:

SELECT * FROM matomo_log_media WHERE idsite LIKE 'X' AND media_type LIKE '2';

For Audio media stored in the log_media table, a “Play” is counted when the watched_time column value is greater than 1. (Any row with media_type 2 and watched_time of 1 or 0 indicates that it was not played)

So for example, we can retrieve all of the “Audio Plays” with the following query:

SELECT * FROM matomo_log_media WHERE idsite LIKE 'X' AND media_type LIKE '2' AND watched_time > '1';

If you would like to query other types of RAW tracking data from the Matomo database, you can see our FAQ here: How do I write SQL queries to select visitors, list of pageviews, searches, events in the Matomo database