Determining time period between opt-in and opt-out

[Megan_Koelemay]

I'm attempting to do some analysis on our unsubscribe data by looking at segments of people (purchasers vs. prospects) and determining on an aggregate level how many days passed between their most recent opt-in, and when they unsubscribed.  I'm hoping to lump this data into a couple different buckets of lengths of time they were on our list before unsubscribing:

• 7 days
• Between 7 and 30 days
• Between 30 and 90 days
• Between 90 and 365 days
• Over 365 days

We hope to use this data to determine if there's some "frequency fatigue" at play - i.e., are we sending too many emails, and is this disproportionately impacting one segment over another.  

One issue I'm trying to work around is data retention limits in Marketo (e.g., some data is only keep for 90 days, others 25 months, etc.)

Another (even bigger) issue is trying to figure out how to report in bulk on this, instead of having to dive into hundreds of individual activity logs.  My first attempt is something along these lines, but it feels a little...iffy:

Any ideas?

Thank you!

[SanfordWhiteman · Accepted solution]

Pull the Activity Log (Get Lead Changes) via the API and do the calculation in Excel/SQL/etc. You can’t calculate the difference between 2 Date fields using the UI alone. (Yes, you can try to hard-code certain date ranges but it will eventually drive you mad.)

Also, as soon as you can, start stamping the time of interesting value changes to dedicated DateTime fields. Otherwise you’re not gonna get more than 90 days of data.

[Megan_Koelemay]

Thank you, @SanfordWhiteman!  

[Ruchi_Lapran1 · Accepted solution]

Hi Megan,

I worked on a similar use case and had to perform certain calculations outside of Marketo. I suggest having a stringent groundwork handy for stamping the important dates in Marketo so that you can bypass some aspects of retention policy. Also you may try the following and tailor according to your needs:

1. Extract the data (I used excel) needed for analysis, which includes the below. Marketo’s retention limits may affect your data export, you may need to use API integrations or periodic exports to gather historical data.

• MarketoID (unique identifier for each contact)
• Most Recent Opt-in Date (date when the contact opted in)
• Unsubscribe Date (date when the contact unsubscribed)
• Segment tagging (purchaser vs. prospect)

2. Calculate the days difference in excel sheet as below. This formula subtracts the Opt-In Date from the Unsubscribe Date, returning the number of days between them.

• Cell A2: Opt-In Date (e.g. 01-12-2023))
• Cell B2: Unsubscribe Date (e.g. 23 - 01-2024)
• Cell C2: Apply calculation formula "=B2 - A2"

3. For building time buckets try the below calculation in Cell D2:

=IF(C2 <= 7, "7 days",
IF(C2 <= 30, "Between 7 and 30 days",
IF(C2 <= 90, "Between 30 and 90 days",
IF(C2 <= 365, "Between 90 and 365 days",
"Over 365 days"))))

You may need some tweaking but this would bring you near analysis. You can also create pivot table or charts for visualizing the data distribution.

[Megan_Koelemay]

Thank you for the detailed breakdown of calculations, @Ruchi_Lapran1!