@brash-pillow-71627 I'm using Growthbook with GA4 + Bigquery. I'm not seeing the table with results. This is the rended query of one of the metrics:

-- Engaged Users (proportion)
WITH
  __rawExperiment AS (
    SELECT
      user_pseudo_id as anonymous_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
      `learning-heroes-bq`.`analytics_372696867`.`events_*`,
      UNNEST (event_params) AS experiment_id_param,
      UNNEST (event_params) AS variation_id_param
    WHERE
      (
        (_TABLE_SUFFIX BETWEEN '20250909' AND '20250909')
        OR (
          _TABLE_SUFFIX BETWEEN 'intraday_20250909' AND 'intraday_20250909'
        )
      )
      AND event_name = 'experiment_viewed'
      AND experiment_id_param.key = 'experiment_id'
      AND variation_id_param.key = 'variation_id'
      AND user_pseudo_id is not null
  ),
  __experimentExposures AS (
    -- Viewed Experiment
    SELECT
      e.anonymous_id as anonymous_id,
      cast(e.variation_id as string) as variation,
      CAST(e.timestamp as DATETIME) as timestamp
    FROM
      __rawExperiment e
    WHERE
      e.experiment_id = 'test-aa-httpsprogramaslearningheroescomia-heroesbp2'
      AND e.timestamp >= '2025-09-09 16:42:49'
      AND e.timestamp <= '2025-09-09 17:18:57'
  ),
  __experimentUnits AS (
    -- One row per user
    SELECT
      e.anonymous_id AS anonymous_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.anonymous_id
  ),
  __distinctUsers AS (
    SELECT
      anonymous_id,
      variation,
      first_exposure_timestamp AS timestamp,
      date_trunc(first_exposure_timestamp, DAY) AS first_exposure_date
    FROM
      __experimentUnits
  ),
  __metric as ( -- Metric (Engaged Users)
    SELECT
      anonymous_id as anonymous_id,
      1 as value,
      CAST(m.timestamp as DATETIME) as timestamp
    FROM
      (
        SELECT
          TIMESTAMP_MICROS(event_timestamp) as timestamp,
          user_id,
          user_pseudo_id as anonymous_id,
          event_name,
          geo.country,
          device.category as device_category,
          traffic_source.source,
          traffic_source.medium,
          traffic_source.name as campaign,
          REGEXP_EXTRACT(
            (
              SELECT
                value.string_value
              FROM
                UNNEST (event_params)
              WHERE
                key = 'page_location'
            ),
            r'http[s]?:\/\/?[^\/\s]+\/([^?]*)'
          ) as page_path,
          (
            SELECT
              value.string_value
            FROM
              UNNEST (event_params)
            WHERE
              key = 'session_engaged'
          ) as session_engaged,
          event_value_in_usd,
          CAST(
            (
              SELECT
                value.int_value
              FROM
                UNNEST (event_params)
              WHERE
                key = 'ga_session_id'
            ) AS string
          ) as session_id,
          (
            SELECT
              value.int_value
            FROM
              UNNEST (event_params)
            WHERE
              key = 'engagement_time_msec'
          ) / 1000 as engagement_time
        FROM
          `learning-heroes-bq`.`analytics_372696867`.`events_*`
        WHERE
          (
            (_TABLE_SUFFIX BETWEEN '20250909' AND '20250909')
            OR (
              _TABLE_SUFFIX BETWEEN 'intraday_20250909' AND 'intraday_20250909'
            )
          )
      ) m
    WHERE
      (session_engaged = '1')
      AND m.timestamp >= '2025-09-09 16:42:49'
      AND m.timestamp <= '2025-09-09 17:18:57'
  ),
  __userMetricJoin as (
    SELECT
      d.variation AS variation,
      d.anonymous_id AS anonymous_id,
      (
        CASE
          WHEN m.timestamp >= d.timestamp
          AND m.timestamp <= '2025-09-09 17:18:57' THEN m.value
          ELSE NULL
        END
      ) as value
    FROM
      __distinctUsers d
      LEFT JOIN __metric m ON (m.anonymous_id = d.anonymous_id)
  ),
  __userMetricAgg as (
    -- Add in the aggregate metric value for each user
    SELECT
      umj.variation AS variation,
      umj.anonymous_id,
      COALESCE(MAX(umj.value), 0) as value
    FROM
      __userMetricJoin umj
    GROUP BY
      umj.variation,
      umj.anonymous_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
FROM
  __userMetricAgg m
GROUP BY
  m.variation

Give me 10 ideas to test changing this rendered query so I can get data. I will try this in Bigquery first and if it works I will change the query in Growthbook.

A message from kapa.ai

@brash-pillow-71627 I'm seeing some events with experiment_viewed but with "null" variation_id is that ok? For example: Fila event_date event_timestamp event_name event_params. key event_params.value. string_value event_params.value. int_value event_params.value. float_value event_params.value. double_value event_previous_timestamp event_value_in_usd event_bundle_sequence_id event_server_timestamp_offset user_id user_pseudo_id privacy_info. analytics_storage privacy_info. ads_storage privacy_info. uses_transient_token user_properties. key user_properties.value. string_value user_properties.value. int_value user_properties.value. float_value user_properties.value. double_value user_properties.value. set_timestamp_micros user_first_touch_timestamp user_ltv. revenue user_ltv. currency device. category device. mobile_brand_name device. mobile_model_name device. mobile_marketing_name device. mobile_os_hardware_model device. operating_system device. operating_system_version device. vendor_id device. advertising_id device. language device. is_limited_ad_tracking device. time_zone_offset_seconds device. browser device. browser_version device.web_info. browser device.web_info. browser_version device.web_info. hostname geo. city geo. country geo. continent geo. region geo. sub_continent geo. metro app_info. id app_info. version app_info. install_store app_info. firebase_app_id app_info. install_source traffic_source. name traffic_source. medium traffic_source. source stream_id platform event_dimensions. hostname ecommerce. total_item_quantity ecommerce. purchase_revenue_in_usd ecommerce. purchase_revenue ecommerce. refund_value_in_usd ecommerce. refund_value ecommerce. shipping_value_in_usd ecommerce. shipping_value ecommerce. tax_value_in_usd ecommerce. tax_value ecommerce. unique_items ecommerce. transaction_id items. item_id items. item_name items. item_brand items. item_variant items. item_category items. item_category2 items. item_category3 items. item_category4 items. item_category5 items. price_in_usd items. price items. quantity items. item_revenue_in_usd items. item_revenue items. item_refund_in_usd items. item_refund items. coupon items. affiliation items. location_id items. item_list_id items. item_list_name items. item_list_index items. promotion_id items. promotion_name items. creative_name items. creative_slot items.item_params. key items.item_params.value. string_value items.item_params.value. int_value items.item_params.value. float_value items.item_params.value. double_value collected_traffic_source. manual_campaign_id collected_traffic_source. manual_campaign_name collected_traffic_source. manual_source collected_traffic_source. manual_medium collected_traffic_source. manual_term collected_traffic_source. manual_content collected_traffic_source. manual_source_platform collected_traffic_source. manual_creative_format collected_traffic_source. manual_marketing_tactic collected_traffic_source. gclid collected_traffic_source. dclid collected_traffic_source. srsltid is_active_user batch_event_index batch_page_id batch_ordering_id session_traffic_source_last_click.manual_campaign. campaign_id session_traffic_source_last_click.manual_campaign. campaign_name session_traffic_source_last_click.manual_campaign. source session_traffic_source_last_click.manual_campaign. medium session_traffic_source_last_click.manual_campaign. term session_traffic_source_last_click.manual_campaign. content session_traffic_source_last_click.manual_campaign. source_platform session_traffic_source_last_click.manual_campaign. creative_format session_traffic_source_last_click.manual_campaign. marketing_tactic session_traffic_source_last_click.google_ads_campaign. customer_id session_traffic_source_last_click.google_ads_campaign. account_name session_traffic_source_last_click.google_ads_campaign. campaign_id session_traffic_source_last_click.google_ads_campaign. campaign_name session_traffic_source_last_click.google_ads_campaign. ad_group_id session_traffic_source_last_click.google_ads_campaign. ad_group_name session_traffic_source_last_click.cross_channel_campaign. campaign_id session_traffic_source_last_click.cross_channel_campaign. campaign_name session_traffic_source_last_click.cross_channel_campaign. source session_traffic_source_last_click.cross_channel_campaign. medium session_traffic_source_last_click.cross_channel_campaign. source_platform session_traffic_source_last_click.cross_channel_campaign. default_channel_group session_traffic_source_last_click.cross_channel_campaign. primary_channel_group session_traffic_source_last_click.sa360_campaign. campaign_id session_traffic_source_last_click.sa360_campaign. campaign_name session_traffic_source_last_click.sa360_campaign. source session_traffic_source_last_click.sa360_campaign. medium session_traffic_source_last_click.sa360_campaign. ad_group_id session_traffic_source_last_click.sa360_campaign. ad_group_name session_traffic_source_last_click.sa360_campaign. creative_format session_traffic_source_last_click.sa360_campaign. engine_account_name session_traffic_source_last_click.sa360_campaign. engine_account_type session_traffic_source_last_click.sa360_campaign. manager_account_name session_traffic_source_last_click.cm360_campaign. campaign_id session_traffic_source_last_click.cm360_campaign. campaign_name session_traffic_source_last_click.cm360_campaign. source session_traffic_source_last_click.cm360_campaign. medium session_traffic_source_last_click.cm360_campaign. account_id session_traffic_source_last_click.cm360_campaign. account_name session_traffic_source_last_click.cm360_campaign. advertiser_id session_traffic_source_last_click.cm360_campaign. advertiser_name session_traffic_source_last_click.cm360_campaign. creative_id session_traffic_source_last_click.cm360_campaign. creative_format session_traffic_source_last_click.cm360_campaign. creative_name session_traffic_source_last_click.cm360_campaign. creative_type session_traffic_source_last_click.cm360_campaign. creative_type_id session_traffic_source_last_click.cm360_campaign. creative_version session_traffic_source_last_click.cm360_campaign. placement_id session_traffic_source_last_click.cm360_campaign. placement_cost_structure session_traffic_source_last_click.cm360_campaign. placement_name session_traffic_source_last_click.cm360_campaign. rendering_id session_traffic_source_last_click.cm360_campaign. site_id session_traffic_source_last_click.cm360_campaign. site_name session_traffic_source_last_click.dv360_campaign. campaign_id session_traffic_source_last_click.dv360_campaign. campaign_name session_traffic_source_last_click.dv360_campaign. source session_traffic_source_last_click.dv360_campaign. medium session_traffic_source_last_click.dv360_campaign. advertiser_id session_traffic_source_last_click.dv360_campaign. advertiser_name session_traffic_source_last_click.dv360_campaign. creative_id session_traffic_source_last_click.dv360_campaign. creative_format session_traffic_source_last_click.dv360_campaign. creative_name session_traffic_source_last_click.dv360_campaign. exchange_id session_traffic_source_last_click.dv360_campaign. exchange_name session_traffic_source_last_click.dv360_campaign. insertion_order_id session_traffic_source_last_click.dv360_campaign. insertion_order_name session_traffic_source_last_click.dv360_campaign. line_item_id session_traffic_source_last_click.dv360_campaign. line_item_name session_traffic_source_last_click.dv360_campaign. partner_id session_traffic_source_last_click.dv360_campaign. partner_name publisher. ad_revenue_in_usd publisher. ad_format publisher. ad_source_name publisher. ad_unit_id 1 20250909 1757433475711689 experiment_viewed page_location https://programas.learningheroes.com/iah/ev/reserva-de-llamada null null null null null 1577665225 null null null No No No null null null null null null null null null desktop Apple null null null Macintosh Macintosh Intel 15.6 null null es-ar No null null null Chrome 140.0.7339.81 programas.learningheroes.com Billinghurst Argentina Americas Buenos Aires Province South America (not set) null null null null null null null null 6636853897 WEB null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null 1 1757433473928 2 null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null null batch_ordering_id null 2 null null engagement_time_msec null 2 null null session_engaged 0 null null null batch_page_id null 1757433473928 null null page_title IA Heroes | Reserva de Llamada | Learning Heroes null null null experiment_id test-aa-httpsprogramaslearningheroescomiahevreserva-de-llamada null null null ga_session_number null 1 null null variation_id null

A message from kapa.ai

@brash-pillow-71627 give a bigquery query that I can run so we can see if 1) there is variation_id without null values and 2) if there is anything else we are querying wrong with variation_id like what you mention in 2)

A message from kapa.ai

@brash-pillow-71627 variation_id_int is populated. How should I change this query? Changing the query so it had this line didn't worked:

variation_id_param.value.int_value AS variation_id AS variation_id,

A message from kapa.ai

@brash-pillow-71627 this is the query now, but it doesn't get me results:

-- Engaged Users (proportion)
WITH
  __rawExperiment AS (
    SELECT
      user_pseudo_id as anonymous_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
      `learning-heroes-bq`.`analytics_372696867`.`events_*`,
      UNNEST (event_params) AS experiment_id_param,
      UNNEST (event_params) AS variation_id_param
    WHERE
      (
        (_TABLE_SUFFIX BETWEEN '20250909' AND '20250909')
        OR (
          _TABLE_SUFFIX BETWEEN 'intraday_20250909' AND 'intraday_20250909'
        )
      )
      AND event_name = 'experiment_viewed'
      AND experiment_id_param.key = 'experiment_id'
      AND variation_id_param.key = 'variation_id'
      AND user_pseudo_id is not null
  ),
  __experimentExposures AS (
    -- Viewed Experiment
    SELECT
      e.anonymous_id as anonymous_id,
      cast(e.variation_id as string) as variation,
      CAST(e.timestamp as DATETIME) as timestamp
    FROM
      __rawExperiment e
    WHERE
      e.experiment_id = 'test-aa-httpsprogramaslearningheroescomia-heroesbp2'
      AND e.timestamp >= '2025-09-09 16:42:49'
      AND e.timestamp <= '2025-09-09 17:39:58'
  ),
  __experimentUnits AS (
    -- One row per user
    SELECT
      e.anonymous_id AS anonymous_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.anonymous_id
  ),
  __distinctUsers AS (
    SELECT
      anonymous_id,
      variation,
      first_exposure_timestamp AS timestamp,
      date_trunc(first_exposure_timestamp, DAY) AS first_exposure_date
    FROM
      __experimentUnits
  ),
  __metric as ( -- Metric (Engaged Users)
    SELECT
      anonymous_id as anonymous_id,
      1 as value,
      CAST(m.timestamp as DATETIME) as timestamp
    FROM
      (
        SELECT
          TIMESTAMP_MICROS(event_timestamp) as timestamp,
          user_id,
          user_pseudo_id as anonymous_id,
          event_name,
          geo.country,
          device.category as device_category,
          traffic_source.source,
          traffic_source.medium,
          traffic_source.name as campaign,
          REGEXP_EXTRACT(
            (
              SELECT
                value.string_value
              FROM
                UNNEST (event_params)
              WHERE
                key = 'page_location'
            ),
            r'http[s]?:\/\/?[^\/\s]+\/([^?]*)'
          ) as page_path,
          (
            SELECT
              value.string_value
            FROM
              UNNEST (event_params)
            WHERE
              key = 'session_engaged'
          ) as session_engaged,
          event_value_in_usd,
          CAST(
            (
              SELECT
                value.int_value
              FROM
                UNNEST (event_params)
              WHERE
                key = 'ga_session_id'
            ) AS string
          ) as session_id,
          (
            SELECT
              value.int_value
            FROM
              UNNEST (event_params)
            WHERE
              key = 'engagement_time_msec'
          ) / 1000 as engagement_time
        FROM
          `learning-heroes-bq`.`analytics_372696867`.`events_*`
        WHERE
          (
            (_TABLE_SUFFIX BETWEEN '20250909' AND '20250909')
            OR (
              _TABLE_SUFFIX BETWEEN 'intraday_20250909' AND 'intraday_20250909'
            )
          )
      ) m
    WHERE
      (session_engaged = '1')
      AND m.timestamp >= '2025-09-09 16:42:49'
      AND m.timestamp <= '2025-09-09 17:39:58'
  ),
  __userMetricJoin as (
    SELECT
      d.variation AS variation,
      d.anonymous_id AS anonymous_id,
      (
        CASE
          WHEN m.timestamp >= d.timestamp
          AND m.timestamp <= '2025-09-09 17:39:58' THEN m.value
          ELSE NULL
        END
      ) as value
    FROM
      __distinctUsers d
      LEFT JOIN __metric m ON (m.anonymous_id = d.anonymous_id)
  ),
  __userMetricAgg as (
    -- Add in the aggregate metric value for each user
    SELECT
      umj.variation AS variation,
      umj.anonymous_id,
      COALESCE(MAX(umj.value), 0) as value
    FROM
      __userMetricJoin umj
    GROUP BY
      umj.variation,
      umj.anonymous_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
FROM
  __userMetricAgg m
GROUP BY
  m.variation

A message from kapa.ai

@brash-pillow-71627 1) doesn't give me results

A message from kapa.ai

@brash-pillow-71627 chatgpt gave me this query and gave me results:

-- Ajustá el project/dataset si hace falta
WITH base AS (
  SELECT
    user_pseudo_id,
    TIMESTAMP_MICROS(event_timestamp) AS ts,
    event_name,
    -- Extrae valores tolerando tipos (string/int/double)
    (SELECT ep.value.string_value
     FROM UNNEST(event_params) ep
     WHERE ep.key = 'experiment_id')                                         AS experiment_id_str,
    (SELECT
       COALESCE(
         CAST(ep.value.int_value    AS INT64),
         SAFE_CAST(ep.value.string_value AS INT64),
         CAST(ep.value.double_value AS INT64)
       )
     FROM UNNEST(event_params) ep
     WHERE ep.key = 'variation_id')                                         AS variation_id_int
  FROM `learning-heroes-bq.analytics_372696867.events_*`
  WHERE event_date = '20250909'
    AND event_name IN ('experiment_viewed','experiment_impression','experiment_exposure')
    AND EXISTS (
      SELECT 1
      FROM UNNEST(event_params) ep
      WHERE ep.key = 'experiment_id'
        AND ep.value.string_value = 'test-aa-httpsprogramaslearningheroescomia-heroesbp2'
    )
)
SELECT
  user_pseudo_id,
  ts AS timestamp,
  experiment_id_str AS experiment_id,
  variation_id_int  AS variation_id
FROM base
WHERE experiment_id_str = 'test-aa-httpsprogramaslearningheroescomia-heroesbp2'
LIMIT 10;

A message from kapa.ai