The Data Journey: Messy to Meaningful — Phase 2: Merging, Features and Sales Trends

Objective

Now that the data is clean, the goal is to:

• Bring in customer information (country)
• Create meaningful features
• Explore patterns in the data
• Start understanding customer behaviour and sales trends

What we'll do

• Merge the orders with the country file
• Create new variables (such as Revenue)
• Extract time-based features
• Run basic aggregations
• Prepare the data for visualisation and machine learning

Load the dataset

We start from the file saved at the end of Phase 1.

import pandas as pd
pd.set_option("display.width", 200)          # show all columns side by side
pd.set_option("display.max_columns", None)

df = pd.read_csv("Online Retail Phase 1 Output.csv", parse_dates=["InvoiceDate"])
df.shape

(388357, 7)

parse_dates turns InvoiceDate back into a date as the file loads.

Load the country file

country = pd.read_csv("Customer_country.csv").dropna(subset=["CustomerID"])
country["CustomerID"] = country["CustomerID"].astype(int)

# How many customers appear with more than one country?
country[country["CustomerID"].duplicated(keep=False)]["CustomerID"].nunique()

8

A lookup table should have one row per key. Here, 8 customers appear with two countries. If we merged as-is, every order from those customers would be duplicated, silently inflating their revenue. We keep the first country listed for each customer (the one on their earliest order) and note the choice:

country = country.drop_duplicates(subset="CustomerID", keep="first")

Merge the data frames

Why merge?

• Real-world data rarely lives in one table.
• Transactions, customers and products are usually stored separately.
• Merging brings them together so we can answer questions such as who bought what, which customers are high value and which products drive revenue.

merged_df = df.merge(country, on="CustomerID", how="left", validate="m:1")
len(df), len(merged_df), int(merged_df["Country"].isna().sum())

(388357, 388357, 0)

validate="m:1" tells pandas to raise an error if the lookup ever has duplicate keys, so the problem above cannot slip through unnoticed. The row count is unchanged and every order has a country.

Why feature engineering?

Raw columns rarely give insight on their own. We create new features that answer business questions directly:

• Revenue = Quantity × UnitPrice
• Time features: year, month, day and hour

In Phase 4 we go further and build customer-level features (number of orders, average order value and so on) for modelling.

Create the Revenue feature

merged_df["Revenue"] = merged_df["Quantity"] * merged_df["UnitPrice"]

Extract time features

merged_df["Year"] = merged_df["InvoiceDate"].dt.year
merged_df["Month"] = merged_df["InvoiceDate"].dt.month
merged_df["YearMonth"] = merged_df["InvoiceDate"].dt.to_period("M")
merged_df["Day"] = merged_df["InvoiceDate"].dt.day
merged_df["Hour"] = merged_df["InvoiceDate"].dt.hour
merged_df.head(3)

   CustomerID  InvoiceNo StockCode                          Description  Quantity         InvoiceDate  UnitPrice         Country  Revenue  Year  Month YearMonth  Day  Hour
0       17850     536365    85123A   WHITE HANGING HEART T-LIGHT HOLDER         6 2010-12-01 08:26:00       2.55  United Kingdom    15.30  2010     12   2010-12    1     8
1       17850     536365     71053                  WHITE METAL LANTERN         6 2010-12-01 08:26:00       3.39  United Kingdom    20.34  2010     12   2010-12    1     8
2       17850     536365    84406B       CREAM CUPID HEARTS COAT HANGER         8 2010-12-01 08:26:00       2.75  United Kingdom    22.00  2010     12   2010-12    1     8

Why YearMonth? The data runs from 1 December 2010 to 9 December 2011, so it contains two Decembers. Grouping by Month alone would add December 2010 and December 2011 together. YearMonth keeps them apart.

Analyse the data: what sells, when and where

Monthly sales trend

monthly_sales = merged_df.groupby("YearMonth")["Revenue"].sum().round(2).reset_index()
monthly_sales

   YearMonth     Revenue
0    2010-12   556508.53
1    2011-01   457056.40
2    2011-02   439206.38
3    2011-03   575699.95
4    2011-04   448585.00
5    2011-05   651931.57
6    2011-06   605727.37
7    2011-07   585945.11
8    2011-08   626495.88
9    2011-09   928209.84
10   2011-10   967633.88
11   2011-11  1123586.80
12   2011-12   342885.66

Interpretation: Revenue is fairly flat from January to August 2011, at roughly £440,000–£650,000 a month. It then climbs steeply from September to a peak of £1.12 million in November, consistent with demand building towards Christmas. December 2011 looks low, but it is only a partial month: the data stops on 9 December. Never compare a partial period with full ones without saying so.

Top products

top_by_revenue = (merged_df.groupby("Description")["Revenue"].sum()
                  .round(2).sort_values(ascending=False).head(10))
top_by_units = (merged_df.groupby("Description")["Quantity"].sum()
                .sort_values(ascending=False).head(10))

Interpretation: The two rankings tell different stories. The best seller by revenue, the Regency cakestand, is not in the top 10 by units, while cheap items such as gliders and cake cases sell in huge volumes but earn less. Decide which question you are asking before choosing the metric. Thanks to the cancellation handling in Phase 1, neither list is distorted by the one-off orders of 80,995 and 74,215 units that were cancelled the same day.

Revenue by country

country_revenue = (merged_df.groupby("Country")["Revenue"].sum()
                   .round(2).sort_values(ascending=False))
country_revenue.head(10)

United Kingdom    6836275.34
Netherlands        283443.54
EIRE               247941.17
Germany            203859.98
France             182143.53
Australia          138289.19
Switzerland         51987.84
Spain               51021.71
Japan               37314.37
Belgium             37027.61

How many customers sit behind each country's revenue?

by_country = merged_df.groupby("Country").agg(customers=("CustomerID", "nunique"),
                                              revenue=("Revenue", "sum"))
by_country.sort_values("revenue", ascending=False).head(6)

                customers     revenue
Country
United Kingdom       3909  6836275.34
Netherlands             9   283443.54
EIRE                    3   247941.17
Germany                94   203859.98
France                 87   182143.53
Australia               9   138289.19

Interpretation: The United Kingdom accounts for 82% of revenue, which is unsurprising for a UK-based retailer. ("EIRE" is how the dataset records Ireland.) The rankings below the UK are driven by a handful of very large buyers. The Netherlands is second with only 9 customers, one of whom accounts for £278,742 of the country's £283,444. Ireland's revenue comes from just 3 customers. Germany and France have 10 to 30 times as many customers but less revenue. "Revenue by country" here really means "a few wholesale accounts", which matters for any market-level decision. We look at these large buyers again in Phase 3.

Save the output for Phase 3

merged_df.to_csv("Online Retail Phase 2 Output.csv", index=False)

Conclusion

In Phase 2 we joined in customer countries safely, created revenue and time features, and found what drives sales, when demand peaks and where revenue comes from. We also saw two traps that are easy to fall into: duplicate keys in a lookup table, and partial periods in a time series.

Coming soon: Phase 3

In Phase 3 we turn these insights into visuals, using charts to tell a clear story that makes the data easy to understand and act on.

Next: Phase 3: Data Visualisation (coming soon)

All code in this article was tested with pandas 3.0.5 on the Phase 1 output of the UCI Online Retail dataset.

Written in conjunction with Sankhya Analytics.