5 Simple Steps to Create a Funnel Chart Using SQL in Periscope

periscope data post-09.png

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 Periscope. But with the SQL hack below you’ll have them working in no time.

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 Periscope. Name the snippet funnel_chart(table,value,series).

 , 1.0 *  (max - [values]) / 2 as left_buffer
 , [values] as value
 , 1.0 * (max - [values]) / 2 as right_buffer
     max([values]) as max
 , [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.

Pro tip: UNIONs can be helpful in transposing your data if it’s not set up as described above. You’ll want to end with an aggregated table, like the one below, with one value per series or row. Take performance into consideration when using UNIONs.

periscope data post-01.png

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:

  table as (
      '1_Leads' as series
      , 1000 as value
    union all
      '2_MQLs' as series
      , 750 as value
    union all
      '3_SQLs' as series
      , 300 as value
    union all
      '4_Closed_Won_Opportunities' as series
      , 100 as value

4. Select the bar graph option, and check ‘horizontal bars’, ‘show all x-axis labels’ and enable ‘preserve sort order’.

Using SQL in Periscope

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 Periscope 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 periscope funnel 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:

Post by Erin Hayes

Brian Jeffcock