How to Connect Salesforce to BigQuery?

Piotr Malek

2 years ago

Salesforce is a popular CRM platform that allows you to collect and manage your sales, marketing, and customer service data effectively. Although it’s great for storing customer details and streamlining workflows, Salesforce doesn’t allow you to analyze your data effectively. So you have to seek an external platform with advanced capabilities for business intelligence.

A good solution is BigQuery, a serverless data warehouse that offers extensive data tools such as machine learning and geospatial analysis to help understand your data and inform decisions. But how can you connect Salesforce to Google BigQuery? This is where the need for data integration comes in. You can use a manual method or automated ones – either with code or utilizing a no-code solution to export data from Salesforce and import into BigQuery.

In this article, we’ll provide step-by-step instructions for using these connection methods to move data from Salesforce to BigQuery.

Methods for connecting Salesforce to BigQuery

There are a couple of methods that you can use to connect Salesforce to Google BigQuery. Let’s have a look at a brief overview of them below. We’ll describe the workflow in subsequent chapters.

Automation tools

You can automate the integration process with the help of data automation platforms such as Coupler.io. They can allow you to set up the connection once, add your preferred schedule, and automatically import marketing, sales, and customer service data from Salesforce to BigQuery.

Salesforce CSV export

There are two ways of exporting your Salesforce data as CSV – Data Export and Dataloader.io. Data Export is the native functionality in Salesforce for downloading and scheduling your data once every month. Dataloader.io allows for much more frequent exports from Salesforce and you can automate to some extent, but you’ll still need to load these files manually to BigQuery.

API integration

Salesforce offers APIs that allow you to extract data from your account and load into BigQuery. The requirements include: an API access token, an instance URL, custom SQL query. You also need a scripting tool such as Google Apps Script to create a script for integrating your data.

Now, we’ll proceed to show the steps or describe how to use each method in the next chapters.

Automate Salesforce data to Google BigQuery

Data automation tools make it easy for anyone to integrate data from one platform to another. They don’t require manual work aside from an initial setup and can significantly speed up your reporting. Let’s have a look at one of the most popular and user-friendly ways to connect Salesforce to BigQuery.

Coupler.io is an all-in-one data analytics and automation platform that you can use to extract, transform, and load your data from a source application to a data destination. It allows you to easily pull data from Salesforce and many other sources, including ecommerce, marketing platforms, CRMs, accounting apps, social media apps, etc. Then, you can automate imports to spreadsheets, data warehouses, or BI platforms.

To get started, sign up for a Coupler.io free trial – no credit card required. Let’s walk through the process of setting up an importer, adding your Salesforce and BigQuery accounts, and using a custom schedule to automate data imports with just a few clicks on your computer screen.

First, log in to your Coupler.io account and click Add importer to begin.

Enter your source app and destination app. We’ll use Salesforce and BigQuery. Click Proceed.

Next, you’ll connect your Salesforce account. And choose the data type for the export.

You can use Data Entity to choose a predefined data type such as Accounts, Contacts, or Lead. There’s also a Custom SOQL option if you’d like to make a custom request and export different types of data at once. The Basic Settings section is where you’ll select a data category to export. The Advanced Settings are optional but you can choose to edit them if you want to.

You can add more sources if you’d like. Otherwise, jump to the Transform module.

Here the platform allows you to transform your data into something easier to process at your destination.

You can choose to add filters, sort, use calculation formulas, or manage columns just like in a spreadsheet. When you’re ready, click Proceed to continue.

Next, you’ll connect your BigQuery account.

When you click Add Account, a popup will appear. You need to carefully follow the instructions in the wizard in order to connect your BigQuery account successfully. Then, you’ll continue to enter a dataset and table name.

Finally, enable Automatic Data Refresh to add a custom data refresh schedule. This will keep your data up-to-date without any effort on your end.

If successful, you should see this notification. Click View Results to see your data in BigQuery.

Voila. You can now find your data already populated in the exact BigQuery dataset and table.

Move data from Salesforce to BigQuery manually

The manual method involves using either Data Export or Dataloader to export your Salesforce data as CSV and then, you’ll proceed to upload the file to Google BigQuery.

Data export

The native data export functionality allows you to export data once per month. To get started, login to your Salesforce account and click on the Setup icon to load the sidebar for setting up.

On the sidebar, scroll to Administration and click the dropdown next to Data.

Select Data Export from the list. This will load the Monthly Export Service page.

You can choose to export your data now or schedule it to run automatically.

When you click Export Now, you’ll see the page below.

You can include all data or select data types according to your requirements. Click Start Export.

You’ll receive an email when your file is available for download.

The link contained in the email will redirect you to the Data Export page where you can find the ZIP archive containing your CSV file.

To set up a schedule for automatic exports, click Schedule Export.

Here, you’ll choose what data to export as well as the preferred frequency for these exports. This way, you won’t have to start over every time. You’d simply receive an email notification and click the link inside to download your file. However, you should note that it’s still once per month.

Dataloader.io

Dataloader.io is an application for importing and exporting data from Salesforce. You can use it to export CSV files from Salesforce as often as you’d like to. It also supports destinations such as, Dropbox, and an FTP server. The free plan is enough to get started, but it only allows a maximum of 10,000 rows. Let’s see how to configure the connection using Dataloader.io.

Navigate to dataloader.io and click Login with Salesforce.

Click New Task and select the Export option.

Choose your connection and object entity. Then, click Next to proceed.

Next, you’ll select the fields of the object you chose, include filters and specify the order for export. This will generate an SOQL query to help pull your salesforce data.

You may also create a custom SOQL query and paste it into the textbox but note that you won’t be able to use the dropdowns to select new fields and parameters any longer. You’d have to click Reset to discard your changes and re-enable the query generator. Click Next to proceed.

You can run the export task once or choose a daily, weekly, or monthly schedule.

The Advanced section allows you to add other parameters, such as date format, timeout, etc. While Add Destination Folder allows you to connect your destination account.

Click Save and Run and wait for your data export task to be completed. If successful, you should see something like this.

Click on the Task Run link to download your CSV file. A copy will also be sent to the destination.

Import CSV to BigQuery

After downloading your data using Data Export or Dataloader.io, you need to upload your file to BigQuery. Let’s go through the steps for creating a dataset and table in Google BigQuery.

Next, click the Add button. You should see the right sidebar showing this.

In the next window, you’ll upload the CSV file containing your Salesforce data.

You can also create a table from sources such as GCS, Google Drive, Amazon S3, etc. To configure, choose the dataset and table. When you’re ready, click Create Table to continue.

Now, you should be able to see your data in the newly created table.

Salesforce to Google BigQuery via API integration

If you have a Salesforce edition such as Enterprise, Unlimited, Developer, and Performance, you can use APIs to pull data from your account. To do so, you’ll need to get an access token which is required to call the Salesforce API.

Use the search bar to find App Manager and click on it.

Next, you’ll click on the New Connected App button to create a new app in Salesforce.

You’ll enter the basic information for the connected app, such as name and email.

Next, click the checkboxes for both Enable OAuth Settings and Enable for Device Flow.

For the Selected OAuth Scopes section, you need to select Manage user data via APIs from the list of Available OAuth Scopes and add it to the Selected OAuth Scopes box.
You can leave other fields since they are not necessary. When you’re done, scroll down to the bottom of the page, click Save and then Continue.

You’ll be taken to the Manage Connected Apps page.

Next, you need to create an API access token and get your instance URL.

The Initial Access Token for Dynamic Client Registration section allows you to generate and copy an access token. As for the instance URL, simply copy your unique link in the search bar. It should look like this – https://foo-bar.lightning.force.com. To form a JSON URL for pulling your data, use this format – {instance-url}/services/data/{api-version}/query/{sql-query}

An example SQL query for sending contact data is: ?q=SELECT+id,name,email+from+Contact. When your JSON URL has been assembled, you can create a script for the integration using Apps Script if you have enough coding knowledge to do so. Alternatively, you can use a no-code platform such as Coupler.io to extract data as JSON and then import it into your BigQuery table.

Benefits of automating Salesforce to Google BigQuery

Although you can use manual integration methods to send data from Salesforce to BigQuery, it can quickly become a repetitive and time-consuming process. Let’s discuss some benefits of using automation platforms for integrating your Salesforce CRM data with BigQuery.

Faster backup

BigQuery is a data warehousing solution that can accommodate all your data needs. But it’s not very convenient to connect and send data from Salesforce to BigQuery every time. This is even worse when you collect lots of customer information and interaction data on a daily basis. With automation, you no longer have to spend a lot of time on manual integration for your CRM data.

Easier reporting

The large amounts of data being generated from sales, marketing, and customer service need to be analyzed frequently to help stakeholders determine the best decisions to make. As you may already know, Salesforce doesn’t offer enough analytical capabilities, so you need to rely on external platforms. Automation makes it easier to begin your reporting process without having to worry about how to move your data from Salesforce to a preferred platform like BigQuery.

Seamless Integration

When dealing with complex and rapidly increasing data, you may realize that more hands and platforms are needed to keep everything organized. An automation platform such as Coupler.io allows for multiple users and teams who need to collaborate on near real-time data. It allows you to automatically send data to any platform of choice like spreadsheets or BI tools.

Improve analytics with Salesforce to BigQuery Integration

One primary reason for integrating Salesforce to BigQuery is to use advanced analytics to uncover valuable insights from your data. If you, however, have extracted tons of useful data from Salesforce but struggle to make use of it, Coupler.io’s data consultancy services may come in handy.

Through them, our data experts build custom solutions of any sort, be it dashboards in your preferred BI tool, automations, entire data pipelines, and more. If this is something you could find useful, then feel free to schedule a free consultation with us and let’s discuss your project!

Thanks for reading.