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’:
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 NULLStep 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
