Kibana retention rate metric

[Richard_Bao]

My case is show user use an app retention rate in last 30 day.
value = app opened count / total installed app count
How to do this by using Kibana?

The user schema is as follow, means who installed the app. The "created_on" can be the date when install the app.
{
"created_on" : {
"type" : "date"
},
"dob" : {
"type" : "date"
},
"email" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"fullname" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"id" : {
"type" : "long"
},
...
}

app usage schema is as follow:
{
"appid" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"user_id" : {
"type" : "long"
},
"duration" : {
"type" : "long"
},
"end_time" : {
"type" : "date"
},
"id" : {
"type" : "long"
},
"start_time" : {
"type" : "date"
}
}

So last 30 day app retention rate should be unique count of user who open the app (based on "start_time") divide by the total user who installed the app

[Richard_Bao]

Can anyone tell is it possible? Or is it possible to use custom visualization making this working?

[lukas (Lukas Olson)]

I believe this is a goal of the new visualization editor, Lens, which will hopefully debut in 7.4 or 7.5. Other than that you may be able to do this using Canvas. Do you have Canvas in your Kibana installation?

[Richard_Bao]

Hi Lukas, thanks for your reply! I do have Canvas in Kibana. But I don't have any idea how o write SQL to make this work. Do you have any idea? Can you write the SQL statement to make it? Thanks!

[Richard_Bao]

Hi Lukas, does Canvas SQL support common table expression (CTE) query, I can't find Elasticsearch SQL support CTE query, if not, then I think Canvas can't do the retention rate metric. What's your opinion? Thanks!