How to get your analytics, CRM and media data into BigQuery

Is BigQuery part of your data stack? If yes, this tutorial will guide you on how to set up your automated data pipelines. Google has great support for it’s own products but in 99% of all cases, you have other platforms on your marketing stack. In this article we will guide you how to stream all your analytics, CRM and media data into BigQuery. The currently supported platforms for this tutorial are:

Google Ads	Facebook Ads	Bing Ads	Google Analytics 4
LinkedIn Ads	TikTok Ads	AdRoll Ads	HubSpot
Criteo	RTB House	Twitter Ads	Salesforce
AppNexus	Google Display & Video 360	Google Campaign Manager (CM360)	Stripe
Taboola	Outbrain	Amazon Seller Central & Amazon MWS	Shopify

Prerequisites before getting started

Access to all the channels you would like to integrate
Google Cloud account with BigQuery

There are a few steps involved to get your data pipelines set up

Connecting your data
Streaming of the data into BigQuery
- Option 1: Manual CSV export
- Option 2: Automated data streaming

The main focus of this article will be around setting up data pipelines to BigQuery.

Let’s get started.

Connecting your data

To begin with we have to connect our data to a ETL platform. In this example we’ll use our platform where I connected all the data of our channels.

Of course you can add channels as you like (see the table at the beginning of this article for more details).

To get started, head to our setup page and connect your data. Free forever plan, no credit card.

The platform is self explanatory and requires only a few clicks. If you think it’s too complex you can check out which buttons to click here.

Try Windsor.ai today

Access all your data from your favorite sources in one place.
Get started for free with a 30 - day trial.

Start Free Trial

Getting your data into BigQuery

There are two options here. Option one is a manual CSV export and upload, which works fine for a one-time analysis or a small dataset but needs to be repeated every time you want fresh data. Option two is the automated data streaming, which is what you’ll want if you’re setting up an ongoing pipeline rather than a one-off export.

Option 1: Manual CSV export

Export the report you need from your connected platform (or from Windsor.ai’s own preview) as a CSV, then in BigQuery: open your project and dataset, click Create table, choose Upload and select your CSV, use auto-detect or define the schema yourself, and click Create to load the data. This has no scheduling and needs to be repeated manually every time, so it’s best for a one-off check rather than ongoing reporting.

Option 2: Automated data streaming

Requirements for Option 2:

The steps are:

Connect your analytics, media, CRM data (or any other source you want, we support 350+ sources) and configure the fields, filters, and date range you need.
Scroll to the Data Destinations section and select BigQuery.
Click Add Destination Task and fill in a task name, your authentication method (Google account/OAuth, or a service account file, as prompted on screen), your BigQuery Project ID and Dataset ID, and a table name (Windsor.ai creates the table for you if it doesn’t already exist). Set a refresh schedule and, optionally, backfilling, partitioning, or clustering.
Click Test connection, then Save. Your data will start streaming in on the schedule you set, and you can come back and change these settings at any time.

See the full BigQuery integration guide for more detail on authentication, partitioning, and clustering options.

Bonus

Do you want to fetch data from the connectors directly? You can try get additional fields which are not in the standard table definitions (such as custom dimensions, metrics and additional media metrics). You can start building your connector queries here.

Did you like this article or do you have any questions? We would love to have your feedback. Just contact us on the chat!

You might also be interested in…

Connect Google Search Console (GSC) to BigQuery

Connect AppsFlyer to BigQuery

Connect Snapchat Ads to BigQuery

Connect Pinterest Ads to BigQuery

Connect Klaviyo to BigQuery

Connect GA4 to BigQuery

How to Get GA4 Data into BigQuery: 4 Methods

Move Data from Google Sheets to BigQuery