https://www.growthbook.io/ logo
Join Slack
Powered by
Hey! Question on how best to model trial outcome m...
# experimentation
b
Hey! Question on how best to model trial outcome metrics, like trial to paid conversion, week 1 active, week 2 active
đź‘€ 1
Setup: We have a 
trials
table with one row per trial — includes 
trial_started_at
, 
converted_at
(null if not converted), and boolean flags for W1/W2 activity. What we wanted: One Fact Table reflecting the whole 
trials
table, with multiple metrics (conversion, W1AA, W2AA) built on top of it via filters. Problem we ran into: • Fact Tables require a single timestamp + identifier field • If we use 
trial_started_at
as the timestamp for the whole table, every metric built on it breaks — since GrowthBook's Proportion metrics check 
timestamp >= exposure_timestamp
, and trial start is always at/before exposure, no metric (conversion, W1AA, or W2AA) can ever satisfy that condition, so we get no data across the board Our workaround: • Instead of one shared table, we're building a separate Fact Table per outcome (conversion, W1AA, W2AA), each still representing the full trial population • Timestamp = when that specific event happened (e.g. 
converted_at
) if it happened, otherwise falls back to 
trial_started_at
• This keeps every trial represented so the metric shows an overall conversion rate, while rows where the event did happen have a timestamp that lands after exposure — solving the 
timestamp >= exposure
issue Is this the right pattern, or is there a better way to model multiple trial-outcome metrics off of one underlying table in GrowthBook?
a
@brief-honey-45610 following up - would love help here on if this is the right way to approach this. we believe our workaround isn't the best solution and would love insights
c
Can you help me clarify what your source of truth is for W1AA, for example?
What is the precise data/condition that means someone should be W1AA?
a
we have a column in our trials table for every trial, where there is a column that gives 'true' or 'false' on if they are W1AA, and another column with the time they became w1aa
c
So this table has one row per trial and that row gets updated whenever the state changes?
a
yes
c
Copy code
SELECT user_id, converted_at AS timestamp, 'conversion' AS event
FROM trials WHERE converted_at IS NOT NULL

UNION ALL

SELECT user_id, w1aa_at AS timestamp, 'w1aa' AS event
FROM trials WHERE w1aa_at IS NOT NULL

UNION ALL

SELECT user_id, w2aa_at AS timestamp, 'w2aa' AS event
FROM trials WHERE w2aa_at IS NOT NULL
You can set up one fact table like this, so that it is one row per "event" that occurred. Then your metrics can be set based on the 
event
column with a single 
timestamp
column. It will be more organized and more efficient query wise than separate fact tables.
I also wonder if writing a custom filter is another approach, e.g. proportion metric where you have 
w1aa_converted_at IS NOT NULL
and then you keep using 
timestamp >= exposure
. This then means that W1AA is "converted W1AA and trial started after exposure".
b
thanks, @calm-tailor-66162! these are great ideas. am i right in thinking using 
IS NOT NULL
in the above query would exclude trials that did not convert or become w1aa, w2aa, from the table? so the downside would be we couldn't look at overall conversion rate for these metrics (it would be 100%) because the full trial population wouldn't be there. whereas this separate fact table could look at trials that converted / overall trials
Copy code
SELECT
  t.* EXCLUDE (fleetio_account_id, converted_on),
  d.* EXCLUDE (fleetio_account_id),
  t.fleetio_account_id AS account_id,
  COALESCE(t.converted_on, t.trial_started_on) AS timestamp
FROM
  prod.metrics_product.trials t
  LEFT JOIN prod.core.dim_customer_accounts d
    ON t.fleetio_account_id = d.fleetio_account_id
đź‘€ 1
l
I ran into a very similar problem: we had a user → subscription model with a bunch of extra subscription-level fields, which made experiment eligibility awkward to express. For trial subscriptions, I ended up unpivoting the data into an event-based representation. The resulting dataset is basically a 
UNION
of two parts: 1. The original events: 
user, subscription, event (i.e. trial_start, trial_converted, auto_renewal_off, etc), timestamp
2. Synthetic 
trial_active
events: 
user, subscription, event = 'trial_active', timestamp
For the second part, I generate timestamps covering the entire trial period. In ClickHouse, it looks roughly like this:
Copy code
ARRAY JOIN arraySort(arrayDistinct(arrayConcat(
    -- first minute: every second
    arrayMap(
        x -> purchase_time + x,
        range(0, 60)
    ),

    -- from 1 minute to 1 hour: every minute
    arrayMap(
        x -> purchase_time + x * 60,
        range(1, 61)
    ),

    -- after the first hour: every 5 minutes
    arrayMap(
        x -> purchase_time + 3600 + x * 300,
        range(
            1,
            intDiv(trial_days * 24 * 3600 - 3600, 300) + 1
        )
    )
))) AS timestamp
ARRAY JOIN
then expands that timestamp array into individual rows, so conceptually a single trial subscription:
Copy code
user_123 | sub_456 | trial_start | 10:00:00
turns into something like:
Copy code
user_123 | sub_456 | trial_active | 10:00:00
user_123 | sub_456 | trial_active | 10:00:01
user_123 | sub_456 | trial_active | 10:00:02
...
user_123 | sub_456 | trial_active | 10:01:00
user_123 | sub_456 | trial_active | 10:02:00
...
user_123 | sub_456 | trial_active | 11:05:00
user_123 | sub_456 | trial_active | 11:10:00
...
The main benefit is that experiment logic no longer needs to know anything special about subscription state. For example, a user can start a trial during onboarding and only get exposed to an experiment later. At the time of exposure, there will still be a nearby 
trial_active
event, so the user can be matched against it and included in the experiment. In other words, instead of treating “trial is active” as a static subscription attribute, I represent it as a stream of synthetic events while the trial remains active.
đź‘€ 1
c
hey @breezy-holiday-30736 > am i right in thinking using 
IS NOT NULL
in the above query would exclude trials that did not convert Not really. The number of exposed users makes up the denominator, not the fact table. The fact table here gives the numerator so dropping or keeping NULLs give the same result. A question about the timing. You said that exposure happens at trial start or after it. Have you looked at the distribution of time-between-trial-start-and-exposure? If some users get treated immediately but some with only 2 days left of the trial, those treatment effects can be quite different and you might want to know which one dominates the average effect you're estimating. Related question: What would it mean to measure W1AA for users getting exposed in their second week of the trial? Probably matters what kind of feature you're testing and I'm sure you've spent a lot of time thinking of this already. But anyway wanted to raise the very interesting interplay of timings here. Happy to continue the discussion, here or in a DM if you prefer.
2 Views
Open in Slack
PreviousNext
Powered by