How to create visualisation in kibana for stickiness(Ratio of DAU/MAU)

[dharanesh]

Hi, i am new to kibana, i created separate visualisation for DAU and MAU now i want to create another chats which shows Ratio of DAU and MAU, how to do this in kibana , is it possible to create charts for this in kibana.

Thanks in advance

[wylie (Wylie Conlon)]

Hi, welcome to the forums. The answer to your question is entirely dependent on what kind of data you have stored, especially if the data has different time ranges. You might be able to do this using TSVB, and I have a post about how to do that here: https://www.elastic.co/blog/how-to-display-data-as-a-percentage-in-kibana-visualizations

[dharanesh]

Hi,
Thanks for the reply, i will explain my requirement in detail can you guide me how to do this in kibana.
I stored data like this
timestamp userid
2020-09-01T01:00:00.629Z 1
2020-09-01T01:00:00.629Z 2
2020-09-02T09:00:00.629Z 1
2020-09-02T09:00:00.629Z 3
2020-09-02T10:00:00.629Z 4

if you compute you will get result as below
DAU =2020-09-01 - 2
2020-09-02 - 3

MAU on month SEPT = 4

DAU/MAU=2020-09-01 - 2/4=50%
2020-09-01 -3/4=74%
this what i am expecting, is it possible to create visualization in kibana?

[wylie (Wylie Conlon)]

Because it looks like you are using the Cardinality aggregation to figure out the number of unique users in each time period, you can't do this purely in TSVB. Your two options are:

1. Precalculate the number of unique users per day and per month, and then visualize on the pre-calculated data. Maybe use transforms.

2. Use Vega to build a completely custom query.

I prefer Vega, and I can show you an example where I build a comparison of "this week" vs 4 weeks ago. You can use a similar approach to calculate "today" vs "the last 30 days", but you'd need to change the query in the filters:

{
  "$schema": "https://vega.github.io/schema/vega-lite/v4.json",
  "data": {
    "url": {
      "index": "metricbeat-*",
      "body": {
        "aggs": {
          "f": {
            "filters": {
              "filters": {
                "current": {
                  "range": { "@timestamp": { "%timefilter%": true } }
                },
                "previous": {
                  "range": {
                    "@timestamp": {
                      "%timefilter%": true, "shift": -4, "unit": "week"
                    }
                  }
                }
              }
            }
          }
        },
        "size": 0
      }
    },
    "format": {
      "type": "json",
      "property": "aggregations.f.buckets"
    },
  }
  
  "transform": [
    {
      "calculate": "if (datum.previous.doc_count, datum.current.doc_count / datum.previous.doc_count, null)",
      "as": "percent_diff"
    }
  ]
  
  "vconcat": [
    {
      "title": "Percent change from 4 weeks ago"
      "width": "container"
      height: 40
      "mark": "text",
      "encoding": {
        "text": {
          "field": "percent_diff"
          "format": "0.2%"
        },
        "size": { value: 32 },
        "align": { "value": "center" },
      }
    },
    {
      "title": "Current value"
      "width": "container"
      "mark": "text",
      "encoding": {
        "text": {
          field: "current.doc_count",
          format: ","
        },
        "fill": { "value": "black" },
        "fontWeight": { "value": "bold" },
        "align": { "value": "center" },
      }
    }
  ],
}