Solved: How do I get Unique users per day and per month in... - Page 3

pzhou07920 · ‎08-03-2016

Hi,

I have a field called "UserID" and a DateActive field. I'm looking to make a bar chart where each bar has a value equal to the average # of unique users per day in a month divided by the total # of active users of that month, for every month in the year (Lets call this value Stickiness). For example, if the month of Jan averages 3,000 unique active users per day and has 10,000 unique active users in the entire month of Jan, then I want the Stickiness for Jan to be 3,000/10,000 or .3.

I'm able to get both unique active users per day and unique active users per month in separate queries but am having trouble doing it together to do the division since I am using

timechart span=1(day or month) dc(UserID) and using eval _time=DateModified

So how can I obtain both the unique daily active user and unique monthly active user counts in a single query so that I can perform division on them? I also want to display the data using a bar graph that has one bar for the Stickiness of each month in a 12 month window? I'd normally use timechart but i'm not sure how for a more complex situation like this.

Might have worded it poorly, let me know so I can clarify.

somesoni2 · ‎08-03-2016

Give this a try

your base search | bucket span=1d _time | stats dc(UserID) as dailyUniq count(UserID) as DailyActive by _time | timechart span=1mon avg(dailyUniq) as DailyAvgUniqUsers sum(DailyActive) as TotalActive | eval Month=strftime(_time,"%B") | eval Stickiness=round(DailyAvgUniqUsers/TotalActive,2) | table Month Stickiness

Update#1
This should givev stickiness as ratio of Daily Average Uniq Users and Unique users for the month. Will be little slower than previous one.

your base search | bucket span=1d _time | eventstats dc(UserID) as dailyUniq by _time
| timechart span=1mon avg(dailyUniq) as DailyAvgUniqUsers dc(UserID) as MonthlyUniq | eval Month=strftime(_time,"%B") | eval Stickiness=round(DailyAvgUniqUsers/MonthlyUniq,2) | table Month Stickiness

View solution in original post

How do I get Unique users per day and per month in one query and then divide them

.conf25 Registration is OPEN!

Detecting Cross-Channel Fraud with Splunk

Splunk at Cisco Live 2025: Learning, Innovation, and a Little Bit of Mr. Brightside

Are you a member of the Splunk Community?

How do I get Unique users per day and per month in one query and then divide them

.conf25 Registration is OPEN!

Detecting Cross-Channel Fraud with Splunk

Splunk at Cisco Live 2025: Learning, Innovation, and a Little Bit of Mr. Brightside