Google Analytics & BigQuery: The Whys and Hows


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 even wrote 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 ?

  • 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!

  • 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.

  • Alex Razumau

    Hi Jonnathan,

    what are your thoughts on simulating raw data without google analytics premium? By combining user id and session id dimensions which will represent a session of a particular user, extracting using analytics api and then packing this data in metrics inside of big query?

    • Hey Alex — sorry, missed this when you initially posted it. Capturing a user ID and session ID in GA as custom dimensions helps with disaggregating the data to pull through the API. The downfall there tends to be the sampling limits in the API. If your traffic volume is low enough and/or you can constrain your queries enough to avoid sampling, this can be a workable substitute without Analytics 360.

  • Kayla

    Hi Jonathan, so Google Analytics/premium cannot store PII. Can BigQuery store PII? I know you can upload customer information into it, but can BigQuery collect PII from GA and store it?

    • Hi Kayla — you’re correct that you can’t collect PII in Google Analytics, and if the data isn’t there, it can’t be exported to BigQuery. BigQuery itself has no restrictions on PII. Typically the strategy is to capture an anonymous identifier (an alphanumeric ID, for example) in Google Analytics and match that with data brought into BQ from elsewhere. Many tools have scriptable APIs that makes it possible to automate importing data into BQ, and we’ve done integrations with CRMs like Salesforce, email marketing tools like MailChimp, and many other sources.

Contact Us.

Follow Us



We'll get back to you
in ONE business day.
Our Locations
THE FOUNDRY [map] LunaMetrics

24 S. 18th Street
Suite 100

Pittsburgh, PA 15203


4115 N. Ravenswood
Suite 101
Chicago, IL 60613


2100 Manchester Rd.
Building C, Suite 1750
Wheaton, IL 60187