Free Spreadsheet: Calculating The Multichannel Purchase Index (MPI)

Why calculate something by hand when somebody can create a free spreadsheet for you?!

Download the Multichannel Purchase Index (MPI) Calculation Worksheet Here.

I spent some time modeling a three-channel relationship, which is included in the spreadsheet. The spreadsheet will only work if you manage two or three physical channels. If you have more than three channels, it is perfectly acceptable to modify your query to analyze a subset of channels.

The spreadsheet gives you clear instructions about how the index is to be interpreted.

• MPI of 0.00 to 0.99 = You have multiple channels, but your customers do not generally shop multiple channels.

• MPI of 1.00 to 1.49 = You have multiple channels, and your customers are willing to try multiple channels. You're a classic multichannel business.

• MPI of 1.50 or Greater = You have multiple channels, and you have multichannel customers. Being "multichannel" is really important to your business.

The metric is interesting to view, when calculated for the past several years. You'll quickly see whether your customer base is becoming "more multichannel" or "less multichannel". I've witnessed fascinating changes in some of the businesses I've looked at.

Why not leave an anonymous comment, sharing with The MineThatData Blog audience your annual frequency, percentage of multichannel customers, and the MPI for your business? I'm sure our readers would like to see anonymous results.

Thanks,
Kevin

The Multichannel Purchase Index (MPI) And Multichannel Forensics

Let's take a look at three examples of the interplay between the Multichannel Purchase Index (MPI) and Multichannel Forensics.


Example #1: A brand utilizes Online and Retail channels.



This is a typical situation, one where customers transfer from the online channel to the retail channel. Retail customers, however, stay in the retail channel, not being as willing to shop online.

Because the online channel transfers customers to retail, multichannel marketing is important. The Multichannel Purchase Index (MPI) is 1.229, suggesting that this brand is much more "multichannel" than the average brand.


Example #2: Another Online and Retail example.



In this example there is very little interplay between the online and retail channels. The online channel is in equilibrium mode, sending a small number of customers to retail. Retail is in isolation mode, not sending many customers to the online channel. Predictably, only 4.7% of the file is deemed "multichannel", yielding a Multichannel Purchase Index (MPI) of 0.844, below average.

When both channels are in isolation mode, you'll see MPIs between 0.400 and 0.750.


Example #3: Catalog and Online channels.



In this case, each channel is in equilibrium. Each channel passes customers to the other channel. Customers are fully immersed in the "multichannel" experience.

Even though the average purchase frequency is low (1.82), and the percentage of the customer file with multichannel status is only 9.8%, the MPI is very high, 2.522.

This is the beauty of the MPI. It is able to simultaneously adjust for purchase frequency and multichannel status, telling you what is really happening.

Multichannel Forensics and the Multichannel Purchase Index are highly correlated. When channels are in isolation mode with each other, the MPI will be low (<> 1).

Since is it so easy to compute the MPI for your two-channel situation, why not give it a try?

(PERCENTAGE OF 12 MONTH FILE BUYING FROM MULTIPLE CHANNELS LAST YEAR) divided by (0.00559 + 0.05536 * LN (AVERAGE PURCHASE FREQUENCY LAST YEAR)).

Multichannel Customers: The Multichannel Purchase Index (MPI)

We're told that multichannel customers are the best customers. And from time to time, we hear about somebody who increased their base of multichannel customers.

But how do we know if we're doing a good job of increasing our customer base? Or how do we compare with other companies? Is it good to have 5% of the twelve-month file purchasing from multiple channels? Should 35% of the twelve-month file be purchasing from multiple channels?

One of the problems we face when understanding multichannel customer behavior involves purchase frequency. Take two brands. One brand has a customer base that purchases five times a year. Another brand has a customer base that purchases two times a year. Which brand needs to take multichannel marketing more seriously?

Yup, you guessed correctly! The brand with customers purchasing five times a year needs to take multichannel marketing more seriously than the brand with customers purchasing two times per year. The customer buying five times a year is more likely to bump into more channels than the customer buying two times a year.

Next time you see your Business Intelligence staffer hanging out by the water cooler, ask her to run this query for you. This query assumes you have two channels (online & retail or online + telephone).

• Step 1 = Retrieve all customers who purchased in the past twelve months.

• Step 2 = Calculate the average number of purchases per customer in the past year (assume 2.0 for illustrative purposes).
  

• Step 3 = Calculate the percentage of customers purchasing from multiple channels in the past year (assume 7% for illustrative purposes).

Now, plot these two components against the line in this chart:



If you are above the line, you are, on average, outperforming peer companies. Your customers are more "multichannel" than peer company customers are. This doesn't mean, of course, that you are doing a good job of multichannel marketing. It simply means your customers are purchasing across channels at a greater rate than at peer companies.

If you prefer, use the following equation to calculate the Multichannel Purchase Index (MPI):

(PERCENTAGE OF 12 MONTH FILE BUYING FROM MULTIPLE CHANNELS LAST YEAR) divided by (0.00559 + 0.05536 * LN (AVERAGE PURCHASE FREQUENCY LAST YEAR)).

In our example, this equation equals (0.07) / (0.00559 + 0.05536*LN(2)) = (0.07) / (0.044) = 1.59.

This equation works for 2-channel companies. If the resulting index is > 1.00, your customers are more "multichannel" than at peer companies. If the resulting index is < 1.00, your customers are less "multichannel" than at peer companies.

Let our readers know what you find out when you calculate the Multichannel Purchase Index (MPI).

Thanks,
Kevin