Как интернет-маркетологу настроить автоматическую отчетность в реальном времени в Google Таблицах

Пошаговая инструкция.

Не все клиенты, маркетологи и аккаунт-менеджеры готовы заходить в интерфейсы Google Analytics или Roistat, чтобы увидеть картину дня: количество звонков, продаж, взаимодействий с чатом и других действий.

(Примечание редакции: Roistat — популярная на рынке система сквозной бизнес-аналитики. В апреле мы публиковали кейс строительной компании о том, как они уменьшили стоимость лида в 5 раз с помощью Roistat.)

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

Отчет настраивается 1 раз (и сразу можно создать шаблоны на любой период времени), он прост и бесплатен, но позволяет сделать сотрудничество клиента с агентством или маркетолога с руководством более открытым.

Единственное, надо учесть 2 факта.

Не все заказы могут попадать в Google Analytics с сайта ввиду погрешностей JS. Например, если заказов в интернет-магазине было 50 за день, то в Analytics процентов 5 могут «не долететь». Соответственно, эта погрешность может переноситься и в формируемый отчет.

Также, если вы выгружать данные по последнему клику (отображать канал, где в итоге пользователь совершил конверсию), данные по мультиканальности останутся за кадром.

Пошаговое создание таблицы

Шаг 1. Интеграция Google Analytics и Google Docs

Переходим по ссылке и создаем новую таблицу в Google Docs. Для использования Google Документов необходимо иметь аккаунт на Gmail.com. Устанавливаем дополнение для Google Таблиц под названием Google Analytics. Для этого в пункте меню «Дополнения» нажимаем на «Установить дополнения». Ищем нужное и жмем на кнопку «Бесплатно».

Во время установки сервис запросит доступ к аккаунту GA — разрешаем.

Шаг 2. Выгрузка данных из GA

Заходим в «Дополнения» → GA → Create a new report (Создать новый отчет). Справа появится окно для настройки выгрузки.

Вводим в поле Name название отчета, выбираем необходимые аккаунт (Account), ресурс (Property) и представление (View), затем определяем метрики (Metrics) и параметры (Dimensions), которые будем выгружать. Их можно выбрать из выпадающего списка.

Для примера выгрузим следующие параметры:

Metrics:

ga:sessions — сеансы
ga:transactions — количество транзакций
ga:transactionRevenue — доход

Dimentions:

ga:date — дата
ga:sourceMedium — источник/канал
ga:eventCategory — действие по событию

Нажимаем на кнопку «Сreate report» и автоматически создается новый лист Report Configuration, где можно смотреть и изменять настройки отчета. Здесь же задается период, за который нужна выгрузка.

Для формирования отчета необходимо в пункте меню «Дополнение» → GA нажать на «Run report» (Запустить отчет). Выгрузятся необходимые данные, но в не очень читаемом виде.

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

Шаг 3. Создание шаблона

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

Для примера спроектируем шаблон со следующими столбцами:

  • Трафик
  • Заказы
  • Конверсия в заказы
  • Доход
  • Звонки
  • Взаимодействие с чатом
  • Заявка через форму обратной связи

Чтобы отслеживать звонки, необходимо настроить динамический коллтрекинг и провести интеграцию сервиса с Google Analytics. Также нужно «подружить» сервис вашего онлайн-консультанта с GA.

Для отслеживания количества транзакций и дохода (для e-commerce) нужно провести настройку электронной коммерции на сайте. Все данные о заказах будут передаваться в GA. А для учета заявок с формы обратной связи достаточно повесить цель на кнопку «Отправить заявку»:

ga('send', 'event', 'category', 'action', 'label')

где 'category' 'action', 'label' необходимо задать самостоятельно.

Пример шаблона для проекта e-commerce:

Шаг 4. Настройка шаблона

Для данной процедуры необходимы знания в Excel, а точнее его функций. Минус автоматизированного отчета в том, что изначально его нужно сделать вручную. То есть в каждую клетку шаблона необходимо прописать соответствующую формулу.

С помощью функций «Индекс» и «Поискпоз» вытягиваем значения из листа с выгрузкой в лист с шаблоном. Рассмотрим вышеописанный пример.

Механика такова, что если пара «канал из столбца В + дата в ячейке В4» на листе шаблона совпадает с парой «канал + дата в листе выгрузки», то выводится соответствующее значение (в нашем случае заполняются клетки с трафиком, количеством заказов, доходом), если канал из столбика B + дата из клетки B4 + ярлык события из столбика А на листе шаблона совпадает с тем же трио на листе выгрузки, то данные, в нашем случае, подтягиваются в клетки «Звонки», «Чат», «Форма обратной связи».

Формула для заполнения полей «Трафик», «Заказ», «Доход»:

где 'Исходник' — лист с выгрузкой;

'Исходник'!C16:C1000 — диапазон в листе выгрузки, значения которого будут подставляться в клетку. Если в исходнике в столбце C выгружены данные по трафику, то данную формулу необходимо прописать в столбик D шаблона, описанного в примере;

B8&B4 — сравниваем дату и канал (в данном случае «Яндекс.Органику») в шаблоне с соответствующими столбиками «Исходника».

Выгрузку для событий нужно делать на другом листе, например, «Исходник2».

Формулы для полей, в которых будут значения целей и событий, выглядит так:

То есть для вывода значения в клетку М8 шаблона (чат с «органики» «Яндекса») мы ищем совпадения канал + дата + событие на листах выгрузки и шаблона и из выгрузки подтягиваем соответствующее значение.

Бывают ситуации, когда один и тот же канал в Google Analytics назван по-разному, например, yandex/cpc и yandex_direct/cpc, но тем не менее мы хотим объединить его в канал Директ. В таких случаях для каждого из них выводим значения и в новом столбце складываем, а лишние столбцы и строки скрываем.

Иногда данные выгружаются из GA в неверном формате. Например, «Доход» может выгрузиться в 4 различных вариантах:

0.0 — в случае, когда заказов не было.

5152.22 — в основном выгружается в текстовом формате. Такие значения необходимо преобразовывать для того, чтобы в дальнейшим производить над ними арифметические действия.

01.11.4876 — формат даты. Нам из этой даты необходимо вычленить последние 4 цифры, то есть год, так как это и есть сумма заказа.

01.11.1376 — формат даты, но excel считает, что сотворение мира началось с 1 января 1990 года, поэтому не может работать с датами до нее. Поэтому такие значения обрабатываются по-другому.

Формула для преобразования значений дохода в числа:

Конверсия — это отношение трафика на заказы, выраженная в процентах. В случае, когда заказов не было, выдается ошибка, так как на ноль делить нельзя. Чтобы этого избежать, применяем следующую формулу, и вместо индикатора ошибки выведется ноль.

=ЕСЛИ(E8=0;0;G8/E8*100)

Так как трафик может идти не только с указанных каналов, то введем поле «Другое», где будут отображаться показатели с прочих источников. Для того чтобы вручную не подсчитывать значения, создаем еще одну выгрузку, состоящую только из общих данных за сутки (без учета каналов) и подтянем их в ячейки строки «Итого». Цифры в строке «Другое» будут высчитываться путем вычитания из итогов суммарных значений остальных каналов.

=E25-E7-E12-E17-E21

Шаг 5. Настройка обновления отчета

Отчет можно обновлять вручную по кнопке Run report. Но лучше настроить автоматическое обновление данных. Для этого необходимо в пункте меню «Дополнения» → GA → Schedule reports (Расписание отчетов) поставить галочку Enable reports to run automatically (Разрешить автоматическое обновление отчетов), выбрать периодичность обновления и сохранить настройки.

Дополнительно: автоматическая визуализация данных

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

В появившемся окне выбрать «Встроить», скопировать код и вставить его в поле html созданного в конструкторе сайта.

Обновляем, сохраняем и диаграмма появляется на сайте.

Строим дашборд с интересующими нас графиками по имеющимся показателям. Графики будут обновляться одновременно с обновлением отчета.

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

Имея ссылку, заинтересованные люди получат доступ к самым актуальным real-time данным.

Мнение редакции может не совпадать с мнением автора. Если у вас есть, что дополнить — будем рады вашим комментариям. Если вы хотите написать статью с вашей точкой зрения — прочитайте правила публикации на Cossa.


Комментарии:

- 0 +
zap800 #
03.06.2016 16:31
Спасибо за статью, очень полезно, можно пример оформленного шаблона в Excel выложить?!
Ответить
- 0 +
Ника Свиязова #
07.06.2016 11:27
Спасибо. Инструкция написана максимально подробно для самостоятельного создания собственного шаблона.
Ответить
- 0 +
Алексей Божин #
03.06.2016 18:45
Молодцы, крутая штука.
Ответить
- 0 +
Кинг Мото #
13.06.2016 03:11
класненько спс )
Ответить
- 0 +
Stas Usachyov #
19.06.2016 18:29
Спасибо за статью.Я правильно понял, что на каждую дату нужно вручную формулы в ячейки забивать? И почему-то не совпадают данные в Google Analytics и данные в выгрузке. Сравнивал по трафику и транзакциям. В выгрузке всегда меньше показывает.
Ответить
- 0 +
Ника Свиязова #
20.06.2016 07:58
Да, вы правильно поняли. Должно все совпасть. А вы какие значения выгружаете в одной выгрузке?
Ответить
- 0 +
Stas Usachyov #
20.06.2016 10:59
Выгружаю это: ga:sessions, ga:transactions, ga:transactionRevenue, ga:date, ga:sourceMedium, ga:eventCategory.  


Насчет ручного забивания формул в каждую ячейку для каждой даты. Хорошо, если мне нужен отчет за день, один раз забил формулы и просто меняй дату. Но если я хочу построить отчет на каждый день месяца, чтобы отследить динамику и построить график — это замучаешься формулы забивать. Проще уже в GA зайти. Или я не прав?
Ответить
- 0 +
Ника Свиязова #
20.06.2016 13:10
Стас, рекомендую в одной выгрузке выгружать "сеансы,транзакции,доход,дата,источник", а в другой "сеансы,источник,дата,событие". И в шаблон уже подтягивать цифры из этих выгрузок. У вас получается меньше, чем в GA, потому что данные фильтруются по событиям.

Действительно проще зайти в GA, не спорю, но на практике часто бывает так, что клиенты не заходят в GA, но при этом просят подробные и наглядные отчеты по сайту. Данная таблица как раз выполняет такую функцию. Один раз потратив несколько часов и сделав табличку, вы сможете применить этот шаблон на несколько месяцев вперед, просто поменяв даты, а так же применить к аналогичным проектам, поменяв id представления в листе с конфигурациями.
Ответить
- +2 +
Роман Романчук #
01.08.2016 17:50
Коллеги, конечно круто сделано. Но прошлый век же, все эти таблицы, формулы..
Даешь Power BI и Data Studio!
Ответить
- 0 +
Kanat Sahanov #
07.09.2016 21:37
Что-то я не понял. Начинали с Google Таблиц, а потом резко "Для данной процедуры необходимы знания в Excel". Здесь опечатка или что? И еще вопрос. Как вы решили вопрос с производительностью Google Таблиц? Я постоянно сталкиваюсь с проблемой того, что когда много данных и формул, таблица начинает "виснуть" (минусы онлайна).
Ответить
- 0 +
Stanislav Kirov #
08.09.2016 15:57
Дополню еще о таких подводных камнях, которые надо учесть:
Если в отчёте хотите показать еще и стоимость лида с канала — то надо выгружать ещё данные о расходах. Не все расходы можно первоначально загрузить в GA в автоматическом режиме, и не все расходы будут загружаться в онлайн режиме. ( в лучшем случае площадки отдают данные раз в сутки и то не в одно время).
Если в отчёте хотите показать еще и количество звонков, то первоначально надо их отправлять в GA (все системы колтрекинга умеют) - учтите что будут расхождения по количеству звонков в GA и аккаунте колтрекинга. В таких сферах, как недвижимость и авто - где звонков в принципе не очень много - потеря одного звонка в день может уже существенно "увеличить" вашу стоимость лида в отчёте.

Нельзя сказать, что отчёт настраивается один раз. Нужно постоянно его админить, особенно если нужны детальные данные по кампаниям. Например, добавились новые РК, добавились новые формы на сайте (читай цели), поменялись UTM-метки -  это всё надо актуализировать и в настройках отчёта.
Ответить
- 0 +
Алексей Гаранин #
03.03.2017 12:24
Инструкция прекрасна, мои комплименты. Мы выгрузим в сводные таблицы с конверсиями еще и звонки с сотовых  через opencalls.me, станет просто ручной CRM со сквозной аналитикой )
Ответить
Ответить?

Самые интересные статьи, обзоры и размышления —
в рассылке!

Email *


Подпишись!


Вход на cossa.ru

Уже есть аккаунт?
Выбирай любой вариант входа:
Facebook Twitter Vkontakte

Используйте свой аккаунт в социальной сети Facebook или Twitter, чтобы пользоваться сайтом

Не забудьте написать email на странице своего профиля для управления рассылкой