Solved

Using Derived Properties to Calculate Status

  • 18 April 2023
  • 5 replies
  • 193 views
R
Rank
Badge

I’m am looking for some assistance using derived properties to calculate a new flag for Trialing Expired based on a string field and a date stamp field. 

We have a user property for membershipStatus with a value of “trialing” and date/time field for Trial Expiration Date and Extended Trial Expiration Date formatted like this 2023-04-08T18:36:48.113Z.  I want to leverage derived properties to determine if a user is Trialing Expired (true/false) based on the following: 

membershipStatus = “trialing” AND

(Trial Expiration Date OR Extended Trial Expiration Date <= TODAY())

I think my underlying issues are with comparing the date fields to TODAY().   I have attempted leaving our Trial Expiration Date Field as is, along with converting them into a unix timestamp with both DATE_TO_LONG and TIME_TO_LONG to compare to TODAY(), but still running into issues.

Thank you!

icon

Best answer by ryanM 10 May 2023, 23:10

View original

5 replies

R
Rank
Badge

Update: I was able to break it out into a few different derived properties to reference in my final flag.  First one looks at Trial Expiration Date and Extended Trial Expiration Date and returns the Extended Date if populated.  I then created a new derived property field to compare that TIME_TO_LONG date to Today() and return true (future), false (past).  I then used that field in a new derived property field to look at membershipStatus = “trialing” AND if the date was in the past to get to my final calculation.  So, this seems to work, but interested in seeing if it can be simplified.

R
Rank
Badge

Actually, I don’t think this works when trying to identify events that happened while the user was trialing-expired.  Any thoughts/suggestions are welcome!

Esther Trapadoux
Rank
Userlevel 5
Badge +8

@ryanM welcome to the community (and big apologies for the delay, we’re steadily catching up). I’ve opened a ticket for our team to look into your question/offer suggestions. In the meantime, does this article offer more help?

Community @ Amplitude
R
Rank
Badge

Thank you @Esther Trapadoux I had referenced that article as well but still stuck.  Thank you for opening the ticket, I have added more details about the use case there.

R
Rank
Badge

Wanted to post an update here for the final solution for others. 

I was able to use the Amplitude Server upload time event field and compare that to our Trial Expiration Date converted to UNIX using TIME_TO_LONG

Reply


Terms & ConditionsCookie settings