Seasonal Analytics: Using SQL to Analyse Christmas Sales and Returns

Every retail dataset tells two stories about Christmas. The first is the rush: bigger baskets, delivery cut-offs, discount codes doing their work. The second arrives in January, when part of that revenue comes back as refunds, exchanges and "not quite what I ordered" cases. Both stories sit in the same database, and SQL is usually the quickest way to read them side by side.

What follows is a practical approach to festive reporting: defining a Christmas window you can compare honestly, writing year-on-year queries that survive a change of calendar, and getting to grips with returns before they quietly distort your margin numbers.

Check the grain of your tables first

Most sales schemas look roughly like this: an orders table with one row per order, an order_items table with one row per product line, a returns table with one row per returned item, and a products table holding category and cost. Before writing anything clever, confirm that grain. Joining orders to order_items and then summing the order total will multiply your revenue by the number of lines on each order — a mistake that tends to surface in December and gets blamed on the data warehouse.

Two other checks are worth doing early: whether cancelled and test orders are flagged rather than deleted, and whether timestamps are stored in UTC. An order placed at 23:40 on Christmas Eve in London belongs to 24 December locally, even if your database disagrees.

Define the Christmas window before you query anything

Comparing "December" with "December" sounds fair until you remember that the two months rarely have the same weekday pattern, and that Black Friday keeps shifting the start of the festive trading period. Pick a window and state it explicitly — 1 November to 31 December, for instance — and filter with a half-open range: greater than or equal to 1 November, strictly less than 1 January. That way you never accidentally pull in New Year's Day sales.

Keep the window definition in a small reference table or a CTE rather than retyping dates into every query. Next December you change one row instead of fourteen reports.

Year-on-year comparisons without the spreadsheet shuffle

Dates do not line up neatly between years, so comparing 7 December with 7 December can mean comparing a Saturday with a Wednesday. Two approaches work well: align by season day (day 1 to day 61 of the window, whatever the actual date) or align by ISO week number. Season day is usually the more honest of the two for retail, because it keeps the shape of the peak visible.

Steps that hold up in practice:

1. Build a CTE that maps every order date to a season day and a season year.
2. Aggregate gross revenue, net revenue and units by season day.
3. Use LAG(revenue) OVER (PARTITION BY season_day ORDER BY season_year) to bring last year's figure onto the same row.
4. Divide the difference by a NULLIF on last year's value so you do not trip over a division by zero.

Add a second layer for products. A category-level year-on-year figure tells you what moved; a product-level one tells you why. Run both, then read them together.

Treat returns as a timing problem, not just a rate

A return recorded in January often belongs to an order placed in November. If you calculate return rate by dividing January's refunds by January's sales, you get a number that is technically correct and completely useless for judging how a product performed. Build two views instead:

• Cash view — returns grouped by the return date. Finance and cash-flow reporting want this, because it shows when the money left.
• Cohort view — returns grouped by the original order date. Merchandising wants this, because it answers the real question: did this product come back?

From the cohort view you can also measure return lag: the number of days between order and refund, bucketed into 0–7, 8–14, 15–30 and 31+. Those buckets tell you whether returns are driven by delivery problems, by sizing, or simply by people changing their minds after the decorations came down.

Where returns concentrate, and where they only look like they do

Cross-tabulating return reason codes against category is one of the highest-value festive queries you can write. Size-related reasons point at product descriptions and fit guides; damage points at packaging or couriers; late delivery points at the promise you made at checkout.

Watch the sample sizes. A category with a dozen orders will produce dramatic-looking percentages that mean nothing at all. Filter out thin cohorts, or show order counts alongside every rate. And remember that gift buying distorts behaviour: people order two sizes intending to keep one, which is not a product fault.

A short checklist for festive queries

• Pin the seasonal window and reuse it everywhere.
• Exclude cancelled, test and internal orders.
• Report gross and net revenue side by side.
• Attribute returns to order date for product analysis, and to return date for cash analysis.
• Show counts next to every rate you publish.
• Compare at least three festive periods before treating a pattern as real.

Make it repeatable before next December

Save the window definition, the season-day mapping and the two returns views as database views or scheduled models. Write the assumptions in a comment above each one, including the time zone you are working in and whether you are counting units or orders. When someone asks in February why your numbers differ from the finance report, that comment is the answer.

Do this well and seasonal analysis stops being a January scramble. You end up with a small, dependable set of queries that show what sold, what came back, and what to change in the next buying plan — long before the following festive window opens.

Photo: webandi / Pixabay