Skip to main content
Craig_Thompson1
Craig_Thompson1
Level 2
October 21, 2015
Question

How to Count Number of Days Between Data Field Changes

  • October 21, 2015
  • 1 reply
  • 2557 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

1 reply

Grégoire_Miche2
Grégoire_Miche2
Level 10
October 21, 2015

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
Mike_Reynolds2
Level 10
October 21, 2015

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
    Josh_Hill13
    Level 10
    October 21, 2015

    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.

    Terms & ConditionsAccessibility statement

    Loading footer...