How to analyse product replenishment

Subscription commerce was all the rage for a while, but it’s not really become as popular as many in ecommerce perhaps envisaged. While we may have subscriptions for certain things, most of us have continued to buy consumable products in the same way - by replenishing them whenever we run out.

Online retailers are wise to this common behaviour, and many create specific product replenishment email campaigns to target such customers. According to some marketing studies, these product replenishment emails can have the highest click through rates of any marketing automation emails, so they’re worth adding to your email marketing arsenal.

Product replenishment email campaigns

Product replenishment emails aim to contact each customer just before they are due to replenish a given product and give them with a gentle reminder to buy some more before they run out. They work because they’re timely and relevant. However, replenishment campaigns are harder to get right than you might first imagine, because customers all behave in slightly different ways.

In this project, I’ll show you how you can use Pandas to calculate product replenishment rates for each of your products and calculate the average time it takes customers to replenish them. For simple products, the data could be used as-is to power basic product replenishment campaigns, but they also serve as the raw features for product replenishment campaign models.

Load the packages

First, open a Jupyter notebook and import the pandas, numpy, and seaborn packages. You’ll likely already have these installed, but if you don’t you can install them via the PyPi package manager by entering pip3 install package-name in your terminal or !pip3 install package-name in a cell in your Jupyter notebook.

import pandas as pd
import numpy as np
import seaborn as sns

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

Load the data

Next, load up your dataset of transaction items. I’m using the Online Retail dataset from the UCI Machine Learning Repository. This is widely used for demonstrations in ecommerce data science and includes a wide range of regularly purchased items from an online retailer selling party supplies. This includes a number of items that regularly get repurchased.

To tidy up the data, I have renamed the columns, and importantly, I have ordered the data in descending order of the order_date column. If you miss this vital step, you’ll get nonsensical results, since everything we’re doing relies on the data being ordered chronologically.

df = pd.read_csv('online_retail.csv', 
                 names=['order_id', 'sku', 'description', 'quantity', 'order_date', 'unit_price', 'customer_id', 'country'], 
                 skiprows=1).sort_values(by=['order_date'], ascending=False)
df.head()

Remove zero-value order lines

The second important thing we must do is ensure that any zero-value order lines, such as returns or free gifts, are removed from the dataset. An item that arrived damaged or was faulty and has been replaced free of charge shouldn’t be confused with a genuine replenishment of the product, so this step is critical. I’ve done this twice - once using unit_price and once using quantity and then have checked the aggregate statistics using describe().

df = df[df['unit_price'] > 0]
df = df[df['quantity'] > 0]

df.describe()

Calculate each customer’s previous order date for each SKU

In order to calculate the average replenishment time for each SKU, our first step is to calculate the replenishment date for each customer-SKU combination. To do this, we’ll use the Pandas groupby() function and will pass it both the customer_id and the sku. We’ll then append the order_date column and use the shift() function to get the previous value from the order_date column using -1.

df['previous_sku_order_date'] = df.groupby(['customer_id', 'sku'])['order_date'].shift(-1)

To check that the date of each customer’s previous order for each SKU has been calculated correctly, we can filter our dataframe to look at a specific customer-SKU combination. Customer 12680 has purchased the SKU 22728 three times, first on August 18th 2011, then on September 27th 2011, and finally on December 9th 2011. For each purchase, we’ve correctly assigned the date of the previous order to the previous_sku_order_date column, and have a NaN when the purchase was a customer’s first for the given SKU.

df[(df['customer_id']==12680) & (df['sku']=='22728')].sort_values(by='order_date', ascending=True)

Calculate the number of days to replenishment

Next, we’ll create a function to calculate the number of days between each of a customer’s orders of a given SKU. This takes three values: the original dataframe, the “before” date, and the “after” date. It then calculates the time delta - or the time difference between the two dates - and returns the number of days as an integer, that we can then assign to a new column.

def get_days_since_date(df, before_datetime, after_datetime):
    """Return a new column containing the difference between two dates in days.
    Args:
        df: Pandas DataFrame.
        before_datetime: Earliest datetime (will convert value)
        after_datetime: Latest datetime (will convert value)
    Returns:
        Days since previous date. 
    """
    df = df.copy()
    df[before_datetime] = pd.to_datetime(df[before_datetime])
    df[after_datetime] = pd.to_datetime(df[after_datetime])

    diff = df[after_datetime] - df[before_datetime]
    return round(diff / np.timedelta64(1, 'D')).fillna(0).astype(int)

To run the function we simply pass in the values and assign the output to a new column in our original dataframe called days_since_previous_sku_order. Running the sample() function returns a sample set of rows, so we can check that the function has worked correctly.

df['days_since_previous_sku_order'] = get_days_since_date(df, 'previous_sku_order_date', 'order_date')
df[['order_id','sku','previous_sku_order_date','days_since_previous_sku_order']].sample(5)

Calculate aggregate statistics on product replenishment

Since certain items are rarely, if ever, replenished, we also need to calculate some product-level data to identify replenishment rates across the product catalogue. We wouldn’t want to inadvertently contact a customer to tell them their TV is running out, when it’s a non-replenished item… Instead, we’d likely want to mark these as non-replenished items that we ignore from our campaigns.

Arguably, there’s also a difference between something that is replenished because it runs out (i.e., protein powder, coffee, or dog food) and something that is repurchased because it’s needed again for a completely different purpose (i.e., batteries for a new product). However, figuring this out is likely to be something that may require human input.

To calculate these data we’ll create a helper function called get_replenishments() to which we’ll pass our dataframe of transactional items. We’ll first calculate the number of times each SKU has been purchased by each customer and store it in the dataframe. Then, we’ll count the number of times it’s been purchased once only.

Next, we’ll create an aggregation containing the SKUs from the dataframe and calculate a range of metrics for each one. Then we’ll join the selected fields from our original transaction items dataframe back to the SKUs dataframe and return the output.

def get_replenishments(df):
    
    df['times_purchased'] = df.groupby(['sku', 'customer_id'])['order_id'].transform('count')
    df['times_purchased'] = df['times_purchased'].fillna(0).astype(int)
    
    df['purchased_once'] = df[df['times_purchased']==1].groupby('sku')['order_id'].transform('count')
    df['purchased_once'] = df['purchased_once'].fillna(0).astype(int)
    
    df_skus = df.groupby('sku').agg(
        total_customers=('customer_id', 'nunique'),
        total_orders=('order_id', 'nunique'),
        total_quantity=('quantity', 'sum'),
        avg_quantity=('quantity', 'mean'),
        avg_price=('unit_price', 'mean'),
        avg_replenishment_days=('days_since_previous_sku_order', 'mean'),
        std_replenishment_days=('days_since_previous_sku_order', 'std'),
        max_replenishment_days=('days_since_previous_sku_order', 'max'),
    )
    
    df_skus['orders_per_customer'] = df_skus['total_orders'] / df_skus['total_customers']
    df_skus['items_per_customer'] = df_skus['total_quantity'] / df_skus['total_customers']

    df_subset = df[['sku', 'purchased_once']].sort_values(by='purchased_once', ascending=False)
    df_subset = df_subset.drop_duplicates('sku', keep='first')
    df_skus = df_skus.merge(df_subset, on='sku', how='left')
    
    df_skus['total_replenishments'] = df_skus['total_orders'] - df_skus['purchased_once']
    df_skus['replenishment_rate'] = df_skus['total_replenishments'] / df_skus['total_orders']
    
    return df_skus

Running the function and sorting the data in descending order of total_replenishments shows us a range of products that are regularly purchased repeatedly by customers. While this is useful for powering our replenishment campaign emails, it’s potentially also worth looking at how we might be able to acquire customers more likely to purchase these SKUs, since they’re likely to have good customer lifetime values with this level of repeat purchasing.

df_skus = get_replenishments(df)
df_skus.sort_values(by='total_replenishments', ascending=False).head(20)

Examine correlations with replenishment time

Finally, just to sanity check our data and see if the assumptions we made earlier are correct, we can run a Pearson correlation on the dataframe of SKU-level data. Before doing this, it’s worth zero-filling any inf values. You can also plot the outputs to see how the data are correlated.

df_skus = df_skus.replace([np.inf, -np.inf], np.nan).fillna(value=0)

df_skus[df_skus.columns[1:]].corr()['avg_replenishment_days'][:].sort_values(ascending=False)

avg_replenishment_days    1.000000
std_replenishment_days    0.919889
max_replenishment_days    0.800298
total_customers           0.597277
total_orders              0.587021
purchased_once            0.563462
total_replenishments      0.560351
total_quantity            0.385017
replenishment_rate        0.193493
items_per_customer       -0.009193
avg_quantity             -0.012034
orders_per_customer      -0.020139
avg_price                -0.022023
Name: avg_replenishment_days, dtype: float64

sns.heatmap(df_skus.corr(), annot=True, cmap="Blues")

<AxesSubplot:>

We now have a SKU-level dataset that identifies the products that are regularly replenished, and those that are not, so we can select or exclude them from our email marketing campaigns. Some retailers do use a metric such as avg_replenishment_days to estimate when a customer is likely to purchase a product again. However, the most accurate solution is likely to come from the creation of a product replenishment campaign model.

Replenishment campaign models typically use multiple linear regression and require a range of features to generate more accurate, personalised predictions. These features could include the SKU-level data above, plus the details on each individual customer-SKU relationship. With these data, they can then be trained to predict the number of days until each customer is likely to replenish their product.

Matt Clarke, Saturday, June 05, 2021