Hey, I am still hoping against hope to find out why my ‘count’ experiment doesn’t show results? The metric is defined with this SQL:

SELECT

user_id,

user_pseudo_id as anonymous_id,

TIMESTAMP_MICROS(event_timestamp) as timestamp,

1 as value

FROM

``analytics_356435236.events_*``

WHERE

event_name = 'Click counter button'

AND _TABLE_SUFFIX BETWEEN '20230328' AND '20231231'

And it shows a graph that looks as I’d expect from my mocked-up BigQuery data, but the experiment is showing a result of ‘1’ instead of the expected 5 to 10 clicks per user. This is the experiment query:

-- Counter button clicks, no intraday in query (count)

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

(

SELECT

*

FROM

``analytics_356435236.events_*``

WHERE

_TABLE_SUFFIX BETWEEN '20230330' AND '20230512'

UNION ALL

SELECT

*

FROM

``analytics_356435236.events_intraday_*``

WHERE

_TABLE_SUFFIX BETWEEN '20230330' AND '20230512'

),

UNNEST (event_params) AS experiment_id_param,

UNNEST (event_params) AS variation_id_param

WHERE

event_name = 'experiment_viewed'

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-secondary-panel-title'

AND CAST(e.timestamp as DATETIME) >= DATETIME("2023-03-30 17:01:00")

),

__metric as ( -- Metric (Counter button clicks, no intraday in query)

SELECT

user_id as user_id,

m.value 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,

1 as value

FROM

``analytics_356435236.events_*``

WHERE

event_name = 'Click counter button'

AND _TABLE_SUFFIX BETWEEN '20230328' AND '20231231'

) m

WHERE

CAST(m.timestamp as DATETIME) >= DATETIME("2023-03-30 17:01:00")

),

__distinctUsers as (

-- One row per user

SELECT

e.user_id as 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,

SUM(m.value) 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

d.variation,

d.dimension,

d.user_id

),

__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

),

__overallUsers as (

-- Number of users in each variation/dimension

SELECT

variation,

dimension,

COUNT(*) as users

FROM

__distinctUsers

GROUP BY

variation,

dimension

)

SELECT

u.variation,

u.dimension,

u.users AS users,

'mean' as statistic_type,

'count' 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 (

u.variation = s.variation

AND u.dimension = s.dimension

)

I’ve essentially been stuck on this for weeks, I can’t see how to make a ‘count’ experiment work. I can download some sample data as CSV if that will help? Please, I am sure there must be something I’m doing wrong, but I cannot see it.

The figure is showing you only have 1 user in your treatment bucket.

Thank you. I looked at the raw data again and it seems that user_id isn’t recorded for this experiment, and in the Network tab I see that when the user logs in, the exeriment_viewed event for the visual experiment, new-secondary-panel, is sent before the login event. Whereas for the (working) sdk experiment, aa-test-feature, it is sent after the login event. So that might be the root of the issue…