---
metadata:
  - name: generator
    content: Diplodoc Platform v5.52.0
alternate:
  - https://www.yandex.com/support/metrica/en/pro/data-work.md
  - https://www.yandex.com/support/metrica/es/pro/data-work.md
  - https://www.yandex.com/support/metrica/pt/pro/data-work.md
  - https://www.yandex.com/support/metrica/ru/pro/data-work.md
  - https://www.yandex.com/support/metrica/tr/pro/data-work.md
  - https://www.yandex.com/support/metrica/zh/pro/data-work.md
  - href: zh/pro/data-work.md
    type: text/markdown
    title: Markdown version
  - href: ../llms.txt
    type: text/markdown
    title: llms.txt
title: 如何处理数据
description: 此功能仅在 Yandex Metrica Pro 套餐中提供。
---
> **Documentation Index:** Fetch the complete configuration index at https://www.yandex.com/support/metrica/zh/llms.txt


# 如何处理数据


{% note info %}

此功能仅在 Yandex Metrica Pro 套餐中提供。

{% endnote %}


您可以从 [Yandex Metrica](https://metrica.yandex.com/) 中收集非聚合数据，并将其导入您在 [Yandex Cloud](https://yandex.cloud/en-ru) 中部署的 ClickHouse 集群。

与 [LogsAPI](https://yandex.ru/dev/metrika/doc/api2/logs/intro.html) 相比，这种集成方法具有以下区别：

- 该集成包含一组扩展字段。
- 与 LogsAPI 不同，会话中的归因数据存储在数组中。 所有 `TrafficSource.XXX` 数组均根据其归因类型相互关联。 所需 `YYY` 归因中的 `XXX` 字段：`TrafficSource.XXX[indexOf(TrafficSource.Model, YYY)] as XXX`。

  [如何正确导出会话](#visits)

- 在会话中，`FirstPartyCookie` 相当于 LogsAPI 中的 `clientid`。


{% note alert %}

集成在数据格式方面与 Logs API 不向后兼容。

{% endnote %}


## 分析流量来源序列的脚本 {#scripts}

这些脚本会为每个用户生成转化路径链。 最终，您将获得：

- 一份关于所有来源相关转化情况的报表。
- 不同归因模型的报表，包括 Yandex Metrica 界面中不可用的模型（例如，线性归因模型）。

基于这些数据，您可以独立统计出能带来转化的最热门流量来源序列。

脚本已发布在 [GitHub](https://github.com/zhdanchik/yandex_metrika_connector_cases) 上。

## 如何正确导出会话 {#visits}


{% note info %}

会话数据会随着新信息的出现而更新。 平均而言，99% 的会话会在开始后的 3 天内结束。

{% endnote %}



{% cut "含多个字段的示例" %}


```javascript
SELECT
    VisitID,
    CounterID,
    StartDate,
    CounterUserIDHash, -- Yandex Metrica 中使用的内部用户 ID
    FirstPartyCookie, -- 相当于 Logs API 中的ClientID
    Duration,
    EAction.Type,
    EndURL,
    Goals.ID,
    IsBounce,
    IsMobile,
    OS,
    OSFamily,
    OSName,
    PageViews,
    Referer,
    RegionID,
    StartURL,
    TrafficSource.ID, -- TrafficSource.ID 字段中的值代表以下含义： 
{-1：内部流量。0：直接流量。
1：点击网站上的链接。 2：搜索引擎流量。
3：广告流量。4：节省的页面流量。
5：未定义。6：外部链接流量。7：邮件流量。
8：社交网络流量。9：推荐系统流量。
10：即时通讯工具流量。11：二维码流量。}
    TrafficSource.StrID, -- 人类可读的流量来源名称
    TrafficSource.Model, -- 所有 TrafficSource.XXX 数组都是相关的。
其中元素的顺序反映了用于确定 TraficSource.XXX 字段值的 TrafficSource.Model 归因模型。该数组保存了 TraficSource.XXX 字段本身的值。
    TrafficSource.ID[indexOf(TrafficSource.Model, 1)] as last_TraficSourceID, -- 基于“最后一次点击”归因的流量来源。
    TrafficSource.ID[indexOf(TrafficSource.Model, 2)] as last_significant_TraficSourceID, -- 基于“最后一次非直接点击”归因的流量来源。
    TrafficSource.ID[indexOf(TrafficSource.Model, 3)] as first_TraficSourceID, -- 基于“首次点击”归因的流量来源。
    TrafficSource.ID[indexOf(TrafficSource.Model, 4)] as last_yandex_direct_TraficSourceID, -- 基于“来自 Yandex Direct 的最后一次非直接点击”归因的流量来源。
    TrafficSource.ID[indexOf(TrafficSource.Model, 5)] as cd_last_significant_TraficSourceID, -- 基于“最后一次非直接点击（跨设备）”归因的流量来源。
    TrafficSource.ID[indexOf(TrafficSource.Model, 6)] as cd_first_TraficSourceID, -- 基于“首次点击（跨设备）”归因的流量来源。
    TrafficSource.ID[indexOf(TrafficSource.Model, 7)] as cd_last_yandex_direct_TraficSourceID, -- 基于“来自 Yandex Direct 的最后一次非直接点击（跨设备）”归因的流量来源。
    
    -- 对于广告和推广流量，您可以查看其来源的广告平台。
以最后一个重要流量来源为例：
    If(last_significant_TraficSourceID = 3, TrafficSource.AdvEnginePlaceStrID[indexOf(TrafficSource.Model, 2)], 'not_ad') as last_significant_adv_engine_id,
    -- 同理适用于社交网络流量、推荐系统流量以及其他流量来源。
    UserAgent,
    WatchIDs -- 用于链接来自 hits_all 的 WatchID。IsParameter = 1 的 hits 不会被包含在此列中。此数组的 hits 上限为 500 次。 超出此数量的 hits 将被排除在外。
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- 在此处插入您的数据库和会话表。
WHERE StartDate = today() - 1 -- 插入任何日期。例如，StartDate = toDate('2022-02-01') 或 StartDate = '2022-02-01'
    AND CounterID = toUInt32(24226447)
GROUP BY
    VisitID,
    CounterID,
    StartDate,
    CounterUserIDHash,
    FirstPartyCookie,
    Duration,
    EAction.Type,
    EndURL,
    Goals.ID,
    IsBounce,
    IsMobile,
    OS,
    OSFamily,
    OSName,
    PageViews,
    Referer,
    RegionID,
    StartURL,
    TrafficSource.Model,
    TrafficSource.ID,
    TrafficSource.StrID,
    last_TraficSourceID,
    last_significant_TraficSourceID,
    first_TraficSourceID, 
    last_yandex_direct_TraficSourceID,
    cd_last_significant_TraficSourceID,
    cd_first_TraficSourceID,
    cd_last_yandex_direct_TraficSourceID,
    last_significant_adv_engine_id,
    last_significant_search_engine_id,
    UserAgent,
    WatchIDs
HAVING sum(Sign) = 1
limit 1000
```


{% endcut %}


- 会话可能会追溯更新，例如，当与线下转化数据匹配时。
- 首次启用会话集成功能时，`sum(Sign)` 可能出现不一致情况。

**如果不使用 `sum(Sign)` 会怎样**

 
:   连接器的日志存储未折叠的会话版本（即同一会话的多个版本）。 如果不折叠这些数据，数据将会不一致，且同一会话（其旧版本）会被多次计数。
    
    这是因为当会话更新时，旧版本 (`VisitVersion = 1`, `Sign = 1`) 不会被删除。 取而代之的是添加一行与旧版本几乎完全相同的记录，唯一的区别在于 `Sign` 标志（`VisitVersion = 1`，`Sign = -1`）。 之后，更新后的会话版本会以正的 `Sign`值添加（V`VisitVersion = 2`，`Sign = 1`）。 因此，当您按相关字段`分组`时，`sum(Sign)` 可以通过“折叠”过期版本（`Sign = 1` 和 `Sign = -1` 相加结果为零）为您提供准确有效的会话计数。
    
    
    {% cut "会话日志中未折叠会话的示例" %}
    
    
    ```javascript
    select VisitID, VisitVersion, Sign
    FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- 在此处添加您的数据库和会话表
    where StartDate = today()-3 -- 在此指定任何日期
        AND (CounterID = toUInt32(24226447)) -- 在此处输入您的计数器 ID
        and VisitID in (select VisitID
                        from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- 在此处添加您的数据库和会话表
                        where StartDate = today()-3 --  在此指定任何日期
                        group by VisitID
                        having count(distinct VisitVersion) > 3 -- 例如，我们有一个会话被修改了超过 3 次（或任意其他次数）
                        order by VisitID desc
                        limit 1 -- 例如，我们有一个（或多个）会话
                        ) 
    order by VisitID, VisitVersion, Sign
    ```
   ![](../_assets/pro-visits1.png)
    
    {% endcut %}
    
    {% cut "正确折叠示例" %}
    
    
    ```javascript
    select VisitID, sum(Sign) as visits
    FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- 在此处添加您的数据库和会话表
    where StartDate = today()-3 -- 在此指定任何日期
        AND (CounterID = toUInt32(24226447)) -- 在此处输入您的计数器 ID
         and VisitID = 1243431264677003301
    group by VisitID
    ```
   ![](../_assets/pro-visits2.png)
    
    {% endcut %}
    
    {% cut "在表名后使用最终子句进行折叠的示例" %}
    
    
    `final` 会以版本已折叠的方式处理表。 它会自动删除冗余的会话版本。 与使用维度和 `sum(Sign)` 计算相比，此方法的运行速度要慢得多。
    
    ```javascript
    select VisitID, VisitVersion, Sign
    FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- 在此处添加您的数据库和会话表
    where StartDate = today()-3 -- 在此指定任何日期
        AND (CounterID = toUInt32(24226447))
        and VisitID = 1243431264677003301
    order by VisitID, VisitVersion, Sign
    ```
   ![](../_assets/pro-visits3.png)
    
    {% endcut %}

## 如何导出 hits {#hits}

[关于 hits](https://www.yandex.com/support/metrica/zh/pro/price.md#hit)


{% note info %}

处理 hits 时，还必须考虑其版本。 与会话类似，单个 hit (`WatchID`) 可以有多个 `HitVersion`，可以使用 `Sign` 字段进行折叠。 按照[如何正确导出会话](#visits)部分所述的相同原则来导出 hits，这一点非常重要。 也可使用 `final` 子句。

{% endnote %}



{% cut "示例" %}


```javascript
select CounterID,
        EventDate,
        CounterUserIDHash, -- the UserID used in Yandex Metrica
        FirstPartyCookie, -- 相当于 LogsAPI 中的 ClientID
        UTCEventTime,
        WatchID,
        Referer,
        OriginalURL, -- 与 OriginalURL 不同，URL 可以被截断
        URL,
        UTMSource,
        IsMobile,
        OS,
        OSFamily,
        OSName,
        FirstPartyCookie,
        IsArtifical,
        IsDownload,
        IsLink,
        IsNotBounce,
        IsPageView,
        IsParameter
from yandex_data_transfer_test.hits_dttql4la13mb206q472r -- 在此处插入您的数据库和 hits 表
where EventDate = today()-1 -- insert any date here
   and CounterID = 24226447 -- 在此处插入您的计数器 ID
group by CounterID,
        EventDate,
        CounterUserIDHash, -- Yandex Metrica 中使用的内部用户 ID
        FirstPartyCookie, -- 相当于 LogsAPI 中的 ClientID
        UTCEventTime,
        WatchID,
        Referer,
        OriginalURL, -- 与 OriginalURL 不同，URL 可以被截断
        URL,
        UTMSource,
        IsMobile,
        OS,
        OSFamily,
        OSName,
        FirstPartyCookie,
        IsArtifical,
        IsDownload,
        IsLink,
        IsNotBounce,
        IsPageView,
        IsParameter
having sum(Sign) = 1
```


{% endcut %}


## 如何将会话与其 hits 相匹配 {#visit-hit}

要将 `VisitID` 与其 hits (`WatchID`) 进行匹配，仅靠 `WatchIDs` 这一列可能不够。 这是因为 `WatchID` 字段不包含会话参数的 hits。 您可以手动整理会话中的 hits 列表。 为此，您需要会话的开始日期、结束日期以及用户 ID。


{% cut "示例" %}


```javascript
select VisitID, -- 此“select”将包含带有已收集 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 ( -- 此“select”将包含会话，且每个 hit 都有一个单独的条目
select VisitID,
        a.CounterUserIDHash as CounterUserIDHash,
        UTCStartTime,
        Duration,
        UTCEndTime,
        WatchID,
        IsPageView,
        IsParameter,
        UTCEventTime
from 
(select -- 我们需要会话、用户以及会话的开始日期和结束日期。会话可能会持续追溯更新！
    VisitID,
    CounterUserIDHash,
    UTCStartTime,
    Duration,
    toDateTime(UTCStartTime) + Duration as UTCEndTime
from yandex_data_transfer_test.visits_dttql4la13mb206q472r final
where StartDate = '2023-04-01'
) as a
left join
(select -- 我们需要 hits、用户以及 hit 日期和时间。历史 hit 数据可能会发生变化。
    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 -- 请专门使用这个用户 ID
where UTCEventTime >= UTCStartTime and -- hit 时间不早于会话开始时间
        UTCEventTime <= UTCEndTime -- hit 时间不晚于会话结束时间 
order by CounterUserIDHash, UTCEventTime
)
group by VisitID,
        CounterUserIDHash,
        UTCStartTime,
        Duration,
        UTCEndTime
limit 100
```


{% endcut %}


## "Traffic" 报表 {#traffic}


{% cut "图表 " %}


```javascript
SELECT StartDate AS `ym:s:date`, 
        sum(Sign) AS `ym:s:visits` -- 正确地将多个会话版本折叠为最新且最相关的版本，并统计会话数量
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') -- 此版本不支持连接器创建之前的历史数据
        and `ym:s:date` <= toDate('2023-02-06') -- “today”数据（以及最近几天的更新较慢的数据，如离线转化量）可能会相对于界面延迟到达
        and CounterID = 24226447 -- 替换为您的计数器 ID
GROUP BY `ym:s:date` 
WITH TOTALS  
HAVING `ym:s:visits` >= 0.0 
ORDER BY `ym:s:date` ASC 
limit 0,7
```


{% endcut %}

{% cut "图表" %}


```javascript
SELECT
    toDate(StartDate) AS `ym:s:datePeriodday`,
    sum(Sign) AS `ym:s:visits`,
    uniqExact(CounterUserIDHash) AS `ym:s:users`,
    sum(PageViews * Sign) AS `ym:s:pageviews`,
    uniqExactIf(CounterUserIDHash, (`TrafficSource.StartTime`[indexOf(`TrafficSource.Model`, 3)]) = (`TrafficSource.StartTime`[indexOf(`TrafficSource.Model`, 1)])) / uniqExact(CounterUserIDHash) * 100. AS `ym:s:percentNewVisitors`, -- 归因 3 代表“首次会话”归因，归因 1 代表“最后一次会话”归因
    100. * (sum(IsBounce * Sign) / `ym:s:visits`) AS `ym:s:bounceRate`,
    `ym:s:pageviews` / `ym:s:visits` AS `ym:s:pageDepth`,
    sum(Duration * Sign) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`
   -- 该连接器不支持“Percentage of robots”和“Cross-device users”指标
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- 在此处插入您的数据库和会话表
AS `default.visits_all`
WHERE (StartDate >= toDate('2023-03-10'))  
        AND (StartDate <= toDate('2023-03-16')) 
        AND (CounterID = toUInt32(24226447)) -- 更改为您的计数器 ID
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
```


{% endcut %}


## “UTM tags” 报表 {#utm}


{% cut "示例" %}


```javascript
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) -- 在此处插入您的计数器 ID
        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
```


{% endcut %}


## “Popular”报表 {#popular}


{% cut "示例" %}


```javascript
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 -- 在此处插入您的计数器 ID
       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
```


{% endcut %}


## “Goal conversion”报表 {#conversion}


{% cut "示例" %}


```javascript
WITH 1. AS W, 17069575 as my_goal_id -- 更改为您的目标 ID
SELECT
    toDate(StartDate) AS `ym:s:datePeriodday`,
    100. * (sum(has(`Goals.ID`, my_goal_id) * (Sign * W)) / sum(Sign * W)) AS `ym:s:goal17069575conversionRate`,
    sum(arrayCount(x → (my_goal_id = x), `Goals.ID`) * (Sign * W)) AS `ym:s:goal17069575reaches`,
    sumIf(Sign * W, arrayExists(x_0 → (x_0 = my_goal_id), `Goals.ID`)) AS `ym:s:goal17069575visits`,
    least(toFloat64(uniqIf(CounterUserIDHash, arrayExists(x_0 → (x_0 = my_goal_id), `Goals.ID`))), `ym:s:goal17069575visits`) AS `ym:s:goal17069575users`,
    sumIf(PageViews * (Sign * W), arrayExists(x_0 → (x_0 = my_goal_id), `Goals.ID`)) AS `ym:s:goal17069575pageviews`,
    (least(uniqIf(CounterUserIDHash, ((`TrafficSource.StartTime`[indexOf(`TrafficSource.Model`, 3)]) = (`TrafficSource.StartTime`[indexOf(`TrafficSource.Model`, 1)])) AND arrayExists(x_0 → (x_0 = my_goal_id), `Goals.ID`)), uniqIf(CounterUserIDHash, arrayExists(x_0 → (x_0 = my_goal_id), `Goals.ID`))) / uniqIf(CounterUserIDHash, arrayExists(x_0 → (x_0 = my_goal_id), `Goals.ID`))) * 100. AS `ym:s:goal17069575percentNewVisitors`,
    100. * (sumIf(IsBounce * (Sign * W), arrayExists(x_0 → (x_0 = my_goal_id), `Goals.ID`)) / `ym:s:goal17069575visits`) AS `ym:s:goal17069575bounceRate`,
    `ym:s:goal17069575pageviews` / `ym:s:goal17069575visits` AS `ym:s:goal17069575pageDepth`,
    sumIf(Duration * (Sign * W), arrayExists(x_0 → (x_0 = my_goal_id), `Goals.ID`)) / `ym:s:goal17069575visits` AS `ym:s:goal17069575avgVisitDurationSeconds`
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- 在此处插入您的数据库和会话表
WHERE (StartDate >= toDate('2023-02-18')) 
        AND (StartDate <= toDate('2023-03-17')) 
        AND (CounterID = 24226447) -- 更改为您的计数器 ID
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
```


{% endcut %}


## “Sources, summary” 报表 {#sources}


{% cut "图表" %}


```javascript
WITH 1. AS W
SELECT
    `TrafficSource.ID`[indexOf(`TrafficSource.Model`, 2)] AS `ym:s:lastSignTrafficSource`,
    sum(Sign * W) AS `ym:s:visits`,
    least(toFloat64(uniq(CounterUserIDHash)), `ym:s:visits`) AS `ym:s:users`,
    100. * (sum(IsBounce * (Sign * W)) / `ym:s:visits`) AS `ym:s:bounceRate`,
    sum(PageViews * (Sign * W)) / `ym:s:visits` AS `ym:s:pageDepth`,
    sum(Duration * (Sign * W)) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`
from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- 在此处插入您的数据库和会话表
WHERE (StartDate >= toDate('2023-03-10')) 
        AND (StartDate <= toDate('2023-03-16')) 
        AND (CounterID = 24226447) -- 更改为您的计数器 ID
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
```


{% endcut %}

{% cut "表格，详细" %}


```javascript
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.SearchEngineRootStrID`[indexOf(`TrafficSource.Model`, 2)], 
            if(`ym:s:lastSignTrafficSource` = toInt8(3), `TrafficSource.AdvEnginePlaceStrID`[indexOf(`TrafficSource.Model`, 2)], 
                if(`ym:s:lastSignTrafficSource` = toInt8(8), toString(`TrafficSource.SocialSourceNetworkStrID`[indexOf(`TrafficSource.Model`, 2)]), 
                  if(`ym:s:lastSignTrafficSource` = toInt8(9), toString(if((`TrafficSource.RecommendationSystemID`[indexOf(`TrafficSource.Model`, 2)]) = 0, '1', `TrafficSource.RecommendationSystemStrID`[indexOf(`TrafficSource.Model`, 2)])), 
                     if(`ym:s:lastSignTrafficSource` = toInt8(10), toString(if((`TrafficSource.MessengerID`[indexOf(`TrafficSource.Model`, 2)]) = 0, '1', `TrafficSource.MessengerStrID`[indexOf(`TrafficSource.Model`, 2)])), 
                         if(`ym:s:lastSignTrafficSource` = toInt8(11), toString(`TrafficSource.QRCodeProviderStrID`[indexOf(`TrafficSource.Model`, 2)]), 
                                ''
                             )
                       )
                     )
                   )
               )
          )
       ) AS `ym:s:lastSignSourceEngine`,
    anyHeavy(if(`ym:s:lastSignTrafficSource` IN (-1, toInt8(1)), concatAssumeInjective('http://', `TrafficSource.Domain`[indexOf(`TrafficSource.Model`, 2)]), '')) AS `ym:s:lastSignSourceEngineURL`,
    sum(Sign * W) AS `ym:s:visits`,
    least(toFloat64(uniqExact(CounterUserIDHash)), `ym:s:visits`) AS `ym:s:users`,
    100. * (sum(IsBounce * (Sign * W)) / `ym:s:visits`) AS `ym:s:bounceRate`,
    sum(PageViews * (Sign * W)) / `ym:s:visits` AS `ym:s:pageDepth`,
    sum(Duration * (Sign * W)) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- 在此处插入您的数据库和会话表 
WHERE (StartDate >= toDate('2023-03-10')) 
        and (StartDate >= toDate('2023-03-16')) 
        AND (CounterID = 24226447) -- 更改为您的计数器 ID
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
```


{% endcut %}


## “Ad systems” 报表 {#advertisement}


{% cut "图表" %}


```javascript
WITH 1. AS W
SELECT
    `TrafficSource.AdvEnginePlaceStrID`[indexOf(`TrafficSource.Model`, 2)] as `ym:s:lastSignAdvEngine`, -- 按“最后一次非直接点击”归因的广告平台
    sum(Sign * W) AS `ym:s:visits`,
    least(toFloat64(uniqExact(CounterUserIDHash)), `ym:s:visits`) AS `ym:s:users`,
    100. * (sum(IsBounce * (Sign * W)) / `ym:s:visits`) AS `ym:s:bounceRate`,
    sum(PageViews * (Sign * W)) / `ym:s:visits` AS `ym:s:pageDepth`,
    sum(Duration * (Sign * W)) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`
from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- insert your database and session table here
WHERE (StartDate = toDate('2023-03-15')) 
        AND (CounterID = 24226447) -- 更改为您的计数器 ID
        AND (`ym:s:lastSignAdvEngine` != '') 
        AND ((`TrafficSource.ID`[indexOf(`TrafficSource.Model`, 2)]) = toInt8(3)) -- 按“最后一次非直接点击”归因的“广告流量”来源
GROUP BY `ym:s:lastSignAdvEngine`
    WITH TOTALS
HAVING (`ym:s:visits` > 0.) OR (`ym:s:users` > 0.)
ORDER BY
    `ym:s:visits` DESC,
    `ym:s:lastSignAdvEngine` ASC
LIMIT 0, 50
```


{% endcut %}


## “E-commerce event funnels”报表（在 Yandex Metrica 界面中不可用） {#ecom-total}

该集成可让您构建 Yandex Metrica 中所没有的更复杂的报表。 例如，您可以构建一个 E-commerce 事件漏斗。

我们建议您在生成此报表时满足以下条件：

- `detail`、`add` 和 `purchase` 事件已正确设置并传递。
- 每个维度至少有 10 个用户。
- 第一步的转化率超过 1%。
- 只要您使用维度，就有足够的数据。 查看关于[不同维度 E-commerce 事件漏斗](#ecom-slices)的报表。

{% cut "示例报表 " %}


```javascript
select counter_id,
        step0_users, -- 用户总数
        step1_users, -- 浏览过产品的用户
        step2_users, -- 浏览过产品后将其加入购物车的用户
        step3_users, -- 浏览过产品后将其加入购物车并
随后完成购买的用户
        round(step0_users/step0_users*100, 4) as perc_step0, -- 用户总数百分比
        round(step1_users/step0_users*100, 4) as perc_step1, -- 浏览过产品的用户百分比
        round(step2_users/step0_users*100, 4) as perc_step2, -- 浏览过产品后将其加入购物车的用户百分比
        round(step3_users/step0_users*100, 4) as perc_step3 -- 浏览过产品后将其加入购物车并随后完成购买的用户百分比。

from

(select 
        counter_id,
        sum(step_1) as step1_users,
        sum(step_2) as step2_users,
        sum(step_3) as step3_users
    from
        (select
            CounterID as counter_id,
            CounterUserIDHash as user_id,
            max(e.Type = 1) as step_1, -- 仅浏览了产品
            sequenceMatch('(?1)(?2)')(e.EventTime, (e.Type = 1), (e.Type = 4)) as step_2, -- 浏览过产品后将其加入购物车
            sequenceMatch('(?1)(?2)(?3)')(e.EventTime, (e.Type = 1), (e.Type = 4), (e.Type = 3)) as step_3 -- 浏览过产品后将其加入购物车并随后完成了购买
        from 
            (select 
                CounterUserIDHash,
                CounterID,
                e.Type, -- E-commerce 事件的类型（1 — 详情，2 — 购物车状态，3 — 完成购买，4 — 将产品加入购物车，5 — 从购物车中移除产品）
                e.EventTime
            from yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- 在此处插入您的数据库和会话表
            array join EAction as e --arrayJoin arrayJoin 在数组中为 E-commerce 事件创建单独的行
            where (StartDate >= '2023-02-01')
                and (StartDate <= '2023-02-28')
                and CounterID = 24226447 -- 更改为您的计数器 ID
                ) 
        group by counter_id, user_id
        )
    group by counter_id) as a

inner join (select
        CounterID as counter_id,
        uniqExact(CounterUserIDHash) as step0_users 
    from yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- 在此处插入您的数据库和会话表
    where (StartDate >= '2023-02-01') 
        and (StartDate <= '2023-02-28')
        and CounterID = 24226447 -- 更改为您的计数器 ID
    group by counter_id) as b
on a.counter_id = b.counter_id
```


{% endcut %}

{% cut "示例可视化" %}

![](../_assets/pro-ecom1.png)

{% endcut %}


## “Funnels of E-commerce events in different cross-sections”报表（在 Yandex Metrica 界面中不可用） {#ecom-slices}
您还可以按各种维度（流量来源、操作系统、设备）构建漏斗。

{% cut "基于 IsMobile 维度构建漏斗的示例 " %}


```javascript
select counter_id,
        is_mobile,
        step0_users, -- 用户总数
        step1_users, -- 浏览过产品的用户
        step2_users, -- 浏览过产品后将其加入购物车的用户
        step3_users, -- 浏览过产品后将其加入购物车并随后完成购买的用户
        round(step0_users/step0_users*100, 4) as perc_step0, -- 用户总数百分比
        round(step1_users/step0_users*100, 4) as perc_step1, -- 浏览过产品的用户百分比
        round(step2_users/step0_users*100, 4) as perc_step2, -- 浏览过产品后将其加入购物车的用户百分比
        round(step3_users/step0_users*100, 4) as perc_step3 -- 浏览过产品后将其加入购物车并随后完成购买的用户百分比

from

(select 
        counter_id,
        is_mobile,
        sum(step_1) as step1_users,
        sum(step_2) as step2_users,
        sum(step_3) as step3_users
    from
        (select
            CounterID as counter_id,
            CounterUserIDHash as user_id,
            is_mobile,
            max(e.Type = 1) as step_1, -- 仅浏览过产品
            sequenceMatch('(?1)(?2)')(e.EventTime, (e.Type = 1), (e.Type = 4)) as step_2, -- 浏览过产品后将其加入购物车
            sequenceMatch('(?1)(?2)(?3)')(e.EventTime, (e.Type = 1), (e.Type = 4), (e.Type = 3)) as step_3 -- 浏览过产品后将其加入购物车并随后完成购买
        from 
            (select 
                CounterUserIDHash,
                CounterID,
                IsMobile as is_mobile,
                e.Type, -- E-commerce 事件类型（1 — 详情，2 — 购物车状态，3 — 完成购买，4 — 将产品加入购物车，5 — 从购物车中移除产品）
                e.EventTime
            from yandex_data_transfer_test.visits_dttql4la13mb206q472r final --在此处插入您的数据库和会话表
            array join EAction as e --arrayJoin 为数组中的 E-commerce 事件创建单独的行
            where (StartDate >= '2023-02-01')
                and (StartDate <= '2023-02-28')
                and CounterID = 24226447 -- 更改为您的计数器 ID
                ) 
        group by counter_id, user_id, is_mobile
        )
    group by counter_id, is_mobile) as a

inner join (select
        CounterID as counter_id,
        IsMobile as is_mobile,
        uniqExact(CounterUserIDHash) as step0_users 
    from yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- 在此处插入您的数据库和会话表
    where (StartDate >= '2023-02-01') 
        and (StartDate <= '2023-02-28')
        and CounterID = 24226447 -- 更改为您的表 ID
    group by counter_id, is_mobile) as b
on a.counter_id = b.counter_id and a.is_mobile = b.is_mobile
```


{% endcut %}

{% cut "示例可视化 " %}

![](../_assets/pro-ecom2.png)

{% endcut %}


## “Retention of new users”报表（在 Yandex Metrica 界面中不可用） {#retention}


{% cut "示例" %}


```javascript
with main as 

(select 
        counter_id,
        num_week,
        uniq(user_id) as users
from
    (select 
        CounterUserIDHash as user_id,
        CounterID as counter_id,
        toDate(FirstVisit) as first_date, -- 用户在网站上的首次会话
        StartDate as event_date,
        (toMonday(event_date) - toMonday(first_date))/7 as num_week
    from yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- 在此处插入您的数据库和会话表
    where event_date >= '2022-12-01' -- 15 周期间
        and event_date <= toDate('2022-12-31') + 92 -- 15 周期间
        and first_date >= '2022-12-01' -- 我们仅考虑在 12 月到来的新用户
        and first_date <= '2022-12-31' -- 我们仅考虑在 12 月到来的新用户
        and counter_id = 24226447 -- 更改为您的计数器 ID
    )
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
```


{% endcut %}



{% cut "示例可视化 " %}

![](../_assets/pro-retention.png)

{% endcut %}


## “Retention of new users in different cross-sections”报表（在 Yandex Metrica 界面中不可用） {#retention-slices}
您还可以添加一个维度，并比较不同维度间的用户留存情况。 我们建议该维度至少包含 30 个用户，且至少占用户总量的 5%。

{% cut "以下为首次用户会话中不同流量来源的用户留存示例" %}


```javascript
with main as 

(select 
        counter_id,
        param,
        num_week,
        uniq(user_id) as users
from
    (select 
        CounterUserIDHash as user_id,
        CounterID as counter_id,
        toDate(FirstVisit) as first_date, -- 用户在网站上的首次会话
        StartDate as event_date,
        (toMonday(event_date) - toMonday(first_date))/7 as num_week,
        TrafficSource.ID[indexOf(TrafficSource.Model, 3)] as param
    from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- 在此处插入您的数据库和会话表
    where event_date >= '2022-12-01' -- 15 周期间
        and event_date <= toDate('2022-12-31') + 92 -- 15 周期间 
        and first_date >= '2022-12-01' -- 我们仅考虑
在 12 月到来的新用户 
        and first_date <= '2022-12-31' -- 我们仅考虑
在 12 月到来的新用户
        and counter_id = 24226447 -- 更改为您的计数器 ID
    )
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
```

{% endcut %}


<!-- source: zh/_includes/buttons/chat-button.md -->
[与我们聊天](https://yandex.com/chat/#/user/036e6a02-3620-9cdc-4c5e-a34667a7379e?utm_source=spravka){.button}

<!-- source: zh/_includes/styles/href-to-button.md -->

<!-- endsource: zh/_includes/styles/href-to-button.md -->
<!-- endsource: zh/_includes/buttons/chat-button.md -->

<!-- source: zh/_includes/pro/support-button.md -->
<div class="cut-button">

{% cut "发送电子邮件" %}

<!-- source: zh/_includes/popup/id-popup/wrong-sup.md -->
请注意：我们的支持团队永远不会主动给您打电话。 请勿按照自称为 Yandex Metrica 支持团队来电者的指示操作。
<!-- endsource: zh/_includes/popup/id-popup/wrong-sup.md -->

<div style="padding: 15px;
     margin: 10px 0;
     background: #FFFFFF;
     border-radius: 10px;
     border: 1px solid var(--g-color-line-generic);">
  <iframe style="background: #FFFFFF;"
        height="700"
        width="100%"
        frameborder="0"
        src="https://forms.yandex.com/surveys/13470764.a1e88715348b88bbfc3fe42a9f189f1ebd4b220d/?&iframe=1&lang=en">
  </iframe>
</div>

{% endcut %}

</div>

<!-- source: zh/_includes/styles/cut-button.md -->

<!-- endsource: zh/_includes/styles/cut-button.md -->
<!-- endsource: zh/_includes/pro/support-button.md -->

<!-- source: zh/_includes/footer-links.md -->
- - -

<div class="borderless-table">

#|
||
实用链接

- [演示计数器](https://metrica.yandex.com/r/dashboard?)
- [添加计数器](https://metrica.yandex.com/add/)
- [免费计数器设置](https://yandex.com/promo/freeservice/metrica?utm_source=help_metrica_en&utm_medium=cpc&utm_campaign=1)
- [Yandex Metrica API](https://tech.yandex.com/metrika/)
- [提出您的想法](https://yandex.com/support/metrica/troubleshooting/idea.html)
|
线上培训



- [参加培训课程](https://yandex.com/adv/edu/online/metrika?utm_source=metrika_help&utm_medium=web&utm_campaign=static&utm_content=useful_links)

||
|#

</div>

<!-- source: zh/_includes/styles/table-style.md -->

<!-- endsource: zh/_includes/styles/table-style.md -->
<!-- endsource: zh/_includes/footer-links.md -->