Google Data Studio is a newer tool launched by Google in 2016 that lets you build custom dashboards by linking various “data sources” like Google Drive, third party CRMs, and Google Analytics in an effort to give you a “one-stop-shop” for all your data tracking needs. The tool is extremely customizable and open-ended, but for the purposes of this blog post we’re going to focus on a single objective: displaying Google Optimize test data automatically in a Google Data Studio dashboard.
We’re going to be using Google Data Studio, Google Sheets, and Google Optimize to display up-to-date CRO testing data from Optimize in our Google Data Studio dashboards (this information will come specifically from Google Analytics Experiments in Optimize).
At its core, this is a fairly simple task. First, we need to have an experiment running in Google Optimize. The data we’ll be pulling isn’t technically coming from Google Optimize, but from the Experiments feature in Google Analytics, which gets its data from Optimize. Then, we are going to pull this Google Analytics/Optimize experiment data into a Google Sheet where it can be processed, and finally, pull that processed data from Sheets into Google Data Studio.
Once this is set up, we can automate our reports and have Google Data Studio refresh with up-to-date information as often as we’d like.
Let’s get started!
Setting Up Google Sheets to Receive Google Optimize Data
Let’s first create a new Google Sheet. We’re going to pull all our CRO test data from Optimize into this sheet. We’ll then set this Sheet up as a data source in Google Data Studio.
Before we start, we’ll make sure we have the Google Analytics Add-on installed in Google Sheets. If we don’t, it’s easy to install, just select “Get Add-ons” from the Add-ons drop down menu and search for it, or go here.
Once we have it installed, let’s get a report set up.
Click on Add-ons, then Google Analytics, then Create New Report. This will bring up a modal window on the right-hand side of the screen. From here we can name our report, select the account/property/view we want to pull data from, and finally, select the metrics and dimensions we need.
Let’s name the report. We’ll do something generic like A/B Homepage Test, but you should name it so it accurately reflects your specific testing situation, to make it easier to find again later.
Next, choose the right account, property, and view. This is the view the CRO test data is linked to, meaning, the Google Analytics account we can see your Optimize experiment data in. Remember, this will be under “Experiments” in Google Analytics. Next, we’ll need to select the metrics we want. In this case, we want to see our CRO tests’ conversion rate. Search for “Goal Conversion Rate”. If you have multiple goals set up within your Optimize experiment, you can select each specific Goal, like “goal 3”. In our case, we’re keeping it simple, so we’ll just look at the overall conversion rate.
Finally, we need to set up our dimensions. The two dimensions we want are “experimentId” and “Variant”. The experimentId is going to come from Google Optimize (more on that later), and will let us filter our Google Analytics Sheets report just down to our specific experiment. The “variant” dimension will let us see both data for our “control” and “variant” from our Optimize experiment.
Now that we’re all setu p, click “Create Report”.
We should now have an empty report template with the parameters we selected. Now we want to filter our report to just the one experiment we want. To do this, we’ll need to hop into our Google Optimize account for this specific test.
Once we’re in our Optimize account for the specific test we want, the piece of information we need is in the Experiment Information window on the right-hand portion of the screen. We need the “ID” from under Google Analytics heading. Copy that for use later. We can exit Optimize now, we won’t be using it again.
Let’s hop back into our Google Sheet where we have our report template set up. In the Filters cell (B11), we need to filter down to only our experiment. To do this, in the cell enter (without quotes) “ga:experimentId==YOUR EXPERIMENT ID FROM OPTIMIZE”. This will tell our report to filter reports only with this experiment ID.
Our report is now all set up, so let’s run it and see what happens. Go back up to the Google Analytics add-on from the menu bar and click “Run Report”.
If all is successful you’ll get a “1 report completed successfully” message.
We should have a new tab in your sheet now, with the name of report. We’ll click this tab if we’re not already taken there. We should now see a bunch of new data, but most importantly, the conversion rate for our experiment.
In the example above, we can see that the control has a conversion rate of .45%, while the variant, has a conversion rate of .36%. We’re almost there. We’ve got the data we need, next we need to link this spreadsheet to our Google Data Studio.
Google Data Studio
Let’s open a new blank report in Google Data Studio.
The first thing we’ll need to do is set up a Google Sheet with our testing data as a New Data Source.
To do this, select Create New Data Source from the right-hand section of the screen. Then select Google Sheets, and find the sheet that contains our Google Analytics report and data. Make sure to choose the right worksheet from our Sheet (in most cases, it will be the second sheet in the spreadsheet). In the options section, we’ll need to select the cells where our data populated. In our case, that is A15:C17. If you have additional metrics or dimensions you want, you’ll just need to adjust that cell range to account for all the data.
Click “Add to Report” on the next screen.
We now have our Google Sheet with our experiment data set up as our Data Source. All that is left is to draw a table and adjust our data. Click Insert from the menu bar and then select “Table”.
Then take your mouse and “draw” the Table on your page. It should look something like this,
After the table is drawn, we need to make one small adjust to the “Type” of data we’re displaying.
Click the pencil icon next to our Data Source.
Then for our experimentVariant and goalConversionRateAll, change the type from Number to Text. Then click “Done”. This needs to be done because by default Google Data Studio is treating your “Variant” as a metric rather than a dimension. In order to display the two variants side by side, we need to turn them into dimensions. Changing their type from “number” to “text” allows us to do this.
Your table should look something like this now, with the experimentVariant and the goalconversionRateAll displayed.
We now have your Google Optimize experiment data showing in your Google Data Studio dashboard. Pretty cool! Feel free to play around with the various dimensions and metrics you pulled in so that they display in whatever order you’d like. You can also rename the metrics and dimensions using the pencil button next to them. In the case below, we are displaying the Variant (renamed to Version (0 is controlled, 1 is variant)) and goalConversionRateAll (renamed to Conversion Rate).
The last thing we would do is set our my Google Analytics reports in Sheets to run automatically daily so that your Google Data Studio dashboard is always somewhat up to date. To do that go back into your original Google Sheet we set up as our Data Source and click “Add-ons”. From the drop-down select “schedule reports,” and then select the time you want your reports to run.
