cancel
Showing results for
Did you mean:

# The Funnel Analysis

Community Team Member

## 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:
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.

## 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:
Image 2. Data before & after transpose

## Solution

Let’s assume that your source data looks something like this:
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’:
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],
[Creation date] AS Date
FROM [Accounts]
WHERE [Creation date] IS NOT NULL

UNION
[Touch base date] AS Date
FROM [Accounts] WHERE [Touch base date] IS NOT NULL

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

UNION
'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:
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

Developers Group:

Product Feedback Forum: