How to calculate a funnel (with less effort)?

[HeinzWaescher]

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?

Thanks in advance

[somesoni2 · Accepted solution]

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

[DalJeanis]

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

[DalJeanis]

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

[HeinzWaescher]

Thanks for your answers.
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.

[DalJeanis]

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)

[DalJeanis]

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*