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

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

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



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

    image

    Данные в таблицу в 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. На вопросы отвечаю ).
    Метки:
    Поделиться публикацией
    Реклама помогает поддерживать и развивать наши сервисы

    Подробнее
    Реклама
    Комментарии 19
    • +6
      и, если про метрики будет интересно — могу написать дальше
      • 0
        Спасибо за пост. Но вот вопрос — как вы считаете конверсию в регистрации?
        Мы раньше брали процент регистраций от новых посещений. Но потом обнаружили, что значительная доля «новых» сразу авторизовывались. Оказалось, что это уже зарегистрированные пользователи заходили с другой машины. Пришлось их минусовать.
        • 0
          у нас регистрация и авторизация это разные «цели». Плюс авторизация происходит не на промосайт, а уже на сабдомене аля mycompany.worksection.com
          • 0
            Видимо я неправильно выразился. Как вы считаете конверсию в регистрации? Как отношение регистраций к новым посещениям? А новые посещения как считаете?
            • 0
              у нас несколько конверсий

              «новые посещения» -> «триал»
              «триал» — «платный пользователь»
              «новые посещения» — «платный»
              • 0
                Попробуйте сейчас посчитать конверсию вот так: (Регистрации) — (новые пользователи минус те, кто авторизовался). Знаменатель можно задать через расширенные сегменты аналитика.
          • 0
            и еще вы всегда можете считать регистрации — прямо в сервисе, а не в GA, так будет 100% точно
        • –1
          спасибо, то что нужно
          • +2
            Спасибо! Забыл что такое есть.

            Кстати, а где можно почтитать документацию про число восклицательных знаков в коде и их влияние на скорость\качество работы скрипта? :)
            • 0
              Восклицательные знаки особо опасны для психики в первую очередь, поэтому ими и обозначил все проблемные места
              • +2
                Кстати, а гугл в своих скриптах эксепшны отлавливает?
                Вдруг, у кого-то не очень популярный сайт…
                sCol[12] = oGA["ga:goalCompletionsAll"]/oGA["ga:visits"];
                
                • 0
                  throw new Error('У вас непопулярный сайт');
            • 0
              на 73 строке затык
              • 0
                вы «разрешили использование API Google Analytics» в своем акке?
            • 0
              красавчики. мы тоже внедрять будем
              • 0
                Улыбнуло: «сервисы, которые позволяют визуализировать метрики… Kissmetrics». Мы от него недавно отказались, потому что он превратился у нас в рисовалку графиков.
                • +1
                  ну вот графики мне удобно было на основании данных в Google Docs таблицу рисовать
                  а Kissmoetrics я поверхностно смотрел, а вот там был всякие цели по активации, привлечению и тд. -мне показалось это интересным.
                  зато у них крутой полезный блог

                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.