cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member

mceclip1.png Analytical Need

‘Funnel analysis’ describes process flow, where in its beginning the number that we measure is relatively big, and in each step within the process this number is getting smaller along the way till the end point.
The most common example for the funnel analysis usage is for sale lead from the point it was recognized as a lead till the sale point (conversion analysis). There are of course some other cases when this analysis can describe processes very well – user engagement to a mobile app through the UX funnel, amount of candidates during a recruitment process, etc.
Example: general sale process. For a lead to become a sale, there is a process one must to go through:
Funnel.PNG
Image 1. Funnel Process
Within each step, some leads are dropping, which had been invested on. The objective of the sales department is to have as smaller difference as possible between the amounts of leads to the won deals to reduce wasted investments.

mceclip2.png Modeling Challenge

When the source data is flatted, for each lead all information for each of the steps is gathered in one record. Therefore, it’s pretty challenging to count the number of leads I had in a specific stage for every given time frame (for example: number of open opportunities per quarter).
To overcome this issue we’ll transpose the date fields to be under the same field, so our data structure will look like this:
alt
Image 2. Data before & after transpose

mceclip3.png Solution

Let’s assume that your source data looks something like this:
alt
Image 3. Original Data structure
We’ll need to transpose the dates’ fields in order to have all required dates within the same field.
To do so, we should create a new table which takes its data from the ‘Account’ table:
Step 1 - Click on the Add Data button and select the ‘Custom SQL Expression’:
alt
Image 4. Add Cutsom Query
Step 2 - Set the relevant query:
a.     We’ll create a new field that will contain all different steps and name it ‘Step’.
b.     This ‘Step’ field will point the relevant date for each lead id and step.
c.     The level of granularity will per ‘Lead id’ and Step. We’ll connect the new table with the Accounts table by the Lead id.
SQL:
SELECT [Lead id], 
'Lead' AS Step,
[Creation date] AS Date 
FROM [Accounts] 
WHERE [Creation date] IS NOT NULL 

UNION 
SELECT [Lead id], 
'Qualified lead' AS Step,
[Touch base date] AS Date 
FROM [Accounts] WHERE [Touch base date] IS NOT NULL 

UNION 
SELECT [Lead id], 
'Opportunity' AS Step, 
[Presale start date] AS Date
FROM [Accounts] WHERE [Presale start date] IS NOT NULL 

UNION 
SELECT [Lead id], 
'Won deal' AS Step, 
[Signoff date] AS Date 
FROM [Accounts] 
WHERE [Signoff date] IS NOT NULL
Step 3 - Eventually we’ll connect the new table to the Accounts table by the ‘Lead id’ field:
alt
 Image 5. Funnel Chart
And that’s it!
To best visualize the funnel process we can use the ‘Funnel Chart’ Widget.

Attachments

 - 328 KB - Funnel.ecdata
 - 44 KB - Funnel.dash
Version history
Last update:
‎03-02-2023 08:51 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Developers Group:

Product Feedback Forum:

Need additional support?:

Submit a Support Request

The Legal Stuff

Have a question about the Sisense Community?

Email [email protected]

Share this page: