26. End-to-End Project: Product Revenue and Customer Analytics

Build a product report from delivered orders. Filter eligible orders, unwind items, calculate line revenue, group by product, calculate units and revenue, join catalog metadata, sort the results, and project the API output.

The example uses historical items.price; it does not calculate revenue using the current catalog price. It reports item revenue before order-level discounts, shipping, taxes, or refunds. A production report must document these rules.

The average recorded unit price is the average of line-item unit prices. If a quantity-weighted average is required, divide total revenue by total units sold, using a consistent revenue basis.

Production reporting habit

Document inclusion rules, date range, currency, price basis, discount handling, and refund treatment alongside the pipeline.

Code Example

db.orders.aggregate([
  { $match: { status: "delivered" } },
  { $unwind: "$items" },
  { $set: { lineRevenue: { $multiply: ["$items.quantity", "$items.price"] } } },
  {
    $group: {
      _id: "$items.productId",
      productName: { $first: "$items.name" },
      unitsSold: { $sum: "$items.quantity" },
      lineCount: { $sum: 1 },
      totalItemRevenue: { $sum: "$lineRevenue" },
      averageRecordedUnitPrice: { $avg: "$items.price" }
    }
  },
  { $lookup: { from: "products", localField: "_id", foreignField: "_id", as: "catalogProduct" } },
  { $unwind: { path: "$catalogProduct", preserveNullAndEmptyArrays: true } },
  { $set: { currentCatalogPrice: "$catalogProduct.currentPrice", currentStock: "$catalogProduct.stock", catalogCategory: "$catalogProduct.category" } },
  { $sort: { totalItemRevenue: -1, _id: 1 } },
  { $project: { _id: 0, productId: "$_id", productName: 1, unitsSold: 1, lineCount: 1, totalItemRevenue: 1, averageRecordedUnitPrice: 1, currentCatalogPrice: 1, currentStock: 1, catalogCategory: 1 } }
]);

Key Takeaways

Filter eligible records before item processing.
Use historical prices for historical revenue.
Distinguish sales reports from accounting-grade net revenue.
Validate results manually.

Knowledge Check

Why calculate revenue using items.price instead of products.currentPrice?

26. End-to-End Project: Product Revenue and Customer Analytics

STAY UPDATED WITH BYTES!