1

There is an Individual Email Result, a custom object created from the Marketing Cloud Connect managed package between Marketing Cloud and the CRM. 

This individual email result object has some custom fields.

My goal is to create a new field named Engaged to track the engagement of these opens using the opened and date opened fields. I am aware that this report can be made using SQL in Marketing Cloud, but the Marketing team want to see this data in the CRM using the Individual Email Result.

What kind of formula can I create to get the results of 1Month, 2Months, 3Months, and so on?

Improve this question

1 Answer 1

Reset to default
0

The best way to go about it is to use an IF statement.

When you subtract two date values from each other, you get the difference in days. You can use that difference in an IF statement to output the specific text values you mentioned.

I tested a quick POC with some date fields:

IF(CustomDateValue__c-CreatedDate<30,"1 Month", IF(CustomDateValue__c-CreatedDate<60,"2 Months",IF(CustomDateValue__c-CreatedDate<90,"3 Months","Unknown")))

and it did the job.

Good luck!

Improve this answer
2
  • Thanks! If I can ask a follow-up question, can you confirm that the CreatedDate only works on new account creations and not if an account is updated?
    – Gitconnect
    Commented May 8 at 19:15
  • I just used the CreatedDate for my example formula. You are correct in that the value won't change when the record is updated. The LastModifiedDate field will update with the the newest value each time a record is updated.
    – Adriaan Van Niekerk
    Commented May 9 at 6:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .