Dashboards & Visualizations

How can I build a chart with returning users?

Explorer

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.

0 Karma
1 Solution

Motivator

My apporach would be like this:

...
| bucket span=1d time
| eventstats earliest(
time) AS firstlogin BY user
| eval day
afterfirstlogin=(time-firstlogin)/86400

| stats dc(user) AS totalusers, dc(eval(if(dayafterfirstlogin=0, user, null()))) AS usersday0, dc(eval(if(dayafterfirstlogin=1, user, null()))) AS usersday1, dc(eval(if(dayafterfirstlogin=2,user, null()))) AS usersday2 BY firstlogin

| eval firstlogin=strftime(firstlogin, "%Y-%m-%d")

| eval ratioday0=round(usersday0/totalusers, 2)
| eval ratio
day1=round(usersday1/totalusers, 2)
| eval ratioday2=round(usersday2/total_users, 2)

| fields+ firstlogin, ratioday0, ratioday1, ratioday2

View solution in original post

Motivator

My apporach would be like this:

...
| bucket span=1d time
| eventstats earliest(
time) AS firstlogin BY user
| eval day
afterfirstlogin=(time-firstlogin)/86400

| stats dc(user) AS totalusers, dc(eval(if(dayafterfirstlogin=0, user, null()))) AS usersday0, dc(eval(if(dayafterfirstlogin=1, user, null()))) AS usersday1, dc(eval(if(dayafterfirstlogin=2,user, null()))) AS usersday2 BY firstlogin

| eval firstlogin=strftime(firstlogin, "%Y-%m-%d")

| eval ratioday0=round(usersday0/totalusers, 2)
| eval ratio
day1=round(usersday1/totalusers, 2)
| eval ratioday2=round(usersday2/total_users, 2)

| fields+ firstlogin, ratioday0, ratioday1, ratioday2

View solution in original post

Explorer

Thanks, we are looking into your example. Still cannot say exactly is it right or not. Anyway thank you!

0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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?

0 Karma

Explorer

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

0 Karma