Google Analytics & BigQuery: The Whys and Hows

By /

January 27, 2014

Back in May, Google announced that GA Premium customers would be able to export analytics data to BigQuery. It’s now rolling out to all Premium customers. What does this really mean? What’s it let you do beyond what you could before?

How do you access the data?

BigQuery stores your GA data in what is basically a giant table. It gives you a SQL-like interface to query that data, either through a web interface or programmatically.

Screen Shot 2014-01-27 at 7.45.17 AM

Why SQL-“like”? Well, this isn’t exactly a relational database. Although I described it as a table, the records are actually hierarchical (each session contains many pageviews, for example). So BigQuery has some special features for dealing with those structures, but overall, if you’re familiar with SQL, you’ll find BigQuery easy to use.

As Google Analytics shifts its focus from the web to — well, the universe — with Universal Analytics, they want you to send ALL THE DATA into your Google Analytics account. But in some cases, that may not be practical or desirable. A tool like BigQuery allows you to join up data outside of Google Analytics as well, using SQL and your other databases and sources of information.

Screen Shot 2014-01-27 at 7.39.24 AM

How is BigQuery different from GA data through the API?

Google Analytics has APIs to access data. These allow you to access GA data to build your own reports and so on. But the data available to you there is the same data as in your reports: it’s the processed, aggregate web data you see. In fact, the API and the Custom Reports feature in Google Analytics essentially use the same structures to access data.

BigQuery is different. Google Analytics will export raw session data to a query-able table in BigQuery. In basic terms, there’s one row in this table for each visit to your website. Each row contains a laundry list of data about the visit: the source, all the pages viewed, the visitor ID, etc.

Because this is session-level data, we can get at the kind of effects you can usually only get at with Advanced Segments in GA (including the new user segments). For example, finding all the visits by users who have viewed Product A. And using the power of BigQuery’s processing engine — voila, no sampling, no matter how big the data set you start with.

How do I get started?

First, BigQuery export is available only for Google Analytics Premium customers.

You can have the BigQuery export turned on through your Premium account manager. Note that there are costs for both data storage and processing in BigQuery, but GA Premium users get a $500/month credit to use toward those charges.

In many cases, that $500 will take you a long way. For reference, I took a look at one of our Premium customers using BigQuery. Their site has about 6M visits and 50M pageviews per month. Data has been exporting since September, and this month their storage charges will be about $12.86.

You also pay for processing. Again, for ad hoc reporting queries, this will be quite inexpensive, although if you are using automated reporting that is updated often, you could rack up charges more quickly.

Hopefully this has sparked your interest in digging into your data with BigQuery. Stay tuned: soon I’ll be posting a case study with some real-life examples of how we’ve used BigQuery in data mining Google Analytics data for customers.

Jonathan Weber is our Data Evangelist, focusing on bringing the strategic value of data analysis to our customers. He spreads the principles of analytics through our training seminars and is currently writing a book on Google Analytics & Tag Manager. Before he caught the analytics bug, he worked in information architecture. Away from the computer you can find him as a flower farmer and plant geek.

  • pradeep

    Thanks Jonathan, this was helpful

  • Krishna

    Can you provide some real life examples along with case studies.

    With Regards,

  • Satish

    Hi Can you provide any examples how is it work ?

  • Mike

    Hi Jonathan,

    I was wondering if bigquery can be used with the google dfa account. I have a client who relies extensively on dfa reporting and I want to export some of the data into a table that I can query with our internal BI tool.

  • pentium10

    Reach out me to provide mentoring about Big Query and executing advanced queries for analytics or reports.

  • A.K.

    Hi, Great article! Can I confirm with you that you are saying it is NOT possible to export raw session data out of GA at all for someone without a premium account or you are saying that the direct import to BigQuery is not possible only? Is there a way just to get a dump from all the raw data in standard GA offline and out of analytics so we can work on it and query it else where? Many thanks!

  • Jonathan Weber

    Hi AK — It is not possible to export raw session data out of GA without GA Premium. GA has APIs you can use to access data, but it’s the same (aggregated) data that’s in reports, not the raw session data.

Contact Us.


24 S. 18th Street, Suite 100,
Pittsburgh, PA 15203

Follow Us



We'll get back to you
in ONE business day.