Cómo trabajar con datos

Nota

Esta función solo está disponible con el paquete Yandex Metrica Pro.

Puede recopilar datos no agregados de Yandex Metrica en su clúster de ClickHouse desplegado en Yandex Cloud.

Este método de integración tiene las siguientes diferencias con LogsAPI:

  • La integración incluye un conjunto ampliado de campos.
  • A diferencia de LogsAPI, en las sesiones, los datos de atribución se almacenan en matrices. Todas las matrices TrafficSource.XXX están interrelacionadas según su tipo de atribución. El campo XXX en la atribución requerida YYY: TrafficSource.XXX[indexOf(TrafficSource.Model, YYY)] as XXX (ver ejemplos de Cómo exportar sesiones).
  • En las sesiones, FirstPartyCookie es equivalente a clientid en LogsAPI.

Alerta

La integración detallada a continuación no es compatible con versiones anteriores del Logs API en términos de formato de datos.

Scripts para analizar secuencias de fuentes de tráfico

Los scripts generan cadenas de transición para cada usuario. Como resultado, usted recibe:

  • Un informe sobre conversiones asociadas de todas las fuentes.
  • Informes en diferentes modelos de atribución, incluidos aquellos no disponibles en la interfaz de Yandex Metrica (por ejemplo, modelo de atribución lineal).

Con base en estos datos, puede contar de manera independiente las secuencias de fuentes de tráfico más populares que resultan en una conversión.

Los scripts se publican en GitHub.

Cómo exportar sesiones correctamente

Nota

Los datos de la sesión se actualizan a medida que se dispone de nueva información. En promedio, el 99 % de las sesiones concluyen dentro de los 3 días de su inicio.

Ejemplo con múltiples campos
SELECT
    VisitID,
    CounterID,
    StartDate,
    CounterUserIDHash, -- el UserID interno que se usa en Yandex Metrica
    FirstPartyCookie, -- equivalente a ClientID en LogsAPI
    Duration,
    EAction.Type,
    EndURL,
    Goals.ID,
    IsBounce,
    IsMobile,
    OS,
    OSFamily,
    OSName,
    PageViews,
    Referer,
    RegionID,
    StartURL,
    TrafficSource.ID, -- Los valores en el campo TrafficSource.ID representan: 
{-1: Internal traffic. 0: Direct traffic. 
1: Site link traffic. 2: Search engine traffic.
3: Ad traffic. 4: Cached page traffic.
5: Not defined. 6: External link traffic. 7: Mailing traffic.
8: Social network traffic. 9: Recommendation system traffic. 
10: Messenger traffic. 11: Traffic from QR codes.}
    TrafficSource.StrID, -- fuentes de tráfico legibles para las personas
    TrafficSource.Model, -- todos los arreglos de TrafficSource.XXX están correlacionados.
El orden de los elementos dentro de ellos refleja el modelo de atribución de TrafficSource.Model usado para determinar el valor del campo TrafficSource.XXX. El valor del campo TrafficSource.XXX se localiza dentro del arreglo.
    TrafficSource.ID[indexOf(TrafficSource.Model, 1)] as last_TraficSourceID, -- Fuente de tráfico por la atribución de "Último clic"
    TrafficSource.ID[indexOf(TrafficSource.Model, 2)] as last_significant_TraficSourceID, --  Fuente de tráfico por la atribución de "Último clic no directo" 
    TrafficSource.ID[indexOf(TrafficSource.Model, 3)] as first_TraficSourceID, --  Fuente de tráfico por la atribución de "Primer clic"
    TrafficSource.ID[indexOf(TrafficSource.Model, 4)] as last_yandex_direct_TraficSourceID,  --  Fuente de tráfico por la atribución de "Último clic no directo Yandex Direct"
    TrafficSource.ID[indexOf(TrafficSource.Model, 5)] as cd_last_significant_TraficSourceID, --  Fuente de tráfico por la atribución de  "Último clic no directo (entre dispositivos)"
    TrafficSource.ID[indexOf(TrafficSource.Model, 6)] as cd_first_TraficSourceID, --  Fuente de tráfico por la atribución de "Primer clic (entre dispositivos)"
    TrafficSource.ID[indexOf(TrafficSource.Model, 7)] as cd_last_yandex_direct_TraficSourceID, --  Fuente de tráfico por la atribución de  "Último clic no directo Yandex Direct (entre dispositivos)"
    
    -- Para el tráfico de anuncios puede ver la plataforma de anuncios de la que vino.
  Por ejemplo, respecto a la última fuente de tráfico significativa:
    If(last_significant_TraficSourceID = 3, TrafficSource.AdvEnginePlaceStrID[indexOf(TrafficSource.Model, 2)], 'not_ad') as last_significant_adv_engine_id,
    -- Para el tráfico de motor puede ver el motor de búsqueda del que vino. 
Por ejemplo, respecto a la última fuente de tráfico significativa:
    If(last_significant_TraficSourceID = 2, TrafficSource.SearchEngineStrID[indexOf(TrafficSource.Model, 2)], 'not_search') as last_significant_search_engine_id,
    -- Lo mismo aplica para el tráfico de redes sociales, tráfico de los sistemas de recomendaciones y otras fuentes de tráfico.
    UserAgent,
    WatchIDs -- para enlazar WatchID desde hits_all. Hits IsParameter = 1 no se incluye en esta columna. Este arreglo tiene un límite de 500 hits y los hits que superen esa cantidad serán excluidos
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r  -- inserte su base de datos y tabla de sesión aquí 
WHERE StartDate = today() - 1 --inserte cualquier fecha, por ejemplo StartDate = toDate('2022-02-01') or 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
  • Las sesiones pueden actualizarse retrospectivamente, por ejemplo, al coincidir conversiones sin conexión.
  • Al habilitar la integración para las primeras sesiones, sum(Sign) puede ser inconsistente.

Qué sucede si no usa sum(Sign)

Los registros del conector almacenan versiones de sesión no colapsadas (es decir, múltiples versiones de la misma sesión). Si no las colapsa, los datos serán inconsistentes y la misma sesión (sus versiones más antiguas) se contará más de una vez.

Esto sucede porque cuando se actualiza una sesión, la versión antigua (VisitVersion = 1, Sign = 1) no se elimina. En su lugar, se agrega una fila casi idéntica a la versión antigua, con la única diferencia siendo la bandera Sign (VisitVersion = 1, Sign = -1). Después de eso, se agrega la versión actualizada de la sesión con un Sign positivo (VisitVersion = 2, Sign = 1). Como resultado, cuando agrupa por los campos relevantes, sum(Sign) le da un conteo de sesiones preciso y válido al “colapsar” versiones desactualizadas (Sign = 1 y Sign = -1 serán cero).

Ejemplo de una sesión no colapsada en registros de sesión
select VisitID, VisitVersion, Sign
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r  -- añada su base de datos y tabla de sesión aquí
where StartDate = today()-3 -- especifique cualquier fecha aquí
    AND (CounterID = toUInt32(24226447)) --  añada su ID de etiqueta aquí
    and VisitID in (select VisitID
                    from yandex_data_transfer_test.visits_dttql4la13mb206q472r  -- añada su base de datos y tabla de sesión aquí
                    where StartDate = today()-3 --  especifique cualquier fecha aquí
                    group by VisitID
                    having count(distinct VisitVersion) > 3 -- por ejemplo, tenemos una sesión que se modificó más de tres veces (o cualquier otra cantidad de veces)
                    order by VisitID desc
                    limit 1 -- por ejemplo, tenemos una sesión (o más)
                    ) 
order by VisitID, VisitVersion, Sign

Ejemplo con colapso adecuado
select VisitID, sum(Sign) as visits
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- añada su base de datos y tabla de sesión aquí
where StartDate = today()-3 -- especifique cualquier fecha aquí
    AND (CounterID = toUInt32(24226447)) -- escriba su ID de etiqueta aquí
    and VisitID = 1243431264677003301
group by VisitID

Ejemplo de colapso usando la cláusula final después del nombre de la tabla

final maneja la tabla de tal manera que las versiones ya están colapsadas. Elimina versiones de sesión redundantes por sí mismo. Este método corre mucho más lento que calcular usando dimensiones y sum(Sign).

select VisitID, VisitVersion, Sign
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r final  -- añada su base de datos y tabla de sesión aquí
where StartDate = today()-3 -- especifique cualquier fecha aquí
    AND (CounterID = toUInt32(24226447))
    and VisitID = 1243431264677003301
order by VisitID, VisitVersion, Sign

Cómo exportar hits

Acerca de los hits

Nota

Al trabajar con hits, también es necesario considerar su versionado. Similar a las sesiones, un solo hit (WatchID) puede tener múltiples HitVersion, que pueden ser colapsadas usando el campo Sign. Es importante exportar hits siguiendo los mismos principios descritos en la sección Cómo exportar sesiones correctamente. También se permite el uso de la cláusula final.

Ejemplo
select CounterID,
        EventDate,
        CounterUserIDHash, -- the UserID used in Yandex Metrica
        FirstPartyCookie, -- equivalente a ClientID en LogsAPI
        UTCEventTime,
        WatchID,
        Referer,
        OriginalURL, -- la URL, a diferencia de OriginalURL, puede truncarse
        URL,
        UTMSource,
        IsMobile,
        OS,
        OSFamily,
        OSName,
        FirstPartyCookie,
        IsArtifical,
        IsDownload,
        IsLink,
        IsNotBounce,
        IsPageView,
        IsParameter
from yandex_data_transfer_test.hits_dttql4la13mb206q472r -- inserte su base de datos y tabla de sesión aquí
where EventDate = today()-1 -- inserte cualquier fecha aquí
   and CounterID = 24226447 -- inserte el ID de su etiqueta aquí
group by CounterID,
        EventDate,
        CounterUserIDHash, -- the internal UserID used in Yandex Metrica
        FirstPartyCookie, -- equivalente al ClientID en LogsAPI
        UTCEventTime,
        WatchID,
        Referer,
        OriginalURL,  -- la URL, a diferencia de OriginalURL, puede truncarse
        URL,
        UTMSource,
        IsMobile,
        OS,
        OSFamily,
        OSName,
        FirstPartyCookie,
        IsArtifical,
        IsDownload,
        IsLink,
        IsNotBounce,
        IsPageView,
        IsParameter
having sum(Sign) = 1

Cómo emparejar una sesión con sus hits

Para emparejar un VisitID con sus hits (WatchID), la columna WatchIDs por sí sola puede no ser suficiente. Esto se debe a que el campo WatchID no incluye los hits de parámetros de las sesiones. Puedes compilar una lista de hits dentro de la sesión manualmente. Para esto, necesita la fecha de inicio de la sesión, la fecha de finalización y el ID de usuario.

Ejemplo
select VisitID, -- este "select" contendrán sesiones con el arreglo de clics obtenido
        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 ( -- this "select" will contain sessions, with a separate entry for each hit
select VisitID,
        a.CounterUserIDHash as CounterUserIDHash,
        UTCStartTime,
        Duration,
        UTCEndTime,
        WatchID,
        IsPageView,
        IsParameter,
        UTCEventTime
from 
(select -- necesitamos sesiones, el usuario y las fechas de inicio y fin de la sesión. ¡Pude que la sesión se actualice de forma retroactiva!
    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 -- necesitamos clics, usuario, y la fecha y hora del clic. Los datos de clic históricos pueden cambiar.
    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 this user ID specifically
where UTCEventTime >= UTCStartTime and -- clics con una hora que no sea anterior al inicio de la sesión 
        UTCEventTime <= UTCEndTime -- clics con una hora que no sea posterior al final de la sesión 
order by CounterUserIDHash, UTCEventTime
)
group by VisitID,
        CounterUserIDHash,
        UTCStartTime,
        Duration,
        UTCEndTime
limit 100

Informe de tráfico

Gráfica 
SELECT StartDate AS `ym:s:date`, 
        sum(Sign) AS `ym:s:visits` -- colapsa adecuadamente múltiples versiones de sesión en las últimas y más relevantes y cuenta el número de sesiones
from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- añada su base de datos y tabla de sesión aquí
as `default.visits_all` 
WHERE `ym:s:date` >= toDate('2023-01-31') -- esta versión no es compatible con los datos históricos del periodo anterior a la creación del conector
        and `ym:s:date` <= toDate('2023-02-06') -- dos para "hoy" (y actualizaciones lentas para los días más recientes, como las conversiones fuera de línea) pueden llegar más tarde en relación con la interface
        and CounterID = 24226447 -- reemplace con su ID de etiqueta
GROUP BY `ym:s:date` 
WITH TOTALS  
HAVING `ym:s:visits` >= 0.0 
ORDER BY `ym:s:date` ASC 
limit 0.7
Tabla
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`, -- la atribución 3 representa la atribución "Primera sesión" y la atribución 1 representa la "Última sesión"
    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 no es compatible con "Porcentaje de robots" y las métricas de "Usuarios entre dispositivos"
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- inserte su base de datos y tabla de sesión aquí
AS `default.visits_all`
WHERE (StartDate >= toDate('2023-03-10'))  
        AND (StartDate <= toDate('2023-03-16')) 
        AND (CounterID = toUInt32(24226447)) -- cambie al ID de su etiqueta
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

Informe “Etiquetas UTM”

Ejemplo
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 -- inserte su base de datos y tabla de sesión aquí
WHERE (StartDate >= toDate('2023-03-10')) 
        AND (StartDate <= toDate('2023-03-16')) 
        AND (CounterID = 24226447) -- inserte el ID de su etiqueta aquí
        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
Ejemplo
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 -- inserte el ID de su etiqueta aquí
       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

Informe de “Conversión de metas”

Ejemplo
WITH 1. AS W, 17069575 as my_goal_id -- cambie el ID de su meta
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 -- inserte su base de datos y su tabla de sesión aquí
WHERE (StartDate >= toDate('2023-02-18')) 
        AND (StartDate <= toDate('2023-03-17')) 
        AND (CounterID = 24226447) -- cambie al ID de su etiqueta
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

Informe “Fuentes, resumen”

Tabla
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 -- inserte su base de datos y su tabla de sesión aquí
WHERE (StartDate >= toDate('2023-03-10')) 
        AND (StartDate <= toDate('2023-03-16')) 
        AND (CounterID = 24226447) -- cambie al ID de su etiqueta
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
Tabla, detallada
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 -- insert your database and session table here
WHERE (StartDate >= toDate('2023-03-10')) 
        and (StartDate >= toDate('2023-03-16')) 
        AND (CounterID = 24226447) -- cambie al ID de su etiqueta
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
Tabla
WITH 1. AS W
SELECT
    `TrafficSource.AdvEnginePlaceStrID`[indexOf(`TrafficSource.Model`, 2)] as `ym:s:lastSignAdvEngine`, -- Añada la plataforma por la atribución de "Último clic no directo" 
    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 -- inserte su base de datos y su tabla de sesión aquí
WHERE (StartDate = toDate('2023-03-15')) 
        AND (CounterID = 24226447) -- cambie al ID de su etiqueta
        AND (`ym:s:lastSignAdvEngine` != '') 
        AND ((`TrafficSource.ID`[indexOf(`TrafficSource.Model`, 2)]) = toInt8(3)) -- fuente de tráfico "Añadir tráfico" por la atribución "Último clic no directo" 
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

Informe de “Embudos de eventos de comercio electrónico” (no disponible en la interfaz de Yandex Metrica)

La integración le permite construir informes más sofisticados que no están disponibles en Yandex Metrica. Por ejemplo, puede construir un embudo de eventos de comercio electrónico.

Recomendamos que cumpla con las siguientes condiciones para este informe:

Informe de muestra 
select counter_id,
        step0_users, -- cantidad total de usuarios
        step1_users, -- usuarios que vieron los productos
        step2_users, -- usuarios que vieron productos, y luego los añadieron a su carrito de compra
        step3_users, -- usuarios que vieron productos y luego los añadieron a su carrito de compra,
  y luego concretaron la compra
        round(step0_users/step0_users*100, 4) as perc_step0, -- % cantidad total de usuarios
        round(step1_users/step0_users*100, 4) as perc_step1, -- % usuarios que vieron productos
        round(step2_users/step0_users*100, 4) as perc_step2, -- % usuarios que vieron productos, y luego los añadieron al carrito de compra
        round(step3_users/step0_users*100, 4) as perc_step3 -- % usuarios que vieron productos, luego los añadieron al carrito de compra y luego hicieron una 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, -- solo los productos vistos
            sequenceMatch('(?1)(?2)')(e.EventTime, (e.Type = 1), (e.Type = 4)) as step_2, -- viewed products, then added them to the shopping cart
            sequenceMatch('(?1)(?2)(?3)')(e.EventTime, (e.Type = 1), (e.Type = 4), (e.Type = 3)) as step_3 -- productos vistos, luego se añadieron al carrito de compra y luego se hizo una compra
        from 
            (select 
                CounterUserIDHash,
                CounterID,
                e.Type, -- tipos de eventos de (1 — detalle, 2 — estado del carrito de compra, 3 — hizo una compra, 4 — se añadió un producto al carrito de compra, 5 — se eliminó un producto del carrito de compra)
                e.EventTime
            from yandex_data_transfer_test.visits_dttql4la13mb206q472r final  -- inserte su base de datos y tablas de sesión aquí
            array join EAction as e --arrayJoin arrayJoin crea filas separadas para los eventos de comercio electrónico en el arreglo
            where (StartDate >= '2023-02-01')
                and (StartDate <= '2023-02-28')
                and CounterID = 24226447 -- cambie al ID de su etiqueta
                ) 
        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 -- inserte su base de datos y su tabla de sesión aquí
    where (StartDate >= '2023-02-01') 
        and (StartDate <= '2023-02-28')
        and CounterID = 24226447 -- cambie al ID de su etiqueta
    group by counter_id) as b
on a.counter_id = b.counter_id
Visualización de muestra

Informe de “Embudos de eventos de comercio electrónico en diferentes secciones transversales” (no disponible en la interfaz de Yandex Metrica)

También puede construir un embudo por varias secciones transversales: fuente de tráfico, sistema operativo, dispositivo.

Ejemplo de construcción de un embudo en una sección transversal IsMobile 
select counter_id,
        is_mobile,
        step0_users, -- cantidad total de usuarios
        step1_users, -- usuarios que vieron los productos
        step2_users, -- usuarios que vieron productos, y luego los añadieron al carrito de compra
        step3_users, -- usuarios que vieron productos y luego los añadieron al carrito de compra y luego hicieron una compra
        round(step0_users/step0_users*100, 4) as perc_step0, -- % cantidad total de usuarios
        round(step1_users/step0_users*100, 4) as perc_step1, -- % usuarios que vieron productos
        round(step2_users/step0_users*100, 4) as perc_step2, -- % usuarios que vieron los productos, y luego los añadieron al carrito de compra
        round(step3_users/step0_users*100, 4) as perc_step3 -- % usuarios que vieron productos, luego los añadieron al carrito de compra e hicieron una 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, -- solo productos vistos
            sequenceMatch('(?1)(?2)')(e.EventTime, (e.Type = 1), (e.Type = 4)) as step_2, -- productos vistos, y luego los añadieron al carrito de compra
            sequenceMatch('(?1)(?2)(?3)')(e.EventTime, (e.Type = 1), (e.Type = 4), (e.Type = 3)) as step_3 -- productos vistos, y luego añadidos al carrito de compra, y luego hicieron una compra
        from 
            (select 
                CounterUserIDHash,
                CounterID,
                IsMobile as is_mobile,
                e.Type, -- tipos de eventos de comercio electrónico (1 — detalle, 2 — el estado del carrito de compra, 3 — se hizo una compra, 4 — se añadió un producto al carrito de compra, 5 — se eliminó un producto del carrito de compra)
                e.EventTime
            from yandex_data_transfer_test.visits_dttql4la13mb206q472r final  -- inserta su base de datos y tabla de sesión aquí
            array join EAction as e --arrayJoin crea filas separadas para los eventos de comercio electrónico en el arreglo
            where (StartDate >= '2023-02-01')
                and (StartDate <= '2023-02-28')
                and CounterID = 24226447 -- cambie al ID de su etiqueta
                ) 
        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  -- inserte su base de datos y tabla de sesión aquí
    where (StartDate >= '2023-02-01') 
        and (StartDate <= '2023-02-28')
        and CounterID = 24226447 -- cambio al ID de su tabla
    group by counter_id, is_mobile) as b
on a.counter_id = b.counter_id and a.is_mobile = b.is_mobile
Visualización de muestra

Informe de “Retención de nuevos usuarios” (no disponible en la interfaz de Yandex Metrica)

Ejemplo
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, -- la primera sesión del usuario en su sitio
        StartDate as event_date,
        (toMonday(event_date) - toMonday(first_date))/7 as num_week
    from yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- inserte su base de datos y tabla de sesión aquí
    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' -- solo consideramos usuarios nuevos que llegaron en diciembre
        and first_date <= '2022-12-31' -- solo consideramos a los usuarios nuevos que llegaron en diciembre
        and counter_id = 24226447 -- cambie al ID de su etiqueta
    )
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
Visualización de muestra

Informe de “Retención de nuevos usuarios en diferentes secciones transversales” (no disponible en la interfaz de Yandex Metrica)

También puede agregar una sección transversal y comparar la retención de usuarios en diferentes secciones transversales. Recomendamos que la sección transversal incluya al menos 30 usuarios y represente al menos el 5 % del total de la base de usuarios.

Aquí tiene un ejemplo de retención a través de diferentes fuentes de tráfico para la primera sesión de usuario
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, -- la primera sesión del usuario en el sitio
        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 -- inserte su base de datos y tabla de sesión aquí
    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' -- solo consideramos usuarios nuevos 
que llegaron en diciembre
        and first_date <= '2022-12-31' -- solo consideramos usuarios nuevos, 
que llegaron en diciembre 
        and counter_id = 24226447 -- cambie al ID de su etiqueta
    )
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

Chatee con nosotros

Escriba un correo electrónico

Tome en cuenta: Nuestro equipo de soporte nunca iniciará llamadas hacia usted. No siga las instrucciones de personas que le llamen y se presenten como el equipo de soporte de Yandex Metrica.

Enlaces útiles

Capacitación en línea
Anterior
Transmisión de Datos (Integración con Yandex Cloud)
Siguiente
Lista de campos de datos exportados: hits (vistas de página)