BigQuery CXL Institute course

Photo by National Cancer Institute on Unsplash

Disclaimer: I’m a learner at CXL Institute. In this post I will talk about the Digital Analytics Minidegree. It’s an in-depth program that can lead you from beginner to Digital analyst in 3 or more months. All courses are taught by world class instructors recognized for what they are doing in those specific fields.

BigQuery is a petabyte-scale analytics data warehouse that you can use to run SQL queries over vast amounts of data in near real-time.

Google BigQuery is naturally connected to Google Analytics and other Google tools. It helps if you are a Google tools user. BigQuery can also receive data from third party tools.

Learn to automate insightful reports with Google’s lightning-fast analytics data warehouse.

to work with BigQuery, you need at least a basic knowledge of SQL language. It will give you the ability to really query your collected data. SQL is the programming language of database. Although it’s a standard, there are different versions of it and fortunately bigquery use the default one.

Before talking about BigQuery, I will give you some of the queries you will use there.

Course summary:

• Introduction to the Google Cloud Platform and Google BigQuery
• Get your data into BigQuery
• Work with data in BigQuery
• Query and analyze your Google Analytics data with BigQuery
• Build automated reports and dashboards using your BigQuery data
• Parting thoughts

INTRODUCTION TO THE GOOGLE CLOUD PLATFORM AND GOOGLE BIGQUERY

1.) Consolidate all your data in one place — no more silos

2.) Automate your marketing reports — break the routine

3.) Develop your skillset to become a data-driven marketer

4.) Learn more about Google Cloud and its possibilities

5.) Discover the full analytics process from data collection to data visualization

Data collection and data visualization are the two part of data analytics infrastructure.

In general, you cannot interfere in the way your data is collected.

You can put every data you can imagine to BigQuery and only retrieve what you need from B after you use some functions to transform this data to what you need.

Google Cloud Platform has plenty of different tools.

· Artificial intelligence & machine learning: AI building blocks and more

· API management: API Analytics and more

· Compute: Compute Engine, App Engine, Container Engine and more

· Containers: Artifact Registry, Cloud Build and more

· Data analytics: BigQuery, Dataflow, Dataproc, Dataflow, Dataprep, Cloud Composer, and more

· Databases: Cloud SQL, Cloud Bigtable, Cloud Spanner, Firestore, Memorystore, and more

· Developer tools: Cloud SDK, Container Registry and more, as well as Cloud Tools for PowerShell and Eclipse

· Serverless computing: App Engine, Cloud Functions, Cloud Run, and Workflows

· Storage: Cloud Storage, Persistent Disk, Filestore, Archive Storage, and more

· And many other

In your Cloud Project, you will have your BigQuery project where you will have your datasets and inside these datasets, you will have the tables with data.

With SandBox, you can try BigQuery in two ways. Either with public dataset or with your own data that will be stored for only 60 days.

you can explore BigQuery without entering your credit card data and without activating your free trial

you can use Gmail, Yandex, Yahoo or another email provider address to connect to Google Cloud Platform

GET YOUR DATA INTO BIGQUERY

Google has some services that can help you to get your data into BigQuery automatically: Google Storage, Google Ads, YouTube, and other tools

- BigQuery Data Transfer Service

- Google Analytics 360

- Google Cloud Function

- Google Dataflow

- Google Cloud Storage

To load data automatically to Google Cloud Storage or to Google BigQuery, we will need to use an API.API basically is the language which you can use to speak to BigQuery

For BigQuery data streaming, you can develop your own solutions. You can use some created solutions like OWOX, Stitch Data, Fivetran, and others.

Use OWOX BI to send Google Analytics data to the Google Analytics dataset. Google Analytics 360 gives you the possibility to send your data to BigQuery. But if you have a standard version of Google Analytics, which was my case, you cannot really connect easily to BigQuery, so you will need to use an external tool for this.

WORK WITH DATA IN BIGQUERY

you will learn how exactly to work with your data in BigQuery. You will learn some basic SQL language, and you will also learn how to prepare your data for analyzing it in your reports

· SELECT

· WHERE

· LIMIT

· ORDER BY

· GROUP BY

· AND

· OR

· MIN

· MAX

· AVG

· SUM

· COUNT

QUERY AND ANALYZE YOUR GOOGLE ANALYTICS DATA WITH BIGQUERY

Partitioned tables: BigQuery query cheaper

Nested fields: fields repeated and nested in one table

BUILD AUTOMATED REPORTS AND DASHBOARDS USING YOUR BIGQUERY DATA

To create BigQuery reports in Google Sheet, you have two possibilities:

1- Sheets data connector for BigQuery is a natural connector for GSuite users

2- External add-on for universal analytics users: OWOX BI BIGQUERY Reports (Build charts in Google Sheets based on data from Google BigQuery)

OWOX BI BigQuery Reports Add-on allows you to create reports in Google Sheets based on data from a query in Google BigQuery. To do this, select on the toolbar Add-ons > OWOX BI BigQuery Reports > Add a new report:

Provide report configuration in a sidebar:

1. Select the existing Google Cloud Platform project;

2. Select a Google BigQuery query, that will provide data to be loaded;

3. If selected Google BigQuery query contains dynamic parameters, you may modify them.

You can find a detailed tutorial with screenshots onto owox website.

View is a virtual table that you can create using SQL query

You can use Google Data Studio, a visualization tool to help you identify trends, respond to them, and make predictions using your data.

To go further, there are several resources that will give you information, detailed tutorials, YouTube videos to learn and use Google BigQuery today in your career as a marketer or analyst. If you are interested in CXL courses, you can find lessons and course about SQL and database.

The foundations of SQL
SQL for marketers
Getting started with installing SQL

SQL in BigQuery https://cloud.google.com/bigquery/docs/reference/standard-SQL/query-syntax

After this course you have to experiment a lot to be able to call yourself BigQuery expert.