Как посчитать конверсию в гугл таблице
Перейти к содержимому

Как посчитать конверсию в гугл таблице

  • автор:

Как конвертировать валюту в Google Таблицах (функция Google Finance)

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

В этом уроке я покажу вам, как конвертировать валюту в Google Таблицах с помощью функции Google Finance.

Использование GOOGLEFINANCE для конвертации валюты в Google Таблицах

Функция GOOGLEFINANCE в Google Таблицах — действительно удобный инструмент, который получает данные о курсах конвертации валют в режиме реального времени (почти в реальном времени).

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

Синтаксис ФУНКЦИИ GOOGLEFINANCE

Базовый синтаксис ФУНКЦИИ GOOGLEFINANCE следующий:

  • source_currency_symbol — это трехбуквенный код валюты, из которой вы хотите конвертировать.
  • target_currency_symbol — это трехбуквенный код валюты, в которую вы хотите конвертировать.

Например, если вам нужен курс конвертации долларов в рупии, вы должны ввести функцию:

Обратите внимание, что между двумя кодами валют нет пробела.

Вот еще несколько кодов валют:

Валюта Код
Доллар США USD
Японская иена JPY
Канадский доллар CAD
Индийская рупия INR
Иранский риал IRR
Российский рубль RUB
Евро EUR
Сингапурский доллар SGD
Гонконгский доллар HKD
Фунт Соединенного Королевства GBP

Как использовать GOOGLEFINANCE для получения курсов валют

Давайте посмотрим на пример. Здесь у нас есть три валюты в столбце B, и мы хотим преобразовать валюту в столбце A в валюту в столбце B:

Вот шаги, которые вам нужно выполнить, чтобы получить обменный курс доллара к трем валютам в столбце B:

  • Выберите первую ячейку столбца, в которой должны отображаться результаты (C2).
  • Введите формулу: =GOOGLEFINANCE(“CURRENCY:USDINR”)
  • Нажмите клавишу возврата.

Вы должны увидеть текущий обменный курс для конвертации долларов США в индийские рупии в ячейке C2.

  • Кроме того, вы можете даже включить ссылки на ячейки в функции, объединив их, как показано ниже: =GOOGLEFINANCE(“CURRENCY:”&A2&B2)
  • Нажмите клавишу возврата.
  • Дважды щелкните маркер заполнения ячейки C2, чтобы скопировать формулу в остальные ячейки столбца C.

Теперь вы должны увидеть курсы конвертации долларов США во все три валюты, указанные в таблице.

Как конвертировать USD в INR с помощью GOOGLEFINANCE

Вышеупомянутые шаги дают вам только курсы конвертации между двумя валютами, но на самом деле они не конвертируют деньги из одной валюты в другую. Предположим, у нас есть следующий список цен в долларах, и мы хотим преобразовать их в INR.

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

  • Выберите первую ячейку столбца, в которой должны отображаться результаты (B2).
  • Введите формулу:
    =GOOGLEFINANCE(«CURRENCY:USDINR»)*A2

  • Нажмите клавишу возврата.
  • Дважды щелкните маркер заполнения ячейки B2, чтобы скопировать формулу в остальные ячейки столбца C.
  • Теперь вы должны увидеть столбец B, в котором указаны цены в индийских рупиях.

Обратите внимание, что мы просто умножили результат функции GOOGLEFINANCE на значение ячейки в столбце A, чтобы преобразовать цену в INR. Достаточно ввести свои параметры вместе с общей функцией GOOGLEFINANCE, чтобы получить точный коэффициент конверсии.

Однако есть и другие необязательные параметры, которые функция GOOGLEFINANCE позволяет вам вводить, чтобы получить именно то, что вам нужно. Например, вы также можете использовать его для отображения исторических обменных курсов.

Как использовать GOOGLE FINANCE для получения исторических курсов валют

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

Чтобы получить исторические обменные курсы, функцию GOOGLEFINANCE можно настроить на следующий синтаксис:

В приведенном выше синтаксисе все параметры, указанные в квадратных скобках, необязательны. Вот что они означают:

  • Параметр attribute указывает тип данных, которые вы хотите получить. Это строковое значение, значение по умолчанию — «цена». Это означает, что мы хотим получать котировки цен в режиме реального времени из Google Финансов. Мы предоставили список значений атрибутов и их значения в конце этого руководства.
  • Параметр start_date указывает дату, с которой мы хотим, чтобы исторические данные начинались.
  • В четвертом параметре вы можете указать end_date для исторических данных или количество дней от start_date, для которых вы хотите получить исторические данные.
  • Параметр interval указывает частоту возвращаемых данных. Это может быть «ЕЖЕДНЕВНО» или «ЕЖЕНЕДЕЛЬНО», в зависимости от ваших требований,

Как использовать GOOGLEFINANCE для получения данных о курсах валют за определенный период времени

Давайте рассмотрим пример, чтобы понять, как функцию GOOGLEFINANCE можно использовать для получения обменных курсов (доллар / индийская рупия) с 10 октября 2020 года по 20 октября 2020 года.

  • Выберите ячейку, с которой вы хотите начать отображение обменных курсов. Вам не нужно добавлять заголовок для столбцов, поскольку функция добавляет заголовки столбцов автоматически.
  • Введите формулу: =GOOGLEFINANCE(“CURRENCY:USDEUR”, “price”, DATE(2020,10,10), DATE(2020,10,20), “DAILY”)
  • Нажмите клавишу возврата

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

Первый столбец содержит дату для каждого дня с 10 октября 2020 года по 20 октября 2020 года. Второй столбец содержит курс обмена на конец каждого дня. Если вы хотите отображать еженедельные ставки вместо дневных, вы можете просто заменить параметр интервала в функции с DAILY на WEEKLY.

Как использовать GOOGLEFINANCE для получения данных о курсах валют за прошедшую неделю

Если вы хотите динамически отображать обменные курсы за прошлый период, скажем, одну неделю в зависимости от дня открытия листа, вы можете использовать функцию СЕГОДНЯ (TODAY) вместо ДАТЫ.

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

Следуй этим шагам:

  • Выберите ячейку, с которой вы хотите начать отображение обменных курсов.
  • Введите формулу: =GOOGLEFINANCE(“CURRENCY:USDEUR”, “price”, TODAY()-10, TODAY(), “DAILY”)
  • Нажмите клавишу возврата

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

Первый столбец содержит дату для каждого дня от 10 дней до текущей даты. Во втором столбце указан обменный курс на конец каждого дня.

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

Несколько моментов, которые следует запомнить

Вот несколько важных вещей, которые вам необходимо знать, чтобы понять функцию GOOGLEFINANCE:

  • Когда мы говорим об обменных курсах в реальном времени, вы можете ожидать задержки до 20 минут.
  • Для ставок в реальном времени функция возвращает единственное значение. Однако для исторических ставок функция возвращает массив вместе с заголовками столбцов.
  • Если вы не укажете никаких параметров даты, GOOGLEFINANCE предполагает, что вам нужны только результаты в реальном времени. Если вы указываете какой-либо параметр даты, запрос рассматривается как запрос исторических данных.

Значения атрибутов и их значения

Вот некоторые из наиболее часто используемых значений параметра атрибута GOOGLEFINANCE:

Для данных в реальном времени:

  • «Priceopen» — нам нужна цена на момент открытия рынка.
  • «High» — нам нужна максимальная цена текущего дня.
  • «Low» — нам нужна минимальная цена текущего дня.
  • «Volume» — нам нужен объем торгов за текущий день.
  • «Marcetcap» — нам нужна рыночная капитализация акций.

Для исторических данных:

  • «Open» — нам нужна цена открытия на указанную дату (даты).
  • «Close» — нам нужна цена закрытия на указанную дату (даты).
  • «High» — нам нужна высокая цена на указанную дату (даты).
  • «Low» — нам нужна низкая цена на указанную дату (даты).
  • «Volume» — нам нужен объем на указанную дату (даты).
  • «All» — Нам нужна вся вышеуказанная информация.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Массив — это как бы виртуальная таблица. В данном случае у нее один столбец: точка с запятой ‘;’ показывает, что значения располагаются сверху вниз, вертикально. А если обратный слеш ‘\’, то наоборот — значения идут по горизонтали, в строку. То есть массивы могут быть и многомерными вроде <<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, а не параметра, или еще проще — через ссылку на соответствующую ячейку, так как в данном примере всегда известно, где находится то самое максимальное значение.

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

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

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

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

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

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

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

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

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

Так воронка преображается, если отталкиваться не от максимального значения в диапазоне, а от №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) и так далее. Меняется только номер столбца, так как нужна та же строка в отчете, просто цифры нужно брать все правее.

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

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

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

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

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

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

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

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

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

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

Александр Киселёв

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

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

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

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

Лист дашборда

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

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

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

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

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

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

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

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

в таком дашборде можно собрать только самые нужные данные — в отличие от отчетов Google Analytics и 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. В ней есть такой фрагмент:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Таблица

График

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

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

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

Формулы для всех этих ячеек мы растягиваем и получаем дашборд. График визуализируется по месяцам, на него выводятся только те позиции, которые вы отметите. Не буду останавливаться на том, как такое сделать — это легко нагуглить или найти в тематических пабликах. Если хотите посмотреть, как все устроено, просто подвиньте график вниз. Под ним вы увидите цифры, которые подтягиваются на этот лист.

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

Шаг 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%.

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

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

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

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

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

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

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

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

Так-с. Мы вас не узнаём! Зайдёте под своим аккаунтом?
  • читать все материалы на ppc.world;
  • добавлять лучшие статьи в Избранное;
  • оставлять комментарии;
  • получать рекомендации актуальных материалов.
Последние комментарии

RTB Sape

Добрый день! Да, конечно, можно также размещать рекламу разных сетей и на одной странице.

Надежда Головина

Здравствуйте. А может ли быть размещение медийной рекламы нескольких сетей на сайте? Например, одну страницу выделить по.

Александр Грошев

Добрый день! Как быстро событие пришедшее из GTM появляется в таблице со списком событий (где можно превратить их в конв.

Александр Павлов

Попробовал. Яндекс предлагает сделать сайт из каталога Яндекс Бизнес, а не из группы ВК. Так это можно было делать и ра.

Max Glot

Михаил подскажите пожалуйста. Все сделал — все работает. Но на сайте все выводиться маленькими буквами. Можно ли как то .

Анатолий Иванов

в статье неточность — при фразовом соответствии порядок слов не учитывается

Как посчитать конверсию в гугл таблице

Как мы знаем, некоторые формулы в таблице Google отличаются от формул в таблице Excel, например, формула для расчета конвертации валюты. Здесь я представляю формулы для нескольких случаев для расчета конвертации валюты и курса валюты в таблице Google.

Расчет конвертации валюты между двумя валютами в листе Excelхорошая идея3

Расчет конвертации валюты между двумя валютами в листе Google

Вот несколько формул для разных случаев.

Рассчитать конвертацию валюты

Выберите ячейку, в которую вы хотите поместить результат, введите эту формулу = A2 * GOOGLEFINANCE («ВАЛЮТА: USDGBP») (в формуле A2 — это значение ячейки, которое вы используете для преобразования, USD — валюта, которую вы хотите конвертировать, GBP — валюта, в которую вы хотите конвертировать). Затем нажмите Enter key, отобразится результат.
документ конвертация валюты в таблице Google 1

Рассчитайте курс между двумя валютами

Выберите ячейку, в которую вы хотите поместить результат, введите эту формулу = GOOGLEFINANCE («ВАЛЮТА:» & $ A $ 4 & B4) (в формуле $ A $ 4 и B4 означают расчет курса при конвертации долларов США в евро). Затем нажмите Enter key, отобразится результат.
документ конвертация валюты в таблице Google 2

Укажите курс двух валют за неделю.

Выберите ячейку A6, чтобы ввести первую дату недели, которую вы хотите перечислить, затем в пустую ячейку, в которую вы хотите поместить результаты, введите эту формулу = GOOGLEFINANCE («ВАЛЮТА: USDGBP», «цена», A6, A6 + 7, «ЕЖЕДНЕВНО») (в формуле A6 — это первая дата недели, A6 + 7 означает перечисление курсов валют на следующие 7 дней, USDGBP означает преобразование долларов США в фунты стерлингов). Затем нажмите Enter key, отобразится результат.
документ конвертация валюты в таблице Google 3

Расчет конвертации валюты между двумя валютами в листе Excel

Если вы работаете в листе Excel, вы можете использовать Kutools for ExcelАвтора Конверсия валюты утилита для быстрой конвертации валют.

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Выберите данные, которые вы используете для преобразования, и нажмите Кутулс > Content > Конверсия валюты.
документ конвертация валюты в таблице Google 4

2. в Конверсия валюты выберите валюты, между которыми вы хотите конвертировать.
документ конвертация валюты в таблице Google 5

3. Нажмите Заполнять параметры, чтобы решить заполнить результат в исходных ячейках или в виде комментариев. Смотрите скриншот:
документ конвертация валюты в таблице Google 6

4. Нажмите Закрыть > Ok. Как вы указали, доллар США был конвертирован в канадский доллар.
документ конвертация валюты в таблице Google 7

Добавить комментарий

Ваш адрес email не будет опубликован.