A Step-By-Step Guide To Creating Funnels in Google’s Data Studio

This is an update to a 2017 blog post, since there are a ton of new features in Data Studio that make some of the old methods unnecessary. If you really need the old post (I can’t fathom why) maybe try the Way Back Machine? 

Given so many sites have some sort of conversion funnel (whether it’s a purchase flow, a “contact us” flow or even an informational newsletter signup flow), it’s a pretty common visualization to include in analytics reporting. For those using Google’s Data Studio, you may have noticed that a true “funnel” visualization is not among the default visualization types available. (Though you may choose to pursue a Community Visualization to help.) 

The way I choose to visualize conversion funnels is by leveraging an horizontal bar chart:

To create this type of visualization, you will need: 

• A linear flow, in which users have to go through the steps in a certain order
• A dimension with a single value* (I’ll explain below) 
• A metric for each step. You could create this in several ways: 
  • Google Analytics Goal
  • Custom Metric
  • BigQuery metric
  • Data Studio data blend (up to 5 steps) 
  • Data in spreadsheet

For example, here I am using Goal Completions: 

A spreadsheet might be as simple as: 

And here I am using a data blend (basically, Data Studio’s “join”), in what I’ll call a “self-join”. Basically, I’m taking filtered data from a Google Analytics view, then joining it with the same Google Analytics view, but a different metric or filter. This is what will allow you to build a funnel where, for example: 

• Step 1 is a page (“home”)
• Step 2 is an event (“contact us”)
• Step 3 might be another page (“thank-you”) 

But remember a blend will only work if you have five funnel steps or fewer. 

* Why a dimension with a single value? For example, a dimension called “All Users” that only has one value, “All Users.”

Here’s what happens to your visualization if you try to use a dimension with multiple values: 

Basically what you want is to create a bar chart, with no dimension. But since that’s not an option, we use a dimension with a single value to mimic this. 

You can create one super fast in your Data Source in Data Studio, by creating a CASE statement similar to this: 

CASE WHEN REGEXP_CONTAINS(DeviceCategory, ".*") then "All Users" ELSE "All Users" END

And don’t try to make your life easy by choosing “year”, thinking that “well it’ll only have one value, this year!” — when Jan 1 rolls around and all your funnels break, you’ll be annoyed you didn’t take the extra two seconds.

A step-by-step walkthrough:

Step 1: Create your bar chart

Our visualization is then a horizontal bar chart, with our “single value dimension” as the dimension, and our steps as the metrics. 

2. Change the colors to be all the same color

3. Hide axes (both X and Y)

4. Add data labels

5. Remove legend and gridlines

6. Add text boxes, to label the steps

Voila! That’s your (raw numbers) funnel. But you probably want conversion rate too, right? 

You’re going to want to create calculations for each step of the funnel: 

Step 1%:

SUM(Step 1)/SUM(Step 1)

Step 2%:

SUM(Step 2)/SUM(Step 1)

Step 3%:

SUM(Step 3)/SUM(Step 1)

Purchase%:

SUM(Step 4)/SUM(Step 1)

This will give you the conversion rate from the first step of the funnel. (And yes, Step 1 % will be 100%, it’s supposed to be!) 

Side note: I tend to put the % sign in the formula, so it makes it easy for me to search for it in the list of metrics later.

And make sure you format as a percentage, so you don’t have to constantly adjust it in the chart. 

Note that you could also add a “Step-to-Step” conversion as well: 

Step 1% s2s (This formula is actually the same, so I don’t bother creating another one) 

SUM(Step 1)/SUM(Step 1) 

Step 2% s2s (This formula is actually the same, so I don’t bother creating another one) 

SUM(Step 2)/SUM(Step 1) 

Step 3% s2s (This is a different formula to the one above)

SUM(Step 3)/SUM(Step 2) 

Purchase% s2s (This is a different formula to the one above)

SUM(Step 4)/SUM(Step 3) 

I use something like “s2s” to denote that that’s the formula with the previous step as the denominator, versus the formula with the first step as the denominator.

Now you’ll follow the steps again, but build a second bar chart with your conversion rate metrics, and/or your step-to-step conversion rates. 

That’s it!

Voila! Look at your lovely funnel visualization: 

The hardest part is getting your data into the right shape (e.g. having a metric for each step.)

And it used to be a lot harder, before some newer features of Data Studio! (In my day, we used to have to create funnels for three miles in the snow…) 

If you have any questions, please don’t hesitate to reach out to me via Twitter, Linked In, email or Measure Chat.