13 Replies Latest reply on Mar 1, 2018 12:05 PM by Keith Nyberg

    Identify Records where Acquisition Date ≠ Created Date

    Keith Nyberg

      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

        • Re: Identify Records where Acquisition Date ≠ Created Date
          JD Nelson

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

          • Re: Identify Records where Acquisition Date ≠ Created Date
            Dan Stevens.

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

              • Re: Identify Records where Acquisition Date ≠ Created Date
                Keith Nyberg

                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.

                  • Re: Identify Records where Acquisition Date ≠ Created Date
                    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.

                     

                      • Re: Identify Records where Acquisition Date ≠ Created Date
                        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...

                          • Re: Identify Records where Acquisition Date ≠ Created Date
                            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).

                            • Re: Identify Records where Acquisition Date ≠ Created Date
                              Keith Nyberg

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

                                • Re: Identify Records where Acquisition Date ≠ Created Date
                                  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.