1 Reply Latest reply on Mar 29, 2018 5:01 PM by Tyler Theurer

    Dirty Data and GDPR Prep

    Ethan Warlick

      If your instance of Marketo is like mine, you have legacy data pulled from multiple platforms, sales computers, and in some cases, written in short hand on business cards and sheets of paper. This makes it that much more fun to prepare for a double opt-in campaign for EU citizens. A few weeks ago I was 100% sure that EU citizens were not properly tagged in our instance, but after a few manual processes (hours of excelling and whiskey to stay sane) we're getting closer and closer to knowing how exposed we are from a legacy data perspective.

       

      Here are a few processes that you can do to clean your data to prepare for an April/May campaign:

       

      • Inferred/Billing Geo: I found that we had a lot of 'Country' fields empty, but had some useful data in the 'Inferred Country' and 'Billing Country' fields. At first I was a bit skiddish to run smart campaigns to pull data into the 'Country' field, but after a bit of auditing the data seemed sound and we updated our empty Country fields.
      • Cities/States/Zips: Much like inferred/billing country, we were able to pull some insight from filled in 'city', 'state', and 'zip' fields. If you're targeting EU citizens, you can do a look up for these data points and run a smart list for "if fields are/contain" and update Country fields appropriately. If anyone finds it helpful, I can compile the list of cities/states/zips that we found to be in the EU and attach it here.
      • Country Codes: One thing that can be extremely helpful, from a geo perspective, is country codes in email domains. You can find many of the Country Codes here. Unfortunately, Marketo does not have an 'email address: ends with' lookup, so you have to get a little creative. If you pull your database (or subset) to CSV/Excel, you can break these down. To do so:
        • Copy/Clone the email address column, and place it at the end of your data set (you're going to push data to many columns)
        • Highlight the new column, and go to Data>>Text to Columns>>Delimited>>Other: @ >>Next>>Finish
        • You now have a 'Domain' column
        • Highlight the Domain column, and go to Data>>Text to Columns>>Delimited>>Other: . >>Next>Finish
        • You now have between 2 and ∞ columns with data (yay!)
        • Filter Row 1 (freeze for ease of scrolling) and search for where the domains end
        • Sort the rows as you go (from the farthest to the closest columns) and if a domain meets a requirement (ends with .de for Germany, ends with .fr for France, etc.) you can do a smart campaign with "Smart List=if email is:___ and Country is Empty // Flow=Country is:___"

       

      Some of this is intuitive, but I haven't found this flow anywhere in the Marketo docs so I thought I'd share.

       

      If you have any additions/alternative ways to work through dirty data, let us know!

       

      Thanks,
      Ethan

        • Re: Dirty Data and GDPR Prep
          Tyler Theurer

          Hi Ethan,

           

          This has been a topic of discussion in the MUG's in San Francisco, San Jose, and Austin recently, and they asked my exec. team to present on it (the presentation from the Austin MUG is recorded on BrightTalk if you want to watch it!).  Openprise can automate this whole process for you, and provide the controls you need for GDPR.  Plus all the geographical reference data you need is freely available for you without having to cultivate the data yourself.  If you would like me to send you a link to the recording or anything else I'd be happy to help out.

           

          Best,

          Tyler