The following article describes the ‘funnel analysis’ method, its usage and how to implement it in Sisense.
‘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 thru the UX funnel, amount of candidates during a recruitment process, etc.
To explain better the usage of this analysis, let’s take for example a general sale process. In this example, for a lead to become a sale, there is a process one must to go through:
· First, the sales department identify lead to sale.
· After getting deeper to the sale process and few analysis and actions will be taken, some of the leads will become qualified lead.
· Going further with the process, part of the qualified leads are being identified as opportunities.
· Finally, there are few that become a won deal.
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.
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:
Solution: Creating dates table
Let’s assume that your source data looks something like this:
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 a name to the table:
Step 3 - 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.
This is how the query should look (the query can be found here - Dates Table Query):
Step 4 - Eventually we’ll connect the new table to the Accounts table by the ‘Lead id’ field:
And that’s it!
To best visualize the funnel process we can use the ‘Funnel Chart’:
· More information about data transpose - https://support.sisense.com/entries/60647560-Data-Transpose-Guide
· How to implement ‘Funnel Chart’ - https://sisensesupport.zendesk.com/entries/61346180-D3-Funnel-Chart