Sales Analytics Dashboard

Use the aggregation framework to compute revenue, top products, and trends.

Aggregation Framework$lookup$group

Overview

The E-commerce Catalog & Orders project built `products` and `orders` collections to record individual sales. This project reuses that same data to answer a different kind of question — not "what did this one order contain," but "how is the business doing overall": total revenue, which products actually sell, and how sales trend day to day. Answering questions like that by pulling every document into application code and looping over it in JavaScript works for a handful of orders, but falls apart at real scale — MongoDB's aggregation framework exists to do that computation inside the database instead, as a declarative pipeline of stages that each transform the documents flowing through it.

By the end of this tutorial you will have written three separate aggregation pipelines against the `orders` collection — total revenue, a top-5-products report, and revenue broken down by day — each combining `$match` (filtering, the aggregation equivalent of `WHERE`), `$group` (aggregating, the equivalent of `GROUP BY`), `$lookup` (joining to `products`, the equivalent of a SQL `JOIN`), `$sort`, and `$limit` into a single pass over the data.

What You'll Build

A `$match` + `$group` pipeline computing total revenue across all non-cancelled orders.
A `$lookup` stage joining order line items back to full product details.
A top-5-products-by-revenue pipeline combining `$unwind`, `$group`, `$sort`, and `$limit`.
A revenue-by-day pipeline grouping on a date truncated from `createdAt`.
An understanding of how each aggregation stage transforms the documents flowing through the pipeline.

Prerequisites

Basic CRUD and the `orders`/`products` shape from the E-commerce Catalog & Orders project.
What a pipeline is — an array of stages, each one transforming the documents produced by the stage before it.
Basic query operators — `$gte`, `$lte`, `$eq`, used the same way inside `$match` as inside `find()`.
The general idea of grouping — collapsing many documents into one summary row per group key.
Array fields — since `$unwind` and `$lookup` both operate on array-valued fields.

Project Structure

No new collections are created for this project — it aggregates over the same `orders` and `products` collections from the E-commerce Catalog & Orders project. Each `orders` document's `items` array is what every pipeline below ultimately reads revenue from.

// The same order shape from the previous project — this is what every pipeline below reads from
{
  "_id": ObjectId("64f2b1a2c4d5e6f7a8b9c0f1"),
  "customerEmail": "neha.kapoor@example.com",
  "status": "shipped",
  "items": [
    { "product": ObjectId("64f2b1a2c4d5e6f7a8b9c0e1"), "quantity": 1, "priceAtPurchase": 79.99 },
    { "product": ObjectId("64f2b1a2c4d5e6f7a8b9c0e2"), "quantity": 2, "priceAtPurchase": 29.99 }
  ],
  "createdAt": ISODate("2026-08-01T12:00:00Z")
}

Step 1: Total Revenue With $match and $group

`$match` runs first and filters out `cancelled` orders, the same way a SQL `WHERE` clause would before any aggregate is computed — this matters because a cancelled order's line items should never count toward revenue. `$unwind` then splits each order document into one output document per line item, which is what lets `$group` sum `quantity * priceAtPurchase` across every item in every order, not just the first item of each order.

db.orders.aggregate([
  { $match: { status: { $ne: 'cancelled' } } },      // Exclude cancelled orders before computing anything
  { $unwind: '$items' },                             // One output document per line item, so $group sees every item
  {
    $group: {
      _id: null,                                     // null groups everything into a single summary row
      totalRevenue: {
        $sum: { $multiply: ['$items.quantity', '$items.priceAtPurchase'] } // Sum of quantity * price across every item
      },
      orderCount: { $addToSet: '$_id' }               // Track distinct order IDs seen, for a count in the next stage
    }
  },
  { $project: { _id: 0, totalRevenue: 1, orderCount: { $size: '$orderCount' } } } // $size turns the set into a count
]);

[
  { "totalRevenue": 139.97, "orderCount": 1 }
]

Step 2: Resolve Product Names With $lookup

`$lookup` is the aggregation framework's join stage: `from: 'products'` names the collection to join against, `localField`/`foreignField` say which fields must match (mirroring a SQL `JOIN ... ON`), and `as` names the new array field the matched product documents get attached under. Because `localField` here is `items.product` inside an unwound line item, each output document ends up with its one matching product attached as a single-element array.

db.orders.aggregate([
  { $unwind: '$items' },                  // One document per line item, same as Step 1
  {
    $lookup: {
      from: 'products',                   // Join against the products collection
      localField: 'items.product',        // This order's stored ObjectId reference
      foreignField: '_id',                // ...matched against the product's own _id
      as: 'productDetails'                 // Matched product document(s) attached here as an array
    }
  },
  { $unwind: '$productDetails' },          // Flatten the one-element array into a plain embedded object for $project
  {
    $project: {
      _id: 0,
      productName: '$productDetails.name', // Now available thanks to the $lookup join
      quantity: '$items.quantity',
      lineRevenue: { $multiply: ['$items.quantity', '$items.priceAtPurchase'] }
    }
  }
]);

productName	quantity	lineRevenue
Mechanical Keyboard	1	79.99
Wireless Mouse	2	59.98

Step 3: Top 5 Products by Revenue

This pipeline reuses the `$unwind`/`$lookup` shape from Step 2, then adds a second `$group` that collapses every line item down to one summary row per product, `$sort`s those summary rows by revenue descending, and finally `$limit`s the output to 5 — the same "aggregate, sort, cap" shape as a SQL `GROUP BY ... ORDER BY ... LIMIT 5`.

db.orders.aggregate([
  { $match: { status: { $ne: 'cancelled' } } },
  { $unwind: '$items' },
  {
    $group: {
      _id: '$items.product',                          // Group by product ObjectId — one row per distinct product
      unitsSold: { $sum: '$items.quantity' },
      revenue: { $sum: { $multiply: ['$items.quantity', '$items.priceAtPurchase'] } }
    }
  },
  { $sort: { revenue: -1 } },                          // Highest revenue first
  { $limit: 5 },                                       // Only the top 5 make it through
  {
    $lookup: { from: 'products', localField: '_id', foreignField: '_id', as: 'product' }
  },
  { $unwind: '$product' },
  { $project: { _id: 0, name: '$product.name', unitsSold: 1, revenue: 1 } }
]);

name	unitsSold	revenue
Mechanical Keyboard	1	79.99
Wireless Mouse	2	59.98

Step 4: Revenue by Day

`$dateTrunc` collapses each order's precise `createdAt` timestamp down to just its calendar day, which becomes the `$group` key — every order placed on the same day lands in the same summary row regardless of what time it happened. This is the pipeline that would feed a trend line chart on a real dashboard.

db.orders.aggregate([
  { $match: { status: { $ne: 'cancelled' } } },
  { $unwind: '$items' },
  {
    $group: {
      _id: { $dateTrunc: { date: '$createdAt', unit: 'day' } }, // Truncate to calendar day: the grouping key
      dailyRevenue: { $sum: { $multiply: ['$items.quantity', '$items.priceAtPurchase'] } }
    }
  },
  { $sort: { _id: 1 } },                                 // Chronological order, oldest day first
  { $project: { _id: 0, date: '$_id', dailyRevenue: 1 } }
]);

date	dailyRevenue
2026-08-01	139.97
2026-08-02	219.99

Complete Code

All three pipelines from the steps above, ready to run independently against the `orders` collection.

// Total revenue
db.orders.aggregate([
  { $match: { status: { $ne: 'cancelled' } } },
  { $unwind: '$items' },
  { $group: { _id: null, totalRevenue: { $sum: { $multiply: ['$items.quantity', '$items.priceAtPurchase'] } }, orderCount: { $addToSet: '$_id' } } },
  { $project: { _id: 0, totalRevenue: 1, orderCount: { $size: '$orderCount' } } }
]);

// Top 5 products by revenue
db.orders.aggregate([
  { $match: { status: { $ne: 'cancelled' } } },
  { $unwind: '$items' },
  { $group: { _id: '$items.product', unitsSold: { $sum: '$items.quantity' }, revenue: { $sum: { $multiply: ['$items.quantity', '$items.priceAtPurchase'] } } } },
  { $sort: { revenue: -1 } },
  { $limit: 5 },
  { $lookup: { from: 'products', localField: '_id', foreignField: '_id', as: 'product' } },
  { $unwind: '$product' },
  { $project: { _id: 0, name: '$product.name', unitsSold: 1, revenue: 1 } }
]);

// Revenue by day
db.orders.aggregate([
  { $match: { status: { $ne: 'cancelled' } } },
  { $unwind: '$items' },
  { $group: { _id: { $dateTrunc: { date: '$createdAt', unit: 'day' } }, dailyRevenue: { $sum: { $multiply: ['$items.quantity', '$items.priceAtPurchase'] } } } },
  { $sort: { _id: 1 } },
  { $project: { _id: 0, date: '$_id', dailyRevenue: 1 } }
]);

Sample Queries

Two more aggregations a real dashboard would run alongside the ones above: average order value, and revenue restricted to a specific date range.

// Average order value across all non-cancelled orders
db.orders.aggregate([
  { $match: { status: { $ne: 'cancelled' } } },
  { $unwind: '$items' },
  { $group: { _id: '$_id', orderTotal: { $sum: { $multiply: ['$items.quantity', '$items.priceAtPurchase'] } } } }, // Per-order total first
  { $group: { _id: null, avgOrderValue: { $avg: '$orderTotal' } } }  // Then average those totals across all orders
]);

[ { "_id": null, "avgOrderValue": 179.98 } ]

// Revenue for a specific date range only
db.orders.aggregate([
  {
    $match: {
      status: { $ne: 'cancelled' },
      createdAt: { $gte: ISODate('2026-08-01'), $lt: ISODate('2026-08-08') } // A one-week window
    }
  },
  { $unwind: '$items' },
  { $group: { _id: null, weeklyRevenue: { $sum: { $multiply: ['$items.quantity', '$items.priceAtPurchase'] } } } }
]);

[ { "_id": null, "weeklyRevenue": 359.96 } ]

Extend This Project

Add a `$facet` stage to compute total revenue and top products in a single aggregation call.
Add a `$bucket` stage to group orders into revenue ranges (e.g. under $50, $50-$150, over $150).
Build a month-over-month growth pipeline using `$dateTrunc` with `unit: 'month'` instead of `'day'`.
Add an index on `orders.createdAt` to speed up the date-range query above at scale.
Write the results of the daily-revenue pipeline into a materialized `daily_revenue_summary` collection nightly.

Summary

You built three aggregation pipelines that compute total revenue, a top-5-products report, and a daily revenue trend — all without pulling raw documents into application code and looping over them yourself. `$match` filtered early (the same instinct as a SQL `WHERE` before a `GROUP BY`), `$unwind` turned array line items into individually groupable documents, `$group` did the actual aggregating, and `$lookup` joined back to `products` for human-readable names — the same five stages combine into most aggregation pipelines you will write against a real MongoDB dataset.