How to analyse product consumption and repurchase rates

You can learn many things about your products from the purchase behaviours and product consumption and product replenishment of your customers. Some items are purchased individually, some items are purchased in small groups, and others are bought in bulk.

Some products are only ever purchased once by each customer, while others are trialled, and the customer either repurchases, trials an alternative, or goes somewhere else. Knowing these things can help you in several ways and can help several departments within the typical ecommerce business improve their KPIs and generate more profit for the business.

In this project, we’re going to take a look some different ways of measuring product consumption and product repurchase rates to identify potential strategic changes that could be made. To do this we first need to understand product consumption and repurchase rates.

Product consumption

Product consumption is a measure of the typical volumes of each product that get purchased. Certain items are usually only purchased individually, either because they’re expensive, or because customers don’t need more than one unit.

For example, most people only ever buy a single fridge, phone, or ladder. Offering bulk discounts on multiple units probably won’t sell you any more units, unless you’re a B2B retailer.

However, others are commonly purchased as multiple units in a single basket, or purchased in bulk. How many people buy a single banana or potato, for example? Customers expect such products to be bundled in larger quantities (imagine the inconvenience of purchasing individual blueberries, for example), and would likely want a discount for purchasing a higher volume.

Product consumption is typically measured in ecommerce using a metric such as “average items per basket”. Since this metric is directly correlated with average order value, ecommerce merchandisers and marketers will be aiming to influence customers to buy slightly more and make both metrics go up.

Factors affecting product consumption

Most people stockpile toilet roll (especially in pandemics). Picture by “Hello I’m Nik”, Unsplash.

Repurchase rates

The repurchase rate, or repurchase index, is calculated by identifying the number of customers who re-purchased a given product over the total number of customers who purchased the product, sometimes over a specific time period. It aims to measure the proportion of customers who purchased a product once, then purchased the same item again.

By introducing a time element, such as the repurchase rate for a rolling 12 month window, you can measure changes in product purchasing behaviour. Perhaps you increased your price and customers switched to cheaper brands or started shopping elsewhere? Or, maybe you’ve changed your product recommendations or run a promotion that has encouraged customers to buy an item again.

As with the average items per basket metric, merchandisers and marketers want customers to repurchase products. This helps strengthen the customer relationship, which in turn increases the likelihood of customer retention and increases CLV and customer equity. Of course, with so many factors influencing the likelihood to repurchase, it’s not all within the hands of the ecommerce and marketing team, but much is.

If repurchase rates on SKUs are high, adding a subscription option may be advantageous. However, it could be a waste of development resource if few products ever get purchased for replenishment.

Factors affecting repurchase rate

Picture by Charisse Kenion, Unsplash.

Load the packages

The vast majority of our work here can be done within Pandas, but we’ll also need a bit of NumPy, and some Seaborn and Matplotlib for data visualisation. Open a Jupyter notebook and load the required packages.

import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt

Optionally, if you want to generate sharper, more stylish charts, you can pass in some additional commands to Seaborn to set up themes and retina mode visualisations.

%config InlineBackend.figure_format = 'retina'
sns.set_context('notebook')
sns.set(rc={'figure.figsize':(15, 6)})
sns.set_style("dark")
sns.set()

Load the data

Next, load up a transactional item dataset. This needs to include the SKU (stock keeping unit or product code), the quantity of units purchased, the unit price paid, the customer ID, the order ID, and the order date.

df = pd.read_csv('transaction_items.csv')
df.sample(5)

Create a repurchase rate function

While you can calculate each metric individually in Jupyter and examine the output if you wish, it’s helpful to create a reusable function for this analysis so you can re-run it again in the future.

The aim of our function will be to segment the product portfolio based on product consumption and repurchase behaviour, so we can identify which items are commonly purchased individually versus those purchased in multiples, and identify which products are purchased once by a customer, versus those that are purchased multiple times.

This uses a bit of data wrangling to calculate the purchase consumption metrics for each SKU in the transactional item dataset, and then calculates the number of times each SKU was purchased individually in orders, and purchased once by customers.

It then uses those data to calculate the “bulk purchase rate” (indicating the percentage of orders that include the same SKU multiple times) and the “repurchase rate” (which shows the percentage of customers who purchased the same SKU again in a future order).

def get_repurchase_rates(df):
    """Given a Pandas DataFrame of transactional items, this function returns
    a Pandas DataFrame containing the purchase behaviour and repurchase 
    behaviour for each SKU. 

    Parameters
    ----------

    df: Pandas DataFrame
        Required columns: sku, order_id, customer_id, quantity, unit_price

    Returns
    -------
    df: Pandas DataFrame
        sku, order_id, customer_id, quantity, unit_price, avg_unit_price, avg_line_price, revenue,
        times_purchased, purchased_individually, purchased_once, items, orders, customers, 
        bulk_purchases, bulk_purchase_rate, repurchases, repurchase_rate

    """

    # Count the number of times each customer purchased each SKU
    df['times_purchased'] = df.groupby(['sku','customer_id'])['order_id'].transform('count')

    # Count the number of times the SKU was purchased individually within orders
    df['purchased_individually'] = df[df['quantity']==1].\
        groupby('sku')['order_id'].transform('count')
    df['purchased_individually'] = df['purchased_individually'].fillna(0)

    # Count the number of times the SKU was purchased once only by customers
    df['purchased_once'] = df[df['times_purchased']==1].\
        groupby('sku')['order_id'].transform('count')
    df['purchased_once'] = df['purchased_once'].fillna(0)

    # Calculate line price
    df['line_price'] = df['unit_price'] * df['quantity']

    # Get unique SKUs and count total items, orders, and customers
    df_skus = df.groupby('sku').agg(
        revenue=('line_price', 'sum'),        
        items=('quantity', 'sum'),
        orders=('order_id', 'nunique'),
        customers=('customer_id', 'nunique'),
        avg_unit_price=('unit_price', 'mean'),
        avg_line_price=('line_price', 'mean')
    )

    # Calculate the average number of units per order
    df_skus = df_skus.assign(avg_items_per_order=( df_skus['items'] / df_skus['orders'] ))

    # Calculate the average number of items per customer
    df_skus = df_skus.assign(avg_items_per_customer=( df_skus['items'] / df_skus['customers'] ))

    # Merge the dataframes
    df_subset = df[['sku','purchased_individually','purchased_once']].fillna(0)
    df_subset.drop_duplicates('sku', keep='first', inplace=True)
    df_skus = df_skus.merge(df_subset, on='sku', how='left')

    # Calculate bulk purchase rates
    df_skus = df_skus.assign(bulk_purchases = (df_skus['orders'] -\
                                               df_skus['purchased_individually']) )
    df_skus = df_skus.assign(bulk_purchase_rate = (df_skus['bulk_purchases'] \
                                                   / df_skus['orders']) )

    # Calculate repurchase rates
    df_skus = df_skus.assign(repurchases = (df_skus['orders'] - df_skus['purchased_once']) )
    df_skus = df_skus.assign(repurchase_rate = (df_skus['repurchases'] / df_skus['orders']) )

    return df_skus

Next, we can run the get_repurchase_rates() function above on our transactional items dataframe to return a new dataframe containing the product consumption and repurchase metrics for each SKU in the product portfolio. Using T to transpose the data makes it a bit easier to fit on the screen.

df = get_repurchase_rates(df)
df.sample(3).T

Binning repurchase rate data

The raw values are useful but labeling the data will make it much easier to understand. For this we can use the quantile-based discretization approach to bin or bucket data and assign labels to each product based on the repurchase rate or bulk purchasing rate.

The Pandas cut() function makes this quite simple. We’ll use this to assign the repurchase_rate metric to five bins that we will label according to the rate. There’s no need to set the bounds for the bins here, as the function can do this for us.

def get_repurchase_rate_label(df):

    labels = ['Very low repurchase', 
              'Low repurchase', 
              'Moderate repurchase', 
              'High repurchase', 
              'Very high repurchase']
    df['repurchase_rate_label'] = pd.cut(df['repurchase_rate'], 
                                          bins=5, 
                                          labels=labels)
    return df

df = get_repurchase_rate_label(df)
df['repurchase_rate_label'].value_counts()

Very low repurchase     4344
Low repurchase           975
Very high repurchase     646
Moderate repurchase      200
High repurchase           29
Name: repurchase_rate_label, dtype: int64

Running the function on our data and examining the number of SKUs with each label using the value_counts() function, we can see that a very high proportion (70%) of the products sold by this retailer have a very low repurchase rate. They’re probably the sorts of product that are one-off purchases, or where the product lifespan is very long. However, some customers may not have been happy with their trial purchase, or they may have found the same item on sale cheaper elsewhere.

Only 14% of the products sold by this retailer have a moderate, high, or very repurchase rate. This might not be an issue in this market, where products may not need to be purchased frequently, but it could represent an opportunity to fill potential range gaps and get customers shopping more frequently. This is certainly going to make it tricky to increase the frequency at which customers shop, whatever the cause, so it needs some extra investigation.

f, ax = plt.subplots(figsize=(15, 10))
sns.countplot(y="repurchase_rate_label", data=df, palette="Blues_d")

<matplotlib.axes._subplots.AxesSubplot at 0x7f563072ec70>

Boxplots of the repurchase rate labels show that the majority of orders are small, but there is a larger spread on some SKUs, plus some outliers on the lower repurchase rate items.

sns.boxplot(x="repurchases", y="repurchase_rate_label", data=df, palette="husl", orient='h')

<matplotlib.axes._subplots.AxesSubplot at 0x7f5630c52070>

Binning bulk purchase rate data

We can use the same binning approach on the bulk purchase rate data to assign labels to each SKU based on the number of times the SKU is purchased individually or in larger volumes.

def get_bulk_purchase_rate_label(df):

    labels = ['Very low bulk', 
              'Low bulk', 
              'Moderate bulk', 
              'High bulk', 
              'Very high bulk']
    df['bulk_purchase_rate_label'] = pd.cut(df['bulk_purchase_rate'], 
                                          bins=5, 
                                          labels=labels)
    return df

Running this function and examining the data shows that 75% of SKUs are purchased individually or in very low bulk quantities. Only 37 SKUs (0.6%) are purchased in high bulk, 2.6% are purchased in moderate bulk, and 8.86% are purchased in low bulk. Encouragingly, 12.6% are bought in very high bulk, so there are some opportunities here to try and get customers to buy more through promotions or bundling.

df = get_bulk_purchase_rate_label(df)
df['bulk_purchase_rate_label'].value_counts()

Very low bulk     4666
Very high bulk     780
Low bulk           549
Moderate bulk      162
High bulk           37
Name: bulk_purchase_rate_label, dtype: int64

f, ax = plt.subplots(figsize=(15, 10))
sns.countplot(y="bulk_purchase_rate_label", data=df, palette="Blues_d")

<matplotlib.axes._subplots.AxesSubplot at 0x7f56308faa00>

Boxplots of the bulk purchase rate for each label show the typical spread and the outliers. Even among the lower volume SKUs, there are some larger orders.

sns.boxplot(x="bulk_purchases", y="bulk_purchase_rate_label", data=df, palette="husl", orient='h')

<matplotlib.axes._subplots.AxesSubplot at 0x7f562fcb4400>

Create a combined label

Finally, we can concatenate the two labels together to form a new feature which merges the repurchase rate data with the bulk purchase rate data. This is a good way to understand the types of products sold by the retailer. There’s a mixture of one-off purchases sold individually comprising the majority of units, followed by some one-offs purchased in bulk.

df['bulk_and_repurchase_label'] = df['repurchase_rate_label'].astype(str) +\
                            '_' + df['bulk_purchase_rate_label'].astype(str)

df.bulk_and_repurchase_label.value_counts()

Very low repurchase_Very low bulk      3461
Low repurchase_Very low bulk            624
Very low repurchase_Very high bulk      468
Very high repurchase_Very low bulk      446
Very low repurchase_Low bulk            296
Low repurchase_Very high bulk           154
Low repurchase_Low bulk                 147
Moderate repurchase_Very low bulk       116
Very high repurchase_Very high bulk     115
Very low repurchase_Moderate bulk        97
Very high repurchase_Low bulk            68
Moderate repurchase_Very high bulk       40
Low repurchase_Moderate bulk             37
Moderate repurchase_Low bulk             33
Very low repurchase_High bulk            22
High repurchase_Very low bulk            19
Very high repurchase_Moderate bulk       16
Low repurchase_High bulk                 13
Moderate repurchase_Moderate bulk        10
High repurchase_Low bulk                  5
High repurchase_Very high bulk            3
High repurchase_Moderate bulk             2
Very high repurchase_High bulk            1
Moderate repurchase_High bulk             1
Name: bulk_and_repurchase_label, dtype: int64

We can use Seaborn to visualise these numbers to see where the product portfolio sits in terms of product consumption and repurchase rate.

f, ax = plt.subplots(figsize=(15, 10))
sns.countplot(y="bulk_and_repurchase_label", data=df, palette="Blues_d")

<matplotlib.axes._subplots.AxesSubplot at 0x7f5630dcfbb0>

Examine correlations

Domain knowledge would likely be required to interpret whether the labels assigned were an issue or not. However, using a Pearson correlation matrix visualisation can show us the relationships between the metrics we’ve calculated.

sns.set_theme(style="white")
corr = df.corr()
mask = np.triu(df.corr())
f, ax = plt.subplots(figsize=(15, 15))
cmap = sns.color_palette("Blues")

sns.heatmap(corr, 
            annot=True,
            fmt='.1g',
            mask=mask, 
            cmap=cmap, 
            vmax=.3, 
            center=0,
            square=True, 
            linewidths=3, 
            cbar_kws={"shrink": .5}
           )

<matplotlib.axes._subplots.AxesSubplot at 0x7f562d59a790>

Some of the observations in here are obvious. The items that get repurchased in future orders are also often purchased in multiples in the same order, which suggests a multi-pack or bulk discount approach could work on some SKUs. As you’d imagine, the items that get frequently repurchased also tend to be cheaper lines.

df[df.columns[1:]].corr()['repurchase_rate'][:].sort_values(ascending=False).to_frame()

Examining the Pearson correlations for the bulk_purchase_rate against the other columns shows that overlap with repurchase rate again.

df[df.columns[1:]].corr()['bulk_purchase_rate'][:].sort_values(ascending=False).to_frame()

Examine data by label

To further examine the relationship between the repurchase rate and the bulk purchase rate we can use a groupby() function with agg() to calculate some aggregate grouped statistics. These show us where the money is being made and from which type of SKU. Again, there could be some opportunities in here.

matrix = df.groupby('bulk_and_repurchase_label').agg(
    skus=('sku', 'nunique'),
    revenue=('revenue', 'sum'),
    orders=('orders', 'sum'),
    repurchases=('repurchases', 'sum'),
    repurchase_rate=('repurchase_rate', 'mean'),
    bulk_purchases=('bulk_purchases', 'sum'),
    bulk_purchase_rate=('bulk_purchase_rate', 'mean'),
    avg_line_price=('avg_line_price', 'mean'),
    avg_unit_price=('avg_unit_price', 'mean'),
    avg_items_per_order=('avg_items_per_order', 'mean'),
    avg_items_per_customer=('avg_items_per_customer', 'mean'),
).sort_values(by='revenue').reset_index()

matrix

Next steps

Now we’ve got our products labeled, and we have metrics identifying how customers commonly purchase them, some further work is required to extract some business value from the data. Here’s what I’d suggest, based on the findings above:

1. Go back and add in the brand, product type or product category and re-run the analysis. Certain product types or product categories are likely to see more volume purchasing and more repeat purchasing than others.
2. Are some products in the same product category, or the same product type showing different levels of volume purchasing or repeat purchasing? Are the products priced correctly? Are they appearing in product recommendations? Can product inter-linking be improved?
3. For the products sold in multiples and that are repurchased, can multi-packs be created to try and increase basket sizes and values? Can you offer customers a discount to get them to “supersize” their order, either on the product page or at the basket?
4. What is the average repurchase rate for each product type, product category and brand? Are some brands or products less popular than others? Is that correlated with review ratings? Should they be removed from the range?
5. Can you combine the data with a cohort analysis? Can you measure whether recipients of a specific promotion were influenced to repurchase a product or brand again following an offer?
6. If you know that certain customers frequently repurchase a particular product, can you persuade them to switch brands to a more profitable premium product?
7. What do your best customers buy? Do you sell enough items that cause customers to buy again and again? Do the data suggest you have range gaps you need to fill?

There are probably loads of other things you could do with these data. Please let me know below if you have any other interesting applications.

Matt Clarke, Sunday, March 14, 2021