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:
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!
Solved! Go to 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.
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.
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.
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.
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.
Thank you, @SanfordWhiteman!
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.
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.
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.
Thank you for the detailed breakdown of calculations, @Ruchi_Lapran1!