JavaScript, webdev

YouTube Analytics Dashboard With Apps Script

player_apisThere are cases where you wish to collect statistics on your youtube videos or channel. There are few options to do it with YouTube API. As the YouTube’s API supports many languages you can choose the one that will work for your environment. In many of these options, you will need to develop a server side that will fetch the data and a front-end to present it and give the users option to query it. If you wish to dive deeper (e.g. specific metric on channel performance and videos statistics), you will need to work with YouTube Analytics API.

In this post, we will see a simple example to create a dashboard that will be updated on a daily basis. Since we wish to save ourselves from building (and maintaining!) a server side and a web app to access it, we will use the power of Google Apps Script (GAS) and Google sheets.

The Dashboard

Here you can find the “Dashboard example sheet”.
Feel free to copy it and work on your own version that is not in ‘read-only’ mode.

The first thing you will notice are the amazing colors.

Screenshot 2014-08-04 15.50.15

We have two main function in this dashboard

Screenshot 2014-08-04 15.50.36

One option is to extract the IDs from the links we provide in column D and the other goes and update all the stats on the active sheet. As a final step, we have a little dialog that give us the confirmation on this last update with errors (if we have some broken links in column D).

Code

You can find the code for this example on a repository for Apps Scripts that I’ve created on GitHub. On the script over there you will also see a function that use the Analytic API (you must be the owner of the channel in order to run it). It gives much more power and many different metrics you can query on.

Another option is to follow the snippet below that drive this example

/**
* YouTube Dashboard Stats Example
* Fetch stats on videos and channel by using YT APIs.
* 1. It using the ATOM feeds - v1.
* 2. For the channel we are using the v3 version of the API.
*
* @Author: Ido Green | @greenido | +Greenido
* @Date: Aug 2014
*
* @see:
* https://developers.google.com/youtube/analytics/v1/code_samples/apps-script
* https://developers.google.com/youtube/analytics/sample-requests#channel-time-based-reports
* https://developers.google.com/apis-explorer/#p/youtubeAnalytics/v1/youtubeAnalytics.reports.query
*/
//
// A Helper function to extract the ID of our video
// It works both on version of links:
// 1. https://www.youtube.com/watch?v=BuHEhmp47VE
// 2. http://youtu.be/BuHEhmp47VE
//
function extractVideoID() {
var curSheet = SpreadsheetApp.getActiveSheet();
var ytLinks = curSheet.getRange("D:D");
var totalRows = ytLinks.getNumRows();
var ytVal = ytLinks.getValues();
// let's run on the rows
for (var i = 1; i <= totalRows - 1; i++) {
var curLink = ytVal[i][0];
if (curLink == "") {
break;
}
var videoID = "";
var inx1 = curLink.indexOf('watch?v=') + 8;
if (inx1 == 7) {
// check if it's the short format: http://youtu.be/75EuHl6CSTo
if (curLink != "" && curLink.indexOf("youtu.be") > 0) {
videoID = curLink.substr(16, curLink.length);
}
}
else {
// we have the link in this format: https://www.youtube.com/watch?v=YIgSucMNFAo
var inx2 = curLink.indexOf("&", inx1);
if (inx2 > inx1) {
videoID = curLink.substr(inx1, inx2-inx1);
} else {
videoID = curLink.substr(inx1, curLink.length);
}
}
curSheet.getRange("E" + (i+1)).setValue(videoID);
}
var htmlMsg = HtmlService
.createHtmlOutput('<h3>Done - Please check the IDs on Column D:D</h3>').setTitle('YT Dashboard Example').setWidth(450).setHeight(300);
SpreadsheetApp.getActiveSpreadsheet().show(htmlMsg);
}
//
// Run on all the rows and according to the video ID fetch the feed
//
function fetchAllData() {
var start = new Date().getTime();
var curSheet = SpreadsheetApp.getActiveSheet();
var ytIds = curSheet.getRange("E:E");
var totalRows = ytIds.getNumRows();
var ytVal = ytIds.getValues();
var errMsg = "<h4>Errors:</h4> <ul>";
// let's run on the rows after the header row
for (var i = 1; i <= totalRows - 1; i++) {
// e.g. for a call: https://gdata.youtube.com/feeds/api/videos/YIgSucMNFAo?v=2&prettyprint=true
if (ytVal[i] == "") {
Logger.log("We stopped at row: " + (i+1));
break;
}
var link = "https://gdata.youtube.com/feeds/api/videos/&quot; + ytVal[i] + "?v=2&prettyprint=true";
try {
fetchYTdata(link, i+1);
}
catch (err) {
errMsg += "<li>Line: " + i + " we could not fetch data for ID: " + ytVal[i] + "</li>";
Logger.log("*** ERR: We have issue with " + ytVal[i] + " On line: " + i);
}
}
if (errMsg.length < 24) {
// we do not have any errors at this run
errMsg += "<li> All good for now </li>";
}
var end = new Date().getTime();
var execTime = (end - start) / 1000;
var htmlApp = HtmlService
.createHtmlOutput('<h2>Done updating!</h2><p>It took us: '+ execTime + 'sec. to update: ' +
(i+1) + ' videos</p>' + errMsg).setTitle('YT Stats').setWidth(450).setHeight(450);
SpreadsheetApp.getActiveSpreadsheet().show(htmlApp);
}
//
// Read YT stats data on our videos and fill the sheet with the data
//
function fetchYTdata(url, curRow) {
//var url = 'https://gdata.youtube.com/feeds/api/videos/Eb7rzMxHyOk?v=2&prettyprint=true&#39;;
var rawData = UrlFetchApp.fetch(url).getContentText();
//Logger.log(rawData);
// published <published>2014-05-09T06:22:52.000Z</published>
var inx1 = rawData.indexOf('published>') + 10;
var inx2 = rawData.indexOf("T", inx1);
var publishedDate = rawData.substr(inx1, inx2-inx1);
// viewCount='16592'
var inx1 = rawData.indexOf('viewCount') + 11;
var inx2 = rawData.indexOf("'/>", inx1);
var totalViews = rawData.substr(inx1, inx2-inx1);
// <yt:duration seconds='100'/>
var inx1 = rawData.indexOf('duration seconds') + 18;
var inx2 = rawData.indexOf("'/>", inx1);
var durationSec = rawData.substr(inx1, inx2-inx1);
Logger.log(curRow + ") TotalViews: " + totalViews + " durationSec: " + durationSec);
// update the sheet
var ss = SpreadsheetApp.getActiveSheet();
ss.getRange("C" + curRow).setValue(publishedDate);
ss.getRange("G" + curRow).setValue(totalViews);
ss.getRange("H" + curRow).setValue(durationSec);
}
//
// Our custom menu
//
function onOpen() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{ name : "Update Stats", functionName : "fetchAllData"},
{ name : "Extract Video IDs", functionName : "extractVideoID"}
];
spreadsheet.addMenu("YT Dashboard", entries);
};

As a final step, we can create an automated task (e.g. cronjob) that will run our update function every night. This will make sure, we have an updated dashboard each time we open it.

YouTube Analytic API

If you wish to gain the full spectrum of metrics that YouTube API expose, you will need to use the Analytic API. Few metrics that you can fetch from this powerful API are:

  1. View metrics
  2. Watch time metrics
  3. Engagement metrics
  4. Playlist metrics
  5. Annotations metrics
  6. Audience retention metrics
  7. Earnings metrics
  8. Ad Performance metrics

Here is an example for an Apps Script that will fetch few metrics per video ID and will update your dashboard.

/**
* YouTube Analytics API Example
* Fetch views and 'estimated view time' on videos you have in your channel.
*
* @Author: Ido Green
* @Date: Aug 2014
*
*/
function getVideoEstimatedMinutesWatched(videoId) {
var myChannels = YouTube.Channels.list('id', {mine: true});
var channel = myChannels.items[0];
var channelId = channel.id;
if (channelId) {
var today = new Date();
var monthAgo = new Date();
monthAgo.setMonth(today.getMonth() - 1);
var todayFormatted = Utilities.formatDate(today, 'UTC', 'yyyy-MM-dd')
var MonthAgoFormatted = Utilities.formatDate(monthAgo12, 'UTC', 'yyyy-MM-dd');
var analyticsResponse = YouTubeAnalytics.Reports.query(
'channel==' + channelId,
MonthAgoFormatted,
todayFormatted,
'views,estimatedMinutesWatched',
{
dimensions: 'video',
filters: 'video==' + videoId
});
Logger.log("Analytics for " + videoId + ": \n " + analyticsResponse.rows)[0];
return analyticsResponse.rows[0];
}
else {
return "N/A Please check the video ID";
}
}

Happy hacking.

App script

Advertisement
Standard

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s