1. Home
  2. Knowledge Base
  3. Google Analytics
  4. How to Get Analytics 360 Data Out of BigQuery Without Writing SQL (Analytics 360 customers only).

Getting Analytics 360 Data Out of BigQuery with Analytics Canvas:

You will need a trial license key or a Premium subscription to use the features discussed in this tutorial.  If you haven’t already done so, sign-up for a free 30-day trial and instantly get your license key so you can begin.

If you are an existing customer, contact support@analyticscanvas.com to request a free 30-day trial of the Premium subscription. 

  1. Open Analytics Canvas and apply your license key (you only need to do this once)
  2. Go to New Source > Google Analytics and choose Analytics 360 BigQuery

Image

  • If this is your first time using Canvas, it will ask you to authorize a connection to Google Analytics AND to BigQuery. The authorization is between your machine and the Google Analytics / BigQuery APIs. Your tokens are not accessible by us and we cannot access your data.

  1. Select the View you wish to query from your list of Google Analytics 360 Accounts and Properties.
  2. Make your query by selecting your dimensions and metrics. That’s right, no SQL required! Canvas will generate the SQL for you. And unlike the Reporting APIs, there is no limit to the number of dimensions and metrics that you can select in this query.

Here’s a “simple” query looking at landingPagePath by date and country:

 

Image

That’s about 175 words and nearly 2,000 characters of pure SQL that Canvas has generated on the fly as you select the dimensions and metrics needed for your query!

  1. Select the Time Period, Filter, and Sort Options.  These will all be added to the BigQuery SQL.
  2. Determine whether you want the data loaded directly to another BigQuery Table where you can query it from your visualization tool, or import your results directly to the Canvas where you can further process it before publishing and analyzing it.
  • To Import your data directly to the Canvas, simply select that option and the data will begin to download.

  • To save the data to a new or existing BigQuery table, click “Save to BigQuery Table”. On the next screen, click “Select Table”. You now have two options
    • Select an existing table from among your list of datasets, OR
    • Select a dataset and enter the name of a new table

After making your selection, click “OK”.

Image

That’s it!  By generating the SQL programmatically, you do not need to worry about syntax errors or the complex functions required to deal with date partitioned tables, nor do you need to worry about flattening/unnesting your data in order to get meaningful results.

The data will either land on your canvas for further processing, or it will be loaded to a BigQuery dataset of your choosing.

You can either read that new table into Canvas or work with the table that was landed to further process the data and make it available to your favorite reporting tool, whether that’s Tableau, Data Studio, Excel or something else.  

Happy Analyzing!

Was this article helpful?

Related Tutorials/Video