Question

Filter event property (date) by relative date.

  • 18 September 2021
  • 3 replies
  • 35 views

Badge

Hello,

I’m trying to create user Cohorts for user’s who are Subscribers, Churned Subscribers, in a Free Trial, churched Free Trial users, ect.

Due to constraints from Apple’s subscription service, this can only be accomplished by analyzing the `expiration_at` property of these events. These events are sent from Apple to our subscription management platform (RevenueCat) via web hook. RevenueCat then sends these events to Amplitude via web hook.

 

For example, A Free Trial user would have to be inferred by the trial_started event whose expiration_at date is in the future. 

 

I can’t find a way to filter an event by a relative date, i.e. “trial_started where expiration_at in the next week). It looks like amplitude allows me to filer by expiration_at but only by the specified values.

 

I’ve attached a screenshot of how it’s down in MixPanel

 

 


3 replies

Badge

@brian.abad Grace Evans said you would be a good person to ask

Userlevel 2
Badge +2

Hi @ahoang,

I understand that you want to create cohorts/segments of users by looking at how their event property value is relative to the event. Specifically, you want to look at a time value. 

You can use the different operators that we have to create a condition like that:

E.g. “expiration at” greater or equal than “next week” (which, depending on how you are sending in the value for the property “expiration at” might need to be converted)

Userlevel 6
Badge +4

Hey @ahoang ,

From what I understand, I don’t think this specific use case is directly supported in Amplitude currently i.e. comparing a date property value to a relative and dynamic future date field as shown in the Mixpanel screenshot. 

As Ning suggested, you can use the different operators to compare to actual specified date values.

Not quite sure about this approach, but you could try playing around the derived properties feature to see if you can build a formula and extract a derived property to label a future expiration date case.

Hope this helps!

Reply