How to Count Number of Days Between Data Field Changes

[Craig_Thompson1]

We have a field that tracks people opting out of SMS messages I want to track the life span of a mobile number in our database,

i have the date a lead was created and i have the SMS opt out field changing from false to true.

Is it possible to Create a Field that would record the number of day that the field was False.

I was thinking Smart Campaign listening for data value change then populating a new field with Created date - Today

[Grégoire_Miche2]

HI Craig,

The first thing it to store the date the SMS Opt out field is set to true. You will need the field "SMS Opt Out Date" to be created (either from your CRM and mapped or directly in Marketo).

Then you will need a smart campaign to populate that field :

• trigger : data value changes, field "SMS Opt Out', new value = true
• flow : change data value, field "SMS Opt Out Date", value = {{system.date}}
• Setting : can go through the flow every time

The hard point is to compute the difference with today. This changes every day 🙂 and Marketo cannot make this computation. The best solution is to make that computation in your CRM.

-Greg

[Mike_Reynolds2]

You could use a scoring campaign to count the number of days. You'll need to create a new score field. Since you want to count the days from when the lead is created to when they opt out, use a trigger campaign with a Lead Is Created trigger. In the flow, add a flow step to add the lead to a static list.

Then create a different recurring batch campaign to run every day. Use the filter of Member of List pointing to this static list and a flow step to add +1 to the new score field each day.

Then, in the trigger campaign Greg mentions for when they opt out, add a flow step to remove the lead from the list.

Once done, you'll have an exact count of the number of days from when the lead was created to the day they opted out.

[Josh_Hill13]

I'd use a Date Opted In field and a Date Opted Out Field like Greg suggests. Yes, calculate this number in a formula field in the CRM or by exporting it out to excel.

While a daily batch may be ok, it's an additional system resource that may not be worth it.

This is an interesting idea and I'm curious what data you come up with. It's probably a function of the # of texts you send and their value.

[Craig_Thompson1]

This could also be done for Email Addresses and Unsubscribe or bounce to give an idea of life span of an email from various sources.

As in the life span of an email collect via Expo could be longer than A website...

I need actual hard numbers as opposed to someones gut feeling....