topic Re: Timechart: First time purchasers and total purchasers per day in Splunk Search

Timechart: First time purchasers and total purchasers per day

HeinzWaescher — Mon, 04 Nov 2013 13:13:00 GMT

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

Re: Timechart: First time purchasers and total purchasers per day

adityapavan18 — Mon, 04 Nov 2013 13:41:43 GMT

could you provide sample event for this scenario

Re: Timechart: First time purchasers and total purchasers per day

martin_mueller — Mon, 04 Nov 2013 14:20:42 GMT

Additionally, post the two single queries you would use.

Re: Timechart: First time purchasers and total purchasers per day

HeinzWaescher — Mon, 28 Sep 2020 15:11:31 GMT

Hi,

the single searches i would use here and combine externally:

to get the dc(user) per day

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

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

Re: Timechart: First time purchasers and total purchasers per day

martin_mueller — Mon, 28 Sep 2020 15:11:49 GMT

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

Re: Timechart: First time purchasers and total purchasers per day

HeinzWaescher — Fri, 08 Nov 2013 09:59:06 GMT

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?

Re: Timechart: First time purchasers and total purchasers per day

martin_mueller — Fri, 08 Nov 2013 10:31:44 GMT

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

Re: Timechart: First time purchasers and total purchasers per day

HeinzWaescher — Mon, 28 Sep 2020 15:14:07 GMT

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"

Re: Timechart: First time purchasers and total purchasers per day

martin_mueller — Fri, 08 Nov 2013 10:46:06 GMT

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

Re: Timechart: First time purchasers and total purchasers per day

HeinzWaescher — Fri, 08 Nov 2013 10:47:56 GMT

Thanks a lot 🙂