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

[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....

[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...

[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.

[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...