Group and Total

Add a match stage for orders in 2020

First, add a $match stage that matches orders placed in 2020:

pipeline.append({
    "$match": {
        "orderdate": {
            "$gte": datetime(2020, 1, 1, 0, 0, 0),
            "$lt": datetime(2021, 1, 1, 0, 0, 0)
        }
    }
})

Add a sort stage to sort by order date

Next, add a $sort stage to set an ascending sort on the orderdate field to surface the earliest 2020 purchase for each customer in the next stage:

pipeline.append({
    "$sort": {
        "orderdate": 1
    }
})

Add a group stage to group by email address

Add a $group stage to group orders by the value of the customer_id field. In this stage, add aggregation operations that create the following fields in the result documents:

• first_purchase_date: the date of the customer's first purchase

• total_value: the total value of all the customer's purchases

• total_orders: the total number of the customer's purchases

• orders: the list of all the customer's purchases, including the date and value of each purchase

pipeline.append({
    "$group": {
        "_id": "$customer_id",
        "first_purchase_date": {"$first": "$orderdate"},
        "total_value": {"$sum": "$value"},
        "total_orders": {"$sum": 1},
        "orders": {"$push": {"orderdate": "$orderdate", "value": "$value"}}
    }
})

Add a sort stage to sort by first order date

Next, add another $sort stage to set an ascending sort on the first_purchase_date field:

pipeline.append({
    "$sort": {
        "first_purchase_date": 1
    }
})

Add a set stage to display the email address

Add a $set stage to recreate the customer_id field from the values in the _id field that were set during the $group stage:

pipeline.append({
    "$set": {
        "customer_id": "$_id"
    }
})

Add an unset stage to remove unneeded fields

Finally, add an $unset stage. The $unset stage removes the _id field from the result documents:

pipeline.append({"$unset": ["_id"]})

Run the aggregation pipeline

Add the following code to the end of your application to perform the aggregation on the orders collection:

aggregation_result = orders_coll.aggregate(pipeline)

Finally, run the following command in your shell to start your application:

python3 agg_tutorial.py

Interpret results

The aggregation returns the following summary of customers' orders from 2020:

{
  'first_purchase_date': datetime.datetime(2020, 1, 1, 8, 25, 37),
  'total_value': 63,
  'total_orders': 1,
  'orders': [ { 'orderdate': datetime.datetime(2020, 1, 1, 8, 25, 37), 'value': 63 } ],
  'customer_id': 'oranieri@warmmail.com'
}
{
  'first_purchase_date': datetime.datetime(2020, 1, 13, 9, 32, 7),
  'total_value': 436,
  'total_orders': 4,
  'orders': [
    { 'orderdate': datetime.datetime(2020, 1, 13, 9, 32, 7), 'value': 99 },
    { 'orderdate': datetime.datetime(2020, 5, 30, 8, 35, 52), 'value': 231 },
    { 'orderdate': datetime.datetime(2020, 10, 3, 13, 49, 44), 'value': 102 },
    { 'orderdate': datetime.datetime(2020, 12, 26, 8, 55, 46), 'value': 4 }
  ],
  'customer_id': 'elise_smith@myemail.com'
}
{
  'first_purchase_date': datetime.datetime(2020, 8, 18, 23, 4, 48),
  'total_value': 191,
  'total_orders': 2,
  'orders': [
    { 'orderdate': datetime.datetime(2020, 8, 18, 23, 4, 48), 'value': 4 },
    { 'orderdate': datetime.datetime(2020, 11, 23, 22, 56, 53), 'value': 187 }
  ],
  'customer_id': 'tj@wheresmyemail.com'
}

The result documents contain details from all the orders from a given customer, grouped by the customer's email address.