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, $sorts those summary rows by revenue descending, and finally $limits 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.