Introduction
In the dynamic world of digital analytics, Google has ushered in a new era with Google Analytics 4 (GA4), replacing its predecessor, Universal Analytics, as of July 1, 2023. This revolutionary tool, characterized by its enhanced intelligence and user-centric approach, marks a transformative shift in data analysis. However, as we anticipate the complete phase-out of Universal Analytics by 2024, businesses and analysts alike must adapt to these changes and prepare for a smooth transition. This article outlines the migration timeline and offers crucial guidance for users navigating this shift to the new platform.
Problem with the existing system
There are numerous ways to extract data from Google Univeral Analytics. A few of the methods are
- Manually Downloading the data as CSV, PDF, or Exporting to Google Sheets
- Using Google Sheet add-ons like Google Analytics Spreadsheet Add-on.
- Using BigQuery and so on.
The problem here is it is challenging to download these massive amounts of data manually. The Google Analytics Spreadsheet Add-on is excellent for small amounts of data. BigQuery is suitable for extracting data that cannot be opened via CSV or Google Sheets. It’s more complicated to access BigQuery to extract the data.
Solution
If you search the internet, you will see only these three options. But that is not true. You only need two things to extract the Universal Analytics data—a Google account connected to your Universal Analytics account and Google Data Studio. If you think this is impossible, follow my instructions, and you will have your data within an hour or so.
In this example, I am going to extract user Behaviour Data
Behavior → Site Content → All Pages
You can extract any historical data with this same technique.
Things to know before you get started
There are two crucial things that you need to know. They are Dimension and Metric.
Dimension
A “dimension” is a descriptive attribute or characteristic of an object that can be given different values. Browser, Exit Page, and Session Duration are all dimensions that appear by default in Universal Analytics.
Ex: The dimension ‘city’ indicates the city, for example, “Paris” or “New York,” from which a session originates. The dimension page shows the URL of a page that is viewed.
Summary: Dimensions are attributes of your data.
Metric
A “metric” is a quantitative measurement of the data described by a dimension. Metrics can be sums, averages, ratios, or other statistical calculations.
Summary: Metrics are quantitative measurements. The metric Sessions is the total number of sessions.
Ex: Metrics include Bounce Rate, Pageviews, and Time on Page.
Together, they provide a complete picture of how your site or app is being used.
Ex: The dimension City combined with the metric Sessions can give you the total number of sessions per city. This can be useful if you tailor your content to audiences in specific cities.
Both are central to understanding the reports in Universal Analytics, as they provide the context and the quantitative measures needed to make informed decisions.
Data Extraction
Technical aspects apart. First, check that your account has been authorized to access. Go to your Google Universal Analytics and click on Admin.
Once you enter into the admin panel, you will see a lot of settings that you need to click on Account Access Management and click it.
Another panel will open, and you will see that your account has access to the Google Universal Analytics account. We are checking this because we must connect the same account with our data studio. To avoid any future conflict, it’s better we check these beforehand.
Now open Google Data Studio and click on Blank Report.
A popup will appear once you enter the blank data studio, asking you to connect to a data source. Now click on Universal Analytics. The reason why we connect with Google Analytics is because we need to extract data. So, we are connecting with Universal Analytics.
Once you click Google Analytics, it will ask you to connect to an account and its property. So connect with the correct account and its property and click on Add. Ensure you connect with your Google Universal Analytics property, not the GA4 property.
Note: If it asks for any permission request, connect with the correct account and provide access to the necessary permissions.
Once the connection is successful, you will see a popup: “You are about to add data to this report.” Click on Add To Report.
You have been successfully connected with the Google Analytics account and will see the following table. As highlighted in the screenshot, you can see the Dimensions and Metric. We will update our Dimensions and Metric to extract the Behavior -> Site Content -> All Pages Data.
By clicking on the “+” icon below, Dimensions, you can access a list of dimensions to add.
Now add the following Dimensions Page and Date. Expand the table, and the table looks similar to Google Analytics.
Come to Metric, remove New Users, and add the following.
- Pageviews
- Unique Pageviews
- Avg. Time on Pag
- Bounce Rat
- Entrances
- %Exit
- Page Value
It’s similar to Dimensions.
Now you can see the table is a replica of the Behavior table. Now, we have all the data we need, but the date range needs to be added. Even though we got all the data similar to the Behaviour table, we need to set the date range to extract the Complete or part-by-part data.
To get the date range, click on Add a Control → Date Range Control. Now you have the power to control the date. We have installed our Google Analytics around, Apr 11, 2018 so I am selecting that as the start date, and I like to get the data till Jul 31, 2023. Select your date accordingly. Once you choose the date range, you can see the amount of data increased in the table gradually.
Now we have everything that we need. All we need to do is to extract the data from the Data Studio to CSV. To do that, you have to hover over the table, and you will see the kebab menu icon. Click on that. In that, you will see a list of menus. In that list, you have to click on the export menu.
A pop-up will appear, showing a list of data export options. You can change your file name and do not change from CSV. If you try to export data in CSV(Excel), the Bounce rate and %Exit will be a whole value rather than a percentage decimal. Google Sheets can handle up to 500000 data. If the data exceeds, you cannot export the entire data. But you can export it as chunks, and exporting more than 100,000 data in Google Sheets will also take a while. And in the options, check the box for “Keep value formatting.”
Now you have all the Behavior data with Bounce Rate in a proper format. With this data, you can recreate Google Universal Analytics data in Google Data Studio.
We can cross-check by adding the Pageview in CSV with the Google Universal Analytics data to ensure we get the same data. Make sure you are using the same date.
You can see both in the CSV file and Google Analytics in the above screenshot. We can see the total page view is 466,530. Now, we have successfully exported the Google Universal Analytics data.
Conclusion
So, there you have it! We’ve successfully navigated the labyrinth of Google Universal Analytics data extraction. Who knew Google Data Studio could rescue us in such a way? Not only does this method sidestep the usual tedious manual data downloading, but it also avoids the complexities associated with tools like BigQuery. So, with this user-friendly solution, you can now breeze through your data analysis tasks without breaking a sweat.
