英文:
Create a better GA4 landing page report with BigQuery
问题
创建一个更好的GA4着陆页面报告，使用BigQuery
大家好，
是否有办法使用Google BigQuery重新创建GA4标准着陆页面报告？
更具体地说，这是一个标准GA4着陆页面报告的示例，显示了与名为'banner_click'的事件相关的所有着陆页面：
GA4着陆报告
唯一的问题是，在“着陆页面”列中，它显示/lading-page-1、/landing-page-2等部分路径，而我希望能够读取完整的网站网址（示例：https://website.com/landing-page-1）。
那么，我如何通过BigQuery获得相同的视图，但带有完整的网址？
谢谢。
在搜索与着陆页面相关的查询示例时，我找到了这个：
SELECT
(SELECT value.string_value FROM unnest(event_params) WHERE key = "page_location") as page_location,
count(*) as sessions,
count(distinct user_pseudo_id) as users
FROM `big-query-323234.analytics_343345454.events_20230306`
WHERE (SELECT value.int_value FROM unnest(event_params) WHERE key="entrances")=1
GROUP BY 1
ORDER BY 2 DESC
我如何修改这个查询以显示'banner_click'事件的事件数？
英文:
Create a better GA4 landing page report with BigQuery
Hi everyone,
is there a way to recreate the standard Landing Page report of GA4 with Google BigQuery?
To be more specific this is an example of a standard GA4 Landing page Report, it show all landing pages related to an event called 'banner_click':
GA4 Landing Report
The only problem is that in the column 'Landing Page' it shows /lading-page-1, /landing-page-2 etc. as partial path and I would like instead to read the full webiste url (example: https://website.com/landing-page-1)
So, how can I obtain the same view but with full url through BigQuery?
Thank you.
Searched for query examples related to landing pages, I've found this one:
SELECTbig-query-323234.analytics_343345454.events_20230306
(SELECT value.string_value FROM unnest(event_params) WHERE key = "page_location") as page_location,
count(*) as sessions,
count(distinct user_pseudo_id) as users
FROM
WHERE (SELECT value.int_value FROM unnest(event_params) WHERE key="entrances")=1
GROUP BY 1
ORDER BY 2 DESC
How can I modify this query to shows number of events for 'banner_click' event?
答案1
得分: 0
返回翻译好的内容：
对于你的横幅查询，执行以下代码部分的计数：
(select value.string_value from unnest(event_params) where key = 'banner_click') as banner_click,
我会将其用于以下URL部分：
regexp_replace((select value.string_value from unnest(event_params) where key = 'page_location'), r'\?.*', '') as page_location
但我感到困惑，因为你的代码应该生成带有所有utm代码的长URL，而不是缺少http的短URL。
英文:
do a count of (select value.string_value from unnest(event_params) where key = 'banner_click') as banner_click, for your banner query
I would use this for the URL
regexp_replace((select value.string_value from unnest(event_params) where key = 'page_location'), r'\?.*', '') as page_location, but am confused because your code should be spitting out a long url with all the utm codes on and not a short one missing out the http
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论