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:
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.
Configure Your Source Settings
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.
Configure Destination Settings
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.
Load Data from Salesforce Marketing Cloud to BigQuery
Load Data from Salesforce Marketing Cloud to Snowflake
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.
Configure Destination Settings
- In the Navigation bar, click on Storage>Browser.
Creating a Bucket
- Click on the option Create Bucket. This will create a bucket to hold your exported CSV data files.
Naming a Bucket
- Enter a unique name for your Google bucket and click CREATE in the Name Your Bucket section.
Bucket Created
- You can Upload or Drag-Drop your CSV data files in the drop zone.
Bucket Created
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.
With a strong background in market research for data science and cybersecurity products, Saloni is an expert at crafting informative articles on key topics within the data science domain, such as data transformation, processes, and analysis. Saloni's passion for the field drives her to continually learn and stay abreast of emerging technologies and trends, ensuring her contributions are impactful. Her work aims to enrich the discourse in data science, providing valuable insights and fostering a deeper understanding of complex subjects.