calculating average waiting time elasticseach

Question

I Have E-commerce data which looks like this:

{
      "id": 20,
      "discount": "0.00",
      "items_price": "10.00",
      "shipping_price": "10.00",
      "payment_price": "0.00",
      "vat": "0.00",
      "total_price": "20.00",
      "shipping_method": "شحن داخل الرياض",
      "payment_method": "Apple Pay",
      "address_owner": "asdf",
      "address_details": null,
      "coupon_code": null,
      "email": null,
      "mobile": "",
      "username": "asdf",
      "last_status_id": 6,
      "shopper_id": 2,
      "address_id": 1,
      "coupon_id": null,
      "city_id": 1,
      "shipping_method_id": 1,
      "payment_method_id": 5,
      "store_id": 1,
      "created_at": "2021-04-30T19:07:03.000000Z",
      "updated_at": "2021-05-01T07:07:40.000000Z",
      "deleted_at": null,
      "city_name": "الرياض",
      "bankTransferUrl": "",
      "total": "20.00",
      "currentStatus": {
        "id": 6,
        "name": "جار التجهيز",
        "name_en": "preparing",
        "created_at": null,
        "updated_at": null,
        "theName": "جار التجهيز"
      },
      "city": {
        "id": 1,
        "name": "الرياض",
        "name_en": "Riyadh",
        "country_id": 1,
        "aramex": 0,
        "in_out": 0,
        "created_at": "2021-03-22T00:47:31.000000Z",
        "updated_at": "2021-03-22T00:47:31.000000Z"
      },
      "order_history": [
        {
          "id": 30,
          "notes": "",
          "order_id": 20,
          "status_id": 3,
          "created_at": "2021-04-30T19:07:03.000000Z",
          "updated_at": "2021-04-30T19:07:03.000000Z"
        },
        {
          "id": 31,
          "notes": "",
          "order_id": 20,
          "status_id": 6,
          "created_at": "2021-05-01T07:07:40.000000Z",
          "updated_at": "2021-05-01T07:07:40.000000Z"
        },
        {
          "id": 32,
          "notes": "",
          "order_id": 20,
          "status_id": 6,
          "created_at": "2021-05-01T07:16:06.000000Z",
          "updated_at": "2021-05-01T07:16:06.000000Z"
        },
        {
          "id": 33,
          "notes": "",
          "order_id": 20,
          "status_id": 6,
          "created_at": "2021-05-01T07:32:20.000000Z",
          "updated_at": "2021-05-01T07:32:20.000000Z",
          "status": {
            "id": 6,
            "name": "جار التجهيز",
            "name_en": "preparing",
            "created_at": null,
            "updated_at": null,
            "theName": "جار التجهيز"
          }
        }
      ]
    }

One thing I am trying to do is Calculating the average waiting time before the order is shipped.

the Order_history is the different statuses the order been to.

I tried multiple aggregation techniques, but nothing worked.

Any idea how to achieve this ?

thanks.

Answer 1

I am assuming that order_status value define if order is booked or processed.

I have used a terms aggregation on order_id and then have calculated min and max date for respective order status.

Bucket_selector can be used to get difference of ordered and shipped dates and finally average across all buckets

Mapping

PUT index124
{
  "mappings": {
    "properties": {
       "order_history":{
         "type": "nested",
         "properties": {
           "created_at": {
             "type":"date"
           }
         }
       }
    }
  }
}

Data

"order_history" : [
            {
              "id" : 30,
              "notes" : "",
              "order_id" : 20,
              "status_id" : 3,
              "created_at" : "2021-04-30T19:07:03.000000Z",
              "updated_at" : "2021-04-30T19:07:03.000000Z"
            },
            {
              "id" : 31,
              "notes" : "",
              "order_id" : 20,
              "status_id" : 6,
              "created_at" : "2021-05-01T07:07:40.000000Z",
              "updated_at" : "2021-05-01T07:07:40.000000Z"
            },
            {
              "id" : 32,
              "notes" : "",
              "order_id" : 20,
              "status_id" : 6,
              "created_at" : "2021-05-01T07:16:06.000000Z",
              "updated_at" : "2021-05-01T07:16:06.000000Z"
            },
            {
              "id" : 33,
              "notes" : "",
              "order_id" : 20,
              "status_id" : 6,
              "created_at" : "2021-05-01T07:32:20.000000Z",
              "updated_at" : "2021-05-01T07:32:20.000000Z",
              "status" : {
                "id" : 6,
                "name" : "جار التجهيز",
                "name_en" : "preparing",
                "created_at" : null,
                "updated_at" : null,
                "theName" : "جار التجهيز"
              }
            }
          ]

Query

{
  "size": 0,
  "aggs": {
    "order_history": {
      "nested": {
        "path": "order_history"
      },
      "aggs": {
        "orders": {
          "terms": {
            "field": "order_history.order_id",
            "size": 10
          },
          "aggs": {
            "order_booked": {
              "filter": {
                "term": {
                  "order_history.status_id": {
                    "value": 3
                  }
                }
              },
              "aggs": {
                "order_booked_time": {
                  "max": {
                    "field": "order_history.created_at"
                  }
                }
              }
            },
            "order_shiped": {
              "filter": {
                "term": {
                  "order_history.status_id": {
                    "value": 6
                  }
                }
              },
              "aggs": {
                "order_shiped_time": {
                  "max": {
                    "field": "order_history.created_at"
                  }
                }
              }
            },
            "time_diff": {
              "bucket_script": {
                "buckets_path": {
                  "min": "order_booked>order_booked_time.value",
                  "max": "order_shiped>order_shiped_time.value"
                },
                "script": "(params.max - params.min) / 1000 / 60 / 60" 
                                                      -->value in minutes
              }
            }
          }
        },
        "avg_all_buckets": {   --> average for all orders
          "avg_bucket": {
            "buckets_path": "orders>time_diff"
          }
        }
      }
    }
  }
}

Result

"aggregations" : {
    "order_history" : {
      "doc_count" : 4,
      "orders" : {
        "doc_count_error_upper_bound" : 0,
        "sum_other_doc_count" : 0,
        "buckets" : [
          {
            "key" : 20,
            "doc_count" : 4,
            "order_shiped" : {
              "doc_count" : 3,
              "order_shiped_time" : {
                "value" : 1.61985434E12,
                "value_as_string" : "2021-05-01T07:32:20.000Z"
              }
            },
            "order_booked" : {
              "doc_count" : 1,
              "order_booked_time" : {
                "value" : 1.619809623E12,
                "value_as_string" : "2021-04-30T19:07:03.000Z"
              }
            },
            "time_diff" : {
              "value" : 12.421388888888888
            }
          }
        ]
      },
      "avg_monthly_sales" : {
        "value" : 12.421388888888888
      }
    }
  }