Hi everyone, I've had a problem setting up my experiment I use the Node SDK to control the features and Big Query as my main data source, pulling data from Google Analytics 4 I created some metrics, and all the data in Growthbook matches what GA4 records. So, I think all my metrics are perfectly configured. The problem is that when I run an experiment and compare the metrics, the data doesn't make sense What my feature does is basically vary the layout of my cart page on my website. Everyone who makes a purchase on my website visits the cart page at least once, and consequently, everyone who makes a purchase automatically generates at least one "experiment_viewed" event. My problem is that, looking at GA4 and my internal CRM, I see that I've received a total of 846 purchases since the start of the experiment. However, the sum of the Baseline and Variation denominators are not equal to even half of the total purchases on my website. From what I understood from reading the documentation, those denominators represent The number of people who participated in the experiment and contributed to the metric connected to the experiment. Therefore, all buyers from my website should be included here Can someone help me with this? Maybe I misunderstood something? Thanks in advance

hey, hey! can you share how that metric is set up? You're right that the denominator doesn't look right. Let's start there. If that's not it, there could be something amiss with the implementation, but this seems like a metric definition thing.

The main metric of the Experiment "Purchases Revenue": I already compared the numbers outside the experiment vs GA4 and the precision is really high it vary 5% max but is probably for some timezone issue, not a big deal Query: Purchase Revenue

WITH purchases AS (

SELECT

e.user_pseudo_id AS user_id,

DATETIME(TIMESTAMP_MICROS(e.event_timestamp), "America/New_York") AS timestamp,

COALESCE((

SELECT ep.value.double_value

FROM UNNEST(e.event_params) ep

WHERE ep.key = "coupon_value"

), 0.0) AS coupon_value,

e.items AS items

FROM

make-my-freshener.analytics_342944115.events_* e

WHERE

e.event_name = 'purchase'

)

SELECT

p.user_id,

p.timestamp,

SUM(

SAFE_CAST(item.quantity AS INT64) * SAFE_CAST(item.price AS FLOAT64)

) - p.coupon_value AS value

FROM

purchases p,

UNNEST(p.items) AS item

GROUP BY

p.user_id,

p.timestamp,

p.coupon_value

ORDER BY

p.timestamp DESC

Metric "Purchases": Counts the number of the purchases Query:

SELECT

user_pseudo_id AS user_id,

DATETIME(TIMESTAMP_MICROS(event_timestamp), "America/New_York") AS timestamp,

1 AS value

FROM

``make-my-freshener.analytics_342944115.events_*``

WHERE

event_name = 'purchase'

Thanks for sharing. Can you also look at the actual experiment results query? on the experiment results page, click on the three dots ⋮ next to update and view the queries that GrowthBook is running to return your results. See if you see anything amiss and paste them here.

Experiment Results Query:

-- Purchases revenue (revenue)

WITH

__rawExperiment AS (

SELECT

COALESCE(t.user_id, t.user_pseudo_id) AS user_id,

TIMESTAMP_MICROS(t.event_timestamp) AS timestamp,

(

SELECT

ep.value.string_value

FROM

UNNEST (t.event_params) ep

WHERE

ep.key = 'experiment_id'

) AS experiment_id,

(

SELECT

<http://ep.value.int|ep.value.int>_value

FROM

UNNEST (t.event_params) ep

WHERE

ep.key = 'variation_id'

) AS variation_id

FROM

``make-my-freshener`.

analytics_342944115

.

events_*

AS t`

WHERE

t.event_name = 'experiment_viewed'

),

__experimentExposures 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

FROM

__rawExperiment e

WHERE

e.experiment_id = 'quantity-field-version'

AND e.timestamp >= '2025-08-06 16:57:11'

AND e.timestamp <= '2025-09-12 18:47:28'

),

__experimentUnits AS (

-- One row per user

SELECT

e.user_id AS user_id,

(

CASE

WHEN count(distinct e.variation) > 1 THEN '__multiple__'

ELSE max(e.variation)

END

) AS variation,

MIN(e.timestamp) AS first_exposure_timestamp

FROM

__experimentExposures e

GROUP BY

e.user_id

),

__distinctUsers AS (

SELECT

user_id,

variation,

first_exposure_timestamp AS timestamp,

date_trunc(first_exposure_timestamp, DAY) AS first_exposure_date

FROM

__experimentUnits

),

__metric as ( -- Metric (Purchases revenue)

SELECT

user_id as user_id,

m.value as value,

CAST(m.timestamp as DATETIME) as timestamp

FROM

(

WITH

purchases AS (

SELECT

e.user_pseudo_id AS user_id,

DATETIME(

TIMESTAMP_MICROS(e.event_timestamp),

"America/New_York"

) AS timestamp,

COALESCE(

(

SELECT

ep.value.double_value

FROM

UNNEST (e.event_params) ep

WHERE

ep.key = "coupon_value"

),

0.0

) AS coupon_value,

e.items AS items

FROM

``make-my-freshener.analytics_342944115.events_*` e`

WHERE

e.event_name = 'purchase'

)

SELECT

p.user_id,

p.timestamp,

SUM(

SAFE_CAST(item.quantity AS INT64) * SAFE_CAST(item.price AS FLOAT64)

) - p.coupon_value AS value

FROM

purchases p,

UNNEST (p.items) AS item

GROUP BY

p.user_id,

p.timestamp,

p.coupon_value

ORDER BY

p.timestamp DESC

) m

WHERE

m.timestamp >= '2025-08-06 16:57:11'

AND m.timestamp <= '2025-09-18 18:47:28'

),

__denominator0 as ( -- Metric (Purchases)

SELECT

user_id as user_id,

m.value as value,

CAST(m.timestamp as DATETIME) as timestamp

FROM

(

SELECT

user_pseudo_id AS user_id,

DATETIME(

TIMESTAMP_MICROS(event_timestamp),

"America/New_York"

) AS timestamp,

1 AS value

FROM

``make-my-freshener.analytics_342944115.events_*``

WHERE

event_name = 'purchase'

) m

WHERE

m.timestamp >= '2025-08-06 16:57:11'

AND m.timestamp <= '2025-09-18 18:47:28'

),

__userMetricJoin as (

SELECT

d.variation AS variation,

d.user_id AS user_id,

(

CASE

WHEN m.timestamp >= d.timestamp

AND m.timestamp <= DATETIME_ADD(d.timestamp, INTERVAL 72 HOUR) THEN m.value

ELSE NULL

END

) as value

FROM

__distinctUsers d

LEFT JOIN __metric m ON (m.user_id = d.user_id)

),

__userMetricAgg as (

-- Add in the aggregate metric value for each user

SELECT

umj.variation AS variation,

umj.user_id,

SUM(COALESCE(value, 0)) as value

FROM

__userMetricJoin umj

GROUP BY

umj.variation,

umj.user_id

),

__userDenominatorAgg AS (

SELECT

d.variation AS variation,

d.user_id AS user_id,

SUM(COALESCE(value, 0)) as value

FROM

__distinctUsers d

JOIN __denominator0 m ON (m.user_id = d.user_id)

WHERE

m.timestamp >= d.timestamp

AND m.timestamp <= DATETIME_ADD(d.timestamp, INTERVAL 72 HOUR)

GROUP BY

d.variation,

d.user_id

)

-- One row per variation/dimension with aggregations

SELECT

m.variation AS variation,

COUNT(*) AS users,

SUM(COALESCE(m.value, 0)) AS main_sum,

SUM(POWER(COALESCE(m.value, 0), 2)) AS main_sum_squares,

SUM(COALESCE(d.value, 0)) AS denominator_sum,

SUM(POWER(COALESCE(d.value, 0), 2)) AS denominator_sum_squares,

SUM(COALESCE(d.value, 0) * COALESCE(m.value, 0)) AS main_denominator_sum_product

FROM

__userMetricAgg m

LEFT JOIN __userDenominatorAgg d ON (d.user_id = m.user_id)

GROUP BY

m.variation

I think this might be a timezone issue. Are you able to remove the timezone conversion from your purchase metric defintions?

Hi @strong-mouse-55694, thanks! I removed the timezone formatting, and the denominators increased quite a bit! What's the problem with formatting queries in Growthbook? Do you do the time formatting automatically? Or maybe you only work with results in UTC time?

Few things to check: • With the timezone aspect, it's just about ensuring that the date ranges match across your metrics and experiment exposures. • In relation to that, it looks like your metric has a conversion window set up, which means that not all purchases will be counted, only those that fall in the 72 hours after conversion. • It looks like the metric and experiment assignment are using slightly different identifiers. The experiment exposure is using user ID and pseudo user ID, but the metric is only using user ID. This may make a difference.