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 3601. 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 Practices1. 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 optionsSave 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 |
| | PricingOperation | 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 commandsSELECT, WITHIN, FROM, FLATTEN, JOIN, WHERE, GROUP
BY, HAVING, ORDER BY, LIMIT |
ReferenceChrome Extension | Streak for bigQuery | Query reference | | GA + BigQuery cookbook | | Set up BigQuery Export | | GA Sample data set | |
|
Created By
Metadata
Comments
No comments yet. Add yours below!
Add a Comment
Related Cheat Sheets