В исходных данных есть три таблицы: Импорт расходов Google Analytics, Данные по трафику с количеством заявок и Выгрузка по сделкам из AMO CRM. Эти данные необходимо объединить в одну таблицу, которая отобразит количество визитов, количество заявок и количество сделок.
Для объединения данных из нескольких источников в один свод, необходим какой-то общий параметр, которому будет производиться склейка. В данном случае этим параметром будет Источник / канал.
1. Приводим к нужному формату
В таблице Импорт расходов, этот параметр присутствует, а в выгрузках из AMO CRM и Данных по трафику, его нет, но есть отдельные колонки с источниками и каналами. Чтобы привести их к нужному формату (источник / канал) пишем формулу: =A2&» / «&B2Протягиваем формулу до конца списка.
В выгрузке из AMO создаём аналогичный столбец, используем данные utm_source (источник) и utm_medium (канал). Пишем формулу: =H2&» / «&I2, протягиваем до конца списка.
Для созданных столбцов прописываем заголовки — Источник / канал.
2. Используем инструмент «Сводная таблица»
Теперь необходимо получить количество сделок по источникам из AMO и Данных по трафику. Для этого используем инструмент Сводная таблица. Выделяем всю таблицу на вкладке AMOCRM (обязательно выделить заголовки).
Переходим в меню: Данные -> Сводная таблица…Откроется новая вкладка, на которой будет формироваться сводная таблица. Справа, в блоке «Редактор сводной таблицы», в разделе «Строки», нажимаем добавить и выбираем Источник / канал. В разделе «Значения», добавляем Этап сделки. В итоге мы получим сводную таблицу в которой отображено количество сделок для каждого источника трафика. Называем вкладку Свод AMO.
Аналогичным способом создаём таблицу со сводными данными по трафику. В редакторе сводной таблицы, в поле «Строки» выбираем Источник / канал, а в поле «Значения» — SUM из Заявка на сайте (Достигнутые переходы к цели 1).
3. Используем функцию ВПР (вертикальный просмотр)
Теперь необходимо объединить весь трафик с заявками и созданные сделки. В этом поможет функция ВПР (вертикальный просмотр). Суть функции в том, что она берёт значение из одной таблицы, и ищет его в другой таблице. Если есть совпадение, то подставляет значение из определённого столбца по строке, в которой обнаружено совпадение, в противном случае – ошибка.
На вкладке Свод по трафику создаём столбец Количество сделок, и прописываем формулу:
=ВПР(A2;'Свод AMO'!A$2:B$29;2;ЛОЖЬ)
A2 – искомое значение;
‘Свод AMO’!A$2:B$29 – таблица в которой производится поиск (поиск по первому столбцу таблицы);
2 – столбец из которого будет подставлено значение при совпадении, ЛОЖЬ – искать точное совпадение;
$ — используется для того, чтобы при протягивании функции вниз до конца списка, эти значения не менялись. В противном случае выделенный диапазон (таблица) будет смещаться вниз, и функция вернёт некорректные значения.На скрине видно, что в некоторый случаях функция ВПР подставляет значение #Н/Д, это значит, что значение не найдено и функция возвращает ошибку. Чтобы избежать такого отображения, и выводить вместо #Н/Д цифру 0, используем функцию обёртки ЕСЛИОШИБКА. Формула будет выглядеть следующим образом:
=ЕСЛИОШИБКА(ВПР(A2;'Свод AMO'!A$2:B$29;2;ЛОЖЬ);0)
4. Функция ВПР для таблицы из Данных по трафику
Трафик подтягиваем при помощи ВПР из таблицы Данные по трафику. Предварительно необходимо создать столбец Источник / канал, после столбца Канал с соответствующими значениями. На листе со сводными данными добавляем столбец трафик с формулой:
=ЕСЛИОШИБКА(ВПР(A2;'Импорт расходов Google Analytics'!A$1:H$11;3;ЛОЖЬ);0)
Добавляем столбец Расходы с формулой:
=ЕСЛИОШИБКА(ВПР(A2;'Импорт расходов Google Analytics'!A$1:H$11;6;ЛОЖЬ);0)
Сводная таблица в Excel готова. Теперь можно оперировать полученными данными при помощи фильтров. Например, можно сделать выборку всех источников, по которым были заявки. В меню выбираем: Данные -> Создать фильтр.
Чтобы Ваши данные из разных источников трафика не превратились в хаотичный набор цифр, используйте данную инструкцию для аналитики.
Подпишись и следи за выходом новых статей в нашем монстрограмме
Остались вопросы?
Не нашли ответ на интересующий Вас вопрос? Или не нашли интересующую Вас статью? Задавайте вопросы и темы статей которые Вас интересуют в комментариях.
Комментарии