Introduction

Welcome to the Mining Panel Data for Insights course. The Ehrenberg-Bass Institute has developed this online learning tool as a step-by-step guide to help you get the most out of household panel data. There are three sections. You can work through them one-by-one or click on the links below to go directly to a particular section.

1. Understanding the key Brand Performance Metrics
2. Tips to organise your data
3. What to expect from your panel buying data

There are examples and exercises to help you along the way. You will need to download this Excel spreadsheet to use while you are completing the course. Let’s get started!

Contact for questions: info@marketingscience.info.

Section 1 – Understanding the Key Brand Performance Metrics (BPMs)

This section of the course will help you to understand the Brand Performance Metrics (BPMs) you will be working with. We’ll cover which BPMs are important, how to calculate each metric and how to interpret the results. We’ll start with market share.

Market Share

Market Share is defined as the total category sales that are devoted to a particular brand. It can be measured in terms of units sold, volume or price paid. This is an example of how to calculate Market Share:

Market Share = (units of a brand sold ÷ total units of the product category sold) x 100

Note: For purchase metrics such as market share, the time frame for analysis is typically one year.
Note: Market share can also be calculated using volume or $.

Now let’s do some calculations.  Below is a table of raw data with the deodorant units sold over one year. Each row records all the purchases made by a particular household. For example, Household 1 made 12 purchases during the year, divided between Fresh Breeze (5), Quartet (3), Sphynx (2) and Sensi-Mate (2). Using the table below (which is also in the spreadsheet) we will do some market share calculations.

Brand Penetration

The penetration of a brand is the proportion of household buyers (the total number of households that have bought from the category) that have purchased the brand at least once during a specified time period.

For example, if there are 1 million households and 100,000 bought your deodorant at least once in the past year, your brand’s penetration would be 10%. You can calculate penetration using the formula below.

You may be familiar with the term “% buying”. Throughout the toolkit we refer to “% buying” as penetration.

Penetration = (number of household buyers ÷ number of total households) x 100

Note: The longer the time period, the higher the brand’s penetration will become.
Tip: Before you can calculate penetration, you first need to work out how many buyers a brand has.

Purchase Rate

The Purchase Rate is the average number of times households who bought the brand did so in the given time period.

Purchase Rate = total sum of household purchases ÷ number of household purchases

Note: Purchase Rate also increases when the time frame is extended, but at a slower rate than penetration.
Note: For Penetration we normally round to a whole figure. For Purchase Rate it is customary to round to one decimal point.
Tip: We exclude those households who did not buy the brand at all in this calculation.

Share of Category Requirements (SCR)

Share of Category Requirements (SCR) is the proportion of a household’s total purchases from the category devoted to one particular brand. This is reported as a percentage. The average of all the individual household SCRs is the brand’s share of category requirements. You can calculate the SCR for any household by using the following formula:

SCR = Household’s purchases of a brand ÷ Household’s total number of category purchases

For example, let’s look at Household 1.

Household 1 purchased from the category 12 times in total. These 12 category purchases were split between four brands, Fresh Breeze, Quartet, Sphynx and Sensi-Mate. So for Household 1 we need to work out the SCR for each of these four brands.

Tip: The SCR for all 4 brands should add up to 100% or very close (with rounding)

Let’s take a look at another example. Household 16 purchased from the category 8 times. These 8 category purchases were split between three brands, Quartet, Ulti-Mate and Sphynx.

We need to work out the SCR for these three brands.

Can you do this analysis for all households and all brands in the table contained in the spreadsheet ‘Table 1 for SCR calculations’? After you have done this, then go to the next page to see what the table should look like.

Now to calculate loyalty for each brand, you simply take an average across all household buyers who have a greater than zero SCR for that brand.

Tip: The average should be based only on those households who buy the brand at all. You might find it easier to delete the zeros and then use the averages of each column to calculate the average SCR.

Exclusive or 100% Loyalty measures the proportion of a brand’s household buyers that only buy that one brand in the time period. It is reported as a percentage of the brand’s customer base.

Exclusive or 100% Loyalty = (number of household buyers who did not purchase any other brand ÷ number of household buyers of that brand) x 100

That’s it for section 1.

You should now know how Market Share, Penetration, Purchase Rate, Share of Category Requirements and % of Exclusive or 100% Loyals are calculated. These are Brand Performance Metrics that we commonly work with. Often, these will come already calculated for you. The next step is about gaining insights from a set of BPM’s across all brands in a category.

Section 2 – Tips to organise your data

This section of the toolkit will help you turn “raw” data into meaningful information. You will learn how to see patterns that exist in panel data, identify exceptions and learn how to easily communicate the key points to others.

Why do we need these principles?

It’s all about managing your data. We usually start with a large amount of raw data in an excel spreadsheet.  It’s often very hard to gain an initial sense of what data could tell you. Given how busy we all are, and how little time we have, learning to organise your data so that everyone can quickly see the key points, is immensely useful. And your colleagues will thank you! Applying these principles will also make it easier to compare results across markets. It becomes much easier to interpret the data by following some simple rules to guide table layout and presentation.

There are six simple rules to follow:

1. Remove % signs from the table body
2. Round to two effective digits
3. Order brands by size
4. Add averages to aid comparisons
5. Use table layout to guide the eye
6. Provide a brief verbal summary

Step 1 – Remove % signs from the table body

Having % signs in the body of a table can make it difficult for the eye to spot patterns. To minimise clutter, remove % signs from the body and place them in column headings.

Before removing the % signs the table looks like this:

This is what the table looks like without the % signs:

Step 2 – Round to two meaningful digits

Rounding to two meaningful digits simply means taking a number such as 23.43% and rounding it to 23%. Numbers rounded to two meaningful digits are much easier to read and remember than the longer ones. This is what the table looks like after rounding.

Step 3 – Order brand by size

The second step is to order your table by size. This is a simple, but often neglected step in the synthesis of buyer behaviour data. For example, data is often presented to clients in alphabetical order, or the order that the brands feature in the questionnaire. Rarely does this type of ordering give us any useful information. And sometimes it can steer us in the wrong direction. Here’s a table where the brands are ordered alphabetically. It’s hard to come to any conclusion about how the metrics are related to each other.

Now let’s look at the same data, but where the table has been re-sorted to present brands by penetration. Now we can start to see a relationship between the metrics. For example, can you see that brands with higher penetration also tend to have higher purchase rates and SCR.

Step 4 – Add averages to aid comparisons

Averages are useful as a basis to compare across brands. You can quickly see when a brand scores higher or lower than most other brands. In the last table (without averages), its hard to get a sense of how the brands vary on each of the metrics. After adding the averages, we can more clearly compare and see that the large share brands have higher penetration, purchase rate and SCR.

Practice Question

Go to the “Table for data reduction” sheet on the Excel workbook (which you may have already downloaded). You will also see the table below.

Can you convert this table into a more user-friendly format, using the steps we have outlined?

• Step 1 – Remove all % signs from the table body
• Step 2 – Round to two meaningful digits
• Step 3 – Order brands by size (in this case, size is penetration, which can stand in for market share in our example)
• Step 4 – Add averages

Make as many changes as you can in your Excel spreadsheet before continuing to the next page to view the re-formatted table.

Common types of deviations

There are two types of deviations:

Niche brands — What looks like excess loyalty, where a brand has higher loyalty than would be expected given its market share.

Change of pace brands — What looks like a deficit in loyalty, where a brand has lower loyalty than would be expected given its market share.

Reasons for Deviations

These are the key reasons that we have found for deviations. As we discover more reasons we will update these lists:

Niche Brands

• Brands with limited distribution (eg, Private label or retailer brands or brands that are sold in a limited geographical area).
• Brands that are functionally different (eg, Diet soft drinks) such that some people do not consider them.

Brands used in limited situations

• Brands that only suit one purchase occasion (eg, a chocolate product only used at Easter which limits the number of times it’s bought).

Deviations will be explained more in section 3.

Organising your data for reports & presentations

For reports and presentations you can make some further improvements to the data layout. Here are some steps for this. To do this section, you need to open up Microsoft Word or Powerpoint – whichever program you use most often to communicate results to others.

Step 5: Use table layout to guide the eye

Use row and column lines in the table to guide the eye. This removes clutter and makes it easier for your eye to focus on, and compare the numbers. Three steps to help you improve how you present data are:

1. Remove any unnecessary table lines (horizontal or vertical) – only keep the ones that guide the eye to look down columns or across rows as necessary
2. Use bold lettering where appropriate (column titles, totals, averages)
3. Table text should have smaller typeface to other written text

Note: we have only used the top eight brands to demonstrate.

Before applying table layout rules our table looks like this:

Table 9: Brand performance metrics

After applying table layout rules it should look like this:

Table 10: Brand performance metrics (lines removed, some lettering bolded)

Note: We have avoided using too much space between the rows. Unnecessary space distracts the eye from seeing the pattern and relationship between the numbers.

Step 6: Provide a brief verbal story

A story
is a brief, one sentence description of the key point of the table. It should provide the reader with the key takeout of the table.

Table 11: Brand performance metrics (with brief story included)

Smaller brands have lower loyalty than larger brands

You can try this for yourself by taking the table of data and putting it into a word document or a Powerpoint presentation. You will immediately see the difference! Your table should now look something like this…

Table 12: Brand performance metrics (all table presentation rules applied)

Brands vary much more in penetration than in purchase rate or SCR

Section 3 – What to expect from your panel data

What should your panel data look like?

Once tables have been presented in line with the guidelines you have just worked through, it becomes easier to spot patterns in the data. You will have noticed that when brands are sorted by size, loyalty measures move in line with penetration. We also find that these measures move in line with market share. As we know what to expect, we can easily spot deviations. Deviations are important as they suggest something interesting might be going on with a brand. They give us direction as to where to dig deeper in the data.

This approach can be done at either Total corporate, Parent brand, or Sub-brand levels but it is best not to mix up the levels of brands.  It is important to make sure there is no “double counting” in the list of brands.

Identifying and spotting deviations

A deviation is where a result is not following the expected pattern. It is where a brand is performing better or worse than expected on a BPM for its size in the market. Let’s take a closer look at the BPMs to see the expected patterns and do some deviation spotting.

Note: Click here to for an explanation of common types of deviations.

Market Share and Brand Penetration (% buying)

Let’s start by looking at the expected patterns between Market Share and Penetration. We expect to see market share moving in line with penetration, i.e. as market share decreases, penetration also decreases.

Brand Penetration and Purchase Rate

The next relationship we can expect to see is that of Double Jeopardy. Remember, the Double Jeopardy pattern says that smaller share brands have a lower level of penetration and purchase rate than their larger share counterparts. Look at the data in this table.

Penetration and SCR

We also expect to see a Double Jeopardy pattern when comparing penetration and SCR. Look at the table below.

To recap again, the smaller share brands have a lower level of penetration and also SCR than their larger share counterparts.

How often do people buy each brand?

Now we are going to look at how often people buy each brand. To do this we look at the distribution of buying frequencies. A buying frequency distribution shows how many households buy the brand zero times, once, twice, three times etc. This is important as it tells you how often your customers are interacting with the brand. A household that buys it six times a year is in the market for the brand about once every two months. However, a household that only purchases it once very rarely interacts with the brand. Let’s have a look at some data showing the percent of households purchasing Scandina in a year:

Tip: Typical is not the average, but rather what is common amongst most households.

Here are some examples of purchase frequency distributions for the bigger brands. You will see that the pattern is the same across the brands.

The End.

That’s it! By now you should:

• Understand the key BPMs and how they are calculated
• Know how to organise and present your BPMs
• See expected patterns so you know what is normal for your data
• Be able to spot deviations in the data, which can help you uncover insights into specific brands
• Know about the Double Jeopardy Law and understand most BPM’s follow this pattern
• Understand how key BPMs vary across brands in a market
• Know that the majority of a brand’s customers buy it quite infrequently

Click here for a print-ready pdf of key BPM calculations. Thank you for taking the time to participate in the Mining Panel Data for Insights course!  We hope you enjoyed the experience, and learnt from it as well. If you have any feedback or suggestions then please email them to: info@marketingscience.info If you are excited by this, or found it too easy, then there are some more advanced analyses you can do with panel data.

We recommend you look to the following reports for suggestions:

1. Quantifying the sales from your heavy buyers
2. Examining brand competition and how brands share customers
3. Dynamic analysis of growth and decline