Дашборд в Google Таблицах для анализа эффективности контекстной рекламы: пошаговая инструкция и шаблон

Директор по маркетингу Ringostat Александр Киселёв делится инструментом, который помогает оценить, насколько успешно продвижение. Это можно сделать по факту наличия сделки с определенным лидом из контекстной рекламы, зафиксированному в CRM. Именно так делали в Ringostat до появления сквозной аналитики.

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

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

Именно поэтому я решил поделиться опытом, который наработал в Ringostat. Пока у нас не было сквозной, при оценке эффективности рекламы мы опирались на данные о сделках с конкретными лидами из контекстной рекламы. А если смотреть на стадии, на которых находятся сделки, можно понять, какого качества лид. 

Чтобы метод работал, нужны:

• подключенная и стабильно работающая CRM;
• Google Ads;
• коллтрекинг, который связан с CRM и передает в последнюю UTM-метки по лидам, которые звонили.

Дашборд, который я опишу в статье, выглядит так:

Вот общий принцип работы дашборда:

• из CRM берутся данные о лидах и рекламных источниках, которые их привели;
• эта информация сопоставляется с той, что зафиксирована в Google Ads обо всех онлайн-конверсиях;
• отображается результат: какое количество лидов дает реклама и можно ли считать их качественными.

Так маркетолог или PPC-специалист четко видит, как трафик из рекламы конвертируется или не конвертируется в продажи. Если трафика много, а продаж мало, придется разобраться, как так вышло. 

Для Ringostat особенно важно понимать, насколько успешно продвижение прямо сейчас, потому что цикл сделки в компании длинный. Иногда для завершения продажи нужно два-три месяца. И нельзя столько ждать, чтобы понять прямо сейчас, эффективна ли реклама. Это может привести к тому, что долгое время бюджет на контекст будет частично расходоваться впустую.

А теперь вы можете скачать шаблон с примером выгрузки. На его основе я буду рассказывать, как анализировать эффективность рекламы. Цифры в нем условные и приведены просто для примера.

Пару слов о том, почему я советую создавать дашборд именно в Google Таблицах:

• это простой, бесплатный и востребованный инструмент, его используют практически все маркетологи;
• работа Google Таблиц описана в многочисленных мануалах, ее обсуждают в тематических чатах, группах, на форумах;
• в дашборде будут только те данные, которые вам точно нужны — это удобнее, чем искать нужные цифры в отчетах Google Analytics и Google Ads;
• уровни доступа можно настроить так, как нужно владельцу: кто-то сможет только смотреть, а кто-то и редактировать. 

Такой дашборд строится за шесть шагов. О каждом из них подробно расскажу ниже.

Шаг 1. Написать скрипт, который будет регулярно выгружать данные из CRM в Google Таблицы

Начать надо с создания пустой таблицы, сделать это можно по ссылке. В созданной таблице откройте вкладку «Инструменты» и перейдите в пункт «Редактор скриптов». Здесь-то вы и будете писать свой собственный скрипт.

Шаблон, который есть по ссылке выше, пустой — то есть, без скриптов. Потому нет что универсального кода, который подходит для любого бизнеса и любой CRM. Скрипт, о котором я говорю здесь, берет данные из нашей CRM, это  Pipedrive. А вообще можно настроить выгрузку из любой CRM, главное, чтобы у нее был API. Делается это двумя способами:

• обратиться в техподдержку вашей CRM или найти ее API-документацию, чтобы написать свой скрипт;
• поискать в интернете написанные кем-то скрипты и изменить их под себя.

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

ВАЖНО: чтобы все работало корректно, надо каждый раз перед запуском скрипта стирать то, что, там уже есть. Только потом можно вставлять новые данные. Очистить лист поможет такой фрагмент кода:

На скрине — часть нашего скрипта из выгрузки:

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

• source of a deal — источник сделки, например, обращение по телефону или заполнение формы на сайте;
• utm source — источник лида;
• utm medium — канал, откуда пришел лид;
• utm campaign — кампания;
• utm term — ключевое слово, которое привело лида;
• ID сделки — уникальный номер сделки из CRM;
• год, месяц и день, когда данные появились;
• время создания;
• статус сделки — «открытая», «проигранная» или «выигранная»;
• причина проигранной сделки, о том, какими они бывают, расскажу дальше;
• client ID — уникальный цифровой номер от Google Analytics, он появляется в CRM по каждому лиду;
• manager — имя или ник сотрудника, ответственного за сделку;
• value — сумма, на которую клиент планирует купить, обычно менеджеры вписывают ее предварительно, а уже после закрытия сделки заменяют фактической;
• stage — этап воронки продаж, обозначается в числовом формате, первичному обращению присваивается номер один;
• tag — тег, их иногда присваивают менеджеры, чтобы упростить себе работу;
• pipeline — номер воронки, стоит использовать, если у вас их несколько, например, те, кто купил машину, могут обращаться за сервисным обслуживанием к официальному дилеру, если у него есть СТО.

Вот этот блок скрипта задает, какие данные выгружаются в столбцы:

В таблицу можно добавить и новые поля. Для этого надо:

• решить, какие названия будут у новых столбцов, и записать их;
• для второй части скрипта — указать ID поля, которое должно выгружаться.

На скрине часть полей — стандартные. К примеру, data.owner-name. Но есть и кастомные, их названия я скрыл. Наша CRM позволяет создавать нужные поля самостоятельно. Они выглядят как набор числовых и цифровых символов. Один из примеров кастомного поля — тег сделки. Когда надо поменять данные, следует просто ввести в поле ID сделки. О том, как его узнать, написано в документации CRM, еще можно задать этот вопрос техподдержке.

Полученные из CRM и преобразованные данные надо переформатировать в двумерный массив и при помощи такого цикла в конструкции разместить в таблице:

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

Шаг 2. Создать лист, где будут фильтроваться данные

Первый шаг мы закончили на том, что создали лист таблицы с необработанными данными. В шаблоне-примере его название DB — сокращенно от data base. Выглядит приблизительно так:

С правой стороны есть и другие столбцы. Как они называются, написано выше. 

ВАЖНО: на листе с выгрузкой нельзя ничего менять. Вместо этого создайте еще один лист. Новые данные надо передавать на него. В примере это лист под названием DB Filter. Из самого имени ясно, что тут информацию можно фильтровать.

Чтобы передавать данные, нужна ARRAYFORMULA. С ее помощью можно отобразить данные, посчитанные при помощи формулы массива, в нескольких строках и столбцах. Формула берет только те значения, которые указаны на нужном листе в заданном диапазоне.

Если на листе DB Filter нажать на название столбца, станет ясно, из какого диапазона ARRAYFORMULA берет данные на листе DB. В моем примере — начиная со столбца А, и заканчивая столбцом Т.

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

ВАЖНО: в начале всех формул надо подставлять ARRAYFORMULA. Если этого не сделать, все будет считаться только для одной ячейки, а надо чтобы условие выполнялось для всего столбца. Это удобно тем, что изменения, которые вы вносите в первую строку, применяются ко всему столбцу.

Шаг 3. Вписать формулы, чтобы квалифицировать лидов

Сейчас данные из CRM уже есть на втором листе шаблона, можно двигаться дальше. Пора записать формулы, которые применяются для квалификации лидов. Для этого нужны данные из столбцов, начиная с W, а заканчивая AG на листе DB Filter. В них отражаются все стадии, которые проходят лиды, которые пришли из маркетинговых каналов. Их еще называют inbound-лиды. Также есть данные о тех, кто пришел от агентств-партнеров Ringostat.

Ваше количество столбцов может быть больше или меньше, ведь ваш бизнес отличается от нашего. Какие обозначения есть у нас:

• MQL — пока еще не реальный, а только потенциальный клиент, которого привели каналы маркетинга;
• SQL — лид, с которым уже пообщались сотрудники отдела продаж и сочли его качественным, еще таких лидов называют квалифицированными;
• won — сделка, которую заключили, еще ее называют выигранной;
• спам;
• дубль сделки — такие поля надо периодически удалять, оставить можно в том случае, если менеджер сам назначил такой статус, например, имеющемуся клиенту, который в этот раз звонил с другого номера. 

Чтобы данные из столбцов, о которых я сказал выше, подтягивались, тоже нужна ARRAYFORMULA. А именно такой код:

if($V2:$V>1,0,1)

Если в колонке V, которая касается воронки, указана цифра один, то конверсия состоялась. Это обусловлено тем, что данные могут выгружаться из разных воронок. Например, у вас есть одна воронка с данными о тех, кто покупает что-то впервые. А еще есть другая, в которой фиксируются те, кто обращается повторно, по поводу сервисных вопросов. Лидов мы будем анализировать только из одной воронки. 

Столбцы с данными о качестве лидов могут содержать только одно из значений: либо 1, либо 0.

Следующий пункт — подсчет качественных лидов. Я строю дашборд на примере Ringostat. Поэтому представим, что шаги к закрытию сделки, они же этапы, у нас такие:

• первичное входящее обращение;
• переданное в работу обращение;
• консультация;
• составленное и отправленное клиенту коммерческое предложение;
• подготовленные для закрытия сделки документы;
• получение оплаты от клиента.

Лиды, для которых отдел продаж подготовил коммерческое предложение, явно качественные. Если бы они были не такими, то до этого этапа просто бы не дошли. А вот как оценить качество лидов, которые пока что находятся на стадиях консультации и более ранних? Маркетологи должны как можно быстрее понимать, каких людей приводит реклама и что делать с кампаниями дальше. 

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

1. Спам и лиды плохого качества. Такие люди обычно «отваливаются» на ранних стадиях, с первой по третью. Пример тому —  звонки тех, кто сами хотели что-то продать или ошиблись номером. Если лиду не получилось дозвониться после пяти, максимум семи попыток, он тоже явно был нецелевым.
2. Качественные лиды, которых не вышло довести до покупки. Такое бывает, когда человек заинтересован в товаре или услуге, но не купил их. Например, получил более выгодное предложение от конкурентов. 
3. Лиды, которые получили коммерческое предложение. Сделки, которые дошли до этого этапа, как правило, закрываются. Из CRM подтягивается сумма, на которую купили. Она станет одной из составляющих подсчета ROI.

Движемся дальше и вводим формулу, которая «обратит внимание» на два условия. Когда в столбце Т (этап сделки) мы видим цифру три и более, то формула выведет все лиды, которые перешли на четвертый этап и дальше. Значит, эти люди получили коммерческое предложение, идет подготовка документов или зафиксировано поступление оплаты.

if($T$2:$T>3,1,0)))))))

Кроме того, формула сосчитает лиды, которые не продвинулись дальше третьего этапа. Это как раз те проигранные сделки, которые не зависели от маркетинга. Такое случается, когда клиент предпочел конкурента или по личным причинам поставил вопрос покупки на паузу. 

if((($T$2:$T>3) + (($T$2:$T<4)*(ARRAYFORMULA(REGEXMATCH($P$2:$P, «Бизнес закрыт|Замороженная сделка|Клиент выбрал конкурентов|Не смогли решить вопрос с бухгалтерией|Не устраивает цена|Несезон|Отказался заполнять бриф«))=true))),1,0)))))

Когда мы анализируем лиды в Ringostat, то смотрим также на количество тех, кто заказал Trial. Это тестовый период, он длится 14 дней и пользователь за него не платит. Довольно часто те, кто протестировал продукт, покупают его. У нас Trial проходит под номером шесть. Так что, формула сосчитает тех, кто продвинулся дальше пятой стадии.

if($T$2:$T>5,1,0)))))))

Подобным образом мы анализируем и выигранные сделки. Из CRM забирается статус сделки, он обозначен в поле O. В нем могут быть такие значения:

• открыта;
• выиграна;
• проиграна.

Как только сделку выигрывают, подтягивается сумма оплаты по ней. То есть, если в целевом столбце появляется статус won (выиграна), то формула обращается к столбцу S («ценность»). Полученное оттуда значение идет в столбец. 

if($O$2:$O=»won«,$S$2:$S,0)))))))

В столбце Value не всегда отмечены фактические суммы по выигранным сделкам. Как я говорил выше, менеджеры иногда записывают предполагаемую сумму оплаты от клиента. В Ringostat ее вносят в CRM после того, как отправили клиенту коммерческое предложение.

ВАЖНО: этот или подобный дашборд позволяет понимать, итоговую или промежуточную статистику вы видите. То есть, если сегодня последний день месяца, но я вижу три незакрытые сделки, то данные могут быть не финальными. Ведь сделки могут и не закрыться.

Шаг 4. Создать дашборд, в котором есть селектор для переключения данных

Выгрузка — большой массив цифр. И быстро сделать выводы, просто глядя на него, сложно. Поэтому лучше сделать лист, в котором информация представлена предельно понятно:

Здесь мы видим:

• селектор, при помощи которого переключаются годы, находится в верхнем левом углу;
• этапы воронки, которые описывали выше: SQL, won и другие.

Сюда же вписываем формулу, которая считает, сколько лидов с определенным статусом было в каком месяце и в каком году.

Можно задать, чтобы формула высчитала количество лидов с такими вводными: месяц 1, год 2021, а в столбце АВ («Выиграно») тоже указана единица.

=COUNTIFS(
DB_filter!$AB:$AB,1,
DB_filter!$L:$L,$A$1,
DB_filter!$M:$M,C$1
)

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

Шаг 5. Сделать лист, в котором фиксируются данные по конкретным кампаниям

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

Слева вы можете выбрать, какую воронку хотите анализировать. Если, конечно, их у вас больше одной.

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

Этот лист дает возможность быстро сделать выводы о том, какие кампании и ключи наиболее эффективны. Кроме того, можно быстро понять, не только как поменялось количество конверсий, но и как изменилось качество лидов. И связать это с изменениями в рекламе.

Шаг 6. Объединить данные из CRM и из Google Ads

Сейчас мы свяжем выгрузку с данными, которые уже есть в Google Analytics. Выгрузить информацию из Google Ads поможет плагин с таким же названием. Перейдите во вкладку «Дополнения», а затем выберите в выпадающем меню пункт «Установить дополнение». После этого нажмите на дополнение Google Ads. Теперь вы можете экспортировать данные из рекламного кабинета. 

Для этого снова нажмите на вкладку «Дополнения», выделите в списке доступных дополнений Google Ads и кликните на пункт “Create new report”. Отчет получится только по кампаниям. А в столбцах появятся такие данные: статус и название кампании, затраты на нее, используемая стратегия назначения ставок. 

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

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

После этого в дашборд подтягивается сумма, фактически истраченная на кампанию. Эту возможность дает выгрузка из Google Ads. А затем формула считает, насколько плановые данные отличаются от фактических. 

Заметили, что в какой-то из кампаний есть излишек бюджета? Прикиньте, хватит ли денег до окончания месяца. 

Есть в шаблоне и индивидуальные поля. Среди них — соотношение показов и кликов. Google Ads дает информацию, что наша реклама из 100% была по какому-то ключевому слову показана 24,6% раз. А процент кликов демонстрирует, что из всех показов нашей рекламы кликнули на нее Х%.

Фиксируется в дашборде и усредненный CTR кампании. Он рассчитывается путем деления числа кликов на число показов. CPC мы берем из выгрузки Google Ads. Она же показывает CR, количество и стоимость конверсий.

Обращу ваше внимание на еще один момент. Например, в какой-то кампании отмечено 18 конверсий, на это потрачено 710 гривен. CR равен 4,64%. По названию кампании мы смотрим в CRM, сколько конверсий было по факту. То есть, сколько людей позвонили, заполнили форму, написали в чат и так далее. Замечаем, что пяти конверсий не хватает. Надо найти причину, почему они пропали. Может оказаться так, что эти люди — уже существующие клиенты. И они хотели, например, связаться с техподдержкой.

После этого изучаем воронку. Получается, что по 18 конверсиям, которые зафиксированы, сотрудники отдела продаж подготовили лишь одно коммерческое предложение. Так можно понять, что данные из Google и реальность отличаются. MQL нам обошелся в 983 гривны, SQL стоил уже 1598 гривен. Таким путем мы придем и к цене клиента, который что-то купил. И увидим, как «переворачиваются» данные, если считать их относительно категории лидов.

Показатели, на основе которых делаются расчеты, есть в этом же дашборде. Мы видим, что на протяжении месяца из рекламных кампаний есть три SQL. Потрачено 11 000 гривен. Формула автоматически считает по этим данным, во сколько обошелся SQL. 

Подобным образом считается и конверсия MQL и SQL относительно кликов. У нас есть 343 клика, а MQL — 10. Выходит, что конверсия равна 3%.

Итоги: 6 шагов для создания дашборда

1. Готовим скрипт, который сможет выгрузить в Google Таблицы данные, которые есть в CRM.
2. Делаем лист, на котором будем фильтровать данные из выгрузки.
3. Проводим квалификацию лидов в зависимости от стадии, на которой находится сделка. Не забываем, что есть сделки, которые проиграны по причинам на стороне покупателя. И лиды были вполне качественные.
4. Делаем дашборд с визуализацией. Данные по лидам и воронкам в нем будут подтягиваться по годам и месяцам.
5. Создаем лист с данными по кампаниям. Здесь мы оценим, что изменилось в рекламе за последнее время. Будем смотреть не только на количество конверсий, но и на качество лидов, полученных из рекламы. 
6. Объединяем данные из CRM с информацией из Google Ads. Это даст понимание того, сколько тратится на лидов и покупателей.

Такой алгоритм позволяет понимать, есть ли эффект от рекламы, которую вы запустили. Если кампаний у вас много, можно работать с ключевыми словами, а не с кампаниями.