Using searches to get a percentage or, Daily uniques as a percentage of monthly uniques.

[cvervais]

I can easily come up with searches to find the number of daily unique users and monthly unique users. But, someone wants to see it as a percentage, for example on Monday X% of monthly visitors logged in. So, I'm essentially looking for a way to have the count of search A be the numerator and the count of search B be the denominator.

Here's the search base I use:

index=main hostname=*PROD extra.user_id=* NOT extra.csv.cohort_id=* NOT extra.user_id=no_user  NOT (extra.template=VerifyEmailAddress OR extra.template=LostPassword)| dedup extra.user_id date_mday |timechart count

I just change the date_mday to date_month to go from daily uniques to monthly uniques.

[martin_mueller · Accepted solution]

Something like this:

index=main hostname=*PROD extra.user_id=* NOT extra.csv.cohort_id=* NOT extra.user_id=no_user  NOT (extra.template=VerifyEmailAddress OR extra.template=LostPassword)
| rename extra.user_id as extra_user_id
| eventstats dc(extra_user_id) as monthly_distinct_users by date_month date_year
| timechart span=1d dc(extra_user_id) as daily_distinct_users avg(monthly_distinct_users) as monthly_distinct_users
| eval percentage = round(daily_distinct_users * 100 / monthly_distinct_users, 2) . "%"

First I rename the field, because having a dot in a field name is asking for trouble - for example, eval will interpret that as the concatenation of two fields extra and user_id.
Second, calculate the monthly distinct count of users.
Third, calculate the daily distinct count of users and carry over the monthly dc.
Last, calculate percentage for each day.

[cvervais]

That seems to do it enough that I think I can take it from here. I may change it to look back 30 days rather than hard month boundaries. Awesome though, and thanks for the tip on the dot in a field name. We log in JSON and the dot carries over from that.