How to Drive Insights by Integrating Salesforce Marketing Cloud to BigQuery?

Easily move your data from Salesforce Marketing Cloud To BigQuery to enhance your analytics capabilities. With Hevo’s intuitive pipeline setup, data flows in real-time—check out our 1-minute demo below to see the seamless integration in action!

To succeed in the digital world, you must respond to your customer’s needs more quickly than ever. Salesforce Marketing Cloud is a platform that automates marketing campaigns across different channels and helps you personalize your customer experience. 

However, it is difficult to query large and complex data sets in the Salesforce marketing cloud. This is where BigQuery comes in. It supports SQL-based querying, which enables you to analyze customer data more efficiently. Integrating data from the Salesforce Marketing Cloud to BigQuery allows you to leverage BigQuery’s built-in features like machine learning and business intelligence to generate custom reports. 

In this article, you will explore how to export Salesforce Marketing Cloud data to BigQuery using different methods. 

Let’s get started. 

What Is Salesforce Marketing Cloud?

Salesforce Marketing Cloud (SFMC) is a digital marketing platform designed to help businesses manage and optimize their marketing efforts across multiple channels. It offers various tools for creating personalized customer experiences, automating marketing campaigns, and analyzing their performance.

Key Features Of SFMC

• Cross-Channel Campaigns: Manage customer journeys across email, mobile, social media, and ads with tools like Journey Builder, Email Studio, Mobile Studio, and Advertising Studio.
• Personalization: Use real-time data and AI-powered insights to deliver personalized content and experiences with Personalization Builder.
• Social Media Management: Engage, publish, and analyze social content through Social Studio.
• Automation & Analytics: Automate marketing tasks and use AI-driven analytics (Einstein) to optimize campaigns and predict customer behavior.
• Data Integration: Leverage Salesforce’s Data Management capabilities to unify customer data across platforms for better targeting and segmentation.

What Is Google BigQuery?

Google BigQuery is a fully managed, serverless data warehouse designed for large-scale data analytics. It enables businesses to store, query, and analyze massive datasets in real-time, using SQL-like syntax. It’s highly scalable and integrates seamlessly with other Google Cloud services, making it ideal for handling big data workloads efficiently.

Key Features Of Google BigQuery

• Serverless Architecture: BigQuery eliminates the need for infrastructure management, allowing you to focus on querying and analyzing data without worrying about scaling or maintenance.
• High-Speed Analytics: It supports real-time data analysis, offering fast query processing for large datasets using distributed computing.
• SQL Queries: Users can run complex SQL queries on petabyte-scale data with ease, making it accessible for those familiar with standard SQL.
• Machine Learning Integration: BigQuery ML allows users to build and train machine learning models directly within the platform using SQL queries.
• Seamless Data Integration: Easily integrates with other Google Cloud services (e.g., Google Analytics, Google Sheets) and third-party tools for data import and export.

Why Integrate Salesforce Marketing Cloud to Google BigQuery? 

Salesforce Marketing Cloud and Google BigQuery integration have several advantages. Let’s look at some of them: 

• BigQuery has a serverless infrastructure, which means you can access and analyze your customer data without setting up or managing the infrastructure. Its columnar storage allows you to optimize resources and analyze complex queries efficiently. 
• Salesforce Marketing Cloud and Google BigQuery integration create a centralized repository for your customer data, which can be replicated across multiple sources, providing high accessibility.
• Before writing your data on the disk, BigQuery automatically encrypts it, providing security for your critical data. 
• With BigQuery’s real-time data streamlining capabilities, you can analyze customer data in near real-time, which can be beneficial for observing campaign performance.

Methods of Migrating Data from Salesforce Marketing Cloud to BigQuery

You can sync your Salesforce Marketing Cloud data to BigQuery using two methods: 

Method 1: Implementing Salesforce Marketing Cloud and Google BigQuery integration through Hevo

Method 2: Migrating data from the Salesforce Marketing Cloud to BigQuery using CSV files

Method 1:  Implementing Salesforce Marketing Cloud and Google BigQuery Integration Using Hevo

Hevo is a real-time ELT platform that seamlessly sends your Salesforce Marketing Cloud data to BigQuery through its no-code, flexible, and automated data pipeline. It offers 150+ data sources from where you can integrate your data into the desired destination. Hevo’s data pipeline transforms your data, enriches it, and makes it ready for analysis. 

Benefits of Using Hevo

• Data Transformation: Hevo offers various data transformation methods, including Python-based and drag-drop transformations. These methods allow you to clean your data before ingesting it into the targeted system. 
• Incremental Data Loading: Incremental data loading allows you to load only the updated or modified data after the first round of ingestion instead of copying the datasets again. 
• Automated Schema Mapping: Hevo’s automated schema mapping automatically reads and replicates the Salesforce Marketing Cloud data’s schema into BigQuery. 

Let’s see how to sync your Salesforce Marketing Cloud data to BigQuery with Hevo. 

Step 1: Configure Salesforce Marketing Cloud as Your Source

Before you configure Salesforce Marketing Cloud as your source for Salesforce Marketing Cloud and Google BigQuery integration, you must ensure the following prerequisites are met.

Prerequisites:

• You must have an active Salesforce Marketing Cloud instance from which the data will be integrated into BigQuery.
• Create Client ID and Secret inside the Salesforce marketing cloud.
• Locate the subdomain.
• You must have a Pipeline Administrator, Team Administrator, or Team Collaborator role to create the Hevo pipeline.

Refer to the Hevo documentation for more information on configuring the Salesforce Marketing Cloud as a source.  

Step 2: Configuration of Google BigQuery as Destination

Prerequisites:

• Create a Google Cloud Project if you do not have one already. 
• Assign the essential roles for the GCP project to the connecting Google account in addition to the Owner or Admin role. 
• Ensure that the active billing account is associated with the GCP project. 
• To create a destination, you are assigned the role of Team Collaborator or any other administrative role except Billing Administrator. 

Refer to the Hevo documentation for more information on configuring Google BigQuery as a destination in Hevo. 

You can easily export Salesforce Marketing Cloud data to BigQuery with these steps. 

Method 2: Migrating Data from the Salesforce Marketing Cloud to BigQuery Using CSV Files

Step 1: Export Data from Salesforce Marketing Cloud to CSV File Format

Prerequisites:

• You should have an active Salesforce Marketing Cloud instance. 
• You need to note which fields are exportable while exporting a list from the marketing list. 
• When exporting the file from the marketing cloud, you must choose whether to receive it via an FTP account or email.

Follow the steps to export a list from the marketing cloud in CSV file format:

• Go to the Subscribers option on your marketing cloud dashboard and click on Lists. 

• Click on Export under Actions next to the list you want to export. 
• Click on Next in the wizard dialog box and specify the mandatory details in the File and Delivery dialog box. 
• Click on Next and choose the data you want to export by moving the attributes from the box on the left to the box on the right. 
• Click on Export and begin the exporting process; once finished, click on Finish. 

NOTE: A dialog box will appear after the exporting process is completed. If you choose an email option, you will receive an email in a CSV file. If you choose the HTTPS browser option, click Download File, open your file in a browser, and click Finish after downloading the file.

Step 2: Load Data from the CSV File to Google Cloud Storage 

You can upload the CSV file containing data extracted from the Salesforce Marketing Cloud to Google Cloud Storage using the following steps: 

• Login to your Google Cloud account and click on Go to Console.

• In the Navigation bar, click on Storage>Browser. 

• Click on the option Create Bucket. This will create a bucket to hold your exported CSV data files. 

• Enter a unique name for your Google bucket and click CREATE in the Name Your Bucket section. 

• You can Upload or Drag-Drop your CSV data files in the drop zone. 

 

Step 3: Upload your CSV File from the Google Cloud Storage to Google BigQuery

Prerequisites

• You should grant IAM permissions to the user to create a dataset and perform operations on it. 
• Create a Dataset and Table in BigQuery where you will load the CSV data.

You can upload your CSV file from Google Cloud Storage to the BigQuery table by using the Python code:

From google.cloud import bigquery
# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table to create.
# table_id = "your-project.your_dataset.your_table_name"

job_config = bigquery.LoadJobConfig(
    schema=[
        bigquery.SchemaField("name", "STRING"),
        bigquery.SchemaField("post_abbr", "STRING"),
    ],
    skip_leading_rows=1,
    # The source format defaults to CSV, so the line below is optional.
    source_format=bigquery.SourceFormat.CSV,
)
uri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv"

load_job = client.load_table_from_uri(
    uri, table_id, job_config=job_config
)  # Make an API request.

load_job.result()  # Waits for the job to complete.

destination_table = client.get_table(table_id)  # Make an API request.
print("Loaded {} rows.".format(destination_table.num_rows))

Thus, you can sync your Salesforce Marketing Cloud data to BigQuery using these steps.

Limitations of Migrating Data from the Salesforce Marketing Cloud to BigQuery Using CSV Files 

• In the Salesforce management cloud instance, you can only choose up to 150 data fields per export file. Also, in Google Cloud Storage, you must meet the size limit for a CSV file to be uploaded, as described in the load job limit.
• You can’t directly load the CSV file into a BigQuery table, which makes the integration process more complex and time-consuming. 
• As the CSV file doesn’t support nested or repeated data structures, you have to handle them using different methods, such as flattening or arrays. Defining the schema in the correct format requires technical expertise. This may pose a challenge to users who don’t have significant knowledge and may still lead to potential errors.

Use Cases of Salesforce Marketing Cloud to BigQuery

• With BigQuery’s analytics capabilities, you can profoundly study customers’ profiles and interactions on various communication channels. It provides a unified view of customer engagement, which enables you to optimize your marketing strategies to reach potential users.
• You can apply BigQuery’s machine learning capabilities to Salesforce Marketing Cloud data to build ML models. These models help you predict customer behavior, lead scoring, and anticipate future trends.

Conclusion 

• Integrating Salesforce Marketing Cloud data with BigQuery enhances marketing strategies and customer engagement.
• CSV files can cause schema mapping and scalability issues during manual data integration.
• Hevo offers a no-code, scalable platform that automates data integration using flexible pipelines.
• Hevo simplifies the process, ensuring real-time data flow between Salesforce Marketing Cloud and BigQuery.
• This integration drives more impactful marketing efforts and better business outcomes.

Explore Hevo’s 14-day free trial and experience the feature-rich Hevo suite firsthand. Check out the pricing details to learn which plan would work best for your organization.

FAQs