Connect with us


Getting Ready For GA4: Saving Your Historical Data



Getting Ready For GA4: Saving Your Historical Data

As you’re preparing to set up Google Analytics (GA4), you’re probably asking the same thing we were: What’s the best method for migrating our historical data?

There should be a way to do this, right?

In this column, you’ll learn whether we can merge data in GA4 and three DIY ways to save your historical data.

Can You Migrate Your Data To GA4?

The primary concern is whether GA users can transfer or migrate Universal Analytics data into their Google Analytics 4 property.

Unfortunately, you cannot migrate your data to GA4, and it’s not likely to be a feature we’ll see added in the coming months.

Migrating your data to GA4 is not likely to be a feature because the two versions use completely different data models.

I spoke with Charles Farina, Head of Innovation at Adswerve, and he explained that:


“It is the difference in schema and dimension definitions/calculations that make merging the data not possible.”

Schema refers to how the data is organized and the language used to ensure compatibility. Essentially it is the blueprint.

He explains you can see the differences in schema well when comparing the BigQuery integrations for UA and GA4.

“The UA export is sessionized, meaning each row in the export is a session, and every interaction is nested in that row. The GA4 export is very different, where each row is the event (interaction) itself,” shared Farina.

Another key reason is how dimensions and metrics are defined and calculated in GA4 compared to UA.

Google has a great support page that goes over many of these.

For example, let’s look at one of the most common KPIs, “Users.”

Universal Analytics reports on Total Users or all users, while GA4 focuses on Active Users or users that have visited the website at least once in the past 28 days.


Even if we could migrate UA data to GA4, it would be like comparing apples to oranges.

If you’re wondering why this change is happening, you’ll find the answer in our article, Google Analytics 4 FAQs: Stay Calm & Keep Tracking.

How To Export Google Analytics Historical Data

Google does empathize and encourages users to export their historical data.

“We know your data is important to you, and we strongly encourage you to export your historical reports during this time.”

Screenshot from Google Help, April 2022GA4 help

Google hints that more guidance on how to export may be coming in the future.

The good news is while we cannot migrate our data, we can still save it.

Google allows GA360 (paid product) users to export Universal Analytics data to BigQuery. However, the cost of this product makes it inaccessible for smaller organizations.

So, what about standard users? How do the rest of us export historical data?

I will show you three DIY methods and a few tools that can handle more complex requests.


1. Manual Export

The easiest way to export data is to get it directly from your Google Analytics account.

Open the GA standard report you want to keep. For example Acquisition > All Traffic > Source/Medium.

Set any customizations you want, such as a segment for a particular country, a filter for a particular page grouping, or a secondary dimension for landing pages.

Click EXPORT in the top right corner.

Select the file format from the drop-down menu. You can choose PDF, Google Sheets, Excel (xlsv), or CSV.

UA Historical Data_Manual Export exampleUA Historical Data_Manual Export example

Though this is the easiest way to export your historical data, there are limitations.

You can only apply two dimensions and are limited to a maximum of 5,000 rows.

If you are registering thousands of hits per day your data may be sampled.

Check for the green checkmark shield in the top left near the title of the report you’re viewing; this means your data is not sampled.


2. Google Analytics Dev Tools: Query Explorer

Google Analytics dev tools sound off-putting (and technical), but you are likely already familiar with one of the tools.

The Campaign URL Builder is commonly used to create UTM parameters for campaigns.

GA dev tools also have a query explorer.

This is an easy (and free) way to export data for non-technical users (yeah!).

Open Query Explorer and click the orange button, LOGIN.

Sign in to your Google Analytics account that has access to the property you are working on.

UA Query ExplorerScreenshot from UA Query Explorer, April 2022UA Query Explorer

Select the account, property, and view you want to save data for.

The tool will automatically set the GA ID, so you don’t need to worry about that.

UA Historical Data_Query Explorer_Select account exampleScreenshot from UA Query Explorer, April 2022UA Historical Data_Query Explorer_Select account example

Set the remaining query parameters: Date range in the format YYYY-MM-DD, metrics, dimensions, and any filters or segments you would like to apply.

For metrics, select the columns from your Google Analytics report that you are extracting data from.


You can choose every metric in the report you want to replicate or just a few metrics that help achieve your goals like “Users,” “bounceRate,” “avgSessionsDuration” and “goalCompletionsAll.”

UA Historical Data_Query Explorer_Query paramters exampleScreenshot from UA Query Explorer, April 2022UA Historical Data_Query Explorer_Query paramters example

Dimensions will be the rows from the Google Analytics report from which we are exporting data.

For example, if we want to see metrics (users, bounce rate, duration, and goal completions) by traffic source select “ga:sourceMedium” as the dimension.

Note: If you plan to visualize this information in Data Studio, you will need to set the dimensions “ga:Medium” and “ga:Source” separately.

“ga:SourceMedium” does not work in Data Studio. More on visualizing to come.

UA Historical Data_Query Explorer_Dimensions exampleUA Historical Data_Query Explorer_Dimensions example
Screenshot from UA Query Explorer, April 2022

The rest of the query parameters are optional. I recommend leaving these blank in this use case to pull the max amount of data.

You can always sort, filter, and segment within your spreadsheet.

Scroll to the bottom and click the orange button RUN QUERY.

From here, download the data as .tsv (tab separated values) and open it in Excel or Google Sheets.

GA Query Explorer_Data download exampleScreenshot from UA Query Explorer, April 2022GA Query Explorer_Data download example

Note: Notice the UA – GA4 toggle in the left-hand menu navigation. By clicking this toggle, you can access Query explorer for GA4 accounts.

3. Google Analytics Sheets Add-On

This option is a tad more complex but connects Google Analytics directly to Sheets, so you don’t have the extra steps of downloading and uploading.


Create a folder in your Google Drive that will hold your historical data. Create a new Google Sheet and name something that will make sense for future team members, like “UA Historical Data_Traffic Acquisition_2021.”

Along the top menu navigation, click Extensions > Add-Ons > Get Add-Ons.

screenshot_Google Sheets Extensions Get Add-OnsScreenshot from Google Sheets, April 2022screenshot_Google Sheets Extensions Get Add-Ons

Search for the Google Analytics app in the Google Workspace Marketplace. Click to install and follow the onscreen prompts.

screenshot_Google Analytics Sheets ExtensionScreenshot from Google Workspace Marketplace, April 2022screenshot_Google Analytics Sheets Extension

Back to your Google Sheet. Click Extensions again. This time you should see the app for Google Analytics.

Hover and click Create new report.

Now it’s time to export your historical data.

screenshot_Google Analytics Sheets Add-on_create new reportScreenshot from Google Sheets, April 2022screenshot_Google Analytics Sheets Add-on_create new report

Step 1. Name your report something that makes sense for your fellow team members. For example, we will pull data by financial quarter, so report no. 1 will be named “Q1 2021.”

Step 2. Select the Analytics view you want to extract data from by choosing our Account, Property, and View.

Step 3. Configure report. Here we will choose our metrics, dimensions, and segments.

I am going to keep it simple for this example and choose “Users,” “Bounce Rate,” and “Goal Conversions” for my metrics and “source” and “medium” for my dimensions.

Note: ga:sourceMedium is not compatible with Data Studio. If you plan on visualizing this sheet, it is best to pull the traffic source dimensions separately like ga: Medium, ga:Source.


Leave Segments empty to see all users.

screenshot_GA Sheets Add-On configuration for historical dataScreenshot from Google Sheets, April 2022screenshot_GA Sheets Add-On configuration for historical data

Clicking the blue button Create Report will lead you to configuration options.

There are more options to customize our report that are not available on the previous screen.

We can adjust the date range using the format YYYY-MM-DD.

We can apply filters like country, ga:country==United States.

Double-check that everything looks correct, then click Extensions > Google Analytics > Run reports to export your historical data.

GA Sheets Extension Run Report exampleScreenshot from Google Sheets, April 2022GA Sheets Extension Run Report example

Note: Speed up this process by copying and pasting the configuration over to the next column, updating the date range, and running multiple reports simultaneously.

A report status popup will let you know if you’ve made any mistakes or once the report is completed successfully.

Row Number 6 will show us if the data is sampled or not. Row number 7 will tell us how much if the sheet contains sampled data.

GA Sheets Extension_Sampled data screenshotScreenshot from Google Sheets, April 2022GA Sheets Extension_Sampled data screenshot

In Universal Analytics, data sampling happens after 500,000 sessions in the timeframe.

So, you can adjust your report data range to reduce the number of sessions in your timeframe.


Or, if you need the full dataset and want to skip the back and forth, use a third-party tool to avoid data sampling.

Third-Party Tools has a 46-step walkthrough of using Supermetrics for sending GA data to BigQuery.

On March 12, 2022, JR Oaks announced that they are working on releasing an open-source GA to BigQuery backup script/workflow to the public.

There are pre-built data pipelines by companies like Hevo and Electrik AI that export historical data from Google Analytics to a database file or data warehouse of your choice.

You may also consider switching to a paid Analytics provider.

A few have already launched a Google Analytics historical data import option.

Visualizing Historical Data With Data Studio

Now that you have pulled your historical data, you want to make something easy to compare to GA4.

Note: I have to forewarn you that attempting to compare UA and GA4 will be really rough because the data models are completely different.


Farina adds,

“Google intends for you to run GA4 side-by-side with UA and, instead of merging the data, just cut over to GA4 as soon as it has 13 months of historical data.”

Be assured that a lot of your hard-earned knowledge and skills carry over to GA4! Read, Getting started with GA4 to learn where to find site traffic, user engagement, events, and conversion reports.

Ok, back to visualizing historical data.

Follow these steps to create a Data Studio report that will stack a historical data table on top of a GA4 data table, so your YoY results are at least in one place.

Open Data Studio and click to start a Blank Report.

Data Studio create a blank report_screenshotScreenshot from Google Data Studio, April 2022Data Studio create a blank report_screenshot

There will be an overlay screen to select the data source you want to connect. Select Google Sheets.

Google Sheets connector for Data Studio screenshotScreenshot from Google Data Studio, April 2022Google Sheets connector for Data Studio screenshot

Locate the spreadsheet you made above when exporting your data. If you followed the steps exactly, it will be named “UA Historical Data _Traffic Acquisition_2021.”

Select the worksheet “Q1 2021.”

Using the first row as headers will automatically name your metrics and dimensions, so keep boxes both checked.


Select the optional range that matches your sheet.

For example, my headers start at A15, and the last number in my sheet is E62, so my range will be “A15:E62.”

Connecting Sheets with Data Studio exampleScreenshot from Google Data Studio, April 2022Connecting Sheets with Data Studio example

Data Studio will automatically create a table. Double-check that the configuration is the same as your sheet in the right-hand menu.

Medium is the primary dimension. Flip the toggle to add a secondary dimension of Source.

Metrics are Users, Bounce Rate, and Goal Completions.

Your historical data table will look similar to the screenshot below.

Google to offer more guidance on data export_quote screenshotScreenshot from Google Data Studio, April 2022Google to offer more guidance on data export_quote screenshot

Next, we will create the same table but for our GA4 data in the same time frame: Q1 2022.

Right-click to copy and paste your table, then change the data source from UA Historical Data to your Google Analytics 4 account.

Because the metrics have different names, you will see an error – invalid metric.

Click on each metric and update it to something similar like “Total Users,” “Engagement Rate,” and “Conversions.”


Dimensions will update to “session/source” and “session/medium.”

Last, in the same menu, scroll down and set the date range so it matches your historical data: January 01 – January 31, 2022.

The final report will look similar to the screenshot below.

screenshot of historical data to GA4 comparison in Data Studioscreenshot of historical data to GA4 comparison in Data Studio
Screenshot from Google Data Studio, April 2022

You can easily see primary metrics year over year by comparing historical data with GA4.

Although, it’s fairly bare-bones.

You can not blend this data because the definitions and calculations of the dimensions and metrics are fundamentally different.

For more robust historical reporting options, such as graphic users or goal completions over a period of time, you may want to consider BigQuery.

Final Thoughts

Unfortunately, migrating your data to GA4 is not currently possible (and not likely to come) because the two versions are fundamentally different data models.

There are a few DIY solutions for saving your historical data, but the outputs are fairly bare-bones.


If you need more robust information and reporting capabilities of historical data, look into a data warehouse like BigQuery.

Google hints that additional information on exporting historical data will come before the July 2023 end date.

Maybe that will be a data connector for BigQuery for Google Analytics standard users – one can hope.

More resources:

Featured Image: Paulo Bobita


if( typeof sopp !== “undefined” && sopp === ‘yes’ ){
fbq(‘dataProcessingOptions’, [‘LDU’], 1, 1000);
fbq(‘dataProcessingOptions’, []);

fbq(‘init’, ‘1321385257908563’);


fbq(‘track’, ‘PageView’);

fbq(‘trackSingle’, ‘1321385257908563’, ‘ViewContent’, {
content_name: ‘ga4-historical-data’,
content_category: ‘marketing-analytics digital-marketing-tools ‘

Source link


8 Pillar Page Examples to Get Inspired By



8 Pillar Page Examples to Get Inspired By

Pillar pages are high-level introductions to a topic. They then link to other pages, which are usually more detailed guides about parts of the main topic.

Altogether, they form a content hub.

Example of a content hub

But not all pillar pages look the same. 

In this guide, we’ll look at eight examples of pillar pages to get your creative juices flowing.

Excerpt of beginner's guide to SEO by Ahrefs

Key stats

Estimated organic traffic: 1,200
Backlinks: 6,900
Referring domains: 899

Overview of Ahrefs' beginner's guide to SEO in Ahrefs' Site Explorer

This is our very own pillar page, covering the broad topic of search engine optimization (SEO)

Why I like it

Besides the fact that I’m biased, I like the custom design we created for this page, which makes it different from the articles on our blog. 

Even though the design is custom, our pillar page is still a pretty classic “hub and spoke” style pillar page. We’ve broken the topic down neatly into six different chapters and internally linked to guides we’ve created about them. There are also custom animations when you hover over each chapter:

Examples of chapters in the SEO guide

We’ve also added a glossary section that comes with a custom illustration of the SERPs. We have explanations of what each element means, with internal links to more detailed content:

Custom illustration of the SERP

Finally, it links to another “pillar page”: our SEO glossary


Consider creating a custom design for your pillar page so that it stands out. 

Excerpt of Doctor Diet's ketogenic diet guide

Key stats

Estimated organic traffic: 92,200
Backlinks: 21,600
Referring domains: 1,700

Overview of Diet Doctor's ketogenic diet guide in Ahrefs' Site Explorer

Diet Doctor is a health company focusing on low-carb diets. Its pillar page is a comprehensive guide on the keto diet. 

Why I like it

On the surface, it doesn’t exactly look like a pillar page; it looks like every other post on the Diet Doctor site. But that’s perfectly fine. It’s simply a different approach—you don’t have to call out the fact that it’s a pillar page. 


Diet Doctor’s guide is split into 10 different sections with links to its own resources. The links bring you to different types of content (not just blog posts but videos too).

Video course about keto diet for beginners

Unlike the classic pillar page, Diet Doctor’s guide goes into enough detail for anyone who is casually researching the keto diet. But it also links to further resources for anyone who’s interested in doing additional research.


Pillar pages need not always just be text and links. Make it multimedia: You can add videos and images and even link to your own multimedia resources (e.g., a video course).

Excerpt of Wine Folly's beginner's guide to wine

Key stats

Estimated organic traffic: 5,600
Backlinks: 2,800
Referring domains: 247

Overview of Wine Folly's beginner's guide to wine in Ahrefs' Site Explorer

Wine Folly is a content site devoted to wine knowledge and appreciation. Its pillar page, as expected, is about wine. 

Why I like it

Wine Folly’s pillar page is a classic example of a “hub and spoke” style pillar page—split into multiple sections, with some supporting text, and then internal links to other resources that support each subsection. 

Supporting text and links to other resources

This page doesn’t just serve as a pillar page for ranking purposes, though. Given that it ranks well and receives quite a significant amount of search traffic, the page also has a call to action (CTA) to Wine Folly’s book:

Short description of book; below that, CTA encouraging site visitor to purchase it


While most websites design pillar pages for ranking, you can also use them for other purposes: capture email addresses, sell a book, pitch your product, etc. 

Excerpt of A-Z directory of yoga poses

Key stats

Estimated organic traffic: 11,100
Backlinks: 3,400
Referring domains: 457

Overview of Yoga Journal's A-Z directory of yoga poses in Ahrefs' Site Explorer

Yoga Journal is an online and offline magazine. Its pillar page is an A-Z directory of yoga poses.

Why I like it

Yoga Journal’s pillar page is straightforward and simple. List down all possible yoga poses (in both their English and Sanskrit names) in a table form and link to them. 

List of yoga poses in table form

Since it’s listed in alphabetical order, it’s useful for anyone who knows the name of a particular pose and is interested in learning more. 

What I also like is that Yoga Journal has added an extra column on the type of pose each yoga pose belongs to. If we click on any of the pose types, we’re directed to a category page where you can find similar kinds of poses: 

Examples of standing yoga poses (in grid format)


The A-Z format can be a good format for your pillar page if the broad topic you’re targeting fits the style (e.g., dance moves, freestyle football tricks, etc.).

Excerpt of Atlassian's guide to agile development

Key stats

Estimated organic traffic: 115,200
Backlinks: 3,200
Referring domains: 860

Overview of Atlassian's guide to agile development in Ahrefs' Site Explorer

Atlassian is a software company. You’ve probably heard of its products: Jira, Confluence, Trello, etc. Its pillar page is on agile development.

Why I like it

Atlassian’s pillar page is split into different topics related to agile development. It then has internal links to each topic—both as a sticky table of contents and card-style widgets after the introduction: 

Sticky table of contents
Card-style widgets

I also like the “Up next” feature at the bottom of the pillar page, which makes it seem like an online book rather than a page. 

Example of "Up next" feature


Consider adding a table of contents to your pillar page. 

Excerpt of Muscle and Strength's workout routines database

Key stats

Estimated organic traffic: 114,400
Backlinks: 2,900
Referring domains: 592

Overview of Muscle and Strength's workout routines database in Ahrefs' Site Explorer

Muscle and Strength’s pillar page is a massive database linking to various categories of workouts. 

Why I like it

Calling it a pillar page seems to be an understatement. Muscle and Strength’s free workouts page appears to be more like a website. 

When you open the page, you’ll see that it’s neatly split into multiple categories, such as “workouts for men,” “workouts for women,” “biceps,” “abs,” etc. 

Workout categories (in grid format)

Clicking through to any of them leads us to a category page containing all sorts of workouts:

Types of workouts for men (in grid format)

Compared to the other pillar pages on this list, where they’re linking to other subpages, Muscle and Strength’s pillar page links to other category pages, which then link to their subpages, i.e., its massive archive of free workouts.


Content databases, such as the one above, are a huge undertaking for a pillar page but can be worth it if the broad topic you’re targeting fits a format like this. Ideally, the topic should be about something where the content for it is ever-growing (e.g., workout plans, recipes, email templates, etc.).

Excerpt of Tofugu's guide to learning Japanese

Key stats

Estimated organic traffic: 39,100
Backlinks: 1,100
Referring domains: 308

Overview of Tofugu's guide to learning Japanese in Ahrefs' Site Explorer

Tofugu is a site about learning Japanese. And its pillar page is about, well, learning Japanese.

Why I like it

This is an incredible (and yes, ridiculously good) guide to learning Japanese from scratch. It covers every stage you’ll go through as a complete beginner—from knowing no Japanese to having intermediate proficiency in the language. 

Unlike other pillar pages where information is usually scarce and simply links out to further resources, this page holds nothing back. Under each section, there is great detail about what that section is, why it’s important, how it works, and even an estimated time of how long that stage takes to complete. 

Another interesting aspect is how Tofugu has structured its internal links as active CTAs. Rather than “Learn more” or “Read more,” it’s all about encouraging users to do a task and completing that stage. 

CTA encouraging user to head to the next task of learning to read hiragana


Two takeaways here:

  • Pillar pages can be ridiculously comprehensive. It depends on the topic you’re targeting and how competitive it is.
  • CTAs can be more exciting than merely just “Read more.”
Excerpt of Zapier's guide to working remotely

Key stats

Estimated organic traffic: 890
Backlinks: 4,100
Referring domains: 1,100

Overview of Zapier's guide to working remotely in Ahrefs' Site Explorer

Zapier allows users to connect multiple software products together via “zaps.” It’s a 100% remote company, and its pillar page is about remote work. 

Why I like it

Zapier’s pillar page is basically like Wine Folly’s pillar page. Break a topic into subsections, add a couple of links of text, and then add internal links to further resources. 

In the examples above, we’ve seen all sorts of execution for pillar pages. There are those with custom designs and others that are crazily comprehensive.

But sometimes, all a pillar page needs is a simple design with links. 


If you already have a bunch of existing content on your website, you can create a simple pillar page like this to organize your content for your readers. 


Keep learning

Inspired by these examples and want to create your own pillar page? Learn how to successfully do so with these two guides:

Any questions or comments? Let me know on Twitter.  

Source link

Continue Reading

Subscribe To our Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address