Hey there, I'm Benjamin from Loves Data. Welcome back! One of the top questions I've been asked over
the last couple of weeks is if data from the previous version of Google Analytics, so Universal
Analytics, can be exported and then imported into Google Analytics 4 (GA4). So that's exactly what we're going to explore
in this video. You're going to learn about what you can (and
can't) export and import when it comes to Google Analytics. Okay, so the good news is that you can export
data from Universal Analytics. But the bad news is that it's not possible
to import your historical data into GA4. So what should we do if we want to back up
data from Universal Analytics? Well, there are a few different ways you can
export data, so let's head to Google Analytics to take a look... I've already logged into Google's demo account
and we're looking at Universal Analytics... Let's select 'Behavior'... Then 'Site Content'... And then 'All Pages'... On the top right corner there is an option
to export the report we're currently looking at. Let's click this... We can see we can export the report as a PDF,
we can export it to Google Sheets, download an Excel file, and download a CSV file... These built-in options are a good starting
point. They're useful if you only want to download
a few reports and you're happy with static reports. By static, I mean you just want to see the
overall metrics for the date range selected. And you don't need to modify the date range
in the report to compare changes over time. To use these export options, the first thing
you should do is check that you're happy with the date range. Whatever you can see in the report will be
exported. So let's change the date range to include
all of 2021... The other thing you will need to decide is
how many rows you want included in your export. We're currently looking at the first ten rows
in the report, so this is what will be included in the export. If you want additional rows, then you will
need to adjust the report first. For example, we can select the drop-down... And choose 'Five Hundred'... This will mean that our export includes the
first one thousand rows. Okay, now let's click 'Export'... And select 'PDF'... Exporting your reports as PDF is one of the
easiest options, but it won't let you compare anything and you can adjust the report later. It's a bit like taking a screen grab of the
report you're looking at. Let's take a look at the PDF... Here we can see the PDF we exported. It looks just like the report in Google Analytics. One thing that isn't great, is that sometimes
rows can get cut-off between pages in the PDF... And I want to highlight that the more rows
you want to include in the PDF, the longer the export will take. So if you want to export more than five hundred
rows, you might want to use one of the other options. This means using the PDF export option is
okay if you only want to export a few reports and you only want to focus on the top rows
in the report. The next option I want to show you is exporting
the data to Google Sheets. This is also really simple, but it means you
can filter the data a little, for example, you can just focus on particular rows in Google
Sheets. Overall, I would recommend exporting to Google
Sheets over the PDF we just saw. So let's head back to Google Analytics... Just like we did for the PDF, you should check
the date range and number of rows you want to export to Google Sheets. Then, when you're ready, click 'Export'... And choose 'Google Sheets'... And let's click 'Import The Data'... We can see the report loads in a new spreadsheet
that is automatically saved in Google Sheets. The thing I like about using the export option
for Google Sheets is that you can at least do some basic filtering and visualizations. Let's start by renaming the tab to the date
range... And let's remove the rows at the top... Now let's freeze the header row by pulling
the gray line below the column headers... Now let's select the first column... And choose 'Data'... And then 'Create A Filter'... Now let's click the filter icon... Select 'Filter by Condition'... And choose 'Text Contains'... And let's enter 'kids'... And click 'Okay'... Now we're just looking at the pages on the
site that include 'kids'. So we can start to see that using Google Sheets
to backup our data from Universal Analytics is more useful than exporting reports to PDF. Apart from exporting to Google Sheets, you
can also export Excel and CSV versions of your reports. I'm not going to cover these today, since
they'll provide you with the same data we can see here in Google Sheets. Now I want to show you another way you can
pull your historical data into Google Sheets. We're going to use the Google Analytics Add-on
to recreate the 'All Pages' report. And then we're going to connect Google Sheets
with Google Data Studio to make the report dynamic. Let's head back to Google Sheets... We need to start by creating a new spreadsheet... Let's name the spreadsheet 'Universal Analytics
Backup'... We're going to use the Google Analytics Add-on
for Google Sheets, so let's select 'Extensions'... Then 'Add-ons'... And 'Get Add-ons'... Now let's search for 'Google Analytics'... And install the add-on... Now we need to select 'Extensions'... Then 'Google Analytics'... And 'Create New Report'... Let's name the report 'Pageviews'... And then you will need to select your account,
property and reporting view for Universal Analytics... Now we need to add the metrics we want to
include. Since we want to recreate the standard report,
let's search for and add 'Pageviews'... 'Unique Pageviews'... 'Average Time on Page'... 'Entrances'... And 'Exits'... Then for dimensions, we need to search for
and add 'Page'... And 'Date'... Now let's click 'Create Report'... This creates a new tab in the spreadsheet
that contains the configurations we just made... Now we need to decide on the date range we
want to use and the number of rows we will need to use to pull the data into Google Sheets. To decide on how much you can pull in using
a single import, I recommend heading to Google Analytics. So let's head to Google Analytics... We're now looking at my Universal Analytics
demo property. I recommend you open your Universal Analytics
reports to follow the steps we're about to cover. We're going to open the report we are going
to re-create, so let's select 'Behavior'... Then 'Site Content'... And then 'All Pages'... Now I want you to select 'Secondary Dimension'
above the table... And search for and select 'Date'... The report updates and now each row shows
us both the page people have viewed and the date. Now I want you to check the number of rows
in your report. You will find this on the bottom right corner... We can see for my demo account, there are
only 39 rows, but take a moment to check the number of rows in your Universal Analytics
report. I recommend that you stay within one hundred
thousand rows to keep things manageable. If you have less than one hundred thousand
rows, then I recommend increasing your date range. For example, you might want to select an entire
year, like 2021, or even multiple years if you're still seeing less than one hundred
thousand rows. If you have more than one hundred thousand
rows, then I recommend reducing your date range. And you might need to export your data for
separate date ranges and then combine them in Google Data Studio. Okay, so for my demo property, I'm going to
use 2021 as my date range. Now let's head back to Google Sheets... I'm going to change the value for 'Limit'
to one hundred thousand... And for the start date I'm going to enter
'2021' for the year, then 'dash', then '01' for the month, 'dash', then '01' for the day... And for the end date, I'm going to enter '2021',
'dash', '12', 'dash', '31'... Now let's select 'Extensions'... Then 'Google Analytics'... And 'Run Reports'... This will now pull the data into Google Sheets. This can take some time if you're requesting
lots of rows, so we'll need to wait until it's finished... And then click 'Okay'... Now we can select the new tab that has been
created in our spreadsheet... This contains all of the metrics for our pages
along with each date. Now let's head to Google Data Studio... Let's create a new report... Select 'Google Sheets'... And then select the spreadsheet we just created... Now let's select the sheet named 'Pageviews'... And since the tab that was created by the
Google Analytics add-on had some extra information at the top, let's enter a range to exclude
this... An alternate option to adding this range would
be to copy the tab in Google Sheets and then remove the extra rows from the top. But let's use the range and click 'Add'... And 'Add To Report'... We can see a table it added by default... Let's adjust the metrics so that it reflects
the report we saw in Universal Analytics. Let's add 'Pageviews'... 'Unique Pageviews'... 'Average Time on Page'... And 'Entrances'... Now we can see that the average time metric
isn't displayed in the same way as what we saw in the original report. It isn't showing us time, So let's adjust
this. Let's click the edit icon for the data source... And let's change the 'Type' for average time
to 'Duration'... Now there are a few other metrics I've left
off the report. I want to keep things simple in this video,
so we're going to recreate the 'Percent Exit' metric, but we're going to leave out 'Bounce
Rate'. It is possible to recreate the bounce rate
too, but it's a bit more complicated, so I might cover that in a future video. Okay, to recreate the 'Percent Exit' metric,
let's select 'Add A Field'... Let's name the field 'Percent Exit', 'v2'... And let's enter 'Sum', 'open parenthesis',
'Exits', 'close parenthesis', 'forward slash', 'Sum', 'open parenthesis', 'Pageviews', 'close
parenthesis'... Let's save the field... And navigate back... Now let's find our new metric... And change the type to 'Percentage'... Now let's head back to our report... And let's add the metric to the table... Now let's add a trendline to the top of our
report... And let's ensure the metric shows us 'Pageviews'... Finally, let's add a date range control to
our report... And let's view our report... We've now created a report that reflects the
standard 'All Pages' report in Universal Analytics. And we can use the date range selector to
change the data included in the report... You could even adjust the report to include
comparisons, additional filtering options and more. Apart from exporting your reports to PDFs,
to Google Sheets, or re-creating key reports in Google Data Studio, you could also consider
exporting data from Google Analytics to BigQuery. This is even more complicated, but it is an
option. I've included links in the extra resources
below this video. And the other thing you should be doing is
setting up GA4 in parallel to Universal Analytics. This will allow you to begin building up historical
data in the new version of Google Analytics. It's really important to do this. So take a moment to check if you've set up
Google Analytics 4 yet and if you haven't then promise me that you'll do it now. Depending on the platform you're using for
your website, it shouldn't take long to at least get the default GA4 tag on your website
so that you're collecting page views, sessions, users and other important metrics. Are you going to export your data from Universal
Analytics? I'd love to know! Let me know in the comments below. And if you found this video helpful, please
like it so I know to make more videos like this one. I'll see you in the next video!
