How to Count Number of Days Between Data Field Changes

Craig_Thompson1
Level 2

How to Count Number of Days Between Data Field Changes

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

4 REPLIES 4
Grégoire_Miche2
Level 10

Re: How to Count Number of Days Between Data Field Changes

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
Level 10

Re: How to Count Number of Days Between Data Field Changes

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
Level 10 - Champion Alumni

Re: How to Count Number of Days Between Data Field Changes

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
Level 2

Re: How to Count Number of Days Between Data Field Changes

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....