Fix - Email opt-in date is empty/ not recorded

[Madhavi_T]

Hi All,

We need a fix, to capture the email opt-in date for those leads who have provided their opt-in consent in the past.

(Email opt-in is captured(TRUE) but Email opt-in date is EMPTY ).

Please suggest a solution to capture the date.

[Jesse_Richard]

Unfortunately there's no way to pull in date/timestamp from a data value changed activity directly into a Smart Campaign flow. So if you want to retroactively add the opt-in date, your best bet (though it can be time consuming depending on how long you were recording opt-ins without the opt-in date) would be to run a batch Smart Campaign with filters for when the data value changed, then update the date using the change data value flow step. For example...

Smart List

• Filter: Data Value Was Changed
  • Attribute: [Email Opt-in]
  • Date of Activity: [2018-11-21]
  • New Value: [True]
• Filter: [Email Opt-in Date] [is empty]

Flow

• Change Data Value
  • Attribute: [Email Opt-in Date]
  • Value: [2018-11-21]

Problem with this is that you'd have to edit, and re-run the Smart Campaign for every date that you were running the opt-in campaign without recording the date. If you were only running it for a few days or weeks, it's not too bad, but any longer is going to take significant time (and Marketo's data retention policy will restrict you on how far back you can go [360 days I believe]). In this case, you can widen the scope of the time frame, for example, look at everyone who opted-in in the past quarter, and set their opt-in date to the last day of the quarter; though this would be less accurate.

I'm not familiar with using the Bulk API, but I believe you can use it to pull the activity logs and extract the timestamp. If so, just pull all the records for leads who opted-in and have no opt-in date and then just format a list so it's email address and the timestamp, and re-upload into Marketo (with the timestamp mapped to the Email Opt-in Date field) so Marketo updates that field. That'd be a much faster option, though again, depending on the data retention policy and how long you've been capturing opt-ins, you may only be able to get a subset of all the leads.

[Julie_Colwell1]

I would agree with Jesse that you need to retroactively go back and run a smart campaign. You can try a bulk update and assign it by the last date of the month, i.e Email Opt-In = December 31, 2018.

Going forward make sure your smart campaign that automatically captures it using the {{system.date}} token.

[SanfordWhiteman]

(and Marketo's data retention policy will restrict you on how far back you can go [360 days I believe]).

It's 90 days for Data Value Changed.

So this exercise could be done with exact timestamp accuracy using the Bulk API, like you said, but only for actions in the past 90 days (and the clock is ticking right now!).

As an additional ray of hope, if the Opt-In was changed via Filled Out Form, those are kept for 25 months. But direct changes to the lead via Smart Campaign, Lead Detail UI, etc. would age out after 90d.

[Jesse_Richard]

Thank you for the clarification on data retention, I was confused because in my instance of Marketo it says 360 days, but then in a doc I found in the community it said 90 days or 25 months for data value changed.

[Denise_Greenb12]

Hey Jesse,

You don't have to rely on memory. You can actually see the 90 day restrictions right in any Smart List (including the Smart List in a Smart Campaign). For example:

Denise

[Jesse_Richard]

I'm aware, but for whatever reason, in my instance it says 360 days... hence the confusion. Ha ha. Then again, my instance is 10 years old, so perhaps we're grandfathered in to the old data retention policy. ¯\_(ツ)_/¯

[Denise_Greenb12]

You can - or at least used to - be able to get Marketo to retain that type of data for longer periods of time. I'm betting your company did that.

[Gunjan_Batra1]

Do you capture activity data in Hadoop or any other data warehouse?

Ideal solution is to extract date of activity when Email opt-in was set to TRUE for people that have date field blank. This should be an easy query in Hadoop. Once you have email address and date, you can do a mass list upload.

In case you do not download activity data on a local server then you will have to update the date retroactively via campaign and use one date for everyone.

Question: Did you send an explicit optin email to these people? If yes, then date of optin can be <Date of EM send + 2 Weeks>

[SanfordWhiteman]

Do you capture activity data in Hadoop or any other data warehouse?

Obviously not, c'mon...

In case you do not download activity data on a local server then you will have to update the date retroactively via campaign and use one date for everyone.

Not true if it's within 90 days or is tied to another event with 25-month retention.