Optimize Your Marketing ROI: 6 Ways to Upload Data to BigQuery

If you’re like many marketers, you’re probably running campaigns using a variety of digital marketing channels — including not only Google Ads and Bing Ads but also Facebook, Instagram, Twitter, LinkedIn, Tiktok, Criteo, and many more. 

Integrating ads data into Google BigQuery can help maximize omnichannel ROI from marketing campaigns by providing a comprehensive and centralized view of your advertising performance. 

By consolidating data from various digital marketing channels, marketers can gain valuable insights into the effectiveness of their campaigns across different platforms, touchpoints, and devices. 

Over 80% of marketers believe that integrating data from multiple channels is crucial for achieving a holistic view of customer journeys, this view allows them to identify patterns, trends, and correlations that can inform strategic decision-making and optimize marketing efforts for better results. 

In this blog post, we’ll examine how you can get everything together and upload data to BigQuery - one data storage to paint a good picture of campaign performance and improve the ROI across all your multiple advertising channels.

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

Note: This post was originally published in Dec 2020 and was completely updated in January 2024 for accuracy and comprehensiveness.

With OWOX BI, the advertising data from your paid campaigns will be automatically pulled into Google BigQuery. You can store it for as long as you want, create detailed reports with automated data preparations, understand the true value of your paid marketing activities, and make better use of your advertising spend.

Today’s customer journeys are more complex than ever.

The numbers speak for themselves: according to Podium, 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, Forbes says that 62% of consumers have said 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 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 fall behind.

According to Gartner, most marketing analytics teams still spend most of their time organizing data, running ad-hoc queries, and generating reports rather than 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 matters to your organization, such as extracting hidden insights and using them to make better, smarter marketing decisions.

Before looking for insights into your data, you must collect it in one place and merge into a unified view. The most widely used data warehouse for marketing data is Google BigQuery — a fully managed serverless data warehouse part of Google’s infrastructure.

BigQuery plays a critical role in the landscape of digital marketing, especially when it comes to managing and analyzing large-scale data operations. 

As marketers increasingly rely on data from various channels and platforms, BigQuery stands out for its ability to efficiently process and analyze massive datasets. This powerful tool allows for real-time data analysis, providing marketers with timely insights into customer behavior and campaign effectiveness. 

With BigQuery, marketing teams can integrate data from multiple sources, enabling a holistic view of marketing efforts. This data integration and analysis level is essential for making informed decisions, optimizing marketing strategies, and ultimately driving better business outcomes. BigQuery's scalability and speed make it an indispensable asset for any data-driven marketing team leveraging big data for strategic advantage.

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. Let's look at some of the key features of using 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.

• BigQuery significantly simplifies the data analysis process as it does not require the deployment of clusters or virtual machines. Users do not have to manage keys, indexes, or install any software.

• 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. One of the standout features of BigQuery is its ability to use thousands of cores for a single query. This massively parallel processing capability allows BigQuery to handle extremely large datasets and complex queries easily.

• BigQuery allows for streaming millions of rows per second, enabling real-time data analysis. This feature is particularly beneficial for applications requiring immediate insights from live data, such as monitoring user activity on websites, tracking IoT device outputs, or real-time financial transactions.

• 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.

• BigQuery's architecture decouples storage and compute resources, providing several advantages. This separation means you can scale storage and computing independently, allowing for more cost-effective and efficient resource management.

However, one big problem with BigQuery is that it doesn’t natively integrate with non-Google services like Facebook or Linkedin Ads.

You need third-party connectors to incorporate touchpoints from non-Google data sources. 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 several ways. The main task is finding a way to fit your business, requiring minimum effort, saving maximum time, and providing reliable and accurate results.

Let’s look at methods you can use to import data to BigQuery:

1. Upload data using third-party ETL tools. For example, OWOX BI Pipeline.

2. Upload data with Google Sheets (and BigQuery Reports Extension).

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 Data Transfer or Google Analytics 4.

6. Get the data with BigQuery API.

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

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 way to load data into Google BigQuery.

Before delving into data uploads in BigQuery, it's crucial to understand the preparatory steps that ensure a smooth process. This involves data ingestion – the initial transfer of data from various sources, followed by creating datasets and tables within BigQuery. These foundational steps are key to organizing and structuring data for efficient analysis and querying.

Data ingestion refers to moving data from various sources into BigQuery for storage, analysis, and processing. This process is a critical step in data management and analysis and involves several key aspects when dealing with BigQuery.

Selecting the right data ingestion format is critical for successful data uploads to BigQuery. Key factors to consider include:

• Schema Support: BigQuery can generate a table schema from the source data automatically. Formats such as Avro, ORC, and Parquet are self-describing and don't require an additional schema definition. However, for formats like JSON and CSV, you should explicitly provide a schema to ensure proper data organization and interpretation.

• Handling of Data Types - Flat, Nested, and Repeated Fields: BigQuery is versatile in handling various data types. It supports hierarchical data structures crucial for representing complex data relationships. Formats, including Avro, ORC, Parquet, and Firestore exports, are adept at managing data with nested and repeated fields, offering a robust way to express hierarchical data.

• Embedded Newlines in JSON Files: For JSON file uploads, BigQuery requires that each row be separated by a newline. This means the JSON files must be newline-delimited, with each line containing a single, complete data record.

• Data Encoding Standards: BigQuery is compatible with UTF-8 encoding for all data types, whether flat, nested, or repeated.

• Additionally, BigQuery can handle ISO-8859-1 encoding when working with CSV files, but this is specifically for flat data structures.

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: The physical storage location will not be defined if you select “Never” for table expiration. You can specify the number of days to store them for temporary tables.

Next, create a table in the dataset. You can use BigQuery data manipulation language (DML), which enables you to update, insert, and delete data from your BigQuery tables.

You can execute DML statements just as you would a SELECT statement, with the following conditions:

• You must use GoogleSQL. To enable GoogleSQL, see Switching SQL dialects.

• You cannot specify a destination table for the query.

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

Having covered the essential pre-steps of data ingestion, dataset creation, and table setup, let's now dive into the various methods of loading data into BigQuery. This exploration will guide us through different ways, each tailored to specific data types and sources, ensuring a comprehensive understanding of efficiently importing data into BigQuery for advanced analytics.

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. You first need to consolidate your data 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 of your marketing platforms, including Bing, LinkedIn, Twitter, Instagram & Facebook Ads 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 can access 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. OWOX BI can backfill your BigQuery dataset with historical data from your ad account so you can start working immediately.

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

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

To set up data collection, you must have BigQuery Data Editor and BigQuery User roles in the project where 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 + NEW… 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 Google Workspace Marketplace.

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 to BigQuery, 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 add fields manually, 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, use the advanced options.

For more information on the CSV to BigQuery 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 store and transfer data online securely.

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 learn 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 using 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 Data Transfers from the left-hand menu.

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

If you have not enabled Data Transfer API earlier in your project, you may have the following notification:

Just follow the link provided in the notification and click on the "Enable" button:

You will then be taken to the 'Data Transfer Service' screen, where you can create a Data 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.

Using 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 search bar you can find the required API. In our case it is BigQuery API:

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 your on-site analytics, advertising services, offline sales, call tracking systems, and CRM systems into Google BigQuery.