Цифровой маркетинговый анализ SQL
Основная цель настройки маркетинговой аналитики SQL - помочь маркетинговой команде средней компании (вымышленной) выяснить, какой должна быть правильная сумма ставки для различных сегментов платного трафика в зависимости от того, насколько хорошо они работают и какой доход они принесут.
В среде MySQL Workbench мы будем использовать базу данных SQL mediumcompany, в частности, две таблицы "website_sessions" и "orders", чтобы помочь нам понять, откуда поступает трафик и как он работает с точки зрения объема и коэффициентов конверсии. Мы также будем корректировать ставки для оптимизации маркетинговых бюджетов.
Наряду со следующими выбранными 5 общими заданиями мы будем кодировать простые SQL-запросы, но достаточно мощные, чтобы ответить на несколько оперативных маркетинговых вопросов. Мы рассмотрим примеры объединения таблиц, расчета коэффициентов конверсии, производительности устройств и тенденций объема с разбивкой.
Задание №1
От: Генеральный директор
Тема: Анализ посещаемости веб-сайта
Дата: 12 апреля 2012 г.
Мы существуем уже почти месяц и начинаем увеличивать продажи.
Можете ли вы помочь мне понять, откуда происходит основная часть посещений нашего веб-сайта до вчерашнего дня?
Я хотел бы увидеть разбивку по источникам, кампаниям и доменам-источникам.
- Мышление.
Мы хотим подсчитать website_session_id (количество сеансов), сгруппированных по utm_source, utm_campaign и utm_referer.
Это таблица наших ожидаемых результатов:
Для решения этого вопроса нам понадобится только таблица website_sessions.
SELECT * FROM mediumcompany.website_sessions;
USE mediumcompany; -- set global use of the db.
SELECT utm_source, utm_campaign, http_referer,
COUNT(DISTINCT website_session_id) AS nb_sessions
FROM website_sessions
WHERE created_at < ‘2012–04–12’
GROUP BY utm_source, utm_campaign, http_referer
ORDER BY nb_sessions DESC;
- Подсказка
Группируйте и заказывайте быстрее по каждой позиции в операторе SELECT:
SELECT
(...)
GROUP BY 1, 2, 3
ORDER BY 4 DESC;
- Результат
- Понимание
Начните с выбора источника, кампании и реферера из таблицы website_sessions на период до 12 апреля 2012 года.
SELECT utm_source, utm_campaign, http_referer
FROM website_sessions
WHERE created_at < “2012–04–12”
Затем подсчитайте количество сеансов с помощью предложения GROUP BY, суммируя значения для каждой комбинации. Расположите сеанс в порядке убывания (наивысший наверху).
SELECT (...)
COUNT(DISTINCT website_session_id) AS nb_sessions
(...)
GROUP BY 1, 2, 3
ORDER BY 4 DESC;
Изображение автора.
Мы можем сделать вывод, что gsearch и nonbrand компании привлекают больше трафика (сеансов), чем любые другие кампании или источники в период до 12 апреля 2012 года.
Задание №2
От: Директор по маркетингу
Тема: Конверсии Gsearch
Дата: 14 апреля 2012 г.
Похоже, что nonbrand gsearch является нашим основным источником трафика, но нам нужно понять, способствуют ли эти сеансы продажам.
Не могли бы вы рассчитать коэффициент конверсии (CVR) от сеанса к заказу? В зависимости от того, сколько мы платим за клики, нам понадобится CVR не менее 4%, чтобы цифры работали.
- Мышление.
Мы хотим подсчитать общее количество сеансов и заказов только для gsearch и nonbrand за период до 14 апреля 2012 года.
Что касается коэффициента конверсии, мы просто делим заказы на сеансы.
Это таблица наших ожидаемых результатов:
Чтобы решить этот вопрос, нам понадобятся как website_sessions (которые мы уже знаем из предыдущего задания), так и таблицы заказов (см. Ниже).
SELECT * FROM mediumcompany.orders;
Мы сделаем "website_sessions" слева, присоединившись к "заказам", потому что мы хотим видеть все идентификаторы website_session_id из таблицы website_sessions и посмотреть, есть ли соответствующий заказ из таблицы заказов. Мы присоединимся к обоим на website_session_id.
USE mediumcompany;
SELECT
COUNT(DISTINCT ws.website_session_id) AS sessions,
COUNT(DISTINCT o.order_id) AS orders,
(COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id)) *100 AS session_to_order_conv_rate
FROM website_sessions ws
LEFT JOIN orders o
ON o.website_session_id = ws.website_session_id
WHERE ws.created_at < ‘2012–04–14’
AND ws.utm_source = ‘gsearch’
AND ws.utm_campaign = ‘nonbrand’;
- Подсказка
Округлите 'session_to_order_conv_rate' с точностью до 2 знаков после запятой:
SELECT
(...)
ROUND((COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id)) * 100 ,2) AS session_to_order_conv_rate
- Результат
- Понимание
Начните с объединения обеих таблиц (website_sessions и orders) с левым соединением по website_session_id, выбрав website_session_id и order_id.
SELECT
ws.website_session_id AS sessions,
o.order_id AS orders
FROM website_sessions ws
LEFT JOIN orders o
ON o.website_session_id = ws.website_session_id
WHERE ws.created_at < “2012–04–14”
AND ws.utm_source = ‘gsearch’
AND ws.utm_campaign = ‘nonbrand’;
Подсчитайте общее количество сеансов и заказов.
SELECT
COUNT(DISTINCT ws.website_session_id) AS sessions,
COUNT(DISTINCT o.order_id) AS orders
(...)
Рассчитайте конверсию, разделив заказы по сессиям (* 100 для ставки).
SELECT (...)
(COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id)) *100 AS session_to_order_conv_rate
(...)
-- Round 2 decimals:
ROUND((COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id)) *100 ,2) AS session_to_order_conv_rate
Коэффициент конверсии составляет 2,9%, что означает, что небрендовые ставки gsearch не приводят к увеличению продаж, как ожидалось, инвестиции работают не лучшим образом.
Задание №3
От: Директора по маркетингу
Тема: Тенденции объемов Gsearch
Дата: 10 мая 2012 г.
На основании вашего анализа коэффициента конверсии мы понизили ставки для небрендовых продуктов gsearch на 2012–04–15 гг.
Можете ли вы просмотреть объем сеансов gsearch с небрендовыми трендами с разбивкой по неделям, чтобы узнать, не привели ли изменения ставок вообще к снижению объема?
- Мышление
Мы хотим подсчитать nonbrand сеансы gsearch в разбивке по временным рядам по неделям до 10 мая 2012 года.
Для этого сгруппируйте их по году и неделям, найдя первый или минимальный день каждой недели.
Наконец, посмотрите, снизился ли объем сессий с 15 апреля.
Это таблица наших ожидаемых результатов:
USE mediumcompany;
SELECT
MIN(DATE(created_at)) as week_started_at,
COUNT(DISTINCT website_session_id) AS sessions
FROM website_sessions
WHERE created_at < ‘2012–05–10’
AND utm_source = ‘gsearch’
AND utm_campaign = ‘nonbrand’
GROUP BY YEAR(created_at),
WEEK(created_at);
- Подсказка.
Фактически мы можем группировать по столбцам (YEAR, WEEK), не включенным в оператор SELECT.
- Результат
- Понимание
Начните с выбора и группировки по году, неделе, дате, чтобы подсчитать количество сеансов.
SELECT
YEAR(created_at) AS yr,
WEEK(created_at) AS wk,
DATE(created_at) AS dt,
COUNT(DISTINCT website_session_id) AS sessions
(...)
GROUP BY 1, 2, 3
Найдите первый или минимальный день недели. Поскольку мы продолжаем группировать их по годам и неделям, но не добавляем в предложение SELECT, детализация даты станет более плотной.
SELECT
MIN(DATE(created_at)) as week_started_at,
COUNT(DISTINCT website_session_id) AS sessions
(...)
GROUP BY YEAR(created_at), WEEK(created_at);
После 15 апреля было подтверждено, что трафик для небрендовых gsearch значительно упал.
Задание №4
От: Директор по маркетингу
Тема: Производительность Gsearch на уровне устройства
Дата: 11 мая 2012 г.
На днях я пытался использовать наш сайт на своем мобильном телефоне, и это было не очень хорошо.
Не могли бы вы перенести коэффициенты конверсии от сеанса к заказу по типу устройства?
Если производительность настольных компьютеров лучше, чем на мобильных устройствах, возможно, мы сможем поднять ставку на настольные компьютеры, чтобы получить больше объема?
- Мышление.
Мы хотим подсчитать общее количество сеансов и заказов для gsearch и nonbrand за период до 11 мая 2012 года.
Что касается коэффициента конверсии, мы просто делим заказы на сеансы.
Затем мы группируем по типу устройства. Это таблица наших ожидаемых результатов:
USE mediumcompany;
SELECT
ws.device_type,
COUNT(DISTINCT ws.website_session_id) AS sessions,
COUNT(DISTINCT o.order_id) AS orders,
ROUND((COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id)) * 100, 2) AS session_to_order_conv_rt
FROM website_sessions ws
LEFT JOIN orders o
ON o.website_session_id = ws.website_session_id
WHERE
ws.created_at < ‘2012–05–11’
AND ws.utm_source = ‘gsearch’
AND utm_campaign = ‘nonbrand’
GROUP BY 1;
- Результат
- Понимание
Начните с подсчета количества сеансов и заказов, а также коэффициента конверсии.
SELECT
COUNT(DISTINCT ws.website_session_id) AS sessions,
COUNT(DISTINCT o.order_id) AS orders,
(COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id)) *100 AS session_to_order_conv_rate
(...)
Звоните и группируйте по устройствам.
SELECT
ws.device_type,
COUNT(DISTINCT ws.website_session_id) AS sessions,
COUNT(DISTINCT o.order_id) AS orders,
ROUND((COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id)) * 100, 2) AS session_to_order_conv_rt
(...)
GROUP BY 1;
Настольные ПК работают намного лучше, поэтому мы должны поднять ставки на этот тип устройств, чтобы увеличить объемы продаж.
Задание № 5
От: директор по маркетингу
Тема: Gsearch на уровне устройств тенденции
Дата: 9 июня 2012
Проведя анализ коэффициентов конверсии на уровне устройств, мы пришли к выводу, что у настольных компьютеров все хорошо, поэтому мы повысили ставки для небрендовых кампаний gsearch для настольных компьютеров в 2012–05–19.
Не могли бы вы определить еженедельные тенденции как для настольных компьютеров, так и для мобильных устройств, чтобы мы могли повлиять на объем?
Вы можете использовать 2012–04–15 до изменения ставки в качестве базового уровня.
- Мышление.
Мы хотим выбрать и отфильтровать устройства, чтобы подсчитать количество раз, когда device_type является «настольным» или «мобильным», а затем преобразовать и сгруппировать временной ряд по годам и неделям, чтобы увидеть week_start_date.
Наконец, проверьте, не снизился ли объем сеансов настольных компьютеров с 15 апреля по 9 июня. Это таблица наших ожидаемых результатов:
USE mediumcompany;
SELECT
MIN(DATE(created_at)) AS week_start_date,
COUNT(DISTINCT CASE WHEN device_type = ‘desktop’ THEN website_session_id ELSE NULL END) AS desktop_sessions,
COUNT(DISTINCT CASE WHEN device_type = ‘mobile’ THEN website_session_id ELSE NULL END) AS mobile_sessions
FROM website_sessions
WHERE created_at BETWEEN ‘2012–04–15’ AND ‘2012–06–09’
AND utm_source = ‘gsearch’ AND utm_campaign = ‘nonbrand’
GROUP BY YEAR(created_at), WEEK(created_at)
- Результат
- Понимание
Мы начнем с выбора device_type и приведем год, неделю и дату.
USE mediumcompany;
SELECT
device_type,
YEAR(created_at) AS yr,
WEEK(created_at) AS wk,
DATE(created_at) AS dt
FROM website_sessions
WHERE created_at BETWEEN ‘2012–04–15’ AND ‘2012–06–09’ AND utm_source = ‘gsearch’ AND utm_campaign = ‘nonbrand’;
Затем отфильтруйте website_session_id по типу устройства (настольный или мобильный).
USE mediumcompany;
SELECT
device_type,
YEAR(created_at) AS yr,
WEEK(created_at) AS wk,
DATE(created_at) AS dt,
CASE WHEN device_type = ‘desktop’ THEN website_session_id ELSE NULL END AS desktop_session_id,
CASE WHEN device_type = ‘mobile’ THEN website_session_id ELSE NULL END AS mobile_session_id
FROM website_sessions
WHERE created_at BETWEEN ‘2012–04–15’ AND ‘2012–06–09’ AND utm_source = ‘gsearch’ AND utm_campaign = ‘nonbrand’
Затем мы отбросим столбец «device_type», так как мы хотим агрегировать и подсчитывать количество раз, когда device_type равно «desktop» или «mobile».
Не забудьте ГРУППИРОВАТЬ ПО ГОДУ "yr", НЕДЕЛЕ "wk" И ДАТЕ "dt", чтобы подсчет имел смысл.
USE mediumcompany;
SELECT
YEAR(created_at) AS yr,
WEEK(created_at) AS wk,
DATE(created_at) AS dt,
COUNT(CASE WHEN device_type = ‘desktop’ THEN website_session_id ELSE NULL END) AS desktop_sessions,
COUNT(CASE WHEN device_type = ‘mobile’ THEN website_session_id ELSE NULL END) AS mobile_sessions
FROM website_sessions
WHERE created_at BETWEEN ‘2012–04–15’ AND ‘2012–06–09’ AND utm_source = ‘gsearch’ AND utm_campaign = ‘nonbrand’
GROUP BY 1,2,3
Наконец, удалите «yr» и «wk» из оператора SELECT (но по-прежнему группируя по ним) для большей детализации.
Для столбца DATE 'dt' приведите и установите дату по первому (минимальному) дню, сгруппировав YEAR 'yr' и WEEK 'wk', чтобы минимальный день был равен первому дню каждой сгруппированной недели.
Не забудьте ГРУППИРОВАТЬ «yr» и «wk», чтобы подсчет имел смысл.
USE mediumcompany;
SELECT
MIN(DATE(created_at)) AS week_start_date,
COUNT(CASE WHEN device_type = ‘desktop’ THEN website_session_id ELSE NULL END) AS desktop_session_id,
COUNT(CASE WHEN device_type = ‘mobile’ THEN website_session_id ELSE NULL END) AS mobile_session_id
FROM website_sessions
WHERE created_at BETWEEN ‘2012–04–15’ AND ‘2012–06–09’ AND utm_source = ‘gsearch’ AND utm_campaign = ‘nonbrand’
GROUP BY YEAR(created_at), WEEK(created_at)
Отвечая на вопрос “Извлекать еженедельные тренды как для настольных компьютеров, так и для мобильных устройств, чтобы мы могли увидеть влияние на объем? Вы можете использовать 2012-04-15 до изменения ставки в качестве базовой линии”. Объем трафика для небрендового рабочего стола gsearch увеличился с 15 апреля по настоящее время.
Заключение
Анализ источников трафика - это понимание того, откуда приходят клиенты и какие каналы обеспечивают трафик самого высокого качества.
Анализ для оптимизации ставок - это понимание ценности различных сегментов платного трафика, чтобы мы могли оптимизировать маркетинговый бюджет.
С точки зрения маркетолога, цель состоит в том, чтобы увеличить объем и привлечь больше трафика на веб-сайт и заработать больше денег для бизнеса.
Что касается аналитики, то миссия состоит в том, чтобы анализировать источники трафика и оптимизацию ставок, чтобы понять ценность различных сегментов платного трафика, улучшая маркетинговый бюджет.
