https://www.growthbook.io/ logo
#ask-questions
Title
# ask-questions
b

busy-air-96466

03/17/2023, 3:14 PM
Hi, I can’t get GrowthBook to find metric or experiment results from BigQuery (using GA4). I would be very grateful for any help as I’ve been struggling with this all week and can’t see what I’m doing wrong! I have a webpage with a button that sends an event “Main CTA” when clicked, and a feature “new-main-panel” which determines which version of the UI the user sees (both have the button). GA4 events are logged as per GrowthBook instructions and connected to BigQuery. I can see the events in BigQuery and they look correct. GrowthBook can connect to BigQuery; but my metric, after a brief blip, is showing nothing and my experiment has no data. BigQuery table: my onclick event that I’m measuring is “Main CTA”, and on login I’m setting user_id. I’m also sending the experiment_viewed event and that looks OK too. I can’t see anything wrong in the BigQuery data, see an example below: Metric: the SQL looks OK, it’s looking for the event “Main CTA”, see screenshot. But when I run the metric query, it only goes to 15 March and shows no data for the last few days, see screenshot. This is the full metric query:
SELECT
user_id,
user_pseudo_id as anonymous_id,
TIMESTAMP_MICROS(event_timestamp) as timestamp
FROM
``analytics_xxxxxx.events_*``
WHERE
event_name = 'Main CTA'
AND _TABLE_SUFFIX BETWEEN '{{startYear}}{{startMonth}}{{startDay}}' AND '{{endYear}}{{endMonth}}{{endDay}}'
My experiment based on this metric is running without error but showing no data. I must be missing something really simple…it’s so close to working! But I can’t think of anything else to check.
s

strong-receptionist-72327

03/17/2023, 3:20 PM
can you share Logged-in users query also
b

busy-air-96466

03/17/2023, 3:29 PM
I’m not sure what you mean? The only other query is for the experiment:
-- Main CTA (binomial)
WITH
__rawExperiment as (
SELECT
user_id as user_id,
TIMESTAMP_MICROS(event_timestamp) as timestamp,
experiment_id_param.value.string_value AS experiment_id,
<http://variation_id_param.value.int|variation_id_param.value.int>_value AS variation_id,
geo.country as country,
traffic_source.source as source,
traffic_source.medium as medium,
device.category as device,
device.web_info.browser as browser,
device.operating_system as os
FROM
``analytics_xxxx.events_*`,`
UNNEST (event_params) AS experiment_id_param,
UNNEST (event_params) AS variation_id_param
WHERE
_TABLE_SUFFIX BETWEEN '20230315' AND '20230319'
AND event_name = 'viewed_experiment'
AND experiment_id_param.key = 'experiment_id'
AND variation_id_param.key = 'variation_id'
AND user_id is not null
),
__experiment as ( -- Viewed Experiment
SELECT
e.user_id as user_id,
cast(e.variation_id as string) as variation,
CAST(e.timestamp as DATETIME) as timestamp,
CAST(e.timestamp as DATETIME) as conversion_start,
DATETIME_ADD(CAST(e.timestamp as DATETIME), INTERVAL 72 HOUR) as conversion_end
FROM
__rawExperiment e
WHERE
e.experiment_id = 'new-main-panel'
AND CAST(e.timestamp as DATETIME) >= DATETIME("2023-03-15 08:00:00")
),
__metric as ( -- Metric (Main CTA)
SELECT
user_id as user_id,
1 as value,
CAST(m.timestamp as DATETIME) as timestamp,
CAST(m.timestamp as DATETIME) as conversion_start,
CAST(m.timestamp as DATETIME) as conversion_end
FROM
(
SELECT
user_id,
user_pseudo_id as anonymous_id,
TIMESTAMP_MICROS(event_timestamp) as timestamp
FROM
``analytics_xxxx.events_*``
WHERE
event_name = 'Main CTA'
) m
WHERE
CAST(m.timestamp as DATETIME) >= DATETIME("2023-03-15 08:00:00")
),
__distinctUsers as (
-- One row per user/dimension
SELECT
e.user_id,
cast('All' as string) as dimension,
(
CASE
WHEN count(distinct e.variation) > 1 THEN '__multiple__'
ELSE max(e.variation)
END
) as variation,
MIN(e.conversion_start) as conversion_start,
MIN(e.conversion_end) as conversion_end
FROM
__experiment e
GROUP BY
e.user_id
),
__userMetric as (
-- Add in the aggregate metric value for each user
SELECT
d.variation,
d.dimension,
d.user_id,
1 as value
FROM
__distinctUsers d
JOIN __metric m ON (m.user_id = d.user_id)
WHERE
m.timestamp >= d.conversion_start
AND m.timestamp <= d.conversion_end
GROUP BY
variation,
dimension,
d.user_id
),
__overallUsers as (
-- Number of users in each variation
SELECT
variation,
dimension,
COUNT(*) as users
FROM
__distinctUsers
GROUP BY
variation,
dimension
),
__stats as (
-- One row per variation/dimension with aggregations
SELECT
m.variation,
m.dimension,
COUNT(*) AS count,
SUM(COALESCE(m.value, 0)) AS main_sum,
SUM(POWER(COALESCE(m.value, 0), 2)) AS main_sum_squares
FROM
__userMetric m
GROUP BY
m.variation,
m.dimension
)
SELECT
u.variation,
u.dimension,
u.users as users,
u.users as count,
'mean' as statistic_type,
'binomial' as main_metric_type,
COALESCE(s.main_sum, 0) AS main_sum,
COALESCE(s.main_sum_squares, 0) AS main_sum_squares
FROM
__overallUsers u
LEFT JOIN __stats s ON (
s.variation = u.variation
AND s.dimension = u.dimension
)
s

strong-receptionist-72327

03/17/2023, 3:32 PM
this is wrong (AND event_name = 'viewed_experiment') it should be event_name='experiment_viewed'
b

busy-air-96466

03/17/2023, 3:44 PM
Thanks! Um. It seems that I can’t edit the experiment query. And I copied the event name from the docs:
const gb = new GrowthBook({
apiHost: "<https://cdn.growthbook.io>",
clientKey: "sdk-abc123",
// Targeting attributes
attributes: {
clientId: gaUserId,
userId: userId,
country: country,
...
},
trackingCallback: (experiment, result) => {
// track using GA4
if ("gtag" in window) {
window.gtag("event", "experiment_viewed", {
event_category: "experiment",
experiment_id: experiment.key,
variation_id: result.variationId,
...
});
} else {
console.log("no gtag");
}
},
});
Is this maybe an error in the docs?
s

strong-receptionist-72327

03/17/2023, 3:56 PM
you can edit the query go to datasource and there you get assignment table which is in your case Loggedin users just make edit there
b

busy-air-96466

03/17/2023, 4:04 PM
Thank you a thousand times!
🙌 1
I’ve learned and set up GA4, BigQuery, Google Tag Manager and GrowthBook from scratch, and you wouldn’t believe how confusing it all is when it’s new.
I’ve GOT A CHART!!!
🙌 2
10 Views