Salesforce Marketing Cloud to Azure Data Integration: 3 Best Ways

Salesforce’s marketing cloud (SFMC) scored 23.9% of the market share in 2022 CRM share. It’s more than the score of its rivals SAP, Oracle, Microsoft, and Adobe combined. This report tells us how much the data from SFMC helps businesses. Now comes the nitty-gritty of technical aspects such as data integration. Let’s take Salesforce marketing cloud to Azure integration as an example. How are you going to replicate from SFMC to Azure?

In this blog, I will walk you through the three ways to replicate data from the Salesforce marketing cloud to Azure. In the end, you will also learn about the benefits of data replication between these two platforms.

Let’s get started!

How to Replicate Data from Salesforce Marketing Cloud to Azure

Ready to dive deep into the first method?

Using CSV

Step 1: Extract Marketing Cloud Data to CSV

• To import an XML file into your account, it must be converted into a comma-, tab-, or pipe-delimited file using Automation Studio’s Data Extract Activity.
• Click on Create Activity.
• Select Data Extract.

• Give a name, an external key, and a description of the activity.
• Add and follow a naming pattern. This is done to let the activity know what name is given for the files. The following are the personalization strings to be used in your file name:  %%Year%% %%Month%% %%Day%% %%Hour%% %%Minute%% %%Second%%
• Use it when you’ve set up a procedure to place a file into a location. This is done after its automatically produced filename meets a pattern.
• Include placeholders or a static name for the date. Always be aware that they are case-sensitive.
• Include a tracking extract with the .zip extension and a data extraction with the .csv extension.
• To find out which web analytics tools were used to analyze the extracted file, select an extract type. What information you can include in the file will depend on this.

• Choose an extract range to fix the range of dates you wish to include in the information’s extract file. This time frame may be one day, seven days, or thirty days.
• Choose the type of file encoding to use.
• Pick the fields from the data extract file that you want to use.
• Choose the name of the field you want to include.
• Insert the desired delimiter character from “,,” “tab,” or “|” if the data extract has a column delimiter field.

Now, you can view the summary of your data extract as shown below.

Step 2: Integrate Data from CSV File into Microsoft Azure

Using the bcp command-line tool, you can import data from a CSV file into an Azure SQL Database or an Azure SQL Managed Instance.

You must encode your data in ASCII or UTF-16 since bcp does not support UTF-8. 

• Create a destination table: Select a table in the SQL Database to serve as the destination table. Each row of your data file’s data must correspond to a column in the table. To create a table, open a command prompt and type the following command using sqlcmd.exe:

sqlcmd.exe -S <server name> -d <database name> -U <username> -P <password> -I -Q "
    CREATE TABLE DimDate2
    (
        DateId INT NOT NULL,
        CalendarQuarter TINYINT NOT NULL,
        FiscalQuarter TINYINT NOT NULL
    )
    ;

• Generate a source data file: You have to copy and paste the data below into a brand-new text file in Notepad. After that, save the file to C:TempDimDate2.txt in your local temporary directory. The format of this data is ASCII. 

20150301,1,3
20150501,2,4
20151001,4,2
20150201,1,3
20151201,4,2
20150801,3,1
20150601,2,4
20151101,4,2
20150401,2,4
20150701,3,1
20150901,3,1
20150101,1,3

Use the following command to export your own data from a SQL Server database after opening a command prompt. Replace tablename, servername, databasename, username, and password with your own details.

bcp <TableName> out C:\Temp\DimDate2_export.txt -S <ServerName> -d <DatabaseName> -U <Username> -P <Password> -q -c -t ","

Load the data: To load the data, open a command prompt and type the following command, replacing the values for the server name, database name, username, and password with your own information.

bcp DimDate2 in C:\Temp\DimDate2.txt -S <ServerName> -d <DatabaseName> -U <Username> -P <password> -q -c -t ","

You need to use this command to verify Google Analytics loaded the data correctly.

sqlcmd.exe -S <server name> -d <database name> -U <username> -P <password> -I -Q "SELECT * FROM DimDate2 ORDER BY 1;"

The result looks like the table below:

DateId	CalendarQuarter	FiscalQuarter
20150101	1	3
20150201	1	3
20150301	1	3
20150401	2	4
20150501	2	4
20150601	2	4
20150701	3	1
20150801	3	1
20150801	3	1
20151001	4	2
20151101	4	2
20151201	4	2

You have seen the process of data integration from the Salesforce marketing cloud to Azure. In fact, CSV is a very useful way for Salesforce Marketing Cloud to Azure migration. Let’s have a look at some use cases now.

• For one-time use cases: This is the best method if your business team requires data from the Salesforce marketing cloud on a one-time basis. Why spend your money on one-time needs?
• For tasks where data transformation is not needed: The spreadsheets would be enough in circumstances when you don’t need to conduct elaborate analysis. Just arrange it properly and deliver it to the audience.
• While handling small files: Downloading multiple CSV files and writing SQL queries both take time. It will be more time-consuming if you have to merge spreadsheets from several teams in the organization to develop a 360-degree perspective of the business. So, only use them while working with small files.

You can see that this method wouldn’t be a good fit for other large-scale requirements, right? Next, let me introduce you to another way of data integration from the Salesforce marketing cloud to Azure. 

Data Replication from Salesforce Marketing Cloud using Azure Data Factory or Synapse Analytics

In this section, let’s learn how to replicate data from Salesforce marketing cloud to Azure using the Copy Activity in Azure Data Factory or Synapse Analytics pipelines.

Let’s start step by step to build a linked service in the Azure portal UI for Salesforce Marketing Cloud.

• Select Linked Services from the Manage tab in your Azure Data Factory or Synapse workspace, then click New:
• Search for Salesforce and find the Salesforce Marketing Cloud connector.

• Create the new linked service after testing the connection, and configuring the service’s specifics.
  

The problem is that it consumes a lot of bandwidth from your engineering team to build and operate a data pipeline. Do you really want to invest in that when there are automated data pipelines available? Let’s come to that in the next section.

Using a Fully Automated Data Pipeline

Let’s get into the details of how a fully automated data pipeline helps you connect Salesforce marketing cloud to Azure.

• Replicable patterns: By visualizing SFMC data processing as a network of pipelines, you can reuse and repurpose certain pipes for different data flows since you can see them as illustrations of patterns in a wider architecture.
• Quick integration of new data sources is possible: Planning for the intake of new data sources while using SFMC is easier. When there is a shared idea and set of tools for how data should flow via analytics platforms, integrating them is quicker and less expensive.
• Ensures more quality data: Viewing your data flows may improve data accuracy, improve the quality of data from SFMC, and reduce the likelihood that pipeline breaches will go undetected. Since the pipes are being watched.
• Ensures high security: Businesses make sure the pipeline is secure from beginning to end by using repeatable patterns and a shared understanding of tools and architectures.
• Enables scalability: By understanding your dataflows in SFMC as pipelines, you can gradually scale them. Starting with a tiny, manageable slice of data from a data source to a user allows you to start quickly and get results right away.
• Provides agility and flexibility: The framework that pipelines offer enables you to swiftly adjust to Salesforce marketing cloud upgrades or the needs of your data users.

The benefits of using a data pipeline for Salesforce marketing cloud to Azure integration are fascinating, right? Yes. Hevo data understands how much it helps you and is an upcoming sink for our product. 

Next, let’s move on to the next section to understand the benefits of Salesforce marketing cloud Azure integration.

What are the Benefits of Transferring Your Data from Salesforce Marketing Cloud to Azure?

Here are a few questions your data analysts can answer by replicating data from Salesforce marketing cloud to Azure:

• Which message would advance a client through the lifecycle?
• How can you know whether creatives are worth investing more in and have a good ROAS?
• Which creatives and copy are the most successful for your target market?
• How can you increase the conversion rate of your website?
• How do different targeting, creatives, or items affect your customer’s lifetime value?

That’s it about the benefits of replicating data from the Salesforce marketing cloud Azure migration. Now, let’s sum it up.

Summary

There are mainly three ways through which you can replicate data from Salesforce marketing cloud to Azure. The first method is by using CSV. For that, you need to extract data from SFMC to a CSV file and then integrate into Azure. You can use this method for one time requirements and small files. 

For large-scale and periodic data integration, the preferred method is using a data pipeline. One way to approach this is by creating a data pipeline on your own with the help of Azure data factory or Synapse Analytics. Or, use a fully automated data pipeline solution like Hevo Data. 

Visit our Website to Explore Hevo Data

In case you want to integrate data into your desired Database/destination, then Hevo Data is the right choice for you! It will help simplify the ETL and management process of both the data sources and the data destinations. We are happy to announce that we have launched Azure Synapse as a destination. 

Want to take Hevo Data for a ride? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. Check out the pricing details to understand which plan fulfills all your business needs.