A Step-By-Step Guide To Creating Funnels in Google’s Data Studio
A Step-By-Step Guide To Creating Funnels in Google’s Data Studio
For those exploring Google’s Data Studio (their beta “Tableau competitor”, that integrates with Google Analytics and many other data sources) you may have noticed there’s no easy way to create a funnel visualization in Data Studio. This post will guide you through a way I came up with to do this.
I first encountered this challenge when working with a client whose conversion funnel is 13 steps long (yes, seriously) and we needed to visualize this in Data Studio.
First… a few notes about what didn’t work. (Don’t care about this? Just skip ahead!)
What didn’t work, and why
Unfortunately, my first attempts did not work.
You are unable to bring the funnel steps of a Google Analytics Destination Goal “Funnel” (this report) into Data Studio (nor is it accessible via the API.)
The “fix” for this would be have a goal for each step, rather than one goal with a goal funnel.
However… even if you had a goal setup for each individual funnel step, you still can’t just use a bar chart in Data Studio, as these are limited to five steps.
(Note: This is a limitation of Custom Funnels, as well. You can only have a maximum of five steps, so for clients with long conversion flows, this is equally unhelpful.)
In the case of my problem (the client with 13 steps) this definitely wouldn’t work. On top of this, you also can’t calculate anything off a bar chart just showing multiple metrics – so even if you have less than five steps, your “funnel” would just have raw numbers, like this:
This would require your business users to constantly whip out their TI-83s and calculate the conversion percentage – which I think we can agree, is not very helpful.
Attempts to use, say, a pages report (assuming your funnel could be presented in a simple pages report) unfortunately would work similarly. You could show multiple pages, but the output would show only raw numbers (no conversion calculations.)
So, what did work?
A two prong approach, combining one set of data for the percentages and another for the visualization of the conversion at each step:
1. Show the percentage conversion at each step
- We will use Scorecard metrics to visualize the percentage conversion.
- These are based on goals to define each step of the funnel that you want to visualize, with calculated field based on those goals
2. Visualize the conversion drop-off at each step
- We use a horizontal bar chart, based on pages for this.
- The bar chart will be based on your Pages data*, tidied up via the CASE formula.
* Note: I’m assuming here that your funnel steps are also tracked as page views (either real pages, or virtual.) If they are not (for example, if they’re tracked as events) this solution may still be possible, but you would use your event data for the second part.
In short, this method will give you this:
So let’s see how the sausage is made. (Ewww.)
1. Show the percentage conversion
If you don’t have this already, set up a goal* for each step of your funnel.
* Note: If you don’t already have these in place, this method will only work moving forward, as goals are not retroactive.
Let’s keep in simple, and assume we have a flow with three steps, and a completion page.
Goal 1: Destination Goal; Page matches reg ex signup.1.of.3
Goal 2: Destination Goal; Page matches reg ex signup.2.of.3
Goal 3: Destination Goal; Page matches reg ex signup.3.of.3
Goal 4: Destination Goal; Page matches reg ex signup.complete
You have some options about how you do this, depending on what you want to visualize. (For example, do you want to visualize conversion from Total Sessions? From step to step? From the first step? Etc.)
To show conversion from Total Sessions through to completion, you’d create your formulas based on Sessions as the denominator:
Goal 1/Sessions Goal 2/Sessions
To show conversion through the funnel, starting at Step 1, you’d create your formulas with Step 1 as your denominator. For example:
Goal 1/Goal 1 (Step 1 would therefore show as 100%) Goal 2/Goal 1 Goal 3/Goal 1
To show Step to Step conversion, you’d create your funnels based on the previous step. For example:
Goal 1/Sessions Goal 2/Goal 1 Goal 3/Goal 2 Goal 4/Goal 3
Be sure to format them as “Percent” under Data Sources:
Pro Tip: If you don’t want to show decimals in your conversion rate, use this formula:
ROUND((Signup 1 of 3/Sessions), 2)
This will round off your calculated field.
(I’ll now move forward assuming our funnels are using Sessions for the denominator.)
Once you have created the calculated field, create a “Scorecard” measure for each:
2. Create your conversion funnel visualization
Once you have a scorecard metric for each step, you then want to use the CASE formula in Data Studio to rewrite your URLs to something clean and friendly.
A CASE formula allows you to rewrite the values in your dimensions, and essentially creates a new dimension, that contains your rewritten values. (If you’ve never used this, read more about it here first.)
The CASE formula can be useful to rewrite the values, but the rewriting can also be used to group values, by rewriting multiple things with the same name.( For example, you could rewrite the “Country” dimension in GA, so that it rewrites both the USA and Canada as “North America”. Using the rewritten field would then show “North America” and the summarized metrics.)
Note: You can skip this step if your URLs are already very simple and will look fine on your chart, but most of my clients benefit from using the CASE formula to clean up via a rewrite.
For example, let’s say your Page Names look like this
You can use the CASE formula to simply show
You can also collapse multiple URLs, such as:
/us/signup/1-of-3.html /uk/signup/1-of-3.html /de/signup/1-of-3.html
Signup Step 1
Here is an example of a CASE formula that would work here:
CASE WHEN REGEXP_MATCH(Page, ".*signup.*1.of.3.*") THEN "Signup Step 1" WHEN REGEXP_MATCH(Page, ".*signup.*2.of.3.*") THEN "Signup Step 2" WHEN REGEXP_MATCH(Page, ".*signup.*3.of.3.*") THEN "Signup Step 3" WHEN REGEXP_MATCH(Page, ".*signup.*complete.*") THEN "Signup Complete" ELSE "Other" END
This will rewrite your Page Names to be “Signup Step 1”, “Signup Step 2” (etc.) Anything that is not a Signup URL will fall under “Other.”
One extra benefit: One additional benefit of doing the rewrite with the CASE formula is that it allows you to sort based on the names, and (if you use step numbers in your names) therefore sort by the actual order of the flow. One of my clients has a conversion funnel where one of the middle steps of the funnel sometimes has higher Unique Pageviews than the previous step, due to the ability to save their cart and return to it on another computer. The CASE formula means I can rewrite their URLs to include the “Step Number”, so that I can sort using the Dimension Name and keep the steps in order. If I did not do this, I would have to sort by the Metric (which would put the steps out of order, since that middle step gets more traffic) or by the alphabetical order of the URLs, neither of which would help me much.
So now that we have used our CASE formula to tidy up the Page URLs, we now create our horizontal bar chart, using that newly created dimension.
Settings for our bar chart:
- Dimension: Our “Signup Flow Page” dimension, which uses our CASE formula.
- Metric: Unique Pageviews (this will show the most similar view to our goal-based conversion percentages, since Unique Pageviews and Goals both de-dupe at the Session level.)
- Style > Chart Type: “Horizontal”
- Chart Filter: Exclude the Signup Flow Page=”Other”
Now, you have a side-by-side visualization of your funnel, together with the calculation of the conversion percentage! You can continue to tweak this as you please (formatting, naming, etc – whatever will make it the easiest for your end users to digest.)
Keep in mind, now that this is built, you could also add controls to your Data Studio report to allow your users to change the timeframe, or choose a specific Source/Medium, and see how this funnel changes.
Here are two examples where I used this method:
Update: An Alternate Approach by Matthew Brandt
After posting this, I had some great conversations with folks via #measure Slack, and one even offered to write up how he does this (which is different from mine.) Here’s Matty’s approach:
Here is how I set up my Google Data Studio dashboard with a funnel that fills itself (heh). This is especially useful if your funnel is based on Events and not Pageviews, as GA cannot create a funnel based on Events.
First, let’s talk about the setup in general.
– Google Analytics
– SuperMetrics for Google Drive
– Google Sheet
– Google Data Studio (duh)
So – what are we trying to visualise? We run a SaaS business and therefore have different metrics compared to “traditional” e-commerce or content sites. We wanted to visualise the customer lifecycle of visiting our website, creating a trial account, setting up your account, buying our product and also cancelling the subscription, if applicable (we hope not). We call this our “funnel” of Prospect to Churn.
The data flows automatically every morning at 4am from GA into a Google Sheet (let’s call it DATA MASTER GA) using SuperMetrics for Google Drive (which I highly recommend). I then created a second Google Sheet (let’s call it FUNNEL PERFORMANCE) where I pull data from DATA MASTER GA and do funny stuff to it (to calculate CR’s, etc.) There I am using a function called IMPORTRANGE() which allows me to keep the calculation-memory in the sheet to a minimum. I won’t go into too much detail here about the Google Sheet tabs but suffice to say that it’s just a lot of calculations. Most importantly however – all data is loaded from SuperMetrics by DAY, meaning each row has then data from one day only. This is important for the configuration as it makes it infinitely easier to configure Data Studio afterwards.
Data Studio Setup
This is what the dashboard looks like (slightly minimised, hence the cutoff date, as it’s 1900 x 1050). Obviously I have hidden our data because the numbers are amazing and I don’t want you all to be jealous 😉
You’re probably asking yourself “that funnel looks completely normal, why is it magic?” Well, that’s what I am about to tell you. You see, it’s not one funnel – it’s TWO funnels that look like one.Why?Because our Prospects metric (# of users who visit our website, who are not already clients of ours) differs vastly from the numbers below (# of users who create a trial, set up their account etc.). I assume many businesses face this issue and I did not want to have a visualisation that had one gigantic bar and 4 smaller, illegible ones underneath it. That’s why.So how does it work? The two bar charts are configured identically from the get go – sizing, dimension, etc. Then, the metrics are added (all 5 of them). You will already see the sizing problem at this stage, but that doesn’t matter. Next you use the magic button in the “Style” menu:
The “Log scale” effectively uses a logarithmic scaling for the chart instead of a “linear” or traditional scale. Adjust the top number to something that makes your funnel look pretty, using a date range that most people will be using (2 weeks is usually a safe bet). Then, uncheck the “Show axes” button and voila. Lastly, make a copy of this chart, remove all but the top metric, and then align as needed. It should look pretty good. In order to get the numbers and titles on the bars, use text fields and scorecards of the metrics in the bar chart – it looks good and they’re easy to do.
Area 2 – Process Funnels
We have two distinct processes *within* this actual funnel that I wanted to visualise. Instead of making tiny funnels (yes, I actually thought about that) I decided to use scorecards and arrows to show the direction the user takes. This received a fairly good response from users of the dashboard too, as it clarifies how the final number came to be in the funnel.
Area 3 – Filtering Options
Filters are great. I use the date range based on the column in the Google Sheet to have numbers on a daily basis and then we’re using a couple of different custom dimensions and the medium to give us a better idea of who is actually converting in this customer journey. That’s it!
Drawbacks / Improvements
Currently DS takes quite a while to load this dashboard, probably because of the complexity of the bar charts. I hope this will improve in the next weeks as the product becomes more refined. I also toyed with the idea of not using a bar chart but I was not able to get the area chart to cooperate for this visualisation.
I hope this helped some of you with your visualisation “issues” with DS and if you have any questions, just write me on the #measure Slack (@mattytwoshoes).
What’s your trick?
I’m sure mine and Matty’s are not the only ways to do this. (Normally challenges like these make for creative solutions!) I’d love to hear how others have managed this, any other tips you might have, and any issues you found (to allow me to improve the method I’m using!) Please share your questions, ideas, feedback or any thoughts generally in the comments. If you have your own blog post outlining your method, please let me know and I’d be very happy to link to it!