# How to get DAU / WAU / ... charts in ElasticSearch?
**URL:** https://discuss.elastic.co/t/how-to-get-dau-wau-charts-in-elasticsearch/64774
**Category:** Elasticsearch
**Created:** [November 2, 2016, 9:18pm UTC](https://discuss.elastic.co/t/how-to-get-dau-wau-charts-in-elasticsearch/64774 "2016-11-02T21:18:25Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@KIVagant](https://discuss.elastic.co/u/KIVagant)
#### Post date: [November 2, 2016, 9:18pm UTC](https://discuss.elastic.co/t/how-to-get-dau-wau-charts-in-elasticsearch/64774/1 "2016-11-02T21:18:25Z")
Hello.
Sorry for my strange English.
I'm trying to use [pipeline aggregations](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline.html) (still deep in the manual) with a goal to get a line chart with a complex calculation for each day on the calendar.
In common, I'm looking a way to get (L)DAU / (L)WAU / (L)MAU charts ([examples](https://www.devtodev.com/apps/main-metrics/337/), [SQL-syntax example with Google BigQuery](http://stackoverflow.com/questions/33226570/how-to-calculate-dau-mau-with-bigquery-engagement)). And then more complex charts (like ARPU, which need to divide one line daily measures to the another line measures) that used the data from WAU etc.
I wrote a query that can show the data for _Daily Active Users_ (DAU) chart:
> **DAU chart data query**
>
> ```
> GET sessions-*/_search
> {
> "size": 0,
> "aggs": {
> "dau": {
> "filter": {
> "bool": {
> "must": [
> {...},
> {
> "range": {
> "@timestamp": {
> "lte": "now",
> "gte": "now-30d/d"
> }
> }
> }
> ]
> }
> },
> "aggs": {
> "interval_aggregation": {
> "date_histogram": {
> "field": "@timestamp",
> "interval": "1d"
> },
> "aggs": {
> "distinct_visitors": {
> "cardinality": {
> "field": "user_id"
> }
> }
> }
> }
> }
> }
> }
> }
>
> ```
> **DAU query response**
>
> ```
> "aggregations": {
> "wau": {
> "doc_count": 1991,
> "interval_aggregation": {
> "buckets": [
> {
> ...
> "distinct_visitors": {
> "value": 90
> }
> },
> {
> ...
> "distinct_visitors": {
> "value": 103
> }
> },
> ]
> }
> }
> }
>
> ```
But for Weekly Active Users (WAU) this query is not enough. I can set interval as **"7d"** and it will return a good value, but I need to see this measure for every calendar day.
So, for today I can get one WAU value with this request:
> **WAU query just for today (on day start)**
>
> ```
> GET sessions-*/_search
> {
> "size": 0,
> "aggs": {
> "wau": {
> "filter": {
> "bool": {
> "must": [
> {...},
> {
> "range": {
> "@timestamp": {
> "lte": "now/d",
> "gte": "now-7d/d"
> }
> }
> }
> ]
> }
> },
> "aggs": {
> "distinct_visitors": {
> "cardinality": {
> "field": "user_id"
> }
> }
> }
> }
> }
> }
>
> ```
> **WAU one day query response**
>
> ```
> "aggregations": {
> "wau": {
> "doc_count": 3492,
> "distinct_visitors": {
> "value": 18
> }
> }
> }
>
> ```
How I can get the WAU value for each calendar day?
---
### Author:  [@KIVagant](https://discuss.elastic.co/u/KIVagant)
#### Post date: [November 2, 2016, 9:18pm UTC](https://discuss.elastic.co/t/how-to-get-dau-wau-charts-in-elasticsearch/64774/2 "2016-11-02T21:18:32Z")
I thinking about this variants:
1. I need to replace this part with something like variables.
In MySQL it can be something like this: (in reality can't because of [the problem](https://gist.github.com/KIVagant/e143ac569a0cb2984d528495d2fcf6b0))
SELECT @day := day as day, wau.visitors
(SELECT visitors FROM ...impossible wau query... WHERE date=@day) as wau FROM (SELECT day FROM ...crazy query for calendar range... )
In ElasticSearch maybe it is possible [with scripting](https://www.elastic.co/guide/en/elasticsearch/reference/current/modules-scripting.html)? But how?
```
... WAU aggregation inside scripted loop through a calendar executed for each day:
"lte": "{{ CALENDAR_DAY_VARIABLE }}",
"gte": "{{ CALENDAR_DAY_VARIABLE }}-6d/d"
```
1. I need to use [pipeline aggregations](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline.html). It is should be something like a "LEFT JOIN".
- On the left side – a calendar – date histogram, _but without using any index data_.
- On the right side – a WAU aggregation that should be called for each day. Some variable is needed again here.
1. I need to use pipeline aggregations again. In the nested aggregation level I need to make a calendar and get any dates from them. In the top aggregations level I need to get WAU aggregation for any date from nested level, using nested.day instead of "now" in "lte", "gte" and/or "interval" params.
Any ideas about this? Maybe someone already makes this charts with ES?
P.S.: I thinking about cardinally different variant then I can call the simple DAU / WAU / ... queries for every day on application level and cache the result inside a different ES index.
- The [related Logstash problem](https://discuss.elastic.co/t/how-to-get-es-aggregation-data-as-logstash-input/64776).
It should be much more faster of course and then I'll can use this index inside exists Kibana visualisations (especially inside Timelion).
---
### Author:  [@Samurais](https://discuss.elastic.co/u/Samurais)
#### Post date: [February 15, 2017, 3:05am UTC](https://discuss.elastic.co/t/how-to-get-dau-wau-charts-in-elasticsearch/64774/3 "2017-02-15T03:05:57Z")
I come to the same problem, track the DAU/WAU data, have you got your script work, can you share it?
---
### Author:  [@KIVagant](https://discuss.elastic.co/u/KIVagant)
#### Post date: [March 14, 2017, 4:57pm UTC](https://discuss.elastic.co/t/how-to-get-dau-wau-charts-in-elasticsearch/64774/4 "2017-03-14T16:57:08Z")
Unfortunately, I can't share the php script because of commercial NDA. In short, inside I have a cycle that requesting each period from ES and sends the result to output. For example, load every N days, where N is console argument. Each result is separate Json document, so you can index this and then compare with other documents or place them all to different charts, such as DAU. And this tool can ask stats for one day and for 7 last days (for future WAU) and for a 28 days. So, if you will call the utility once, you will have 3 documents. If you call the tool every day (or will add some additional args such as 'offset'), you will have stat for each day that you need. Then just save all of new documents to separate ES index and place on charts.
See the [logstash config example here](https://discuss.elastic.co/t/how-to-get-es-aggregation-data-as-logstash-input/64776/2?u=kivagant).
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 5, 2017, 10:02pm UTC](https://discuss.elastic.co/t/how-to-get-dau-wau-charts-in-elasticsearch/64774/5 "2017-07-05T22:02:17Z")