Tutorial: Analyze eCommerce data with aggregations using Query DSL
Elastic Cloud Serverless projects are autoscaled environments, fully managed by Elastic and available on Elastic Cloud. Availability This functionality is generally available and ready for production usage. Serverless interfaces and procedures might differ from classic Elastic Stack deployments. The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability This functionality is generally available and ready for production usage. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes.
Serverless
Generally available
Stack
Generally available
This hands-on tutorial shows you how to analyze eCommerce data using Elasticsearch aggregations with the _search API and Query DSL.
You’ll learn how to:
- Calculate key business metrics such as average order value
- Analyze sales patterns over time
- Compare performance across product categories
- Track moving averages and cumulative totals
You’ll need:
A running instance of Elasticsearch, either on Elastic Cloud Serverless or together with Kibana on Elastic Cloud Hosted/Self Managed deployments.
If you don’t have a deployment, you can run the following command in your terminal to set up a local dev environment:
Shell curl -fsSL https://elastic.co/start-local | sh
The sample eCommerce data loaded into Elasticsearch. To load sample data follow these steps in your UI:
- Open the Integrations pages by searching in the global search field.
- Search for
sample datain the Integrations search field. - Open the Sample data page.
- The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Generally available from 9.0 to 9.3 This functionality is generally available and ready for production usage. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes. Select the Other sample data sets collapsible.
- Add the Sample eCommerce orders data set. This will create and populate an index called
kibana_sample_data_ecommerce.
Before we start analyzing the data, let’s examine the structure of the documents in our sample eCommerce index. Run this command to see the field mappings:
GET kibana_sample_data_ecommerce/_mappingThe response shows the field mappings for the kibana_sample_data_ecommerce index.
Example response
{
"kibana_sample_data_ecommerce": {
"mappings": {
"properties": {
"category": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"currency": {
"type": "keyword"
},
"customer_birth_date": {
"type": "date"
},
"customer_first_name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"customer_full_name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"customer_gender": {
"type": "keyword"
},
"customer_id": {
"type": "keyword"
},
"customer_last_name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"customer_phone": {
"type": "keyword"
},
"day_of_week": {
"type": "keyword"
},
"day_of_week_i": {
"type": "integer"
},
"email": {
"type": "keyword"
},
"event": {
"properties": {
"dataset": {
"type": "keyword"
}
}
},
"geoip": {
"properties": {
"city_name": {
"type": "keyword"
},
"continent_name": {
"type": "keyword"
},
"country_iso_code": {
"type": "keyword"
},
"location": {
"type": "geo_point"
},
"region_name": {
"type": "keyword"
}
}
},
"manufacturer": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"order_date": {
"type": "date"
},
"order_id": {
"type": "keyword"
},
"products": {
"properties": {
"_id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"base_price": {
"type": "half_float"
},
"base_unit_price": {
"type": "half_float"
},
"category": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"created_on": {
"type": "date"
},
"discount_amount": {
"type": "half_float"
},
"discount_percentage": {
"type": "half_float"
},
"manufacturer": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"min_price": {
"type": "half_float"
},
"price": {
"type": "half_float"
},
"product_id": {
"type": "long"
},
"product_name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
},
"analyzer": "english"
},
"quantity": {
"type": "integer"
},
"sku": {
"type": "keyword"
},
"tax_amount": {
"type": "half_float"
},
"taxful_price": {
"type": "half_float"
},
"taxless_price": {
"type": "half_float"
},
"unit_discount_amount": {
"type": "half_float"
}
}
},
"sku": {
"type": "keyword"
},
"taxful_total_price": {
"type": "half_float"
},
"taxless_total_price": {
"type": "half_float"
},
"total_quantity": {
"type": "integer"
},
"total_unique_products": {
"type": "integer"
},
"type": {
"type": "keyword"
},
"user": {
"type": "keyword"
}
}
}
}
}
fields: Multi-field mapping that allows both full text and exact matchinggeoip.properties: Object type field containing location-related propertiesgeoip.location: Geographic coordinates stored as geo_point for location-based queriesproducts.properties: Nested structure containing details about items in each order
The sample data includes the following field data types:
textandkeywordfor text fields- Most
textfields have a.keywordsubfield for exact matching using multi-fields
- Most
datefor date fields3 numeric types:
integerfor whole numberslongfor large whole numbershalf_floatfor floating-point numbers
geo_pointfor geographic coordinatesobjectfor nested structures such asproducts,geoip,event
Now that we understand the structure of our sample data, let’s start analyzing it.
Let’s start by calculating important metrics about orders and customers.
Calculate the average order value across all orders in the dataset using the avg aggregation.
GET kibana_sample_data_ecommerce/_search{ "size": 0, "aggs": { "avg_order_value": { "avg": { "field": "taxful_total_price" } } } }
- Set
sizeto 0 to avoid returning matched documents in the response and return only the aggregation results - A meaningful name that describes what this metric represents
- Configures an
avgaggregation, which calculates a simple arithmetic mean
Example response
{
"took": 0,
"timed_out": false,
"_shards": {
"total": 1,
"successful": 1,
"skipped": 0,
"failed": 0
},
"hits": {
"total": {
"value": 4675,
"relation": "eq"
},
"max_score": null,
"hits": []
},
"aggregations": {
"avg_order_value": {
"value": 75.05542864304813
}
}
}
- Total number of orders in the dataset
hitsis empty because we setsizeto 0- Results appear under the name we specified in the request
- The average order value is calculated dynamically from all the orders in the dataset
Calculate multiple statistics about orders in one request using the stats aggregation.
GET kibana_sample_data_ecommerce/_search{ "size": 0, "aggs": { "order_stats": { "stats": { "field": "taxful_total_price" } } } }
- A descriptive name for this set of statistics
statsreturns count, min, max, avg, and sum at once
Example response
{
"aggregations": {
"order_stats": {
"count": 4675,
"min": 6.98828125,
"max": 2250,
"avg": 75.05542864304813,
"sum": 350884.12890625
}
}
}
"count": Total number of orders in the dataset"min": Lowest individual order value in the dataset"max": Highest individual order value in the dataset"avg": Average value per order across all orders"sum": Total revenue from all orders combined
Tip
The stats aggregation is more efficient than running individual min, max, avg, and sum aggregations.
Let’s group orders in different ways to understand sales patterns.
Group orders by category to see which product categories are most popular, using the terms aggregation.
GET kibana_sample_data_ecommerce/_search{ "size": 0, "aggs": { "sales_by_category": { "terms": { "field": "category.keyword", "size": 5, "order": { "_count": "desc" } } } } }
- Name reflecting the business purpose of this breakdown
termsaggregation groups documents by field values- Use
.keywordfield for exact matching on text fields - Limit to top 5 categories
- Order by number of orders (descending)
Example response
{
"took": 4,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": {
"value": 4675,
"relation": "eq"
},
"max_score": null,
"hits": []
},
"aggregations": {
"sales_by_category": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 572,
"buckets": [
{
"key": "Men's Clothing",
"doc_count": 2024
},
{
"key": "Women's Clothing",
"doc_count": 1903
},
{
"key": "Women's Shoes",
"doc_count": 1136
},
{
"key": "Men's Shoes",
"doc_count": 944
},
{
"key": "Women's Accessories",
"doc_count": 830
}
]
}
}
}
- Due to Elasticsearch’s distributed architecture, when terms aggregations run across multiple shards, the doc counts may have a small margin of error. This value indicates the maximum possible error in the counts.
- Count of documents in categories beyond the requested size.
- Array of category buckets, ordered by count.
- Category name.
- Number of orders in this category.
Group orders by day to track daily sales patterns using the date_histogram aggregation.
GET kibana_sample_data_ecommerce/_search{ "size": 0, "aggs": { "daily_orders": { "date_histogram": { "field": "order_date", "calendar_interval": "day", "format": "yyyy-MM-dd", "min_doc_count": 0 } } } }
- Descriptive name for the time-series aggregation results.
- The
date_histogramaggregation groups documents into time-based buckets, similar to terms aggregation but for dates. - Uses calendar and fixed time intervals to handle months with different lengths.
"day"ensures consistent daily grouping regardless of timezone. - Formats dates in response using date patterns (e.g. "yyyy-MM-dd"). Refer to date math expressions for additional options.
- When
min_doc_countis 0, returns buckets for days with no orders, useful for continuous time series visualization.
Example response
{
"took": 2,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": {
"value": 4675,
"relation": "eq"
},
"max_score": null,
"hits": []
},
"aggregations": {
"daily_orders": {
"buckets": [
{
"key_as_string": "2024-11-28",
"key": 1732752000000,
"doc_count": 146
},
{
"key_as_string": "2024-11-29",
"key": 1732838400000,
"doc_count": 153
},
{
"key_as_string": "2024-11-30",
"key": 1732924800000,
"doc_count": 143
},
{
"key_as_string": "2024-12-01",
"key": 1733011200000,
"doc_count": 140
},
{
"key_as_string": "2024-12-02",
"key": 1733097600000,
"doc_count": 139
},
{
"key_as_string": "2024-12-03",
"key": 1733184000000,
"doc_count": 157
},
{
"key_as_string": "2024-12-04",
"key": 1733270400000,
"doc_count": 145
},
{
"key_as_string": "2024-12-05",
"key": 1733356800000,
"doc_count": 152
},
{
"key_as_string": "2024-12-06",
"key": 1733443200000,
"doc_count": 163
},
{
"key_as_string": "2024-12-07",
"key": 1733529600000,
"doc_count": 141
},
{
"key_as_string": "2024-12-08",
"key": 1733616000000,
"doc_count": 151
},
{
"key_as_string": "2024-12-09",
"key": 1733702400000,
"doc_count": 143
},
{
"key_as_string": "2024-12-10",
"key": 1733788800000,
"doc_count": 143
},
{
"key_as_string": "2024-12-11",
"key": 1733875200000,
"doc_count": 142
},
{
"key_as_string": "2024-12-12",
"key": 1733961600000,
"doc_count": 161
},
{
"key_as_string": "2024-12-13",
"key": 1734048000000,
"doc_count": 144
},
{
"key_as_string": "2024-12-14",
"key": 1734134400000,
"doc_count": 157
},
{
"key_as_string": "2024-12-15",
"key": 1734220800000,
"doc_count": 158
},
{
"key_as_string": "2024-12-16",
"key": 1734307200000,
"doc_count": 144
},
{
"key_as_string": "2024-12-17",
"key": 1734393600000,
"doc_count": 151
},
{
"key_as_string": "2024-12-18",
"key": 1734480000000,
"doc_count": 145
},
{
"key_as_string": "2024-12-19",
"key": 1734566400000,
"doc_count": 157
},
{
"key_as_string": "2024-12-20",
"key": 1734652800000,
"doc_count": 158
},
{
"key_as_string": "2024-12-21",
"key": 1734739200000,
"doc_count": 153
},
{
"key_as_string": "2024-12-22",
"key": 1734825600000,
"doc_count": 165
},
{
"key_as_string": "2024-12-23",
"key": 1734912000000,
"doc_count": 153
},
{
"key_as_string": "2024-12-24",
"key": 1734998400000,
"doc_count": 158
},
{
"key_as_string": "2024-12-25",
"key": 1735084800000,
"doc_count": 160
},
{
"key_as_string": "2024-12-26",
"key": 1735171200000,
"doc_count": 159
},
{
"key_as_string": "2024-12-27",
"key": 1735257600000,
"doc_count": 152
},
{
"key_as_string": "2024-12-28",
"key": 1735344000000,
"doc_count": 142
}
]
}
}
}
- Results of our named aggregation "daily_orders"
- Time-based buckets from date_histogram aggregation
key_as_stringis the human-readable date for this bucketkeyis the same date represented as the Unix timestamp for this bucketdoc_countcounts the number of documents that fall into this time bucket
Now let’s calculate metrics within each group to get deeper insights.
Calculate metrics within each category to compare performance across categories.
GET kibana_sample_data_ecommerce/_search{ "size": 0, "aggs": { "categories": { "terms": { "field": "category.keyword", "size": 5, "order": { "total_revenue": "desc" } }, "aggs": { "total_revenue": { "sum": { "field": "taxful_total_price" } }, "avg_order_value": { "avg": { "field": "taxful_total_price" } }, "total_items": { "sum": { "field": "total_quantity" } } } } } }
- Order categories by their total revenue instead of count
- Define metrics to calculate within each category
- Total revenue for the category
- Average order value in the category
- Total number of items sold
Example response
{
"aggregations": {
"categories": {
"buckets": [
{
"key": "Men's Clothing",
"doc_count": 2179,
"total_revenue": {
"value": 156729.453125
},
"avg_order_value": {
"value": 71.92726898715927
},
"total_items": {
"value": 8716
}
},
{
"key": "Women's Clothing",
"doc_count": 2262,
...
}
]
}
}
}
- Category name
- Number of orders
- Total revenue for this category
- Average order value for this category
- Total quantity of items sold
Let’s combine metrics to track daily trends: daily revenue, unique customers, and average basket size.
GET kibana_sample_data_ecommerce/_search{ "size": 0, "aggs": { "daily_sales": { "date_histogram": { "field": "order_date", "calendar_interval": "day", "format": "yyyy-MM-dd" }, "aggs": { "revenue": { "sum": { "field": "taxful_total_price" } }, "unique_customers": { "cardinality": { "field": "customer_id" } }, "avg_basket_size": { "avg": { "field": "total_quantity" } } } } } }
- Daily revenue
- Uses the
cardinalityaggregation to count unique customers per day - Average number of items per order
Example response
{
"took": 119,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": {
"value": 4675,
"relation": "eq"
},
"max_score": null,
"hits": []
},
"aggregations": {
"daily_sales": {
"buckets": [
{
"key_as_string": "2024-11-14",
"key": 1731542400000,
"doc_count": 146,
"unique_customers": {
"value": 42
},
"revenue": {
"value": 10578.53125
},
"avg_basket_size": {
"value": 2.1780821917808217
}
},
{
"key_as_string": "2024-11-15",
"key": 1731628800000,
"doc_count": 153,
"unique_customers": {
"value": 44
},
"revenue": {
"value": 10448
},
"avg_basket_size": {
"value": 2.183006535947712
}
},
{
"key_as_string": "2024-11-16",
"key": 1731715200000,
"doc_count": 143,
"unique_customers": {
"value": 45
},
"revenue": {
"value": 10283.484375
},
"avg_basket_size": {
"value": 2.111888111888112
}
},
{
"key_as_string": "2024-11-17",
"key": 1731801600000,
"doc_count": 140,
"unique_customers": {
"value": 42
},
"revenue": {
"value": 10145.5234375
},
"avg_basket_size": {
"value": 2.142857142857143
}
},
{
"key_as_string": "2024-11-18",
"key": 1731888000000,
"doc_count": 139,
"unique_customers": {
"value": 42
},
"revenue": {
"value": 12012.609375
},
"avg_basket_size": {
"value": 2.158273381294964
}
},
{
"key_as_string": "2024-11-19",
"key": 1731974400000,
"doc_count": 157,
"unique_customers": {
"value": 43
},
"revenue": {
"value": 11009.45703125
},
"avg_basket_size": {
"value": 2.0955414012738856
}
},
{
"key_as_string": "2024-11-20",
"key": 1732060800000,
"doc_count": 145,
"unique_customers": {
"value": 44
},
"revenue": {
"value": 10720.59375
},
"avg_basket_size": {
"value": 2.179310344827586
}
},
{
"key_as_string": "2024-11-21",
"key": 1732147200000,
"doc_count": 152,
"unique_customers": {
"value": 43
},
"revenue": {
"value": 11185.3671875
},
"avg_basket_size": {
"value": 2.1710526315789473
}
},
{
"key_as_string": "2024-11-22",
"key": 1732233600000,
"doc_count": 163,
"unique_customers": {
"value": 44
},
"revenue": {
"value": 13560.140625
},
"avg_basket_size": {
"value": 2.2576687116564416
}
},
{
"key_as_string": "2024-11-23",
"key": 1732320000000,
"doc_count": 141,
"unique_customers": {
"value": 45
},
"revenue": {
"value": 9884.78125
},
"avg_basket_size": {
"value": 2.099290780141844
}
},
{
"key_as_string": "2024-11-24",
"key": 1732406400000,
"doc_count": 151,
"unique_customers": {
"value": 44
},
"revenue": {
"value": 11075.65625
},
"avg_basket_size": {
"value": 2.0927152317880795
}
},
{
"key_as_string": "2024-11-25",
"key": 1732492800000,
"doc_count": 143,
"unique_customers": {
"value": 41
},
"revenue": {
"value": 10323.8515625
},
"avg_basket_size": {
"value": 2.167832167832168
}
},
{
"key_as_string": "2024-11-26",
"key": 1732579200000,
"doc_count": 143,
"unique_customers": {
"value": 44
},
"revenue": {
"value": 10369.546875
},
"avg_basket_size": {
"value": 2.167832167832168
}
},
{
"key_as_string": "2024-11-27",
"key": 1732665600000,
"doc_count": 142,
"unique_customers": {
"value": 46
},
"revenue": {
"value": 11711.890625
},
"avg_basket_size": {
"value": 2.1971830985915495
}
},
{
"key_as_string": "2024-11-28",
"key": 1732752000000,
"doc_count": 161,
"unique_customers": {
"value": 43
},
"revenue": {
"value": 12612.6640625
},
"avg_basket_size": {
"value": 2.1180124223602483
}
},
{
"key_as_string": "2024-11-29",
"key": 1732838400000,
"doc_count": 144,
"unique_customers": {
"value": 42
},
"revenue": {
"value": 10176.87890625
},
"avg_basket_size": {
"value": 2.0347222222222223
}
},
{
"key_as_string": "2024-11-30",
"key": 1732924800000,
"doc_count": 157,
"unique_customers": {
"value": 43
},
"revenue": {
"value": 11480.33203125
},
"avg_basket_size": {
"value": 2.159235668789809
}
},
{
"key_as_string": "2024-12-01",
"key": 1733011200000,
"doc_count": 158,
"unique_customers": {
"value": 42
},
"revenue": {
"value": 11533.265625
},
"avg_basket_size": {
"value": 2.0822784810126582
}
},
{
"key_as_string": "2024-12-02",
"key": 1733097600000,
"doc_count": 144,
"unique_customers": {
"value": 43
},
"revenue": {
"value": 10499.8125
},
"avg_basket_size": {
"value": 2.201388888888889
}
},
{
"key_as_string": "2024-12-03",
"key": 1733184000000,
"doc_count": 151,
"unique_customers": {
"value": 40
},
"revenue": {
"value": 12111.6875
},
"avg_basket_size": {
"value": 2.172185430463576
}
},
{
"key_as_string": "2024-12-04",
"key": 1733270400000,
"doc_count": 145,
"unique_customers": {
"value": 40
},
"revenue": {
"value": 10530.765625
},
"avg_basket_size": {
"value": 2.0965517241379312
}
},
{
"key_as_string": "2024-12-05",
"key": 1733356800000,
"doc_count": 157,
"unique_customers": {
"value": 43
},
"revenue": {
"value": 11872.5625
},
"avg_basket_size": {
"value": 2.1464968152866244
}
},
{
"key_as_string": "2024-12-06",
"key": 1733443200000,
"doc_count": 158,
"unique_customers": {
"value": 42
},
"revenue": {
"value": 12109.453125
},
"avg_basket_size": {
"value": 2.151898734177215
}
},
{
"key_as_string": "2024-12-07",
"key": 1733529600000,
"doc_count": 153,
"unique_customers": {
"value": 42
},
"revenue": {
"value": 11057.40625
},
"avg_basket_size": {
"value": 2.111111111111111
}
},
{
"key_as_string": "2024-12-08",
"key": 1733616000000,
"doc_count": 165,
"unique_customers": {
"value": 42
},
"revenue": {
"value": 13095.609375
},
"avg_basket_size": {
"value": 2.1818181818181817
}
},
{
"key_as_string": "2024-12-09",
"key": 1733702400000,
"doc_count": 153,
"unique_customers": {
"value": 41
},
"revenue": {
"value": 12574.015625
},
"avg_basket_size": {
"value": 2.2287581699346406
}
},
{
"key_as_string": "2024-12-10",
"key": 1733788800000,
"doc_count": 158,
"unique_customers": {
"value": 42
},
"revenue": {
"value": 11188.1875
},
"avg_basket_size": {
"value": 2.151898734177215
}
},
{
"key_as_string": "2024-12-11",
"key": 1733875200000,
"doc_count": 160,
"unique_customers": {
"value": 42
},
"revenue": {
"value": 12117.65625
},
"avg_basket_size": {
"value": 2.20625
}
},
{
"key_as_string": "2024-12-12",
"key": 1733961600000,
"doc_count": 159,
"unique_customers": {
"value": 45
},
"revenue": {
"value": 11558.25
},
"avg_basket_size": {
"value": 2.1823899371069184
}
},
{
"key_as_string": "2024-12-13",
"key": 1734048000000,
"doc_count": 152,
"unique_customers": {
"value": 45
},
"revenue": {
"value": 11921.1171875
},
"avg_basket_size": {
"value": 2.289473684210526
}
},
{
"key_as_string": "2024-12-14",
"key": 1734134400000,
"doc_count": 142,
"unique_customers": {
"value": 45
},
"revenue": {
"value": 11135.03125
},
"avg_basket_size": {
"value": 2.183098591549296
}
}
]
}
}
}
You can use pipeline aggregations on the results of other aggregations. Let’s analyze how metrics change over time.
Moving averages help identify trends by reducing day-to-day noise in the data. Let’s observe sales trends more clearly by smoothing daily revenue variations, using the Moving Function aggregation.
GET kibana_sample_data_ecommerce/_search{ "size": 0, "aggs": { "daily_sales": { "date_histogram": { "field": "order_date", "calendar_interval": "day" }, "aggs": { "daily_revenue": { "sum": { "field": "taxful_total_price" } }, "smoothed_revenue": { "moving_fn": { "buckets_path": "daily_revenue", "window": 3, "script": "MovingFunctions.unweightedAvg(values)" } } } } } }
- Calculate daily revenue first.
- Create a smoothed version of the daily revenue.
- Use
moving_fnfor moving window calculations. - Reference the revenue from our date histogram.
- Use a 3-day window — use different window sizes to see trends at different time scales.
- Use the built-in unweighted average function in the
moving_fnaggregation.
Example response
{
"took": 13,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": {
"value": 4675,
"relation": "eq"
},
"max_score": null,
"hits": []
},
"aggregations": {
"daily_sales": {
"buckets": [
{
"key_as_string": "2024-11-14T00:00:00.000Z",
"key": 1731542400000,
"doc_count": 146,
"daily_revenue": {
"value": 10578.53125
},
"smoothed_revenue": {
"value": null
}
},
{
"key_as_string": "2024-11-15T00:00:00.000Z",
"key": 1731628800000,
"doc_count": 153,
"daily_revenue": {
"value": 10448
},
"smoothed_revenue": {
"value": 10578.53125
}
},
{
"key_as_string": "2024-11-16T00:00:00.000Z",
"key": 1731715200000,
"doc_count": 143,
"daily_revenue": {
"value": 10283.484375
},
"smoothed_revenue": {
"value": 10513.265625
}
},
{
"key_as_string": "2024-11-17T00:00:00.000Z",
"key": 1731801600000,
"doc_count": 140,
"daily_revenue": {
"value": 10145.5234375
},
"smoothed_revenue": {
"value": 10436.671875
}
},
{
"key_as_string": "2024-11-18T00:00:00.000Z",
"key": 1731888000000,
"doc_count": 139,
"daily_revenue": {
"value": 12012.609375
},
"smoothed_revenue": {
"value": 10292.3359375
}
},
{
"key_as_string": "2024-11-19T00:00:00.000Z",
"key": 1731974400000,
"doc_count": 157,
"daily_revenue": {
"value": 11009.45703125
},
"smoothed_revenue": {
"value": 10813.872395833334
}
},
{
"key_as_string": "2024-11-20T00:00:00.000Z",
"key": 1732060800000,
"doc_count": 145,
"daily_revenue": {
"value": 10720.59375
},
"smoothed_revenue": {
"value": 11055.86328125
}
},
{
"key_as_string": "2024-11-21T00:00:00.000Z",
"key": 1732147200000,
"doc_count": 152,
"daily_revenue": {
"value": 11185.3671875
},
"smoothed_revenue": {
"value": 11247.553385416666
}
},
{
"key_as_string": "2024-11-22T00:00:00.000Z",
"key": 1732233600000,
"doc_count": 163,
"daily_revenue": {
"value": 13560.140625
},
"smoothed_revenue": {
"value": 10971.805989583334
}
},
{
"key_as_string": "2024-11-23T00:00:00.000Z",
"key": 1732320000000,
"doc_count": 141,
"daily_revenue": {
"value": 9884.78125
},
"smoothed_revenue": {
"value": 11822.033854166666
}
},
{
"key_as_string": "2024-11-24T00:00:00.000Z",
"key": 1732406400000,
"doc_count": 151,
"daily_revenue": {
"value": 11075.65625
},
"smoothed_revenue": {
"value": 11543.4296875
}
},
{
"key_as_string": "2024-11-25T00:00:00.000Z",
"key": 1732492800000,
"doc_count": 143,
"daily_revenue": {
"value": 10323.8515625
},
"smoothed_revenue": {
"value": 11506.859375
}
},
{
"key_as_string": "2024-11-26T00:00:00.000Z",
"key": 1732579200000,
"doc_count": 143,
"daily_revenue": {
"value": 10369.546875
},
"smoothed_revenue": {
"value": 10428.096354166666
}
},
{
"key_as_string": "2024-11-27T00:00:00.000Z",
"key": 1732665600000,
"doc_count": 142,
"daily_revenue": {
"value": 11711.890625
},
"smoothed_revenue": {
"value": 10589.684895833334
}
},
{
"key_as_string": "2024-11-28T00:00:00.000Z",
"key": 1732752000000,
"doc_count": 161,
"daily_revenue": {
"value": 12612.6640625
},
"smoothed_revenue": {
"value": 10801.763020833334
}
},
{
"key_as_string": "2024-11-29T00:00:00.000Z",
"key": 1732838400000,
"doc_count": 144,
"daily_revenue": {
"value": 10176.87890625
},
"smoothed_revenue": {
"value": 11564.700520833334
}
},
{
"key_as_string": "2024-11-30T00:00:00.000Z",
"key": 1732924800000,
"doc_count": 157,
"daily_revenue": {
"value": 11480.33203125
},
"smoothed_revenue": {
"value": 11500.477864583334
}
},
{
"key_as_string": "2024-12-01T00:00:00.000Z",
"key": 1733011200000,
"doc_count": 158,
"daily_revenue": {
"value": 11533.265625
},
"smoothed_revenue": {
"value": 11423.291666666666
}
},
{
"key_as_string": "2024-12-02T00:00:00.000Z",
"key": 1733097600000,
"doc_count": 144,
"daily_revenue": {
"value": 10499.8125
},
"smoothed_revenue": {
"value": 11063.4921875
}
},
{
"key_as_string": "2024-12-03T00:00:00.000Z",
"key": 1733184000000,
"doc_count": 151,
"daily_revenue": {
"value": 12111.6875
},
"smoothed_revenue": {
"value": 11171.13671875
}
},
{
"key_as_string": "2024-12-04T00:00:00.000Z",
"key": 1733270400000,
"doc_count": 145,
"daily_revenue": {
"value": 10530.765625
},
"smoothed_revenue": {
"value": 11381.588541666666
}
},
{
"key_as_string": "2024-12-05T00:00:00.000Z",
"key": 1733356800000,
"doc_count": 157,
"daily_revenue": {
"value": 11872.5625
},
"smoothed_revenue": {
"value": 11047.421875
}
},
{
"key_as_string": "2024-12-06T00:00:00.000Z",
"key": 1733443200000,
"doc_count": 158,
"daily_revenue": {
"value": 12109.453125
},
"smoothed_revenue": {
"value": 11505.005208333334
}
},
{
"key_as_string": "2024-12-07T00:00:00.000Z",
"key": 1733529600000,
"doc_count": 153,
"daily_revenue": {
"value": 11057.40625
},
"smoothed_revenue": {
"value": 11504.260416666666
}
},
{
"key_as_string": "2024-12-08T00:00:00.000Z",
"key": 1733616000000,
"doc_count": 165,
"daily_revenue": {
"value": 13095.609375
},
"smoothed_revenue": {
"value": 11679.807291666666
}
},
{
"key_as_string": "2024-12-09T00:00:00.000Z",
"key": 1733702400000,
"doc_count": 153,
"daily_revenue": {
"value": 12574.015625
},
"smoothed_revenue": {
"value": 12087.489583333334
}
},
{
"key_as_string": "2024-12-10T00:00:00.000Z",
"key": 1733788800000,
"doc_count": 158,
"daily_revenue": {
"value": 11188.1875
},
"smoothed_revenue": {
"value": 12242.34375
}
},
{
"key_as_string": "2024-12-11T00:00:00.000Z",
"key": 1733875200000,
"doc_count": 160,
"daily_revenue": {
"value": 12117.65625
},
"smoothed_revenue": {
"value": 12285.9375
}
},
{
"key_as_string": "2024-12-12T00:00:00.000Z",
"key": 1733961600000,
"doc_count": 159,
"daily_revenue": {
"value": 11558.25
},
"smoothed_revenue": {
"value": 11959.953125
}
},
{
"key_as_string": "2024-12-13T00:00:00.000Z",
"key": 1734048000000,
"doc_count": 152,
"daily_revenue": {
"value": 11921.1171875
},
"smoothed_revenue": {
"value": 11621.364583333334
}
},
{
"key_as_string": "2024-12-14T00:00:00.000Z",
"key": 1734134400000,
"doc_count": 142,
"daily_revenue": {
"value": 11135.03125
},
"smoothed_revenue": {
"value": 11865.674479166666
}
}
]
}
}
}
- Date of the bucket is in default ISO format because we didn’t specify a format
- Number of orders for this day
- Raw daily revenue before smoothing
- First day has no smoothed value as it needs previous days for the calculation
- Moving average starts from second day, using a 3-day window
Tip
Notice how the smoothed values lag behind the actual values - this is because they need previous days' data to calculate. The first day will always be null when using moving averages.
Track running totals over time using the cumulative_sum aggregation.
GET kibana_sample_data_ecommerce/_search{ "size": 0, "aggs": { "daily_sales": { "date_histogram": { "field": "order_date", "calendar_interval": "day" }, "aggs": { "revenue": { "sum": { "field": "taxful_total_price" } }, "cumulative_revenue": { "cumulative_sum": { "buckets_path": "revenue" } } } } } }
- Name for our running total
cumulative_sumadds up values across buckets- Reference the revenue we want to accumulate
Example response
{
"took": 4,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": {
"value": 4675,
"relation": "eq"
},
"max_score": null,
"hits": []
},
"aggregations": {
"daily_sales": {
"buckets": [
{
"key_as_string": "2024-11-14T00:00:00.000Z",
"key": 1731542400000,
"doc_count": 146,
"revenue": {
"value": 10578.53125
},
"cumulative_revenue": {
"value": 10578.53125
}
},
{
"key_as_string": "2024-11-15T00:00:00.000Z",
"key": 1731628800000,
"doc_count": 153,
"revenue": {
"value": 10448
},
"cumulative_revenue": {
"value": 21026.53125
}
},
{
"key_as_string": "2024-11-16T00:00:00.000Z",
"key": 1731715200000,
"doc_count": 143,
"revenue": {
"value": 10283.484375
},
"cumulative_revenue": {
"value": 31310.015625
}
},
{
"key_as_string": "2024-11-17T00:00:00.000Z",
"key": 1731801600000,
"doc_count": 140,
"revenue": {
"value": 10145.5234375
},
"cumulative_revenue": {
"value": 41455.5390625
}
},
{
"key_as_string": "2024-11-18T00:00:00.000Z",
"key": 1731888000000,
"doc_count": 139,
"revenue": {
"value": 12012.609375
},
"cumulative_revenue": {
"value": 53468.1484375
}
},
{
"key_as_string": "2024-11-19T00:00:00.000Z",
"key": 1731974400000,
"doc_count": 157,
"revenue": {
"value": 11009.45703125
},
"cumulative_revenue": {
"value": 64477.60546875
}
},
{
"key_as_string": "2024-11-20T00:00:00.000Z",
"key": 1732060800000,
"doc_count": 145,
"revenue": {
"value": 10720.59375
},
"cumulative_revenue": {
"value": 75198.19921875
}
},
{
"key_as_string": "2024-11-21T00:00:00.000Z",
"key": 1732147200000,
"doc_count": 152,
"revenue": {
"value": 11185.3671875
},
"cumulative_revenue": {
"value": 86383.56640625
}
},
{
"key_as_string": "2024-11-22T00:00:00.000Z",
"key": 1732233600000,
"doc_count": 163,
"revenue": {
"value": 13560.140625
},
"cumulative_revenue": {
"value": 99943.70703125
}
},
{
"key_as_string": "2024-11-23T00:00:00.000Z",
"key": 1732320000000,
"doc_count": 141,
"revenue": {
"value": 9884.78125
},
"cumulative_revenue": {
"value": 109828.48828125
}
},
{
"key_as_string": "2024-11-24T00:00:00.000Z",
"key": 1732406400000,
"doc_count": 151,
"revenue": {
"value": 11075.65625
},
"cumulative_revenue": {
"value": 120904.14453125
}
},
{
"key_as_string": "2024-11-25T00:00:00.000Z",
"key": 1732492800000,
"doc_count": 143,
"revenue": {
"value": 10323.8515625
},
"cumulative_revenue": {
"value": 131227.99609375
}
},
{
"key_as_string": "2024-11-26T00:00:00.000Z",
"key": 1732579200000,
"doc_count": 143,
"revenue": {
"value": 10369.546875
},
"cumulative_revenue": {
"value": 141597.54296875
}
},
{
"key_as_string": "2024-11-27T00:00:00.000Z",
"key": 1732665600000,
"doc_count": 142,
"revenue": {
"value": 11711.890625
},
"cumulative_revenue": {
"value": 153309.43359375
}
},
{
"key_as_string": "2024-11-28T00:00:00.000Z",
"key": 1732752000000,
"doc_count": 161,
"revenue": {
"value": 12612.6640625
},
"cumulative_revenue": {
"value": 165922.09765625
}
},
{
"key_as_string": "2024-11-29T00:00:00.000Z",
"key": 1732838400000,
"doc_count": 144,
"revenue": {
"value": 10176.87890625
},
"cumulative_revenue": {
"value": 176098.9765625
}
},
{
"key_as_string": "2024-11-30T00:00:00.000Z",
"key": 1732924800000,
"doc_count": 157,
"revenue": {
"value": 11480.33203125
},
"cumulative_revenue": {
"value": 187579.30859375
}
},
{
"key_as_string": "2024-12-01T00:00:00.000Z",
"key": 1733011200000,
"doc_count": 158,
"revenue": {
"value": 11533.265625
},
"cumulative_revenue": {
"value": 199112.57421875
}
},
{
"key_as_string": "2024-12-02T00:00:00.000Z",
"key": 1733097600000,
"doc_count": 144,
"revenue": {
"value": 10499.8125
},
"cumulative_revenue": {
"value": 209612.38671875
}
},
{
"key_as_string": "2024-12-03T00:00:00.000Z",
"key": 1733184000000,
"doc_count": 151,
"revenue": {
"value": 12111.6875
},
"cumulative_revenue": {
"value": 221724.07421875
}
},
{
"key_as_string": "2024-12-04T00:00:00.000Z",
"key": 1733270400000,
"doc_count": 145,
"revenue": {
"value": 10530.765625
},
"cumulative_revenue": {
"value": 232254.83984375
}
},
{
"key_as_string": "2024-12-05T00:00:00.000Z",
"key": 1733356800000,
"doc_count": 157,
"revenue": {
"value": 11872.5625
},
"cumulative_revenue": {
"value": 244127.40234375
}
},
{
"key_as_string": "2024-12-06T00:00:00.000Z",
"key": 1733443200000,
"doc_count": 158,
"revenue": {
"value": 12109.453125
},
"cumulative_revenue": {
"value": 256236.85546875
}
},
{
"key_as_string": "2024-12-07T00:00:00.000Z",
"key": 1733529600000,
"doc_count": 153,
"revenue": {
"value": 11057.40625
},
"cumulative_revenue": {
"value": 267294.26171875
}
},
{
"key_as_string": "2024-12-08T00:00:00.000Z",
"key": 1733616000000,
"doc_count": 165,
"revenue": {
"value": 13095.609375
},
"cumulative_revenue": {
"value": 280389.87109375
}
},
{
"key_as_string": "2024-12-09T00:00:00.000Z",
"key": 1733702400000,
"doc_count": 153,
"revenue": {
"value": 12574.015625
},
"cumulative_revenue": {
"value": 292963.88671875
}
},
{
"key_as_string": "2024-12-10T00:00:00.000Z",
"key": 1733788800000,
"doc_count": 158,
"revenue": {
"value": 11188.1875
},
"cumulative_revenue": {
"value": 304152.07421875
}
},
{
"key_as_string": "2024-12-11T00:00:00.000Z",
"key": 1733875200000,
"doc_count": 160,
"revenue": {
"value": 12117.65625
},
"cumulative_revenue": {
"value": 316269.73046875
}
},
{
"key_as_string": "2024-12-12T00:00:00.000Z",
"key": 1733961600000,
"doc_count": 159,
"revenue": {
"value": 11558.25
},
"cumulative_revenue": {
"value": 327827.98046875
}
},
{
"key_as_string": "2024-12-13T00:00:00.000Z",
"key": 1734048000000,
"doc_count": 152,
"revenue": {
"value": 11921.1171875
},
"cumulative_revenue": {
"value": 339749.09765625
}
},
{
"key_as_string": "2024-12-14T00:00:00.000Z",
"key": 1734134400000,
"doc_count": 142,
"revenue": {
"value": 11135.03125
},
"cumulative_revenue": {
"value": 350884.12890625
}
}
]
}
}
}
daily_sales: Results from our daily sales date histogrambuckets: Array of time-based bucketskey_as_string: Date for this bucket (in ISO format since no format specified)revenue: Daily revenue for this datecumulative_revenue: Running total of revenue up to this date
Refer to the aggregations reference for more details on all available aggregation types.