Google Analytics is a powerful and useful tool for marketers. It gives them deep insights into how their campaigns are performing but it may be easier to analyze the data in spreadsheet software like Google Sheets or Excel.
Spreadsheets have features that allow you to slice and dice your data and apply various conditions with filters to give you specific views of your data. There are a few ways you can do this, but one of the most straightforward ways to do this is with the column filter feature.
Download the data from Google Analytics
First, download the data. If you’re using Google Analytics 4, follow these steps:
- Click on the share icon in the top right corner.
- Click Download File
- Choose Download CSV
If you are using the Universal Analytics version of Google Analytics, click on Export in the top right and select the format or the software you’ll use to analyze the data.
Our example will use the column filters in Google Sheets but the concepts should be the same for other spreadsheet software.
Highlight the entire table without the last summary row if your dataset has one. Click on Data from the top menu, and choose Create a filter.
Now you can see that each column has a funnel icon or an upside down pyramid next to it.
If you click on it, you can see that each filter has the same functions listed. You can sort the table according to the values in the column by ascending order or descending order, color or text colors if your cells are formatted in such a way. If none of the cells in your table are formatted by color in any way, then those options will be grayed out.
You can also sort by condition by clicking on the drop down menu here. These are the different conditions that you can apply.
Filtering data by dimension values
For example, if you’re looking at data for traffic sources, you can use the “Text contains” condition and type in a string like, “Search”, and click okay. You can see all the values that include “Search” in that column, like “Paid Search”. Now if you delete that, then it restores the table in the original view.
We can filter by values, which is similar to filter by condition except it lists out all the distinct values for you. You can clear the selection, select all, or select specific values, and click okay. That will filter the data according to that view.
Sorting by numerical values
If you go to a column with numerical values, you can also filter or sort this table by some of these metrics. You can sort according to revenue in descending order so that we see the top sources and top landing pages that generate the most revenue.
You can filter by setting limits to the values you want to see. For example, look for all values that are greater to or equal to $1,000.
You can use this to determine top traffic sources with the top landing pages.
Using multiple filters
We can also apply multiple filters at the same time. Continuing from the example above, you can add a filter to the e-commerce conversion rate to look for values greater than or equal to 5%.
This way you can identify which pages have the highest conversion rates.
Spreadsheet column filters are one of the most simple yet most useful features of a spreadsheet. There’s a lot of insight you can uncover with the spreadsheet column filters. You can easily find answers to many questions about your data.
AUTHOR: Isaiah Stone
Isaiah Stone is a digital analyst in the consumer goods industry. He is fascinated with productivity, business growth, and accomplishing more.