Reporting

Cohort report ?

Yarsa
Path Finder

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

Tags (2)
0 Karma
1 Solution

Yarsa
Path Finder

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!

View solution in original post

Yarsa
Path Finder

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
Explorer

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

0 Karma

Yarsa
Path Finder

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

0 Karma

Ayn
Legend

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...