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
Best answer by jonathan
View original
+3
Hey
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!
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 :)
+3
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?
+2
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.
Hi
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
+2
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 :-).
Enabling a query like this:
+3
There’s a couple flaws with it:
now() or current_date function)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.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.