Hi team,
I have a stats requirement to get he user retention rate that visit a module per month in last 1 year.
Detail Requirement to stats:
step1. find the distinct users that visit a module in 2020, January
step2. go to 2020, February and find the number of users in step1 visit the module again
step3. go to 2020, March and find the number of users in step2 visit the module again
step4. go to 2020, April and find ....
.....
2020, December and find ...
Log sample in splunk and how can I write the query to get the expected user retention rate?
2021-01-19 06:00:38,668 PLV=REQ CIP=0.0.0.0 CMID=test CMN="testCompany" UID=testUser AGN="[Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/84.0.4147.89 Safari/537.36]" module=SUCCESSION |
| eval month=strftime(_time, "%m")
| stats first(_time) as visit by month, UID, module
| sort visit
| streamstats count by UID, module
| where count = month
This assumes you are starting from January so counts will be 1 in January, 2 in February, etc. if the user has visited the module at least once in all previous months.