Karthic Subramanian

Sr Product Marketing Manager

Katya Kamenieva

Product Manager

One of the key aspects of being a successful merchant is knowing your market. Understanding your top-selling products, trending SKUs, and top customer locations helps you plan, market, and sell effectively. As a marketplace, providing this visibility and insights for your sellers is crucial. For example, [SHOPLINE](https://www.mongodb.com/customers/shopline) has helped over 350,000 merchants reach more than 680 million customers via e-commerce, social commerce, and offline point-of-sale (POS) transactions. With key features such as inventory and sales management tools, data analytics, etc. merchants have everything they need to build a successful online store.

In this article, we are going to look at how a single query on MongoDB can power a real-time view of top selling products, and a deep-dive into the top selling regions.

![Real-time view of top 5 products sold](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/blog1_image1_bd58926b65.jpg)

## Status Quo: stale data

In the relational world, such a dashboard would require multiple joins across at least four distinct tables: seller details, product details, channel details, and transaction details. 

![Entity relationship diagram showing the 4 different tables to be joined for a merchant dashboard view](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/blog2_image2_e89add758a.jpg)

This increases complexity, data latency, and costs for providing insights on real-time, operational data. Often, organizations pre-compute these tables with up to a 24-hour lag to ensure a better user experience. 

## How can MongoDB help deliver real-time insights?

With MongoDB, using the Query API, we could deliver such dashboards in near real-time, working directly on operational data. The required information for each sales transaction can be stored in a single collection. 

Each document would look as follows:

```
{
  "_id": { "$oid": "5bd761dcae323e45a93ccfed" },
  "saleDate": { "$date": {...} },
  "items": [
    { "name": "binder",
      "tags": [
        "school",
        "general"],
      "price": { "$numberDecimal": "13.44" },
      "quantity": 8
    },
    { "name": "binder",
      "tags": [
        "general",
        "organization"
      ],
      "price": { "$numberDecimal": "16.66" },
      "quantity": 10
    }
  ],
  "storeLocation": "London",
  "customer": {
    "gender": "M",
    "age": 44,
    "email": "owtar@pu.cd",
    "satisfaction": 2
  },
  "couponUsed": false,
  "purchaseMethod": "In store"
}
```
This specific document is from the *“sales”* collection within the *“sample_supplies”* database, available as sample data when you create an Atlas Cluster. [Start free on Atlas](https://www.mongodb.com/cloud/atlas/register) and try out this exercise yourself. MongoDB allows for flexible schema and versioning which makes updating this document with a “seller” field, similar to the customer field, and managing it in your application, very simple. From a data modeling perspective, the [polymorphic pattern](https://www.mongodb.com/developer/products/mongodb/polymorphic-pattern/) is ideal for our current use case.

## Desired output

In order to build a dashboard showcasing the top five products sold over a specific period, we would want to transform the documents into the following sorted array: 

```
[
 {
   "total_volume": 1897,
   "item": "envelopes"
 },
 {
   "total_volume": 1844,
   "item": "binder"
 },
 {
   "total_volume": 1788,
   "item": "notepad"
 },
 {
   "total_volume": 1018,
   "item": "pens"
 },
 {
   "total_volume": 830,
   "item": "printer paper"
 }
]
```
With just the “_id” and “total_volume” fields, we can build a chart of the top five products. If we wanted to deliver an improved seller experience, we could build a deep-dive chart with the same single query that provides the top five locations and the quantity sold for each. 

The output for each item would look like this:

```
{
  "_id": "binder",
  "totalQuantity": 100,
  "topFiveRegionsByQuantity": {
    "Seattle": 41,
    "Denver": 26,
    "New York": 14,
    "Austin": 10,
    "London": 9
  }
}
```
With the Query API, this transformation can be done in real-time in the database with a single query. In this example, we go a bit further to build another transformation on top which can improve user experience. In fact, on our Atlas data platform, this becomes significantly easier when you leverage [Atlas Charts](https://www.mongodb.com/products/charts).

## Getting started

1. [Set up your Atlas Cluster](https://www.mongodb.com/developer/products/atlas/getting-started-atlas-mongodb-query-language-mql/) and load sample data “sample_supplies.”
2. Connect to your Atlas cluster [through Compass](https://www.mongodb.com/docs/compass/current/) or open the [Data Explorer tab](https://www.mongodb.com/docs/atlas/atlas-ui/agg-pipeline/#std-label-atlas-ui-agg-pipeline) on Atlas.

In this example, we can use the [aggregation builder in Compass](https://www.mongodb.com/docs/compass/current/aggregation-pipeline-builder/) to build the following pipeline.

(Tip: Click “Create new pipeline from text” to copy the code below and easily play with the pipeline.) 

## Aggregations with the query API

Keep scrolling to see the following code examples in [Python](#python), [Java](#java), and [JavaScript](#javascript). 

```
[{
 $match: {
  saleDate: {
   $gte: ISODate('2017-12-25T05:00:00.000Z'),
   $lt: ISODate('2017-12-30T05:00:00.000Z')
  }
 }
}, {
 $unwind: {
  path: '$items'
 }
}, {
 $group: {
  _id: {
   item: '$items.name',
   region: '$storeLocation'
  },
  quantity: {
   $sum: '$items.quantity'
  }
 }
}, {
 $addFields: {
  '_id.quantity': '$quantity'
 }
}, {
 $replaceRoot: {
  newRoot: '$_id'
 }
}, {
 $group: {
  _id: '$item',
  totalQuantity: {
   $sum: '$quantity'
  },
  topFiveRegionsByQuantity: {
   $topN: {
    output: {
     k: '$region',
     v: '$quantity'
    },
    sortBy: {
     quantity: -1
    },
    n: 5
   }
  }
 }
}, {
 $sort: {
  totalQuantity: -1
 }
}, {
    $limit: 5
}, {
 $set: {
  topFiveRegionsByQuantity: {
   $arrayToObject: '$topFiveRegionsByQuantity'
  }
 }
}]
```
This short but powerful pipeline processes our data through the following stages: 

* First, it filters our data to the specific subset we need. In this case, sale transactions are from the specified dates. It’s worth noting here that you can parametrize inputs to the [$match](https://www.mongodb.com/docs/manual/reference/operator/aggregation/match/) stage to dynamically filter based on user choices.

Note: Beginning our pipeline with this filter stage significantly improves processing times. With the right index, this entire operation can be extremely fast and reduce the number of documents to be processed in subsequent stages.

* To fully leverage the polymorphic pattern and the document model, we store items bought in each order as an embedded array. The second stage [unwinds](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unwind/) this so our pipeline can look into each array. We then [group](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group/) the unwound documents by item and region and use [$sum](https://www.mongodb.com/docs/manual/reference/operator/aggregation/sum/) to calculate the total quantity sold. 
* Ideally, at this stage we would want our documents to have three data points: the item, the region, and the quantity sold. However, at the end of the previous stage, the item and region are in an embedded object, while quantity is a separate field. We use [$addFields](https://www.mongodb.com/docs/manual/reference/operator/aggregation/addFields/) to move quantity within the embedded object, and then use [$replaceRoot](https://www.mongodb.com/docs/manual/reference/operator/aggregation/replaceRoot/) to use this embedded _id document as the source document for further stages. This quick maneuver gives us the transformed data we need as a single document. 
* Next, we [group](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group/) the items as per the view we want on our dashboard. In this example, we want the total volume of each product sold, and to make our dashboard more insightful, we could also get the top five regions for each of these products. We use $group for this with two operators within it: 
    * [$sum](https://www.mongodb.com/docs/manual/reference/operator/aggregation/sum/) to calculate the total quantity sold.
    * [$topN](https://www.mongodb.com/docs/manual/reference/operator/aggregation/topN/) to create a new array of the top five regions for each product and the quantity sold at each location.
* Now that we have the data transformed the way we want, we use a [$sort](https://www.mongodb.com/docs/manual/reference/operator/aggregation/sort/) and [$limit](https://www.mongodb.com/docs/manual/reference/operator/aggregation/limit/) to find the top five items. 
* Finally, we use [$set](https://www.mongodb.com/docs/manual/reference/operator/aggregation/set/) to convert the array of the top five regions per item to an embedded document with the format {region: quantity}, making it easier to work with objects in code. This is an optional step.

Note: The $topN operator was introduced in MongoDB 5.2. To test this pipeline on Atlas, you would require an M10 cluster. By downloading MongoDB community version, you can test through Compass on your local machine.

## What would you build?

While adding visibility on the top five products and the top-selling regions is one part of the dashboard, by leveraging MongoDB and the Query API, we deliver near real-time visibility into live operational data. 

In this article, we saw how to build a single query which can power multiple charts on a seller dashboard. What would you build into your dashboard views? Join our vibrant [community forums](https://www.mongodb.com/community/forums/c/data/6), to discuss more. 

*For reference, here’s what the code blocks look like in other languages.*

*<a name="python">Python*

```python
# Import the necessary packages
from pymongo import MongoClient
from bson.son import SON

# Connect to the MongoDB server
client = MongoClient(URI)

# Get a reference to the sample_supplies collection
db = client.<database_name>
supplies = db.sample_supplies

# Build the pipeline stages
match_stage = {
    "$match": {
        "saleDate": {
            "$gte": "ISODate('2017-12-25T05:00:00.000Z')",
            "$lt": "ISODate('2017-12-30T05:00:00.000Z')"
        }
    }
}

unwind_stage = {
    "$unwind": {
        "path": "$items"
    }
}

group_stage = {
    "$group": {
        "_id": {
            "item": "$items.name",
            "region": "$storeLocation"
        },
        "quantity": {
            "$sum": "$items.quantity"
        }
    }
}

addfields_stage = {
 $addFields: {
  '_id.quantity': '$quantity'
 }
}

replaceRoot_stage = {
 $replaceRoot: {
  newRoot: '$_id'
 }
}

group2_stage = {
 $group: {
  _id: '$item',
  totalQuantity: {
   $sum: '$quantity'
  },
  topFiveRegionsByQuantity: {
   $topN: {
    output: {
     k: '$region',
     v: '$quantity'
    },
    sortBy: {
     quantity: -1
    },
    n: 5
   }
  }
 }
}

sort_stage = {
 $sort: {
  totalQuantity: -1
 }
}

limit_stage = {
    $limit: 5
}

set_stage = {
 $set: {
  topFiveRegionsByQuantity: {
   $arrayToObject: '$topFiveRegionsByQuantity'
  }
 }
}


pipeline = [match_stage, unwind_stage, group_stage, 
                     addfields_stage, replaceroot_stage, group2_stage,
                     sort_stage, limit_stage, set_stage]

# Execute the aggregation pipeline
results = supplies.aggregate(pipeline)
```

*<a name="java">Java*
```java
import com.mongodb.client.MongoCollection;
import com.mongodb.client.model.Aggregates;
import org.bson.Document;

import java.util.Arrays;

// Connect to MongoDB and get the collection
MongoClient mongoClient = new MongoClient(URI);
MongoDatabase database = mongoClient.getDatabase(<database_name>);
MongoCollection<Document> collection = database.getCollection("sample_supplies");

// Create the pipeline stages
Bson matchStage = Aggregates.match(Filters.and(
        Filters.gte("saleDate", new Date("2017-12-25T05:00:00.000Z")),
        Filters.lt("saleDate", new Date("2017-12-30T05:00:00.000Z"))
));

Bson unwindStage = Aggregates.unwind("$items");

Bson groupStage = Aggregates.group("$items.name",
        Accumulators.sum("quantity", "$items.quantity")
);

Bson addFieldsStage = Aggregates.addFields(new Field("_id.quantity", "$quantity"));

Bson replaceRootStage = Aggregates.replaceRoot("_id");

Bson group2Stage = Aggregates.group("$item",
        Accumulators.sum("totalQuantity", "$quantity"),
        Accumulators.top("topFiveRegionsByQuantity", 5, new TopOptions()
                .output(new Document("k", "$region").append("v", "$quantity"))
                .sortBy(new Document("quantity", -1))
        )
);

Bson sortStage = Aggregates.sort(new Document("totalQuantity", -1));

Bson limitStage = Aggregates.limit(5);

Bson setStage = Aggregates.set("topFiveRegionsByQuantity", new Document("$arrayToObject", "$topFiveRegionsByQuantity"));

// Execute the pipeline
List<Document> results = collection.aggregate(Arrays.asList(matchStage, unwindStage, groupStage, addFieldsStage, replaceRootStage, group2Stage, sortStage, limitStage, setStage)).into(new ArrayList<>());
```

*<a name="javascript">JavaScript*

```javascript
const MongoClient = require('mongodb').MongoClient;
const assert = require('assert');

// Connection URL
const url = 'URI';

// Database Name
const db = 'database_name';

// Use connect method to connect to the server
MongoClient.connect(url, function(err, client) {
  assert.equal(null, err);
  console.log("Connected successfully to server");

  const db = client.db(dbName);

  // Create the pipeline stages
  const matchStage = {
    $match: {
      saleDate: {
        $gte: new Date('2017-12-25T05:00:00.000Z'),
        $lt: new Date('2017-12-30T05:00:00.000Z')
      }
    }
  };

const unwindStage = {
  $unwind: {
    path: '$items'
  }
};

  const groupStage = {
    $group: {
      _id: {
        item: '$items.name',
        region: '$storeLocation'
      },
      quantity: {
        $sum: '$items.quantity'
      }
    }
  };

const addFieldsStage = {
  $addFields: {
    '_id.quantity': '$quantity'
  }
};

const replaceRootStage = {
  $replaceRoot: {
    newRoot: '$_id'
  }
};

const groupStage = {
  $group: {
    _id: '$item',
    totalQuantity: {
      $sum: '$quantity'
    },
    topFiveRegionsByQuantity: {
      $topN: {
        output: {
          k: '$region',
          v: '$quantity'
        },
        sortBy: {
          quantity: -1
        },
        n: 5
      }
    }
  }
};

const sortStage = {
  $sort: {
    totalQuantity: -1
  }
};

const limitStage = {
  $limit: 5
};

const setStage = {
  $set: {
    topFiveRegionsByQuantity: {
      $arrayToObject: '$topFiveRegionsByQuantity'
    }
  }
};

const pipeline = [matchStage, unwindStage, groupStage,
                        addFieldsStage, replaceRootStage, group2Stage, 
                        sortStage, limitStage, setStage]

  // Execute the pipeline
  db.collection('sample_supplies')
    .aggregate(pipeline)
    .toArray((err, results) => {
      assert.equal(null, err);
      console.log(results);

      client.close();
    });
});
```

In this article, we look at how a single query on MongoDB can power a real-time view of top-selling products, and deep-dive into the top-selling regions.

Karthic Subramanian

Content by Karthic Subramanian

Building a Real-Time, Dynamic Seller Dashboard on MongoDB