https://www.growthbook.io/ logo
#ask-questions
Title
# ask-questions
m

mysterious-hospital-86275

03/13/2024, 11:33 AM
Hi, can we change user segmentation queries in Growthbook?
f

fresh-football-47124

03/13/2024, 3:00 PM
can you explain what you're trying to do a bit more?
m

mysterious-hospital-86275

03/13/2024, 3:02 PM
GrowthBook with Ga4/Bigquery integration. Can we change the query which creates segments for user for each experiment variations. Instead of showing data w.r.t to users can we show sessions instead and related information.
I have shared one query below 1. Our aim is to show sessions instead of users from Bigquery db. 2. Problem we are facing is we can create and change query for metrics but we need to change the query which calculates users which fall under an experiment using _*experiment_key, date range*_. 3. Is there any way we can change the query and fetch sessions instead of users for. server side experiments.
Copy code
-- metric
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
      `reporting-346412`.`analytics_249515168`.`events_*`,
      UNNEST (event_params) AS experiment_id_param,
      UNNEST (event_params) AS variation_id_param
    WHERE
      (
        (_TABLE_SUFFIX BETWEEN '20240306' AND '20240313')
        OR (
          _TABLE_SUFFIX BETWEEN 'intraday_20240306' AND 'intraday_20240313'
        )
      )
      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 = 'ORGANIC_CHECKOUT'
      AND e.timestamp >= '2024-03-06 05:28:00'
      AND e.timestamp <= '2024-03-13 11:20:21'
  ),
  __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,
      cast('All' as string) AS dimension,
      variation,
      first_exposure_timestamp AS timestamp,
      date_trunc(first_exposure_timestamp, DAY) AS first_exposure_date
    FROM
      __experimentUnits
  ),
  __metric as ( -- Metric (buy_now)
    SELECT
      anonymous_id as anonymous_id,
      1 as value,
      CAST(m.timestamp as DATETIME) as timestamp
    FROM
      (
        SELECT
          user_pseudo_id as anonymous_id,
          TIMESTAMP_MICROS(event_timestamp) as timestamp
        FROM
          `reporting-346412.analytics_249515168.events_*`
        WHERE
          (
            (_TABLE_SUFFIX BETWEEN '20240306' AND '20240316')
            OR (
              _TABLE_SUFFIX BETWEEN 'intraday_20240306' AND 'intraday_20240316'
            )
          )
          AND event_name = 'buy_now'
      ) m
    WHERE
      m.timestamp >= '2024-03-06 05:28:00'
      AND m.timestamp <= '2024-03-16 11:20:21'
  ),
  __userMetricJoin as (
    SELECT
      d.variation AS variation,
      d.dimension AS dimension,
      d.anonymous_id AS anonymous_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.anonymous_id = d.anonymous_id)
  ),
  __userMetricAgg as (
    -- Add in the aggregate metric value for each user
    SELECT
      variation,
      dimension,
      anonymous_id,
      MAX(COALESCE(value, 0)) as value
    FROM
      __userMetricJoin
    GROUP BY
      variation,
      dimension,
      anonymous_id
  )
  -- One row per variation/dimension with aggregations
SELECT
  m.variation AS variation,
  m.dimension AS dimension,
  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,
  m.dimension
6 Views