Oh, hi there!
If you've been creating Google Analytics reports in Data Studio, chances are you've wanted to add conversion rates or % value for a certain series of events at some stage. Questions like what % of people who started this form completed the form, what % selected this option etc are pretty common. But if you've tried to do this chances are you've found it frustratingly difficult/impossible to do.
Well good news, there is a solution!
What you will need
Before you get too excited, there is a catch. You will need to be/start capturing Session ID in your Google Analytics data to be able to use this method. Simo has a great blog on how to set that up here.
UPDATE: Not able to add Session Id to your tracking? Try this technique to create a Session ID in Data Studio!
What doesn't work
If you're like me you might have tried something like this already:
Form Starts = case when page="/form/pageone" then pageviews else 0 end Form Completes = case when page="/form/success" then pageviews else 0 end Conversion Rate = Form Completes/Form Starts
And you've probably been told off by Data Studio for mixing dimensions and metrics or for trying to reaggregate aggregated things.
Then you might have tried this:
SUM(CASE WHEN Page = "/form/pageone" THEN 1 ELSE 0 END)
And you may have gotten excited when it didn't spit out an error, then been brought back down to earth when you realised it just counted the number of pages with that label and returned you a value of 1.
Using Session ID to calculate custom metrics
What we need to be able to create funnels is a separate metric for each step in the process, that way we can divide one metric by the other to see conversion rates.
So how do we get around this? We use the Session ID dimension:
Once we have the Session ID in our data, we have an individual row for each session in which a page was viewed or an event was triggered which means we can use the case statement in Data Studio to return a count of Session IDs when certain criteria are matched.
In the above table above after bringing in the Session ID as a secondary dimension we can see we have 1,229 rows. This means there were 1,229 unique sessions in which /page was viewed.
So let's say /page was the first step in our conversion funnel, we would want to use that count as a metric in Data Studio.
To do that we would click in to edit our data source (this is our Google Analytics. data):
Click the + symbol to add a new calculated field:
Now we add a name, well call this one Funnel Starts:
And now the calculation:
So what's going on here? Let's start with the case statement:
CASE WHEN Page = "/Page" THEN Session ID ELSE "" END
Basically what's happening here is we're saying show us all the Session IDs when the session included a page view of the "/Page" page. If the session doesn't include a view of /Page return a blank value. That way we're left with a list of Session IDs for users who viewed the "/Page"
What we do next is count how many session IDs are in this list, and this gives us our Metric for "Funnel Starts". To do this we simply wrap our Case Statement in the COUNT_DISTINCT function.
COUNT_DISTINCT(CASE WHEN Page = "/Page" THEN Session ID ELSE "" END)
This will return a count of how many unique strings (in this case Session IDs) are returned by the case statement.
Once we've saved that, our new metric will be available to add to our charts. So if we insert a new Scorecard and go to the metric picker we will see our new metric under Default Groupings at the top of the list.
And we can add that into our reports:
Now we will want to create the next step in our funnel. We simply duplicate the above calculated field, and adjust the case statement to look for the next step in the process. For this example we'll just create a two step "funnel" so for our second metric we're going to create our "Funnel Complete" step.
Now in our report we have our two metrics for starts and completes:
This might not look special at this stage, as you could have created these two scorecards just using filters. But the difference is, now we can do calculations between the two values!
Using calculated metrics to create conversion rates
Now we just go into our calculated fields and simply do:
Funnel Completes / Funnel Starts
Once you've saved the field, click into the type field along side the metric in the table and set it to a percent value:
Now if we look in our metric picker you'll see we have Funnel Starts, Funnel Completions and Funnel Conversion Rate:
So there we have it, a simple two step funnel with a conversion rate. Now where we're going to get the most value out of this is for multi step funnels as this will allow not only end to end conversion rates but also step to step conversion rates. This is just a matter of making new calculated metrics for each step in the process then dividing each step by the step prior e.g. step3/step2, step4/step3 etc. Then you can divide the final step by the initial step for the end to end conversion.
Add a few arrow icons via the insert image function, use your favourite mustard and pink colour scheme and you have yourself a nice funnel report.
Another great thing about creating these conversion values is you can use them in time series and other charts to monitor trends in conversion through each step over time.
What if my steps are... different
Are your funnel steps based on events rather than page paths? No worries, we can adjust the case statement to get counts of users who triggered events e.g.
COUNT_DISTINCT(CASE WHEN Event Label = "Form Start" THEN Session ID ELSE "" END)
If you need to match multiple criteria, that's fine too. You simply add AND after your first match and specify additional match criteria e.g.
COUNT_DISTINCT(CASE WHEN Event Category = "Form Steps" AND Event Action = "Booking Form" AND Event Label = "Form Start" THEN Session ID ELSE "" END)
You can also use the regexp_match function to do partial matches or match multiple items using regex e.g. you might want to group users who viewed to variants of a payment page into one "Payment" step.
COUNT_DISTINCT(CASE WHEN regexp_match(Page,".*/page/creditpayment.*|.*/page/paypalpayment.*" THEN Session ID ELSE "" END)
Another use case for these custom metrics is to show the proportion of users who completed a certain action on your site. To do that simply create a custom metric for the action you want to monitor and divide it by the number of sessions or users e.g.
Sessions with download= COUNT_DISTINCT(CASE WHEN Event Label = "Form Start" THEN Session ID ELSE "" END) % of sessions with download= Sessions with download / Sessions
It is a bit of a pain that you will only be able to report on these figures after Session ID tracking has been implemented, but it will be worth it in the long run for the added flexibility you will have in how you use your Google Analytics data in Data Studio.