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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...