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.

can you share Logged-in users query also

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

)

this is wrong (AND event_name = 'viewed_experiment') it should be event_name='experiment_viewed'

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?

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

Thank you a thousand times!