Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 37 ยท Projects and Practice

Project: A Sales Analysis Report

Build a complete sales report with Polars. Generate data, clean it, join products and customers, compute KPIs, monthly growth, top products per region and customer segments.

In this project you will build the kind of report an analyst delivers every month: headline numbers, a monthly trend with growth rates, the best products in each region, and a customer segmentation. You will use almost everything from the previous chapters: expressions, joins, group_by, window functions with over, pivot, and a lazy final query.

We create the data ourselves so the project runs anywhere and gives the same numbers every time. Three tables: orders (the facts), products, and customers (the lookup tables).

Step 1: generate the data

A fixed random seed makes every run identical. We build plain Python lists first and create each DataFrame once (never append rows one at a time).

Python Create orders, products and customers

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The orders cover January through June 2025 (180 days). Every order references a customer and a product by id, which is how real relational data looks.

Step 2: join and compute revenue

We join the lookup tables onto orders and compute revenue after discount. Because both lookups have unique keys, we protect ourselves with validate="m:1": if a duplicate ever sneaks in, the join fails loudly instead of inflating revenue.

Python Join and derive revenue

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The row count must be unchanged after the joins (5,000) and no region is null: those two lines are the cheap sanity checks that catch most join bugs. dt.truncate("1mo") snaps each date to the first day of its month, a tidy key for monthly grouping.

Step 3: the monthly trend with growth

A window function inside a sorted frame gives the previous month's value on the same row, and growth is one expression.

Python Monthly revenue and growth

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The first month has no previous value, so its growth is null (not zero): there is nothing to compare with. Notice we sorted before using shift; window-style operations follow row order.

Step 4: top products in each region

"Rank within a group" is a window job. We rank products by revenue inside each region, and keep the top three.

Python Top 3 products per region

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

rank(...).over("region") ranks inside each region, and sum().over("region") gives the region total next to every row, so the share is plain arithmetic. No loops, no joins back.

Step 5: customer segments

Which customers matter most? We compute per-customer totals, then split them into segments with when/then on quantile cut-offs. This is a simplified version of RFM-style segmentation (recency, frequency, monetary value).

Python Customer segments by spend and recency

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The when/then chain is evaluated top to bottom, so the order encodes priority: someone who is both a big spender and recent becomes a "champion" before the "big spender" rule is reached. The last line is a useful sanity check: the segment sizes must add up to the number of customers.

Step 6: the final report as one lazy query

Finally, rewrite the revenue-by-category summary as a single lazy query. The optimizer sees the whole pipeline, pushes the date filter down, and reads only the needed columns.

Python A lazy report query with a plan

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In a real project the first line would be pl.scan_parquet("orders/*.parquet") and the report would stream from disk with only the used columns loaded.

Extensions to try

  • Add a weekly revenue series with group_by_dynamic and a 4-week rolling average.
  • Compute repeat rate: the share of customers with at least two orders per month.
  • Find product pairs bought by the same customer on the same day with a self-join.
  • Rebuild the KPI block with pl.SQLContext and check that it matches.
  • Export the monthly table with write_csv() and the segments with the Arrow IPC writer from the IO chapter.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why use validate='m:1' when joining the products table onto orders?

    A duplicate key in the lookup table would repeat order rows and silently overstate totals. validate catches it.
  2. 2. How do you get 'revenue as a share of its region total' next to each row?

    over('region') broadcasts the regional total back to every row, so dividing gives each row's share.
  3. 3. In the segment when/then chain, why is the order of the branches important?

    when/then branches are checked top to bottom and the first condition that is true decides the value.
  4. 4. Why is the growth of the first month null?

    shift(1) puts null in the first row; arithmetic with null stays null, which correctly says 'unknown'.

Summary

  • Build lookup tables and a fact table, join with validate, and check row counts and nulls after every join.
  • KPIs are a single select of aggregations; monthly trends use group_by, shift and cum_sum.
  • Per-group ranks and shares use over.
  • Segments are a prioritized when/then chain; sanity-check that segments add up.
  • The final report is a lazy query that the optimizer can push down.

Next: Project: Log File Analysis