I am creating a funnel report based on total customer sessions on each url by taling sessionid
www.abc.com www.abc.com/page1 www.abc.com/page1/page2 www.abc.com/page1/page2/page3
I want the number people went through each page with time chart for eg:
Time |Total Session |www.abc.com |www.abc.com/page1 |www.abc.com/page1/page2 |www.abc.com/page1/page2/page3
09:00 | 10 | 10 | 8 | 5 | 2
10:00 | 15 | 15 | 12 | 9 | 7
11:00 | 11 | 11 | 10 | 6 | 5
I have achieved the above using lookup
index=abc sourcetype=test1
| timechart span=1h count as "TotalSession"
|appendcols [index=abc sourcetype=test1 www.abc.com
| lookup filename.csv SessionID |outputnew sessionid as x_sessionid
| dedup x_sessionid
| timechart span=1h count as "www.abc.com"]
|appendcols [index=abc sourcetype=test1 www.abc.com/page1
| lookup filename.csv SessionID |outputnew sessionid as x_sessionid
| dedup x_sessionid
| timechart span=1h count as "www.abc.com/page1"]
|appendcols [index=abc sourcetype=test1 www.abc.com/page1/page2
| lookup filename.csv SessionID |outputnew sessionid as x_sessionid
| dedup x_sessionid
| timechart span=1h count as "www.abc.com/page1/page2"]
|appendcols [index=abc sourcetype=test1 www.abc.com/page1/page2/page3
| lookup filename.csv SessionID |outputnew sessionid as x_sessionid
| dedup x_sessionid
| timechart span=1h count as "www.abc.com/page1/page2/page3"]
the lookup file is scheduled every 5 mins to append all the sessionid irrespective of page url.
Problem: Cannot use Join as it has limitations of 50K records
The above approach is slow very slow and it is trying to run all the query.
Need a fine tuned optimized solution.
