我目前正在尝试使用ga4内的大查询连接器重新创建我的谷歌分析仪表板。我使用自定义查询拉我需要从大查询的数据，并在数据工作室显示它。当我只是从数据中计算一些kpi并使用查询提取这些kpi时，信息就会正确地传递出来。但是，一旦我尝试访问自定义事件数据，我必须打开它之前，它是可访问的。一旦取消嵌套，则需要对未嵌套的数据进行分组，否则查询将抛出错误。行分组似乎弄乱了先前计算的KPI i，并使值膨胀。如何正确地查询这些数据?我试着把所有的原始数据拉进来，并使用数据工作室内的自定义字段来计算我需要的字段，但也遇到了问题。
SELECT
distinct
event_date,
event_timestamp,
event_name,
user_pseudo_id,
device.category,
-- author
(
SELECT
distinct
params.value.string_value
FROM
UNNEST(event_params) AS params
WHERE
params.key='author'
group by 1 ) AS author,
-- campaign
(
SELECT
params.value.string_value
FROM
UNNEST(event_params) AS params
WHERE
params.key='campaign'
group by 1 ) AS campaign,
-- categories
(
SELECT
params.value.string_value
FROM
UNNEST(event_params) AS params
WHERE
params.key='categories'
group by 1 ) AS categories,
-- clientid
(
SELECT
params.value.string_value
FROM
UNNEST(event_params) AS params
WHERE
params.key='clientid'
group by 1 ) AS clientid,
-- duration
(
SELECT
params.value.string_value
FROM
UNNEST(event_params) AS params
WHERE
params.key='duration'
group by 1 ) AS duration,
-- eventactions
(
SELECT
params.value.string_value
FROM
UNNEST(event_params) AS params
WHERE
params.key='eventactions'
group by 1 ) AS eventactions,
-- eventcategory
(
SELECT
params.value.string_value
FROM
UNNEST(event_params) AS params
WHERE
params.key='eventcategory'
group by 1 ) AS eventcategory,
-- eventlabel
(
SELECT
params.value.string_value
FROM
UNNEST(event_params) AS params
WHERE
params.key='eventlabel'
group by 1 ) AS eventlabel,
-- mediatype
(
SELECT
params.value.string_value
FROM
UNNEST(event_params) AS params
WHERE
params.key='mediatype'
group by 1 ) AS mediatype,
-- pagetitle
(
SELECT
params.value.string_value
FROM
UNNEST(event_params) AS params
WHERE
params.key='pagetitle'
group by 1 ) AS pagetitle,
-- pagetype
(
SELECT
params.value.string_value
FROM
UNNEST(event_params) AS params
WHERE
params.key='pagetype'
group by 1 ) AS pagetype,
-- source
(
SELECT
params.value.string_value
FROM
UNNEST(event_params) AS params
WHERE
params.key='source'
group by 1 ) AS SOURCE,
-- sourceurl
(
SELECT
params.value.string_value
FROM
UNNEST(event_params) AS params
WHERE
params.key='sourceurl'
group by 1 ) AS sourceurl,
-- srclink
(
SELECT
params.value.string_value
FROM
UNNEST(event_params) AS params
WHERE
params.key='srclink'
group by 1 ) AS srclink,
-- status
(
SELECT
params.value.string_value
FROM
UNNEST(event_params) AS params
WHERE
params.key='status'
group by 1 ) AS status,
-- title
(
SELECT
params.value.string_value
FROM
UNNEST(event_params) AS params
WHERE
params.key='title'
group by 1 ) AS title,
-- user_clientid
(
SELECT
params.value.string_value
FROM
UNNEST(event_params) AS params
WHERE
params.key='user_clientid'
group by 1 ) AS user_clientid,
traffic_source.source AS User_Source,
-- end groupby
COUNT(1) AS eventCount,
SAFE_DIVIDE(COUNT(DISTINCT
CASE
WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') = '1' THEN CONCAT(user_pseudo_id,( SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))
END
),COUNT(DISTINCT CONCAT(user_pseudo_id,(
SELECT
value.int_value
FROM
UNNEST(event_params)
WHERE
key = 'ga_session_id')))) AS engagement_rate,
COUNT(DISTINCT user_pseudo_id) AS Unique_Users,
COUNT(DISTINCT
CASE
WHEN ( SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') > 0 OR ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') = '1' THEN user_pseudo_id
ELSE
NULL
END
) AS active_users,
COUNT(DISTINCT
CASE
WHEN ( SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') = 1 THEN user_pseudo_id
ELSE
NULL
END
) AS new_users,
COUNT(DISTINCT user_pseudo_id) AS users
FROM
`zngly-corporate.analytics_315869392.events_*`,
UNNEST(event_params) AS params
GROUP BY
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
18,
19,
20,
21,
22,
23
以上是我尝试的sql查询。不知道我如何才能实现我需要正确的，如果有人有任何见解分享我将不胜感激。
用于此的SQL相当复杂。您可以尝试像Analytics Canvas这样的查询生成器来验证您的结果集:https://analyticscanvas.com/ga4-bigquery-query-builder/。有一个免费的试用版，所以你不需要购买软件来验证你的结果，让你回到正轨。
直接连接Looker Studio到BQ时要超级小心。它为报表的每个图表、表格、记分卡和用户发送单独的查询。如果您直接连接，请确保您正在创建日期分区汇总表并点击这些汇总表，而不是原始的GA4导出表。