By Richard Boire
Partner, Boire Filler Group

August 26, 2009

As has been discussed in many articles and white papers, understanding what you want to measure within a given business initiative can be the most significant hurdle to effective ROI measurement.

How often do we hear of situations where measurement reports are produced but with the common marketing refrain: ‘It’s not what I asked for’? Resolving or mitigating this type of situation requires an approach that looks at addressing the needs of all stakeholders involved.

Presuming that stakeholder needs and appropriate measurement objectives have been identified, the next question is: how we will do it? This question requires us to look at the data and determine if the identified measurement objectives can be met within the existing data environment.

This article more closely examines understanding data within the context of ROI measurement.

Defining your Metrics

Once we have understood the measurement objectives, we need to understand the specific metrics that are being evaluated. These are often referred to as Key Business Measures (KBM).

Retention

Retention is a key metric commonly tracked by marketers. Unfortunately, the ability to define retention is not necessarily a straightforward task. It is rare that cancellation or “non-retention” can simply be defined by an event such as someone canceling their membership, or not renewing their subscription. In most cases, the notion of defining retention involves the capability to look at customer inactivity over a period of time.

Within the grocery industry, would you consider an individual who does not make a purchase for a week a non-retained or cancelled customer? Intuitively the answer is “no”, as many people only purchase groceries on a weekly basis. But often the consideration is not intuitive. For example, when should a Goodyear tire customer considered a lapsed customer? Obviously, the same timeframe or measurement period used to define cancellation within the grocery business is not going to be applicable to the tire business. Even between players within the same industry – credit cards for example – the debate ensues as to which time period should be used in defining a cancelled customer: 30 days inactive, 60 days inactive or 90 days inactive?

The issue of defining the appropriate retention time period will differ by industry, but can be determined analytically. Conceptually, the analysis attempts to determine the precise time interval where the retention rate becomes more or less stabilized. Here is an example:

In this example, we have four different time intervals that we’d like to examine. According to the date, we have accumulated results for each examined time period, at four different points in time. The objective of this exercise is to determine the appropriate time interval that produces consistent, or somewhat stable, results across the four different points in time (periods one through four).

Based on the notion of stability as a retention time period indicator, the results in this example indicate that the six month time interval yields the most stability, thereby implying the retention time interval should be six months.

Response

Another common characteristic that is measured in most campaigns is response.

Response can be identified various ways including receipt of a business reply envelope (BRE); receipt of a telephone call; purchase of an item (from an outbound telephone call, for example); or clicks to a specific URL or page views.

Similar to the challenges discussed as it relates to measuring retention, the ability to identify actual response may not be as straightforward as portrayed by this list. Analysts generally attempt to use a proxy for response, such as the occurrence of some event within a specific period of time.

A good example of this might be a cross-sell type of promotion such as a travel company marketing winter travel destinations to the U.S Southeast to all its travel customers. In this example, imagine the campaign occurs in January 2010 and our corresponding proxy for response is the occurrence of spending on any destination in Florida, Georgia, and South Carolina within January or February 2010. Of course, one could argue that defining response in this fashion is not entirely exact. There would be customers who would have initiated that activity regardless of receipt of the promotion. But any attempt to quantify that fact is highly suspect. Hence, this is why we refer to it as a proxy.

Other Metrics

Beyond retention and response, there are other key metrics that may need to be evaluated within a given measurement exercise. Examples include spend and cross-product spend.

But the key to defining metrics is first understanding those metrics that can be determined explicitly from a specific event (such as a response to a BRE or non-renewal of a subscription) versus those metrics which are implicitly derived (such as retention defined as no spending in 3 months).

Dimensions vs. Metrics

Once KBMs have been created, the next step of any measurement process is consideration of the goals for viewing the information. In other words, how should the data be sliced or which of various dimensions of a particular metric should be considered? For example, one may want to measure the impact of a retention campaign but look at the impact of tenure and region of country on retention. In this case, retention represents the metric or KBM, while tenure and region of country represent dimensions. Reports can then be designed based on desired metrics and dimensions.

At this step of the process, the user will understand what information is critical versus information that is important. Information deemed to be critical would be depicted in the form of a standard report which would be produced at a designated time interval. This kind of report would be produced automatically without any user manipulation of data. Information deemed to be important is readily available but needs to be manipulated by the user in order to be effectively presented in a report. This important information – often referred to as “adhoc information” – is stored within a pivot table. It is this pivot table information that is manipulated by the business user into appropriate adhoc reports.

This kind of process – identifying metrics and dimensions – should be familiar to those who are well-versed in the area of building data cubes. Companies such as Cognos and Business Objects have built huge organizations around the technology of creating business intelligence data cubes. The objective of these organizations and their technology is to empower end users by increasing their ability to generate analytical reports themselves.

Conducting a Data Audit on the Source Data

Once the data elements required in measurement reports have been identified, the next step is to map the data elements to the source data within the IT environment. When the appropriate source data is extracted, a data audit can be conducted. The extent or detail of a data audit depends on whether or not you are at the stage of developing the reports or simply reproducing these reports on an ongoing basis.

If it is the first time creating a report, the source data and source files are likely to be unknown to the analysts. When this is the case, the initial development of reports requires a more exhaustive data audit in order to explore each field or variable from all the source data. These reports would provide insights on the extent of missing values; the range of values; the average, minimum value and maximum value; and the number of unique values.

That which is learned from the data audit will indicate the “usefulness” of a given variable within the overall set of measurement reports. For example, suppose age is a desired field within a measurement template, yet the data audit uncovers that the age field contains 75% missing values. The usefulness of age in any reporting function would be severely mitigated by the missing values. That being the case, the analyst could create a binary variable from age based on whether the value is missing or not missing. Analysts have often found that this type of information can yield valuable learning in any measurement or analytical type exercise.

Once reports are being produced on a regular or ongoing basis, there is no need to continue to do the extensive data audit process every time the measurement report is re-run. However, some regular basic level checks and high level reviews of the data should be established. Diagnostics pertaining to the number of records, as well as averages or means on the key business measurement variables should be produced each time a new set of measurement reports is developed.

Creation of the Analytical File and Pivot Table

With the data audit process completed, the next part of the measurement process involves the creation of the analytical file. This involves extensive manipulation of the data whereby files are merged and data elements are summarized at the customer level. To create the pivot tables, further summarization is done based on how one wants to view the data against a given KBM or set of KBM’s. The complexity of this summarization will increase as the number of dimensions and the range of outcomes within a given dimension increase.

For example, if the goal is to assess the average spend for a given business initiative but to look at in terms of gender (male and female) and education level (college-educated and non college-educated), then we have four (2X2) possible views or dimensions. However, suppose that we want to analyze spend by gender (male and female), education level (college educated vs. non college educated), age category (<25,26-35,36-45,46-54,55+) and by household size category (1,2,3,4,5+), then the number of views or ways that we might want to look at spending explodes to one hundred (2X2X5X5) possible views or dimensions. The use of the pivot table represents the source file in terms of creating the desired measurement reports.

In Conclusion

Above is a visual schematic of the measurement process discussed, from the original data sources to the creation of the measurement reports.

Considerable planning for exactly what you are trying to measure is central to the development of measurement reports. At the same time, a deep understanding of the data environment is absolutely critical to successfully implementing any type of measurement plan. This requires a team-oriented approach between the marketers and the ‘data analytics’ group. Organizations with a strong cross functional team philosophy will have reporting systems that are most effective in the measurement of ROI.

About the author
Richard Boire is a recognized authority on data mining and is among the top five experts in this field in Canada. Richard is a Partner at Boire Filler Group, an organization which offers analytical and database services to companies seeking solutions to their existing data mining or database marketing challenges. Richard began his career pioneering predictive modeling and segmentation technology for direct marketing programs at Reader's Digest and American Express. As well as being published widely, Richard has taught applied statistics and database marketing at Concordia University, Vanier College in Montreal, and Humber College in Toronto and is currently a part-time lecturer at both George Brown College and Seneca College in Toronto. Richard Boire is the Chair of Canadian Marketing Association’s Marketing Technology and Database Intelligence Council and a member of the CMA’s Board of Directors.