How to work with data

Note

This feature is only available with the Yandex Metrica Pro package.

You can collect non-aggregated data from Yandex Metrica into your ClickHouse cluster deployed in Yandex Cloud.

This integration method has the following distinctions from LogsAPI:

  • The integration includes an extended set of fields.
  • Unlike in LogsAPI, in sessions, the attribution data is stored in arrays. All TrafficSource.XXX arrays are intercorrelated based on their attribution type. The XXX field in the required YYY attribution: TrafficSource.XXX[indexOf(TrafficSource.Model, YYY)] as XXX (see examples of How to export sessions).
  • In sessions, FirstPartyCookie is equivalent to clientid in LogsAPI.

Alert

The integration is not backward compatible with the Logs API in terms of data format.

Scripts for analyzing traffic source sequences

The scripts generate transition chains for each user. As a result, you receive:

  • A report on associated conversions from all sources.
  • Reports in different attribution models, including those not available in the Yandex Metrica interface (for example, linear attribution model).

Based on this data, you can independently count the most popular traffic source sequences that result in a conversion.

The scripts are published on GitHub.

How to properly export sessions

Note

Session data is updated as new information becomes available. On average, 99% of sessions conclude within 3 days of their start.

Example with multiple fields
SELECT
    VisitID,
    CounterID,
    StartDate,
    CounterUserIDHash, -- the internal UserID used in Yandex Metrica
    FirstPartyCookie, -- equivalent to ClientID in LogsAPI
    Duration,
    EAction.Type,
    EndURL,
    Goals.ID,
    IsBounce,
    IsMobile,
    OS,
    OSFamily,
    OSName,
    PageViews,
    Referer,
    RegionID,
    StartURL,
    TrafficSource.ID, -- The values in the TrafficSource.ID fields represent: 
{-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, -- human-readable traffic source name
    TrafficSource.Model, -- all TrafficSource.XXX arrays are correlated.
The order of elements within them reflects the TrafficSource.Model attribution model used to determine the value of the TraficSource.XXX field. The value of the TraficSource.XXX field is located within the array.
    TrafficSource.ID[indexOf(TrafficSource.Model, 1)] as last_TraficSourceID, -- Traffic source by the "Last click" attribution
    TrafficSource.ID[indexOf(TrafficSource.Model, 2)] as last_significant_TraficSourceID, -- Traffic source by the "Last non-direct click" attribution 
    TrafficSource.ID[indexOf(TrafficSource.Model, 3)] as first_TraficSourceID, -- Traffic source by the "First click" attribution
    TrafficSource.ID[indexOf(TrafficSource.Model, 4)] as last_yandex_direct_TraficSourceID,  -- Traffic source by the "Last non-direct click Yandex Direct" attribution
    TrafficSource.ID[indexOf(TrafficSource.Model, 5)] as cd_last_significant_TraficSourceID, -- Traffic source by the "Last non-direct click (cross-device)" attribution
    TrafficSource.ID[indexOf(TrafficSource.Model, 6)] as cd_first_TraficSourceID, -- Traffic source by the "First click (cross-device)" attribution
    TrafficSource.ID[indexOf(TrafficSource.Model, 7)] as cd_last_yandex_direct_TraficSourceID, -- Traffic source by the "Last non-direct click Yandex Direct (cross-device)" attribution
    
    -- For ad traffic, you can see the ad platform it came from.
  For example, regarding the last significant traffic source:
    If(last_significant_TraficSourceID = 3, TrafficSource.AdvEnginePlaceStrID[indexOf(TrafficSource.Model, 2)], 'not_ad') as last_significant_adv_engine_id,
    -- For search engine traffic, you can see the search engine it came from. 
For example, regarding the last significant traffic source:
    If(last_significant_TraficSourceID = 2, TrafficSource.SearchEngineStrID[indexOf(TrafficSource.Model, 2)], 'not_search') as last_significant_search_engine_id,
    -- The same applies to social network traffic, recommendation system traffic, and other traffic sources.
    UserAgent,
    WatchIDs -- for linking WatchID from hits_all. Hits IsParameter = 1 won't be included in this column. This array has a limit of 500 hits and hits beyond that will be excluded
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r  -- insert your database and session table here
WHERE StartDate = today() - 1 --insert any date. For example, 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
  • Sessions may update retrospectively, for example, when matching offline conversions.
  • When enabling integration for first sessions, sum(Sign) may be inconsistent.

What happens if you don’t use sum(Sign)

The connector’s logs store non-collapsed session versions (that is, multiple versions of the same session). If you don’t collapse them, the data will be inconsistent, and the same session (its older versions) will be counted more than once.

This happens because when a session is updated, the old version (VisitVersion = 1, Sign = 1) is not deleted. A row almost identical to the old version is added instead, with the only difference being the Sign flag (VisitVersion = 1, Sign = -1). After that, the updated session version is added with a positive Sign (VisitVersion = 2, Sign = 1). As a result, when you group by the relevant fields, sum(Sign) gives you an accurate and valid session count by “collapsing” outdated versions (Sign = 1 and Sign = -1 will be zero).

Example of an uncollapsed session in session logs
select VisitID, VisitVersion, Sign
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r  -- add your database and session table here
where StartDate = today()-3 -- specify any date here
    AND (CounterID = toUInt32(24226447)) --  enter your tag ID here
    and VisitID in (select VisitID
                    from yandex_data_transfer_test.visits_dttql4la13mb206q472r  -- add your database and session table here
                    where StartDate = today()-3 --  specify any date here
                    group by VisitID
                    having count(distinct VisitVersion) > 3 -- for example, we have a session that was modified more than three times (or any other number of times)
                    order by VisitID desc
                    limit 1 -- for example, we have one session (or more)
                    ) 
order by VisitID, VisitVersion, Sign

Example with proper collapsing
select VisitID, sum(Sign) as visits
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- add your database and session table here
where StartDate = today()-3 -- specify any date here
    AND (CounterID = toUInt32(24226447)) -- enter your tag ID here
    and VisitID = 1243431264677003301
group by VisitID

Example of collapsing using final clause after the table name

final handles the table in such a way that the versions are already collapsed. It removes redundant session versions on its own. This method runs much slower than calculating using dimensions and sum(Sign).

select VisitID, VisitVersion, Sign
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r final  -- add your database and session table here
where StartDate = today()-3 -- specify any date here
    AND (CounterID = toUInt32(24226447))
    and VisitID = 1243431264677003301
order by VisitID, VisitVersion, Sign

How to export hits

About hits

Note

When working with hits, it’s also necessary to consider their versioning. Similar to sessions, a single hit (WatchID) can have multiple HitVersion, which can be collapsed using the Sign field. It’s important to export hits following the same principles as described in the How to correctly export sessions section. The use of the final clause is also allowed.

Example
select CounterID,
        EventDate,
        CounterUserIDHash, -- the UserID used in Yandex Metrica
        FirstPartyCookie, -- equivalent to ClientID in LogsAPI
        UTCEventTime,
        WatchID,
        Referer,
        OriginalURL, -- the URL, unlike OriginalURL, can be truncated
        URL,
        UTMSource,
        IsMobile,
        OS,
        OSFamily,
        OSName,
        FirstPartyCookie,
        IsArtifical,
        IsDownload,
        IsLink,
        IsNotBounce,
        IsPageView,
        IsParameter
from yandex_data_transfer_test.hits_dttql4la13mb206q472r -- insert your database and hits table here
where EventDate = today()-1 -- insert any date here
   and CounterID = 24226447 -- insert the ID of your tag here
group by CounterID,
        EventDate,
        CounterUserIDHash, -- the internal UserID used in Yandex Metrica
        FirstPartyCookie, -- equivalent to ClientID in LogsAPI
        UTCEventTime,
        WatchID,
        Referer,
        OriginalURL,  -- the URL, unlike OriginalURL, can be truncated
        URL,
        UTMSource,
        IsMobile,
        OS,
        OSFamily,
        OSName,
        FirstPartyCookie,
        IsArtifical,
        IsDownload,
        IsLink,
        IsNotBounce,
        IsPageView,
        IsParameter
having sum(Sign) = 1

How to match a session with its hits

To match a VisitID with its hits (WatchID), the WatchIDs column alone may not be enough. This is because the WatchID field doesn't include sessions' parameter hits. You can compile a list of hits within the session manually. For this, you need the session’s start date, end date, and the user ID.

Example
select VisitID, -- this "select" will contain sessions with the collected array of 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 ( -- 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 -- we need sessions, the user, and the session's start date and end date. The session may continue to update retrospectively!
    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 -- we need hits, the user, and the hit's date and time. Historical hit data may change.
    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 -- hits with a time no earlier than the start of the session 
        UTCEventTime <= UTCEndTime -- hits with a time no later than the end of the session 
order by CounterUserIDHash, UTCEventTime
)
group by VisitID,
        CounterUserIDHash,
        UTCStartTime,
        Duration,
        UTCEndTime
limit 100

Traffic report

Chart 
SELECT StartDate AS `ym:s:date`, 
        sum(Sign) AS `ym:s:visits` -- properly collapse multiple session versions into the latest and most relevant one and count the number of sessions
from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- add your database and session table here
as `default.visits_all` 
WHERE `ym:s:date` >= toDate('2023-01-31') -- this version doesn't support historical data from the period before the connector was created
        and `ym:s:date` <= toDate('2023-02-06') -- data for "today" (and slow updates for more recent days, such as offline conversions) may arrive late relative to the interface
        and CounterID = 24226447 -- replace with your tag ID
GROUP BY `ym:s:date` 
WITH TOTALS  
HAVING `ym:s:visits` >= 0.0 
ORDER BY `ym:s:date` ASC 
limit 0,7
Table
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`, -- attribution 3 represents the attribution "First session" and attribution 1 represents "Last session"
    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`
   -- this connector doesn't support the "Percentage of robots" and "Cross-device users" metrics
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- insert your database and session table here
AS `default.visits_all`
WHERE (StartDate >= toDate('2023-03-10'))  
        AND (StartDate <= toDate('2023-03-16')) 
        AND (CounterID = toUInt32(24226447)) -- change to the ID of your 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

“UTM tags” report

Example
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 -- insert your database and session table here
WHERE (StartDate >= toDate('2023-03-10')) 
        AND (StartDate <= toDate('2023-03-16')) 
        AND (CounterID = 24226447) -- insert the ID of your tag here
        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
Example
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 -- insert the ID of your tag here
       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

“Goal conversion” report

Example
WITH 1. AS W, 17069575 as my_goal_id -- change to the ID of your goal
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 -- insert your database and session table here
WHERE (StartDate >= toDate('2023-02-18')) 
        AND (StartDate <= toDate('2023-03-17')) 
        AND (CounterID = 24226447) -- change to the ID of your 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

“Sources, summary” report

Table
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 -- insert your database and session table here
WHERE (StartDate >= toDate('2023-03-10')) 
        AND (StartDate <= toDate('2023-03-16')) 
        AND (CounterID = 24226447) -- change to the ID of your 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
Table, detailed
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) -- change to the ID of your 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
Table
WITH 1. AS W
SELECT
    `TrafficSource.AdvEnginePlaceStrID`[indexOf(`TrafficSource.Model`, 2)] as `ym:s:lastSignAdvEngine`, -- Ad platform by the "Last non-direct click" attribution
    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-15')) 
        AND (CounterID = 24226447) -- change to the ID of your tag
        AND (`ym:s:lastSignAdvEngine` != '') 
        AND ((`TrafficSource.ID`[indexOf(`TrafficSource.Model`, 2)]) = toInt8(3)) -- traffic source "Ad traffic" by the "Last non-direct click" attribution
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

“E-commerce event funnels” report (not available in the Yandex Metrica interface)

The integration allows you to build more sophisticated reports that are not available in Yandex Metrica. For example, you can build a funnel of E-commerce events.

We recommend that you meet the following conditions for this report:

  • The detail, add, and purchase events are set up and passed correctly.
  • You have at least 10 users per cross-section.
  • The conversion rate for the first step is more than 1%.
  • You have sufficient data for cross-sections, provided you use them (see the report on funnels of E-commerce events in different cross-sections).
Sample report 
select counter_id,
        step0_users, -- total number of users
        step1_users, -- users who viewed products
        step2_users, -- users who viewed products, then added them to the shopping cart
        step3_users, -- users who viewed products, then added them to the shopping cart,
  then made a purchase
        round(step0_users/step0_users*100, 4) as perc_step0, -- % total number of users
        round(step1_users/step0_users*100, 4) as perc_step1, -- % users who viewed products
        round(step2_users/step0_users*100, 4) as perc_step2, -- % users who viewed products, then added them to the shopping cart
        round(step3_users/step0_users*100, 4) as perc_step3 -- % users who viewed products, then added them to the shopping cart, then made a purchase

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, -- only viewed products
            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 -- viewed products, then added them to the shopping cart, then made a purchase
        from 
            (select 
                CounterUserIDHash,
                CounterID,
                e.Type, -- types of E-commerce events (1 — detail, 2 — the state of the shopping cart, 3 — made a purchase, 4 — added a product to the shopping cart, 5 — removed a product from the shopping cart)
                e.EventTime
            from yandex_data_transfer_test.visits_dttql4la13mb206q472r final  -- insert your database and session table here
            array join EAction as e --arrayJoin arrayJoin creates separate rows for E-commerce events in the array
            where (StartDate >= '2023-02-01')
                and (StartDate <= '2023-02-28')
                and CounterID = 24226447 -- change to the ID of your 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 -- insert your database and session table here
    where (StartDate >= '2023-02-01') 
        and (StartDate <= '2023-02-28')
        and CounterID = 24226447 -- change to the ID of your tag
    group by counter_id) as b
on a.counter_id = b.counter_id
Sample visualization

“Funnels of E-commerce events in different cross-sections” report (not available in the Yandex Metrica interface)

You can also build a funnel by various cross-sections: traffic source, operating system, device.

Example of building a funnel on an IsMobile cross-section 
select counter_id,
        is_mobile,
        step0_users, -- total number of users
        step1_users, -- users who viewed products
        step2_users, -- users who viewed products, then added them to the shopping cart
        step3_users, -- users who viewed products, then added them to the shopping cart, then made a purchase
        round(step0_users/step0_users*100, 4) as perc_step0, -- % total number of users
        round(step1_users/step0_users*100, 4) as perc_step1, -- % users who viewed products
        round(step2_users/step0_users*100, 4) as perc_step2, -- % users who viewed products, then added them to the shopping cart
        round(step3_users/step0_users*100, 4) as perc_step3 -- % users who viewed products, then added them to the shopping cart, then made a purchase

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, -- only viewed products
            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 -- viewed products, then added them to the shopping cart, then made a purchase
        from 
            (select 
                CounterUserIDHash,
                CounterID,
                IsMobile as is_mobile,
                e.Type, -- types of E-commerce events (1 — detail, 2 — the state of the shopping cart, 3 — made a purchase, 4 — added a product to the shopping cart, 5 — removed a product from the shopping cart)
                e.EventTime
            from yandex_data_transfer_test.visits_dttql4la13mb206q472r final  -- insert your database and session table here
            array join EAction as e --arrayJoin creates separate rows for E-commerce events in the array
            where (StartDate >= '2023-02-01')
                and (StartDate <= '2023-02-28')
                and CounterID = 24226447 -- change to the ID of your 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  -- insert your database and session table here
    where (StartDate >= '2023-02-01') 
        and (StartDate <= '2023-02-28')
        and CounterID = 24226447 -- change to the ID of your table
    group by counter_id, is_mobile) as b
on a.counter_id = b.counter_id and a.is_mobile = b.is_mobile
Sample visualization

“Retention of new users” report (not available in the Yandex Metrica interface)

Example
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, -- user's first session on the site
        StartDate as event_date,
        (toMonday(event_date) - toMonday(first_date))/7 as num_week
    from yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- insert your database and session table here
    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' -- we only consider new users who came in December
        and first_date <= '2022-12-31' -- we only consider new users who came in December
        and counter_id = 24226447 -- change to the ID of your 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
Sample visualization

“Retention of new users in different cross-sections” report (not available in the Yandex Metrica interface)

You can also add a cross-section and compare user retention across different cross-sections. We recommend that the cross-section include at least 30 users and represent at least 5% of the total user base.

Here’s an example of retention across different traffic sources for the first user session
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, -- user's first session on the 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 -- insert your database and session table here
    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' -- we only consider new users 
who came in December
        and first_date <= '2022-12-31' -- we only consider new users, 
who came in December 
        and counter_id = 24226447 -- change to the ID of your 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

Chat with us

Write an email

Please note: Our support team will never initiate a call to you. Do not follow any instructions of people who call you and introduce themselves as the Yandex Metrica support team.

Useful links

Online training
Previous
Data Streaming (Integration with Yandex Cloud)
Next
List of exported data fields: hits (pageviews)