Skip to content
Tutorials Center
My learning

Topics

Lesson 55 of 64 ยท Projects

Project: Sales Analysis

Clean order data, compute revenue by month and category, rank top products, measure growth and chart it with pandas, then write findings that match the numbers.

In this project you play the analyst. A shop hands you a table of order lines and asks the questions every business asks: how much did we sell, when, what sold best, and are we growing? You will go from raw data to a short written report, using only pandas and matplotlib in your browser.

The data is generated with a fixed random seed, so every run gives identical numbers, and it contains deliberate problems: repeated order lines, a missing product and impossible quantities. Real exports always have some of those. The project uses ideas from earlier lessons: data cleaning, merging, groupby, pivot tables, time series and plotting.

The plan

flowchart LR
    A["raw order lines<br/>(612 rows)"] --> B["clean<br/>duplicates, missing,<br/>bad quantities"]
    B --> C["enrich<br/>join product catalog,<br/>add revenue and month"]
    C --> D["analyse<br/>monthly, category,<br/>top products, growth"]
    D --> E["visualise and<br/>write findings"]

The project follows the same load, clean, transform, report shape you saw in the pipelines lesson.

Each step below is a complete, runnable program. Step 2 and step 3 begin with the same setup code as step 1 (the browser runs each example separately), so you can edit any one of them freely.

Step 1: build the data and clean it

The raw table has one row per order line: an order_id, a date, a product name in lower case, a quantity and a region. Prices live in a separate product catalog, so cleaning ends with a merge. Before changing anything, measure the problems. Then fix them one at a time and check the counts, so you always know what each rule removed.

Python Step 1: inspect and clean the order lines

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 cleaning rules are explicit and each is a decision you can defend. Repeated order ids are the same line exported twice, so keep one. A row without a product cannot be priced or grouped, so it is dropped (in a real project you would also report how many and ask the shop why). A quantity of -1 is probably a return or a typo; this project excludes it, and in your notes you would flag it as a question for the business. The merge uses validate="m:1", so pandas raises an error if the catalog ever contains the same product twice, which would silently multiply revenue.

Finally, the revenue column is quantity * price and month is a monthly Period, which groups cleanly and sorts in calendar order.

Step 2: answer the questions with tables

Now the analysis. We want four tables:

  1. Revenue by month, with the month-over-month growth rate in percent (pct_change).
  2. Revenue by category and month as a pivot table, so seasonal patterns by category are visible.
  3. Top products, with each product's share of total revenue and the running (cumulative) share.
  4. Revenue by region.

The cumulative share answers a common management question: how few products account for most of the money? This pattern is often called a Pareto analysis.

Python Step 2: monthly, category and product tables

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.

Read the tables carefully before you chart anything. Two things stand out:

  • Revenue per unit and units per product tell different stories. Notebooks are the best-selling product by units but the smallest by revenue, because each one costs only a few dollars. Ranking by units alone would send the shop's attention to the wrong place.
  • Month-over-month growth swings wildly. With only a few dozen orders per product per month, random variation is large. The jump of +108% in December and the fall of -56% in November are mostly noise, not trends. Keep that in mind when you write conclusions.

Step 3: charts and written findings

A chart should answer one question. Here we draw two: a bar chart of monthly revenue with a three-month rolling average to smooth out the noise, and stacked bars of revenue by category. Then the program writes the findings itself, using f-strings, so the sentences can never drift away from the numbers.

Python Step 3: two charts and a findings report

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.

Look at finding 6. The second half is about 11% below the first, but with monthly figures that swing this much, a gap of that size could easily be chance. A careful analyst writes "possibly lower, not conclusive" and asks for more data rather than announcing a decline.

Reading the result like an analyst

  • Concentration. A handful of products make up most of the revenue (the cumulative column crosses 80% after four products). That suggests protecting stock for the top sellers.
  • Price versus volume. The cheap items sell the most units but add little revenue. Whether to keep them depends on goals such as bringing customers into the shop, which this table cannot tell you.
  • Seasonality needs more history. One year of data cannot separate a seasonal pattern from chance. Ask for at least two years before forecasting.
  • State your cleaning rules. The report should list what was removed (12 repeated lines, 15 rows with no product, 10 impossible quantities) so readers can judge the numbers.

Ideas to extend the project

  • Add a discount column and compute net revenue; check how the ranking changes.
  • Compute each region's best-selling category with groupby and idxmax.
  • Use resample("W", on="order_date") to chart weekly revenue and compare its noise with the monthly chart.
  • Wrap the three steps into functions and a pipeline following the pipelines lesson, then add a test with assert_frame_equal from Testing and Validating DataFrames.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does the project merge the product catalog with validate='m:1'?

    Many order lines map to one catalog row (m:1). If the right side had duplicate keys, each order line would be repeated and revenue would double-count.
  2. 2. Notebooks sell the most units but rank low in revenue. What does that tell you?

    Revenue is quantity times price, so a low price keeps revenue small even for high volume. Choose the metric that fits the question.
  3. 3. Why is a month-over-month growth of +100% in one month not strong evidence of a trend here?

    Small samples fluctuate. Smooth with a rolling average and look at longer periods before calling something a trend.
  4. 4. What is the benefit of generating the findings text with f-strings from the data?

    Hard-coded numbers in prose go stale as soon as the data changes. Computing them in the same program keeps the report consistent.

Summary

  • Start by measuring data problems, then clean with explicit, countable rules and assertions.
  • Join reference data with merge(..., validate="m:1") to protect against duplicated keys.
  • Derive revenue and a monthly Period, then answer questions with groupby, pivot_table and pct_change.
  • Rank by the metric that matches the question, and look at cumulative shares to see concentration.
  • Smooth noisy series with a rolling average and avoid reading trends into small samples.
  • Generate the written findings from the data so numbers and text never disagree.

Next: Project: Cleaning a Messy Survey