Salesforce Marketing Cloud to BigQuery: Driving Significant Insights for Marketing Success

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. 

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.

Overview of Salesforce Marketing Cloud

Salesforce Marketing Cloud(SFMC) is a digital platform that automates marketing activities across various channels, such as email, social media, SMS, apps, and more. SFMC is a Salesforce product that is not built on the core Salesforce platform, but you can use connectors to sync the data between the two.  

It combines all marketing channels and lets you send personalized customer messages according to their preferences and buying activities. SFMC includes analytics, Google plug-ins, CDP, and more that help you manage content and campaigns to perform distributed marketing. It manages and analyses all the touchpoints of the customer lifecycle, uncovering potential leads and opportunities for your business growth.

Overview of BigQuery

BigQuery is a serverless, fully managed data warehouse platform developed by Google and hosted on Google Cloud. It is designed to be scalable and can hold petabytes of data without intervening manually. BigQuery’s serverless architecture allows users to focus on their data for analysis and querying. It supports real-time data analysis and integrates seamlessly with other Google Cloud services such as Google Data Studio, Dataflow, Looker, and more. 

BigQuery also provides robust security features through encryption and fine-grained access controls, ensuring compliance, confidentiality, and data integrity. Its pay-as-you-go pricing allows you to optimize storage costs and manage data efficiently.

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 Clouddata’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.

Follow the steps given below to configure the Salesforce Marketing Cloud as a source:

• In the Navigation Bar, Click PIPELINES.
• Click + CREATE in the Pipelines List View.
• Select Salesforce Marketing Cloud from the Select Source Type page.
• Enter the mandatory details on the Configure your Salesforce Marketing Cloud Source settings page. 
• Click the Test & Continue button to complete the source setup.

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. 

Here are the steps to configure BigQuery as a destination: 

• Click DESTINATIONS in the Navigation Bar.
• In the Destinations List View, click + CREATE.
• On the Add Destination page, select Google BigQuery as the destination type.
• On the Configure your Google BigQuery Destination page, specify all the details. 

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  for 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 data from the Salesforce Marketing Cloud to BigQuery gives you many advantages, such as enhancing marketing strategies, building better customer engagement, and more. You can integrate your data between the two platforms using CSV files or Hevo. 

Using a CSV file for data integration can pose schema mapping and scalability challenges. On the other hand, Hevo is a scalable data integration platform that automates your process using its no-code, flexible data pipelines. Integrating the Salesforce Marketing Cloud and BigQuery can drive more impactful engagement and achieve better business results.

FAQs 

Q. Is there a code-free solution to migrate data between the Salesforce Marketing Cloud and BigQuery? 

Some third-party ETL/ELT tools, such as Hevo, enable you to migrate your data between the Salesforce Marketing Cloud and BigQuery through their no-code data pipelines.