Hi!
How can I build a chart with returning users?
I have filed 'user' which contain unique key. So I can count unique users for some day.
But I have more complex task - I nead a graph that will show ratio for how many users from day X are returning each day during month.
I guess that I use map for this search, but I bumped.
Please help!
Thanks.
My apporach would be like this:
...
| bucket span=1d _time
| eventstats earliest(_time) AS first_login BY user
| eval day_after_first_login=(_time-first_login)/86400
| stats dc(user) AS total_users, dc(eval(if(day_after_first_login=0, user, null()))) AS users_day0, dc(eval(if(day_after_first_login=1, user, null()))) AS users_day1, dc(eval(if(day_after_first_login=2,user, null()))) AS users_day2 BY first_login
| eval first_login=strftime(first_login, "%Y-%m-%d")
| eval ratio_day0=round(users_day0/total_users, 2)
| eval ratio_day1=round(users_day1/total_users, 2)
| eval ratio_day2=round(users_day2/total_users, 2)
| fields+ first_login, ratio_day0, ratio_day1, ratio_day2
My apporach would be like this:
...
| bucket span=1d _time
| eventstats earliest(_time) AS first_login BY user
| eval day_after_first_login=(_time-first_login)/86400
| stats dc(user) AS total_users, dc(eval(if(day_after_first_login=0, user, null()))) AS users_day0, dc(eval(if(day_after_first_login=1, user, null()))) AS users_day1, dc(eval(if(day_after_first_login=2,user, null()))) AS users_day2 BY first_login
| eval first_login=strftime(first_login, "%Y-%m-%d")
| eval ratio_day0=round(users_day0/total_users, 2)
| eval ratio_day1=round(users_day1/total_users, 2)
| eval ratio_day2=round(users_day2/total_users, 2)
| fields+ first_login, ratio_day0, ratio_day1, ratio_day2
Thanks, we are looking into your example. Still cannot say exactly is it right or not. Anyway thank you!
Finalym we've made it like this:
...
| bucket span=1d _time
| eval first_login=(if(name="first",_time,0))
| eventstats max(first_login) AS reg_date BY user
| eval day_after_first_login=(_time-reg_date)/86400
| eval reg_date=strftime(reg_date, "%Y-%m-%d")
| stats dc(eval(if(day_after_first_login=0, user, null()))) AS users_day0, dc(eval(if(day_after_first_login=1, user, null()))) AS users_day1, dc(eval(if(day_after_first_login=2, user, null()))) AS users_day2, dc(eval(if(day_after_first_login=3, user, null()))) AS users_day3, dc(eval(if(day_after_first_login=7, user, null()))) AS users_day7, dc(eval(if(day_after_first_login=14, user, null()))) AS users_day14, dc(eval(if(day_after_first_login=30, user, null()))) AS users_day30 BY reg_date
| eval ret_day1=round(users_day1/users_day0, 2)
| eval ret_day2=round(users_day2/users_day0, 2)
| eval ret_day3=round(users_day3/users_day0, 2)
| eval ret_day7=round(users_day7/users_day0, 2)
| eval ret_day14=round(users_day14/users_day0, 2)
| eval ret_day30=round(users_day30/users_day0, 2)
| fields+ reg_date, users_day0, ret_day1, ret_day2, ret_day3, ret_day7, ret_day14, ret_day30
So, thank you very much, you help is appreciated
So do you want to list Users who logged in on day 1 and they logged in for the rest of the month as well, everyday?
Looks that I need to be more specific.
If speaking in terms of some scripting language, what I need is:
Firstly, find unique users for day 0, let's call them array Uniq0.
Then, I need to find unique users for each day N [1 - 30]. Then I need to find intersection with Uniq0 (who of them where logged in in day 0). Let's call these intersected arrays UniqN.
As a goal I need the graph with ratios between count(UniqN) and count(Unic0).
Like this:
count(Uniq1)/count(Uniq0), count(Uniq2)/count(Uniq0), ... count(Uniq30)/count(uniq0)
Easily can do it in python, but absolutely do not know from which side to do it in splunk