Automate metrics for a web service using GoogleDocs + Google Script

My name is Chingis, I am a co-founder of a web service for team problem solving Worksection.com

In our service we track a number of control metrics. Visits, registration, conversion, activation, retention, outflow, and so on. We conduct metrics in the pivot table in Google Docs. Data for metrics - collected manually from Google Analytics and from our very service. Spending quite a bit of time, we automated this collection.


')
For the prototype of the pivot Excel spreadsheet, they took the financial model of Matthew Carroll for startups .



The data in the table in Google Docs were driven in manually. As a result, I updated the metrics manually every month until I came across a review about the services that allow us to visualize the metrics

In the west, such services are already a lot. For example:

Ducksboard
Totango
Kissmetrics

I once again wanted to automate this process. But since there were a lot of services, and the table was already set up and “familiar”, I decided to try to automate the receipt of data into the table itself in Google Docs using Google Script

So, my task was:

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

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

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

2. copy the code

function setNewMetrics() { //  Excel       ////////////////// var ss = SpreadsheetApp.openById("0AjAbTD8WcDQMdC1MWmtUR3VlUWJTSHIzQ0dsSS1HSGc"); //    Excel      !!!!!!!!!!!!!!!!!!!!! SpreadsheetApp.setActiveSpreadsheet(ss); var sheet= SpreadsheetApp.setActiveSheet(ss.getSheets()[1]); //  (sheet)  Excel  !!!!!!!!!!!!!! Logger.log("  = "+sheet.getName()); // ,      -   " Run-> setNewMetrics" -  CNTRL + ENTER -   var sCol = new Array(); //   ,     Excel //   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 //    (     2   "1"   "2" ) if ( (cMonth-1) > 0 ) cYear--; //  ,   var cMonth_str = monthNames[cMonth]; sCol[1] = cMonth_str+" "+cYear; //   //    GA ///////////////////////////////////////////////////////////////////// //     API GA   "" API,   https://developers.google.com/analytics/solutions/articles/reporting-apps-script#registration //         getProfile()  var sProfile=getProfile(); //      GA,      ,   !!!!!! var oGA = getReportDataForProfile(sProfile).getTotalsForAllResults(); //  ,       !!!!!! sCol[3] = oGA["ga:visits"]; //  visits sCol[12] = oGA["ga:goalCompletionsAll"]/oGA["ga:visits"]; //   Visit-> Trial Logger.log( "  GA -> "+sCol[12]); //     GA  //    JSON   ,      ///////////////////////////////////////////////////////// //        JSON  var opts = {"contentType":"multipart/form-data", "method" : "post",} var response = UrlFetchApp.fetch("http://YOURSITE.com/8IiXcnPkEi3W.json", opts); //       JSON     /* {"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); //      jsondata for (var month in jsondata) { //Logger.log("==>"+month+jsondata[month] + jsondata[month]["date"]); if ( (cMonth_str+" "+ cYear) === jsondata[month]["date"] ) { //      -   !!!!!!!!!!!!!! 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"]; } } //   Excel /////////////////////////////////////////////////////////////// //       Excel .     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]); //     oRange = oRange.offset(1, 0, 1, 1); //    } //      var oRange1 =sheet.getRange(1,sLastcolumn-1,100,1) //         oRange1.copyFormatToRange(sheet,sLastcolumn,sLastcolumn,1,100);//   } //      GA(google analytics) function getProfile() { var accounts = Analytics.Management.Accounts.list(); if (accounts.getItems()) { var firstAccountId = accounts.getItems()[1].getId(); //       (  "1"      "" )!!!!! //Logger.log(accounts.getItems()[1].getName()); //  var webProperties = Analytics.Management.Webproperties.list(firstAccountId); if (webProperties.getItems()) { var firstWebPropertyId = webProperties.getItems()[10].getId();//    №10 (     ) ) !!!!!!! //Logger.log(webProperties.getItems()[10]);//   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) { //    Google Analytics         var profileId = firstProfile.getId(); var tableId = 'ga:' + profileId; 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'); } } //   function getLastNdays(nDaysAgo) { var today = new Date(); var before = new Date(); before.setDate(today.getDate() - nDaysAgo); return Utilities.formatDate(before, 'GMT', 'yyyy-MM-dd'); } function getLastMonth() { 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(); } 

The places in which you need to make changes, I marked the comments with a panic “!!!!!” .
The script can be run by hand once a month. And you can also set the cron to start automatically “Menu -> Resource -> Manage Triggers”.
Run the “setNewMetrics ()” function.

I hope this helps someone automate the retrieval of metrics in Google Docs. I answer the questions).