如何处理数据
- 分析流量来源序列的脚本
- 如何正确导出会话
- 如何导出 hits
- 如何将会话与其 hits 相匹配
- Traffic 报表
- “UTM tags” 报表
- “Popular”报表
- “Goal conversion”报表
- “Sources, summary” 报表
- “Ad systems” 报表
- “E-commerce event funnels”报表（在 Yandex Metrica 界面中不可用）
- “Funnels of E-commerce events in different cross-sections”报表（在 Yandex Metrica 界面中不可用）
- “Retention of new users”报表（在 Yandex Metrica 界面中不可用）
- “Retention of new users in different cross-sections”报表（在 Yandex Metrica 界面中不可用）
备注
此功能仅在 Yandex Metrica Pro 套餐中提供。
您可以从 Yandex Metrica 中收集非聚合数据，并将其导入您在 Yandex Cloud 中部署的 ClickHouse 集群。
与 LogsAPI 相比，这种集成方法具有以下区别：
-
该集成包含一组扩展字段。
-
与 LogsAPI 不同，会话中的归因数据存储在数组中。 所有
TrafficSource.XXX数组均根据其归因类型相互关联。 所需YYY归因中的XXX字段：TrafficSource.XXX[indexOf(TrafficSource.Model, YYY)] as XXX。 -
在会话中，
FirstPartyCookie相当于 LogsAPI 中的clientid。
注意
集成在数据格式方面与 Logs API 不向后兼容。
分析流量来源序列的脚本
这些脚本会为每个用户生成转化路径链。 最终，您将获得：
- 一份关于所有来源相关转化情况的报表。
- 不同归因模型的报表，包括 Yandex Metrica 界面中不可用的模型（例如，线性归因模型）。
基于这些数据，您可以独立统计出能带来转化的最热门流量来源序列。
脚本已发布在 GitHub 上。
如何正确导出会话
备注
会话数据会随着新信息的出现而更新。 平均而言，99% 的会话会在开始后的 3 天内结束。
含多个字段的示例
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,
-- 对于搜索引擎流量，您可以查看其来源的搜索引擎。
以最后一个重要流量来源为例：
If(last_significant_TraficSourceID = 2, TrafficSource.SearchEngineStrID[indexOf(TrafficSource.Model, 2)], 'not_search') as last_significant_search_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
- 会话可能会追溯更新，例如，当与线下转化数据匹配时。
- 首次启用会话集成功能时，
sum(Sign)可能出现不一致情况。
如果不使用 sum(Sign) 会怎样
-
连接器的日志存储未折叠的会话版本（即同一会话的多个版本）。 如果不折叠这些数据，数据将会不一致，且同一会话（其旧版本）会被多次计数。
这是因为当会话更新时，旧版本 (
VisitVersion = 1,Sign = 1) 不会被删除。 取而代之的是添加一行与旧版本几乎完全相同的记录，唯一的区别在于Sign标志（VisitVersion = 1，Sign = -1）。 之后，更新后的会话版本会以正的Sign值添加（VVisitVersion = 2，Sign = 1）。 因此，当您按相关字段分组时，sum(Sign)可以通过“折叠”过期版本（Sign = 1和Sign = -1相加结果为零）为您提供准确有效的会话计数。会话日志中未折叠会话的示例
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
正确折叠示例
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
在表名后使用最终子句进行折叠的示例
final会以版本已折叠的方式处理表。 它会自动删除冗余的会话版本。 与使用维度和sum(Sign)计算相比，此方法的运行速度要慢得多。select VisitID, VisitVersion, Sign FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- 在此处添加您的数据库和会话表 where StartDate = today()-3 -- 在此指定任何日期 AND (CounterID = toUInt32(24226447)) and VisitID = 1243431264677003301 order by VisitID, VisitVersion, Sign
如何导出 hits
备注
处理 hits 时，还必须考虑其版本。 与会话类似，单个 hit (WatchID) 可以有多个 HitVersion，可以使用 Sign 字段进行折叠。 按照如何正确导出会话部分所述的相同原则来导出 hits，这一点非常重要。 也可使用 final 子句。
示例
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
如何将会话与其 hits 相匹配
要将 VisitID 与其 hits (WatchID) 进行匹配，仅靠 WatchIDs 这一列可能不够。 这是因为 WatchID 字段不包含会话参数的 hits。 您可以手动整理会话中的 hits 列表。 为此，您需要会话的开始日期、结束日期以及用户 ID。
示例
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
Traffic 报表
图表
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
图表
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
“UTM tags” 报表
示例
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
“Popular”报表
示例
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
“Goal conversion”报表
示例
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
“Sources, summary” 报表
图表
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
表格，详细
WITH 1. AS W
SELECT
`TrafficSource.ID`[indexOf(`TrafficSource.Model`, 2)] AS `ym:s:lastSignTrafficSource`,
`TrafficSource.StrID`[indexOf(`TrafficSource.Model`, 2)] AS `ym:s:lastSignTrafficSourceName`,
if(
((`TrafficSource.Domain`[indexOf(`TrafficSource.Model`, 2)]) != '') AND
(`ym:s:lastSignTrafficSource` IN (-1, toInt8(1))),
`TrafficSource.Domain`[indexOf(`TrafficSource.Model`, 2)],
if(`ym:s:lastSignTrafficSource` = toInt8(2),
`TrafficSource.SearchEngineStrID`[indexOf(`TrafficSource.Model`, 2)],
if(`ym:s:lastSignTrafficSource` = toInt8(3), `TrafficSource.AdvEnginePlaceStrID`[indexOf(`TrafficSource.Model`, 2)],
if(`ym:s:lastSignTrafficSource` = toInt8(8), toString(`TrafficSource.SocialSourceNetworkStrID`[indexOf(`TrafficSource.Model`, 2)]),
if(`ym:s:lastSignTrafficSource` = toInt8(9), toString(if((`TrafficSource.RecommendationSystemID`[indexOf(`TrafficSource.Model`, 2)]) = 0, '1', `TrafficSource.RecommendationSystemStrID`[indexOf(`TrafficSource.Model`, 2)])),
if(`ym:s:lastSignTrafficSource` = toInt8(10), toString(if((`TrafficSource.MessengerID`[indexOf(`TrafficSource.Model`, 2)]) = 0, '1', `TrafficSource.MessengerStrID`[indexOf(`TrafficSource.Model`, 2)])),
if(`ym:s:lastSignTrafficSource` = toInt8(11), toString(`TrafficSource.QRCodeProviderStrID`[indexOf(`TrafficSource.Model`, 2)]),
''
)
)
)
)
)
)
) AS `ym:s:lastSignSourceEngine`,
anyHeavy(if(`ym:s:lastSignTrafficSource` IN (-1, toInt8(1)), concatAssumeInjective('http://', `TrafficSource.Domain`[indexOf(`TrafficSource.Model`, 2)]), '')) AS `ym:s:lastSignSourceEngineURL`,
sum(Sign * W) AS `ym:s:visits`,
least(toFloat64(uniqExact(CounterUserIDHash)), `ym:s:visits`) AS `ym:s:users`,
100. * (sum(IsBounce * (Sign * W)) / `ym:s:visits`) AS `ym:s:bounceRate`,
sum(PageViews * (Sign * W)) / `ym:s:visits` AS `ym:s:pageDepth`,
sum(Duration * (Sign * W)) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- 在此处插入您的数据库和会话表
WHERE (StartDate >= toDate('2023-03-10'))
and (StartDate >= toDate('2023-03-16'))
AND (CounterID = 24226447) -- 更改为您的计数器 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
“Ad systems” 报表
图表
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
“E-commerce event funnels”报表（在 Yandex Metrica 界面中不可用）
该集成可让您构建 Yandex Metrica 中所没有的更复杂的报表。 例如，您可以构建一个 E-commerce 事件漏斗。
我们建议您在生成此报表时满足以下条件：
detail、add和purchase事件已正确设置并传递。- 每个维度至少有 10 个用户。
- 第一步的转化率超过 1%。
- 只要您使用维度，就有足够的数据。 查看关于不同维度 E-commerce 事件漏斗的报表。
示例报表
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
示例可视化
“Funnels of E-commerce events in different cross-sections”报表（在 Yandex Metrica 界面中不可用）
您还可以按各种维度（流量来源、操作系统、设备）构建漏斗。
基于 IsMobile 维度构建漏斗的示例
select counter_id,
is_mobile,
step0_users, -- 用户总数
step1_users, -- 浏览过产品的用户
step2_users, -- 浏览过产品后将其加入购物车的用户
step3_users, -- 浏览过产品后将其加入购物车并随后完成购买的用户
round(step0_users/step0_users*100, 4) as perc_step0, -- 用户总数百分比
round(step1_users/step0_users*100, 4) as perc_step1, -- 浏览过产品的用户百分比
round(step2_users/step0_users*100, 4) as perc_step2, -- 浏览过产品后将其加入购物车的用户百分比
round(step3_users/step0_users*100, 4) as perc_step3 -- 浏览过产品后将其加入购物车并随后完成购买的用户百分比
from
(select
counter_id,
is_mobile,
sum(step_1) as step1_users,
sum(step_2) as step2_users,
sum(step_3) as step3_users
from
(select
CounterID as counter_id,
CounterUserIDHash as user_id,
is_mobile,
max(e.Type = 1) as step_1, -- 仅浏览过产品
sequenceMatch('(?1)(?2)')(e.EventTime, (e.Type = 1), (e.Type = 4)) as step_2, -- 浏览过产品后将其加入购物车
sequenceMatch('(?1)(?2)(?3)')(e.EventTime, (e.Type = 1), (e.Type = 4), (e.Type = 3)) as step_3 -- 浏览过产品后将其加入购物车并随后完成购买
from
(select
CounterUserIDHash,
CounterID,
IsMobile as is_mobile,
e.Type, -- 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
示例可视化
“Retention of new users”报表（在 Yandex Metrica 界面中不可用）
示例
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
示例可视化
“Retention of new users in different cross-sections”报表（在 Yandex Metrica 界面中不可用）
您还可以添加一个维度，并比较不同维度间的用户留存情况。 我们建议该维度至少包含 30 个用户，且至少占用户总量的 5%。
以下为首次用户会话中不同流量来源的用户留存示例
with main as
(select
counter_id,
param,
num_week,
uniq(user_id) as users
from
(select
CounterUserIDHash as user_id,
CounterID as counter_id,
toDate(FirstVisit) as first_date, -- 用户在网站上的首次会话
StartDate as event_date,
(toMonday(event_date) - toMonday(first_date))/7 as num_week,
TrafficSource.ID[indexOf(TrafficSource.Model, 3)] as param
from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- 在此处插入您的数据库和会话表
where event_date >= '2022-12-01' -- 15 周期间
and event_date <= toDate('2022-12-31') + 92 -- 15 周期间
and first_date >= '2022-12-01' -- 我们仅考虑
在 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