Splunk Search

## How to calculate a funnel (with less effort)?

Motivator

Hi,

let's say I want to create a 5 step-funnel for customers depending on their max step.

My first approach would be like

...
| stats max(funnel_step) AS max_step BY customer
| stats dc(eval(if(max_step>=1, customer, null()))) AS customers_finished_step1,
dc(eval(if(max_step>=2, customer, null()))) AS customers_finished_step2,
dc(eval(if(max_step>=3, customer, null()))) AS customers_finished_step3,
dc(eval(if(max_step>=4, customer, null()))) AS customers_finished_step4,
dc(eval(if(max_step>=5, customer, null()))) AS customers_finished_step5

When a customer only triggered step5, he should count for all steps before as well. That's why I don't use a simple count by funnel_step.

For funnels with much more steps the approach above would result in very long searches. Is there a better way to avoid all the dc(eval(if(max_step>=x, customer, null()))) parts?

Tags (2)
1 Solution
Revered Legend

Give this a try

`````` ...
| stats max(funnel_step) AS max_step BY customer
| stats count by max_step | eval max_step="customers_finished_step".max_step
| accum count | eval temp=1
| xyseries temp max_step count
``````

UPdate

To get a row for all possible steps, you can try like this for 5 possible steps (just update the limit [N+1] in append subsearch-mvrange command)

`````` ...
| stats max(funnel_step) AS max_step BY customer
| stats count by max_step
| append [| gentimes start=-1 | eval max_step=mvrange(1,6) | table max_step | mvexpand max_step | eval count=0 ]
| stats max(count) as count by max_step
| eval max_step="customers_finished_step".max_step
| accum count | eval temp=1
| xyseries temp max_step count
``````
Revered Legend

Give this a try

`````` ...
| stats max(funnel_step) AS max_step BY customer
| stats count by max_step | eval max_step="customers_finished_step".max_step
| accum count | eval temp=1
| xyseries temp max_step count
``````

UPdate

To get a row for all possible steps, you can try like this for 5 possible steps (just update the limit [N+1] in append subsearch-mvrange command)

`````` ...
| stats max(funnel_step) AS max_step BY customer
| stats count by max_step
| append [| gentimes start=-1 | eval max_step=mvrange(1,6) | table max_step | mvexpand max_step | eval count=0 ]
| stats max(count) as count by max_step
| eval max_step="customers_finished_step".max_step
| accum count | eval temp=1
| xyseries temp max_step count
``````
Legend

Oooooooh `mvrange`. I like it. That's going to simplify a lot of stuff I've been doing the hard way.

Legend

Nice and simple, with two issues...
1) Order inverse, and depends on lexicography order.

2) Skips any intervening levels if no one fell out of the funnel there.

The second is a useful feature, so you should consider using this method in some contexts. The first issue can be solved as below...

`````` | stats count by max_step
| eval max_step="customers_finished_step".case(max_step=0,"00",max_step<10,"0".max_step,true(),max_step)
| sort 0 -max_step
| accum count | eval temp=1
| xyseries temp max_step count
``````

This brought up a question of what to do if a customer completed zero steps. It seems like there ought to be special handling for that, since you don't want to report anyone in that category who DID complete a higher level

Motivator

This approach works fine so far. But you are right, it skips levels where no one fell out, which could be misleading in some cases. Any idea whether this can be fixed in this search?

In my definition of a funnel the users enters it, when he completes the first step. So I don't care about users with zero steps in this kind of analysis.

Legend

My code samples do not have that feature of skipping steps where no one fell out. Like I said, it's a useful feature in some scenarios, but if it doesn't match your need, then you can use my second model. It's slightly less concise, but it solves the issue generally for up to 50 steps, and if you have a crazy-complicated funnel, you can increase the potential number of available steps to 99 or 999 with only a 1-line change.

Change line 16 to read as follows for up to 99 steps...

``````| eval fieldname="MyStep".case(max_step=0, "00", max_step<10, "0".max_step, true(), max_step)
``````

Change line 16 to read as follows for up to 999 steps...

``````| eval fieldname="MyStep".case(max_step=0, "000", max_step<10, "00".max_step, max_step<100, "0".max_step, true(), max_step)
``````
Legend

After that first `stats` command, your customers are all distinct anyway, so you can just `count` them rather than using `dc`.

This code isn't significantly less complex than your existing code, but it might be more readable to most people...

``````...
| stats max(funnel_step) AS max_step BY customer
| eval step1=if(max_step<1,0,1), step2=if(max_step<2,0,1), step3=if(max_step<3,0,1), step4=if(max_step<4,0,1), step5=if(max_step<5,0,1)
| stats sum(step*) as customers_finished_step*
``````

A more general solution is necessarily more complicated, but here it is...

``````| makeresults count=8 | streamstats count as customer | eval customer="cust".customer
| eval funnel_step=random()%15

| rename COMMENT as "The above section creates some test data, and the below cuts it to max level per customer."
| stats max(funnel_step) AS max_step BY customer

| rename COMMENT as "This section figures out the max number of steps for any customer (up to 50) and adds that many MyStep* fields to some temporary records."
| eval rectype="customer"
| appendpipe
[ | stats max(max_step) as overall_max_step
| append
[| makeresults count=50 | streamstats count as recCount ]
| eventstats max(overall_max_step) as overall_max_step
| where (recCount<=overall_max_step)
| table recCount
| eval fieldname="MyStep".if(length(recCount)=1,"0","").recCount
| eval {fieldname}=0
| fields MyStep*
]

| rename COMMENT as "This section adds the fields to each record and then kills the temporary records."
| eventstats min(MyStep*) as MyStep*
| where rectype=="customer"

| rename COMMENT as "This section calculates your funnel."
| foreach MyStep* [ eval <<FIELD>>=if(max_step < tonumber(<<MATCHSTR>>),0,1)]
| stats sum(MyStep*) as customers_finished_step*
``````
Get Updates on the Splunk Community!

#### 3 Ways to Make OpenTelemetry Even Better

My role as an Observability Specialist at Splunk provides me with the opportunity to work with customers of ...

#### What's New in Splunk Cloud Platform 9.2.2406?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2406 with many ...

#### Enterprise Security Content Update (ESCU) | New Releases

In August, the Splunk Threat Research Team had 3 releases of new security content via the Enterprise Security ...