Как сделать воронку продаж/конверсии в Google Таблицах для отчета/дашборда

Воронка (продаж, конверсии) — наглядный инструмент, чтобы понять, какая часть людей доходит до конечной цели, и на каком шаге больше всего «отваливаются». Конечно, такая визуализация есть в интерфейсах Яндекс.Метрики и Google Analytics, но ее кастомизация ограничена. Гораздо удобнее уметь делать воронки самостоятельно, например, в Google Таблицах.

Читайте также: Большая посещаемость ≠ много конверсий и лидов: как проанализировать качество трафика на сайт

Загрузка отчета

Сделаем на примере табличного отчета Яндекс.Метрики, где в группировках — источники трафика, а в качестве метрик выбраны визиты и достижения нескольких шагов составной цели. (Но по тому же принципу можно будет обрабатывать любые другие данные.)

Пример отчета Яндекс.Метрики по источникам трафика с визитами и достижениями целей.

Чтобы перенести информацию в Google Таблицы, придется сначала выгрузить ее в Excel — кнопка для скачивания находится между таймером и кнопкой «Сохранить отчет». Во всплывающем окне нужно выбрать «Данные таблицы» и XLSX, а после импортировать полученный файл или просто скопировать-вставить таблицу.

Так уже выглядит отчет, выгруженный из Яндекс.Метрики и загруженный в Google Spreadsheets

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

К сожалению, нет прямой интеграции между Google Таблицами и Яндекс.Метрикой, приходится работать руками. Но для работы с GA есть очень удобное расширение — Google Analytics Spreadsheet Add-on. С ним можно строить и актуализировать отчеты прямо в гугл-таблицах.

Вариант без формул — простая диаграмма

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

Для этого достаточно выделить нужный диапазон (допустим, A7:E7, если хочется посмотреть «Итого и средние»), выбрать в меню «Вставка», а затем кликнуть на «Диаграмма». Далее в настройках как минимум нужно выбрать тип диаграммы «Линейчатая» (если по умолчанию открылась другая), затем поменять «Ярлыки» на «Строки/столбцы», чтобы убрать разноцветное выделение. И что получилось это по сути это половинка воронки, которая позволяет по похожему принципу оценивать конверсионность.

Останется только настроить: поменять название, в стиле выбрать «Развернуть», чтобы убрать легенду и увеличить масштаб и т. п.

Создание и настройка линейчатой диаграммы в Google Spreadsheets, которая может заменить классическую воронку

И, конечно, гораздо удобнее создать ее не поверх имеющейся таблицы, а на отдельном листе, просто ссылаясь на эти данные. Плюс рядом с полями диаграммы — уже в самих ячейках — можно задать формулы для расчета показателей конверсии. «=C7/B7», «=D7/B7» и так далее. А чтобы десятичные дроби превратились в привычные проценты, нужно выделить соответствующие ячейки, затем кликнуть на «Формат → Числа → Процент».

Читайте также: 25 инструментов для анализа и визуализации данных

Вариант сложнее, но интереснее — спарклайны

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

Функция SPARKLINE принимает два аргумента:

1. Диапазон или массив данных. То есть можно передать как «A7:E7», так и конкретные значения, например, «{100;200;1000}».

Массив — это как бы виртуальная таблица. В данном случае у нее один столбец: точка с запятой ‘;’ показывает, что значения располагаются сверху вниз, вертикально. А если обратный слеш ‘’, то наоборот — значения идут по горизонтали, в строку. То есть массивы могут быть и многомерными вроде {{1;2}{3;4}}, где в первой строке идут 1 и 3, во второй — 2 и 4.

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

Самих опций довольно много. Ключевой параметр charttype устанавливает тип диаграммы: line — для графика (стоит по умолчанию, если не задавать этот параметр), bar — для гистограммы, column — для столбчатой диаграммы, winloss — для столбчатой диаграммы с отрицательными и положительными результатами. (В данном случае нужен именно bar.)

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

• max — устанавливает максимальное значение по горизонтальной оси;
• color1 — задает первый цвет столбцов;
• color2 — соответственно, второй;
• empty — задает значения пустых ячеек (возможные варианты: zero – ноль, ignore – игнорировать);
• nan — устанавливает значения для ячеек с нечисловыми данными (возможные варианты: convert – конвертировать, ignore – игнорировать);
• rtl — указывает, если нужно отображать данные на диаграмме справа налево (true – да, false – нет).

Для построения воронки хватит параметров color1 и color2. Максимальное значение тоже нужно, но это можно сделать с помощью функции max, а не параметра, или еще проще — через ссылку на соответствующую ячейку, так как в данном примере всегда известно, где находится то самое максимальное значение.

Итак, для визуализации — вышеуказанного примера отчета из Яндекс.Метрики — понадобятся четыре спарклайна: по одному на каждый шаг. (Для удобства лучше создать их на соседнем листе, просто ссылаясь на данные первого листа, где располагается выгруженный отчет.)

Для первой диаграммы можно написать такую формулу: =SPARKLINE({‘Лист1′!B7;’Лист1′!B7;’Лист1’!B7};{«charttype» «bar»; «color1» «white»; «color2» «blue»}), а для последующих второе значение будет просто меняться с B7 на C7, затем D7 и E7. (То есть в четвертом, например, так: =SPARKLINE({‘Лист1′!B7;’Лист1′!E7;’Лист1’!B7};{«charttype» «bar»; «color1» «white»; «color2» «blue»})).

Теперь подробнее:

• Первый аргумент — массив из трех чисел: первое и последнее — самое большое значение в диапазоне B7:E7, посередине — значение, которое нужно визуализировать на данном шаге. На первом шаге получается, что все они совпадают.
• Указать просто одно значение в каждом спарклайне не получится — тогда они просто полностью закрасят ячейки. Суть в том, чтобы визуализировать разницу между цифрами на каждом шаге, отталкиваясь от какого-то эталонного, наибольшего значения. А два крайних значения повторяются для того, чтобы нарисовать ту самую воронку.
• Второй аргумент — тоже массив, но с опциями, набором параметров и значений. ‘charttype’ указывает, что нужна именно гистограмма, а ‘color1’ и ‘color2’ задают цвета для чередующихся столбцов. Получается, что первый и третий столбец становятся «невидимыми», окрашиваются в белый — они нужны только для буфера, чтобы отделить средний столбец, показывающий количество конверсий, от границ ячейки.
• С этим отчетом сразу понятно, где в диапазоне самое большое значение (левее всех), но это не всегда может быть очевидно. Другой вариант — рассчитывать максимальное значение из диапазона с помощью функции max, например, так: max(‘Лист1’!B7:E7).

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

Рядом можно добавить показатели для расчета конверсии. Например, в F2 прописать =’Лист1′!C7/’Лист1′!B7, в F3 — =’Лист1′!D7/’Лист1′!B7 и так далее. Лучше не протягивать формулы — все равно ячейки пропишутся неправильно, придется все перепроверять и переделывать. Опять же, чтобы десятичные дроби превратились в привычные проценты, нужно выделить соответствующие ячейки, затем кликнуть на «Формат → Числа → Процент».

Пример воронки с расчетными показателями конверсии рядом. А чтобы при копировании-вставке ячеек с формулами ссылки не менялись, можно надежно закрепить их такими значками доллара ‘$’.

Воронка получилась, но не очень красивая, верно? Из-за маленькой конверсии последних шагов диаграммы очень сужаются, буквально стремятся к нулю. Решение: выбрать в качестве максимального значения (которое, напомним, не отображается, а служит буфером с двух сторон) не число визитов, а количество открытий формы, то есть второе по величине значение. В данном кейсе — данные из ячейки ‘Лист1’!C7. То есть поменять, например, последнюю формулу на: =SPARKLINE({max(‘Лист1′!C7:E7);’Лист1’!E7;max(‘Лист1’!C7:E7)};{«charttype» «bar»; «color1» «white»; «color2» «blue»}). В итоге воронка получается более плавной и наглядной.

Так воронка преображается, если отталкиваться не от максимального значения в диапазоне, а от №2 по величине.

Бонус: выбор данных для построения воронки

На примерах выше воронка продаж/конверсии строилась только по одной строке «Итого и средние». Можно модернизировать инструмент и сделать интерактивным: чтобы устанавливать любой источник трафика, и уже по нему визуализировать данные. Для этого нужно добавить специальный селектор, задействовать больше функций Google Таблиц.

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

В данном кейсе «Переходы из рекомендательных систем», «Не определено» и «Переходы с сохранённых страниц» визуализировать нет смысла, поэтому можно включить только диапазон ‘Лист1’!A7:A13. И — не стоит забывать везде указывать название, если данные берутся с другого листа. Кстати, для удобства можно назвать его латиницей, тогда не придется каждый раз вставлять одинарные кавычки.

В процессе настройки выпадающего списка. Чтобы открыть это окно, нужно кликнуть в меню на «Данные → Настроить проверку данных».

Теперь нужно как-то подтягивать данные исходя из выбранного значения в списке. В этом поможет ВПР (VLOOKUP) — мощная функция Google Spreadsheets для вертикального поиска, связи одной таблицы с другой. Она принимает четыре аргумента:

1. Запрос, по которому нужно искать. В данном случае это значение, которое будет выбрано в ячейке B1.
2. Диапазон, в котором нужно искать. Чтобы не путаться в формулах, лучше сразу взять весь диапазон, где могут лежать искомые данные — то есть ‘Лист1’!A7:E13.
3. Номер столбца, из которого нужно извлечь данные. Для первой диаграммы это будет цифра 2, для второй — 3 и так далее.
4. Режим поиска. Лучше всегда ставить 0 или FALSE, так как в большинстве случаев нужно именно точное совпадение. (1 или TRUE будет возвращать значение, ближайшее к запрошенному (меньшее либо равное) — сложно сказать, когда на практике такое может понадобиться.)

Более простое решение — загружать данные в строку выше воронки, справа от селектора, а формулы просто немного поменять, чтобы они брали значения с этого же листа. Тогда в C1 будет такая формула: =VLOOKUP(B1;’Лист1′!A7:E13;2;0), в С2 — =VLOOKUP(B1;’Лист1′!A7:E13;3;0) и так далее. Меняется только номер столбца, так как нужна та же строка в отчете, просто цифры нужно брать все правее.

Так выглядит конструктор/селектор после настройки и добавления формул ВПР

После остается только изменить формулы для создания диаграмм — например, первую на =SPARKLINE({D1;C1;D1};{«charttype» «bar»; «color1» «white»; «color2» «blue»}); вторую на =SPARKLINE({D1;D1;D1};{«charttype» «bar»; «color1» «white»; «color2» «blue»}) и так далее.

Формулы для подсчета CTR тоже соответственно нужно поменять: =D1/C1; =E1/C1 и так далее.

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

При желании можно сделать сложнее — не копировать данные с одного листа на другой, а сразу забирать их в диаграммы SPARKLINE за счет вложения ВПР внутрь формул. Пример для третьей гистограммы, которая визуализирует «Достижения цели (Получить прайс: Нажатие на кнопку «Отправить»)»:

=SPARKLINE({VLOOKUP(B1;’Лист1′!A7:E13;3;0);VLOOKUP(B1;’Лист1′!A7:E13;4;0);VLOOKUP(B1;’Лист1′!A7:E13;3;0)};{«charttype» «bar»; «color1» «white»; «color2» «blue»})

Как видно, формула усложнилась, тем не менее работает нормально — извлекает все нужные данные без необходимости копирования на этот второй лист

Напоследок

• Все, что разбирали, — пример отчета, обычной диаграммы, воронки из спарклайнов, селектора в связке с ВПР, — доступно в этой гугл-таблице (можно забрать себе через «Файл → Создать копию»).
• Больше о гугл-таблицах — в других материалах блога CyberMarketing: 20+ продвинутых функций Spreadsheets и 25+ функций Google Таблиц для работы с текстом.
• Еще в статье «Воронка продаж как основа рекламной стратегии» мы рассказывали, что это за инструмент, зачем нужен и как его использовать.
• Прокачаться по всем остальным важным темам и направлениям диджитал-маркетинга — SMM, SEO, PPC, веб-аналитике, арбитражу трафика — можно в обучающем центре CyberMarketing. Форматы: статьи, вебинары, видеокурсы, интенсивы, конференции.
• А за классными инструментами для поискового продвижения, контекстной и таргетированной рекламы идите в Promopult и Click.