Автоматизация метрик для веб-сервиса с помощью GoogleDocs + Google Script

Меня зовут Чингис, я сооснователь веб-сервиса для командного решения задач Worksection.com

Мы у себя в сервисе отслеживаем ряд контрольных метрик. Посещения, регистрации, конверсии, активации, удержание, отток и тд. Метрики ведем в сводной таблице в Google Docs. Данные для метрик — собирались вручную из Google Analytics и из самого нашего сервиса. Потратив совсем немного времени, мы автоматизировали этот сбор.

За прототип сводной Excel таблицы брали финансовую модель Matthew Carroll для стартапов.

Данные в таблицу в Google Docs вбивались вручную. В итоге, я так и обновлял каждый месяц метрики вручную, пока не наткнулся на обзор про сервисы, которые позволяют визуализировать метрики

На западе таких сервисов уже много. Например:

DucksBoard
Totango
Kissmetrics

Мне еще раз захотелось автоматизировать этот процесс. Но поскольку сервисов было много, а таблица была уже настроена и “привычна”, я решил попробовать автоматизировать получения данных в саму таблицу в Google Docs с помощью Google Script

Итак, моя задача была:

• получить данные из Google Analytics за последний месяц
• получить данные из сервиса за последний месяц
• вставить данные в таблицу страницу, с форматированием

Потратив полдня на разбор документации, я написал простой скрипт, который можно использовать и для получения ваших метрик в ваш Excel файл

Создаем новый скрипт script.google.com/ ( у вас должны быть учетка Google )

2. копируем код

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(); }  

Места в которых необходимо внести изменения, я пометил комментами с паническим “!!!!!”.
Скрипт можно запускать руками раз в месяц. А можно и настроить крон для запуска автоматически “Меню -> Resourсe -> Manage Triggers”.
Запускаем функцию “setNewMetrics()”.

Надеюсь, это поможет кому-то автоматизировать получение метрик в Google Docs. На вопросы отвечаю ).