Hey folks, I'm trying to migrate our metrics to the fact metrics approach. In particular, I'm working on those metrics that are defined as ratio metric such as retention rate: the denominator shouldn't include all the users exposed to the experiments but only those that are eligible to retain (those that at some moment converted to pay customers). While this was pretty easy to do in the old metrics (

Fig.1

), I'm struggling to replicate the same with the fact metrics approach (

Fig.2

), were apparently a user doesn't have to belong to the denominator to be eligible for the numerator -indeed you can end up having results which are bigger than 1 (

Fig.3

). Is this expected or am I doing something wrong? Thanks fyi @stocky-zoo-87795

the denominator shouldn't include all the users exposed to the experiments but only those that are eligible to retain (those that at some moment converted to pay customers).

Hi Francesco, I'm a data scientist at GrowthBook. Thanks for your question. You are right that the denominator incorrectly includes all users for retention metrics. We do this because: 1. it reduces query costs 2. the bias due to incorrectly including these users attenuates over time If desired, you can see how big of an impact these exclusions have on retention: 1. for your experiment, go to the Overview tab 2. select Edit 3. under Metric Conversion Windows select Exclude In-progress Conversions We may update this logic down the road. Luke

Hey @steep-dog-1694 thanks for the quick answer! Actually for us the effect is very big and make these metrics unusable, for the time being I'll have to switch back to the old approach, meaning that I'll not be able to put them under github. Can you give me a rough estimation of why could this be tackled on your side? This would help me plan the work. Thanks!

Francesco, can you add the filter for your denominator to your numerator as well?

hey @helpful-application-7107, this would be extremely complex and difficult to read. To give you an idea, this is how the metric should look like: NUMERATOR (Those that were pay customers 1 month after the conversion date)

WITH conversions AS (
    SELECT
    account_uid,
    converted_at
FROM funnel_table
)
SELECT
c.account_uid,
mrr.day,
converted_at,
(mrr.day::TIMESTAMP + (c.converted_at::TIME)) AS timestamp
FROM conversions c
    INNER JOIN daily_customers_table mrr USING (account_uid)
WHERE mrr.is_customer = 1
--Here we add the custom filters which determine the conversion window, such as:
AND mrr.day = DATEADD(day, 32, converted_at::DATE)

DENOMINATOR: Those accounts that were already paid customers when they were first assigned to the experiment (it's the group of those eligible for the 1M retention for existing customers)

WITH exp AS (
    SELECT
      experiment_id,
      account_uid,
      MIN(timestamp) as assignment_timestamp
    FROM assignments_table
    WHERE timestamp BETWEEN '{{ startDate }}' AND '{{ endDate }}'
    GROUP BY 1,2
)

SELECT
  account_uid,
  funnel_table.signup_at,
  funnel_table.converted_at,
  experiment_id,
  assignment_timestamp AS timestamp
FROM exp
    LEFT JOIN funnel_table USING (account_uid)
--Here we add the custom filters which determine the conversion window, such as:
WHERE experiment_id = '{{ experimentId }}'
AND timestamp BETWEEN converted_at AND DATEADD('month', 1, converted_at)

The metric is one of the retention metrics that we have (we distinguish between retention of existing pay customers, retention of new accounts, and retention of free accounts). The idea would be to have just one fact table for all the numerators, and just one fact table for all the denominators, but currently we are limited because this doesn't work as a funnel metric. --- What currently happens is that the denominator works as expected and so is the numerator, with the problem that it doesn't filter out those that didn't meet the filters of the denominator. This is skewing our metrics, making them being greater than 1 in some cases (which doesn't make sense).

Hi Francesco, 1. Is there a reason your denominator query has the

experimentId

? Ideally, you want to set up your denominator metric query so it does not rely upon the

experimentId

. Your metric should be standalone sql code that applies to your user population. GrowthBook will then join the metric query to the experiment assignment query (which contains the

experimentId

) in the analysis. 2. Separate fact tables for numerators and denominators will result in more expensive queries than if numerators and denominators are in the same fact table. In general, a fact table should contain related entities. For a metric like retention, a fact table that has first conversion date and most recent conversion date in the same fact table may make sense. However, you know your architecture better than I do. Hope this helps, Luke