topic Re: How to create a funnel using a subsearch? in Splunk Search

How to create a funnel using a subsearch?

Valisha2005 — Tue, 29 Sep 2020 18:47:11 GMT

Hello,
I am trying to create a funnel that first count the number visits to page one and out of those how many went to page2? So if the first result produces 50, I would like to know out of that 50 how many visited the second page?

index="main" sourcetype=abc event_name=spa | rename page.url as url | search url="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support%23select-issue" OR url = "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support" | stats dc(session.id) as "customer_support_page_uniqie_visitspage1"

index="main" sourcetype=abc event_name=spa | rename page.url as url | search url="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support%23select-options2" OR url = "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_options" | stats dc(session.id) as "customer_support_page_uniqie_visitspage2"

Re: How to create a funnel using a subsearch?

elliotproebstel — Tue, 27 Mar 2018 19:29:40 GMT

The more efficient way to get these numbers out of Splunk isn't to create a funneled effect using subsearches (although that is possible, and tempting!) but rather to gather all events that would be used in either count and then use stats to calculate the numbers you seek. I'd do it like this:

index="main" sourcetype=abc event_name=spa page.url="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support%23select-issue" OR page.url = "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support" OR page.url="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support%23select-options2" OR page.url = "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_options"| eval visited_1=if('page.url'="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support%23select-issue" OR 'page.url'="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support", 1, NULL), visited_2=if('page.url'="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support%23select-options2" OR 'page.url'="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_options", 1, NULL) 
| stats max(visited_1) AS visited_1, max(visited_2) AS visited_2 BY session.id 
| eval visited_both=if(isnotnull(visited_1) AND isnotnull(visited_2), 1, 0) 
| stats sum(visited_1) AS total_visited_first_page sum(visited_both) AS total_visited_both_pages

Re: How to create a funnel using a subsearch?

richgalloway — Tue, 27 Mar 2018 19:39:29 GMT

You can combine the two searches with stats like this.

index="main" sourcetype=abc event_name=spa | rename page.url as url | search (url="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support%23select-issue" OR url = "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support" OR url="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support%23select-options2" OR url = "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_options") | stats count(eval(match(url, "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support%23select-issue") OR match(url, "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support"))) as page1 count(eval(match(url, "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support%23select-options2") OR match(url = "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_options"))) as page2 by session.id

Re: How to create a funnel using a subsearch?

elliotproebstel — Wed, 28 Mar 2018 16:40:46 GMT

Thanks. I knew there had to be a way to combine those, but I couldn't seem to muster it.

Re: How to create a funnel using a subsearch?

richgalloway — Tue, 03 Apr 2018 12:34:53 GMT

@elliotproebstel If your problem is resolved, please accept an answer to help future readers.

Re: How to create a funnel using a subsearch?

elliotproebstel — Wed, 04 Apr 2018 02:06:50 GMT

Thanks, but I'm guessing you were trying to tag @Valisha2005 on this, since it wasn't my question. So @Valisha2005 - if either of our answers solved your problem, please accept one to help others. 🙂