https://www.growthbook.io/ logo
b

busy-air-96466

05/10/2023, 8:25 AM
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.
h

helpful-application-7107

05/10/2023, 1:28 PM
The figure is showing you only have 1 user in your treatment bucket.
Copy code
__rawExperiment as (
    SELECT
      user_id as user_id,
      TIMESTAMP_MICROS(event_timestamp) as timestamp,
      experiment_id_param.value.string_value AS experiment_id,
      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
  )
    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")
That query should show you all the experiment impressions that fit this experiment. My guess is there will be very few results.
b

busy-air-96466

05/10/2023, 2:27 PM
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…
The experiment is set up to use logged-in users, but the GA event timing seems wrong and I’m not yet seeing how I can change it…
@helpful-application-7107 @fresh-football-47124 thank you so much for your patience and assistance. Yes, it seems that the issue was one of timing, with a visual experiment. I put in a setTimeout - the JavaScript programmer’s last-ditch workaround - to delay the experiment_viewed GA event and it is all now working!
Copy code
const growthbook = new GrowthBook({
  apiHost: '<http://localhost:4100>',
  clientKey: 'sdk-wwD7n4nfhRYZXeac',
  enableDevMode: true,
  trackingCallback: (experiment, result) => {
    console.log(
      '*** Experiment Viewed. experimentId:',
      experiment.key,
      'variationId:',
      result.key,
    );

    // make sure a visual experiment based on Logged-in Users
    // will send its experiment_viewed event after the Login event
    // so that user_id is tracked with experiment results
    setTimeout(() => {
      // track using GA4
      window.sendGA4event('experiment_viewed', {
        event_category: 'experiment',
        experiment_id: experiment.key,
        variation_id: result.key,
      });
    }, 10);
  },
});
Login is now sent before experiment_viewed for visual as well as SDK experiments:
5 Views