6 ways to upload data to Google BigQuery

If you’re like many marketers, you’re probably running campaigns using a variety of digital marketing channels — including not only Google Ads but also Facebook, Instagram, Twitter, LinkedIn, and many more. With no money to waste, you aim to get the most out of each marketing investment. And to do that, you need a clear, holistic view of your customers’ journeys across all channels and devices.

In this blog post, we’ll examine how you can get everything together and upload all marketing data into one data storage (e. g. Google BigQuery) to paint a good picture of campaign performance and improve the ROI across all your multiple ad channels.

Omnichannel marketing analytics has become the key tool for organizations of any size and industry. Yet, most marketers are wasting time on routine activities such as organizing data manually. 

Today’s customer journeys are more complex than ever. The numbers speak for themselves: according to the Salesforce’s 2020 State of the Connected Customer report, 72% of consumers have used multiple methods of communication to start and complete a single transaction. 63% of consumers across generations — from Gen Z to the Silent Generation — have used multiple devices to start and complete a single transaction. And on top of that, 62% of consumers have said that they prefer personalized products or services over standard, ’one-size-fits-all’ offers.

A single customer journey can take anywhere from a few minutes to several months. It may occur in one channel or span multiple touchpoints and devices. Take for example: a user can first visit your website via your Facebook ad on their smartphone, only to sign up and make a purchase via a search ad on their laptop, days later after the initial interaction. In today’s multi-channel, multi-device landscape, the consumer is one click or tap away from leaving your brand for a competitor.

If you’re like most marketers, you’re probably struggling to stay connected with your customers and keep them engaged across the internet. You need data — that goes without saying. Like most marketers, you’re probably already sifting through countless campaign reports in an effort to spot trends and patterns in customer behavior, identify winning tactics, and optimize your marketing strategy. You keep an eye out for some chosen metrics and the rest is just piling up in the ad platform, never really being analyzed or used.

But here’s the caveat: If you’re not focused on value, you’re falling behind.

According to Gartner, most marketing analytics teams are still spending most of their time organizing data, running ad-hoc queries, and generating reports rather than actually using their data to inform their marketing decisions. The result? Gartner reports that more than half of senior marketing leaders are disappointed in the results of their analytics investments.

Automating routine activities will enable you to focus on what really matters for your organization, such as extracting hidden insights and using them to make better, smarter marketing decisions.

Still, before looking for insights in your data, you have to collect and merge it in one place. The most widely used solution for this is Google BigQuery — a fully managed serverless data warehouse that’s part of Google’s infrastructure.

One of the major stumbling blocks to omnichannel marketing is that data is stored in silos that don’t readily communicate with each other. If you run paid marketing campaigns using many different platforms, you already know the struggle of keeping a close eye on each campaign and trying to piece the data together across all your marketing initiatives.

Why do you need to load data into one storage? If you want to use end-to-end analytics, use raw data for creating reports, and measure the efficiency of your marketing, then you should use Google BigQuery.

Google BigQuery is a serverless, scalable data warehouse with a built-in query service. It provides marketers with a single centralized location for advanced data-driven marketing, without the need for extensive technical skills, reliance on IT, or significant money investments.

Google BigQuery (GBQ) allows you to collect data from different sources and analyze it using SQL queries. Among the advantages of GBQ are its high speed of calculations – even with large volumes of data – and its low cost.

If you need to analyze terabytes of data in seconds, Google BigQuery is the easiest and most affordable choice. You can learn more about this service by watching a short video on the Google Developers YouTube channel.

There is, however, one big problem with BigQuery — it doesn’t natively integrate with non-Google services.

You need third-party connectors to incorporate touchpoints from non-Google data sources. That is, if you want to pull data from your website, CRM, ESP, or ad platforms such as Facebook Ads to BigQuery, you need a service like OWOX BI to do the heavy lifting for you.

You can upload your ad data to Google BigQuery in a number of ways. The main task is to find a way that will fit your business, requiring minimum effort, saving maximum time, and providing reliable and accurate results.

Let’s take a look at 6 methods you can use to upload your data to Google BigQuery:

1. Upload data using third-party tools. For example, OWOX BI Pipeline.
2. Upload data with Google Sheets (OWOX BI BigQuery Reports Add-on).
3. Upload data from CSV or JSON files.
4. Upload data from Google Cloud Storage.
5. Upload data from other Google services such as Google Ads and Google Ad Manager.
6. Download data with the BigQuery API.

Each of these methods has pros and cons, as well as business challenges where they work best. Still, to keep up with the pace of the modern world, it is preferable to save your time and effort for tasks you cannot delegate, by automating and simplifying processes as much as possible.

For most businesses, the most optimal decision is to choose a single service that satisfies their needs in the fastest and easiest way. Below we will take a closer look at each of the ways to load data into Google BigQuery.

Before you upload any data, you need to create a dataset and table in Google BigQuery. To do this, on the BigQuery home page, select the resource in which you want to create a dataset.

In the Create dataset window, give your dataset an ID, select a data location, and set the default table expiration period.

Note: If you select “Never” for table expiration, the physical storage location will not be defined. For temporary tables, you can specify the number of days to store them.

Next, create a table in the dataset.

It’s ready! Now you can start loading data.

OWOX BI is a comprehensive Business Intelligence system designed to facilitate marketing analytics and decision-making. With OWOX BI, marketers can automatically centralize disparate data from various sources and use this consolidated data to:

• analyze their marketing efforts
• create automated reports
• reveal new insights into customer interactions with the organization
• optimize your marketing investments for better ROI.

You won’t have to write a single line of code, look around for connectors, or prepare the data manually. OWOX BI will handle it all.

Now, what exactly can OWOX BI and Google BigQuery help you do?

It’s only after that data is consolidated in one centralized location that it becomes actionable. The first thing you need to do is to consolidate your data effectively so you can easily process, analyze, and extract insights from all those numerous customer touchpoints. You need to know that you trust your data, and that you’re comparing apples to apples.

OWOX BI provides a set of connectors to automatically pull raw data from all your marketing platforms, including LinkedIn, Twitter, Instagram and Facebook, to BigQuery. The result is one ready-to-use dataset with automated daily updates. Moreover, you’ll be able to connect this data with the data from other touchpoints, including raw real-time data from your website and the data from your CRM, call tracking, and email marketing systems. The only thing left for you to do is to buckle down and focus on putting the data to action.

BigQuery is part of the Google Cloud Platform, which means you get access to Google’s cloud computing and storage capabilities. You can have terabytes of data stored here, including all your historical data, and run queries against billions of rows in a matter of seconds.

In traditional scenarios, marketers have to manually switch back and forth between advertising services to extract data snapshots and organize them into reports and dashboards. Automating these processes with OWOX BI and BigQuery removes the headaches of performing regular data updates and ensures that you’ll have the most recent data at your fingertips. OWOX BI will also retrospectively refresh cost data in Google BigQuery if it changes in your ad account.

Most marketing platforms have limits on how much data you can store or how long you can access the data. With Google BigQuery, you can preserve data from all your marketing platforms for as long as you want, and analyze it to capture not-so-obvious trends that only become visible over extended periods of time. OWOX BI can backfill your BigQuery dataset with historical data from your ad account so you can start working right away.

With data in BigQuery, you’ll be able to create custom metrics and dimensions that are unavailable in the original data, create arbitrary parameter combinations, apply custom filters to your reports.

And if you’re not into writing SQL queries, OWOX BI has got you covered. You can use the Report Builder in OWOX BI Smart Data to ask questions of your data in natural English and get answers in the form of neatly organized reports with specific metrics you need based on your data.

Create actionable data visualizations by connecting BigQuery to your favorite visualization tools, or exporting your reports to data visualization services such as Google Data Studio. To do this, you won’t have to use multiple data sources: a single BigQuery connector is enough.

To set up data collection, you must have BigQuery Data Editor and BigQuery User roles in the project in which you want to collect data. Once these roles are present in your project, follow the steps below to upload your ad data to Google BigQuery:

1. In your OWOX BI dashboard, click Create pipeline.

2. Select the data source you want to connect.
   

3. Select Google BigQuery as the destination.

4. Provide access to your advertising account.
   
5. Provide access to the Google BigQuery account where you want to store the exported data.
6. Select a Google BigQuery project and create a dataset you want to upload your data to (or choose an existing dataset).
7. Specify the date that marks the beginning of the period for which you want to upload cost data and select the currency you want your cost data to be converted to.
8. Click Create pipeline.

Done! Data will be collected automatically in Google BigQuery, and you’ll get ready-made sets of complete data from your selected sources.

If you need to upload data from Google Sheets to Google BigQuery, the easiest way to do that is to install the free OWOX BI BigQuery Reports Add-on.

You can install this add-on directly from Google Sheets or from the Chrome Web Store.

After installing it, a dialog box will appear with tips and permission requests.

Now it’s time to go back to Google Sheets. To upload data to BigQuery, just select Upload data to BigQuery from the Add-ons –> OWOX BI BigQuery Reports menu.

Specify the project, dataset, and name of the table to upload the data to. And that’s all :)

An undeniable advantage of the OWOX BI BigQuery Reports Add-on is its ease of use. You can also use the add-on to set up scheduled reports.

You can manually upload a CSV or JSON file with ad data directly to Google BigQuery from Google Cloud Storage, Google Drive, or your computer.

To upload data from a CSV file, in the Create table window, select a data source and use the Upload option.

Then select the file and file format.

Next, define the destination for the data, specifying the name of the project and the dataset.

Note: In Google BigQuery, you can select two types of tables: native and external.

Google BigQuery will automatically determine the table structure, but if you want to manually add fields, you can use either the text revision function or the + Add field button.

Note: if you want to change how Google BigQuery parses data from the CSV file, you can use the advanced options.

For more information on the CSV format, see this detailed documentation from the Internet Society.

To upload data from JSON files, repeat all the steps create or select the dataset and table you’re working with – only select JSON as the file format.

You can upload a JSON file from your computer, Google Cloud Storage, or Google Drive disk.

Google Cloud Storage allows you to securely store and transfer data online.

Useful information about working with this service:

• Getting Started with Google Cloud Storage
• Cloud Storage documentation
• Quickstarts
• Choosing your storage and database on Google Cloud Platform

You can upload files in the following formats from Google Cloud Storage to Google BigQuery:

• CSV
• JSON (newline delimited)
• Avro
• Parquet
• ORC
• Cloud Datastore

You can read more about using Cloud Storage with big data in the official documentation.

You can also find out about data download limits and Cloud Storage permissions in the Google Cloud help center.

To upload data from various Google services, you first need to configure the BigQuery Data Transfer Service. Before you can use it, you must select or create a data project and, in most cases, enable billing for it. For example, billing is mandatory for these services:

• Campaign Manager
• Google Ad Manager
• Google Ads
• Google Play (beta)
• YouTube – Channel Reports
• YouTube – Content Owner Reports

Note: Read more about billing settings and changes in the Google Cloud help center.

To start the BigQuery Data Transfer Service, on the BigQuery home page, select Transfers from the left-hand menu.

Note: You'll need admin access to create a Transfer.

In the next window, all you have to do is select the data source you want.

Note: The BigQuery Data Transfer Service can be accessed not only from the platform console but also from:

• сlassic bq_ui
• bq command-line tool
• BigQuery Data Transfer Service API

Once configured, the service will automatically and regularly upload data to BigQuery. However, you cannot use it to download data from BigQuery.

By means of Cloud Client Libraries, you can use your favorite programming language to work with the Google BigQuery API.

Note: You can find more details about downloading data using the API in the Google Cloud documentation.

To start, you need to create or select the project with which you’ll work. Then on the home page, go to the APIs section.

In the APIs overview window, you can enable APIs and services and select the API from the library.

In the library, you can use a field search or filter APIs by the category.

You can use a set of Python scripts from OWOX BI to automate the import of data into Google BigQuery.

You’ll find scripts to automate the import of data into Google BigQuery from the following sources:

• amoCRM
• FTP
• FTPS
• HTTP(S)
• Intercom
• ExpertSender
• MySQL
• SFTP

You can download these Python scripts from GitHub.

Learn how to use Python while working with the Google API in this video lesson on the Google Developers YouTube channel.

1. BigQuery data structure in Google: How to get started with cloud storage
2. Top 6 BigQuery Visualization Tools
3. Automate reports in Google Sheets with data from Google BigQuery
4. Standard SQL in Google BigQuery: Advantages and Examples of Use in Marketing
5. Google tutorial on loading data to BigQuery
6. Loading Data into BigQuery
7. Google Developers YouTube Channel
8. Google Cloud Platform YouTube Channel

In this article, we considered options for uploading data to Google BigQuery cloud storage. We considered easy ways of loading data from CSV/JSON files and ways of uploading through an API or add-on.

If you’re looking for a convenient connector for transferring data to Google BigQuery, we recommend OWOX BI Pipeline. It merges data from Google Analytics, advertising services, websites, offline stores, call tracking systems, and CRM systems into Google BigQuery.