By the end of the text you will have learned to integrate two indispensable tools for any digital marketer: Google Analytics and Google Sheets (or Google’s Excel).
There are several reporting tools that show in a nice and uncomplicated way the information of the Google Analytics. But, at times, you want to be able to make some differentiated crossings with your data. This is one of the reasons, among many others, why you need to learn this type of configuration.
Tip: How is data marketing capable of increasing sales for a business?
For this article we will assume that you already have some basic experience with both tools, OK? Let’s go to the steps!
1) Create a new spreadsheet
First, log in with your Google account to the Google Sheets homepage. Create a new blank document and give it a name of your choice.
I called my spreadsheet “Google Analytics – Reports“. I know, lacked creativity! 😀
2) Install the add-on
Google has made a free add-on available for pull analytics data. There are some paid tools that do the same, but there’s no need to buy. We will use the official and free plugin.
Go to the menu “Add-ons > Install add-ons…” and search for “analytics”. Install the following plugin:
3) Create your first report
Here we assume that you already have a Google Analytics account configured. So, let’s configure our first report.
Go to “Add-ons > Google Analytics > Create new report”. You may be asked about some permissions Google needs to be able to manipulate data. Accept them and you should see a sidebar with some options.
Tip: 12 Essential Tools and Strategies for Success in Digital Marketing
On one name for your report and select the information you want to display. If in doubt, leave it blank and click “Create Report”.
You will be presented with a settings screen.
4) Configure report generation
In this new screen, we will make some adjustments:
1. In Start Date, put the formula below to bring the results of the last 5 years. If you want a different range, replace the “-5” in the formula with another number of years, keeping a negative value. Formula: =DATE(YEAR(EOMONTH(TODAY();12*-5));1;1)
2. in metrics paste the following: ga:users,ga:newUsers,ga:sessions,ga:avgSessionDuration,ga:organicSearches,ga:pageviews,ga:uniquePageviews;
3. Already in dimensions enter: ga:year, ga:month,ga:source,ga:medium;
4. In Limit change it to 15,000 rows so that you bring in all available data;
5) Run the report
Run the report from the menu “Google Analytics > Run reports”.
Note that a new tab will be created with the results.
In this report I chose some information grouped by 2 dimensions:
- Month and Year;
- Source and Media, to know where my hits are coming from. The source of traffic, in a nutshell.
Tip: Integrating Google Ads with Google Sheets in 5 Minutes
You can learn more about these options by clicking here.
Click on the banner and learn about other strategies to “hack” your marketing and accelerate results!
6) Run the report automatically
It doesn’t make sense, in the 21st century, you need to log in to manually run this report. We will configure here so that the script collects the data automatically every day.
You need to: click on “Add-ons > Google Analytics > Schedule reports”.
I configured my report to run once a day, between midnight and 1 am:
Ready! If you want new reports, follow the same steps and select other attributes and metrics!
Did you like this article? Comment below if he made your life easier just as he greatly improved mine!