https://www.growthbook.io/ logo
Powered by
h

hallowed-lifeguard-32533

05/16/2023, 2:46 PM
I am looking to create some time bound metrics - let’s say Day 7 conversion for argument’s sake. Is there a way to exclude users from a calculation (both numerator and denominator) if the user, for example, registered fewer than 7 days ago? My first thought was to create a calculation that could be used as a denominator, which returned the 
user_id
and the registration timestamp, with a where clause that required the date diff between registration timestamp and current timestamp to be greater than 7 days. I need to return the registration timestamp so that the conversion window for the conversion metric works properly, but by definition the registration timestamp is before the user is bucketed in the experiment, so is not counted in the experiment calculation. Would very much appreciate help here - there are lots of metrics that we need to write in this way to align with our other reporting.
h

helpful-application-7107

05/16/2023, 3:08 PM
Hey Callum, I think maybe the best way to do this is to create a User Dimension which builds on a table/query that is unique on 
user_id
and has the 
registration_timestamp
for that user. In that table, you could do:
Copy code
SELECT
 user_id,
 date_diff(registration_timestamp, {{ endDate }}, "day") > 7 as value
FROM
 {...table...}
Then you could run your analyses split by this dimension when you require it, and see results combined and separated by this status. You would be creating a User Dimension with a SQL template variable This may get you lots of information but may not be the most performant solution (and you may want to add certain filters on the above table if that could help as well). Also, if you stop an experiment, users bucketed in the last 7 days of the experiment won't update their status since 
{{ endDate }}
will be the end date for stopped experiments.
Another alternative would be to do:
Copy code
SELECT
 user_id,
 date_diff(registration_timestamp, CURRENT_DATE(), "day") > 7 as value
FROM
 {...table...}
or the like, which when executed would work even after the experiment has stopped with the latest date, but now your results depend on when the query was run, even for stopped experiments.
There are other alternatives: creating a Segment with one of the above definitions, and then just setting that on your experiment. Then you wouldn't get dimension results, but the queries will run somewhat faster and your overall results would just be for that segment. creating a new Experiment Assignment Query that filters out users with something like the above logic.
Maybe there are other options as well but I'm not sure.
The problem with using an activation or denominator metric is that you don't have access to their other timestamps to compare to in order to preserve the conversion windows (as you suggested above).
h

hallowed-lifeguard-32533

05/16/2023, 3:38 PM
thanks for the response. i guess the challenge is that we need to be measuring different metrics with different denominator requirements within a single experiment. So for example: • D0, D7, and D30 conversion, which would all have their own respective time boundings • retention metrics, where we’d only include the user if their subscription had been up for renewal since bucketing • subscription upgrade metrics, where we’d only include users on a certain plan Unless i misunderstood, your suggestions are kind of applying these ‘denominators’ at an overall experiment level?
h

helpful-application-7107

05/16/2023, 4:01 PM
That was my suggestion, yes. I'm not sure which of these apply to your original question about registration dates, but all of these seem to be pretty different measurement issues.
• D0, D7, and D30 conversion, which would all have their own respective time boundings
If you can define what you mean by D0, D7 conversion, i can more precisely help you. This, naively, seems like it should be solved by conversion windows. If it isn't and the 0 and 7 are relative to some timestamp that is before the experiment, you need to define the metric query to explicitly return this status using your own tables that compute 1 and 0 for these conversion values.
• retention metrics, where we’d only include the user if their subscription had been up for renewal since bucketing
If this means that "only include user if sub up for renewal since bucketing" and if this is not affected by the experiment, then you could use an activation metric to just look at these users, or a denominator metric. This would be easily solved if you track "up for renewal" as an event and create a metric based on it.
• subscription upgrade metrics, where we’d only include users on a certain plan
Here, you need a dimension or a segment that looks at users from the pre-experiment period and pulls a unique, pre-experiment "plan" per user_id (again, there should only be one value per user, and it should precede the experiment exposure).
That's my best guess based on your use cases.
3 Views
Powered by