Урок по Power BI: как блог влияет на продажи?

Сергей Довганич Сергей Довганич 30 января 2017

Всем привет! 

В сегодняшнем посте мы будем использовать данные из Google Analytics и SalesForce, которые хранятся в Azure SQL DB. А после получения данных, построим отчёт в Power BI. 

Задача

При росте расходов на контент-маркетинг, возник вопрос: на сколько эффективно работает блог?

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

В результате получили отчёт, который отвечает на поставленный вопрос:

Ответ: в отчете видно, что количество закрытых сделок в CRM-системе, прямо пропорционально количеству лидов в Google Analytics. 

Техническая реализация

Данные из Google Analytics и SalesForce хранятся в базе данных в виде отдельных таблиц. 

  • Таблица с данными из Google Analytics содержит: данные о странице входа, доменное имя, количество сеансов, client id. Но не содержит информацию о типе страниц.
    Нужно добавить.
  • Таблица с информацией о лидах, содержит столбец уникальных значений Client ID (CID) и статус сделки.
    Если в CRM-системе есть несколько обращений с одинаковыми CID, то берется статус сделки, который находится ближе всего к успешной продажи. 

Первый шаг

Необходимо определить тип страницы, которую посещал пользователь. Для этого необходимо выполнить два sql-запроса к таблице с данными из Google Analytics.

Первый запрос создает столбец, который хронит информацию о типе страниц.
Назовём его pageType. 

Задача второго запроса, определить тип страницы и добавить полученную информацию в столбец pageType: 

Где: 

  • landingPagePath — URL, который посещал пользователь. Данные из Google Analytics. 
  • Логические операторы then else, определяют содержит ли столбец landingPagePath ссылки с содержанием blog и если содержит, то присваивается тип страницы Blog, если нет — Site

 

 

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

Второй шаг

В этом шаге мы объединим данные из двух таблиц. Объединение будет происходить по общему столбцу — CID. Делается это при помощи следующего SQL-запроса: 

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

На этом техническая реализация заканчивается.
Можно перейти к созданию отчетов в Power BI. 

Построение отчётов в Power BI

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

Далее, необходимо создать несколько мер:

Где:

  • Total Sessions — сумма всех сеансов. 
  • Leads — количество уникальных лидов в CRM. 
  • CR — коэф. конверсии в лиды. 
  • Deals — количество уникальных сделок (продаж). 

Меры — это вычисляемые метрики.
Аналог формул в Excel-e. 

Создаются следующим образом: 

И последним шагом необходимо перейти к построению визуализации: диаграмм, графиков. После чего, итоговый отчёт будет готов. 

Инструменты

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

  • SQL Server Management Studio (SSMS) под Windows. 
  • Navicat под Mac.

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

Сергей Довганич
Сергей Довганич,
Руководитель проектов
Другие интересные материалы:

Ваш комментарий:
Ваш ответ:


3 бесплатных урока по Google Analytics!
Перестаньте путаться в отчетах и научитесь считать CPL, CPO и ROI.
Понятные руководства с наглядными примерами!
Открыть доступ!
Мне это не нужно!
close-link