Automation of metrics for a web service using GoogleDocs + Google Script

my name is Genghis, I'm the co-founder of a web service for command decision tasks Worksection.com

We, at the service tracked the number of monitoring metrics. Visit, registration, conversion, activation, retention, churn and so on. Metrics are in a pivot table in Google Docs. The data for metrics is gathered manually from Google Analytics and from our service. Having spent quite a bit of time, we automated this collection.



A prototype Excel pivot table took a financial model Matthew Carroll for startups.



The data in a table in Google Docs were driven manually. In the end, I was updated every month metrics manually, until I stumbled on overview about services that allow you to visualize metrics

In the West, such services have a lot. For example:

DucksBoard
Totango
Kissmetrics

I wanted more time to automate this process. But since the services were many, and the table was already set up and “normal”, I decided to try to automate the receipt of data into the spreadsheet in Google Docs with the help of Google Script

So my task was:

the

the
• get data from Google Analytics for the last month
the
• to obtain data from the service over the last month
the
• to insert data into the table the page with formatting

After spending half a day on the parse documentation, I wrote a simple script that you can use to get your metrics in your Excel file

Create a new script script.google.com ( you must have Google account )

2. copy the code
the

function setNewMetrics() {

// select the Excel which we are going to write data //////////////////
var ss = SpreadsheetApp.openById("0AjAbTD8WcDQMdC1MWmtUR3VlUWJTSHIzq0dsss1hsgc"); // Insert the hash of your Excel page to which we write !!!!!!!!!!!!!!!!!!!!!
SpreadsheetApp.setActiveSpreadsheet(ss);
var sheet= SpreadsheetApp.setActiveSheet(ss.getSheets()[1]); // select page(sheet) in Excel file !!!!!!!!!!!!!!
Logger.log("page Title = "+sheet.getName()); // Check whether we selected the page - run the script "Run menu-> setNewMetrics" - I press CNTRL + ENTER -  see  the log

var sCol = new Array(); // array for the data that we insert in Excel 
// generated date
var d = new Date();
var monthNames = [ "January", "February", "March", "April", "May", "June","July", "August", "September", "October", "November", "December" ];
var cMonth= d.getMonth(); var cYear = d.getFullYear(); 
cMonth= (cMonth + 12 - 1) % 12 // search for last month ( if you need stats for 2 month ago "1" is replaced by "2" )
if ( (cMonth-1) > 0 ) cYear--; //  adjust  year if Jan 
cMonth_str var = monthNames[cMonth];
sCol[1] = cMonth_str+" "+cYear; // recorded month


// get data from GA /////////////////////////////////////////////////////////////////////
//Before interacting with the GA API, you need to "enable" API, detailed instructions https://developers.google.com/analytics/solutions/articles/reporting-apps-script#registration 
// when you need to get your profile in function getProfile() below 

var sProfile=getProfile(); // select and  connect  the desired profile of the GA, the function need to customize your profile, see below !!!!!!
var oGA = getReportDataForProfile(sProfile).getTotalsForAllResults(); // get the data, metrics can be set in this function !!!!!!
sCol[3] = oGA["ga:visits"]; // recorded visits
sCol[12] = oGA["ga:goalCompletionsAll"]/oGA["ga:visits"]; // got conversion Visit-> Trial
Logger.log( "Data from GA -> "+sCol[12]); // check whether received data GA



// get and process a JSON file with the metrics that I get from the service /////////////////////////////////////////////////////////
// Need to fix the script with the format of your JSON file 
var opts = {"contentType":"multipart/form-data", "method" : "post",}
var response = UrlFetchApp.fetch("http://YOURSITE.com/8IiXcnPkEi3W.json", opts); // get metrics from the service in JSON format like this
/* 
{"24":{"date":"February 2011","new_paid":"360","churn":2},
"23":{"date":"March 2011","new_paid":"38","churn":2}} 
*/
var jsondata = response.getContentText(); 
jsondata = JSON.parse(jsondata); // parsim the data obtained in the variable jsondata 
for (var month in jsondata) {
//Logger.log("==>"+month+jsondata[month] + jsondata[month]["date"]);

// found the data for last month - makes their !!!!!!!!!!!!!!
sCol[7] = jsondata[month]["new"];
sCol[8] = jsondata[month]["lost"];
sCol[22] = jsondata[month]["churn"]+"%";
sCol[23] = jsondata[month]["churn_paid"]+"%";
sCol[25] = jsondata[month]["ltv"];
}
}


// record in Excel ///////////////////////////////////////////////////////////////
// I'll write in the last column of the Excel file. fill the content of the last column
var sLastcolumn = sheet.getLastColumn()+1;
var oRange = sheet.getRange(1,sLastcolumn ) ;
for (var i=1; i<(sCol.length+1); i++) {
if (sCol[i]) oRange.setValue(sCol[i]); // write value in cell 
oRange = oRange.offset(1, 0, 1, 1); // move the pointer down
}
// copy the format from a previous column 
var oRange1 =sheet.getRange(1,sLastcolumn-1,100,1) // get the range of the previous columns and one hundred rows of records
oRange1.copyFormatToRange(sheet,sLastcolumn,sLastcolumn,1,100);// copy format

}



// the function selects the desired profile from GA(google analytics) 
function getProfile() { 
var accounts = Analytics.Management.Accounts.list();

if (accounts.getItems()) {
var firstAccountId = accounts.getItems()[1].getId(); // select the desired account from Google Analytics ( number "1" may need to change "your")!!!!! 
//Logger.log(accounts.getItems()[1].getName()); // check
var webProperties = Analytics.Management.Webproperties.list(firstAccountId); 
if (webProperties.getItems()) {
var firstWebPropertyId = webProperties.getItems()[10].getId();// get the profile №10 ( the profile number is picked by too much)) !!!!!!!
//Logger.log(webProperties.getItems()[10]);// check the profile
var profiles = Analytics.Management.Profiles.list(firstAccountId, firstWebPropertyId);

if (profiles.getItems()) {
var Profile = profiles.getItems()[0];
return Profile;
} else {
throw new Error('No profiles found.');
}
} else {
throw new Error('No webproperties found.');
}
} else {
throw new Error('No accounts found.');
}
}


function getReportDataForProfile(firstProfile) {
// function addresses in Google Analytics data and receives data for the last month 
var profileid as = firstProfile.getId();
var tableId = 'ga:' + profileid as;
var sLastMonthRange = getLastMonth(1); // 

var optArgs = {
/* 'dimensions': 'ga:keyword', // Comma separated list of dimensions.
'sort': '-ga:visits,ga:keyword', // Sort by visits descending, then keyword.
'segment': 'dynamic::ga:isMobile==Yes', // Process only mobile traffic.
'filters': 'ga:source==google', // Display only google traffic.
'start-index': '1',
'max-results': '250' // Display the first 250 results.*/
};

// Make a request to the API.
var results = Analytics.Data.Ga.get(
tableId, // Table id (format ga:xxxxxx).
sLastMonthRange[0], // Start-date (format yyyy-MM-dd).
sLastMonthRange[1], // End-date (format yyyy-MM-dd).
'ga:visits, ga:pageviews,ga:goalCompletionsAll', // Comma seperated list of metrics. ( https://developers.google.com/analytics/resources/articles/gdataCommonQueries) 
optArgs);

if (results.getRows()) {
return results;

} else {
throw new Error('No profiles found');
}
}
// date functions
function getLastNdays(nDaysAgo) {
var today = new Date(); 
var before = new Date();
before.setDate(today.getDate() - nDaysAgo);
return Utilities.formatDate(before, 'GMT', 'yyyy-MM-dd');
}

getLastMonth function() {
var sNow = new Date();
var dd = sNow.getDate();
var mm = sNow.getMonth(); 
var yyyy = sNow.getFullYear();
var sStart = new Date( yyyy, mm-1, 2, 0,0,1 );
var sDays = daysInMonth(yyyy, mm-1) ;
var sEnd = new Date( yyyy, mm-1, sDays+1, 0,0,1 );
//Logger.log(sStart + ""+ sEnd + ' -- ' + sDays); 
return [ Utilities.formatDate(sStart, 'GMT', 'yyyy-MM-dd') , Utilities.formatDate(sEnd, 'GMT', 'yyyy-MM-dd') ] ;
}
function daysInMonth(year, month) {
return new Date(year, month, 0).getDate();
}

Places that you need to make changes, I marked comments with of panic “!!!!!”.
You can run the script by hand once a month. But you can configure Kron to run automatically “Menu -> Resourсe -> Manage Triggers”.
Run the function “setNewMetrics()”.

Hope this helps somebody to automate the acquisition of metrics in Google Docs. Answer the questions ).

Article based on information from habrahabr.ru