Marketing leaders are under more pressure than ever to prove the value of their efforts. It’s up to sales teams to capitalize on the opportunities that marketing generates. And to succeed, both teams need clear, consistent insights and understanding.
It’s the job of analysts and business intelligence (BI) team members to draw out the insights these teams are looking for. And in our opinion, if you need to share meaningful information with teams who might not have extensive technical skills, look no further than the humble funnel chart.
Funnel charts are an easy way to understand marketing performance at-a-glance because they clearly show the progression of leads through the sales pipeline. You can see how many leads have reached each stage of the sales cycle to date.
This provides two important insights to marketing and sales teams. First, of course, you can quickly understand rough conversion rates based on the number of leads that have progressed. But funnel charts can do even more—they help identify what stages of your sales cycle may need closer examination and analysis. Maybe your lead scoring criteria need to be revisited. Or you could be following up on the wrong leads. Funnel charts ensure teams optimize efficiently and across functions.
There’s a lot of value that can be gleaned from a funnel chart. But it’s not always a default chart type in BI tools such as Sisense. But with the SQL hack below you’ll have them working in no time.
5 Simple Steps to Create a Funnel Chart Using SQL in Sisense For Cloud Data Teams
1. Save this code as a snippet so that you can apply it to your data.
Snippets are a great way to save frequently used code, so you can avoid writing the same code repeatedly. The following code is an example of a Parameterized Snippet in Sisense For Cloud Data Teams. Name the snippet funnel_chart(table,value,series).
select
[series]
, 1.0 * (max - [values]) / 2 as left_buffer
, [values] as value
, 1.0 * (max - [values]) / 2 as right_buffer
from
(
select
max([values]) as max
from
[table]
)
, [table]
order by [series]
[series]
, 1.0 * (max - [values]) / 2 as left_buffer
, [values] as value
, 1.0 * (max - [values]) / 2 as right_buffer
from
(
select
max([values]) as max
from
[table]
)
, [table]
order by [series]
When applied to your query, this snippet creates a table with a series, left buffer, values and right buffer column. The buffer columns center your funnel.
2. If you’re using Amazon Redshift, Google BigQuery or Postgres, use a CTE to format your data so that you have all of your values in one column and series names in another column.
It helps to use a numbering system within the series names to control how they are ordered in the funnel. Otherwise, they will be ordered alphabetically.
3. Following your query, call the snippet [funnel_chart(table,value,series)] by assigning the ‘table’, ‘series’ and ‘values’ variables to match the naming conventions defined in your query.
Sample query:
with
table as (
select
'1_Leads' as series
, 1000 as value
from
crm.report
union all
select
'2_MQLs' as series
, 750 as value
from
crm.report
union all
select
'3_SQLs' as series
, 300 as value
from
crm.report
union all
select
'4_Closed_Won_Opportunities' as series
, 100 as value
from
crm.report
)
[funnel_chart(table,value,series)]
table as (
select
'1_Leads' as series
, 1000 as value
from
crm.report
union all
select
'2_MQLs' as series
, 750 as value
from
crm.report
union all
select
'3_SQLs' as series
, 300 as value
from
crm.report
union all
select
'4_Closed_Won_Opportunities' as series
, 100 as value
from
crm.report
)
[funnel_chart(table,value,series)]
4. Select the bar graph option, and check ‘horizontal bars’, ‘show all x-axis labels’ and enable ‘preserve sort order’.
5. Set the color of ‘right_buffer’ and ‘left_buffer’ to #F9F9F9 so that it will blend into the background of the chart.
This is what the final funnel chart in Sisense For Cloud Data Teams looks like:
A bonus
Enable filters in your funnel chart so the user can adjust the aggregation and date range without having to dig into the underlying SQL. Link to filter documentation here.
Final thoughts
We’ve seen a lot of success using funnel charts with our clients. They’re an easy visual to share with teams to illustrate marketing performance from lead generation all the way to closed/won. And those are insights every marketer can use.
Happy funneling!
Snippet Source:
https://support.sisense.com/hc/en-us/community/posts/360038229274-Chart-Type-Funnel-Chart
Enable filters in your funnel chart so the user can adjust the aggregation and date range without having to dig into the underlying SQL. Link to filter documentation here.
Final thoughts
We’ve seen a lot of success using funnel charts with our clients. They’re an easy visual to share with teams to illustrate marketing performance from lead generation all the way to closed/won. And those are insights every marketer can use.
Happy funneling!
Snippet Source:
https://support.sisense.com/hc/en-us/community/posts/360038229274-Chart-Type-Funnel-Chart
