ConversionXL & Khrystyna Grynko – BigQuery for Marketers

Google is the best search engine. BigQueryYou can find many articles and online classes. Many are. “tech to tech” explanations—which are great. File Size: 334.34 MB

ConversionXL & Khrystyna Grynko – BigQuery for Marketers

Google is the best search engine. BigQueryYou can find many articles and online classes. Many are. “tech to tech” explanations—which are great. However, they can be daunting. for Marketers who start their own marketing-To-Tech journey

But where do you start? Or, if you’re already using BigQueryYou can do so much more by using it.

Get started—or move faster—with this marketer-Focused tutorial
What is Google? BigQuery?

Google BigQuery A warehouse for analytics data. It’s a place where you can:

House your data for $0.02 per gigabyte (equivalent to 256 MP3 files).
Query your data for $5.00 for 5 Terabytes of queries (approximately 1,000,000 5).-minute songs).
Make nice (and totally free!) dashboards in Google Data Studio Google Data Studio allows you to create dashboards. for The price of the tool

Free storage for the first terabyte and 10 gigabytes per month of query data.
Google bigquery and connceted service diagram

BigQuery isn’t the only game in town. Other options include Amazon Redshift (Snowflake), Microsoft Azure SQL Data Warehouse (Microsoft Azure SQL Data Warehouse), Apache Hive, and many others.

Oft-The cited benefits of BigQuery include:

No database administration;
There are no restrictions
Imports quickly
You can process terabytes of data in just seconds
Data stored in USA or EU
Encryption;
Control of access;
Two-step verification;
Data-Prevention of loss
Integration with other Google Tools
$300 in test credits
Huge community;
Educational resources (courses, labs, etc.).

But why not try a cloud-based solution instead of your traditional digital analytics tool? BigQuery?
What are the pros and cons of tools like this? BigQuery?

Google Analytics, or any other digital analytics tool, gives you control of data collection and analysis. You can use a tool such as BigQueryThis gives you more control over all stages of the analytics infrastructure.
Diagram showing the comparison in access to analytics data between bigquery (Google Analytics) and bigquery.

It’s not the only difference. Google Analytics allows you to add CRM.-Office, or call-tracking data (via the API or Measurement Protocol), it’s still a suboptimal solution to consolidate your data.

You have little control over the Google Analytics system—if your data is sampled or altered because Analytics wants to, well, that’s your problem.
Hypothetical use case

Imagine you are in need of a monthly report containing data from Google Analytics. Your CRM, call tracking software and other sources. Each month you visit these tools and perform a search. for You can use this data to enrich your report.

But, sometimes, you can’t really access the CRM because you don’t have permissions (i.e. enterprise politics), or you’re at an agency and your client doesn’t want you to touch their CRM. Now, wait. for someone to send you the necessary data to integrate into your report, which—as it’s often happened to me—takes time.

Next, you must manually integrate all of the data. This takes time. By the 10th of the month, you have everything you need, but it’s kind of late to present these figures and make a decision about the actions to take that month.

Then think about it. “We can’t do this anymore—we have to automate!” Your client might be interested in some tools. “too expensive,” “too complicated,” To every option, etc. You can now look for The simplest and most cost-effective solution.

Get your instant download ConversionXL & Khrystyna Grynko – BigQuery for Marketers

To me, BigQuery That was the solution. I thought, and was eventually right, that the amount client data would never exceed the free threshold. We could also connect it to a Data Studio dashboard that is free and easy.

And that was it—a cheap and simple solution for Monthly reporting is a struggle.
Another use case

Imagine you want to know how much revenue your campaigns generated…

…and you sell houses.

…and you have a shitty custom CRM that can never connect to your Ads or Analytics platforms.

You know the number of leads, but you can’t connect them to house purchases. To pull data from Google Analytics and CRM into your house, you can give each lead and purchase a userID (like encrypted emails). BigQuery data warehouse, and then—with a simple SQL query—join the two tables.

It can be said that userID X, which was generated via Google Ads on January 11, brought us $500,000 revenue. Therefore, it’s okay that we spent $500 to get that lead.

Inspired? Here’s how to get started.
How to get started BigQuery
Main screen of Google Bigquery.

BigQuery This is part the Google Cloud Platform. You will need to first create or log in to a Gmail account. Next, go to Google Cloud Console and create a Cloud Project. It is easy to create these elements.

Notice: A credit card is required to open a Cloud account. This will allow you to receive $300 in credits to try the platform. Don’t be afraid—$300 is more than enough for vetting or educational purposes, and they won’t charge you without notifying you that your credits have run out.
Project selection screen in Google Bigquery.

If you don’t want to enter your credit card and only want to play with BigQuery Public data (there are many public datasets available). BigQueryYou can also use it to: BigQuery sandbox.

Simply enter a BigQuery after you have created a Cloud Project and accepted all terms. You’ll see a “Sandbox” Label at the top-Left corner
Google bigquery query editor

If you’re using only BigQuery This schema will give you a clear explanation of the structure of your Cloud Project.

Your BigQuery Interface with tables and datasets (covered later);
Jobs (i.e. tasks), which include every operation in your Cloud Project—query, save, import, export, etc.;
Team access is where you can grant access to particular elements or tasks in your project (e.g. BigQuery DataViewer access

Breakdown of components of cloud projects with bigquery

Google Cloud IAM manages accesses. It can be found in the menu (top).-in the left corner) to your Cloud Project.

In your Google Cloud account, you can also create an Organization. Users of GSuite (paid Gmail) and Cloud Identity owners have the option to create organizations.

An Organization can have its own billing account, projects, and can access other projects without having to access their billing accounts.
Diagram of account organization for Google bigquery.

We have an Organization that is a GSuite user in our agency. Sometimes, we even create projects for Our billing account is linked to the clients. In other cases (when the client already has a project on the Cloud Platform), we just link their project to our organization to work without access to our client’s billing account.
Bigquery’s dropdown menu for project selection

Once the project is created and you’re in BigQuery, you’ll need to know some SQL to start playing with your BigQuery data. SQL is not difficult to learn. There are many SQL queries examples available that can be tailored to your needs. (Here’s a great tutorial for SQL can be used BigQuery.)

Now, let’s look at some important steps for Use BigQuery. These can be divided into three phases:

Before. Get data into BigQuery.
During. Work with BigQuery data.
After. Create dashboards with BigQuery data.

1. Get data into BigQuery
Build your data schema

Before you begin your BigQuery As you embark on your journey, I suggest that you create a data schema. Plan out the datasets, tables, and table fields you’ll need. Ask these questions to get started:

What is my goal? BigQuery? For example: “I want to automate my current reports.”
What data are you currently using in your reports? You might, for example: “I have CRM, Google Analytics, and back-office data.”
What KPIs are you able to export from your CRM and Google Analytics? You could, for example: “Sessions per source per month per store, purchases per source per month per store, leads per source per month per store, etc.”

The taxonomy BigQuery These are the flows:

Projects > datasets > tables and views.

For me, one dataset = one data source. For the answers to the above questions, three datasets are required (CRM, Google Analytics and back office).

If you have many brands, one table can be considered one brand. If you want to store previous years separately (because you rarely use previous years’ data) you can have one table per year. That’s for you to decide. You’re charged less for Lang-Term data storage (i.e. data you didn’t change in the last 90 days).

Once you’ve answered all the above questions, you can start building your schema. I used a database schema software to do it. (There are plenty of them on the Internet—and always one that’s absolutely free.).
sample data schema.

After building a schema—which, honestly, you can sketch out on paper—start creating your datasets. Click on the project name (e.g. “angular-radar-255111” Below is an example. You can modify the random project ID that Google Cloud assigns to your project name.

Next, click on “Create Dataset.”
dataset creation in google bigquery.

In most cases, you will only need to name your data and choose a place. for your data. If your client is located in the EU, choose an EU location (GDPR!). You’ll notice a table expiration of 60 days if you use a BigQuery Sandbox, the version that is free as mentioned previously.
Dataset options in bigquery
Google services

Google offers some built-in services-In services to import data BigQuery. The first one is BigQuery Data Transfer allows you to access data from Google Ads or Cloud Storage, Amazon S3, Google Play, YouTube, and Amazon S3. It’s free for Amazon Cloud Storage and Amazon S3

BigQuery also connects to Google Drive (Google Sheets and CSV, Avro, or JSON files), but the data is stored in Drive—not in BigQuery. However, you can query it directly from Drive.

It has a fascinating use-Example: On a daily basis, data must be manually added to Google Sheets. A recruitment agency might fill out a sheet at the end each day listing the number of applicants received and the placements of those candidates.

This link will take you to Google Sheets.
Connection options and table creation for bigquery.

Google Analytics 360 and Firebase (Blaze plan) provide free integrations with BigQuery. For other tools and a standard Google Analytics version, you’ll have to use non-Google connectors
Use other-Google services

Some CRMs offer native integration with cloud data warehouses. BigQuery. Our clients often have custom CRMs. Therefore, we asked their developers to design a connector to Cloud Storage. BigQuery.

There are many paid connectors that can be used to extend your Google Analytics account. These connects cost around $100 for 100K monthly visitors. Pulling your Google Analytics data into BigQuery These are its benefits:

No sampling
You have the data.
Non-aggregate stats;
Ability to create custom models of attribution, etc.

BigQuery is a popular service—it’s not hard to find connectors for Any ad and analytics platform will do.
Get your developers to work

We previously discussed a solution to creating your own connector. There are two options here—to BigQuery Directly or first to Cloud Storage. Both offer API documentation that will assist your developers.

In one of our use case, we asked developers to send two CSV file (one from our CRM and another with back).-office data) every midnight with the previous day’s data to Cloud Storage.

In terms of development, it was the cheapest solution—the dev team had to export only two CSVs, once per day. Next, we used Cloud Functions to pull the latest files from Cloud Storage into our cloud storage. BigQuery tables.
Diagram of bigquery import.

Google Cloud Functions can automate simple tasks with their lightweight solution. They are composed of JavaScript/Python/Go and a trigger (rule). I found a Medium code and customized it to my requirements.

Here’s a code that you can use in your project:
Function creation with Python for Google bigquery.

Some BigQuery professionals won’t like this solution. It can lead to many problems.

Two times cloud function activation for One file. It’s happened to me a few times, and the reason remains unclear.
Cloud function not activating at all Sometimes it was due to the incorrect data format (different than the BigQuery table) in a CSV file; in other cases, the reason was unclear—I just had to re-You can manually import the CSV file.
Quality control issues. If there’s a problem with CSV file data, you can’t block it from getting into your BigQuery SQL is required to clean up data from tables. CSV files must have the same number and format of data as your tables. BigQuery table will be imported, so—in most cases—the data structure and data format issues are managed.

I chose it because it was simple and affordable. for my client and it works pretty well—for now.
Batch vs. stream processing

You have two options to send your data directly to the Cloud: streaming or batch. Batch processing sends data one per period (e.g. the data from the previous morning at 1:00 a.m.). It’s a good option unless you want real-time data.

Mobile applications are a great example—you may want to know in real time if there are issues with your application. Streaming data can be more difficult than batching.

A Cloud Dataflow and/or other services may be required to create a streaming stream. This topic would require a separate article. We’ll stick to batch processing for now.
2. Data in BigQuery
The BigQuery Interface

Let’s take a look at the BigQuery interface. Some articles/courses use the older version of BigQuery:
Table details and query.

The interface has been updated to be similar to the original.
Bigquery query editor

Here are the parts:

Your query history. Queries you’ve run previously. It’s especially useful when you run tests but forget to register the best queries you might need later.
Saved query. Where to find registered queries. These should be clearly named so that you can quickly find them later.
History of work. The history of events in BigQuery—imports, exports, task history, etc.
Transfers. Data Transfers is a Google service that allows you to import Google data (e.g. Advertisements, Play, and YouTube BigQuery.
Scheduled queries. Register your queries and have them run every hour, day, week, etc.
BI engines. This new feature integrates with Google Data Studio, a familiar Google tool, to speed up data exploration and analysis. The basic idea is that a BigQuery enhancer.
Resources. Pin a project for it to be at the top. It’s super useful when you have lots of projects but work often on just a few. You can also add a public dataset to play with data if you want to learn the tool but don’t have your own.

Bigquery Project: Add data The “Create connection” Option is for Developers have the option to directly send data to BigQuery.
Introduction to queries, tables, views and datasets

It’s easiest to understand the structure of a BigQuery Google Analytics analogy for project:

A BigQuery Project is similar to a Google Analytics account. One account can be created per brand or company. One Analytics account = one company. BigQuery project.
A BigQuery dataset is like a Google Analytics property—you create one per data source (e.g., website, application).
A BigQuery A table or view can be compared to a Google Analytics view. To view and subdivide your data, you can create a view or table.

You can create, delete, copy and modify data in a project.
Bigquery projects contain tables and datasets.

Clicking on a table will open a menu that allows you to query, copy or delete the data, as well as export.
How to query, copy and delete data from bigquery.

Get your instant download ConversionXL & Khrystyna Grynko – BigQuery for Marketers

Export your table to Cloud Storage, examine it in Data Studio or scan it using the Google Data Loss Prevention Service (all available via the “Export” button).

If you find yourself running a particular query often, it’s simpler to create a view. A view is a table that’s based on your query and created when you work with it. You can use it in Data Studio, which we’ll talk about later.
Example query in bigquery

A query is your SQL code—how you communicate with your BigQuery data. To retrieve the data you require from the tables, create a query.
bigquery query.

This is only one example of a query. It can be a long piece of code, but the object of this article isn’t to teach you SQL.
Some best practices for Data in BigQuery

To use BigQuery These tips will help you work more efficiently

Use denormalized tables. Try to group your data into only a few tables; don’t create a table for A small number of fields. Consider, for instance, a table that contains data on “purchases per agency,” A second table contains the postal addresses of these agencies. To “denormalize” You can combine these data into one table.

Bigquery data denormalization

Preview mode. The Preview tab is a helpful tool to help you understand the data in your table before you start querying it. It’ll help you understand the kind of data you have available in your table before you start creating:

data preview in bigquery.

Use the Query Validator. When you create a query, you’ll see a red or green circle in the right corner under your query table. If your query is red, it means that it contains an error. You should fix it before running it.

In bigquery, query validation issue
Bigquery accepts query validation

NO * SELECT. Don’t use the “Select all” option (Select *). You can always visit the Preview mode to view all your table data. Remember: You pay for the queries, even if that’s simply consuming your free allocation.
Use table expiration. If you know that you won’t need a table after some period of time, you can set a table expiration option. Remember that you also have to pay for Storage is a great option.
Divide. It’s possible to “split” A date column can be used to divide a table into smaller subtables. It is usually divided by the day. It’s a way to optimize query cost—when you need data for A particular time period BigQuery Search for Subtables per day, first. Then you can get data from subtables and not the whole gazillion.-Row table. When creating a table, you can create a part.
Use pricing calculator. BigQuery This pricing calculator allows you to see an estimate. for data storage or a query.
Control costs (custom quotas). Google Cloud has many ways to limit the usage of its resources (e.g. Limit the number of queries that you launch (e.g. You can place limitations for “maximum per query cost,” “per user daily budget,” “per project daily budget.”

3. Create dashboards using BigQuery Data
Google Data Studio

To create a Data Studio dashboard using your BigQuery data, either to open or create a new dashboard. Click on “Create New Data Source”:
new report interface in data studio.

Select “BigQuery” You can find them all. After that, you’ll refine your selection by project and dataset.
Google data studio: Add bigquery as data source

There are two options: a table or view. My choice was a view, as it’s basically a pre-Created query with only the data I require. You can connect to a table directly and use Google Data Studio to do all the magic.
Datastudio connection setup for bigquery.

When you connect to a view or a table, you’ll see the fields available in your data source:
Connection editing with bigquery

Click on to continue “Add to Report,” You create a connection between the data source and your data source (BigQuery Data Studio and view (or table). So go ahead, you’re ready to create a dataviz with your BigQuery data.
Google Sheets

If you’re a GSuite user, you can use a native BigQuery Connect with your Google Sheet using the connector
Connector bigquery in google sheets

Click on to continue “Connect to BigQuery,” you’ll have to choose a BigQuery Create a project, then create a query just like you would in the BigQuery interface:
Bigquery data connection.
Bigquery editor.

One problem: You can’t schedule a query—at least not yet. (The BigQuery The connector is new. You’ll have to refresh the query regularly to fill your Google Sheets table with the newest data.
refresh data in bigquery.

Once you have your data pulled into Google Sheets, it is possible to start creating Google Sheets dashboards. Non-English speaking users:-Google Sheets Add for GSuite users-Ons (free and for a fee) that allow you to pull in. BigQuery data. OWOX BI is used at our agency. BigQuery Reports are also available to help you plan your queries.
Non-Google tools

Of course, you’re not limited to Google Data Studio or Google Sheets. Here are some data tools that you can easily integrate with. BigQuery:

Power BI
Tableau;
QlikView;
OWOX Smart Data.

The list is limited to my own knowledge—I’m sure there are tons of other options.
Conclusion

BigQuery This is a great way to consolidate your data. This service offers many options to learn, test, and embrace. Google Cloud is a great resource for learning about Google Cloud. BigQueryCheck out these courses to learn SQL and more.

Google Cloud Platform Specialization: Data to Insights
SQL For Data Science With Google BigQuery

There’s a great BigQuery community out there, too, so don’t be afraid to search for Questions? Ask questions.

You can move further as you make progress BigQueryUse the integrated machine-Pre-programmed learning models-Templates created. Segment your audience based on their potential to buy, predict customer lifetime worth, etc.

Download immediately ConversionXL & Khrystyna Grynko – BigQuery for Marketers

If you learn the basics, you’re most of the way there.

Readmore: http://archive.is/BYQol