Google Analytics

How to Add GA Segments to Google Data Studio Reports

**Update: Google Data Studio now includes native support for GA Segments. The post below may still be relevant if you are looking to combine data from multiple sources into a single Data Studio report /Update**

Ever since Google released Data Studio in mid-2016, I’ve received a lot of interest from clients who find its data visualization and data sharing capabilities much easier to grasp than the standard Google Analytics reports. However, anyone who has put together a Data Studio report has noticed that its simplicity is both its strength and weakness. You can easily create visually compelling reports in minutes, but it lacks the sophistication of more feature-rich tools such as Tableau. One missing feature that I’ve seen users complain about is its lack of support for GA Segments. Fortunately, with the Google Sheets connector and Google Analytics add-on for Sheets we’re able to work around this limitation. Note that this same process works (and is slightly easier) with Supermetrics, but I’ll demonstrate my solution with the GA add-on for Sheets because it’s free.

The overall process we’ll walk through is as follows:

1. Create a Google Sheet that pulls in data from multiple GA segments
2. Combine that data into a single worksheet for Data Studio
3. Connect Data Studio to the combined worksheet for reporting

The desired output will be a single Data Studio visualization that is able to show data from multiple side-by-side GA segments filterable by date.

1. Create a Google Sheet that Pulls GA Segment Data

Before you begin, you’ll need to install the GA add-on for Sheets and connect it to your GA account. That setup process has been extensively documented and won’t be covered here.

Next, you’ll create a new Google Sheet and use the add-on to create the necessary GA reports that pull in segment data. To keep things simple, I’ll pull the “Organic Traffic” segment and “Direct Traffic” segments from my www.noisetosignal.io GA property. Remember, the GA Query Explorer is your friend as you search for your metrics, dimensions, and segments.

You can see the resulting report configuration here.

2. Combine Data into a Single Worksheet

Step 1 provides us with 2 new worksheets which contain data for each individual segment. However, if we want to show this data in a single Data Studio visualization, we’ll need to merge the data into a single worksheet. So while we start with this:

We want to end up with this:

!!WARNING Head-scratching spreadsheet functions ahead!!

To accomplish the above, you can either copy the functions from my final sheet here and update your worksheet names as appropriate, or dive into a breakdown of the functions below.

There are two types of functions required to create the output shown above. First, we need each row to be labeled with “Direct” or “Organic” so that Data Studio knows how to label the sessions. Note that these labels did not come over from the original GA query, so we need to create them ourselves with the following array function:

={TRANSPOSE(SPLIT(REPT("Direct,", COUNTA('Direct Traffic Segment'!A16:A10000)),","));TRANSPOSE(SPLIT(REPT("Organic,", COUNTA('Organic Traffic Segment'!A16:A10000)),","))}

This function is wrapped in brackets { … } to indicate that it’s an array function that will return results spanning multiple cells and columns. The semi-colon ; between the transpose statements is how we merge the data we’re pulling from each individual sheet.

To put this in English:

• Count how many rows were returned from our query in each sheet (I only search A:16:A10000 but you can increase that range)
• Repeat the word “Direct” or “Organic” that many times depending on which sheet we counted. Add a comma between each repetition
• Split up each word by comma and put each word in its own cell
• Transpose those cells to make them vertical, rather than horizontal

That takes care of column A above. For columns B & C, we use a different but related function:

={'Direct Traffic Segment'!A16:INDIRECT("'Direct Traffic Segment'!B" & 15+COUNTA('Direct Traffic Segment'!A16:A10000));'Organic Traffic Segment'!A16:INDIRECT("'Organic Traffic Segment'!B" & 15+COUNTA('Organic Traffic Segment'!A16:A10000))}

This function is a little messier because we’re using the “Indirect” function to dynamically piece together a function out of text. To put this one in English:

• Count how many rows were returned from the query in each sheet
• Take that number and apply it to a range lookup so that we can say: Pull data from A16 to B[however many rows our query returned]
• Merge the results together into a single range

This will ensure that if our query returned 100 rows, we’ll grab data from A16:B115 (the extra 15 are to account for the header rows that the add-on returns).

Connect Data Studio to our Worksheet

Our data is now ready to be ingested by Data Studio for reporting purposes. First, create your Data Studio report. Next, create a new “Sheets” data source that is connected to the “Combined Data” worksheet. If your worksheet contains basic date and session information, all of the data types should be correctly interpreted by Data Studio. If you have currency or numeric dimensions, you may need to fiddle with these settings:

With this connection established, it should be fairly straight forward to create a line chart broken down by segment.

You can see my final results here.

Caveats

There are a few caveats worth considering when utilizing this approach:

1. Queries made using the GA Add-On are subjected to data sampling when spanning 500,000+ sessions within the property
2. While you can combine visualizations from Sheets and GA within Data Studio that are both affected by the date range filter , other filters can only effect 1 data source or the other. For instance, if you have a “Default Channel Source” filter from GA on your report, it will only affect the GA visualizations, not the Sheets visualizations.
3. Don’t forget to schedule your queries to run on a schedule within the GA add-on if you want to see the latest data
4. Data Studio will not stop you from selecting date ranges that are outside the bounds of the data you pulled into Sheets. Take this into consideration when defining your queries, constructing the report, and sharing your report with others.

Conclusion

As I mentioned above, this same approach can be used with tools like Supermetrics which provides a bit more flexibility in how the results are structured.

Let me know if there are any questions and happy reporting!