How to create an ecommerce trading calendar using Pandas

In both B2C and B2B ecommerce, special trading periods such as Christmas, Mothers’ Day, and Valentines’ Day can often greatly contribute to sales. Indeed, the introduction of Black Friday sales to the UK’s ecommerce trading calendar has helped it one of the busiest days of the year for many internet retailers - and one of the busiest returns periods for operations.

If you’re analysing time series data in Pandas or engineering features for a machine learning model, or time series forecasting model it’s useful to be able to know if a given date coincides with one of these special trading periods. This can help you forecast sales and product demand to help ensure you’re ready to cope with a spike in traffic and sales, and demands on your customer service team.

However, there are a few things that can make it difficult to calculate these dates. Firstly, they’re often not fixed to a given date. Black Friday, for example, occurs on the fourth Friday of November, so its date changes each year. Others, such as Christmas, occur on a fixed date but demand for products comes several days or weeks before because customers need to have received the goods to give them as presents.

Creating an ecommerce trading calendar

Here I’ll explain how you can create a dynamic ecommerce trading calendar using Pandas that includes the dates that matter to your business sector. You’ll be able to use this every year to aid marketing, forecasting and modeling and it will work on historic dates as well as those in the future.

1. Create a test data set

First, we’ll create a calendrical data set. This consists of a single date column with one date for each day in the year. We can create this automatically in Pandas using pd.date_range('2021-01-01', periods=365, freq='D') and then add the series returned to our data frame.

import pandas as pd
period = pd.date_range('2021-01-01', periods=365, freq='D')
df = pd.DataFrame({ 'date': period })
df.head()

2. Identify your ecommerce trading events

Next, identify the key ecommerce trading events you want to add to your trading calendar and when the trading impact will come. For example, you’re unlikely to sell Valentine’s Day gifts on February 14th, but you will sell them in the week that precedes it. Similarly, due to retailers trying to steal their shoppers share-of-wallet Black Friday sales now encompass the whole week that includes Black Friday.

The key ones are below, but there are are also a raft of other days marketers have invented to help promote certain things, such as World Book Day, that you may wish to include on your calendar. In B2C markets, pay days are also worth targeting. In B2B markets, certain sectors also attempt to spend their remaining budget before the end of the financial year.

3. Create an AbstractHolidayCalendar

Next we’ll load up some Pandas time series offset and holiday tools and will use the AbstractHolidayCalendar class to create a custom ecommerce trading calendar called UKEcommerceTradingCalendar. We can calculate the dates of pay days (which generally fall on the last working day of the month) by using Holiday('January Pay Day', month=1, day=31, offset=BDay(-1) ),. This takes the last date of a given month and then finds the previous business day.

For the seasonal trading events, such as Black Friday, Mother’s Day, Father’s Day, Valentine’s Day, and the various sales you might run on an ecommerce website during the year we’ll use the date offset feature to calculate their date, as it often moves from year to year. Since some events, such as Mother’s Day, Father’s Day, Valentine’s Day, and Christmas, happen after the ecommerce event, we can use a similar technique to identify the last order date for each one.

from pandas.tseries.offsets import BDay

from pandas.tseries.holiday import (
    AbstractHolidayCalendar, Holiday, DateOffset, \
    SU, MO, TU, WE, TH, FR, SA, \
    next_monday, nearest_workday, sunday_to_monday,
    EasterMonday, GoodFriday, Easter
)

class UKEcommerceTradingCalendar(AbstractHolidayCalendar):
    rules = [
        
        # Pay days (based on fourth Friday of the month)
        Holiday('January Pay Day', month=1, day=31, offset=BDay(-1) ),
        Holiday('February Pay Day', month=2, day=28, offset=BDay(-1) ),
        Holiday('March Pay Day', month=3, day=31, offset=BDay(-1) ),
        Holiday('April Pay Day', month=4, day=30, offset=BDay(-1) ),
        Holiday('May Pay Day', month=5, day=31, offset=BDay(-1) ),
        Holiday('June Pay Day', month=6, day=30, offset=BDay(-1) ),
        Holiday('July Pay Day', month=7, day=31, offset=BDay(-1) ),
        Holiday('August Pay Day', month=8, day=31, offset=BDay(-1) ),
        Holiday('September Pay Day', month=9, day=30, offset=BDay(-1) ),
        Holiday('October Pay Day', month=10, day=31, offset=BDay(-1) ),
        Holiday('November Pay Day', month=11, day=30, offset=BDay(-1) ),
        Holiday('December Pay Day', month=12, day=31, offset=BDay(-1) ),
        
        # Seasonal trading events
        Holiday('January sale', month=1, day=1 ),
        Holiday('Valentine\'s Day [last order date]', month=2, day=14, offset=BDay(-2) ),        
        Holiday('Valentine\'s Day', month=2, day=14),
        Holiday('Mother\'s Day [last order date]', month=5, day=1, offset=BDay(-2) ),        
        Holiday('Mother\'s Day', month=5, day=1, offset=pd.DateOffset(weekday=SU(2))),        
        Holiday('Father\'s Day [last order date]', month=6, day=1, offset=BDay(-2) ),        
        Holiday('Father\'s Day', month=6, day=1, offset=pd.DateOffset(weekday=SU(3))),
        Holiday("Black Friday [sale starts]", month=11, day=1, offset=[pd.DateOffset(weekday=SA(4)), BDay(-5)]),
        Holiday('Black Friday', month=11, day=1, offset=pd.DateOffset(weekday=FR(4))),
        Holiday("Cyber Monday", month=11, day=1, offset=[pd.DateOffset(weekday=SA(4)), pd.DateOffset(2)]),
        Holiday('Christmas Day [last order date]', month=12, day=25, offset=BDay(-2) ),
        Holiday('Boxing Day sale', month=12, day=26 ),
    ]

Finally, we can use the UKEcommerceTradingCalendar class to create our custom trading calendar. For the start and end date, we’re passing in the start date and end date from our original dataframe. We get back a list of all of the key ecommerce trading events during the given calendar year, and can simply change the dates passed to calculate these for any period in the past or the future.

calendar = UKEcommerceTradingCalendar()
start = df.date.min()
end = df.date.max()

events = calendar.holidays(start=start, end=end, return_name=True)
events = events.reset_index(name='event').rename(columns={'index':'date'})
events

To create a time series dataframe comprising all the dates in a year, along with the name of their special trading event, we can join the two dataframes and fill any NaN values. The resulting data can then be encoded and used within time series models to aid forecasting and help you analyse demand and performance.

df = df.merge(events, on='date', how='left').fillna('')
df.head()

Matt Clarke, Wednesday, March 03, 2021