时间戳

首页 > 解决方案 > 如何在 BigQuery 中构建报告的热门转化路径

sql - 如何在 BigQuery 中构建报告的热门转化路径

问题描述

我在 Google Analytics（分析）中构建类似“Top Conversion Paths”之类的报告时遇到问题。有什么想法可以创建这个吗？

我找到了类似的东西，但它不起作用（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

标签： sqlgoogle-analyticsgoogle-bigquery

解决方案

它不起作用，因为您必须根据需要修改查询。

需要更改此行以匹配您的特定事件操作：

    hits.eventInfo.eventAction="YOUR EVENT ACTION HERE")

表格参考和日期也需要更改：

TABLE_DATE_RANGE([pro-tracker-id.ga_sessions_], TIMESTAMP('2018-10-01'), TIMESTAMP('2018-10-05'))

推荐阅读