Hey! Anyone here that has configured metrics that measure the time between two different events? For example time from add to cart to purchase? I know funnel metrics for fact tables is in the works so I guess something like that will come natively in the future, but are there any workarounds that I can use today? I've tried different approaches, but no luck as of yet. General time to metrics works, but doing that approach increases the variance significantly since it would incorporate the time before the first event of interest as well. Edit: It actually seems that i've also incorrectly defined my other "time to" metrics. They've simply just measured the total engagement time for users that has triggered the specified event. I guess the only way to go about it is to create the definitions in your fact table query.

I guess the only way to go about it is to create the definitions in your fact table query.

That is how we have done it. We put the logic in our data warehouse, though, so that they are persistent rather than recalculated every time the metric is refreshed in some experiment.

I agree with Scott that this is the best way to do it. We've definitely considered ways to make "time between event" metrics easy but it's not currently in the works. Funnel metrics, which are planned, wouldn't quite handle this case.

Thanks for the answers! @late-dentist-52023 Is the logic that you've setup something that you can share 😁 I'll go with defining it in the fact table for now and just limit it to few metrics, because the little I did added a lot to the query.

@helpful-glass-76730 the code itself is overly specific to our dbt codebase. The general concept is to have two tables: event_`a` (your starting event) event_`b` (your event that ends the timing) Have an

id

column you can join on (user, session, etc). Join

a

to

b

on

id

where

a.timestamp

<

b.timestamp

and

datediff('seconds', a.timestamp, b.timestamp) < xxxx

where

xxxx

is relevant to your business case, similar to a conversion window, you allow a certain amount of time to pass for the second event. You will also need to think about handling the case of users who never do action

b

as that is going to be null or infinite time, might be a second metric that is proportion of users who do

a -> b within xxxx

that you could also build. Since a user might be able to do

b

multiple times after

a

take the minimum difference between

a.timestamp

and

b.timestamp

that are in that join for each

a

event. If you need to further deduplicate (i.e. they could do action

b

multiple times quickly) you can also use a window function like row_number() to ensure you only get the first record. There are other ways to accomplish this only first record ordered by smallest time difference depending upon what data warehouse you are using etc. In the end that gives you one record for each

a

table entry that is the first time the user did

b

after

a

within some defined conversion window.

Major thanks @late-dentist-52023. That will help a lot!