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

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

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

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

Эффективная и выгодная реклама с сервисом от МегаФона

Широкий выбор рекламных каналов, более 100 параметров по интересам, подробная аналитика и другие возможности уже ждут в Личном кабинете. А еще кешбэк 100% за запуск рекламы в первый месяц и еще 10% — каждый месяц.

Узнать больше >>

Реклама. ПАО «МегаФон». ИНН 7812014560. ОГРН 1027809169585. ERID LjN8K1P7y.

Отчет настраивается 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.