Автоматизация метрик для веб-сервиса с помощью 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. На вопросы отвечаю ).