Как вовремя выявлять категории товаров, в которых снизился рекламный трафик: решение на базе Logs API Яндекс Метрики и MS Power BI

Предыстория

Представьте большой интернет-магазин одежды, обуви и спортивных товаров. У него 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. Поиск слабого трафика из органического поиска на категории бренда

На этом все, буду благодарен за обратную связь.

  • Константин Фанки

    хорошая статья, спасибо

    • Павел

      Спасибо за отзыв!

  • Интересная статья! Кстате кто не видел здесь тоже интересная статья про заработок https://goo.gl/kYkMcr?pocarquespel

  • Great content! Super high-quality! Keep it up! 🙂

  • Great content! Keep up the good work!