CRM со сквозной аналитикой в Google Spreadsheets

26.02.2018 / Сева Миронович

Что вы знаете о Client service? Всегда ли клиент прав? Стоит ли делать задачу, которая не имеет смысла? А может 🏃🚴🚌✈️🚀🚗

Прежде чем мы начнем, предлагаю посмотреть обучающее видео по теме, которое я уже заботливо перемотал на ключевой момент.

А теперь расскажу по пунктам, как я это делал.

Тонкости настройки аналитики в Telegram канале

Что такое сквозная аналитика

Это анализ эффективности рекламных кампаний, отслеживающий весь путь клиента от рекламного касания до финальной продажи. В большинстве случаев в отсутствие онлайн-оплаты трекинг customer journey заканчивается на отправке заявки. Однако заявка != продажа, и не отражает реальную эффективность РК, поэтому компании ищут различные способы автоматически уведомлять аналитическую систему о результатах этих заявок. Подобный сервис уже предоставляют различные CRM-системы, но ведь они стоят денег, требуют установки, настройки, обучения, а кто мы, если не стремимся сократить издержки производства. Даже этот блог тратит деньги только на домен.

Итак, чтобы сделать простую crm в spreadsheet'ах нам потребуется:

Логировать информацию, оставленную клиентом в заявке
Уведомлять Google Analytics о результатах контакта

Передаем заявки в Google Spreadsheets

Сперва создадим таблицу для логирования заявок.

Шаблон CRM c GA

CRM в Google Spreadsheets

Время - время, когда была отправлена заявка
timestamp - Unix Time заявки в миллисекундах
Form Id - идентификатор формы, используйте, чтобы различать формы на сайте
Client Id - уникальный идентификатор клиента, нужен для отправки события в аналитику
Имя - указанное имя
Телефон - указанный телефон Статусы - Статус, который нужно отправить в аналитику. Допустимые значения в списке "Все статусы"
Отправить? - для отправки выберите ДА. После этого в аналитику улетит событие со статусом.

Теперь Инструменты - Редактор скриптов:

// идентификатор таблицы, берется из url
var SHEET_KEY = '13HONJjvisEKn4N2ef-qOcl2W8esdr34t89wCbvsEwtz7pngQ'
// лист с заявки
var SHEET_NAME = 'Заявки'

// стандартная функция для обработки GET-параметров
function doGet(e) {
  // документ блокируется от посторонних записей на время действия скрипта
  var lock = LockService.getPublicLock()

  try {
    var doc = SpreadsheetApp.openById(SHEET_KEY)
    var sheet = doc.getSheetByName(SHEET_NAME)
    var data = []

    var a = new Date(parseInt(e.parameter['time']))
    var date =
      a.getFullYear() +
      '.' +
      a.getMonth() +
      '.' +
      a.getDate() +
      '.' +
      a.getHours() +
      ':' +
      a.getMinutes() +
      ':' +
      a.getSeconds()

    // формируется массив с данными заявки
    data = [
      date,
      e.parameter['time'],
      e.parameter['form'],
      e.parameter['clientId'],
      e.parameter['name'],
      e.parameter['phone'],
    ]

    // данные записываются в лист
    sheet.getRange(sheet.getLastRow() + 1, 1, 1, data.length).setValues([data])

    return ContentService.createTextOutput(
      JSON.stringify({ result: 'success' })
    ).setMimeType(ContentService.MimeType.JSON)
  } catch (e) {
    return ContentService.createTextOutput(
      JSON.stringify({ result: 'error', error: e })
    ).setMimeType(ContentService.MimeType.JSON)
  } finally {
    lock.releaseLock()
  }
}

Следом Публикация - Развернуть как веб-приложение.

Запускать от моего имени
Все, включая анонимных пользователей

Сохраните url опубликованного приложения, он пригодится в дальнейшем.

Как вы возможно уже догадались, данные по заявке будем передавать из GTM через GET-запрос. Для этого сперва нам надо получить их. Эта часть, что называется, tricky, ведь здесь многое зависит от конкретного сайта, от того, как на нём работают формы заявок. Я конечно рассмотрю самый изи-вариант: у вас обычная форма, которая трекается стандартным триггером gtm.formSubmit.

Для сбора данных создадим несколько переменных:

{{gtm.element}} - переменная уровня данных

{{Form ID}} - встроенная переменная

{{getTime}}

function () {
  var d = new Date();
  return d.getTime();
}

{{getClientId}}

function(){
  var tracker = ga.getAll()[0], cid;
  if(cid = tracker.get('clientId')) return cid
  else return 'undefined'
}

{{getName}}

// селектор поля с именем на ваше усмотрение
function(){
  return {{gtm.element}}.querySelector('input[placeholder=Имя]').value
}

{{getPhone}}

// селектор поля с именем на ваше усмотрение
function(){
  return {{gtm.element}}.querySelector('input[placeholder=Телефон]').value

Теперь основной тег типа Пользовательское изображение, в нём указываем ссылку на созданное приложение с перечислением передаваемых параметров.
Должно получиться что-то вроде этого:

https://script.google.com/a/mgcom.ru/macros/s/AKfycbxiuTjkXbmV3NUB7/exec?time={{getTime}}&form={{Form ID}}&clientId={{getClientId}}&name={{getName}}&phone={{getPhone}}

Триггером идет Отправка формы.

Отправляем результат в Google Analytics

Осталось совсем немного - обработать результаты контактов.
Сценарий выглядит следующим образом:

клиент оставил заявку
gtm отправил её в spreadsheets
оператор связался с клиентом по оставленным контактам, по результатам проставил статус и заказал отправку данных в analytics
spreadsheet отправил данные с результатом заявки в analytics

Для обработки статуса отправки добавим к приложению немного кода:

// здесь мог быть номер именно вашего счетчика GA
var idGA = 'UA-xxxxxxxxx-x'

// стандартная функция обработки изменений onEdit не подходит, т.к. не позволяет делать fetch
function myOnEdit(e) {
  // проверяются внесенные изменения
  if (e.range.getColumn() == 8 && e.value == 'ДА') {
    e.range.setNote(e.range.getRow() + ' ' + e.range.getColumn())

    var sheet = SpreadsheetApp.getActiveSheet()
    var range = sheet.getRange(2, 1, 1, 8)
    var data = sheet
      .getRange(e.range.getRow(), 1, 1, e.range.getColumn())
      .getValues()
    var row = data[0]

    e.range.setNote(idGA + ' ' + row[3] + ' ' + row[6] + ' ' + row[1])

    // url для отправки: в категорию передается "Заявка", в действие статус
    var url =
      'http://www.google-analytics.com/collect?v=1&tid=' +
      idGA +
      '&cid=' +
      row[3] +
      '&t=event&ec=Заявка&ea=' +
      row[6] +
      '&z=' +
      row[1]

    try {
      var res = UrlFetchApp.fetch(url)
      // данные отправки логируются в примечание
      e.range.setNote('Событие отправлено ' + new Date() + ' url: ' + url)
    } catch (c) {
      e.range.setNote(c + ' res: ' + res)
    }
  }
}

Так как мы переопределили функцию прослушки редактирования, надо добавить её в триггеры, для этого идем в Правка - Триггеры текущего проекта.

Определение триггера прослушки ввода

При публикации необходимо выбирать новую версию, иначе изменения не применятся.

средний

инструмент

сквозная аналитика