Предыстория
Представьте большой интернет-магазин одежды, обуви и спортивных товаров. У него 300+ брендов, в каждом примерно по 20 категорий. Трафик идет с множества каналов, таких как ppc, cpa, market, seo и другие.
Проблема
В связи с этим есть проблема, которая звучит так «Мы не знаем на какую категорию товаров, с какого источника не настроена реклама. Но хотим, чтобы на все категории, со всех источников шел трафик».
Решение
Построим таблицу, в которой будет виден объем продаж и трафика в разрезе источников, так победим. На сайте настроена Яндекс Метрика, включая модуль «Электронная коммерция». Возьмем данные логов пользователей из LogsApi Яндекс Метрики. Благодаря ним, мы знаем, с каких источников на какую страницу «приземляется» пользователь, при посещении сайта. Это поле называется StartURL. Но нюанс в том, что есть много страниц сайта представляющих одну категорию товаров.
Domen. ru/brand/nike/krossovki/ (страница бренда, где можно фильтровать по категориям)
Domen. ru/cat/krossovki/brand/nike (категорийная страниц, где можно фильтровать по бренду)
И еще две таких же страницы на сайте для мобильных устройств m.Domen. ru.
Поэтому по StartURL мы не может построить таблицу. Нужно определить категорию товаров для StartURL. В этом поможет модуль «Электронная коммерция», определяющая категории и бренды товаров, которые просматривает и покупает пользователь. Но пользователь пришедший, например, на страницу Domen. ru/cat/krossovki/brand/nike (кроссовки найк), также будет смотреть товары других категорий и брендов. Но большинство просмотренных товаров будет «кроссовки найк». Тогда посчитаем количество просмотренных товаров для StartURL по категории и бренду и возьмем максимальный.
Также учтем, что есть 2 типа трафика. Первый тип, пользователи ищут кроссовки без упоминания бренда (купить кроссовки), второй тип с упоминанием бренда (купить кроссовки Nike). Определим его так, если пользователь приземляется на страницу категории без фильтрации бренда, то это первый тип трафика; если на страницу категории с фильтрацией бренда, на брендовую или продуктовую страницу, то это второй тип.
Все нюансы описал, теперь учитывая их, понимаем SQL запрос.
SELECT -- 4. Определяем тип трафика по тому, к какому типу страницы относится startURL if( startURL LIKE '%brand%' OR startURL LIKE '%manufacturers_id%' OR startURL LIKE '%/product/%', 'cat-brand', if(startURL LIKE '%/cat/%', 'cat', 'other' ) ) as LandingType, Source, Category, Brand, sum(visits) as Visits, sum(Revenue) as Revenue FROM( -- 3. Считаем кол-во трафика. 1 строка = 1 визит. Сумма строк = сумма визитов SELECT -- 3.1 Все источники нам не нужны. Разметим, который нам интересен. if( UTMMedium = 'cpa', 'cpa', if( UTMCampaign LIKE '%rtg%' OR UTMCampaign LIKE '%eting%', 'retargeting', if(lastTrafficSource = 'ad' AND lastAdvEngine LIKE 'ya_%', 'ya_direct', if(lastTrafficSource = 'ad' AND lastAdvEngine = 'market', 'ya_market', if(lastTrafficSource = 'ad' AND lastAdvEngine = 'google_adwords', 'google_adwords', if(lastTrafficSource = 'ad' AND (lastAdvEngine = 'vkontakte' OR UTMSource LIKE 'VK%'), 'vk', if(lastTrafficSource = 'organic', 'organic', 'other' ) ) ) ) ) ) ) as Source, startURL, count() as visits, sumArray(purchaseRevenue) as Revenue FROM proskater.logsapi_visits_all WHERE date >= today()-45 GROUP BY startURL, Source ) ALL INNER JOIN ( -- 2. Выбираем максимальное значение. SELECT startURL, argMax(Category, count) as Category, argMax(Brand, count) as Brand FROM( -- 1. Считаем кол-во просмотренных товаров для StartURL по категории и бренду SELECT startURL, impressionsProductCategory3 as Category, impressionsProductBrand as Brand, count() as count FROM proskater.logsapi_visits_all ARRAY JOIN impressionsProductCategory3, impressionsProductBrand WHERE date >= today()-45 and notEmpty(impressionsProductCategory3) GROUP BY startURL, Category, Brand ) GROUP BY startURL ) USING startURL WHERE Source != 'other' GROUP BY Source, LandingType, Category, Brand HAVING LandingType != 'other' -- 5. Считаем все тоже самое, для всех источников, чтоб было с чем сравнивать. UNION ALL SELECT if( startURL LIKE '%brand%' OR startURL LIKE '%manufacturers_id%' OR startURL LIKE '%/product/%', 'cat-brand', if(startURL LIKE '%/cat/%', 'cat', 'other' ) ) as LandingType, 'all' as Source, Category, Brand, sum(visits) as Visits, sum(Revenue) as Revenue FROM( SELECT if( UTMMedium = 'cpa', 'cpa', if( UTMCampaign LIKE '%rtg%' OR UTMCampaign LIKE '%eting%', 'retargeting', if(lastTrafficSource = 'ad' AND lastAdvEngine LIKE 'ya_%', 'ya_direct', if(lastTrafficSource = 'ad' AND lastAdvEngine = 'market', 'ya_market', if(lastTrafficSource = 'ad' AND lastAdvEngine = 'google_adwords', 'google_adwords', if(lastTrafficSource = 'ad' AND (lastAdvEngine = 'vkontakte' OR UTMSource LIKE 'VK%'), 'vk', if(lastTrafficSource = 'organic', 'organic', 'other' ) ) ) ) ) ) ) as Source, startURL, count() as visits, sumArray(purchaseRevenue) as Revenue FROM proskater.logsapi_visits_all WHERE date >= today()-45 GROUP BY startURL, Source ) ALL INNER JOIN ( SELECT startURL, argMax(Category, count) as Category, argMax(Brand, count) as Brand FROM( SELECT startURL, impressionsProductCategory3 as Category, impressionsProductBrand as Brand, count() as count FROM proskater.logsapi_visits_all ARRAY JOIN impressionsProductCategory3, impressionsProductBrand WHERE date >= today()-45 and notEmpty(impressionsProductCategory3) GROUP BY startURL, Category, Brand ) GROUP BY startURL ) USING startURL WHERE Source != 'other' GROUP BY Source, LandingType, Category, Brand HAVING LandingType != 'other'
Файл Power BI можно скачать тут.
Дашборд goo.gl/KAtf9X
Анализ
Сценарий 1. Поиск слабого трафика из органического поиска на категории
Слабые, по моему мнению, категории выделил.
Сценарий 2. Поиск слабого трафика из органического поиска на категории бренда
На этом все, буду благодарен за обратную связь.