Refine your search:

0
1

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

asked 22 Aug '12, 02:33

Yarsa's gravatar image

Yarsa
1406
accept rate: 100%

edited 22 Aug '12, 02:34

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

(22 Aug '12, 02:50) Ayn ♦

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

(22 Aug '12, 03:36) Yarsa

One Answer:

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!

link

answered 22 Aug '12, 05:59

Yarsa's gravatar image

Yarsa
1406
accept rate: 100%

Post your answer
toggle preview

Follow this question

Log In to enable email subscriptions

RSS:

Answers

Answers + Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×243
×1

Asked: 22 Aug '12, 02:33

Seen: 556 times

Last updated: 22 Aug '12, 05:59

Copyright © 2005-2014 Splunk Inc. All rights reserved.