hallowed-lifeguard-32533
05/16/2023, 2:46 PMuser_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.helpful-application-7107
05/16/2023, 3:08 PMuser_id
and has the registration_timestamp
for that user. In that table, you could do:
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.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.hallowed-lifeguard-32533
05/16/2023, 3:38 PMhelpful-application-7107
05/16/2023, 4:01 PM• D0, D7, and D30 conversion, which would all have their own respective time boundingsIf 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 bucketingIf 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 planHere, 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).