Google Analytics and SQL

Google Analytics is a very popular tool among website and blog owners alike. With a simple and quick setup, it lets you gather data about your web page visitors easily. However, did you know that you can export data from Google Analytics to create your own SQL reports? Read this article to find out how.

Google Analytics is an extremely popular and powerful solution that lets you collect and analyze various kinds of information about your website. It is used worldwide by marketing specialists and is the go-to tool for most website and blog owners.

Google Analytics (GA) itself provides advanced data analysis features. It lets you browse your website’s statistics in a number of ways and analyze various aspects of your website traffic, such as the number of views for each page and the demographic data about your visitors worldwide. If you feel lost using GA, you can attend one of the numerous trainings available worldwide.

Even though Google Analytics is such an advanced tool, you may still want to go beyond the data insights it provides. And Google lets you do that easily: you can export your data from Google Analytics into several formats, such as Excel spreadsheets or CSV files. In this article, we’ll show you how you can use the exported data to create reports in SQL.

To make things more interesting, we’ll show you an easy-to-follow example based on our own actual business scenario with LearnSQL.com.

Business Scenario: LearnSQL.com Cookbook

To understand our business scenario, we’ll need to start with a bit of history. LearnSQL.com is an e-learning platform developed by the Vertabelo team. Prior to starting this site, we already had lots of experience with a similar platform named Vertabelo Academy. While working on Vertabelo Academy, we noticed that our platform users and blog readers frequently looked for information about very narrow and specific problems they encountered while writing SQL queries – e.g. how to get the current date, sort columns alphabetically, multiply two columns and put the result into a third column, etc. They could read a SQL manual, but they didn’t have time for that.

That’s how our SQL Cookbook was born. It consists of short, informative blog entries that each deal with a single specific SQL problem. If you’re interested, you can browse our LearnSQL Cookbook for more examples.

First, we did a bit of brainstorming and selected 90 topics for our SQL Cookbook. We prepared the entries and made them available at LearnSQL.com. Six months have passed since then and we’ve gathered some Google Analytics data that clearly shows our users enjoy this idea. Now, we want to extend the cookbook with new entries. To do that, we need to analyze the traffic we’ve had so far and understand what our users love most. Thus, we’ll export the data from Google Analytics and create an SQL report to help us decide what topics to include.

Exporting Data from Google Analytics

We logged into Google Analytics and picked Behaviour>Site Content>Content Drilldown as the source of data. (See the menu on the left-hand side.) Then, we selected the cookbook page address and chose the time range (May to August), as shown in the screenshot below.

To get more data in the exported file, we also picked a secondary dimension (Country). Then, we used the Export option at the top and generated an Excel file with our data.

Note: Google Analytics only exports the top rows that you can see in the list on the Content Drilldown page. If you want to export more rows than the default 10, go to the very bottom of the page and increase the number of rows:

The Excel file we received contained a few spreadsheets and definitely more information than we needed for our analysis. After a bit of cleanup, we were left with four columns in a single spreadsheet:

As you can see, we had some information about the number of views and the average time spent on a page for each country and each cookbook entry.

Next, we opened up our database management system (DBMS), which is PostgreSQL. Naturally, you can use another DBMS, such as Microsoft SQL Server, Oracle, or MySQL. We created a new table named cookbook_country_views with four columns that corresponded to the Excel columns.

Most modern databases allow you to create new tables using some sort of graphical user interface (GUI). However, GUIs differ from one vendor to another. If you want to learn a universal, database-agnostic method, look at our Creating Database Structure track.

Finally, we used the PostgreSQL import tool to import the data into our brand-new table. Each modern DBMS has some sort of import feature that can help you get your data from ordinary files. The most popular import format is a CSV file, so you may need to save your Excel spreadsheet as a CSV file, which you can easily do in Excel’s Save dialogue.

As a side note, keep in mind that you don’t necessarily need a database to analyze Google Analytics data with SQL. You can also export your datasets as Google Sheets and use SQL there. To find out more, learn our article about SQL in Google Sheets.

Creating Google Analytics Reports with SQL

Now that we had the Google Analytics data in place, we wanted to combine it with our existing database table.

If you take a look at our LearnSQL.com Cookbook, you’ll quickly see that each entry is tagged with some DBMS names. These tags inform our users which databases accept the given solution. Some recipes are universal (“Standard SQL”), others are very specific and only apply to a certain DBMS (e.g. “MySQL” or “MS SQL Server”).

Google Analytics doesn’t collect or analyze these tags, but we do. The Vertabelo team has a dedicated database table where we keep track of all the tags for all the cookbook entries. Each row in this table represents a single tag for a single article.

For our report, we’ll combine both tables: cookbook_country_views with Google Analytics data and cookbook_dbms with our tags. We’d like to understand which database management system is the most popular choice among our cookbook users. To that end, we’ll create a simple report that will show the total number of cookbook views for each database tag. We’ll use the following query:

SELECT dbms, SUM(views)
FROM cookbook_dbms cd
JOIN cookbook_country_views ccv
  ON cd.cookbook = ccv.cookbook
GROUP BY dbms
ORDER BY SUM(views) DESC;

In the query above, we used a few core SQL concepts: an aggregate function (SUM()) with grouping (GROUP BY) and sorting (ORDER BY) on two tables joined on the column cookbook. If any of these seem foreign to you, take a look at our SQL Fundamentals track.

When we ran the query, we got the following result:

We can clearly see that the most popular tag in the cookbook was MS SQL Server. It generated 70% more views than the Oracle tag. This may be a meaningful insight: we may want to focus on SQL Server recipes in the future, as they clearly attract most attention. Perhaps we could also think about how to broaden our Oracle audience.

Let’s create one more report: we’ll analyze the average time spent reading our most popular entries about MS SQL Server. We’ll run a report in groups based on country:

SELECT dbms, country, ROUND(AVG(avg_time), 2)
FROM cookbook_dbms cd
JOIN cookbook_country_views ccv
  ON cd.cookbook = ccv.cookbook
WHERE dbms = 'MS SQL Server'
GROUP BY dbms, country
ORDER BY ROUND(AVG(avg_time), 2) DESC;

The query above is very similar to the previous one. The new element is the WHERE clause, which we used to filter on the MS SQL Server tag. Here is an excerpt from the result:

Does this table tell us anything useful? For one thing, you can see that people from countries where English is the official language take a significantly shorter time to read a cookbook page. For instance, there is an eight-fold difference in the average time between Suriname and Australia! Perhaps we could make life easier for non-native English readers if we wrote shorter sentences and used simpler vocabulary? This area could definitely benefit from a bit more research.

Where to Go Next

The reports above are only two examples of how you can analyze Google Analytics data outside GA itself. Naturally, we could show you other SQL reports. We could analyze which SQL functions are most commonly searched, which countries generate the most page views, and where our users spend the most time in our Cookbook. The possibilities are practically endless.

If you too have a lot of Google Analytics data but you don’t know how to analyze it with SQL, we’ve got you covered. At LearnSQL.com, there’s an entire SQL Reporting track with courses that will teach you how to create sophisticated SQL reports. We put the focus on hands-on practice. You’ll write real SQL queries that apply to real-world business situations. Have a look and see for yourself!

Google Analytics and SQL: A Powerful Combo

Google Analytics is a powerful tool. However, you can also export information from GA to create SQL reports that will give you even more insight into your data. And if you've analyzed this article well, you already know that learning SQL will pay off for sure. With SQL, only the sky and your imagination are the limits. Good luck with your reports!