# Kibana retention rate metric

**URL:** <https://discuss.elastic.co/t/kibana-retention-rate-metric/194871>\
**Category:** Kibana\
**Created:** [August 12, 2019, 3:07pm UTC](https://discuss.elastic.co/t/kibana-retention-rate-metric/194871 "2019-08-12T15:07:52Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Richard\_Bao](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/richard_bao/32/47247_2.png) [@Richard\_Bao](https://discuss.elastic.co/u/Richard_Bao)\
**Post date:** [August 12, 2019, 3:07pm UTC](https://discuss.elastic.co/t/kibana-retention-rate-metric/194871/1 "2019-08-12T15:07:52Z")

</div>

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?

[![](https://us1.discourse-cdn.com/elastic/original/3X/5/b/5b108744c3234ce02a9210d1def3681ac0fae12b.png) ](https://us1.discourse-cdn.com/elastic/original/3X/5/b/5b108744c3234ce02a9210d1def3681ac0fae12b.png)

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

---

<div class="post-metadata">

**Author:** ![Richard\_Bao](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/richard_bao/32/47247_2.png) [@Richard\_Bao](https://discuss.elastic.co/u/Richard_Bao)\
**Post date:** [August 13, 2019, 1:50pm UTC](https://discuss.elastic.co/t/kibana-retention-rate-metric/194871/2 "2019-08-13T13:50:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![lukas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukas/32/6812_2.png) [@lukas](https://discuss.elastic.co/u/lukas)\
**Post date:** [August 13, 2019, 10:12pm UTC](https://discuss.elastic.co/t/kibana-retention-rate-metric/194871/3 "2019-08-13T22:12:53Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Richard\_Bao](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/richard_bao/32/47247_2.png) [@Richard\_Bao](https://discuss.elastic.co/u/Richard_Bao)\
**Post date:** [August 15, 2019, 3:17am UTC](https://discuss.elastic.co/t/kibana-retention-rate-metric/194871/4 "2019-08-15T03:17:12Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![Richard\_Bao](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/richard_bao/32/47247_2.png) [@Richard\_Bao](https://discuss.elastic.co/u/Richard_Bao)\
**Post date:** [August 15, 2019, 1:18pm UTC](https://discuss.elastic.co/t/kibana-retention-rate-metric/194871/5 "2019-08-15T13:18:12Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [September 12, 2019, 1:18pm UTC](https://discuss.elastic.co/t/kibana-retention-rate-metric/194871/6 "2019-09-12T13:18:17Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.