Skip to content
Tutorials Center
My learning

Topics

Lesson 64 of 70 ยท Projects

Project: Sales Analytics Pipelines

Turn raw orders into business reports with MongoDB aggregation: revenue by month and category, top customers, cohorts, $lookup joins and a $facet dashboard.

Collecting orders is the easy half of a shop. The valuable half is answering questions about them: how much did we earn each month, which categories sell, who are our best customers, do customers who joined in January behave differently from those who joined in March? In a relational database you would write a handful of GROUP BY queries. In MongoDB you write aggregation pipelines, and this project builds a small set of them end to end.

The data lives in two collections, orders and customers, and every pipeline runs in the tutorial's practice engine (a JavaScript stand-in, not MongoDB; see The Practice Engine Used in This Tutorial). The pipeline documents themselves are exactly what you would send to a real server with db.orders.aggregate([...]). You should already be comfortable with Grouping with $group, Working with Arrays in Pipelines and Joining with $lookup.

The reports we will build

Revenue by month Group by a formatted date.
By category Unwind line items first.
Top customers Group, sort, limit, then join.
Cohorts Group by signup month.
1 / 2

The data

An order embeds its line items, because items are only ever read with their order. Each item carries the category and unit price at the time of purchase, a deliberate copy: prices change, but a past order must keep the price the customer paid. The order's total is stored too, computed when the order is written. Customers live in their own collection, with the month they signed up.

The helper below builds the orders so you can read the data as a short table of purchases. Everything is deterministic: dates are fixed, and nothing is random.

Step 1: revenue by month and by category

A monthly revenue report has three parts. First $match to keep only paid orders (refunded and cancelled orders must not count as revenue). Then $group on a month key made with $dateToString, summing the order totals. Then $sort on the key, because $group returns groups in no promised order.

Category revenue needs one extra step. A category lives on the line items, and an order can mix categories, so you $unwind the items array first. After the unwind there is one document per line item, and you can group on items.category and sum qty * price computed with $multiply. Notice that the $match still comes first: the unwind would multiply the number of documents the rest of the pipeline has to process, so filter before you expand.

JS Revenue by month and by category, plus top customers

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

No output.

In the monthly report December has two orders, January three (the refunded order is excluded) and February three (the cancelled one is excluded). Compare the totals with the order list to check that the pipeline agrees with arithmetic you can do by hand: that habit catches mistakes in $match early.

The top-customers pipeline shows the join last rule. It groups the ten-or-so orders into one row per customer, sorts, cuts to three rows, and only then runs $lookup. Doing the lookup first would join every order to its customer before grouping, which is far more work on a large collection. In real MongoDB localField: "_id" against the customers._id index makes each lookup an index probe.

In real MongoDB, $dateToString also takes a timezone. The stand-in always formats in UTC. For month boundaries that matter to a business, pass the shop's timezone in production.

Step 2: cohorts

A cohort is a group of customers who share a starting point, usually the month they signed up. Cohort reports answer "do customers from different months spend differently?". The signup month is in customers, the money is in orders, so the pipeline joins the two.

Starting from orders, $lookup fetches each order's customer into an array, $unwind turns that one-element array into a plain subdocument, and $group uses two keys at once: the cohort and the order month. To count distinct buyers rather than orders, $addToSet collects customer ids and a later $addFields with $size counts them.

Step 3: a dashboard with $facet

A dashboard needs several answers over the same slice of data: the headline numbers, the orders per status, the best-selling SKUs. Running three queries scans the data three times. $facet feeds one stream into several named sub-pipelines and returns a single document (see $facet, $bucket and Other Stages). Because the result is one document, use it for summaries, never for thousands of rows.

JS Cohorts and a $facet dashboard

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

No output.

Read the cohort output from top to bottom: the November cohort (Ana and Leo) bought in December, January and February, while the December cohort (Mia) bought once. A real cohort report would also divide by cohort size to get a retention rate, which is a good extension exercise: count customers per signupMonth with a second $group and compare.

Turning pipelines into a reporting layer

Pipelines are only code, so treat them like code. Wrap each report in a function that takes parameters (a date range, a status), keep them in one module, and test them against a small fixture like the ones above. For expensive reports there are two standard moves:

  • Index the $match. An index on { status: 1, createdAt: 1 } lets the first stage read only the orders it needs.
  • Precompute. End the pipeline with $merge into a summary collection (for example daily_revenue) on a schedule, and serve the dashboard from that small collection. $merge and $out cannot run in the practice engine, so this is real MongoDB only; see $facet, $bucket and Other Stages.
JavaScript
db.orders.aggregate([
  { $match: { status: "paid", createdAt: { $gte: ISODate("2026-02-01") } } },
  { $group: { _id: { $dateToString: { format: "%Y-%m-%d", date: "$createdAt" } }, revenue: { $sum: "$total" } } },
  { $merge: { into: "daily_revenue", on: "_id", whenMatched: "replace", whenNotMatched: "insert" } }
])

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does the category revenue pipeline use $unwind on items?

    An order can contain several categories. After $unwind there is one document per line item, so $group can sum per category.
  2. 2. Why does the top-customers pipeline run $lookup after $limit?

    Reduce the data first, then join. The lookup runs on the few rows that survived grouping and limiting.
  3. 3. Which stage lets one pipeline return a total, a status breakdown and a top-3 list in a single pass?

    $facet runs several named sub-pipelines over the same input and returns one document with an array for each.
  4. 4. Why is the order's price copied into each line item instead of read from a products collection?

    Historical records should be snapshots. Copying the price at purchase time keeps reports correct and avoids a join.

Summary

  • Revenue reports follow one recipe: $match early, $group on a key (a formatted date, a category, a customer), $sort the result.
  • Group-level data inside arrays needs $unwind first; filter before you unwind.
  • Join late: run $lookup on the small, grouped output, and index the foreign field in real MongoDB.
  • Cohorts group on two keys; $addToSet plus $size counts distinct buyers.
  • $facet assembles a dashboard from one pass; precompute heavy reports with $merge on a schedule.

Next, handle a never-ending stream of small records in Project: Event Logging with Bucketing and TTL.