list management

| brunocm, on December 10th, 2006

We recently signed on as beta clients for a customer targeting tool, a system that uses advanced analytics to segment the people who interact with your site for target email marketing campaigns.

A company could use this predictive engine to find people who would be likely candidates for an email campaign defined by merchandise categories, or a company could go in reverse and determine which merchandise they should push in an email campaign to a particular set of email accounts.

His system does some very interesting and advanced analytics  and should also become an interesting source of data regarding how our site visitors are interacting with the merchandise.

In order to baseline his system to our current targeted email list generation system we ran a series of tests, mano a mano. Each week, we would decide the purpose of the test, GS would split our opt-in email list statistically based on RF groups (for ex, we would want both lists to have the same number of 0-6 month 3+’s, our most biggest and most profitable group), which I would then have to Q/A for equal RF profiles. Depending on the test, GS would later send their list(s) of target candidate id’s to us (where we would then have decode to an email address) and would be uploaded to our email distribution system. Sometimes there were as many as 3 category/brands tested, so both sides would have 3 lists.

My most important (and most interesting) role in all of this would be to manage the targeted email list generation on our end, based on my methods that had been used for over a year. I didn’t have the foresight to develop a PL/SQL based approach before week 1, and I struggled the first week to get the list out in the time provided with the appropriate Q/A. You see, until these test, my targeted lists were relatively simple since they would support the merchandise mix being touted in the next days email campaign. So the approach I used in week 4, when we had 3 lists based on 3 different merchandise mixes, was to create 3 tables contained the 3 populations. Very simple. Now you’re going to have people in multiple lists. Based on a heirarchy provided by the marketing dept, I would filter the 3 lists by prioritizing them – so if I gave List 1 priority over the other 2, everybody on that list would receive Email 1. I would have to suppress List 1 members from List 2 and List 3, and assuming List 2 would get priority over List 3, I would have to suppress List 1 and List 2 members from List 3 to provide the final target lists to the marketing department. Can you say Null outer join galore? Not to mention the typing,

Finally, human error played into week 4 being launched with lots of emails not being sent as our email distribution system doesn’t send dupes. This would have been the most interesting and relevant test to date. Remember how I mentioned before how GS would split the email universe into 2 equal groups? We would upload both lists into our db (for my Q/A – and we would pick which of the 2 we wanted). Well we did this process twice in week 4 (so both sides could run through the motions, and to gain an additional day or 2 of data for algorithms to crunch on…) creating 2 completely sets of ‘half-universes’, and dba error (I generally rely on the dbas to upload large lists into a db table for further manipulation), and oversight on my part, led to the first version of the lists to pass for the actual lists. So essentially there was a lot of overlap in our lists with the GS lists.

Again, ultimately, I should have caught this (and how easy it would have been!). So what did i do? I explained what happened to the marketing department,
accepted responsibility, and made several suggestions as to how it would go down going forward…

On my end, I had a few tests left and needed a way to create multiple distinct lists in an efficient manner. Q/A’ing the final lists against multiple table null outer joins (oh the drama!) could be eliminated if I worked with one master list, and I could use PL/SQL to update this master list based on list criteria. I could then pull a subset of the list to actually generate the email lists for the mktg dept. Each candidate id would be in the master list once and only once, and would be set up to join to our co.s half-universe list (still being generated by GS) to generate my target audience. The overall process went from over 4 hours to about an hour.

I would then run through logic using various PL/SQL statements using the master list as the cursor to assign a member to one list….I used loop counters to ‘fill’ each sublist, as each needed to be a different size. I also had to accomodate for the fact that our lists might not have enough members based on a given set of events around the merchandise categories. I created another PL/SQL counter to run through the master list filling in available members meeting other criteria as list ‘filler’.

Then to create the final output list of email addresses, I separately selected the members assigned to each list and downloaded the data into a text file.

I hope this was interesting to someone, pls let me know your thoughts.