我在Google Analytics中构建像"Top Conversion Paths"这样的raport时遇到了问题。任何想法如何创建它?
我发现这样的东西,但它不起作用(https://lastclick.city/top-conversion-paths-in-ga-and-bigquery.html(:
SELECT
REGEXP_REPLACE(touchpointPath, 'Conversion >.*', 'Conversion') as touchpointPath, COUNT(touchpointPath) AS TOP
FROM (SELECT
GROUP_CONCAT(touchpoint,' > ') AS touchpointPath
FROM (SELECT
*
FROM (SELECT
fullVisitorId,
'Conversion' AS touchpoint,
(visitStartTime+hits.time) AS timestamp
FROM
TABLE_DATE_RANGE([pro-tracker-id.ga_sessions_], TIMESTAMP('2018-10-01'), TIMESTAMP('2018-10-05'))
WHERE
hits.eventInfo.eventAction="Email Submission success")
,
(SELECT
fullVisitorId,
CONCAT(trafficSource.source,'/',trafficSource.medium) AS touchpoint,
(visitStartTime+hits.time) AS timestamp
FROM
TABLE_DATE_RANGE([pro-tracker-id.ga_sessions_], TIMESTAMP('2018-10-01'), TIMESTAMP('2018-10-05'))
WHERE
hits.hitNumber=1)
ORDER BY
timestamp)
GROUP BY
fullVisitorId
HAVING
touchpointPath LIKE '%Conversion%')
GROUP BY
touchpointPath
ORDER BY
TOP DESC
它不起作用,因为您必须根据需要修改查询。
需要更改此行以匹配您的特定事件操作:
hits.eventInfo.eventAction="YOUR EVENT ACTION HERE")
表引用和日期也需要更改:
TABLE_DATE_RANGE([pro-tracker-id.ga_sessions_], TIMESTAMP('2018-10-01'), TIMESTAMP('2018-10-05'))
共享文章引用了有关获取有关 BigQuery Legacy SQL 中扁平函数的信息的链接。
据我所知,新的 BigQuery UI 中的查询默认作为标准 SQL 运行;但是,您可以通过在 Web UI、REST API 调用或使用云客户端库时为查询添加前缀来设置 SQL 变体。