Hello everyone,
I want to use Elasticsearch to track user events like placing bets, making deposits, withdrawals etc.
I have created a data stream with document which track timestamp of the event, user_id as keyword and bet_amount for bets, deposit_amount for deposits etc.
I need to be able to perform complex queries for example get user_id of users that have placed more than $10 bets in the last 24 hours and less than $20 bets in the last 12 hours. I want to get back a list of user_id to create segments.
This is a query I use for now and with 800k dummy docs it takes 2-3 seconds if it's not cached.
{
"size": 0,
"aggs": {
"users": {
"composite": {
"size": 10000,
"sources": [
{
"user_id": {
"terms": {
"field": "user_id",
"order": "asc"
}
}
}
]
},
"aggs": {
"sum_bet_amount_0": {
"filter": {
"range": {
"@timestamp": {
"gte": 1738528380,
"lte": 1738614780
}
}
},
"aggs": {
"sum_bet_amount_0": {
"sum": {
"field": "bet_amount"
}
}
}
},
"sum_bet_amount_1": {
"filter": {
"range": {
"@timestamp": {
"gte": 1738571580,
"lte": 1738614780
}
}
},
"aggs": {
"sum_bet_amount_1": {
"sum": {
"field": "bet_amount"
}
}
}
},
"filter_by_bet_amount_0": {
"bucket_selector": {
"buckets_path": {
"total": "sum_bet_amount_0>sum_bet_amount_0"
},
"script": "params.total >= 10"
}
},
"filter_by_bet_amount_1": {
"bucket_selector": {
"buckets_path": {
"total": "sum_bet_amount_1>sum_bet_amount_1"
},
"script": "params.total <= 20"
}
}
}
}
}
}
Any tips on how I can improve this query or is there a better way to perform such complex queries? Any other tips for elastic?
With this I get back an array of buckets but ideally I want to get the unique count of user_id in all filtered buckets as well.
Any help will be much appreciated!
Thank you!