В исходных данных есть три таблицы: Импорт расходов 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 готова. Теперь можно оперировать полученными данными при помощи фильтров. Например, можно сделать выборку всех источников, по которым были заявки. В меню выбираем: Данные -> Создать фильтр.
Чтобы Ваши данные из разных источников трафика не превратились в хаотичный набор цифр, используйте данную инструкцию для аналитики.
Провести Аудит РК!
Подпишись и следи за выходом новых статей в нашем монстрограммеОстались вопросы?
Не нашли ответ на интересующий Вас вопрос? Или не нашли интересующую Вас статью? Задавайте вопросы и темы статей которые Вас интересуют в комментариях.
Этот веб-сайт использует файлы cookie, чтобы улучшить вашу работу во время навигации по веб-сайту. Из них файлы cookie, которые классифицируются как необходимые, хранятся в вашем браузере, поскольку они необходимы для работы основных функций веб-сайта. Мы также используем сторонние файлы cookie, которые помогают нам анализировать и понимать, как вы используете этот веб-сайт. Эти файлы cookie будут храниться в вашем браузере только с вашего согласия. У вас также есть возможность отказаться от этих файлов cookie. Но отказ от некоторых из этих файлов cookie может повлиять на ваш опыт просмотра.
Необходимые файлы cookie абсолютно необходимы для правильной работы веб-сайта. Эти файлы cookie анонимно обеспечивают основные функции и функции безопасности веб-сайта.
Cookie
Duration
Description
cookielawinfo-checkbox-analytics
11 months
Этот файл cookie устанавливается подключаемым модулем GDPR Cookie Consent. Файл cookie используется для хранения согласия пользователя на использование файлов cookie в категории «Аналитика».
cookielawinfo-checkbox-functional
11 months
Cookie-файл устанавливается в соответствии с GDPR, чтобы записать согласие пользователя на использование cookie-файлов в категории «Функциональные».
cookielawinfo-checkbox-necessary
11 months
Этот файл cookie устанавливается подключаемым модулем GDPR Cookie Consent. Файлы cookie используются для хранения согласия пользователя на файлы cookie в категории «Необходимые».
cookielawinfo-checkbox-others
11 months
Этот файл cookie устанавливается подключаемым модулем GDPR Cookie Consent. Файл cookie используется для хранения согласия пользователя на использование файлов cookie в категории «Другое».
cookielawinfo-checkbox-performance
11 months
Этот файл cookie устанавливается подключаемым модулем GDPR Cookie Consent. Файл cookie используется для хранения согласия пользователя на использование файлов cookie в категории «Производительность».
viewed_cookie_policy
11 months
Файл cookie устанавливается подключаемым модулем GDPR Cookie Consent и используется для хранения информации о том, согласился ли пользователь на использование файлов cookie. Он не хранит никаких личных данных.
Функциональные файлы cookie помогают выполнять определенные функции, такие как совместное использование содержимого веб-сайта в социальных сетях, сбор отзывов и другие сторонние функции.
Файлы cookie производительности используются для понимания и анализа ключевых показателей производительности веб-сайта, что помогает улучшить пользовательский интерфейс для посетителей.
Аналитические файлы cookie используются для понимания того, как посетители взаимодействуют с веб-сайтом. Эти файлы cookie помогают предоставить информацию о таких показателях, как количество посетителей, показатель отказов, источник трафика и т.д.
Рекламные файлы cookie используются для предоставления посетителям релевантной рекламы и маркетинговых кампаний. Эти файлы cookie отслеживают посетителей на веб-сайтах и собирают информацию для предоставления персонализированной рекламы.
Комментарии