This is the third in a series of delicious analytics “recipes” that will make any data-driven professional’s mouth water.

Recipe: Product Analysis Pasta

Amazon, Netflix, and Pandora paved the way in cross product analysis. Their recommendation engines are big data at its best, comprised of finely-tuned algorithms that know what customers want before they even want it. These recommendation engines are powerful, but for most ecommerce companies, the cost is prohibitive.

Are you doing cross product analysis to increase customer lifetime value?

In this Product Analysis Pasta recipe, we’ll outline an approach to cross product analysis (also known as “affinity analysis” or “market basket analysis”) that will help you tap into the power of a recommendation engine, without the cost. This is a bare bones approach, but it works. One of our clients made an additional $12k in three days by implementing their findings from this approach.

Let’s get started!

Prep Work

Before you start, you need to first identify whether your product catalog is:

  • Narrow, but deep – for example, a company that only sells knitting-related products, including knitting needles, yarn, patterns, and kits.
  • Broad, but shallow – for example, a company that sells clothing for the whole family.

A company with a narrowly focused product line will want to do analysis at the item level. They’ll start by identifying their best-performing product and then find what items are most often purchased along with it. A company that sells a broad array of categories will be better served by analyzing what categories are frequently purchased with their top-performing category.

You also need to think about whether you want to look at your data on the order level or user level.

  1. Order level: Looking at your data on an order level will give you a good sense of how to boost your average order value by offering cross-sell and up-sell options.
  2. User Level: Looking at your data on a user level will show you how a product fits into the customer lifecycle. This analysis is particularly useful when combined with segmentation. For example, is your most popular item or category frequently sold to repeat purchasers? This could indicate that it’s a driver of loyalty, and you would want to promote it heavily to first-time buyers.

In this example, we’ll be looking at data the item level and the order level. The raw data will be different depending on the approach you choose, but the analysis will work exactly the same way.



Transaction Data

You’ll need a list of every order you have filled in the past 3 months, broken down by items purchased.

Get Cooking

Step 1: Measure Your Ingredients

To start setting up your product analysis calculation, you’ll first need to pull all of your Item and Order data from the past 3 months into an Excel spreadsheet.

Step 2: Find Your Top Selling Product

The next step is to filter your spreadsheet by only those orders that contain your top-selling item. You can find the formula for this, as well as the following steps, on this handy Google spreadsheet.

Step 3: Find What Products Are Most Often Bought With Your Most Popular Item

Once you identify what the top-selling product is, you’ll next want to look at which item is most frequently purchased with it. Calculate the total number of times that an order contained both your most popular item and each potential cross-sell item.

Step 4: Calculate Frequency of Cross-Sell Possibilities

Calculate the percentage of time that an order contained both your most popular item and each potential cross-sell item.

Step 5: Measure Popularity of Cross-sells

Calculate the total number of orders that contain each potential cross-sell item. This step will help provide some context by showing how popular each potential cross-sell item is overall.

Step 6: Calculate Frequency of Cross-sell Items

Finally, you’ll want to calculate the percentage of total orders that contain each potential cross-sell item, to get an overall sense of how frequently each one is purchased overall.

To start looking at cross-product analysis for your business, you can save a copy of the spreadsheet we created and do some experimentation with your own data.


Note: This kind of cross-product analysis can be a quick win to improve customer lifetime value, but it’s important to understand that product analysis is not the answer for all of your product-related issues. Unless your product is extremely unique in the marketplace, there are other factors that may be affecting your customers’ purchase patterns. What is your advantage in the market? If your customers buy a lot of one of your products but not another, it may just be that they’re finding a better deal or better customer service elsewhere.

Make Product Analysis Work For Your Business

Once you know what your data looks like, you can try one or more of following three marketing strategies:

1. Cross-selling

“Do you want fries with that?”

Cross-selling is offering a complementary product to your customers: like fries with a burger, or ping pong balls with chips.

Any kind of company can use cross-selling as a strategy to increase revenue. It’s not always as intuitive as grocery store examples, which is why using your data to be strategic about it is key.

Use your data to find the perfect-cross sells for your business.

2. Up-selling

“Try our premium yarns for those special knitting projects”

Upselling is offering your customers a more expensive product than the product they are looking at. This works well for companies that have narrow, deep product lines. It can be challenging to get your customers to break their usual patterns and purchase higher-value items. Looking at your data can help you determine which customer segments would be most receptive, so that you can approach them strategically.

3. Sequencing

“Many of our customers also purchased…”

Sequencing is looking at the behavior of your best customers and using that information to guide other customers in the same direction. What do your best CLV customers buy overall? What did they buy in their first order? Second order? The behavior of your worst customers can all be very informative. If, for example, you find that you have a lot of one-time order customers, you’ll want to take a closer look. It may be a sign of a significant product quality issue. Sequencing is best for companies that have a lot of data about their product line.

When you’re trying to decide which of these three strategies to try, you should take into account whether your focus is on loyalty or acquisition:

  • Loyalty. If you sell knitting supplies, you likely have loyal customers who keep coming back. You have time to sell your customers more items and migrate them to your premium product lines, so you’ll want to focus on how best to do that through cross-selling and up-selling efforts.
  • Acquisition. If you sell mattresses, you’ll have very few repeat purchasers. You have one shot to sell to each customer, so you’ll want to focus on increasing average order value.

Gourmet Pasta Preparation

Using spreadsheets for some rough cross-product analysis on one or two products is doable. However, your results will be much more accurate if you’re able to do advanced calculations to take support, confidence, and lift into account:

  • Support is the probability that an order contains item X.
  • Confidence is the conditional probability that an order contains item Y, given that it already contained item X.
  • Lift is a statistical measure that shows whether the presence of X increases or decreases the likelihood of an order containing Y.

Doing these calculations for multiple products in a spreadsheet can be complicated and time-consuming, especially when you consider that you’ll need to update your data frequently. With RJMetrics, you’ll be able to set up your calculations for a number of products or categories just one time, and they’ll update automatically.