How to calculate video played to 25% from API reports

[kis...@citizennet.com]

I am fetching last 7 days campaign performance reports through API. Now I want to calculate the all metric values at the current day from last 7 days.

To calculate video_view_rate I have added all VideoViews divided by sum of impressions( from docs) which will gives me the exact answer.

In the same way I want to calculate for other metrics which are video_played_to_25%, video_played_to_50%.

[Dhanya Sundararaju (AdWords API Team)]

Hi Kishore,

You can use the fields: VideoQuartile25Rate and VideoQuartile50Rate in Campaign Performance Report to get the percentage of impressions where the viewer watched 25% and 50% of your video respectively. If that does not answer your question, could you please elaborate with specific examples from the UI so that I can check further? 

Regards,
Dhanya, AdWords API Team

[kis...@citizennet.com]

Hi Dhanya,

I am using CampaignPerformance report only. I am giving an example of my case. Lets say I am fetching CampaignPerformance Report from API for last 7 days with impressions, VideoQuartile25Rate, VideoQuartile50Rate. So I will get 7 records with those metrics and I am calculating total value for that day also like below

day, impressions, video_played_to_25%, video_played_to_50%, total_impressions, total_video_played_to_25%, total_video_played_to_50% 

day1, 50, 30, 40, 50, 30, 40

day2, 40, 20, 35, 90, 50, 75 

day3, 20, 10, 30, 110, 60, 105 

day4, 20, 10, 30, 130, 70, 135 

day5, 20, 10, 30, 150, 80, 165 

day6, 20, 10, 30, 170, 90, 195 

day7, 20, 10, 30, 190, 100, 225 

The last 3 columns I have added to calculate total values of that day by adding previous records with current records.

For impressions its correct because we can sum yesterdays impressions with todays to get total, but not other metrics.

For VideViewRate I have calculated using formula of views/impressions to get totals. So I am searching for a way to calculate the same with VideoQuartile25Rate, VideoQuartile50Rate.

Thanks,

N Kishore.

[Dhanya Sundararaju (AdWords API Team)]

Hi Kishore,

As you have a product data specific question, I would suggest reaching out to product support via the Help link available on the top right of your AdWords account. Alternatively, you can also post on the Advertiser community forum. If you find that the data via API is different from that via the UI, please feel free to write back to us.

[Zweitze]

Hello,

I struggled with this exact question a year and a half ago.

You are talking about InStream video ads. For those ads, metric Impressions states the number of videos that were started. VideoQuartile25Rate is the percentage of those starts, where the video was shown for at least 25 percent, VideoQuartile50Rate is the percentage where the video was shown for at least 50 percent etc.

So, your calculation is (in SQL notation):

SUM(Impressions * VideoQuartile25Rate) / SUM(Impressions)

But...

Video campaigns may have two different types of video ads: InStream and VideoDiscovery. VideoDiscovery are ads shown in the search page, and there Impressions have a different meaning for those ads: the number of times that the ad was shown in the search results page. However, VideoDiscovery does not support VideoQuartileXXXRate metrics. In other words, the calculation breaks when a campaign has both InStream and VideoDiscovery ads.

So how do you distinguish the number of Impressions for InStream ads, from Impressions for VideoDiscovery ads?

See my question in this topic 18 months ago.

In short: AdWords Web UI features a segmentation called "View Type" allowing to separate InStream metrics from VideoDiscovery metrics, and allows to distinguish the impressions between the video types. This segmentation is exactly what we need for our calculations. Is this segmentation "Video Type" supported in AdWords API as well? The answer was no, there is no timeframe, check the blog, blablabla. To my knowledge this has not been added (yet).

So the answer is: if you have campaigns with both InStream ads, and VideoDiscovery ads, there is no way.

But there is a workaround. Although campaigns can contain both InStream and VideoDiscovery ads, ad groups can only contain one of those - not both. You can nowadays look that distinction up in attribute AdGroupType, it has value YOUTUBE_WATCH for InStream ads, and YOUTUBE_SEARCH for VideoDiscovery ads. (Note: those values appeared a few months after my question, so that's why this workaround was not described in forementioned topic).

Thus, the workaround is to use ADGROUP_PERFORMANCE_REPORT with a predicate on AdGroupType=YOUTUBE_WATCH, then do the same calculation. If you also want to show Impressions, Clicks, Cost etc. as well, you need to omit the predicate, and also request AdGroupType, and change the calculation accordingly.

When I tested this the numbers matched exactly.

I hope this helps. I would appreciate if you posted your findings - when they match or not match, in the latter case I have to look at this again as well...

[Dhanya Sundararaju (AdWords API Team)]

Hi,

Thank you for sharing your analysis and the workaround. "View type" segment is not available currently, on the Campaign Performance Report. 

[kis...@citizennet.com]

Hi,

Thanks for your workaround. It works for me as we are using only InStream video ads. I can match the all values.