Dashboards & Visualizations

How can I build a chart with returning users?

xenozauros
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

HeinzWaescher
Motivator

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

View solution in original post

HeinzWaescher
Motivator

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

xenozauros
Explorer

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

0 Karma

xenozauros
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

somesoni2
Revered Legend

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

xenozauros
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
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) v3.54.0

The Splunk Threat Research Team (STRT) recently released Enterprise Security Content Update (ESCU) v3.54.0 and ...

Using Machine Learning for Hunting Security Threats

WATCH NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more for ...

New Learning Videos on Topics Most Requested by You! Plus This Month’s New Splunk ...

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...