How to Migrate Data from Salesforce to BigQuery?

Bringing your key sales, marketing, and customer data from Salesforce to BigQuery is the right step towards building a robust analytics infrastructure. By merging this information with more data points available from various other data sources your business uses, you can extract deep, actionable insights that grow your business. 

In this blog, I will give you a detailed guide on connecting the two platforms for better reporting. We will also see the use cases and benefits of setting up this connection. Before we jump into the details, let us briefly understand each of these systems.

What is Salesforce?

Salesforce is one of the world’s most renowned customer relationship management platforms. Salesforce has many features that allow you to manage your key accounts and sales pipelines. While Salesforce does provide analytics within the software, many businesses would want to extract this data, and combine it with data from other sources such as marketing, product, and more to get deeper insights on the customer. This can be achieved by bringing the CRM data into a modern data warehouse like BigQuery. You can also connect your Salesforce account to Power BI to directly visualize your customer data.

Key Features of Salesforce

Salesforce is one of the most popular CRM in the current business scenario and it is due to its various features. Some of these key features are:

• Ease of Use: Businesses usually spend more time putting it to use and comparatively less time understanding how Salesforce works.
• Effective: Salesforce is convenient to use and can also be customized by businesses to meet their requirements. Due to this feature, users find the tool very beneficial.
• Account Planning: Salesforce provides you with enough data about each Lead so that your sales team can customize their approach for every potential Lead. This will increase their chance of success, and the customer will also get a personalized experience.
• ETL Capabilities: Salesforce connects seamlessly with other tools, which can be performed easily with the help of Salesforce ETL tools.
• Accessibility: Salesforce is a Cloud-based software, hence, it is accessible from any remote location if you have an internet connection. Moreover, Salesforce has an application for mobile phones, making it super convenient.

What is Google BigQuery?

BigQuery comes with a pay-as-you-go pricing model and allows users to pay only for the queries run. is also very cost-effective as you only pay for the queries you run. Together, these features make BigQuery a very sought-after data warehouse platform.  BigQuery provides an outstanding query performance owing to its column-based storage system.

Key Features:

1. Serverless Architecture: BigQuery manages servers and storage in the background, so a user does not need to.
2. High Scalability: It scales seamlessly to handle petabytes of data.
3. SQL Compatibility: It supports ANSI SQL, which is helpful for people who already know SQL and want to write and run queries. This also allows a user to combine various BI tools for data visualization.
4. Machine Learning: BigQuery ML allows users to train and run machine learning models in BigQuery using only SQL syntax.

 You can read more about the key features of BigQuery here.

This blog covers two methods of loading data from Salesforce to Google BigQuery. The article also sheds light on the advantages/disadvantages of both approaches. This would give you enough pointers to evaluate them based on your use case and choose the right direction.

What are the Methods to Connect Salesforce to BigQuery?

Method 1: Simplest Way to Move Salesforce Data to BigQuery using Hevo

Step 1: Configure Salesforce as Source

Learn more about configuring Salesforce from our documentation page.

Step 2: Configure BigQuery as your destination.

Learn more about configuring BigQuery as a destination.

Method 2: Using Custom Scripts

Step 1: Set Up Salesforce API Access

The first step would be to decide what data you need to extract from the API and Salesforce has an abundance of APIs.

• Salesforce REST APIs
• Salesforce Bulk APIs
• Salesforce SOAP APIs
• Salesforce Streaming APIs

You may want to use Salesforce’s streaming API so your data is always current.

Step 1.1: Enable API Access: Ensure your Salesforce account has API access. This requires a Professional or higher edition.

Step 1.2: Create Connected App:

• Go to Salesforce Setup > Apps > App Manager.
• Click New Connected App, configure the app, and enable OAuth settings.
• Obtain the Consumer Key and Consumer Secret.

Step 1.3: Generate Access Token: Use tools like Postman or scripts to authenticate and get an access token for API requests.

Step 2: Install Required Libraries

Use Python or any other scripting language to reinstall libraries. In this tutorial, I have used Python. 

pip install simple-salesforce google-cloud-bigquery pandas

Step 3: Extract Data from Salesforce

Utilize the simple-salesforce library for extraction. 

from simple_salesforce import Salesforce

# Connect to Salesforce

sf = Salesforce(username='your_username', password='your_password', security_token='your_token')

# Query Salesforce Data

query = "SELECT Id, Name, Email FROM Contact"

sf_data = sf.query_all(query)

records = sf_data['records']

Step 4: Convert to a data frame

import pandas as pd

df = pd.DataFrame(records).drop(columns='attributes')

Step 5: Transform your data

Once you have extracted the data using the above approach, you will need to transform your data because: 

• BigQuery supports loading data in CSV and JSON formats. If the API you use returns data in formats other than these (e.g., XML), you must transform them before loading. 
• You also need to make sure BigQuery supports your data types. Use this link, BigQuery data types, to learn more about BigQuery data types.

Perform any necessary transformations (e.g., renaming columns, cleaning data, or adjusting formats):

df.rename(columns={'Name': 'full_name'}, inplace=True)

Step 6: Setup BigQuery

Enable the BigQuery API, create a service account, and download the JSON key. 

• Now you can load data to BigQuery using any desired method: 
• Upload the prepared data to Google Cloud Storage.
• Load to BigQuery from your GCS bucket using BigQuery’s command-line tool or any cloud SDK.

Limitations in writing Custom Code

• When writing and managing API scripts you need to have the resources for coding, code reviews, test deployments, and documentation.
• Depending on the use cases developed by your organization, you may need to amend API scripts; change the schema in your data warehouse; and make sure data types in source and destination match.
• You will also need to have a system for data validation. This would help you be at peace that data is being moved reliably.
• Each of these steps can make a substantial investment of time and resources. In today’s work setting, there are very few places with ‘standby’ resources that can take up the slack when major projects need more attention.

In addition to the above, you would also need to:

1. Watch out for Salesforce API for changes
2. Monitor GCS/BigQuery for changes and outages
3. Retain skilled people to rewrite or update the code as needed

If all of this seems like a crushing workload you could look at alternatives like Hevo. Hevo frees you from inspecting data flows, examining data quality, and rewriting data-streaming APIs. Hevo gives you analysis-ready data so you can spend your time getting real-time business insights.

Method 3: Using CSV/Avro 

This method involves using CSV/Avro files to export data from Salesforce into BigQuery. The steps are:

• Inside your Salesforce data explorer panel, select the table that you want to export your data. 
• Click on ‘Export to Cloud Storage’ and select CSV as the file type. Then, select the compression type as GZIP (GNU Zip) or go ahead with the default value.  
• Download that file to the system.
• Login to your BigQuery account. In the Data Explorer section, select “import” and choose “Batch Ingestion”.  
• Choose the file type as CSV/Avro. You can enable schema auto-detection or specify it specifically. 
• Add dataset and table name, and select “Import”. 

The limitation of the method is that it becomes complex if you have multiple tables/files to import. Same goes for more than one data sources with constantly varying data.

What are the Use cases for Migrating Salesforce Data to BigQuery?

Organizations use Salesforce’s Data Cloud along with Google’s BigQuery and Vertex AI to enhance their customer experiences and tailor interactions with them.  Salesforce BigQuery Integration enables organizations to combine and analyze data from their Salesforce CRM system with the powerful data processing capabilities of BigQuery. Let’s understand some real-time use cases:

• Retail: Retail businesses can integrate CRM data with non-CRM data, such as real-time online activity and social media sentiment in BigQuery to help you understand the complete customer journey and subsequently when you implement customized AI models to forecast customer tendency. The outcome involves delivering highly personalized customer recommendations through optimal channels like email, mobile apps, or social media.
• Healthcare Organizations: CRM data, including appointment history and patient feedback, can be integrated with non-CRM data, such as patient demographics and medical history, in BigQuery. The outcome is the prediction of patients susceptible to readmission, allowing for creating personalized care plans. This proactive approach enhances medical outcomes through preemptive medical care.
• Financial institutions: Financial institutions have the capability to integrate CRM data encompassing a customer’s transaction history, credit score, and financial goals with non-CRM data such as market analysis and economic trends. By utilizing BigQuery, these institutions can forecast customers’ spending patterns, investment preferences, and financial goals. This valuable insight informs the provision of personalized banking services and offers tailored to individual customer needs.

Read More About: Salesforce Marketing Cloud to Bigquery

Conclusion

The blog talks about the two methods you can use to move data from Salesforce to BigQuery in a seamless fashion. The idea of custom coding with its implicit control over the entire data-transfer process is always attractive. However, it is also a huge resource load for any organization.

A practical alternative is Hevo – a fault-tolerant, reliable Data Integration Platform. Hevo gives you an environment free from any hassles, where you can securely move data from any source to any destination.

Sign up for a 14-day free trial and experience the feature-rich Hevo suite firsthand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Frequently Asked Questions