All Apps and Add-ons
Highlighted

Help - How to create a funnel chart based on pre-defined steps??

Explorer

Hi Team,

I have data like below. and I want to create a funnel chart for knowing the conversion rate for 'Add to Pool'. The pre-defined funnel path is :

Step1- Open Page ->Step2 - Search->Step3 - Facted Search ->Step4 Export.

12:00:00, SID1, CustomerA, UserA, moduleA, pageA, Open PageA
12:00:01, SID1, CustomerA, UserA, moduleA, pageA, Search
12:00:02, SID1, CustomerA, UserA, moduleA, pageA, Faceted Search
12:00:03, SID1, CustomerA, UserA, moduleA, pageA, Export
12:00:01, SID2, CustomerB, UserB, moduleA, pageA, Open PageA
12:00:02, SID2, CustomerB, UserB, moduleA, pageA, Search
12:00:03, SID2, CustomerB, UserB, moduleA, pageA, AddtoPool
12:00:01, SID3, CustomerC, UserC, moduleA, pageA, Open PageA
12:00:02, SID3, CustomerC, UserC, moduleA, pageA, Search
12:00:03, SID3, CustomerC, UserC, moduleA, pageA, Facted Search
12:00:04, SID3, CustomerC, UserC, moduleA, pageA, Export

I attach the funnel chart created by python . Can splunk language achieve the same chart? I have already install the funnel visualization and calculate the count of each steps, but how let the funnel chart displayed according to the order of

Step1- Open Page ->Step2 - Search->Step3 - Facted Search ->Step4 Export.

alt text

0 Karma
Highlighted

Re: Help - How to create a funnel chart based on pre-defined steps??

Legend

@cheriemilk since Funnel Visualization on Splunkbase has not been updated for a long time and has been marked as Archived, I would suggest you to build your own custom visualization.

Meanwhile if you want Simple XML JS Extension of Table to show up results as Funnel Data Bars, you can try the following run anywhere example as per scenario provided by you. Refer to one of my older answers for understanding the concept of Table Data bars: https://answers.splunk.com/answers/694309/how-do-you-create-the-below-chart-in-splunk.html

alt text

Following are the steps I have taken

  1. Created a table with ID tableWithFunnelDataBars
  2. Got the data in the form expected as your question. Sorted with highest value first, initial percent, previous percent and total percent calculated as per your question.
  3. Created a label column for simplicity and better representation of all required data. You can use Tooltip text as well (controlled via JS). For simplicity's sake I have only displayed Initial Percent on Tooltip text which shows up on hovering over any data bar. Refer to screenshot. Since data column has all other required options as well, you can extend tooltip to show all values and get rid of label column.
  4. The originalData column has been added for displaying information that will be available to Simple XML JS extension which plotting Data Bar and Tooltip text. (minimum data required is the Value of each data bar and its initial percent). PS: Needless to say since it is Simple XML Table Extension the sorting by any column is applicable hence the Funnel can be inverted (as shown in the example) or even Jumbled if sorted based on server field for example.

Following is the required Simple XML for the run anywhere example:

<dashboard script="table_with_funnel_data_bar.js" theme="dark">
  <label>Table with Funnel Data Bars</label>
  <row>
    <panel>
      <html depends="$alwaysHideCSSStylePanel$">
         <style>
           .data-bar-cell{
             padding: 4px 8px;
           }
           .data-bar-tooltip{
               height: 16px;
               min-width: 0px;
               text-align:center;
               color:white;
           }          
           .data-bar-tooltip .data-bar-tooltip-text{
             visibility: hidden;
             background-color: black;
             color: #fff;
             text-align: center;
             border-radius: 6px;
             padding: 2px 5px;
             /* Position the tooltip */
             position: relative;
             top:-15px;
             z-index: 1;
           }
           .data-bar-tooltip:hover .data-bar-tooltip-text {
               visibility: visible;
           }
           .data-bar-tooltip.initial{
               background: #007ABD;
               height: 50px;
               padding-top: 30px;
           }
           #tableWithFunnelDataBars th[data-sort-key=data] {
               width: 40% !important;
           }
           #tableWithFunnelDataBars th[data-sort-key=label],
           #tableWithFunnelDataBars th[data-sort-key=originalData]{
               width: 20% !important;
           }
         </style>
       </html>
      <table id="tableWithFunnelDataBars">
        <search>
          <query>| makeresults count=5
| fields - _time
| streamstats count as server
| eval server="server".server
| eval count=random(), count=substr(count,1,3)
| sort count
| eval delta=300
| accum delta
| eval time=_time-delta
| fields - delta
| rename time as _time
| eventstats sum(count) as total max(count) as initial
| reverse
| table server count total initial
| streamstats last(count) as prev current=f window=1
| eval initial_perc=round((count/initial)*100,1),
       prev_perc=if(isnull(prev),100.0,round((count/prev)*100,1)),
       total_perc=round((count/total)*100,1)
| eval data=count."|".initial_perc."|".prev_perc."|".total_perc
| eval label=count."|".initial_perc."% of initial|".prev_perc."% of previous|".total_perc."% of total"
| eval label=split(label,"|")
| table server data label
| eval originalData=data</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
      </table>
    </panel>
  </row>
</dashboard>

Following is the code for table_with_funnel_data_bar.js JS file for Simple XML Extension:

 require([
     'jquery',
     'underscore',
     'splunkjs/mvc',
     'views/shared/results_table/renderers/BaseCellRenderer',
     'splunkjs/mvc/simplexml/ready!'
 ], function($, _, mvc, BaseCellRenderer) {
     var DataBarCellRenderer = BaseCellRenderer.extend({
         canRender: function(cell) {
             return (cell.field === 'data');
         },
         render: function($td, cell) {
             var value = parseInt(cell.value.split("|")[0]);
             var initial_percent = parseInt(cell.value.split("|")[1]);
             var left_right_percent=((100-initial_percent)/2).toFixed(1);

             $td.addClass('data-bar-cell').html(_.template(
                '<div class="data-bar-container" style="display:flex">'+
                   '<div class="data-bar-tooltip left" style="width:<%- left_right_percent %>%;">'+
                   '</div>'+
                   '<div class="data-bar-tooltip initial" style="width:<%- initial_percent %>%;"><%- value %>'+
                      '<span class="data-bar-tooltip-text neutral"><%- initial_percent %>%</span>'+
                   '</div>'+
                   '<div class="data-bar-tooltip right" style="width:<%- left_right_percent %>%;">'+
                   '</div>'+
                '</div>', {
                 left_right_percent: left_right_percent,
                 initial_percent: initial_percent,
                 value: value
             }));
         }
     });
     mvc.Components.get('tableWithFunnelDataBars').getVisualization(function(tableView) {
         tableView.addCellRenderer(new DataBarCellRenderer());
     });
 });



| eval message="Happy Splunking!!!"


Highlighted

Re: Help - How to create a funnel chart based on pre-defined steps??

Legend

@cheriemilk were you able to try and confirm the above solution?




| eval message="Happy Splunking!!!"


0 Karma