You have a robust multi-subscription model using Marketo Custom Objects: each Subscription+Person combo has robust metadata about when somebody subscribed/unsubscribed, why, and how.
The boss goes, “Can we get a scheduled report of people who subscribed to any newsletter in the past month?” You know just what to do:
Then they throw in “Can we compare that to the 2 prior months?”
You almost say, “Sure” but decide to play around a bit. Then the uh-oh hits you. First, the between operator won’t work because it requires hard-coded dates, and this is to be a dynamic Smart List:
Next, you try to combine in past before and in past (like you would with person fields):
But that’s got a fatal flaw: each filter may be looking at a different CO record, i.e. a different newsletter!
This Smart List fetches people who:
(a) have at least one Subscriber record with Subscribed At earlier than 1 month ago
AND
(b) have at least one Subscriber record with a Subscribed At in the past 3 months
This could include people who subscribed to a newsletter within the last month, as long as they subscribed to another newsletter more than a month ago. Filter 1 and Filter 2 aren’t looking at the same record, they’re looking at the whole CO list. Make sense?
Prepare for this
You can prepare your COs for this kind of query by creating another field, also a DateTime, that holds the exact same value:
Then you can use in past before and in past against the same CO record:
