Archive for June, 2007

9 things to keep in mind when integrating external data feeds

Monday, June 25th, 2007

A key task in my new role is to deal with the ProActive Marketing data supply which has been in place for some time (see my previous post “Sourcing external marketing data” for more on ProActive). The intention is that this data is used in all prospect marketing activity, so it’s important to make it as readily available as possible at the point of campaign execution. Just as I’ve experienced previously though, securing the external data source is just the first step; integrating into operational marketing systems for campaign execution can be quite challenging. This is something of a weakness on the part of ProActive actually (though I’m not aware of anyone else doing it any better), as it makes their very valuable data quite hard to exploit to the maximum extent.

The task here is made more complicated, with very specific requirements for certain field coding and formatting. (This is fair enough in some respects, as it maintains data quality and good database management, but makes life harder.) Previous attempts had resulted in a very manually intensive and error prone process, taking days to undertake – not ideal for a monthly process. I’ve been able to streamline this into a semi-automated process using MS Access, resulting in a set of files that can be uploaded to the Onyx SFA/marketing database. Although many of the tasks could be performed by our database admin when he receives the files, I’m working on the basis that the less work he has to do, the faster it will get loaded and the more popular I’ll be! Here are 9 things to keep in mind when putting such a process together:

  1. What is the nature of the data supply – incremental update (i.e. just new data and changes to existing records) or refresh (a complete set of new data)? An incremental supply is preferable, as this reduces file sizes and import processing.
  2. Check for general data quality. Don’t rely on or expect your supplier to provide perfect data, this is unfortunately rarely the case. Ensure key fields are populated and check for invalid characters and values.
  3. How is the data transferred from the supplier? Bear in mind that sending a simple Excel or text file via standard email is not compliant with privacy legislation, since this method is not secure. At the very least ensure the file is encrypted (Winzip has built-in encryption capabilities) and even better use secure ftp or a web hosting solution, such as iWeb.
  4. If you are receiving a data refresh, you will need to establish records that have changed so they can be updated in your database (updating the entire dataset is not likely to be efficient, unless its very small). Whether you receive a refresh or incremental update, once amended data is identified you will need to decide whether to simply overwrite the data you’re holding (are there likely to have been any other changes?) or discard incoming data that has already been changed.
  5. Similarly to handling updates, you must process removes or deletes, Again, the feed you receive will determine how you identify these, but this is crucial to ensure your data is up to date and privacy compliant. Your supplier should be advising you of any individuals who have opted-out subsequently to having been supplied originally, and it’s crucial that you observe these. You may be in breach of your supplier contract, as well as the law, if you do not.
  6. Depending on the sophistication of your supply, you may be receiving updates where individuals have moved between organisations. Make sure this is reflected in your database, by moving the contact record from the previous organisation record to the new one if you can. If this is functionally not possible, at least ensure that you do not continue contacting the individual at their old organisation (i.e. treat them as having left).
  7. What are the pick-list coding and field formatting requirements? It’s likely that taxonomies such as industry and job role in the data you’re receiving are not the same as those you hold, so they will need to be converted if possible. (Many suppliers will use SIC for industry, which might match what you’re using.) Adding your supplier’s industry categories directly to your database will make it significantly harder to query and profile your data. Bear in mind any field formatting requirements too, particularly phone numbers, ranges and currencies.
  8. What is the file type and layout required by the system into which this data is to be imported? This may be a text file with specific field headings or field order – Excel files in particularly are not always acceptable. Check also what the field length limits are to avoid data being truncated when it’s imported. You will need to map your supplier’s file to the fields of your database. Take care in particular with address fields; you might receive four address lines but only have two in your database for instance.
  9. How will the data be merge/appended to your database? There may not be much you can do about it, but if you at least understand the process so you know what to expect.

Hopefully once you’ve thought through these steps you should find integrating your external data a smooth process and that campaign execution down the line is much easier. Rather than having to worry about getting the data into your database, you can focus on optimising your selection queries for the perfectly targeted marketing campaign!

Data quality – a vision

Thursday, June 21st, 2007

Here’s my vision for our data quality solution, for your inspiration!

  • Data quality platform
    • Validate and standardise online data capture (inc address look-ups)
    • Monitor marketing data quality and highlight issues for action
    • Process all data loads (de-dupe, merge, append)
  • Data enhancement
    • Individual and organisation enhancements including role, industry, IT usage
    • Campaign execution
    • High degree of data and process “readiness” for selection, segmentation, tracking and reporting
    • “Outcome” feedback wherever possible (esp. email)
  • Lead management
    • Scoring and pass-over threshold
    • Maintenance of a single lead per contact through identification and merging of subsequent responses
  • Privacy
    • Adherence to compliance and best practice

I’ll outline how these translate into a set of requirements for a data quality solution soon.

Data health check

Tuesday, June 12th, 2007

In getting to grips with a new database, a good first step is to have a complimentary data health check undertaken by one of the several suppliers who offer such a service. Obviously they’re designed as a hook to draw you into their services, so try and choose someone you think you’ll use in the future. Having worked with Global Database Management on some other initiatives recently, I supplied a dataset to them to put through their process. As their names suggests, international data is a particular strong point for them, including addressing and personal names, so I was keen to see results on the database for which I’m newly responsible.

Consisting of a well presented PDF document, the quality assessment covered address, naming, country coverage and duplicates, with statistics and commentary on various aspects of the database. The results were much as I expected, some of the validity checks in particular making for amusing reading, with entries such as “***update address ***”, “not existing individuals” and “hugh jarse”! (If you have a few minutes and want to bring a smile to your face, try looking for cartoon character names in your database, there are bound to be a few!) Their quantification though will be invaluable in terms of determining how to approach the issues and in particular constructing a business plan. I’ll return to this as work progresses.

Marketing operations endorsed

Thursday, June 7th, 2007

Writing in the April-June issue of the IDM’s house publication Journal of Direct, Data and Digital Marketing Practice, Peter Simpson, veteran founder of First Direct Bank, wrote approvingly of the role of marketing operations. Well, ok, not in so many words, but his “four Ps of new marketing” include process, which “might be the gritty stuff of marketing” (true!). “In the new world,” Peter goes on to say, “it is the hard edge by which the best marketers can achieve the best results.” Couldn’t put it better myself!