Identify Records where Acquisition Date ≠ Created Date

[Keith_Nyberg2]

Hey Community,

Anyone have any clever or easy ways to isolate records that have an acquisition date that doesn't match a creation date? I know I have some of these records in my database based on poor older practices and am working to reconcile.

I tried to create a smart list with the filter Acquisition Date ≠ Created At to isolate affected records, but this does not work as the {{lead.Created At}} token is invalid in the Acquisition date filter.

I know I can export my entire DB and audit the acquisition date relative to the creation date, but as my existing DB is nearly 700K people, this will take alot of time and my Macbook will probably crash trying to open the .xls.

Does anyone know of an easier way to isolate these records to correct inconsistencies in MKTO?

I know I can run my entire BD through a smart campaign to set Acquisition Date = Created At but again want to avoid touching all the records in my DB when my gut is only a few thousand are out of sync if any. (this would also majorly backlog my campaign queue)

Let me know what ya got and thanks in advance for reading/engaging in this community.

Sincerely,

Keith Nyberg

[Dan_Stevens_]

Keith, also be aware that you can't use tokens in smart list filters/triggers.

[Keith_Nyberg2]

Yeah... that is the major problem here... not sure there is a better solution than just dumping my DB,  isolating records that are not equal and then importing the Created At date into the Acquisition Date field.

[Dan_Stevens_]

For us, having different AD and CD are normal.  Unless we’re importing people into a program that is part of a “list acquisition“ program, we don't assign AP/AD during the import process.  Mainly because the leads themselves didn't engaged with anything to qualify them as members of a program.  Which is why we also always include a flow step - in all of our programs - to define the AP within the first program they actively engage with.

[JD_Nelson]

We do the same thing here, which I thought couldn't have been best practice, but glad to see I'm not the only one doing it -- which lends the question "what's causing the issue with the mismatch, Keith Nyberg​?" Just wondering from a 30k' perspective...

[Keith_Nyberg2]

JD Nelson​ we actually do the same thing as you both mentioned but simple answer to mismatch issue is legacy program infrastructure that allowed people to fall through the cracks and not be acquired by a program when they should have. After auditing and correcting the entry point issue, we also need to correct the acquisition program but when you do this, the date is set to {{system.dateTime}} which unless you are finding issues and correcting them the day of is incorrect.... Audit I just performed showed only 30K records that have an acquisition date that does not equal creation date (date only, not time as they are never equal).

We now know its a best practice to set Acquisition Date = Created At BEFORE setting the Acquisition Program but have made mistakes in the past when correcting data which resulted in the Acquisition Date field being appended incorrectly.

Dan Stevens​ - I like how you manage the list purchase process by not setting AP until they engage in another program. Question though, based on what I've learned over the years people always panic about not having an acquisition program, which in the list purchase scenario is typically a LP program. How do you give credit to the List Purchase as being the source of the lead if you aren't setting the acquisition to a program with List Purchase as the channel? What benefits or issues have you encountered by following your process as opposed to setting acquisition program to a list purchase channel program? My gut is that this could skew your records acquired by channel data when the record engages later on...

[Dan_Stevens_]

Actually with a dedicated "list purchase" channel, you would set the AP to this program (which was a separate program with a "list purchase" channel - not a LP program).  That's how we used to do it.  We did away with this channel, since it wasn't reflective of true attribution/engagement.  Now, when we import lists (since a very small % is a result of a list purchase - I actually want to get this down to 0%), we either import them directly within the program where they will be used; or in a central list in Database. 

As for giving credit, no credit should be given if the imported lead never engaged.  So not a problem for us.  When they do engage, they'll receive the AP name of the program where that took place.

[JD_Nelson]

When we used to do list purchases we did the same as you, Dan, with the exception that we have a 'lead source detail' field that we would populate 'list purchase' (today that's mainly the details of the channel, Facebook to the Social Lead Source, for example). We were generally purchasing lists for specific campaigns, so the AP defaulted to being the program we used it in -- but both interesting and curious about leaving it blank until they actually engage. What was that rate of engagement? Did you see a big chunk never engage (a la killing the channel)?

[Dan_Stevens_]

We sure did see a big chunk never engage - especially when some of our regions insisted that purchasing lists of highly segmented contacts were needed to execute a campaign.  As you and I both know, response rates will be far below average (when compared to those contacts that engage and establish a relationship on their own terms with your brand).  So this was dual-purpose - to also show that there was a very poor response/engagement rate when reporting on these specific audiences.  Not to mention the high unsubscribe rate and low deliverability rates.

[Keith_Nyberg2]

Really appreciate the extra insight and thoughts/comments JD Nelson and Dan Stevens! Making me rethink how we manage this function....

[Dan_Stevens_]

JD, it's definitely a best practice - especially for teams that will often import names into Marketo for programs like events (e.g, list of names to invite to an event).  We tell our team to never select the AP (step 3 of the list import process) unless this is an import to manually define program membership (e.g., attended event).  A month down the road, let's say some of these same names finally engage with our content - download a gated piece of content.  It's that content program that should be given credit for acquiring the lead - not the list import where no activity took place.  And it's those instances that the AP needs to be manually defined that we have here (AD is automatically populated when the AP is defined).

[JD_Nelson]

adding this to my running list of topics for the Champion panel at Summit -- should they choose it.

[JD_Nelson]

what if you run a batch to copy acquisition date to a new string field, then you can compare those two fields?

[Keith_Nyberg2]

That is a great idea but still requires me to run my entire DB through the campaign to isolate the records which is really not ideal. If I'm gonna run my whole DB through a campaign, might as well just run Set Acquisition Date = Created Date. Same campaign run time....