Skip to content

Instantly share code, notes, and snippets.

@siliconvallaeys

siliconvallaeys/Get Stats for YouTube Videos for TrueView Ads

Last active August 12, 2020 20:40
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save siliconvallaeys/b7bd08273334afdba1e28cbd6855a461 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save siliconvallaeys/b7bd08273334afdba1e28cbd6855a461 to your computer and use it in GitHub Desktop.
Download ZIP
Append YouTube stats to a video placement report from AdWords to help identify videos to add as placement exclusions.
Raw
Get Stats for YouTube Videos for TrueView Ads
/*
// AdWords Script: Append YouTube Video Stats to Video Placement Report
// ---------------------------------------------------------------------
// Copyright 2017 Optmyzr Inc., All Rights Reserved
//
// This script adds YT statistics like 'likes,' 'dislikes,' 'comments,' etc to a Google Sheet of videos
// that your in-stream video ads have appeared on. You can then use these stats to determine when a video should
// be added as a negative placement, for example when there is too high a ratio of 'dislikes' to 'likes'.
//
// Thanks to Kris Belau from Firewood Marketing for presenting the concept at SMX West 2017.
//
// Note that this script pulls stats from the YouTube Data API and these may not match exactly to what appears
// on the video's view page on youtube.com. No data will be returned for some videos, even when they have
// data on youtube.com
//
//
// For more PPC management tools, visit www.optmyzr.com
//
*/
function main() {
// EDIT THIS. REPLACE THE URL WITH THE URL FOR YOUR DATA SHEET
//-----------------------------
var spreadsheetUrl = "https://docs.google.com/spreadsheets/d/1T3pjCIXPtBBzvzq83g3Phob4nJ7lcojsXJwN2CScd40/edit#gid=1648890643";
// DON'T EDIT ANYTHING PAST HERE ---------
var currentSetting = new Object();
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
var sheet = spreadsheet.getActiveSheet();
var data = sheet.getDataRange();
var numRows = data.getNumRows();
var numColumns = data.getNumColumns();
var values = data.getValues();
var columnNames = new Array();
var spreadsheetInfo = getSpreadsheetData(spreadsheetUrl);
if(!spreadsheetInfo.dislikeCountColumn) {
sheet.getRange(spreadsheetInfo.headerRow+1, numColumns+1, 1, 1).setValue(["dislike count"]);
numColumns++;
spreadsheetInfo.dislikeCountColumn = numColumns;
}
if(!spreadsheetInfo.likeCountColumn) {
sheet.getRange(spreadsheetInfo.headerRow+1, numColumns+1, 1, 1).setValue(["like count"]);
numColumns++;
spreadsheetInfo.likeCountColumn = numColumns;
}
if(!spreadsheetInfo.viewCountColumn) {
sheet.getRange(spreadsheetInfo.headerRow+1, numColumns+1, 1, 1).setValue(["view count"]);
numColumns++;
spreadsheetInfo.viewCountColumn = numColumns;
}
if(!spreadsheetInfo.commentCountColumn) {
sheet.getRange(spreadsheetInfo.headerRow+1, numColumns+1, 1, 1).setValue(["comment count"]);
numColumns++;
spreadsheetInfo.commentCountColumn = numColumns;
}
if(!spreadsheetInfo.favoriteCountColumn) {
sheet.getRange(spreadsheetInfo.headerRow+1, numColumns+1, 1, 1).setValue(["favorite count"]);
numColumns++;
spreadsheetInfo.favoriteCountColumn = numColumns;
}
Logger.log("YT stats are now being added to " + spreadsheetUrl);
for(var i = spreadsheetInfo.firstDataRow; i<numRows; i++) {
var row = values[i];
var url = row[spreadsheetInfo.urlColumn];
var urlParts = url.split("/");
var videoId = urlParts[2];
//Logger.log("video ID: " + videoId);
if(!videoId) break;
try {
var list = YouTube.Videos.list("Statistics", {
id: videoId
});
//Logger.log(JSON.stringify(list));
var items = list.items;
var item = items[0];
var statistics = item.statistics;
if(statistics) {
var dislikeCount = statistics.dislikeCount;
var likeCount = statistics.likeCount;
var favoriteCount = statistics.favoriteCount;
var viewCount = statistics.viewCount;
var commentCount = statistics.commentCount;
sheet.getRange(i+1, spreadsheetInfo.dislikeCountColumn, 1, 1).setValue(dislikeCount);
sheet.getRange(i+1, spreadsheetInfo.likeCountColumn, 1, 1).setValue(likeCount);
sheet.getRange(i+1, spreadsheetInfo.viewCountColumn, 1, 1).setValue(viewCount);
sheet.getRange(i+1, spreadsheetInfo.favoriteCountColumn, 1, 1).setValue(favoriteCount);
sheet.getRange(i+1, spreadsheetInfo.commentCountColumn, 1, 1).setValue(commentCount);
} else {
Logger.log("The YT API returned no stats for video with ID " + videoId);
}
} catch (e) {
Logger.log("No YT data for video with ID " + videoId + ". " + e);
}
}
}
function getSpreadsheetData(spreadsheetUrl) {
var spreadsheetInfo = new Object();
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
var sheet = spreadsheet.getActiveSheet();
var data = sheet.getDataRange();
var numRows = data.getNumRows();
var numColumns = data.getNumColumns();
var values = data.getValues();
var columnNames = new Array();
for(var i = 0; i<numRows; i++) {
if(spreadsheetInfo.headerRow != null) return(spreadsheetInfo);
for(var j = 0; j < numColumns; j++) {
var row = values[i];
var value = row[j];
Logger.log(value);
columnNames[i] = value;
try {
if(value.toLowerCase().indexOf("url") != -1) {
spreadsheetInfo.firstDataRow = i+1;
spreadsheetInfo.headerRow = i;
spreadsheetInfo.urlColumn = j;
} else if(value.toLowerCase().indexOf("like count") != -1) {
if(value.toLowerCase().indexOf("dislike count") != -1) {
spreadsheetInfo.firstDataRow = i+1;
spreadsheetInfo.headerRow = i;
spreadsheetInfo.dislikeCountColumn = j;
} else {
spreadsheetInfo.firstDataRow = i+1;
spreadsheetInfo.headerRow = i;
spreadsheetInfo.likeCountColumn = j;
}
} else if(value.toLowerCase().indexOf("view count") != -1) {
spreadsheetInfo.firstDataRow = i+1;
spreadsheetInfo.headerRow = i;
spreadsheetInfo.viewCountColumn = j;
} else if(value.toLowerCase().indexOf("favorite count") != -1) {
spreadsheetInfo.firstDataRow = i+1;
spreadsheetInfo.headerRow = i;
spreadsheetInfo.favoriteCountColumn = j;
} else if(value.toLowerCase().indexOf("comment count") != -1) {
spreadsheetInfo.firstDataRow = i+1;
spreadsheetInfo.headerRow = i;
spreadsheetInfo.commentCountColumn = j;
}
} catch(e) {
//
}
}
}
}
@dnalob

dnalob commented Jan 14, 2020

Copy link
Copy Markdown

does this still work? very interesting

@siliconvallaeys

siliconvallaeys commented Jan 14, 2020

Copy link
Copy Markdown
Author

I just tested it and it still works for me. Just be sure to connect the YT API as explained in the how-to section here: https://searchengineland.com/heres-script-stop-ads-showing-next-offensive-videos-272850

@siliconvallaeys

siliconvallaeys commented Jan 14, 2020 via email

Copy link
Copy Markdown
Author

@siliconvallaeys

siliconvallaeys commented Aug 12, 2020 via email

Copy link
Copy Markdown
Author

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment