Splunk Search

Timechart: First time purchasers and total purchasers per day

HeinzWaescher
Motivator

Hi everyone,

i would like to create a timechart for unique users who made at least one action="purchase" a day. I need the total count of unique purchasers and the count of unique users who made their first lifetime purchase on that day.

01.01.2013 , 10000 , 2000

This means:

On 01.01.2013 10000 unique users did a purchase. Out of these 10000, 2000 unique Users did their first lifetime purchase on that day. I would be able to create something like with two different searches and write the outputs in a single excelsheet afterwards. But it would be great to have it in one single splunk search.

Thanks for help

Heinz

Tags (1)
0 Karma

HeinzWaescher
Motivator

Hi,

the single searches i would use here and combine externally:

  1. to get the dc(user) per day

| timechart dc(eval(if(action="purchase", user, null())))

  1. to get every purchasers first day

| stats min(eval(if(action="purchase", __time, null()))) by user | eval day=strftime(_time, "%d.%m.%Y")

A count per day could be calculated out of the second search and could be added to the first table.

Best

Heinz

0 Karma

HeinzWaescher
Motivator

Thanks a lot 🙂

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Yep, _time is the default field for bin, and bin is an alias for bucket 🙂

0 Karma

HeinzWaescher
Motivator

Ah ok, so it seems to have the same effect as | bucket span=1d _time |

I've got the "first_time" as timestamp in seconds. So I have to use the bin command here as well, to be able to compare both timestamps. My approach would be this now and it seems to work:

| bin span=1d first_time
| bin span=1d _time
| timechart span=1d dc(user) AS Total_Users, dc(eval(if(_time=first_time, user, null()))) AS "New Users"

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

... | bin span=1d | ...

After that _time will be in unixtime (seconds since 1970) rounded down to midnight. If your first_time is a date as a string (ie "2013-11-08") you can use strftime(_time, "%Y-%m-%d") to convert the timestamp to the same format.

HeinzWaescher
Motivator

Thanks for the input. I got some questions regarding this:

What does "bin by day" mean?
And in what kind of format do I need "first day" here to compare it to _time? Unixtime?

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Without thinking about precise syntax, here's an approach that should work:

  • start with your base search before these two reporting pipes
  • replace the stats call with an eventstats, computing the first day by user
  • bin by day
  • stats dc(blah) count(eval(_time==first_day))
  • maybe some cosmetics
0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Additionally, post the two single queries you would use.

0 Karma

adityapavan18
Contributor

could you provide sample event for this scenario

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