The most common Youtube Report or Endpoint is the Basic Stats from Video Reports (see screenshot below)
This report type works in a specific way. It retrieves at every fetch, whatever the Time Range specified, all video published from the account since the creation of this account. However it will only display in the extracts, the performance data of the Time Range specified.
For instance, when fetching September 2019 data, all my videos, including the ones published in 2012 will be recovered, but only the performance data (shares, views, likes, dislikes, subscribers gained..) from September will be displayed.
The main issue when pulling data from Youtube Analytics comes from the missing Time stamp or Date Column. The extract is implicitly reporting the videos on the Time Range specified, without any daily attribution.
Therefore, here are the best Practice to fetch, import and overwrite Youtube data:
FETCH LIFETIME EVERY DAY
When fetching Lifetime, overwriting only on Datastream is sufficient as every new extract will contain accumulated data from account creation to Date.
FETCH CURRENT/LAST MONTH + METADATA EXTRACT DATE
If client wants to visualize data from the ongoing Month, selecting "Current Month" and fetching every day is necessary. If client however can wait for the beginning of next Month to visualize its data, fetching "Last Month" one time on the first day of next month is enough.
Since there is no field "Date" pulled from the API, it is also important to set correct overwrite options as we cannot simply overwrite on "Video_published_at", with the risk to overwrite everything with each new import.
To get a timestamp, we need to extract the date from the meta_data as followed:
Once the fields start_date and end_date have been created, we need to map them in Schema mapping. In the meantime, we need to select in Schema mapping the 2 following Key columns: Start_date, Video_id, (Or Start_date and Video_published_at).
Finally, in Tab Overwrite options, we need to select "Datastream" + "Key Columns".
In this way, every new import will overwrite previous import if the data are from the same month (based on Start_date which will always hold the value of the 1st of the month).
This would result in Monthly accumulated data. Extract from the 25/09 will overwrite extract from the 24/09 containing the latest data of the current Month. On the 01/10, I will now start importing the data for October without impacting the data from September anymore as my Start Date now changed from 01/09 to 01/10).
EXAMPLE WHEN IMPORTING TO DB:
- Fetch "Current Month".
- Extract Start_date from the Metadata.
- Set Video_id as well as Start_date as Key columns.