Business Tip 7
National Promotions ... "What's Our ROI?"

The Basics

Evaluating promotions that are rolled out nationally over an extended period of time is no easy task for a couple of reasons:

• Participating retailers roll out the promotion at different times
• Two or more promotions may overlap
  

Here is a simple example of overlapping timing for three promotions-Football Frenzy, Halloween, and Thanksgiving-across three retailers:

Once the promotions have ended, pulling each retailer's promotional data, organizing it by promotion, and then manually totaling/summarizing the results within a spreadsheet could require a large block of time, and is often prone to human error. Which gets us back to our original question: Is there an easy way to determine ROI for national promotions?

For many data analytics tools on the market, the quick answer is "No". However, with some advanced planning, there is an excellent solution using Interactive Edge's XP3. And once you have it pre-built, it's simple to apply again and again.

The solution involves adding a "fourth dimension" to typical multidimensional database queries.

In Action
Multidimensional Databases: Adding a "Promotion" Dimension

If you've ever used (or seen the results from) syndicated data tools like Nitro or PlusSuite, then you have pulled data using 3 dimensions-time, geography/account, and product. Along with these dimensions you also would choose measures, such as sales and units. The type of database that allows you to make these selections is called a multidimensional database. This type of database gives you the ability to group and order members of each dimension and look at an intersection of three or more data points.

If we wanted only to look at the results for Football Frenzy in the above example, however, the problem is that our three retailers ran other promotions during the same weeks. Data tools that sit on top of 3-dimensional databases cannot split a shared time period between two different promotional activities. While doing individual data pulls for each retailer would get you around this issue, you would still be faced with manually summing up the results-every time you analyze a promotion.

For a longer-term, pre-designed approach to resolving this issue, a fourth dimension (besides Product, Market, and Time) called "Promotions" needs to be created within the database. Adding a Promotions dimension allows you to link a specific promo time period to a retailer, so that you can easily separate the sales volume for each promotion and for each participating retailer.

How does the Promotion Dimension work?

Let's go back to our promotional calendar example look at the Football Frenzy time period definitions:

• Retailer A: August to Mid-October
• Retailer B: August to Mid-October
• Retailer C: September to November
  

With a standard 3-dimensional database, you would need to select the specific promo weeks for each retailer every time you built or modified a data pull. However, by adding a fourth dimension with pre-defined promo date ranges for each retailer, the time period definition for Football Frenzy is always there, for each retailer. In spreadsheet terms, an extra column is added, and each cell in that column has a promotion name linked to the intersection of retailer, time period, product, and measure.

In the above diagram, the user clicked on the Promotion dimension, selected Football Frenzy from a list of promotions, chose the time period that covered all the retailers promotions, and selected the participating retailers and products (products are usually unique to one given promotion). The result is a Football Frenzy (FF) total for all the retailers, but no data for Halloween or Thanksgiving.

The Deliverables
By adding a fourth dimension to a multidimensional database, the data for each promotion is automatically segmented and available, allowing analysts to quickly and easily slice and dice the data for a variety of evaluations. This solution dramatically reduces the amount of time needed to determine whether a particular promotion delivered a good ROI, or if one particular retail customer executed better across promotions.

XP3 is a tool developed by Interactive Edge that builds multidimensional databases and allows a user to create as many dimensions as needed for complex analysis. Once the desired totals have been created, XP3 also has built-in calculation functions that would be able to show the percent change (growth) between comparison time periods, or across several different promotions.

The data, products and accounts depicted in this example are fictitious. Any resemblance to actual data, products or accounts is purely coincidental.