Como trabalhar com dados

Observação

Esta funcionalidade só é disponibilizada com o pacote Yandex Metrica Pro.

Você pode coletar dados não agregados do Yandex Metrica no seu cluster ClickHouse implantado na Yandex Cloud.

Esse método de integração apresenta as seguintes distinções em relação ao da API de Logs:

  • A integração abrange um conjunto ampliado de campos.
  • Ao contrário do que ocorre na API de Logs, nas sessões os dados de atribuição são armazenados em arranjos. Todos os arranjos TrafficSource.XXX são correlacionados com base no tipo de atribuição. O campo XXX na atribuição YYY desejada: TrafficSource.XXX[indexOf(TrafficSource.Model, YYY)] as XXX (veja exemplos de Como exportar sessões do modo correto).
  • Nas sessões, o FirstPartyCookie equivale à clientid na API de Logs.

Atenção

A integração não é compatível com versões anteriores da API de Logs em matéria de formato de dados.

Scripts para analisar sequências de origens de tráfego

Os scripts geram cadeias de transição para cada usuário. Consequentemente, você recebe:

  • Um relatório sobre conversões associadas, a partir de todas as origens.
  • Relatórios com diferentes modelos de atribuição, incluindo os não disponíveis na interface do Yandex Metrica (por exemplo, modelo de atribuição linear).

Com base nesses dados, você pode contar de forma independente as sequências de origem de tráfego mais populares que resultam em uma conversão.

Os scripts são publicados no GitHub.

Como exportar sessões de modo correto

Observação

Os dados de sessão são atualizados conforme novas informações vão sendo disponibilizadas. Em média, 99% das sessões são concluídas dentro de 3 dias após o início.

Exemplo com campos múltiplos
SELECT
    VisitID,
    CounterID,
    StartDate,
    CounterUserIDHash, -- A ID de usuário interna UserID usada no Yandex Metrica
    FirstPartyCookie, -- equivalente à ClientID na API de Logs
    Duration,
    EAction.Type,
    EndURL,
    Goals.ID,
    IsBounce,
    IsMobile,
    OS,
    OSFamily,
    OSName,
    PageViews,
    Referer,
    RegionID,
    StartURL,
     TrafficSource.ID, -- Os valores nos campos TrafficSource.ID representam:  
{-1: Tráfego interno. 0: Tráfego direto.  
1: Tráfego de links de site. 2: Tráfego de mecanismos de pesquisa. 
3: Tráfego de anúncios. 4: Tráfego de página em cache.
5: Não definido. 6: Tráfego de link externo. 7: Tráfego de lista de e-mails.
8: Tráfego de redes sociais. 9: Tráfego de sistemas de recomendações.  
10: Tráfego de apps de mensagem. 11: Tráfego de códigos QR.}
     TrafficSource.StrID, -- nome da origem de tráfego legível para humanos
     TrafficSource.Model, -- todos os arranjos TrafficSource.XXX estão correlacionados.
A ordem dos elementos dentro deles reflete o modelo de atribuição TrafficSource.Model usado para determinar o valor do campo TraficSource.XXX. O valor do campo TraficSource.XXX está situado dentro do arranjo.
    TrafficSource.ID[indexOf(TrafficSource.Model, 1)] as last_TraficSourceID, -- Origem de tráfego pela atribuição "Último clique"
     TrafficSource.ID[indexOf(TrafficSource.Model, 2)] as last_significant_TraficSourceID, -- Origem de tráfego pela atribuição "Último clique não direto" 
      TrafficSource.ID[indexOf(TrafficSource.Model, 3)] as first_TraficSourceID, -- Origem de tráfego pela atribuição "Primeiro clique"
     TrafficSource.ID[indexOf(TrafficSource.Model, 4)] as last_yandex_direct_TraficSourceID,  -- Origem de tráfego pela atribuição "Último clique não direto do Yandex Direct"
     TrafficSource.ID[indexOf(TrafficSource.Model, 5)] as cd_last_significant_TraficSourceID, -- Origem de tráfego pela atribuição "Último clique não direto (multidispositivos)"
     TrafficSource.ID[indexOf(TrafficSource.Model, 6)] as cd_first_TraficSourceID, -- Origem de tráfego pela atribuição "Primeiro clique (multidispositivos)"
     TrafficSource.ID[indexOf(TrafficSource.Model, 7)] as cd_last_yandex_direct_TraficSourceID, -- Origem de tráfego pela atribuição "Último clique não direto do Yandex Direct (multidispositivos)"
    
    -- Para tráfego de anúncios, você consegue ver a plataforma publicitária de onde ele veio.
   Por exemplo, em relação à última origem de tráfego relevante:
     If(last_significant_TraficSourceID = 3, TrafficSource.AdvEnginePlaceStrID[indexOf(TrafficSource.Model, 2)], 'not_ad') as last_significant_adv_engine_id,
     -- Para tráfego de mecanismos de pesquisa, você consegue ver o mecanismo de pesquisa de onde ele veio.  
Por exemplo, em relação à última origem de tráfego relevante:
     If(last_significant_TraficSourceID = 2, TrafficSource.SearchEngineStrID[indexOf(TrafficSource.Model, 2)], 'not_search') as last_significant_search_engine_id,
     -- O mesmo vale para o tráfego de redes sociais, sistemas de recomendações e demais origens.
     UserAgent,
     WatchIDs -- para vincular WatchID de hits_all. Hits IsParameter = 1 não será incluído nesta coluna. Este arranjo possui um limite de 500 ocorrências (hits), e o que passar disso será excluído
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r  -- insira seu banco de dados e tabela de sessões aqui
ONDE StartDate = today() - 1 --insira qualquer data. Por exemplo, StartDate = toDate('2022-02-01') ou 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
  • As sessões podem ser atualizadas retrospectivamente, por exemplo no caso da correspondência de conversões offline.
  • Na hora de habilitar a integração das primeiras sessões, sum(Sign) pode apresentar inconsistência.

O que acontece se você não usar sum(Sign)

Os logs do conector armazenam versões de sessão não colapsadas (ou seja, várias versões da mesma sessão). Se você não as colapsar, os dados serão inconsistentes e a mesma sessão (suas versões mais antigas) será contabilizada mais de uma vez.

Isso acontece porque quando uma sessão é atualizada, a versão antiga (VisitVersion = 1, Sign = 1) não é deletada. Em vez disso, uma linha quase idêntica à versão antiga é adicionada, com a única diferença sendo a flag Sign (VisitVersion = 1, Sign = -1). Depois disso, a versão atualizada da sessão é adicionada com um Sign positivo (VisitVersion = 2, Sign = 1). Consequentemente, quando você agrupa por campos relevantes, sum(Sign) propicia uma contagem de sessões exata e válida ao "colapsar" versões desatualizadas (Sign = 1 e Sign = -1 darão zero).

Exemplo de uma sessão não colapsada nos logs de sessão
select VisitID, VisitVersion, Sign
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r  -- adicione seu banco de dados e tabela de sessões aqui
where StartDate = today()-3 -- especifique qualquer data aqui
    AND (CounterID = toUInt32(24226447)) --  insira a ID da sua tag aqui
    and VisitID em (select VisitID
                    from yandex_data_transfer_test.visits_dttql4la13mb206q472r  -- adicione seu banco de dados e tabela de sessões aqui
                    where StartDate = today()-3 --  especifique qualquer data aqui
                    group by VisitID
                    having count(distinct VisitVersion) > 3 -- por exemplo, temos uma sessão que foi modificada mais de três vezes (ou qualquer outro número de vezes)
                    order by VisitID desc
                    limit 1 -- por exemplo, temos uma sessão aqui (ou mais)
                    ) 
order by VisitID, VisitVersion, Sign

Exemplo com colapso adequado
select VisitID, sum(Sign) as visits
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- adicione seu banco de dados e tabela de sessões aqui
where StartDate = today()-3 -- especifique qualquer data aqui
    AND (CounterID = toUInt32(24226447)) -- insira a ID da sua tag aqui
    and VisitID = 1243431264677003301
group by VisitID

Exemplo de colapso usando cláusula final após o nome da tabela

Do modo como final lida com a tabela, as versões já estão colapsadas. Ele remove versões redundantes de sessão por conta própria. Esse método roda mais devagar do que o cálculo usando dimensões e sum(Sign).

select VisitID, VisitVersion, Sign
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r final  -- adicione seu banco de dados e tabela de sessões aqui
where StartDate = today()-3 -- especifique qualquer data aqui
    AND (CounterID = toUInt32(24226447))
    and VisitID = 1243431264677003301
order by VisitID, VisitVersion, Sign

Como exportar ocorrências

Sobre ocorrências

Observação

Ao trabalhar com ocorrências, também é necessário considerar a versão delas. Semelhante ao que acontece com as sessões, uma única ocorrência (WatchID) pode ter múltiplas HitVersion, que podem ser colapsadas usando o campo Sign. É importante exportar ocorrências seguindo os mesmos princípios descritos na seção Como exportar sessões de modo correto. O uso da cláusula final também é permitido.

Exemplo
select CounterID,
        EventDate,
        CounterUserIDHash, -- a UserID usada no Yandex Metrica
        FirstPartyCookie, -- equivalente à ClientID na API de Logs
        UTCEventTime,
        WatchID,
        Referer,
        OriginalURL, -- a URL, ao contrário da OriginalURL, pode ficar truncada
        URL,
        UTMSource,
        IsMobile,
        OS,
        OSFamily,
        OSName,
        FirstPartyCookie,
        IsArtifical,
        IsDownload,
        IsLink,
        IsNotBounce,
        IsPageView,
        IsParameter
from yandex_data_transfer_test.hits_dttql4la13mb206q472r -- insira seu banco de dados e tabela de sessões aqui
where EventDate = today()-1 -- insira qualquer data aqui
   and CounterID = 24226447 -- insira a ID da sua tag aqui
group by CounterID,
        EventDate,
        CounterUserIDHash, -- a UserID interna usada no Yandex Metrica
        FirstPartyCookie, -- equivalente à ClientID da API de Logs
        UTCEventTime,
        WatchID,
        Referer,
        OriginalURL,  -- a URL, ao contrário da OriginalURL, pode ficar truncada
        URL,
        UTMSource,
        IsMobile,
        OS,
        OSFamily,
        OSName,
        FirstPartyCookie,
        IsArtifical,
        IsDownload,
        IsLink,
        IsNotBounce,
        IsPageView,
        IsParameter
having sum(Sign) = 1

Como associar uma sessão a suas ocorrências

Para associar uma VisitID a suas ocorrências (WatchID), apenas a coluna WatchIDs pode não ser suficiente. Isso ocorre porque o campo WatchID não abrange ocorrências de parâmetros de sessão. Você pode compilar manualmente uma lista de ocorrências dentro da sessão. Para isso, será preciso da data de início da sessão, da data de término e da ID de usuário.

Exemplo
select VisitID, -- este "select" irá conter sessões com o arranjo coletado de ocorrências (hits)
        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 (-- este "select" irá conter sessões, com uma entrada distinta para cada ocorrência (hit)
select VisitID,
        a.CounterUserIDHash as CounterUserIDHash,
        UTCStartTime,
        Duration,
        UTCEndTime,
        WatchID,
        IsPageView,
        IsParameter,
        UTCEventTime
from 
(select -- precisamos das sessões, do usuário e das data inicial e data final da sessão. A sessão pode continuar se atualizando retrospectivamente!
    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 -- precisamos das ocorrências, do usuário, e da data e horário da ocorrência. É possível que os dados históricos da ocorrência mudem.
    WatchID,
    CounterUserIDHash,
    IsPageView,
    IsParameter,
    UTCEventTime
from yandex_data_transfer_test.hits_dttql4la13mb206q472r final
where EventDate >= '2023-04-01' 
        and EventDate <= toDate('2023-04-01')+5
        ) as b
on a.CounterUserIDHash = b.CounterUserIDHash -- use esta ID de usuário especificamente
where UTCEventTime >= UTCStartTime and -- ocorrências com horário posterior ao início da sessão 
        UTCEventTime <= UTCEndTime -- ocorrências com horário anterior ao fim da sessão 
order by CounterUserIDHash, UTCEventTime
)
group by VisitID,
        CounterUserIDHash,
        UTCStartTime,
        Duration,
        UTCEndTime
limit 100

Relatório de tráfego

Gráfico 
SELECT StartDate AS `ym:s:date`, 
        sum(Sign) AS `ym:s:visits` -- recolha várias versões de sessão dentro da mais recente e pertinente e conte o número de sessões
from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- adicione seu banco de dados e tabela de sessões aqui
as `default.visits_all` 
WHERE `ym:s:date` >= toDate('2023-01-31') -- esta versão não comporta dados históricos do período anterior à criação do conector
        and `ym:s:date` <= toDate('2023-02-06') -- dados relativos a "hoje" (e atualizações lentas relativas a dias recentes, como conversões offline) pode chegar atrasadas em relação à interface
        and CounterID = 24226447 -- substitua com a ID da sua tag
GROUP BY `ym:s:date` 
WITH TOTALS  
HAVING `ym:s:visits` >= 0.0 
ORDER BY `ym:s:date` ASC 
limit 0,7
Tabela
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`, -- a atribuição 3 representa a atribuição "Primeira sessão" e a atribuição 1 representa "Última sessão"
    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`
   -- este conector não é compatível com as métricas "Percentual de robôs" e "Usuários multidispositivos"
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- insira seu banco de dados e tabela de sessões aqui
AS `default.visits_all`
WHERE (StartDate >= toDate('2023-03-10'))  
        AND (StartDate <= toDate('2023-03-16')) 
        AND (CounterID = toUInt32(24226447)) -- mude para a ID da sua tag
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

Relatório "Tags UTM"

Exemplo
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 -- insira seu banco de dados e tabela de sessões aqui
WHERE (StartDate >= toDate('2023-03-10')) 
        AND (StartDate <= toDate('2023-03-16')) 
        AND (CounterID = 24226447) -- insira a ID da sua tag aqui
        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
Exemplo
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`,
    sum(Sign * W) 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 -- insira a ID da sua tag aqui
       and IsPageView
GROUP BY `ym:pv:URLPathLevel1Hash`
HAVING `ym:pv:pageviews` > 0.0 AND (`ym:pv:pageviews` > 0.0 OR `ym:pv:users` > 0.0)
    WITH TOTALS
ORDER BY
    `ym:pv:pageviews` DESC,
    `ym:pv:URLPathLevel1` ASC,
    `ym:pv:URLPathLevel1Hash` ASC
LIMIT 0, 50

Relatório "Conversão de objetivos"

Exemplo
WITH 1. AS W, 17069575 as my_goal_id -- substitua pela ID do seu objetivo
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 -- insira seu banco de dados e tabela de sessões aqui
WHERE (StartDate >= toDate('2023-02-18')) 
        AND (StartDate <= toDate('2023-03-17')) 
        AND (CounterID = 24226447) -- substitua pela ID da sua tag
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

Relatório "Origens, sumário"

Tabela
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 -- insira seu banco de dados e tabela de sessões aqui
WHERE (StartDate >= toDate('2023-03-10')) 
        AND (StartDate <= toDate('2023-03-16')) 
        AND (CounterID = 24226447) -- substitua pela ID da sua tag
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
Tabela, detalhada
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 -- insira seu banco de dados e tabela de sessões aqui
WHERE (StartDate >= toDate('2023-03-10')) 
        and (StartDate >= toDate('2023-03-16')) 
        AND (CounterID = 24226447) -- substitua pela ID da sua tag
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
Tabela
WITH 1. AS W
SELECT
    `TrafficSource.AdvEnginePlaceStrID`[indexOf(`TrafficSource.Model`, 2)] as `ym:s:lastSignAdvEngine`, -- Plataforma de anúncios pela atribuição "Último clique não direto"
    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 -- insira seu banco de dados e tabela de sessões aqui
WHERE (StartDate = toDate('2023-03-15')) 
        AND (CounterID = 24226447) -- substitua pela ID da sua tag
        AND (`ym:s:lastSignAdvEngine` != '') 
        AND ((`TrafficSource.ID`[indexOf(`TrafficSource.Model`, 2)]) = toInt8(3)) -- origem de tráfego "Tráfego de anúncios" pela atribuição "Último clique não direto"
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

Relatório “Funis de eventos de E-commerce” (indisponível na interface do Yandex Metrica)

A integração permite criar relatórios mais sofisticados, que não estão disponíveis no Yandex Metrica. Por exemplo, você pode criar um funil de eventos de E-commerce.

Recomendamos que você atenda às seguintes condições desse relatório:

  • Os eventos detail, add e purchase estejam configurados e informados corretamente.
  • Você tenha pelo menos 10 usuários por recorte transversal.
  • A taxa de conversão da primeira etapa seja superior a 1%.
  • Você possua dados suficientes de recortes transversais, contanto que os utilize (veja o relatório sobre funis de eventos de E-commerce em diferentes recortes transversais).
Exemplo de relatório 
select counter_id,
        step0_users, -- total number of users
        step1_users, -- users who viewed products
        step2_users, -- usuários que visualizaram produtos e depois os adicionaram ao carrinho de compras
        step3_users, -- usuários que visualizaram produtos, depois os adicionaram ao carrinho de compras,
  depois fizeram uma compra
        round(step0_users/step0_users*100, 4) as perc_step0, -- % número total de usuários
        round(step1_users/step0_users*100, 4) as perc_step1, -- % usuários que visualizaram produtos
        round(step2_users/step0_users*100, 4) as perc_step2, -- % usuários que visualizaram produtos e depois os adicionaram ao carrinho de compras
        round(step3_users/step0_users*100, 4) as perc_step3 -- % usuários que visualizaram produtos, depois os adicionaram ao carrinho de compras, depois fizeram uma compra

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, -- apenas visualizaram produtos
            sequenceMatch('(?1)(?2)')(e.EventTime, (e.Type = 1), (e.Type = 4)) as step_2, -- visualizaram produtos e depois os adicionaram ao carrinho de compras
            sequenceMatch('(?1)(?2)(?3)')(e.EventTime, (e.Type = 1), (e.Type = 4), (e.Type = 3)) as step_3 -- visualizaram produtos, depois os adicionaram ao carrinho de compras, depois fizeram uma compra
        from 
            (select 
                CounterUserIDHash,
                CounterID,
                e.Type, -- tipos de evento E-commerce (1 — detalhe, 2 — a situação do carrinho de compras, 3 — fizeram uma compra, 4 — adicionaram um produto ao carrinho de compras, 5 — removeram um produto do carrinho de compras)
                e.EventTime
            from yandex_data_transfer_test.visits_dttql4la13mb206q472r final  -- insira seu banco de dados e tabela de sessões aqui
            array join EAction as e -- arrayJoin cria linhas distintas para eventos E-commerce no arranjo
            where (StartDate >= '2023-02-01')
                and (StartDate <= '2023-02-28')
                and CounterID = 24226447 -- substitua pela ID da sua tag
                ) 
        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 -- insira seu banco de dados e tabela de sessões aqui
    where (StartDate >= '2023-02-01') 
        and (StartDate <= '2023-02-28')
        and CounterID = 24226447 -- substitua pela ID da sua tag
    group by counter_id) as b
on a.counter_id = b.counter_id
Exemplo de visualização

Relatório “Funis de eventos de E-commerce em diferentes recortes transversais” (indisponível na interface do Yandex Metrica)

Você também pode criar um funil segundo vários recortes transversais: origem de tráfego, sistema operacional, dispositivo.

Exemplo de criação de funil em um recorte transversal IsMobile 
select counter_id,
        is_mobile,
        step0_users, -- total number of users
        step1_users, -- usuários que visualizaram produtos
        step2_users, -- usuários que visualizaram produtos, depois os adicionaram ao carrinho de compras
        step3_users, -- usuários que visualizaram produtos, depois os adicionaram ao carrinho de compras, depois fizeram uma compra
        round(step0_users/step0_users*100, 4) as perc_step0, -- % número total de usuários
        round(step1_users/step0_users*100, 4) as perc_step1, -- % usuários que visualizaram produtos
        round(step2_users/step0_users*100, 4) as perc_step2, -- % que visualizaram produtos, depois os adicionaram ao carrinho de compras
        round(step3_users/step0_users*100, 4) as perc_step3 -- % que visualizaram produtos, depois os adicionaram ao carrinho de compras, depois fizeram uma compra

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, -- apenas visualizaram produtos
            sequenceMatch('(?1)(?2)')(e.EventTime, (e.Type = 1), (e.Type = 4)) as step_2, -- visualizaram produtos, depois os adicionaram ao carrinho de compras
            sequenceMatch('(?1)(?2)(?3)')(e.EventTime, (e.Type = 1), (e.Type = 4), (e.Type = 3)) as step_3 -- visualizaram produtos, depois os adicionaram ao carrinho de compras, depois fizeram uma compra
        from 
            (select 
                CounterUserIDHash,
                CounterID,
                IsMobile as is_mobile,
                e.Type, -- types of E-commerce events (1 — detalhe, 2 — a situação do carrinho de compras, 3 — fizeram uma compra, 4 — adicionaram um produto ao carrinho de compras, 5 — removeram um produto do carrinho de compras)
                e.EventTime
            from yandex_data_transfer_test.visits_dttql4la13mb206q472r final  -- insira seu banco de dados e tabela de sessões aqui
            array join EAction as e --arrayJoin cria linhas distintas para eventos E-commerce no arranjo
            where (StartDate >= '2023-02-01')
                and (StartDate <= '2023-02-28')
                and CounterID = 24226447 -- substitua pela ID da sua tag
                ) 
        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  -- insira seu banco de dados e tabela de sessões aqui
    where (StartDate >= '2023-02-01') 
        and (StartDate <= '2023-02-28')
        and CounterID = 24226447 -- substitua pela ID da sua tabela
    group by counter_id, is_mobile) as b
on a.counter_id = b.counter_id and a.is_mobile = b.is_mobile
Exemplo de visualização

Relatório "Retenção de novos usuários" (indisponível na interface do Yandex Metrica)

Exemplo
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, -- primeira sessão do usuário no site
        StartDate as event_date,
        (toMonday(event_date) - toMonday(first_date))/7 as num_week
    from yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- insira seu banco de dados e tabela de sessões aqui
    where event_date >= '2022-12-01' -- 15-week period
        and event_date <= toDate('2022-12-31') + 92 -- 15-week period
        and first_date >= '2022-12-01' -- só são considerados novos usuários que chegaram em dezembro
        and first_date <= '2022-12-31' -- só são considerados novos usuários que chegaram em dezembro
        and counter_id = 24226447 -- substitua pela ID da sua tag
    )
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
Exemplo de visualização

Relatório “Retenção de novos usuários em diferentes recortes transversais” (indisponível na interface do Yandex Metrica)

Você também pode adicionar um recorte transversal e comparar a retenção de usuários em diferentes recortes transversais. Recomendamos que o recorte transversal inclua pelo menos 30 usuários e represente pelo menos 5% da base total de usuários.

Eis aqui um exemplo de retenção com diferentes origens de tráfego para a primeira sessão do usuário
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, -- primeira sessão do usuário no site
        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 -- insira seu banco de dados e tabela de sessões aqui
    where event_date >= '2022-12-01' -- período de 15 semanas
        and event_date <= toDate('2022-12-31') + 92 -- período de 15 semanas
        and first_date >= '2022-12-01' -- só são considerados novos usuários 
que chegaram em dezembro
        and first_date <= '2022-12-31' -- só são considerados novos usuários, 
que chegaram em dezembro 
        and counter_id = 24226447 -- substitua pela ID da sua tag
    )
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

Fale conosco

Escrever um e-mail

Observe, por gentileza, que nossa equipe de suporte jamais iniciará uma chamada com você. Se alguém ligar para você e se apresentar como a equipe de suporte do Yandex Metrica, não siga as instruções dessa pessoa.

Links úteis

Treinamento online
Anterior
Streaming de dados (integração com Yandex Cloud)
Seguinte
Lista de campos exportados de dados: ocorrências (visualizações de página)