Youtube Connector and Video Titles

[JustinRamsey]

Good Afternoon, 

I am working with pulling data from our company youtube channel. I have the connection established and some metrics pulling in but I am struggling to get data by Video. I would like to know views per video, but whenever I enter video as a dimension in the query I receive a (400) Bad Request error. What do I need to do different to the below script to add in the videos as a dimension (preferably by video title)? 

YOUTUBE_TEMP:

LOAD dim_day as [Report.dim_day], 

metric_subscribersGained as [Report.metric_subscribersGained], 

metric_subscribersLost as [Report.metric_subscribersLost], 

metric_views as [Report.metric_views], 

metric_averageViewDuration as [Report.metric_averageViewDuration], 

metric_estimatedMinutesWatched as [Report.metric_estimatedMinutesWatched];

SELECT dim_day,

metric_subscribersGained,

metric_subscribersLost,

metric_views,

metric_averageViewDuration,

metric_estimatedMinutesWatched

FROM Report

WITH PROPERTIES (

ids='channel==MINE',

startDate='01-01-2012',

endDate='01-01-2030',

metrics='subscribersGained,subscribersLost,views,averageViewDuration,estimatedMinutesWatched',

dimensions='day',

filters='',

sort='day',

maxResults=''

);

[diegozecchini]

Hi! try this

YOUTUBE_TEMP:
LOAD dim_day as [Report.dim_day],
dim_video as [Report.dim_video],
dim_videoTitle as [Report.dim_videoTitle],
metric_views as [Report.metric_views],
metric_averageViewDuration as [Report.metric_averageViewDuration],
metric_estimatedMinutesWatched as [Report.metric_estimatedMinutesWatched];

SELECT dim_day,
dim_video,
dim_videoTitle,
metric_views,
metric_averageViewDuration,
metric_estimatedMinutesWatched
FROM Report
WITH PROPERTIES (
ids='channel==MINE',
startDate='01-01-2012',
endDate='01-01-2030',
metrics='views,averageViewDuration,estimatedMinutesWatched',
dimensions='day,video,videoTitle',
filters='',
sort='day',
maxResults=''
);

If you still encounter a (400) Bad Request, it is worth to double-check:

That your YouTube Data API is properly authenticated.
That your channel has sufficient video data available.
That the query respects API quota limits.

[JustinRamsey]

I tried this and still got the Bad Request error. I dont know what else to check with the API connection if I can pull every other dimension but the video titles. Any suggestions on how/what to check? 

[diegozecchini]

Hi! If I were you I would check API permission (Scopes) Go to OAuth consent screen, and make sure your app has the correct OAuth scopes for youtube data and analytics API:
https://www.googleapis.com/auth/youtube.readonly
https://www.googleapis.com/auth/yt-analytics.readonly
If necessary, add these scopes and reauthenticate your API access.

To confirm youtub api are enabled go to Enabled APIs & Services and search for
-YouTube Data API v3 (for video titles)
-YouTube Analytics API (for video metrics)

If not enabled, click on them.

[JustinRamsey]

We were able to finally pull the video dim only when we removed the day dimension. Not sure why they both are not compatible in the same load script. Would seem pretty common to want to know video views per day 

[diegozecchini]

Hi!

Good to know!
A possible idea could be YouTube Analytics API may aggregate data differently when multiple dimensions are used.

A workarounds could be first, to fetch video data (video, videoTitle, views).
Second, fetch daily aggregated metrics (day, views).
Then, join the datasets in Qlik. Does it sound good?

[JustinRamsey]

Theres no common value without the video ID to join the data sets. I mean yes I could download again and get views over time but still would not be able to filter on the individual videos

[JustinRamsey]

Stranger still, we have found that having dims video and day works as long as we put the video ID's as a filter statement. I'm starting to think this is a bug 

[diegozecchini]

Thanks for sharing, it is strange indeed

[hooram95]

Yeah, that 400 error usually pops up because some metrics and dimensions don’t mix well — video-level data often needs video or video==ID filters and compatible metrics. Try querying by video as the dimension with a shorter date range or per-video IDs.

Also funny how YouTube gives so much detail for YouTube Premium stats but makes basic video queries tricky sometimes