Cohort report ?

[Yarsa]

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?
streamstats perhaps?

thanks

[Yarsa · Accepted solution]

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"

Final query

"| 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 "

Thanks!

[jbranislav]

Hi, can you please explain little your search? I'm trying to do similar search but with no luck. Thanks

[Yarsa]

I have logs like this:
* 2012-08-07T23:36:16+0000 A=enterEvent userId=####### ...

[Ayn]

How do you identify unique users? By cookie value or something else?