Why this project?

Christmas is a major time for sales around the world, and making the most of the holiday rush can be a bit challenging. In this task, I helped Apple analyse Christmas data to uncover insights into customer behaviors during the festive season. The dataset provided includes anonymized Christmas sales data across various product categories, customer demographics, and marketing campaigns.

These are the questions I was interested in answering

Here are my key takeaways

The Tableau dashboard

Two tabs answer the six questions. Slide between them — or tap either dashboard to zoom in.

Data Cleaning

Instead of looking at the data by calendar year, data was grouped into Christmas Seasons to get a better understanding. For example, November 2018, December 2018, and January 2019 are combined into Xmas 2018/2019, considering these three months as one season for retailers and shoppers alike.

• To make things more business-friendly, some fields were given new names. For example, Purchase Type (In-store, Online, Xmas Market) is now Shopping Channel.
• Since most of the data is from EU countries, I assumed Euro as the currency. In the real scenario, this needs to be clarified with the business.
• A Calendar Table was added to enhance time-related analysis.

Analysis

Customer Segment

The Children segment (ages 1-11) stood out, contributing nearly 40% of total Christmas sales over the years. Interestingly, there was no significant difference between male and female customers across different age groups.

In-store shopping, particularly for the Children segment (74% in stores), was a prevalent choice. This is understandable as kids often prefer the hands-on experience of selecting gifts. Cash was the preferred payment method for Kid purchases, while Teens (ages 12–17) and Adults (ages 18 and over) leaned towards credit card, and cash was their least preferred payment method.

Breaking it down to the product level, Iphone 13 and Iphone SE were the best sellers, each making up 7.9% to the overall sales. Interestingly, the top 5 performers were all targeted at the Children segment. However, despite their initial popularity, all of these products saw a decline in sales, ranging from -2% to -15% over the past four seasons. Zooming in on the Teenager segment, HomePod Mini emerged as the most cherished Christmas gifts. Meanwhile, in the world of Adults, the Apple Watch Series 5 stole the show, contributing the most to sales and enjoying a substantial sales growth of nearly 50% since the Christmas season of 2018/2019.

Geography

United States took the crown for the most Christmas sales over the four seasons, reaching €6.2 million. Canada, once the best in Xmas 2018/2019, faced a decline of -12% over the four seasons. The Netherlands showcased the most significant growth of 14%, climbing from the 7th to the 5th spot.

Drilling down to the Sales Leads, the three Stores in United States (Apple Stores, Best Buy USA & MacConnection) emerged as the top-selling Stores, each generating over €1.8 million in sales. In Ireland, Apple Store Ireland & Select (Compu B) emerged as the top-selling Stores, each generating over €0.9 million in sales.

Covid

The Christmas gift market felt the impact of Covid in 2020, experiencing a -3.2% sales decrease. A recovery followed in 2021/2022 with a +1.9% increase, but the sales had not yet reached pre-Covid levels.

Pricing

Despite higher prices, children's products lead in sales quantity, with over 12 thousand units sold for each product over the past four Christmas seasons. Among products for adults, unit price variation did not significantly affect the quantity sold (around 6–7 thousand units per product).

Customers tend to buy 1–5 products per transaction, but when making bulk purchases, they preferred 5, 10, 15, or even 30 items. This suggests an opportunity for special pricing on bulk purchases to encourage upselling.

Shopping Timing

December was the busiest month for Christmas gift shopping, with sales doubling compared to November and January. In-store shopping dominated December, accounting for 77% of sales, making it an excellent time for in-store promotions.

Shopping times varied across different channels. In-store shopping peaked on Monday, followed by Sunday. Sundays at 3 pm became the busiest time for Christmas markets. Online shopping provided flexibility, with peak times at Monday 8 am, Wednesday 11 pm, and Saturday 7 am. Weekday afternoons at 4 pm witnessed the highest online sales. While females preferred Sunday and Monday for Christmas shopping, males leaned towards Thursdays for their festive gift-buying spree.

Why this project?

I built the BI stack for a landscaping business I co-founded — an SQL script and Power BI report that replaced manual processes. It increased profit by 20%, identified high-value towns to focus marketing and service checkups on, and saved 1.5 hours of reporting time every week.

The questions I set out to answer

How I built it

A single pipeline moves the data from a spreadsheet all the way through to an interactive report.

Python script

My Python code converted individual Excel pages into individual CSV files, ready to import into the Postgres SQL server.

# execute py file in jupyter_venv
from pathlib import Path
import pandas as pd

# path to the workbook
wb = Path('Mallow - Liscarroll Landscaping/landscaping_schema.xlsx')

# check the file exists before proceeding
if not wb.exists():
    raise FileNotFoundError(wb)

# iterate through every sheet
for sheet in pd.ExcelFile(wb).sheet_names:
    df = pd.read_excel(wb, sheet_name=sheet, engine="openpyxl")
    csv_path = wb.parent / f"{sheet.lower()}.csv"
    df.to_csv(csv_path, index=False)
    print("Exported", csv_path)

SQL script

170-line SQL script used to build tables from the CSV files. Defines keys and data types, NULL / empty value handling. CTE for dashboard metrics.

Below is the base CTE on fact_work_order, normalises service_charge nulls to 0, rounds to two decimals, and outputs dashboard metrics: operating_cost, total_profit, profit_per_person, total_time, and profit_per_hour

ml_landscaping.sql

The Power BI dashboard

Two tabs make up the report. Slide between them — or tap either dashboard to zoom in.

DAX measures

18 DAX measures were created across both tabs of the dashboard. Here are three of the core ones.

Target Revenue

Revenue

Profit/hr

A walkthrough of the dashboard in action

Conclusion

Overall, this project demonstrates how a data pipeline built with Microsoft Excel → Python → SQL → Power BI can transform raw operational data into actionable insights that increase profit, identify high-value clients, and support smarter business decisions.