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, TiktokCriteo, 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.

Automate your digital marketing reporting

Manage and analyze all your data in one place! Access fresh & reliable data with OWOX BI — an all-in-one reporting and analytics tool

Start Free Trial
Automate your digital marketing reporting

Challenges in Today's Digital Marketing Landscape

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.

Salesforce report

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.

Understanding BigQuery for Data Uploads and Imports

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.

Key Benefits of Using Google Bigquery For Ads Data Analysis

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.

Dive deeper with this read

Google BigQuery: The Best Marketing Data Warehouse

Image for article: Google BigQuery: The Best Marketing Data Warehouse

Different Methods to Load Data into BigQuery

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.

Preparing Data for Uploading in BigQuery: Ingestion, Dataset Creation, and Table Setup

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 Format

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.

Creating a dataset and table

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.

BigQuery home page

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.

Step-by-Step Guide on How to Load Data into BigQuery

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.

Way 1. Upload data to BigQuery with Third Party Data Tools: OWOX BI Pipeline

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?

Bring together data from multiple systems

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 service

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.

Save 70+ hours on data preparation

Spend time reaching your monthly KPIs instead of collecting the data or building reports

Start Free Trial
Automate your digital marketing reporting

Crunch mountains of data

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.

Cut down on repetitive tasks

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.

Never lose control over your historical data

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.

Conduct comprehensive data analysis

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.

Visualize data

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.

All-in-one Digital marketing Dashboard

All-in-one Digital marketing Dashboard

Download template

How to upload data to BigQuery with OWOX BI Pipeline

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.

  1. Provide access to your advertising account.
  2. Provide access to the Google BigQuery account where you want to store the exported data.
  3. Select a Google BigQuery project and create a dataset you want to upload your data to (or choose an existing dataset).
  4. 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.
  5. 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.

Way 2. Upload data to BigQuery with Google Sheets

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.

Way 3. Upload data to Bigquery with CSV or JSON files

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.

CSV file

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.

JSON file

Way 4. Upload data to Bigquery from Google Cloud Storage

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

Useful information about working with this service:

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

  • CSV

  • JSON (newline delimited)

  • Avro

  • Parquet

  • ORC

  • Cloud Datastore

Google Cloud Storage

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.

Way 5. Upload data to BigQuery from other Google services (Google Ads and Google Ad Manager)

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.

BigQuery Data Transfer Service

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.

Dive deeper with this read

How to upload raw data from Google Ads to Google BigQuery

Image for article: How to upload raw data from Google Ads to Google BigQuery

Way 6. Upload data to Bigquery by downloading data with the BigQuery API

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.

BigQuery API

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:

Useful links

Key Takeaways

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.

Automate your digital marketing reporting

Manage and analyze all your data in one place! Access fresh & reliable data with OWOX BI — an all-in-one reporting and analytics tool

Start Free Trial
Automate your digital marketing reporting

FAQ

Expand all Close all
  • What factors should be considered when choosing a method to upload ad data to Google BigQuery?

    When choosing a method to upload ad data to Google BigQuery, consider factors like data volume, source compatibility, automation level, technical expertise required, and the specific analytics needs of the business.
  • What is the role of the BigQuery API in uploading ad data?

    The BigQuery API facilitates programmatic data upload, allowing custom integration and automation of data transfer from various sources into BigQuery.
  • What is the process of uploading ad data using Google Sheets?

    Using Google Sheets for ad data upload involves adding data to a sheet and then using an add-on like OWOX BI BigQuery Reports to automate its transfer to BigQuery.
  • How can third-party tools be used to upload ad data?

    Third-party tools automate the process of uploading ad data to Google BigQuery, streamlining data integration from various platforms and simplifying analytics and reporting for more efficient marketing decision-making.
  • What is the purpose of integrating advertising data into Google BigQuery?

    Integrating advertising data into Google BigQuery enables centralized analysis of diverse marketing channels, enhancing decision-making, improving ROI, and providing a comprehensive view of customer journeys.
  • How do I upload my ads data to BigQuery?

    There are several ways to upload your ads data to BigQuery, including using the Google Ads API or the Google Analytics API. You can also use third-party tools like OWOX BI, which offers an easy-to-use UI for uploading data to BigQuery.
  • What are some benefits of storing ads data in BigQuery?

    Storing your ads data in BigQuery provides several benefits, including fast querying and analysis, scalability, and the ability to combine different types of data (e.g. advertising data with CRM data) for more comprehensive insights.
  • Can I upload data from multiple advertising platforms to BigQuery?

    Yes, you can upload data from multiple advertising platforms to BigQuery. You just need to make sure that the data is formatted correctly and that you have the necessary permissions to access the data.