What’s big query?
Cloud-based big data analytics web service |
Processes very large read-only data sets |
SQL-like syntax |
Runs on Google Cloud Storage Infrastructure |
Analyze terabytes of data with just a click of a button |
Can be accessed with REST oriented API |
Link BigQuery to GA 360
1. Sign in to Google Analytics |
2. Click "Admin" and Navigate to Analytics 360 property |
3. Under property settings, click All Products, then click link BigQuery |
4. Select the view you want to link |
5. Confirm that you have enabled billing and applied any relevant credits or coupons to your project and click Save. |
6. If you need to stop the export, return to this page, and click Adjust Link in the BigQuery section. |
Logical Operators:
AND, OR, NOT IF(condition, true_return, false_return) |
Mathematical functions:
COUNT, SUM, AVG, MAX, MIN |
+ (addition), - (subtraction), / (division), * (multiplication), % (modulo) |
|
|
How to start?
Create a project from google cloud console page(https://console.cloud.google.com/projectcreate). |
Select BigQuery in the menu to open Google BigQuery UI |
Create a dataset |
Now you are ready to query! |
Best Practices
1. Only you need SELECT fields |
2. Whenever possible use LIMIT |
3. Store often queried intermediate tables (save table) |
4. Test on smaller dataset |
5. Compare results with GA’s web ui |
Save options
Save Query (requires no space) |
Save View (alias for a query) |
Save Table (requires space, but offers fast execution and low cost) |
Common queries:
SELECT - Select attributes (dim & met to be returned) |
FROM - From which data table(s) |
ORDER BY - DESC|ASC - order by dimension or metric, descending or ascending |
JOIN - Join attributes |
AS - Give an alias to an attribute |
WITHIN - selecting inside nested values |
|
|
Pricing
Operation |
Pricing |
Details |
Active storage |
$0.020 per GB |
The first 10 GB is free each month. |
Long-term storage |
$0.010 per GB |
The first 10 GB is free each month. |
Streaming Inserts |
$0.010 per 200 MB |
You are charged for rows that are successfully inserted. Individual rows are calculated using a 1 KB minimum size. |
Queries (analysis) |
$5.00 per TB |
First 1 TB per month is free. |
Order of commands
SELECT, WITHIN, FROM, FLATTEN, JOIN, WHERE, GROUP
BY, HAVING, ORDER BY, LIMIT |
Reference
Chrome Extension |
Streak for bigQuery |
Query reference |
|
GA + BigQuery cookbook |
|
Set up BigQuery Export |
|
GA Sample data set |
|
|
Created By
Metadata
Favourited By
Comments
No comments yet. Add yours below!
Add a Comment
Related Cheat Sheets