Showing SFMC data in Sales Cloud

I am seeing large numbers of questions, asking how we can sync large volumes of data from Marketing Cloud and into Sales Cloud. Well, let me start by asking you a question:

Do you really need the data to be physically in Sales Cloud? Or do you want it to be presented in Sales Cloud?

Well, that was actually two questions…

What are your options?

There is quite a difference to these two scenarios. Firstly, let’s talk about storage. A single record in Sales Cloud is about 2kb. Multiply it by tens, maybe hundreds of thousands of records. Now multiply it by what Salesforce charges for storage. Here you have your first problem.

Secondly, updating large data sets in Sales Cloud is not trivial. Especially not from Marketing Cloud. You could use a combination of SSJS and Bulk API, as described here, but still, it is quite a complex job.

Lastly, let’s discuss use cases. If you want to use the data for processes within Sales Cloud. E.g. you need to use it in a flow, trigger, or something similar, you will need the records physically in Sales Cloud. But if you only need to show this on a record page, storing it as Sales Cloud record might not be needed.

What are the alternatives?

You can do like I did: store the data in a data extension in Marketing Cloud, and present it in a Lightning Web Component (LWC). It is something I wanted to do for a while, but never got a chance (or an excuse) to build such a solution.

So, why is it so clever? Well, LWCs are quite lightweight, as they are built in the frontend and consist mainly of HTML and JavaScript. They have little dependencies on the backend of your Sales Cloud. They can be embedded in record pages, where you can basically allow them to do whatever you want.

How does an LWC communicate with a data extension in Salesforce Marketing Cloud? Well, it doesn’t. It communicates with a custom API endpoint in your Salesforce Marketing Cloud. Confusing? It is actually quite simple.

Let’s assume, for simplicity, a use case of showing email engagement on a Lead/Contact record page in Sales Cloud.

JSON Code Resource as custom API endpoint

We will start by creating a data extension called Aggregated_email_engagement. It will hold information about emails received by the individual person over the last 60 days. It stores following fields:

• Subscriber Key (text)
• Email name (text)
• Opened (boolean)
• Clicked (boolean)
• Bounced (boolean)
• Sent date (datetime)

I will not go into details on how to populate this data, as it is outside of the scope for our guide here.

Next, we will need a JSON code resource, which will fetch data from this data extension for a particular record ID, assuming we are using Lead/Contact IDs as Subscriber Key in Marketing Cloud (which you should).

The code for this code resource is found below:

But why a JSON code resource? Well, firstly, it return the correct content type header, making it compliant with integrations requiring application/json header. Secondly, it is free. Well, free in the sense of it not costing us any supermessages to show – contrary to Cloud Pages, which consume one supermessage each time they are viewed.

Sample output of the this code resource can be seen here:

{
    "Emails": [
        {
            "name": "Your Email 1",
            "opened": true,
            "clicked": false,
            "bounced": false,
            "sent": "2023-10-17T02:32:28.1770000"
        },
        {
            "name": "Your Email 2",
            "opened": false,
            "clicked": false,
            "bounced": false,
            "sent": "2023-10-17T02:00:54.4470000"
        }
    ]
}

As you can see, we are returning a valid JSON containing information on emails received and engaged with. This could be any type of data you fetch from a data extension.

You have probably already realised by looking at our Ampscript, that we expect an ID passed as request parameter, making our URL look something like this:
https://cloud.e.example.com/coderesource?id=0031o00F02T7JszFFF

Fetching and presenting data

I could write LONG articles about how to get started building Lightning Web Components, but Trailhead has actually really good content on this topic, which got me started quite easily. Follow this guide to get started with Visual Studio Code, Salesforce CLI and LWC in general. You can start by using a Trailhead Org to connect to, and start building.

Compared to the Create a Hello World Lightning Web Component Trailhead project, you will need to make few adjustments.

Firstly, the HTML needs to be updated. We need a basic LWC data table:

As you can see, we have a conditional visibility, which will hide the component should no email engagement records be found in the JSON.

Secondly, the javascript is needed:

It will ensure to:

1. Fetch the JSON from our code resource, using the ID of the record (assume it is placed on Lead and Contact record pages)
2. Parse the JSON, preparing to populate the table
3. Hide the LWC should no records be found in the JSON

And you are done.

Few considerations: as this is a front-end integration with no authentication, you should be cautious passing sensitive information in this code resource. In our case, no personally identifiable information (email address, name, etc.) is exposed. This is my first LWC implementation, which took me few hours to finalise, hence it is to be seen more as a proof-of-concept rather than a best practice for this type of implementations.

Hence I am more than ever open to your feedback and input. Let me know in the comments.