Hive + Tableau Calculates Retention Rate Calculates Retention Number

Original link:https://segmentfault.com/a/1190000039736441

Background: The monthly retention data of the APP in the past 2 years should be counted

Retention: users who downloaded the APP 12 months ago, and users who visited the APP in the following 12 months

Demand diagram (Figure is Youmeng Statistics) :

Visit_Table_Name field: user ID, access date date 12ABC, 2021-03-29

Analysis: 1. Count the month of the first interview

Substr (date,1,7) returns month format like 2021-03-29 => 2021-03
SELECT user,min(substr(date,1,7)) AS first_month
FROM visit_table_name
WHERE date >= "2019-02-01" AND date < "2021-03-01"
GROUP BY user

2. Statistics of users’ monthly visits

SELECT user,substr(date,1,7) AS visit_month FROM visit_table_name WHERE date >= "2019-02-01" AND date < "2021-03-01" GROUP BY the user, substr (date, 1, 7)

The original connection: https://segmentfault.com/a/1190000039736441

3. Connect and subtract months

SELECT first_table.user, first_month, visit_month, (year(CONCAT(visit_month,"-01"))-year(CONCAT(first_month,"-01")))*12+(month(CONCAT(visit_month,"-01"))-month(CONCAT(firs t_month,"-01"))) AS gap_month FROM (SELECT user, Min (substr(date,1,7)) AS first_month FROM visit_table_name WHERE date >= "2019-02-01" AND date < "2021-03-01" GROUP BY user) AS first_table LEFT JOIN (SELECT user, Substr (date,1,7) AS visit_month FROM visit_table_name WHERE date >= "2019-02-01" AND date < "2021-03-01" GROUP BY user Substr (date,1,7)) AS visit_table ON first_table.user = visit_table.user

If the first time the user visits in February 20, the second time in March 20, there will be a 1gap.

The effect is shown using Tableau BI tool



It looks something like this

The original connection: https://segmentfault.com/a/1190000039736441