Как работать с неагрегированными данными Яндекс.Метрики? (Метрика Про)

11 сентября, 2023

В этом руководстве я покажу, как использовать представленные фрагменты кода из официальной справки Яндекса для работы с неагрегированными данными вашего счетчика Яндекс.Метрики, выгруженными в базу данных ClickHouse.

Чтобы лучше понять, о чем будет идти речь далее, я рекомендую познакомиться вам с Метрикой Про, а также со следующими материалами моего блога, и только после этого переходить к изучению нижеприведенных примеров:

Именно на их основе я буду выполнять работу с неагрегированными (сырыми) данными своего счетчика Яндекс.Метрики, настроенную с помощью интеграции, входящей в пакет Метрика Про. Это важно, поскольку механизм интеграции обладает некоторыми отличиями от выгрузки данных с использованием Logs API (+ не является обратно совместимой с Logs API по формату данных):

интеграция в Метрике Про содержит расширенный набор полей;
в визитах, в отличие от Logs API, атрибуция лежит массивами. Все массивы вида TrafficSource.XXX скоррелированы между собой по типу атрибуции. Поле XXX в нужной атрибуции YYY: TrafficSource.XXX[indexOf(TrafficSource.Model, YYY)] as XXX;
в визитах поле FirstPartyCookie аналогично clientid в LogsAPI.

Фрагменты запросов, которые будут использоваться в этом руководстве, размещены в официальной документации Яндекса. Мы лишь пройдемся по ним и увидим реальный результат от выполнения данных команд. И сделаем это в двух местах:

подключимся к базе данных ClickHouse с помощью DBeaver и выполним запрос там;
подключимся к Yandex DataLens с помощью встроенного коннектора ClickHouse и выполним запрос в нем, создав QL-чарт.

Чтобы повторить все то, что я буду делать ниже, вам необходимо прочитать материалы (см. выше) и выполнить все, что в них написано, строго следуя моим рекомендациям.

Начнем работу с неагрегированными данными Яндекс.Метрики?

Все приведенные запросы выполняются только для пакета Метрика Про.

Содержание

Как выгружать визиты

Первый запрос, который мы выполним - это данные по визитам. В официальной справке Яндекса он выглядит так (пример со множеством полей):

SELECT
    VisitID,
    CounterID,
    StartDate,
    CounterUserIDHash,  -- внутренний UserID, с которым работает Метрика
    FirstPartyCookie, -- то же самое, что и ClientID в LogsAPI
    Duration,
    EAction.Type,
    EndURL,
    Goals.ID,
    IsBounce,
    IsMobile,
    OS,
    OSFamily,
    OSName,
    PageViews,
    Referer,
    RegionID,
    StartURL,
    TrafficSource.ID, -- Значения в полях TrafficSource.ID означают: 
{-1: внутренние переходы; 0: прямые заходы; 
1: переходы по ссылкам на сайтах; 2: переходы из поисковых систем;
3: переходы по рекламе; 4: переходы с сохраненных страниц;
5: Не определен; 6: Переходы по внешним ссылкам; 7: переходы с почтовых рассылок;
8: переходы из соц сетей; 9: переходы из рекомендательных систем; 
10: переходы из мессенджеров; 11: переходы по QR коду}
    TrafficSource.StrID, -- человеческое название источника трафика
    TrafficSource.Model, -- все массивы TrafficSource.XXX скоррелированы между собой.
Порядок элементов в них отражает по какой атрибуции TrafficSource.Model считается значение поля TraficSource.XXX, а внутри массива лежит само значение TraficSource.XXX
    TrafficSource.ID[indexOf(TrafficSource.Model, 1)] as last_TraficSourceID, -- Источник трафика по атрибуции Последний переход
    TrafficSource.ID[indexOf(TrafficSource.Model, 2)] as last_significant_TraficSourceID, -- Источник трафика по атрибуции Последний значимый переход
    TrafficSource.ID[indexOf(TrafficSource.Model, 3)] as first_TraficSourceID, -- Источник трафика по атрибуции Первый переход
    TrafficSource.ID[indexOf(TrafficSource.Model, 4)] as last_yandex_direct_TraficSourceID,  -- Источник трафика по атрибуции Последний значимый переход Яндекс Директ
    TrafficSource.ID[indexOf(TrafficSource.Model, 5)] as cd_last_significant_TraficSourceID, -- Источник трафика по атрибуции Последний значимый переход (кросс-девайс)
    TrafficSource.ID[indexOf(TrafficSource.Model, 6)] as cd_first_TraficSourceID, -- Источник трафика по атрибуции Первый переход (кросс-девайс)
    TrafficSource.ID[indexOf(TrafficSource.Model, 7)] as cd_last_yandex_direct_TraficSourceID, -- Источник трафика по атрибуции Последний значимый переход Яндекс Директ (кросс-девайс)
    
    -- Если источник трафик реклама, то можно посмотреть, какая это рекламная система.
На примере последнего значимого источника трафика:
    If(last_significant_TraficSourceID = 3, TrafficSource.AdvEnginePlaceStrID[indexOf(TrafficSource.Model, 2)], 'not_ad') as last_significant_adv_engine_id,
    -- Если источник трафик поиск, то можно посмотреть, какая это поисковая система. 
На примере последнего значимого источника трафика:
    If(last_significant_TraficSourceID = 2, TrafficSource.SearchEngineStrID[indexOf(TrafficSource.Model, 2)], 'not_search') as last_significant_search_engine_id,
    -- Аналогично для остальных источников трафика: соц сети, рекомендательная система и т.д.
    UserAgent,
    WatchIDs -- для связки с WatchID из hits_all. В эту колонку не попадут хиты IsParameter = 1. В случае переполнения 500 хитов в этом массиве, остальные вывалятся за борт
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r  -- сюда вставить свою базу и свою таблицу визитов
WHERE StartDate = today() - 1 --сюда вставить любую дату, например, StartDate = toDate('2022-02-01') или StartDate = '2022-02-01'
    AND CounterID = toUInt32(24226447)
GROUP BY
    VisitID,
    CounterID,
    StartDate,
    CounterUserIDHash,
    FirstPartyCookie,
    Duration,
    EAction.Type,
    EndURL,
    Goals.ID,
    IsBounce,
    IsMobile,
    OS,
    OSFamily,
    OSName,
    PageViews,
    Referer,
    RegionID,
    StartURL,
    TrafficSource.Model,
    TrafficSource.ID,
    TrafficSource.StrID,
    last_TraficSourceID,
    last_significant_TraficSourceID,
    first_TraficSourceID, 
    last_yandex_direct_TraficSourceID,
    cd_last_significant_TraficSourceID,
    cd_first_TraficSourceID,
    cd_last_yandex_direct_TraficSourceID,
    last_significant_adv_engine_id,
    last_significant_search_engine_id,
    UserAgent,
    WatchIDs
HAVING sum(Sign) = 1
limit 1000

SELECT

VisitID,

CounterID,

StartDate,

CounterUserIDHash, -- внутренний UserID, с которым работает Метрика

FirstPartyCookie, -- то же самое, что и ClientID в LogsAPI

Duration,

EAction.Type,

EndURL,

Goals.ID,

IsBounce,

IsMobile,

OS,

OSFamily,

OSName,

PageViews,

Referer,

RegionID,

StartURL,

TrafficSource.ID, -- Значения в полях TrafficSource.ID означают:

{-1: внутренние переходы; 0: прямые заходы;

1: переходы по ссылкам на сайтах; 2: переходы из поисковых систем;

3: переходы по рекламе; 4: переходы с сохраненных страниц;

5: Не определен; 6: Переходы по внешним ссылкам; 7: переходы с почтовых рассылок;

8: переходы из соц сетей; 9: переходы из рекомендательных систем;

10: переходы из мессенджеров; 11: переходы по QR коду}

TrafficSource.StrID, -- человеческое название источника трафика

TrafficSource.Model, -- все массивы TrafficSource.XXX скоррелированы между собой.

Порядок элементов в них отражает по какой атрибуции TrafficSource.Model считается значение поля TraficSource.XXX, а внутри массива лежит само значение TraficSource.XXX

TrafficSource.ID[indexOf(TrafficSource.Model, 1)] as last_TraficSourceID, -- Источник трафика по атрибуции Последний переход

TrafficSource.ID[indexOf(TrafficSource.Model, 2)] as last_significant_TraficSourceID, -- Источник трафика по атрибуции Последний значимый переход

TrafficSource.ID[indexOf(TrafficSource.Model, 3)] as first_TraficSourceID, -- Источник трафика по атрибуции Первый переход

TrafficSource.ID[indexOf(TrafficSource.Model, 4)] as last_yandex_direct_TraficSourceID, -- Источник трафика по атрибуции Последний значимый переход Яндекс Директ

TrafficSource.ID[indexOf(TrafficSource.Model, 5)] as cd_last_significant_TraficSourceID, -- Источник трафика по атрибуции Последний значимый переход (кросс-девайс)

TrafficSource.ID[indexOf(TrafficSource.Model, 6)] as cd_first_TraficSourceID, -- Источник трафика по атрибуции Первый переход (кросс-девайс)

TrafficSource.ID[indexOf(TrafficSource.Model, 7)] as cd_last_yandex_direct_TraficSourceID, -- Источник трафика по атрибуции Последний значимый переход Яндекс Директ (кросс-девайс)

-- Если источник трафик реклама, то можно посмотреть, какая это рекламная система.

На примере последнего значимого источника трафика:

If(last_significant_TraficSourceID = 3, TrafficSource.AdvEnginePlaceStrID[indexOf(TrafficSource.Model, 2)], 'not_ad') as last_significant_adv_engine_id,

-- Если источник трафик поиск, то можно посмотреть, какая это поисковая система.

На примере последнего значимого источника трафика:

If(last_significant_TraficSourceID = 2, TrafficSource.SearchEngineStrID[indexOf(TrafficSource.Model, 2)], 'not_search') as last_significant_search_engine_id,

-- Аналогично для остальных источников трафика: соц сети, рекомендательная система и т.д.

UserAgent,

WatchIDs -- для связки с WatchID из hits_all. В эту колонку не попадут хиты IsParameter = 1. В случае переполнения 500 хитов в этом массиве, остальные вывалятся за борт

FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу визитов

WHERE StartDate = today() - 1 --сюда вставить любую дату, например, StartDate = toDate('2022-02-01') или StartDate = '2022-02-01'

AND CounterID = toUInt32(24226447)

GROUP BY

VisitID,

CounterID,

StartDate,

CounterUserIDHash,

FirstPartyCookie,

Duration,

EAction.Type,

EndURL,

Goals.ID,

IsBounce,

IsMobile,

OS,

OSFamily,

OSName,

PageViews,

Referer,

RegionID,

StartURL,

TrafficSource.Model,

TrafficSource.ID,

TrafficSource.StrID,

last_TraficSourceID,

last_significant_TraficSourceID,

first_TraficSourceID,

last_yandex_direct_TraficSourceID,

cd_last_significant_TraficSourceID,

cd_first_TraficSourceID,

cd_last_yandex_direct_TraficSourceID,

last_significant_adv_engine_id,

last_significant_search_engine_id,

UserAgent,

WatchIDs

HAVING sum(Sign) = 1

limit 1000

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

В строке:

FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r  -- сюда вставить свою базу и свою таблицу визитов

1	FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу визитов

Необходимо вставить путь к базе данных и таблице визитов для своего кластера ClickHouse. Чтобы узнать его, перейдите в свой проект Yandex Cloud и откройте сервис Managed Service for ClickHouse. Нажмите на название своего кластера:

Выбор кластера ClickHouse

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

Подключение к ClickHouse

В следующем окне провалитесь внутрь вашей базы данных. Там вы должны увидеть таблицу визитов с данными вашего счетчика Яндекс.Метрики:

Таблица визитов в ClickHouse

У вас будет другое название этой таблицы, поскольку идентификатор, следующий за префиксом visits_, для каждого проекта уникальный. Скопируйте название таблицы визитов и перед ней добавьте название базы данных, разделив их точкой (без пробелов!). Для моего проекта - это db1.visits_dtta54kkhn59qe3i94jj . Вставьте полученное значение вместо шаблона yandex_data_transfer_test.visits_dttql4la13mb206q472r.

Следующее, что вам потребуется заменить - это диапазон дат. В строке:

WHERE StartDate = today() - 1 --сюда вставить любую дату, например, StartDate = toDate('2022-02-01') или StartDate = '2022-02-01'

1	WHERE StartDate = today() - 1 --сюда вставить любую дату, например, StartDate = toDate('2022-02-01') или StartDate = '2022-02-01'

В качестве примера я укажу 1 сентября 2023 года (2023-09-01).

Поскольку Яндекс не знает идентификатора вашего счетчика Метрики, то вам необходимо вручную задать его в запросе. Для этого измените значение 24226447 в скобках в строке:

CounterID = toUInt32(24226447)

1	CounterID = toUInt32(24226447)

На идентификатор собственного счетчика Яндекс.Метрики. Чтобы найти его значение, перейдите по ссылке и скопируйте идентификатор напротив нужного счетчика:

Идентификатор счетчика Яндекс.Метрики

Это ключевые изменения, которые вы должны делать всегда и для каждого запроса, когда будете копировать код из официальной документации Яндекса или из какого-либо другого источника информации. В том числе и для всех последующих примеров в этом руководстве. Обязательно запомните - изменяем:

идентификатор счетчика;
путь к базе данных и таблице с данными;
диапазон дат.

Если вы являетесь опытным пользователем и умеете составлять SQL-запросы, то вы также можете отредактировать сам запрос, убрав лишние поля из представленного кода. В противном случае саму структуру запроса оставьте без изменений.

Теперь этот запрос можно выполнить и посмотреть на результат. Как я писал ранее, мы будем это делать в DBeaver и/или DataLens.

Чтобы выполнить SQL-запрос в DBeaver, правой кнопкой мыши нажмите на базу данных или само подключение, и выберите Редактор SQL - Новый редактор SQL:

Редактор SQL - Новый редактор SQL

В открывшемся окне вы можете написать SQL-запрос. Вставьте вышеприведенный код с данными, которые вы заменили на свои:

Пример запроса со множеством полей (по визитам)

Чтобы выполнить команду, нажмите на оранжевую иконку или сочетание клавиш Ctrl+Enter:

Выполнение SQL-запроса

Под окном запроса вы увидите результат выполнения программы:

Результат выполнения

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

Он будет содержать множество полей, которое можно выгрузить в тарифе Метрика Про. По столбцу FirstPartyCookie можно подсчитать количество строк и сопоставить полученный результат с данными Яндекс.Метрики. Количество визитов по уникальному идентификатору пользователя за выбранный диапазон дат должно +/- сходиться.

Сравнение статистики визитов по сырым данным и в интерфейсе Метрики

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

визиты могут обновляться в прошлое, например при привязывании офлайн-конверсии;
при включении интеграции у самых первых визитов sum(Sign) может оказаться неконсистентным.

В логах коннектора лежат несколлапсированные версии визитов (т.е. несколько версий одного визита). Если их не коллапсировать, то данные будут неконсистентыми и один и тот же визит (его старые версии) будет учитываться более 1 раза. Это происходит, потому что при обновлении визита старая версия (VisitVersion = 1, Sign = 1) не удаляется. Вместо этого добавляется полностью идентичная старой версии строчка, с разницей лишь в знаке Sign (VisitVersion = 1, Sign = -1). Далее добавляется уже обновленная версия визита с положительным Sign (VisitVersion = 2, Sign = 1). Таким образом, сделав group by по интересующим полям, вы получите правильное и корректное число визитов через sum(Sign) за счет «схлопывания» неактуальных версий (Sign = 1 и Sign = -1 превратится в ноль).

Вот так это выглядит в таблице с данными в ClickHouse:

VisitID, Sign, VisitVersion

Один визит с VisitID имеет несколько строк в таблице с разными значениями Sign и VisitVersion, и старая версия из таблицы не удаляется.

Далее в справке Яндекса приводится несколько примеров.

Пример несколлапсированного визита в логах визита

select VisitID, VisitVersion, Sign
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r  -- сюда вставить свою базу и свою таблицу визитов
where StartDate = today()-3 -- сюда можно вставить любую дату
    AND (CounterID = toUInt32(24226447)) -- сюда вставить свой номер счетчика
    and VisitID in (select VisitID
                    from yandex_data_transfer_test.visits_dttql4la13mb206q472r  -- сюда вставить свою базу и свою таблицу визитов
                    where StartDate = today()-3 -- сюда можно вставить любую дату
                    group by VisitID
                    having count(distinct VisitVersion) > 3 -- для наглядности берем визит с более чем тремя изменениями (можно взять любое количество)
                    order by VisitID desc
                    limit 1 -- для наглядности берем 1 визит, можно взять больше
                    ) 
order by VisitID, VisitVersion, Sign

select VisitID, VisitVersion, Sign

FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу визитов

where StartDate = today()-3 -- сюда можно вставить любую дату

AND (CounterID = toUInt32(24226447)) -- сюда вставить свой номер счетчика

and VisitID in (select VisitID

from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу визитов

where StartDate = today()-3 -- сюда можно вставить любую дату

group by VisitID

having count(distinct VisitVersion) > 3 -- для наглядности берем визит с более чем тремя изменениями (можно взять любое количество)

order by VisitID desc

limit 1 -- для наглядности берем 1 визит, можно взять больше

)

order by VisitID, VisitVersion, Sign

В DBeaver это сам запрос и результат его выполнения будет выглядеть так:

Пример несколлапсированного визита в логах визита

Если ваш запрос выдает пустой результат, попробуйте изменить значение count(distinct VisitVersion) > 3 на меньшее, например, 1 или 2.

Пример с правильным коллапсированием

select VisitID, sum(Sign) as visits
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r  -- сюда вставить свою базу и свою таблицу визитов
where StartDate = today()-3 -- сюда можно вставить любую дату
    AND (CounterID = toUInt32(24226447)) -- сюда вставить свой номер счетчика
    and VisitID = 1243431264677003301
group by VisitID

select VisitID, sum(Sign) as visits

FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу визитов

where StartDate = today()-3 -- сюда можно вставить любую дату

AND (CounterID = toUInt32(24226447)) -- сюда вставить свой номер счетчика

and VisitID = 1243431264677003301

group by VisitID

Результат выполнения SQL-запроса:

Пример с правильным коллапсированием

Пример коллапсирования с использованием конструкции final после названия таблицы

final обрабатывает таблицу таким образом, чтобы версии уже были сколлапсированы. Убирает неактуальные версии визитов сам. Работает гораздо дольше, чем при подсчете через группировку и sum(Sign).

Пример SQL-запроса:

select VisitID, VisitVersion, Sign
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r final  -- сюда вставить свою базу и свою таблицу визитов
where StartDate = today()-3 -- сюда можно вставить любую дату
    AND (CounterID = toUInt32(24226447))
    and VisitID = 1243431264677003301
order by VisitID, VisitVersion, Sign

select VisitID, VisitVersion, Sign

FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- сюда вставить свою базу и свою таблицу визитов

where StartDate = today()-3 -- сюда можно вставить любую дату

AND (CounterID = toUInt32(24226447))

and VisitID = 1243431264677003301

order by VisitID, VisitVersion, Sign

Результат его выполнения:

Пример коллапсирования с использованием final

Как выгружать хиты

Хит (hit) - это событие, которое учитывается Метрикой. Такими событиями являются:

просмотры;
достижение любой цели, созданной на счетчике;
отправка параметров визитов.

Хитом не считаются события, которые передаются при инициализации счетчика.

В официальной справке Яндекса представлен следующий пример запроса:

select CounterID,
        EventDate,
        CounterUserIDHash, -- внутренний UserID, с которым работает Метрика
        FirstPartyCookie, -- то же самое, что и ClientID в LogsAPI
        UTCEventTime,
        WatchID,
        Referer,
        OriginalURL,  -- URL, в отличие от OriginalURL, может быть обрезан
        URL,
        UTMSource,
        IsMobile,
        OS,
        OSFamily,
        OSName,
        FirstPartyCookie,
        IsArtifical,
        IsDownload,
        IsLink,
        IsNotBounce,
        IsPageView,
        IsParameter
from yandex_data_transfer_test.hits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу хитов
where EventDate = today()-1 -- сюда вставить любую дату
   and CounterID = 24226447 -- сюда вставить номер своего счетчика

select CounterID,

EventDate,

CounterUserIDHash, -- внутренний UserID, с которым работает Метрика

FirstPartyCookie, -- то же самое, что и ClientID в LogsAPI

UTCEventTime,

WatchID,

Referer,

OriginalURL, -- URL, в отличие от OriginalURL, может быть обрезан

URL,

UTMSource,

IsMobile,

OS,

OSFamily,

OSName,

FirstPartyCookie,

IsArtifical,

IsDownload,

IsLink,

IsNotBounce,

IsPageView,

IsParameter

from yandex_data_transfer_test.hits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу хитов

where EventDate = today()-1 -- сюда вставить любую дату

and CounterID = 24226447 -- сюда вставить номер своего счетчика

, где вам необходимо изменить:

yandex_data_transfer_test.hits_dttql4la13mb206q472r на свою базу данных а таблицу с хитами;

Путь к базе данных и таблице хитов в ClickHouse можно посмотреть точно так же, как и для таблицы визитов:

перейдите в свой проект Yandex Cloud и откройте сервис Managed Service for ClickHouse. Нажмите на название своего кластера;
на открывшейся странице выберите SQL, а затем введите имя пользователя и пароль и подключитесь к своей базе данных;
в следующем окне провалитесь внутрь вашей базы данных. Там вы должны увидеть таблицу хитов с данными вашего счетчика Яндекс.Метрики. Таблица хитов имеет префикс hits_

Таблица хитов в ClickHouse

В строке where EventDate = today()-1 вставьте нужную дату, а в CounterID = 24226447 измените значение на идентификатор своего счетчика Яндекс.Метрики.

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

Результат выполнения

Как соотнести визит с его хитами

Для соотнесения VisitID с принадлежащими ему хитами (WatchID) может быть недостаточно колонки WatchIDs. Это связано с тем, что в поле WatchID отсутствуют хиты-параметры визитов. Вы можете самостоятельно собрать список хитов, которые входят в визит. Для этого нужна дата начала визита, дата окончания визита и идентификатор посетителя.

SQL-запрос:

select VisitID, -- в этом select будут визиты с собранном массивом его хитов
        CounterUserIDHash,
        UTCStartTime,
        Duration,
        UTCEndTime,
        groupArray(WatchID) as `watchids.id`,
        groupArray(IsPageView) as `watchids.is_page_view`,
        groupArray(IsParameter) as `watchids.is_parameter`,
        groupArray(UTCEventTime) as `watchids.is_utc_event_time`
from ( -- в этом select будут визиты, размноженные по каждому хиту
select VisitID,
        a.CounterUserIDHash as CounterUserIDHash,
        UTCStartTime,
        Duration,
        UTCEndTime,
        WatchID,
        IsPageView,
        IsParameter,
        UTCEventTime
from 
(select -- берем визиты, юзера, дату начала и дату окончания визита. Визит может продолжать обновляться в прошлое!
    VisitID,
    CounterUserIDHash,
    UTCStartTime,
    Duration,
    toDateTime(UTCStartTime) + Duration as UTCEndTime
from yandex_data_transfer_test.visits_dttql4la13mb206q472r final
where StartDate = '2023-04-01'
) as a
left join
(select -- берем хиты, юзера, дату и время хита. Хиты не обновляются в прошлое
    WatchID,
    CounterUserIDHash,
    IsPageView,
    IsParameter,
    UTCEventTime
from yandex_data_transfer_test.hits_dttql4la13mb206q472r
where EventDate >= '2023-04-01' 
        and EventDate <= toDate('2023-04-01')+5 ) as b on a.CounterUserIDHash = b.CounterUserIDHash -- важно ориентироваться именно на данный идентификатор посетителя where UTCEventTime >= UTCStartTime and -- хиты со временем не раньше, чем начало визита 
        UTCEventTime <= UTCEndTime -- хиты со временем не позже, чем конец визита 
order by CounterUserIDHash, UTCEventTime
)
group by VisitID,
        CounterUserIDHash,
        UTCStartTime,
        Duration,
        UTCEndTime
limit 100

select VisitID, -- в этом select будут визиты с собранном массивом его хитов

CounterUserIDHash,

UTCStartTime,

Duration,

UTCEndTime,

groupArray(WatchID) as `watchids.id`,

groupArray(IsPageView) as `watchids.is_page_view`,

groupArray(IsParameter) as `watchids.is_parameter`,

groupArray(UTCEventTime) as `watchids.is_utc_event_time`

from ( -- в этом select будут визиты, размноженные по каждому хиту

select VisitID,

a.CounterUserIDHash as CounterUserIDHash,

UTCStartTime,

Duration,

UTCEndTime,

WatchID,

IsPageView,

IsParameter,

UTCEventTime

from

(select -- берем визиты, юзера, дату начала и дату окончания визита. Визит может продолжать обновляться в прошлое!

VisitID,

CounterUserIDHash,

UTCStartTime,

Duration,

toDateTime(UTCStartTime) + Duration as UTCEndTime

from yandex_data_transfer_test.visits_dttql4la13mb206q472r final

where StartDate = '2023-04-01'

) as a

left join

(select -- берем хиты, юзера, дату и время хита. Хиты не обновляются в прошлое

WatchID,

CounterUserIDHash,

IsPageView,

IsParameter,

UTCEventTime

from yandex_data_transfer_test.hits_dttql4la13mb206q472r

where EventDate >= '2023-04-01'

and EventDate <= toDate('2023-04-01')+5 ) as b on a.CounterUserIDHash = b.CounterUserIDHash -- важно ориентироваться именно на данный идентификатор посетителя where UTCEventTime >= UTCStartTime and -- хиты со временем не раньше, чем начало визита

UTCEventTime <= UTCEndTime -- хиты со временем не позже, чем конец визита

order by CounterUserIDHash, UTCEventTime

)

group by VisitID,

CounterUserIDHash,

UTCStartTime,

Duration,

UTCEndTime

limit 100

В этом запросе вам нужно изменить значения примеров базы данных и таблиц на свои:

from yandex_data_transfer_test.visits_dttql4la13mb206q472r на свою таблицу визитов и базу данных;
from yandex_data_transfer_test.hits_dttql4la13mb206q472r на свою таблицу хитов и базу данных;

А а также скорректировать дату выгрузку в нескольких местах:

where StartDate = '2023-04-01'
EventDate >= '2023-04-01'
and EventDate <= toDate('2023-04-01')+5 )

Результат выполнения кода в DBeaver будет выглядеть так:

Результат объединения данных таблиц визитов и хитов

Отчет “Посещаемость”

В официальной справке представлены шаблоны запросов для построения стандартных и более сложных отчетов, которых нет в интерфейсе Яндекс.Метрики. Мы пройдемся по ним всем и попробуем повторить их на неагрегированных данных.

И первый отчет, который мы воспроизведем - отчет по посещаемости.

SQL-запрос для построения QL-чарта:

SELECT StartDate AS `ym:s:date`, 
        sum(Sign) AS `ym:s:visits` -- правильное коллапсирование нескольких версий визита в самую последнюю и актуальную, и подсчет количества визитов
from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу визитов
as `default.visits_all` 
WHERE `ym:s:date` >= toDate('2023-01-31') -- исторические данные до момента создания коннектора в данной версии не поддерживаются
        and `ym:s:date` <= toDate('2023-02-06') -- данные за "сегодня" (и медленные обновления за более поздние дни, например, оффлайн конверсии) могут доезжать с опозданием относительно интерфейса and CounterID = 24226447 -- поменять на свой номер счетчика GROUP BY `ym:s:date` WITH TOTALS HAVING `ym:s:visits` >= 0.0 
ORDER BY `ym:s:date` ASC 
limit 0,7

SELECT StartDate AS `ym:s:date`,

sum(Sign) AS `ym:s:visits` -- правильное коллапсирование нескольких версий визита в самую последнюю и актуальную, и подсчет количества визитов

from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу визитов

as `default.visits_all`

WHERE `ym:s:date` >= toDate('2023-01-31') -- исторические данные до момента создания коннектора в данной версии не поддерживаются

and `ym:s:date` <= toDate('2023-02-06') -- данные за "сегодня" (и медленные обновления за более поздние дни, например, оффлайн конверсии) могут доезжать с опозданием относительно интерфейса and CounterID = 24226447 -- поменять на свой номер счетчика GROUP BY `ym:s:date` WITH TOTALS HAVING `ym:s:visits` >= 0.0

ORDER BY `ym:s:date` ASC

limit 0,7

Результат выполнения в DBeaver:

Количество визитов за конкретный день

Вы можете сравнить данные, полученные после выполнения SQL-команды, с интерфейсными. Они должны сходиться со статистикой в отчете "Посещаемость":

Данные в отчете "Посещаемость"

В моем примере количество визитов сходятся (968 за 1 сентября 2023 года). Вы можете изменить диапазон дат, указав больший период выгрузки, а не только за один день. Например, с 1 по 7 сентября 2023 года:

Выгрузка данных за больший диапазон дат

Чтобы построить график по визитам в Yandex DataLens, вам необходимо перейти по ссылке и в меню Чарты выбрать Создать чарт - QL-чарт:

Создание QL-чарта

Затем выберите типа чарта SQL и подключение к своему кластеру ClickHouse. В завершение нажмите кнопку Создать:

Выбор подключения к ClickHouse

На открывшейся странице в левой ее части вы можете писать свой SQL-запрос, а в правой получать результат его выполнения в виде выбранной визуализации (чарта):

Окно создания QL-чарта в Yandex DataLens

Давайте построим самый простой график по посещаемости, выбрав тип чарта Линейная диаграмма. Вставьте вышеприведенный код в окно запроса и нажмите кнопку Запустить. В правой части вы увидите итоговый график по визитам, очень схожий на то, что представлено в интерфейсе Яндекс.Метрики:

График посещаемости на основе сырых данных Яндекс.Метрики

Вы можете изменить тип чарта на Столбчатая диаграмма, и тогда ваша визуализация будет такой же, как в интерфейсе Метрики для этого отчета.

Столбчатая диаграмма

Поздравляю! Вы только что создали свою первую визуализацию на основе неагрегированных данных с помощью SQL-запроса. Чтобы сохранить график по визитам и в дальнейшем использовать его на своем дашборде DataLens, в правом верхнем углу нажмите кнопку Сохранить. Задайте QL-чарту название и сохраните его в нужной папке.

Помимо графика в отчете по посещаемости есть таблица с данными различных показателей. SQL-запрос для нее будет таким:

SELECT
    toDate(StartDate) AS `ym:s:datePeriodday`,
    sum(Sign) AS `ym:s:visits`,
    uniqExact(CounterUserIDHash) AS `ym:s:users`,
    sum(PageViews * Sign) AS `ym:s:pageviews`,
    uniqExactIf(CounterUserIDHash, (`TrafficSource.StartTime`[indexOf(`TrafficSource.Model`, 3)]) = (`TrafficSource.StartTime`[indexOf(`TrafficSource.Model`, 1)])) / uniqExact(CounterUserIDHash) * 100. AS `ym:s:percentNewVisitors`, -- атрибуция 3 - это атрибуция "Первое посещение", 1 - это "Последнее посещение"
    100. * (sum(IsBounce * Sign) / `ym:s:visits`) AS `ym:s:bounceRate`,
    `ym:s:pageviews` / `ym:s:visits` AS `ym:s:pageDepth`,
    sum(Duration * Sign) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`
   -- метрики Роботность и Кросс-девайс посетители недоступны в коннекторе
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу визитов
AS `default.visits_all`
WHERE (StartDate >= toDate('2023-03-10'))  
        AND (StartDate <= toDate('2023-03-16')) AND (CounterID = toUInt32(24226447)) -- поменять на свой номер счетчика GROUP BY `ym:s:datePeriodday` WITH TOTALS HAVING (`ym:s:visits` > 0.) OR (`ym:s:users` > 0.) OR (`ym:s:pageviews` > 0.)
ORDER BY `ym:s:datePeriodday` DESC
LIMIT 0, 50

SELECT

toDate(StartDate) AS `ym:s:datePeriodday`,

sum(Sign) AS `ym:s:visits`,

uniqExact(CounterUserIDHash) AS `ym:s:users`,

sum(PageViews * Sign) AS `ym:s:pageviews`,

uniqExactIf(CounterUserIDHash, (`TrafficSource.StartTime`[indexOf(`TrafficSource.Model`, 3)]) = (`TrafficSource.StartTime`[indexOf(`TrafficSource.Model`, 1)])) / uniqExact(CounterUserIDHash) * 100. AS `ym:s:percentNewVisitors`, -- атрибуция 3 - это атрибуция "Первое посещение", 1 - это "Последнее посещение"

100. * (sum(IsBounce * Sign) / `ym:s:visits`) AS `ym:s:bounceRate`,

`ym:s:pageviews` / `ym:s:visits` AS `ym:s:pageDepth`,

sum(Duration * Sign) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`

-- метрики Роботность и Кросс-девайс посетители недоступны в коннекторе

FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу визитов

AS `default.visits_all`

WHERE (StartDate >= toDate('2023-03-10'))

AND (StartDate <= toDate('2023-03-16')) AND (CounterID = toUInt32(24226447)) -- поменять на свой номер счетчика GROUP BY `ym:s:datePeriodday` WITH TOTALS HAVING (`ym:s:visits` > 0.) OR (`ym:s:users` > 0.) OR (`ym:s:pageviews` > 0.)

ORDER BY `ym:s:datePeriodday` DESC

LIMIT 0, 50

Результат выполнения этого кода в DBeaver:

Таблица с данными о посещаемости за выбранный диапазон дат

Вы можете скопировать SQL-запрос и добавить его в DataLens, выбрав в качестве чарта тип Таблица:

QL-чарт по посещаемости в DataLens

Сохранив эту таблицу как отдельный QL-чарт, вы сможете в дальнейшем использовать ее в дашборде.

Отчет “UTM метки”

Пример запроса из официальной справки Яндекса (таблица визитов):

SELECT
    `TrafficSource.UTMSource`[indexOf(`TrafficSource.Model`, 2)] AS `ym:s:lastSignUTMSource`,
    sum(Sign) AS `ym:s:visits`,
    least(uniqExact(CounterUserIDHash), `ym:s:visits`) AS `ym:s:users`,
    100. * (sum(IsBounce * Sign) / `ym:s:visits`) AS `ym:s:bounceRate`,
    sum(PageViews * Sign) / `ym:s:visits` AS `ym:s:pageDepth`,
    sum(Duration * Sign) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`,
    sumArray(arrayMap(x -> (if(isFinite(x), x, 0) * Sign), arrayMap(x_0 -> toInt64(notEmpty(x_0)), `EPurchase.ID`))) AS `ym:s:ecommercePurchases`
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r  -- сюда вставить свою базу и свою таблицу визитов
WHERE (StartDate >= toDate('2023-03-10')) 
        AND (StartDate <= toDate('2023-03-16')) AND (CounterID = 24226447) -- сюда вставить свой номер счетчика AND (`ym:s:lastSignUTMSource` != '') GROUP BY `ym:s:lastSignUTMSource` HAVING (`ym:s:visits` > 0.) OR (`ym:s:users` > 0.) OR (`ym:s:ecommercePurchases` > 0.)
ORDER BY
    `ym:s:visits` DESC,
    `ym:s:lastSignUTMSource` ASC
LIMIT 0, 50

SELECT

`TrafficSource.UTMSource`[indexOf(`TrafficSource.Model`, 2)] AS `ym:s:lastSignUTMSource`,

sum(Sign) AS `ym:s:visits`,

least(uniqExact(CounterUserIDHash), `ym:s:visits`) AS `ym:s:users`,

100. * (sum(IsBounce * Sign) / `ym:s:visits`) AS `ym:s:bounceRate`,

sum(PageViews * Sign) / `ym:s:visits` AS `ym:s:pageDepth`,

sum(Duration * Sign) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`,

sumArray(arrayMap(x -> (if(isFinite(x), x, 0) * Sign), arrayMap(x_0 -> toInt64(notEmpty(x_0)), `EPurchase.ID`))) AS `ym:s:ecommercePurchases`

FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу визитов

WHERE (StartDate >= toDate('2023-03-10'))

AND (StartDate <= toDate('2023-03-16')) AND (CounterID = 24226447) -- сюда вставить свой номер счетчика AND (`ym:s:lastSignUTMSource` != '') GROUP BY `ym:s:lastSignUTMSource` HAVING (`ym:s:visits` > 0.) OR (`ym:s:users` > 0.) OR (`ym:s:ecommercePurchases` > 0.)

ORDER BY

`ym:s:visits` DESC,

`ym:s:lastSignUTMSource` ASC

LIMIT 0, 50

Результат выполнения в DBeaver:

Таблица с данными по UTM-меткам в DBeaver

Запуск запроса в DataLens:

QL-чарт с данными по UTM-меткам в DataLens

Сохранив эту таблицу как отдельный QL-чарт, вы сможете в дальнейшем использовать ее в дашборде.

Отчет “Популярное”

Пример запроса в официальной документации Яндекса (таблица хитов):

SELECT
    URLHash(URL, toInt8(0)) AS `ym:pv:URLPathLevel1Hash`,
    anyHeavyIf(domain(URL), domain(URL) != '') AS `ym:pv:URLPathLevel1HashFavicon`,
    max(URLHierarchy(ifNull(URL, ''))[1]) AS `ym:pv:URLPathLevel1`,
    count() AS `ym:pv:pageviews`,
    least(uniq(CounterUserIDHash), `ym:pv:pageviews`) AS `ym:pv:users`
FROM yandex_data_transfer_test.hits_dttql4la13mb206q472r AS `default.hits_all`
WHERE (EventDate >= toDate('2023-03-10')) 
        AND (EventDate <= toDate('2023-03-16')) 
        and CounterID = 24226447 -- сюда вставить номер своего счетчика
       and IsPageView
GROUP BY `ym:pv:URLPathLevel1Hash`
    WITH TOTALS
ORDER BY
    `ym:pv:pageviews` DESC,
    `ym:pv:URLPathLevel1` ASC,
    `ym:pv:URLPathLevel1Hash` ASC
LIMIT 0, 50

SELECT

URLHash(URL, toInt8(0)) AS `ym:pv:URLPathLevel1Hash`,

anyHeavyIf(domain(URL), domain(URL) != '') AS `ym:pv:URLPathLevel1HashFavicon`,

max(URLHierarchy(ifNull(URL, ''))[1]) AS `ym:pv:URLPathLevel1`,

count() AS `ym:pv:pageviews`,

least(uniq(CounterUserIDHash), `ym:pv:pageviews`) AS `ym:pv:users`

FROM yandex_data_transfer_test.hits_dttql4la13mb206q472r AS `default.hits_all`

WHERE (EventDate >= toDate('2023-03-10'))

AND (EventDate <= toDate('2023-03-16'))

and CounterID = 24226447 -- сюда вставить номер своего счетчика

and IsPageView

GROUP BY `ym:pv:URLPathLevel1Hash`

WITH TOTALS

ORDER BY

`ym:pv:pageviews` DESC,

`ym:pv:URLPathLevel1` ASC,

`ym:pv:URLPathLevel1Hash` ASC

LIMIT 0, 50

Результат выполнения в DBeaver:

Таблица с данными по популярным страницам сайта в DBeaver

Запуск запроса в DataLens:

QL-чарт с данными по популярным страницам в DataLens

Сохранив эту таблицу как отдельный QL-чарт, вы сможете в дальнейшем использовать ее в дашборде.

Отчет “Конверсия цели”

Если вы хотите получить статистику по конкретной цели вашего счетчика Яндекс.Метрика, то используйте нижеприведенный SQL-запрос:

WITH 1. AS W, 17069575 as my_goal_id -- поменять на номер своей цели
SELECT
    toDate(StartDate) AS `ym:s:datePeriodday`,
    100. * (sum(has(`Goals.ID`, my_goal_id) * (Sign * W)) / sum(Sign * W)) AS `ym:s:goal17069575conversionRate`,
    sum(arrayCount(x -> (my_goal_id = x), `Goals.ID`) * (Sign * W)) AS `ym:s:goal17069575reaches`,
    sumIf(Sign * W, arrayExists(x_0 -> (x_0 = my_goal_id), `Goals.ID`)) AS `ym:s:goal17069575visits`,
    least(toFloat64(uniqIf(CounterUserIDHash, arrayExists(x_0 -> (x_0 = my_goal_id), `Goals.ID`))), `ym:s:goal17069575visits`) AS `ym:s:goal17069575users`,
    sumIf(PageViews * (Sign * W), arrayExists(x_0 -> (x_0 = my_goal_id), `Goals.ID`)) AS `ym:s:goal17069575pageviews`,
    (least(uniqIf(CounterUserIDHash, ((`TrafficSource.StartTime`[indexOf(`TrafficSource.Model`, 3)]) = (`TrafficSource.StartTime`[indexOf(`TrafficSource.Model`, 1)])) AND arrayExists(x_0 -> (x_0 = my_goal_id), `Goals.ID`)), uniqIf(CounterUserIDHash, arrayExists(x_0 -> (x_0 = my_goal_id), `Goals.ID`))) / uniqIf(CounterUserIDHash, arrayExists(x_0 -> (x_0 = my_goal_id), `Goals.ID`))) * 100. AS `ym:s:goal17069575percentNewVisitors`,
    100. * (sumIf(IsBounce * (Sign * W), arrayExists(x_0 -> (x_0 = my_goal_id), `Goals.ID`)) / `ym:s:goal17069575visits`) AS `ym:s:goal17069575bounceRate`,
    `ym:s:goal17069575pageviews` / `ym:s:goal17069575visits` AS `ym:s:goal17069575pageDepth`,
    sumIf(Duration * (Sign * W), arrayExists(x_0 -> (x_0 = my_goal_id), `Goals.ID`)) / `ym:s:goal17069575visits` AS `ym:s:goal17069575avgVisitDurationSeconds`
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу визитов
WHERE (StartDate >= toDate('2023-02-18')) 
        AND (StartDate <= toDate('2023-03-17')) AND (CounterID = 24226447) -- поменять на свой номер счетчика GROUP BY `ym:s:datePeriodday` HAVING (`ym:s:goal17069575reaches` > 0.) AND ((`ym:s:goal17069575reaches` > 0.) OR (`ym:s:goal17069575visits` > 0.) OR (`ym:s:goal17069575users` > 0.) OR (`ym:s:goal17069575pageviews` > 0.))
ORDER BY `ym:s:datePeriodday` DESC
LIMIT 0, 50

WITH 1. AS W, 17069575 as my_goal_id -- поменять на номер своей цели

SELECT

toDate(StartDate) AS `ym:s:datePeriodday`,

100. * (sum(has(`Goals.ID`, my_goal_id) * (Sign * W)) / sum(Sign * W)) AS `ym:s:goal17069575conversionRate`,

sum(arrayCount(x -> (my_goal_id = x), `Goals.ID`) * (Sign * W)) AS `ym:s:goal17069575reaches`,

sumIf(Sign * W, arrayExists(x_0 -> (x_0 = my_goal_id), `Goals.ID`)) AS `ym:s:goal17069575visits`,

least(toFloat64(uniqIf(CounterUserIDHash, arrayExists(x_0 -> (x_0 = my_goal_id), `Goals.ID`))), `ym:s:goal17069575visits`) AS `ym:s:goal17069575users`,

sumIf(PageViews * (Sign * W), arrayExists(x_0 -> (x_0 = my_goal_id), `Goals.ID`)) AS `ym:s:goal17069575pageviews`,

(least(uniqIf(CounterUserIDHash, ((`TrafficSource.StartTime`[indexOf(`TrafficSource.Model`, 3)]) = (`TrafficSource.StartTime`[indexOf(`TrafficSource.Model`, 1)])) AND arrayExists(x_0 -> (x_0 = my_goal_id), `Goals.ID`)), uniqIf(CounterUserIDHash, arrayExists(x_0 -> (x_0 = my_goal_id), `Goals.ID`))) / uniqIf(CounterUserIDHash, arrayExists(x_0 -> (x_0 = my_goal_id), `Goals.ID`))) * 100. AS `ym:s:goal17069575percentNewVisitors`,

100. * (sumIf(IsBounce * (Sign * W), arrayExists(x_0 -> (x_0 = my_goal_id), `Goals.ID`)) / `ym:s:goal17069575visits`) AS `ym:s:goal17069575bounceRate`,

`ym:s:goal17069575pageviews` / `ym:s:goal17069575visits` AS `ym:s:goal17069575pageDepth`,

sumIf(Duration * (Sign * W), arrayExists(x_0 -> (x_0 = my_goal_id), `Goals.ID`)) / `ym:s:goal17069575visits` AS `ym:s:goal17069575avgVisitDurationSeconds`

FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу визитов

WHERE (StartDate >= toDate('2023-02-18'))

AND (StartDate <= toDate('2023-03-17')) AND (CounterID = 24226447) -- поменять на свой номер счетчика GROUP BY `ym:s:datePeriodday` HAVING (`ym:s:goal17069575reaches` > 0.) AND ((`ym:s:goal17069575reaches` > 0.) OR (`ym:s:goal17069575visits` > 0.) OR (`ym:s:goal17069575users` > 0.) OR (`ym:s:goal17069575pageviews` > 0.))

ORDER BY `ym:s:datePeriodday` DESC

LIMIT 0, 50

В нем вам не просто нужно поменять даты, идентификатор счетчика и название таблицы визитов и базы данных, но еще и указать идентификатор цели. Узнать его можно в интерфейсе Яндекс.Метрики в разделе Цели:

Номер цели в интерфейсе Метрики

Напротив нужной цели будет написан ее номер (ID). Вам нужно скопировать его и вставить в запросе в несколько мест, заменив шаблонное значение goal17069575 на свое goalXXXXXXXX, где XXXXXXXX - ваш идентификатор цели.

В DBeaver очень легко заменить все значения на новые, используя функцию замены с помощью клавиш Ctrl+F. В результате вы получите следующий результат:

Таблица с данными по выбранной конверсии в DBeaver

Запуск запроса в DataLens:

QL-чарт с данными по выбранной конверсии в DataLens

Сохранив эту таблицу как отдельный QL-чарт, вы сможете в дальнейшем использовать ее в дашборде.

Отчет “Источники, сводка”

Следующий отчет, который мы построим с вами на сырых данных - это Источники, сводка. Он будет в двух модификациях - таблица и таблица, детально. Точно так же, как и в интерфейсе Яндекс.Метрики, где в отчете Источники, сводка присутствует две группировки - Источник трафика, Источник трафика (детально):

Отчет "Источники, сводка" (Источник трафика, Источник трафика (детально))

Сначала просто табличный вариант (таблица визитов):

WITH 1. AS W
SELECT
    `TrafficSource.ID`[indexOf(`TrafficSource.Model`, 2)] AS `ym:s:lastSignTrafficSource`,
    sum(Sign * W) AS `ym:s:visits`,
    least(toFloat64(uniq(CounterUserIDHash)), `ym:s:visits`) AS `ym:s:users`,
    100. * (sum(IsBounce * (Sign * W)) / `ym:s:visits`) AS `ym:s:bounceRate`,
    sum(PageViews * (Sign * W)) / `ym:s:visits` AS `ym:s:pageDepth`,
    sum(Duration * (Sign * W)) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`
from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу визитов
WHERE (StartDate >= toDate('2023-03-10')) 
        AND (StartDate <= toDate('2023-03-16')) AND (CounterID = 24226447) -- поменять на свой номер счетчика GROUP BY `ym:s:lastSignTrafficSource` WITH TOTALS HAVING (`ym:s:visits` > 0.) OR (`ym:s:users` > 0.)
ORDER BY
    `ym:s:visits` DESC,
    `ym:s:lastSignTrafficSource` ASC
LIMIT 0, 50

WITH 1. AS W

SELECT

`TrafficSource.ID`[indexOf(`TrafficSource.Model`, 2)] AS `ym:s:lastSignTrafficSource`,

sum(Sign * W) AS `ym:s:visits`,

least(toFloat64(uniq(CounterUserIDHash)), `ym:s:visits`) AS `ym:s:users`,

100. * (sum(IsBounce * (Sign * W)) / `ym:s:visits`) AS `ym:s:bounceRate`,

sum(PageViews * (Sign * W)) / `ym:s:visits` AS `ym:s:pageDepth`,

sum(Duration * (Sign * W)) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`

from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу визитов

WHERE (StartDate >= toDate('2023-03-10'))

AND (StartDate <= toDate('2023-03-16')) AND (CounterID = 24226447) -- поменять на свой номер счетчика GROUP BY `ym:s:lastSignTrafficSource` WITH TOTALS HAVING (`ym:s:visits` > 0.) OR (`ym:s:users` > 0.)

ORDER BY

`ym:s:visits` DESC,

`ym:s:lastSignTrafficSource` ASC

LIMIT 0, 50

Результат выполнения в DBeaver:

Таблица с данными по источникам трафика в DBeaver

В столбце ym:s:lastSignTrafficSource отображаются не сами источники трафика, а их индексы - 1, 2, -1, 0, 9 и т.д. Значения в полях означают:

-1: Внутренние переходы;
0: Прямые заходы;
1: Переходы по ссылкам на сайтах;
2: Переходы из поисковых систем;
3: Переходы по рекламе;
4: Переходы с сохраненных страниц;
5: Не определен;
6: Переходы по внешним ссылкам;
7: Переходы с почтовых рассылок;
8: Переходы из соц сетей;
9: Переходы из рекомендательных систем;
10: Переходы из мессенджеров;
11: Переходы по QR коду.

Вы можете сравнить полученные данные со статистикой в интерфейсе по данному отчету:

Отчет "Источники, сводка"

Незначительные расхождения в данных могут присутствовать, так как в интерфейсе статистика строится на основе API отчетов, а вы используете Logs API.

Запрос в DataLens:

QL-чарт с данными по источникам трафика в DataLens

Другой SQL-запрос позволяет детальнее узнать про источники трафика (таблица визитов):

WITH 1. AS W
SELECT
    `TrafficSource.ID`[indexOf(`TrafficSource.Model`, 2)] AS `ym:s:lastSignTrafficSource`,
    `TrafficSource.StrID`[indexOf(`TrafficSource.Model`, 2)] AS `ym:s:lastSignTrafficSourceName`,
     
    if(
        ((`TrafficSource.Domain`[indexOf(`TrafficSource.Model`, 2)]) != '') AND 
            (`ym:s:lastSignTrafficSource` IN (-1, toInt8(1))), 
        `TrafficSource.Domain`[indexOf(`TrafficSource.Model`, 2)], 
        if(`ym:s:lastSignTrafficSource` = toInt8(2), 
            `TrafficSource.SearchEngineStrID`[indexOf(`TrafficSource.Model`, 2)], 
            if(`ym:s:lastSignTrafficSource` = toInt8(3), `TrafficSource.AdvEnginePlaceStrID`[indexOf(`TrafficSource.Model`, 2)], 
                if(`ym:s:lastSignTrafficSource` = toInt8(8), toString(`TrafficSource.SocialSourceNetworkStrID`[indexOf(`TrafficSource.Model`, 2)]), 
                  if(`ym:s:lastSignTrafficSource` = toInt8(9), toString(if((`TrafficSource.RecommendationSystemID`[indexOf(`TrafficSource.Model`, 2)]) = 0, '1', `TrafficSource.RecommendationSystemStrID`[indexOf(`TrafficSource.Model`, 2)])), 
                     if(`ym:s:lastSignTrafficSource` = toInt8(10), toString(if((`TrafficSource.MessengerID`[indexOf(`TrafficSource.Model`, 2)]) = 0, '1', `TrafficSource.MessengerStrID`[indexOf(`TrafficSource.Model`, 2)])), 
                         if(`ym:s:lastSignTrafficSource` = toInt8(11), toString(`TrafficSource.QRCodeProviderStrID`[indexOf(`TrafficSource.Model`, 2)]), 
                                ''
                             )
                       )
                     )
                   )
               )
          )
       ) AS `ym:s:lastSignSourceEngine`,
    anyHeavy(if(`ym:s:lastSignTrafficSource` IN (-1, toInt8(1)), concatAssumeInjective('http://', `TrafficSource.Domain`[indexOf(`TrafficSource.Model`, 2)]), '')) AS `ym:s:lastSignSourceEngineURL`,
    sum(Sign * W) AS `ym:s:visits`,
    least(toFloat64(uniqExact(CounterUserIDHash)), `ym:s:visits`) AS `ym:s:users`,
    100. * (sum(IsBounce * (Sign * W)) / `ym:s:visits`) AS `ym:s:bounceRate`,
    sum(PageViews * (Sign * W)) / `ym:s:visits` AS `ym:s:pageDepth`,
    sum(Duration * (Sign * W)) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу визитов
WHERE (StartDate >= toDate('2023-03-10')) 
        and (StartDate >= toDate('2023-03-16')) 
        AND (CounterID = 24226447) -- поменять на свой номер счетчика
GROUP BY
    `ym:s:lastSignTrafficSource`,
    `ym:s:lastSignTrafficSourceName`,
    `ym:s:lastSignSourceEngine`
    WITH TOTALS
HAVING (`ym:s:visits` > 0.) OR (`ym:s:users` > 0.)
ORDER BY
    `ym:s:visits` DESC,
    `ym:s:lastSignTrafficSource` ASC,
    `ym:s:lastSignSourceEngine` ASC
LIMIT 0, 50

WITH 1. AS W

SELECT

`TrafficSource.ID`[indexOf(`TrafficSource.Model`, 2)] AS `ym:s:lastSignTrafficSource`,

`TrafficSource.StrID`[indexOf(`TrafficSource.Model`, 2)] AS `ym:s:lastSignTrafficSourceName`,

if(

((`TrafficSource.Domain`[indexOf(`TrafficSource.Model`, 2)]) != '') AND

(`ym:s:lastSignTrafficSource` IN (-1, toInt8(1))),

`TrafficSource.Domain`[indexOf(`TrafficSource.Model`, 2)],

if(`ym:s:lastSignTrafficSource` = toInt8(2),

`TrafficSource.SearchEngineStrID`[indexOf(`TrafficSource.Model`, 2)],

if(`ym:s:lastSignTrafficSource` = toInt8(3), `TrafficSource.AdvEnginePlaceStrID`[indexOf(`TrafficSource.Model`, 2)],

if(`ym:s:lastSignTrafficSource` = toInt8(8), toString(`TrafficSource.SocialSourceNetworkStrID`[indexOf(`TrafficSource.Model`, 2)]),

if(`ym:s:lastSignTrafficSource` = toInt8(9), toString(if((`TrafficSource.RecommendationSystemID`[indexOf(`TrafficSource.Model`, 2)]) = 0, '1', `TrafficSource.RecommendationSystemStrID`[indexOf(`TrafficSource.Model`, 2)])),

if(`ym:s:lastSignTrafficSource` = toInt8(10), toString(if((`TrafficSource.MessengerID`[indexOf(`TrafficSource.Model`, 2)]) = 0, '1', `TrafficSource.MessengerStrID`[indexOf(`TrafficSource.Model`, 2)])),

if(`ym:s:lastSignTrafficSource` = toInt8(11), toString(`TrafficSource.QRCodeProviderStrID`[indexOf(`TrafficSource.Model`, 2)]),

)

) AS `ym:s:lastSignSourceEngine`,

anyHeavy(if(`ym:s:lastSignTrafficSource` IN (-1, toInt8(1)), concatAssumeInjective('http://', `TrafficSource.Domain`[indexOf(`TrafficSource.Model`, 2)]), '')) AS `ym:s:lastSignSourceEngineURL`,

sum(Sign * W) AS `ym:s:visits`,

least(toFloat64(uniqExact(CounterUserIDHash)), `ym:s:visits`) AS `ym:s:users`,

100. * (sum(IsBounce * (Sign * W)) / `ym:s:visits`) AS `ym:s:bounceRate`,

sum(PageViews * (Sign * W)) / `ym:s:visits` AS `ym:s:pageDepth`,

sum(Duration * (Sign * W)) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`

FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу визитов

WHERE (StartDate >= toDate('2023-03-10'))

and (StartDate >= toDate('2023-03-16'))

AND (CounterID = 24226447) -- поменять на свой номер счетчика

GROUP BY

`ym:s:lastSignTrafficSource`,

`ym:s:lastSignTrafficSourceName`,

`ym:s:lastSignSourceEngine`

WITH TOTALS

HAVING (`ym:s:visits` > 0.) OR (`ym:s:users` > 0.)

ORDER BY

`ym:s:visits` DESC,

`ym:s:lastSignTrafficSource` ASC,

`ym:s:lastSignSourceEngine` ASC

LIMIT 0, 50

Результат выполнения в DBeaver:

Таблица с детальными данными по источникам трафика в DBeaver

Запрос в DataLens:

QL-чарт с детальными данными по источникам трафика в DataLens

Сохранив таблицы по источникам трафика и источникам трафика (детально) как отдельные QL-чарты, вы сможете в дальнейшем использовать ее в дашборде.

Отчет “Рекламные системы”

Еще один интересный пример, который разбирается в официальной документации Яндекса - это отчет Рекламные системы (таблица визитов):

WITH 1. AS W
SELECT
    `TrafficSource.AdvEnginePlaceStrID`[indexOf(`TrafficSource.Model`, 2)] as `ym:s:lastSignAdvEngine`, -- Рекламная система по атрибуции "Последний значимый переход"
    sum(Sign * W) AS `ym:s:visits`,
    least(toFloat64(uniqExact(CounterUserIDHash)), `ym:s:visits`) AS `ym:s:users`,
    100. * (sum(IsBounce * (Sign * W)) / `ym:s:visits`) AS `ym:s:bounceRate`,
    sum(PageViews * (Sign * W)) / `ym:s:visits` AS `ym:s:pageDepth`,
    sum(Duration * (Sign * W)) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`
from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу визитов
WHERE (StartDate = toDate('2023-03-15')) 
        AND (CounterID = 24226447) -- поменять на свой номер счетчика
        AND (`ym:s:lastSignAdvEngine` != '') 
        AND ((`TrafficSource.ID`[indexOf(`TrafficSource.Model`, 2)]) = toInt8(3)) -- источник трафик "Реклама" по атрибуции "Последний значимый переход"
GROUP BY `ym:s:lastSignAdvEngine`
    WITH TOTALS
HAVING (`ym:s:visits` > 0.) OR (`ym:s:users` > 0.)
ORDER BY
    `ym:s:visits` DESC,
    `ym:s:lastSignAdvEngine` ASC
LIMIT 0, 50

WITH 1. AS W

SELECT

`TrafficSource.AdvEnginePlaceStrID`[indexOf(`TrafficSource.Model`, 2)] as `ym:s:lastSignAdvEngine`, -- Рекламная система по атрибуции "Последний значимый переход"

sum(Sign * W) AS `ym:s:visits`,

least(toFloat64(uniqExact(CounterUserIDHash)), `ym:s:visits`) AS `ym:s:users`,

100. * (sum(IsBounce * (Sign * W)) / `ym:s:visits`) AS `ym:s:bounceRate`,

sum(PageViews * (Sign * W)) / `ym:s:visits` AS `ym:s:pageDepth`,

sum(Duration * (Sign * W)) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`

from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу визитов

WHERE (StartDate = toDate('2023-03-15'))

AND (CounterID = 24226447) -- поменять на свой номер счетчика

AND (`ym:s:lastSignAdvEngine` != '')

AND ((`TrafficSource.ID`[indexOf(`TrafficSource.Model`, 2)]) = toInt8(3)) -- источник трафик "Реклама" по атрибуции "Последний значимый переход"

GROUP BY `ym:s:lastSignAdvEngine`

WITH TOTALS

HAVING (`ym:s:visits` > 0.) OR (`ym:s:users` > 0.)

ORDER BY

`ym:s:visits` DESC,

`ym:s:lastSignAdvEngine` ASC

LIMIT 0, 50

Результат выполнения в DBeaver:

Таблица с данными по рекламным системам в DBeaver

Запрос в DataLens:

QL-чарт с данными по рекламным системам в DataLens

Сохранив эту таблицу как отдельный QL-чарт, вы сможете в дальнейшем использовать ее в дашборде.

Теперь на сырых данных попробуем построить отчеты, которых нет в интерфейсе в Метрики. Яндекс дает примеры следующих отчетов:

Воронки ecommerce-событий
Воронки ecomerce-событий в различных срезах
Удержание новых посетителей
Удержание новых посетителей в различных срезах

Воронки ecommerce-событий

Интеграция Яндекс.Метрики c Yandex Cloud (Метрика Про) позволяет построить более сложные отчеты, которых нет в Метрике. Например, построить воронку по ecommerce-событиям.

Для этого отчета Яндекс рекомендует соблюдать следующие условия:

должны быть настроены события detail, add, purchase и их корректная передача;
от 10 посетителей в каждом из срезов;
конверсия в первый шаг больше 1 %.

Если ваш проект удовлетворяет данным требованиям, то запрос по воронке ecommerce-событий будет выглядеть так (таблица визитов):

select counter_id,
        step0_users, -- общее число посетителей
        step1_users, -- посетители, посмотревшие товары
        step2_users, -- посетители, посмотревшие товары, затем добавившие их в корзину
        step3_users, -- посетители, посмотревшие товары, затем добавившие их в корзину, затем совершившие покупку
        round(step0_users/step0_users*100, 4) as perc_step0, -- % общее число посетителей
        round(step1_users/step0_users*100, 4) as perc_step1, -- % посетителей, посмотревших товары
        round(step2_users/step0_users*100, 4) as perc_step2, -- % посетителей, посмотревших товары, затем добавивших их в корзину
        round(step3_users/step0_users*100, 4) as perc_step3 -- % посетителей, посмотревших товары, затем добавивших их в корзину, затем совершивших покупку

from

(select 
        counter_id,
        sum(step_1) as step1_users,
        sum(step_2) as step2_users,
        sum(step_3) as step3_users
    from
        (select
            CounterID as counter_id,
            CounterUserIDHash as user_id,
            max(e.Type = 1) as step_1, -- только просмотр товаров
            sequenceMatch('(?1)(?2)')(e.EventTime, (e.Type = 1), (e.Type = 4)) as step_2, -- просмотр товаров, а затем добавление в корзину
            sequenceMatch('(?1)(?2)(?3)')(e.EventTime, (e.Type = 1), (e.Type = 4), (e.Type = 3)) as step_3 -- просмотр товаров, затем добавление в корзину, затем покупка
        from 
            (select 
                CounterUserIDHash,
                CounterID,
                e.Type, -- типы еком событий (1 - detail, 2 - стейт корзины, 3 - покупка, 4 - добавление в корзину, 5 - удаление из корзины)
                e.EventTime
            from yandex_data_transfer_test.visits_dttql4la13mb206q472r final  -- сюда вставить свою базу и свою таблицу визитов
            array join EAction as e --arrayJoin размножает массив с еком-событиями в отдельные строки
            where (StartDate >= '2023-02-01')
                and (StartDate <= '2023-02-28') and CounterID = 24226447 -- поменять на свой номер счетчика ) group by counter_id, user_id ) group by counter_id) as a inner join (select CounterID as counter_id, uniqExact(CounterUserIDHash) as step0_users from yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- сюда вставить свою базу и свою таблицу визитов where (StartDate >= '2023-02-01') 
        and (StartDate <= '2023-02-28')
        and CounterID = 24226447 -- поменять на свой номер счетчика
    group by counter_id) as b
on a.counter_id = b.counter_id

select counter_id,

step0_users, -- общее число посетителей

step1_users, -- посетители, посмотревшие товары

step2_users, -- посетители, посмотревшие товары, затем добавившие их в корзину

step3_users, -- посетители, посмотревшие товары, затем добавившие их в корзину, затем совершившие покупку

round(step0_users/step0_users*100, 4) as perc_step0, -- % общее число посетителей

round(step1_users/step0_users*100, 4) as perc_step1, -- % посетителей, посмотревших товары

round(step2_users/step0_users*100, 4) as perc_step2, -- % посетителей, посмотревших товары, затем добавивших их в корзину

round(step3_users/step0_users*100, 4) as perc_step3 -- % посетителей, посмотревших товары, затем добавивших их в корзину, затем совершивших покупку

from

(select

counter_id,

sum(step_1) as step1_users,

sum(step_2) as step2_users,

sum(step_3) as step3_users

from

(select

CounterID as counter_id,

CounterUserIDHash as user_id,

max(e.Type = 1) as step_1, -- только просмотр товаров

sequenceMatch('(?1)(?2)')(e.EventTime, (e.Type = 1), (e.Type = 4)) as step_2, -- просмотр товаров, а затем добавление в корзину

sequenceMatch('(?1)(?2)(?3)')(e.EventTime, (e.Type = 1), (e.Type = 4), (e.Type = 3)) as step_3 -- просмотр товаров, затем добавление в корзину, затем покупка

from

(select

CounterUserIDHash,

CounterID,

e.Type, -- типы еком событий (1 - detail, 2 - стейт корзины, 3 - покупка, 4 - добавление в корзину, 5 - удаление из корзины)

e.EventTime

from yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- сюда вставить свою базу и свою таблицу визитов

array join EAction as e --arrayJoin размножает массив с еком-событиями в отдельные строки

where (StartDate >= '2023-02-01')

and (StartDate <= '2023-02-28') and CounterID = 24226447 -- поменять на свой номер счетчика ) group by counter_id, user_id ) group by counter_id) as a inner join (select CounterID as counter_id, uniqExact(CounterUserIDHash) as step0_users from yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- сюда вставить свою базу и свою таблицу визитов where (StartDate >= '2023-02-01')

and (StartDate <= '2023-02-28')

and CounterID = 24226447 -- поменять на свой номер счетчика

group by counter_id) as b

on a.counter_id = b.counter_id

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

Вышеприведенный запрос нет смысла выполнять в DBeaver, поскольку результат представляется в виде столбчатой диаграммы. Поэтому сразу выполним его в Yandex DataLens:

QL-чарт с воронкой ecommerce-событий в DataLens

Такой отчет вы не получите в интерфейсе Яндекс.Метрики, поэтому я рекомендую сохранить этот QL-чарт и в дальнейшем использовать его в своем дашборде как уникальную визуализацию, доступную только благодаря сырым данным.

Воронки ecommerce-событий (по срезам)

Также можно построить воронку по различным срезам: источник трафика, операционная система, устройство и т.д.

Сам SQL-запрос (срез по мобильным и не мобильным устройствам, isMobile):

select counter_id,
        is_mobile,
        step0_users, -- общее число посетителей
        step1_users, -- посетители, посмотревшие товары
        step2_users, -- посетители, посмотревшие товары, затем добавившие их в корзину
        step3_users, -- посетители, посмотревшие товары, затем добавившие их в корзину, затем совершившие покупку
        round(step0_users/step0_users*100, 4) as perc_step0, -- % общее число посетителей
        round(step1_users/step0_users*100, 4) as perc_step1, -- % посетителей, посмотревших товары
        round(step2_users/step0_users*100, 4) as perc_step2, -- % посетителей, посмотревших товары, затем добавивших их в корзину
        round(step3_users/step0_users*100, 4) as perc_step3 -- % посетителей, посмотревших товары, затем добавивших их в корзину, затем совершивших покупку

from

(select 
        counter_id,
        is_mobile,
        sum(step_1) as step1_users,
        sum(step_2) as step2_users,
        sum(step_3) as step3_users
    from
        (select
            CounterID as counter_id,
            CounterUserIDHash as user_id,
            is_mobile,
            max(e.Type = 1) as step_1, -- только просмотр товаров
            sequenceMatch('(?1)(?2)')(e.EventTime, (e.Type = 1), (e.Type = 4)) as step_2, -- просмотр товаров, а затем добавление в корзину
            sequenceMatch('(?1)(?2)(?3)')(e.EventTime, (e.Type = 1), (e.Type = 4), (e.Type = 3)) as step_3 -- просмотр товаров, затем добавление в корзину, затем покупка
        from 
            (select 
                CounterUserIDHash,
                CounterID,
                IsMobile as is_mobile,
                e.Type, -- типы еком событий (1 - detail, 2 - стейт корзины, 3 - покупка, 4 - добавление в корзину, 5 - удаление из корзины)
                e.EventTime
            from yandex_data_transfer_test.visits_dttql4la13mb206q472r final  -- сюда вставить свою базу и свою таблицу визитов
            array join EAction as e --arrayJoin размножает массив с еком-событиями в отдельные строки
            where (StartDate >= '2023-02-01')
                and (StartDate <= '2023-02-28') and CounterID = 24226447 -- поменять на свой номер счетчика ) group by counter_id, user_id, is_mobile ) group by counter_id, is_mobile) as a inner join (select CounterID as counter_id, IsMobile as is_mobile, uniqExact(CounterUserIDHash) as step0_users from yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- сюда вставить свою базу и свою таблицу визитов where (StartDate >= '2023-02-01') 
        and (StartDate <= '2023-02-28')
        and CounterID = 24226447 -- поменять на свой номер счетчика
    group by counter_id, is_mobile) as b
on a.counter_id = b.counter_id and a.is_mobile = b.is_mobile

select counter_id,

is_mobile,

step0_users, -- общее число посетителей

step1_users, -- посетители, посмотревшие товары

step2_users, -- посетители, посмотревшие товары, затем добавившие их в корзину

step3_users, -- посетители, посмотревшие товары, затем добавившие их в корзину, затем совершившие покупку

round(step0_users/step0_users*100, 4) as perc_step0, -- % общее число посетителей

round(step1_users/step0_users*100, 4) as perc_step1, -- % посетителей, посмотревших товары

round(step2_users/step0_users*100, 4) as perc_step2, -- % посетителей, посмотревших товары, затем добавивших их в корзину

from

(select

counter_id,

is_mobile,

sum(step_1) as step1_users,

sum(step_2) as step2_users,

sum(step_3) as step3_users

from

(select

CounterID as counter_id,

CounterUserIDHash as user_id,

is_mobile,

max(e.Type = 1) as step_1, -- только просмотр товаров

sequenceMatch('(?1)(?2)')(e.EventTime, (e.Type = 1), (e.Type = 4)) as step_2, -- просмотр товаров, а затем добавление в корзину

from

(select

CounterUserIDHash,

CounterID,

IsMobile as is_mobile,

e.Type, -- типы еком событий (1 - detail, 2 - стейт корзины, 3 - покупка, 4 - добавление в корзину, 5 - удаление из корзины)

e.EventTime

from yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- сюда вставить свою базу и свою таблицу визитов

array join EAction as e --arrayJoin размножает массив с еком-событиями в отдельные строки

where (StartDate >= '2023-02-01')

and (StartDate <= '2023-02-28') and CounterID = 24226447 -- поменять на свой номер счетчика ) group by counter_id, user_id, is_mobile ) group by counter_id, is_mobile) as a inner join (select CounterID as counter_id, IsMobile as is_mobile, uniqExact(CounterUserIDHash) as step0_users from yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- сюда вставить свою базу и свою таблицу визитов where (StartDate >= '2023-02-01')

and (StartDate <= '2023-02-28')

and CounterID = 24226447 -- поменять на свой номер счетчика

group by counter_id, is_mobile) as b

on a.counter_id = b.counter_id and a.is_mobile = b.is_mobile

Сделаем то же самое в DataLens, заменив шаблонные данные на свои:

QL-чарт построения воронки на срезе по IsMobile

Удержание новых посетителей

SQL-запрос из справки Яндекса (таблица визитов):

with main as 

(select 
        counter_id,
        num_week,
        uniq(user_id) as users
from
    (select 
        CounterUserIDHash as user_id,
        CounterID as counter_id,
        toDate(FirstVisit) as first_date, -- первый визит посетителя на сайте
        StartDate as event_date,
        (toMonday(event_date) - toMonday(first_date))/7 as num_week
    from yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- сюда вставить свою базу и свою таблицу визитов
    where event_date >= '2022-12-01' -- окно в 15 недель
        and event_date <= toDate('2022-12-31') + 92 -- окно в 15 недель and first_date >= '2022-12-01' -- берем только новых посетителей, которые пришли в декабре
        and first_date <= '2022-12-31' -- берем только новых посетителей, которые пришли в декабре
        and counter_id = 24226447 -- поменять на свой номер счетчика
    )
group by 
    counter_id,
    num_week
order by num_week)

select counter_id, 
        a.users as users, 
        b.users as users_first_week,
        round(a.users/b.users*100, 4) as perc_retention
from main as a
inner join (select * from main where num_week = 0) as b
on a.counter_id = b.counter_id

with main as

(select

counter_id,

num_week,

uniq(user_id) as users

from

(select

CounterUserIDHash as user_id,

CounterID as counter_id,

toDate(FirstVisit) as first_date, -- первый визит посетителя на сайте

StartDate as event_date,

(toMonday(event_date) - toMonday(first_date))/7 as num_week

from yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- сюда вставить свою базу и свою таблицу визитов

where event_date >= '2022-12-01' -- окно в 15 недель

and event_date <= toDate('2022-12-31') + 92 -- окно в 15 недель and first_date >= '2022-12-01' -- берем только новых посетителей, которые пришли в декабре

and first_date <= '2022-12-31' -- берем только новых посетителей, которые пришли в декабре

and counter_id = 24226447 -- поменять на свой номер счетчика

)

group by

counter_id,

num_week

order by num_week)

select counter_id,

a.users as users,

b.users as users_first_week,

round(a.users/b.users*100, 4) as perc_retention

from main as a

inner join (select * from main where num_week = 0) as b

on a.counter_id = b.counter_id

Для расчет удержания новых посетителей вашего сайта вам необходимо указать интервал дат, в которые будут попадать пользователи. После этого они будут разделены на когорты по неделям, и вы сможете построить таблицу, в который вы будете видеть какой % пользователей, пришедших в 0 неделю, переходят из недели в неделю далее.

Не забудьте изменить идентификатор счетчика Метрики, таблицу визитов и базу данных, а также даты только для новых посетителей, в которые пользователи будут попадать в когорты.

Результат выполнения кода в DataLens:

QL-чарт удержания (retention) новых пользователей

Такой отчет и визуализацию вы не получите в интерфейсе Яндекс.Метрики, поэтому я рекомендую сохранить этот QL-чарт и в дальнейшем использовать его в своем дашборде как уникальную информацию, доступную только благодаря сырым данным.

Удержание новых посетителей (по срезам)

Также можно добавить срез и сравнивать удержание в разных срезах. Яндекс рекомендует, чтобы в срезе было хотя бы 30 посетителей, а сам срез составлял хотя бы 5% от общего числа.

Пример удержания по различным источникам трафика первого визита посетителя (таблица визитов):

with main as 

(select 
        counter_id,
        param,
        num_week,
        uniq(user_id) as users
from
    (select 
        CounterUserIDHash as user_id,
        CounterID as counter_id,
        toDate(FirstVisit) as first_date, -- первый визит посетителя на сайте
        StartDate as event_date,
        (toMonday(event_date) - toMonday(first_date))/7 as num_week,
        TrafficSource.ID[indexOf(TrafficSource.Model, 3)] as param
    from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу визитов
    where event_date >= '2022-12-01' -- окно в 15 недель
        and event_date <= toDate('2022-12-31') + 92 -- окно в 15 недель and first_date >= '2022-12-01' -- берем только новых посетителей, 
которые пришли в декабре
        and first_date <= '2022-12-31' -- берем только новых посетителей, 
которые пришли в декабре
        and counter_id = 24226447 -- поменять на свой номер счетчика
    )
group by 
    counter_id,
    param,
    num_week,
    param
order by param, num_week)

select counter_id, 
        param,
        a.users as users, 
        b.users as users_first_week,
        round(a.users/b.users*100, 4) as perc_retention
from main as a
inner join (select * from main where num_week = 0) as b
on a.counter_id = b.counter_id and a.param = b.param

with main as

(select

counter_id,

param,

num_week,

uniq(user_id) as users

from

(select

CounterUserIDHash as user_id,

CounterID as counter_id,

toDate(FirstVisit) as first_date, -- первый визит посетителя на сайте

StartDate as event_date,

(toMonday(event_date) - toMonday(first_date))/7 as num_week,

TrafficSource.ID[indexOf(TrafficSource.Model, 3)] as param

from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- сюда вставить свою базу и свою таблицу визитов

where event_date >= '2022-12-01' -- окно в 15 недель

and event_date <= toDate('2022-12-31') + 92 -- окно в 15 недель and first_date >= '2022-12-01' -- берем только новых посетителей,

которые пришли в декабре

and first_date <= '2022-12-31' -- берем только новых посетителей,

которые пришли в декабре

and counter_id = 24226447 -- поменять на свой номер счетчика

)

group by

counter_id,

param,

num_week,

param

order by param, num_week)

select counter_id,

param,

a.users as users,

b.users as users_first_week,

round(a.users/b.users*100, 4) as perc_retention

from main as a

inner join (select * from main where num_week = 0) as b

on a.counter_id = b.counter_id and a.param = b.param

Запрос в DataLens позволяет построить такой QL-чарт:

QL-чарт удержания (retention) новых пользователей по источникам трафика

Как видите, этот запрос показывает источники трафика как индексы:

-1: Внутренние переходы;
0: Прямые заходы;
1: Переходы по ссылкам на сайтах;
2: Переходы из поисковых систем;
3: Переходы по рекламе;
4: Переходы с сохраненных страниц;
5: Не определен;
6: Переходы по внешним ссылкам;
7: Переходы с почтовых рассылок;
8: Переходы из соц сетей;
9: Переходы из рекомендательных систем;
10: Переходы из мессенджеров;
11: Переходы по QR коду.

Для корректного отображения подписей и самих источников нужно произвести определенные преобразования в запросе. Уверен, что вы с этим без проблем справитесь!

Метки: ClickHouse, DBeaver, SQL, Yandex Cloud, Yandex DataLens, Метрика Про, Яндекс Метрика

Как работать с неагрегированными данными Яндекс.Метрики? (Метрика Про)

Как выгружать визиты

Пример несколлапсированного визита в логах визита

Пример с правильным коллапсированием

Пример коллапсирования с использованием конструкции final после названия таблицы

Как выгружать хиты

Как соотнести визит с его хитами

Отчет “Посещаемость”

Отчет “UTM метки”

Отчет “Популярное”

Отчет “Конверсия цели”

Отчет “Источники, сводка”

Отчет “Рекламные системы”

Воронки ecommerce-событий

Воронки ecommerce-событий (по срезам)

Удержание новых посетителей

Удержание новых посетителей (по срезам)

Получайте бесплатные уроки и фишки

Еще статьи