Splunk Search
Highlighted

How to create a funnel using a subsearch?

New Member

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 eventname=spa | rename page.url as url | search url="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomersupport%23select-issue" OR url = "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomersupport" | stats dc(session.id) as "customersupportpageuniqie_visitspage1"

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

0 Karma
Highlighted

Re: How to create a funnel using a subsearch?

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 
0 Karma
Highlighted

Re: How to create a funnel using a subsearch?

SplunkTrust
SplunkTrust

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 
---
If this reply helps you, an upvote would be appreciated.
Highlighted

Re: How to create a funnel using a subsearch?

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

0 Karma
Highlighted

Re: How to create a funnel using a subsearch?

SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

Re: How to create a funnel using a subsearch?

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

0 Karma