Build a Customer Data Platform using Excel for MailChimp Lists

“If you’ve got a manual email marketing scenario that requires a creative technical approach, this narrative just might be the catalyst you were searching for.”

There are two paths leading to a well-thought-out email marketing strategy; one automated, that reduces human intervention to a minimum; and two manual, which involves considerable human effort.  Utilizing an automated email marketing software solution that collates, cleanses and organizes your customer data (profile & sales) is ideal.  Managing a manual process in the CASL era is a “Nightmare” and requires consolidating customer profile data from multiple distribution channel sources to produce coherent “MailChimp Subscriber Lists”.  Producing “Lists” in this manner, for email campaign scheduling and delivery, is both laborious and perplexing; therefore inefficient until an automated solution can be introduced.

OK, so before I go any further the post title is somewhat misleading.  The definition of a Customer Data Platform (CDP) is a marketer-based management system, not an elaborate function rich Excel set of Worksheets.  Both produce persistent, unified customer databases accessible to other systems, but what I’m referring to does not.  Sorry!  While I’m still pulling data from multiple sources, combining and cleaning it to create a single customer profile, this particular structured data is then manually imported into MailChimp, having already been segmented & personalized, unlike a true CDP that provides this in real-time.

Email Marketing is a two-prong approach, both creative and technical.  Initially, the primary focus will be the technical side, ensuring the foundation of your strategy is precisely accurate, supporting the campaign’s creative/messaging.  To get started you’ll need to validate and confirm all physical (i.e. paper sign-up forms) and digital (i.e. online submission forms) properties to produce a unified “List”.  Having done this, you can begin developing an email marketing strategy that adheres to all aspects of CASL compliance and policy with confidence.

This is where the real work begins because the initial “Contact Build” is frictionless; meaning you are only combining and filtering customer data to produce your initial “List”.  Yes, I’m making this seem rather simple and it is straightforward compared to the ongoing management of your customer data profiles, be it month-to-month or campaign-to-campaign.

Imagine this, you’ve completed your initial contact build and are confident the customer data compiled is accurate and trustworthy.  You launch your first email campaign successfully, that’s awesome.  The following week you decide to schedule another email campaign for delivery and during that time frame you’ve acquired new customers and some of your existing customers have made subsequent purchases.  How are you going to isolate the “new customer” acquisitions from “existing customer” profile data updates and affect these modifications within your ongoing “Subscriber List” to adhere to “CASL Implied Consent” (Inquiry – 6 month & Transactional – 24 months) valid expiry periods?  I thought you were never going to ask!

Here is a high-level overview on how to manually modify and update your MailChimp Subscriber Lists using Excel:

Using Excel and a series of different functions across multiple worksheets I was able to merge and filter customer profile data from two different sales reports (Crystal Reports) by using VLOOKUP to compare the “Account Number” and corresponding “Last Transaction Date”.  Then through a series of additional steps; filtering email addresses, removing duplicate email addresses and filtering countries to produce a “Final Result” & “Final Result Date Based” worksheet which were compared to the previous month’s or the last Contact Build “Final Result” worksheet.  Finally, these two “Final Result” worksheets are filtered once again to produce the “Contact Additions This Month” & “Contacts Modified Date Based” worksheets to be optimized and converted to CSV format for importation into MailChimp – convoluted to say the least!

Let’s dissect our two scenarios below where I’ll explain the differences in reaching the results, hang on…

Note: Before we get going here I should mention that the procedure I developed is unique, meaning unique to the situation I was presented with.

New Customers; results are produced from the “Contact Additions This Month” worksheet by filtering the “Greater Than Dates” comparing “Last Transaction Date” columns and where email addresses return a false error in VLOOKUP meaning the email address is new, hence a “New Customer” to be added.

Existing Customers; results are produced from the “Contacts Modified Date Based” worksheet by filtering the “Greater Than Dates” comparing “Last Transaction Date” columns and where email addresses return all email address except where email addresses return a false error in VLOOKUP meaning the email address already exists (i.e. New Customer result), hence an “Existing Customer” to be modified.

This is a step-by-step process with many moving parts, some of which were omitted here to lessen confusion while staying on point.  Remember to double check your results as you move from worksheet to worksheet, it’s very easy to make a mistake that could cause you some real CASL grief.  If you’ve got a manual email marketing scenario that requires a creative technical approach, this narrative just might be the catalyst you were searching for.  If you really want to be great, try to automate (or get me to do it for you, NOT!)

David


David Moores @ NYC

At moorescode, our objectives are to build “first in class” digital experiences that focus on perfecting the customers’ journey.  Our strengths lie in digital analytics, metric components and the implementation of data tracking methods for mining interpretation.  These mechanisms provide the necessary insight to ensure your company’s branding message maintains a unified voice across all touch-points.  With our advanced knowledge of web server deployment, database management systems, and server-side scripting languages we’ve earned a reputation as experts in the web technology sector.

Leave a Reply