Google Analytics(分析)示例dataset有一本食谱,其中包含一些示例,例如匹配顺序一个here。使用STRING_AGG(hits.page.pagePath)
,您可以构建所需的userJourney
匹配序列。
要仅过滤两个所需的页面,我们可以使用诸如答案here之类的方法。但是,在我们的情况下,我们要将一个设置为origin
,将另一个设置为destination
。此外,我们可以在用户到达目标页面后进行剪切,以使这两个路径相似:
/page13
-> /page_5
-> /page_41
-> /page_99
-> /page_22
/page13
-> /page_5
-> /page_41
-> /page_99
-> /page_22
-> page_37
还要注意,我们可以使用Beta中的新scripting功能,以便能够更改脚本顶部的页面对并构建动态正则表达式。例如,使用可用的公共数据来检查/home
和/google+redesign/shop+by+brand/youtube
之间的旅程:
#standardSQL
-- Script to change origin and destination pages with dynamic regex.
DECLARE origin,destination,regex STRING;
SET origin = '/home';
SET destination = '/google+redesign/shop+by+brand/youtube';
SET regex = CONCAT('(',REPLACE(origin,'+','\\+'),'.*?',REPLACE(destination,')');
-- Run query
SELECT
pagePath AS userJourney,COUNT(1) AS frequency
FROM (
SELECT
visitId,REGEXP_EXTRACT(STRING_AGG(hits.page.pagePath),regex) AS pagePath
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`,UNNEST(hits) AS hits
WHERE
_TABLE_SUFFIX BETWEEN '20170701'
AND '20170731'
AND hits.type="PAGE"
GROUP BY
visitId)
WHERE
pagePath IS NOT NULL
GROUP BY
pagePath
ORDER BY
COUNT(1) DESC
LIMIT
10
哪个返回以下结果:
为进一步优化它,我们可以仅选择origin
的最后一次出现,但也许您希望将诸如此类的事物计数为不同的路径:
/page13
-> /page_99
-> /page_22
/page13
-> /page_5
-> /page_13
-> /page_99
-> /page_22
,
您可以使用array_agg()
。如果我的理解正确,那么您希望一个组在它第一次到达page_13时就存在,并在它到达page_22时结束。
让我假设,对于每个用户,您希望 first 命中数为13到 first 命中数为22。您可以通过以下两个特征来识别组:>
select h.*
from (select h.*,countif( page like '%/page_13' ) over (partition by user order by hit_time) as hit_13,countif( page like '%/page_22' ) over (partition by user order by hit_time) as hit_22,countif( page like '%/page_22' ) over (partition by user) as has_22
from hits h
) h
where has_22 and
hit_13 > 0 and
(hit_22 = 0 or page like '%/page_22);
这将返回以13开头的页面,以22结尾的页面,并确保用户同时拥有这两个页面。
现在开始旅程,只需使用聚合即可。但是,可惜,BQ不允许在阵列上进行聚合-如果您随后想要总结一下过程。因此,我将改用string_agg()
:
select h.user,string_agg(page order by hit_time,' -> ')
from (select h.*
from (select h.*,countif( page like '%/page_22' ) over (partition by user) as has_22
from hits h
) h
where has_22 and
hit_13 > 0 and
(hit_22 = 0 or page like '%/page_22)
) h
group by user;
本文链接:https://www.f2er.com/3090967.html