How do I get the the total number of Impressions and Plays for Media from the Matomo database?

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