How to create an ecommerce trading calendar using Pandas

Learn how to use Pandas to create a dynamic ecommerce trading calendar of special trading events, such as Black Friday and Mother’s Day.

How to create an ecommerce trading calendar using Pandas
Ashkan Forouzani, Unsplash.
13 minutes to read

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()
date
0 2021-01-01
1 2021-01-02
2 2021-01-03
3 2021-01-04
4 2021-01-05

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.

Event Timing
Pay day The days following the last Friday of the month
January sales The days following the first day of January
Valentine's Day The days leading up to February 14th
Mother's Day The days leading up to fourth Sunday of Lent
Father's Day The days leading up to third Sunday of June
Black Friday The week of the fourth Friday in November
Cyber Monday The first Monday after Black Friday
Christmas The weeks before December 25th
Boxing Day sales December 26th

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
date event
0 2021-01-01 January sale
1 2021-01-29 January Pay Day
2 2021-02-11 Valentine's Day [last order date]
3 2021-02-14 Valentine's Day
4 2021-02-26 February Pay Day
5 2021-03-30 March Pay Day
6 2021-04-29 April Pay Day
7 2021-04-29 Mother's Day [last order date]
8 2021-05-09 Mother's Day
9 2021-05-28 May Pay Day
10 2021-05-28 Father's Day [last order date]
11 2021-06-20 Father's Day
12 2021-06-29 June Pay Day
13 2021-07-30 July Pay Day
14 2021-08-30 August Pay Day
15 2021-09-29 September Pay Day
16 2021-10-29 October Pay Day
17 2021-11-22 Black Friday [sale starts]
18 2021-11-26 Black Friday
19 2021-11-29 November Pay Day
20 2021-11-29 Cyber Monday
21 2021-12-23 Christmas Day [last order date]
22 2021-12-26 Boxing Day sale
23 2021-12-30 December Pay Day

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()
date event
0 2021-01-01 January sale
1 2021-01-02
2 2021-01-03
3 2021-01-04
4 2021-01-05

Matt Clarke, Wednesday, March 03, 2021

Matt Clarke Matt is a Digital Director who uses data science to help in his work. He has a Master's degree in Internet Retailing (plus two other Master's degrees in different fields) and specialises in the technical side of ecommerce and marketing.

Financial Forecasting in Python

Step into the role of CFO and learn how to advise a board of directors on key metrics while building a financial forecast.

Start course for FREE

Comments