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
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

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!

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 ...