Hi, I'm trying to build a cohort report in splunk and I can't think of an easy way other than a million joins.
Let's say I want to measure retention to my site.
I would take all the users who visited my site on the week of June 5th and see how much of them return in the following weeks.
The same thing I would do for each of the following weeks.
Week 1| Week 2 | Week 3 | Week 4 | Week 5 |
June 5 | 100 | 20 | 15 | 10 | 10
June 12 | 110 | 40 | 35 | 30 |
June 19 | 106 | 50 | 44 | |
June 26 | 100 | 55 | | |
I've built a cohort like this with more than 10 different joins and a few appends.
Is there a simpler way?
Hi turns out it was easier than I thought :)
Here's what I did
scheduled job - test#:
"earliest="-4w@w" latest="@w" sourcetype=bi | bucket _time as week span=1w | table userId week"
scheduled job - test##:
"earliest="-4w@w" latest="@w" sourcetype=bi A=enterEvent | bucket _time as week span=1w | convert timeformat="%d/%m/%y" ctime(_time) AS day | stats dc(day) as days by userId,week"
"| loadjob savedsearch="yarin:search:test#" | dedup userId week | join userId type=inner max=0 [| loadjob savedsearch="yarin:search:test##" | rename week as weeks | streamstats count as rank by userId] | chart dc(userId) as users by week,rank | streamstats count as rank | rename "1" as week1 | rename "2" as week2 | rename "3" as week3 | rename "4" as week4 "