Hack32.Best Practices for Data Integration

Hack 32. Best Practices for Data Integration

Many companies, as they get more experience with web measurement, seek to integrate external data in an attempt to create a unified marketing interface.

Many web analytics reports are highly actionable, but usually the hidden power in web site measurement is the data itself. When combined with other data sources, web data can be used to analyze merchandising promotion gross margin contribution, campaign ROI, and how certain demographics behave on your web site. Web measurement data, when stored in a visitor-centric form, can be used to analyze the behavior of your survey respondents, target your email marketing campaigns, and even fuel your multichannel data warehouse.

2.21.1. Examples of Common Data Integrations

A number of common data integration projects have emerged over the past few years and are worth reviewing to develop an understanding of the value of combining disparate data sources.

2.21.1.1 Integrate cost data to calculate gross margin contribution.

Many companies tie product cost data to their web measurement merchandising report to analyze product gross margin contribution driven by product category browsing, product real estate placement, on-site product search, and product site tool (such as "magnify") usage. The potential of this data is obvious. Merchandisers can, at a glance, see how their initiatives are driving bottom-line profit instead of gross sales. With this simple data import, all merchandising reports can be updated to show gross margin contribution in addition to gross sales.

2.21.1.2 Integrate marketing cost data to determine real campaign ROI.

You may want to integrate campaign cost data into your web measurement marketing reports so you can more accurately determine the true return on investment for your campaigns. By tying campaign cost data and asking your vendor to customize their marketing reports to use that data, you will be able to analyze campaign ROI in addition to the typical metric of gross sales. With this combined view, you won't have to log into each campaign vendor's reports to see how much profit you are generating from each campaign investment. You will have a single campaign measurement source to report from and know that you are using the same measurement methodology to analyze the performance of all your online campaign sources.

2.21.1.3 Integrate customer registration data to drill into demographics.

You may want to tie your customer registration data to your web measurement reports. If you collect household income, for example, and your vendor supports customer segmentation based on that type of data, you can analyze the behavior of customer groups by household income [Hack #77]. Imagine the power of analyzing how different customer demographics path through various parts of your site, what merchandise they abandon and purchase, and how they respond to email campaigns.

2.21.1.4 Integrate customer satisfaction data.

Customer satisfaction is one aspect of data that most web measurement applications don't measure well. Because of the complexity of doing so, most companies use an outside vendor to collect satisfaction data; however, this data can be tremendously important to your understanding of your visitors and customers. Combining your web data with data collected by an outside vendorsay, BizRate or Foresee Resultscan add valuable missing elements to your analysis (Figure 2-21).

Figure 2-21. Combining data using BizRate

2.21.1.5 Integrate data from targeted email campaigns.

Another example is using web measurement data to drive targeted email marketing campaigns. With most web measurement solutions, you know what your product abandonment rate is. You also know how many customers are looking at products and not buying. Those measurements alone are helpful in changing your merchandise promotion and placement. But imagine the power of using that data to find the customers who are abandoned and target them with a special promotion. Many web measurement vendors in the last year have partnered with email delivery vendors to fuel their targeting engines with customer browsing and abandonment behavior.

2.21.2. Taking Action to Integrate Non-Web Data

The generalized steps to take to integrate non-web data are as follows:

• Identify the problem you'd like to solve.

• Identify the sources of web and non-web data that will be required.

• Determine how you're going to tie the data sources together.

• Integrate the data.

• Generate reports and take action.

Obviously, none of these steps are trivial, and thus should be explored in greater depth.

2.21.2.1 Identify the problem you'd like to solve.

Data integration is typically complex and very expensive. Consider the examples provided above; each is trying to create a more complete understanding of a common problem. Whether you're trying to determine "true" return on investment, better understand the customer, or improve your ability to drive loyalty, you're trying to solve a very concrete problem. Before you try and integrate data make sure you truly understand both the problem and how the combined data set will help you solve it.

2.21.2.2 Identify the sources of web and non-web data that will be required.

Once you're sure you understand the problem you want to solve, you then need to determine which data you'll need for the job. Many companies make the mistake of trying to integrate all available data into a monster data warehouse, assuming that if all the data is in one place, any question can be asked and any problem solved. Unfortunately, this is never the case, and massive data combination projects nearly always fail. Instead, identify the minimum number of data sources you need to answer the question and integrate those; you can always go back and add appropriate data later as warranted.

2.21.2.3 Determine how you're going to tie the data sources together.

Most data integration projects fail because of the difficulty tying multiple sources together in meaningful ways. In each of the cases listed above, there needs to be a unique identifier in each data set that relates the data together. Whether that ID is a product SKU, a marketing campaign ID, or a unique user identifier, your major challenge is determining how to associate the IDs in one data set with the IDs in another. It may sound trivial, but it's not, especially when you stop and consider that many data sources are incomplete or polluted (spelling errors, use of upper- and lowercase, etc.).

2.21.2.4 Integrate the data.

Your first temptation may be to try and bring the data together in your web measurement application. Remember, however, that you can also use your company's existing analysis tools to analyze site activity data combined with additional in-house data. Web measurement data, if structured in an exportable format, can be analyzed in Microsoft Excel, Microsoft Access, Business Objects, Cognos, NCR Teradata, MicroStrategy, Oracle, and any number of analytical tools. It is important that you ask your web measurement vendor what export formats they support and what the exported data includes.

2.21.2.5 Generate reports and take action.

If you took the time to form a good question in step one, taking action based on the data should not be a problem.

Once you get the data combined and you're taking action, you need to remember to maintain the combined data and forge a strategy for incrementally updating or cleaning the data from time to time. Again, one of the reasons many projects like these fail is the sheer size of the data sets involved. As long as you're realistic in your scope and are trying to answer well defined questions, you're likely to be successful.

Brett Hurt and Eric T. Peterson