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

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

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

1. CRM.

2. Google Ads.

3. Коллтрекинг, интегрированный с CRM. Без него у вас не будет UTM-меток по лидам, которые звонили.

Коллтрекинг передает источник лида

Так выглядит один из листов дашборда:

Если вкратце, то работает дашборд так:

• забирает из CRM данные о лидах с их рекламными источниками;

• сопоставляет их с информацией Google Ads обо всех онлайн-конверсиях;

• отображает, сколько лидов и какого качества поступает из рекламы.

Так маркетолог или PPC-специалист видит, как трафик конвертируется в продажи и приходит ли из рекламы целевая аудитория. Например, мы использовали такой способ и сами — еще до того, как в Ringostat появилась собственная сквозная аналитика. Он удобен еще и тем, что у нас длинный цикл продаж: иногда сделки закрываются по два—три месяца. А по количеству и качеству лидов из контекста можно судить об успешности кампаний «здесь и сейчас».

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

Почему лучше создавать дашборд в Google Таблицах? Причин — несколько:

• это простой и популярный инструмент, которым владеет большинство маркетологов;

• по Google Таблицам есть множество мануалов и тематических чатов и групп;

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

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

Расскажем по шагам, как построить такой дашборд:

1. Создать скрипт для выгрузки данных из CRM в Google Таблицы
2. Создать лист с фильтрацией данных
3. Добавить формулы для квалификации лидов
4. Создать дашборд с селектором для данных
5. Создать лист с данными по отдельным кампаниям
6. Связать данные из CRM с информацией из Google Ads

Шаг 1. Создать скрипт для выгрузки данных из CRM в Google Таблицы

Создайте таблицу — sheet.new. После этого нужно будет создать скрипт во вкладке «Инструменты» — «Редактор скриптов»:

В шаблоне, ссылку на который я дал выше, скрипта нет. Потому что не бывает универсального кода, который мог бы выгружать данные из любой CRM-системы. Например, у нас есть собственный скрипт, который подтягивает данные из Pipedrive. Вы можете настроить выгрузку из любой CRM, у которой есть API. Здесь два пути:

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

• найти в сети готовые скрипты и адаптировать их под свои нужды.

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

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

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

Этот фрагмент кода задает названия столбцов в шапке документа — от первого до последнего:

• 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 Pipedrive, где есть возможность создавать собственные поля. В этом случае они выглядят как набор числовых и цифровых символов. Например, таким кастомным полем может быть тег сделки.

Если вам нужно будет менять данные, просто вносите сюда ID сделки. Как его узнать? В каждой CRM это устроено по-разному, поэтому опять-таки обратитесь к документации системы или в техподдержку.

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

Итак, скрипт написан, теперь нужно задать для него расписание. Код должен отрабатывать ежедневно и выгружать данные за предыдущий день. Советую задать время 4:00–5:00, когда никого еще нет на работе — команда с утра будет получать актуальную информацию.

Шаг 2. Создать лист с фильтрацией данных

В итоге мы получили лист с «сырыми» данными. В нашем шаблоне он называется DB — data base. Условно он может выглядеть так:

Справа еще есть столбцы, названия которых я привел выше.

Важно! Ничего не меняйте на листе с выгрузкой. Создайте новый лист и передайте данные туда. В нашем шаблоне это DB Filter. Лист называется так, потому что тут мы уже можем применять фильтры к информации.

Как передать данные? Это легко сделать с помощью ARRAYFORMULA. Она отображает значения, полученные с помощью формулы массива, в нескольких строках и столбцах. Или, проще говоря, забирает все значения, которые есть на нужном листе в указанном диапазоне.

Например, в шаблоне на листе DB Filter можно кликнуть на название столбца и посмотреть, из какого диапазона ARRAYFORMULA тянет данные с листа DB. С колонки A по колонку T:

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

Шаг 3. Добавить формулы для квалификации лидов

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

У вас ситуация будет совсем другой. Тут все зависит от специфики бизнеса. Например, столбцов может быть гораздо меньше:

• MQL — потенциальный клиент, который пришел из каналов, за которые отвечает маркетинг;

• SQL — лид, которого квалифицировал отдел продаж, посчитав качественным;

• won — выигранная сделка;

• спам;

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

За сбор данных из описанных выше колонок тоже отвечает ARRAYFORMULA. В ней есть такой фрагмент:

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

Логика следующая: если в колонке V, которая отвечает за воронку, есть единица — значит, мы считаем, что конверсия состоялась. Почему так? Вы можете выгружать данные из разных воронок, а вам нужно анализировать лиды только из одной. Например, отвечающей за продажи, а не за обращения в техподдержку или сервис.

Обратите внимание — во всех столбцах, отвечающих за качество лида, возвращается только одно из двух значений: или 0, или 1.

После этого нужно посчитать качественных лидов. Чтобы не отвлекать вас «внутренней кухней» Ringostat, представим, что этапы у нас такие:

1. Входящее обращение.

2. Обращение в работе.

3. Консультация.

4. Отправлено коммерческое предложение.

5. Подготовка документов.

6. Оплата.

Понятно, что если лид дошел до стадии коммерческого предложения — он точно качественный и попал в воронку неслучайно. Но что можно сказать о лидах, которые находятся на более ранних этапах? Как понять, «плохие» они или нет, чтобы маркетинг мог делать выводы об успешности кампаний и принимать на их основе решения?

Делим причины проигрыша на три вида:

1. Некачественные лиды и спам. Они проигрываются на стадиях от первой до третьей. Например, человек ошибся номером или сам хочет что-то продать — скажем, доставку воды. Сюда же относятся лиды, которым нельзя дозвониться за пять–семь раз.

2. Нормальные лиды, которых не получилось «дожать». То есть они не стали покупателями по причинам, не зависящим от маркетинга. Например, выбрали конкурентов.

3. Лид, который дошел до стадии составления коммерческого предложения. Когда сделка по такому лиду закроется, мы подтянем ее сумму. На ее основе можно будет посчитать 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 — бесплатный двухнедельный тестовый период, на который подключается пользователь. Потому что после этого гораздо выше шанс, что сделка закроется. В нашем случае Trial — это шестой этап, поэтому формула посчитает всех, кто находится на стадии выше 5.

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

Аналогично с выигранными сделками. Мы «тянем» из CRM поле O — статус сделки. Туда подтягивается один из трех вариантов:

• проиграна;

• выиграна;

• открыта.

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

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

Почему так? Потому что в колонке Value могут быть и суммы по сделкам, которые еще не выиграны. Так как менеджер по продажам может заранее понимать, на какую сумму планируется продажа, он вносит ее в CRM. У нас сотрудники вносят эту информацию в CRM сразу после составления коммерческого предложения.

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

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

Шаг 4. Создать дашборд с селектором для данных

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

Тут все достаточно просто:

• в левом верхнем углу селектор, который переключает года;

• переносим сюда этапы нашей воронки — MQL, SQL и так далее, их можно поменять под ваш бизнес;

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

Например, формула считает, сколько есть лидов, у которых месяц 1, год 2021 и в колонке AB («Выиграно») стоит единица.

=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% раз. Процент кликов говорит о следующем — среди 100% раз, когда Google показал наше объявление, мы получили N% от всех кликов, которые были в данный момент.

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

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

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

Все показатели берутся из этого же дашборда. Допустим, у нас три SQL из кампании за месяц, а траты — 11 тысяч гривен. Формула делит 11 тысяч на три и показывает стоимость SQL. Аналогично считаем конверсию MQL и SQL применительно к кликам. Допустим, есть 343 клика и 10 MQL. Делим одно на другое и получаем конверсию в 3%.

Резюме: основные шаги для построения дашборда по контексту

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

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

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

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

5. Делаем лист под данные по кампаниям. Он позволит оценить, что изменилось в рекламе за последнее время — не просто в количестве конверсий, а в качестве лидов, которые она вам дает.

6. Связываем данные из CRM с информацией из Google Ads, чтобы видеть, сколько денег мы по факту тратим на качественных лидов и покупателей.

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

Источник статьи