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