Question

Account Age calculation

  • 12 April 2022
  • 7 replies
  • 85 views

Userlevel 2
Badge +4

Hi everyone,

I'm trying to compute our user accounts age based on the user property createdAt. Following a simple MY SQL coding logic I intend to get the TIMESTAMPDIFF(year, createdAt, now()) to create a new property.

Did someone faced this issue before? We have Heap connected to our Salesforce account, however I think if we can compute the account's age directly in Heap it will be easier.

Best regards,

 

Bernardo Madeira


7 replies

Userlevel 4
Badge +3

Hey @bernardo.madeira — this is a great question! Unfortunately, Heap doesn’t yet support creating calculated/derived properties like this. One day!

You’ll need to calculate it outside of Heap and then send it in via the Add User Properties API or an integration like SFDC. If you’re a Looker shop, you could calculate it there and push into Heap using Looker Actions (or use a reverse ETL tool like Hightouch or Census if the data is already modeled in a warehouse).

Hope this helps! 

Userlevel 2
Badge +4

Hi Jonathan!

Thank you for your prompt reply! We have some Salesforce Account properties being used such as Created Date. However we don't have the date difference between the created date and the current_date() timestamp. Do you know if we will have this capability in the future?

 

Thank you :)

Userlevel 4
Badge +3

@bernardo.madeira Nothing currently on the roadmap, but this will get on Product’s radar, so thanks for sharing the request!

It should be relatively straightforward to calculate the age between Created Date and current_date() in Salesforce. Something like datediff(current_date(), createdDate) on the Contact and then sync that to Heap via the integration?

Userlevel 2
Badge +1

@bernardo.madeira I’m curious about your use case, if you’re willing to share a bit more. What types of analyses will you do using this?

 

For example, as a CS leader, I use calculations like this to understand variance to a milestone (e.g. “exit onboarding” should have happened by X days, but we’re currently at Y days, so flag as a risk).

 

Having some additional ideas on use cases from you and the community would be great to include when considering with our Product team.

Userlevel 2
Badge +4

Hi @Lane ,

I will need to create some cohorts based on our clients age. So that we can compare the behaviour in the usage of specific features

Userlevel 2
Badge +1

For this, one (not totally ideal) thing that you could do is just bucket them by Start date using a defined property such as “2021-06” (2021 June) and then use that defined property in your analyses. This would be more of a short-term workaround since you’ll need to manually maintain it.

For the record, agree with Jonathan that using Looker Actions or SFDC would be much better, this is really just a quick suggestion in case that’s not possible for you and you need an intermediate (but not durable) workaround :-).

Define the Date Buckets conditionally using a Date user property

 

Enabling a query like this:

Group by Defined Date Buckets

 

Userlevel 4
Badge +3

@bernardo.madeira — I’m going to shamelessly steal my colleague’s solution that he shared with you in a support ticket—I waited a month, it seems fair game ;-)

There’s a couple flaws with it:

  1. needing to maintain the constant (we don’t have a now() or current_date function)
  2. because it’s not actually subtracting dates as a data type, 2022-01-01 minus a createdAt value of 2021-12-31T00:00:00 would return 1 and not 0, as in in their first year, as you probably would want. 

But without doing the work to bring in a more precise number, it’s not a bad solution.

I’ve made a product request for a now() or current_date function and also to be able to treat date-like strings as date data types, both of which I think we would need to make this type of calculation accurate. 

Reply