Search A Data Extension From Sales Cloud

Search A Data Extension From Sales Cloud

Create a lightning component to search a Send Logging data extension in Marketing Cloud.

Jason Hanshaw
– 38 minute read

When working with clients that are heavily integrated with Sales Cloud, it can be a challenge for users of the core system to identify data existing in Marketing Cloud that isn't included in standard tracking or the SF object model. While we could provide an automated solution in Marketing Cloud to sync this data into the core system, you run the risk of confusing novice users as well as having a less dynamic approach for adhoc requests.

In this post, I'll demonstrate an example solution, using a custom lightning component, that allows Sales and Service Cloud users to search a Send Logging data extension by email address within the core system UI.

Note: This solution uses an undocumented API endpoint in Marketing Cloud that could be modified or removed without warning by Salesforce. A sudden change to this could result in your application no longer functioning as intended. For a more reliable method of data retrieval, it is recommended that you use the SOAP API instead. Since this post is just a demonstration of this functionality, I'll be using the REST endpoint for simplicity and as a matter of personal preference.

Before We Begin:

Assumptions:

This post assumes that you are familiar with development, version control and deployment of an SF codebase through SalesforceDX. For the ease of creating a quick proof-of-concept, you may also create your component via the Developer Console though this method should not be used in production or team-based sandbox environments. Also, we'll assume that you have an understanding of creating unit tests as well as storing API credentials as these items will not be within the scope of this post.

With that out of the way, let's get our Marketing Cloud environment configured.

Prerequisites:

In order for our lightning component to function, we'll need to ensure that we have configured our data source and installed package on the Marketing Cloud side.

Send Logging Data Extension

First, you'll need to create a Send Logging data extension with the following custom fields added:

  • view_email_url
  • send_date

The view_email_url column will automatically log the view as webpage link for each email send and will allow our SF users to view the individual email creative from our component. The send_date column will log the datetime that the email was sent to a subscriber.

Note: In order for our send_date column to populate data, we'll need to add the following script to the emails/templates that we are deploying out of Marketing Cloud:

%%[
var @send_date
set @send_date = Now()
]%%

 
This will allow our Send Logging data extension to capture that value at send time, and will give our users more insight into the email delivery by seeing the date it deployed.

Installed Package

In addition to setting up our Send Logging data extension, we'll also need to create an installed package so that we can authenticate and make API calls to Marketing Cloud from an external system. The process for doing this is outside the scope of this post, but you can find more information in the Salesforce documentation.

Now that we have everything configured on the Marketing Cloud side, lets start developing our lightning component.

Building Our Component:

Now that we are ready to start developing our component, lets take a look at each of our necessary files in order to get a better idea on how this application will function:

SendLogSearch.cls

This is our custom controller Apex class that will be responsible for making our API calls into Marketing Cloud and returning the data which we want to display in our component.

SendLogSearch.cmp

This is the component file that will define the markup and design of our application. It's helpful to consider this as the "front-end" of our component.

SendLogSearch.css

This file will contain all CSS styles that are being utilized within our component file. It should be noted that all declarations in the file are prepended with the prefix .THIS (i.e. .THIS .some-class { color: red; } in order to allow SF to accurately style our application since this class will automatically be converted to our component name by the system framework.

SendLogSearchController.js

This is our client-side controller that will contain our functions related to events that occur within the component, such as when the user enters the desired email address and clicks our search icon.

SendLogSearchHelper.js

This file contains our utility functions for the component. In order to maintain code readability and testability, you should keep your client-side controller as lean as possible and include any functions that do complex processing within the helper. For our application, the helper file will process all of our javascript logic, while the client-side controller serves to call the helper file based on events from our component.

SendLogSearch.app

Our application file will simply serve as a container for our component files and tells the system what to execute.

With our necessary files outlined, let's start building!

Creating The Apex Class

We'll need to create a custom controller Apex class in order to interact with Marketing Cloud to retrieve our data from the Send Logging data extension. To begin let's create the basic class structure that will house our functions:

public with sharing class SendLog {

}

We'll use the with sharing keyword in our class in order to execute our functions in a user (with security settings enforced) context rather than a system context. This won't be applicable in our use-case, but is helpful in solutions where access to fields, objects, etc... should be dependent on the user.

With our apex class declared, let's add a generic function to make a POST request so that we can authenticate into Marketing Cloud a little easier. Let's also add the @testVisible keyword to this as well in order to make it available for constructing unit tests on this private method in the future.

public with sharing class SendLog {
    @testVisible private static String makeJSONPostCall(String endPoint, String restBody, String token){
        Http h = new Http();
        HttpRequest r = new HttpRequest();
        r.setTimeout(60000);
        r.setEndpoint(endPoint);  
        r.setMethod('POST');
        r.setHeader('Content-type','application/json'); 
        if(!String.isEmpty(token)){
        r.setHeader('Authorization', 'Bearer ' + token);
        }
        r.setBody(restBody);    
        HttpResponse res = h.send(r);
        return res.getBody();
    }
}

Let's add some global variables for our class that will specify the user credentials and API endpoint for searching our Send Logging data extension:

Remember, this is for demonstration only and any production implementation should never include unencrypted credentials in your class.

public with sharing class SendLog {
    private static String MARKETING_CLOUD_AUTH_URL = YOURAUTHURL;
    private static String ACCESS_TOKEN = 'access_token';
    private static String ClientID = CLIENTID;
    private static String ClientSecret = CLIENTSECRET;
    private static String sendLogURL = 'https://www.exacttargetapis.com/data/v1/customobjectdata/key/SendLog/rowset?$page=1&$pageSize=5&$orderBy=send_date%20DESC&$filter=emailaddr=';
    @testVisible private static String makeJSONPostCall(String endPoint, String restBody, String token){
        Http h = new Http();
        HttpRequest r = new HttpRequest();
        r.setTimeout(60000);
        r.setEndpoint(endPoint);  
        r.setMethod('POST');
        r.setHeader('Content-type','application/json'); 
        if(!String.isEmpty(token)){
        r.setHeader('Authorization', 'Bearer ' + token);
        }
        r.setBody(restBody);    
        HttpResponse res = h.send(r);
        return res.getBody();
    }
}

The important item to note here is the structure of the sendLogURL variable. This will be the (again, undocumented) endpoint that we will call to retrieve our data from the Send Logging data extension. For our purposes, we will set the following parameters on the URL:

  • key: SendLog (Our External Key Of Our Send Logging data extension)
  • page: 1 (Return only 1 page of results)
  • pageSize: 5 (We'll return only 5 results from our data extension)
  • orderBy: send_date%20DESC (We'll order our results based on the most recent send_date)
  • filter: emailaddr (Filter data extension by an email address)

Now, let's set up a function to make a request to Marketing Cloud in order to generate the access token we'll need to call that endpoint.

public with sharing class SendLog {
    private static String MARKETING_CLOUD_AUTH_URL = YOURAUTHURL;
    private static String ACCESS_TOKEN = 'access_token';
    private static String ClientID = CLIENTID;
    private static String ClientSecret = CLIENTSECRET;
    private static String sendLogURL = 'https://www.exacttargetapis.com/data/v1/customobjectdata/key/SendLog/rowset?$page=1&$pageSize=5&$orderBy=send_date&$filter=emailaddr=';
    @testVisible private static String makeJSONPostCall(String endPoint, String restBody, String token){
        Http h = new Http();
        HttpRequest r = new HttpRequest();
        r.setTimeout(60000);
        r.setEndpoint(endPoint);  
        r.setMethod('POST');
        r.setHeader('Content-type','application/json'); 
        if(!String.isEmpty(token)){
        r.setHeader('Authorization', 'Bearer ' + token);
        }
        r.setBody(restBody);    
        HttpResponse res = h.send(r);
        return res.getBody();
    }
    @AuraEnabled
    public static String getMarketingCloudToken(){
        String responseBody = makeJSONPostCall(
            MARKETING_CLOUD_AUTH_URL,
            JSON.serialize( new Map<String, String>{
                'grant_type': 'client_credentials'
                    'clientId' => ClientID,
                        'clientSecret' => ClientSecret
                            'account_id': YOURMID
                    } ), NULL
        );
        return ((Map<String, String>) JSON.deserialize(responseBody, Map<String, String>.class)).get( ACCESS_TOKEN ); 
    }
}

This function makes use of our makeJSONPostCall function to make a POST request to Marketing Cloud with our credentials in order to return an access token if the call is successful.

Finally, let's write another function that will take a user input for email address and make a GET request to Marketing Cloud in order to return our filtered data set from the Send Logging data extension.

public with sharing class SendLog {
    private static String MARKETING_CLOUD_AUTH_URL = YOURAUTHURL;
    private static String ACCESS_TOKEN = 'access_token';
    private static String ClientID = CLIENTID;
    private static String ClientSecret = CLIENTSECRET;
    private static String sendLogURL = 'https://www.exacttargetapis.com/data/v1/customobjectdata/key/SendLog/rowset?$page=1&$pageSize=5&$orderBy=send_date&$filter=emailaddr=';
    @testVisible private static String makeJSONPostCall(String endPoint, String restBody, String token){
        Http h = new Http();
        HttpRequest r = new HttpRequest();
        r.setTimeout(60000);
        r.setEndpoint(endPoint);  
        r.setMethod('POST');
        r.setHeader('Content-type','application/json'); 
        if(!String.isEmpty(token)){
        r.setHeader('Authorization', 'Bearer ' + token);
        }
        r.setBody(restBody);    
        HttpResponse res = h.send(r);
        return res.getBody();
    }
    @AuraEnabled
    public static String getMarketingCloudToken(){
        String responseBody = makeJSONPostCall(
            MARKETING_CLOUD_AUTH_URL,
            JSON.serialize( new Map<String, String>{
                'grant_type': 'client_credentials'
                    'clientId' => ClientID,
                        'clientSecret' => ClientSecret
                            'account_id': YOURMID
                    } ), NULL
        );
        return ((Map<String, String>) JSON.deserialize(responseBody, Map<String, String>.class)).get( ACCESS_TOKEN ); 
    }
    @AuraEnabled
    public static String searchSendLog(String Email) {
            String authToken = getMarketingCloudToken();
            Http h = new Http();
            HttpRequest webReq = new HttpRequest();
            webReq.setMethod('GET');
            webReq.setHeader('Authorization','Bearer '+ authToken);
            webReq.setEndpoint(sendLogURL + '"' + Email + '"');
            HttpResponse res = h.send(webReq);
            String response = res.getbody();
            return response;
    }
}

That's all we need in order to configure our Apex class to authenticate and retrieve the necessary data from Marketing Cloud. With that piece of the puzzle in place, let's take a look at our client-side controller and helper files in order to pull this data into our component.

Creating The Client-Side Controller

Our client-side controller configuration is actually quite simple to implement. Since we are using our helper file to do the heavy-lifting for our component, our controller only needs to provide a function that our front-end can call in order to retrieve the data gathered in our helper. We'll create a new function that will call our helper function.

  ({
    sendLogSubmit: function (component, event, helper) {
    helper.getSendLogResults(component, event, helper);
  }
  });

 
That's all we need for our controller, so let's take a look at our helper file to see how we are retrieving the data from our Apex class.

Creating The Helper File

For our helper file, we'll need to set up a function that can make a request to our Apex class and then pass the appropriate JSON source to the front-end in order to dynamically display results from our Send Logging data extension:

({
    getSendLogResults: function (component, event, helper) {
    var action = component.get("c.searchSendLog");
    var sendLogEmail = component.get("v.sendLogEmail");
    action.setParams({ Email: sendLogEmail });
    action.setCallback(this, function(response) {
        var temp = response.getReturnValue();
        var json = JSON.parse(temp.toString());
        var primNode = json.items;
        var sendArray = [];
        for(var i in primNode) {
            var arrVals = primNode[i].values;
            sendArray.push(arrVals);
        }
        component.set("v.sendLogResults",sendArray);
    });
    $A.enqueueAction(action);
  }
});

 
In this function, we're first identifying the class we want to invoke (component.get("c.searchSendLog")) as well as retrieving the email address input that the user has provided on our front-end of the component (component.get("v.sendLogEmail")). Then we execute the request to our controller and return the JSON response from Marketing Cloud containing the 5 most recent entries for that email address in the Send Logging data extension.

Once we've captured that data, we'll create an array that will contain the nested results we want to display. Then, we'll assign it to a variable that we'll use to call this data within our front-end (component.set("v.sendLogResults")).

Creating The Component File

Our application logic has now been setup, but we don't have a way for our users to interact with our scripts. To do this, we'll need to create our component file that will serve as the front-end.

To begin, let's build out the basic structure of our component.

<aura:component
  controller="SendLog"
  implements="flexipage:availableForAllPageTypes"
  access="global"
>
  <aura:attribute name="sendLogEmail" type="String" default="" />
  <aura:attribute name="sendLogResults" type="String[]" />
</aura:component>

 
This is the base for our component. We're letting it know what customer controller Apex class we'll be using for this as well as the context that we can call/display this component.

In addition to this, we're going to declare the variables that we made reference to in our helper file, that will allow us to take the user input (sendLogEmail) and to display the array of results returned (sendLogResults).

Now we can add in the markup for our component so that we can take input and display things in a user-friendly way.

<aura:component
  controller="SendLog"
  implements="flexipage:availableForAllPageTypes"
  access="global"
>
  <aura:attribute name="sendLogEmail" type="String" default="" />
  <aura:attribute name="sendLogResults" type="String[]" />
  <div class="component">
    <div class="container">
      <div class="main">
        <div class="row">
          <div class="col-s3 col table-container">
            <div class="card-container">
              <div class="card">
                <div class="card-title">
                  <h6 class="card-subdescription">SEARCH EMAIL</h6>
                  <h3 class="card-description">Send Log</h3>
                </div>
                <div class="card-subcontainer">
                  <div class="table-search">
                    <lightning:input
                      type="text"
                      name="email-search"
                      placeholder="Enter An Email Address To Search Sent Emails"
                      value="{!v.sendLogEmail}"
                    />
                    <div class="search-icon" onclick="{!c.sendLogSubmit}">
                      <lightning:icon
                        iconName="utility:search"
                        alternativeText="Search"
                        size="small"
                    /></div>
                  </div>
                  <aura:if isTrue="{!not(empty(v.sendLogResults))}">
                    <div class="table-responsive">
                      <table class="table">
                        <thead>
                          <tr>
                            <th>Email Name</th> <th>Send Date</th
                            ><th>Email Send</th>
                          </tr>
                        </thead>
                        <tbody>
                          <aura:iteration items="{!v.sendLogResults}" var="row">
                            <tr>
                              <td class="email-name">{!row.emailname_}</td>
                              <td>{!row.send_date}</td>
                              <td
                                ><a
                                  class="btn preview"
                                  href="{!row.view_email_url}"
                                  target="_blank"
                                  >Preview</a
                                ></td
                              >
                            </tr>
                          </aura:iteration>
                        </tbody>
                      </table>
                    </div>
                    <aura:set attribute="else">
                      <div class="empty-sl"
                        ><div class="empty-header"
                          ><h3>No results listed.</h3></div
                        ><div class="empty-text"
                          ><p
                            >Enter an email address in the search bar in order
                            to view the most recent email sends for a
                            recipient.</p
                          ></div
                        ></div
                      >
                    </aura:set>
                  </aura:if>
                </div>
              </div>
            </div>
          </div>
        </div>
      </div>
    </div>
  </div>
</aura:component>

 
Let's break down some of this markup in order to see what's going on outside of basic styling. Here's a snippet from our component file related to taking user input:

<lightning:input type="text"
  name="email-search"
  placeholder="Enter An Email Address To Search Sent Emails"
  value="{!v.sendLogEmail}"
  />
<div class="search-icon" onclick="{!c.sendLogSubmit}">
<lightning:icon
  iconName="utility:search"
  alternativeText="Search"
  size="small"
  />

 
Notice that we've set the value parameter on the lightning:input element to equal the variable we defined at the top of our file. This allows us to access and set this value programatically for use in our controllers and helper. Also, you will see that we have attached the sendLogSumbit function from our client-side controller to the onclick action of our search icon. This will execute our client-side controller, and thus helper function, when a user clicks the icon.

Before the user actually clicks the icon to submit their search, or if the search returns no results, we'll want to display some fallback text to the user. We can wrap this section of the markup with an if/else statement to show our data when we have results and to show a fallback when no results are found:

<aura:if isTrue="{!not(empty(v.sendLogResults))}">

<aura:set attribute="else">

</aura:set>
</aura:if>

 
The only piece left is to iterate through the results, retrieved from our helper function, and to display them to the user:

<aura:iteration items="{!v.sendLogResults}" var="row">
  <tr>
    <td class="email-name">{!row.emailname_}</td>
    <td>{!row.send_date}</td>
    <td
      ><a
      class="btn preview"
      href="{!row.view_email_url}"
      target="_blank"
      >Preview</a
      ></td
      >
  </tr>
</aura:iteration>

 
This snippet takes our variable sendLogResults and iterates over each row of the data. We use the local row variable as an alias in order to access a given element in each iteration and to display its properties in a table row.

Creating The CSS File

Now, we'll want to style our elements so that the component is more visually appealing to the user. Here's the full CSS file we'll use to style our component:

/* PRIMARY CONTAINER STYLES */
.THIS .col-s12 {
  flex: 0 0 100%;
  max-width: 100%;
  display: flex;
  align-items: stretch;
  position: relative;
  width: 100%;
  min-height: 1px;
}
.THIS .container {
    width: 100%;
    background-color: #f8f8f8;
    border-radius: 5px;
    padding: 1rem 0;
}
.THIS .main {
  width: 92%;
  margin-left: auto;
  margin-right: auto;
  margin-bottom: 0.75em;
  color: #575a65;
}
.THIS .row {
  display: flex;
  flex-wrap: wrap;
  margin-right: -15px;
  margin-left: -15px;
  margin-bottom: 2rem;
}
/* CARD STYLES */
.THIS .card {
  box-shadow: 0 4px 8px 0 rgba(0, 0, 0, 0.2);
  transition: 0.3s;
  background-color: #ffffff;
  border-radius: 5px;
  width: 100%;
}
.THIS .card:hover {
  box-shadow: 0 8px 16px 0 rgba(0, 0, 0, 0.2);
}
.THIS .card-container {
  width: 100%;
}
.THIS .card-description {
  font-size: 1.85em;
}
.THIS .card-footer {
  padding: 15px 15px 10px 20px;
  display: flex;
  align-items: center;
}
.THIS .table-container .card-container {
  padding: 0 15px;
}
.THIS .card-subcontainer {
  padding: 2px 16px;
}
.THIS .card-title {
  padding: 25px 20px;
}
/* TABLE STYLES */
.THIS .table {
  width: 100%;
  margin-bottom: 1rem;
  color: #575a65;
}
.THIS .table td,
.THIS .table th {
  padding: 0.75rem;
  vertical-align: middle;
  color: #575a65;
}
.THIS td, .THIS th {
  padding-left: 5px !important;
}
.THIS .table thead th {
  vertical-align: bottom;
}
.THIS .table-bordered {
  border: 1px solid #dee2e6;
}
.THIS .table .email-name {
  color: #222222;
}
.THIS .table-striped tbody tr:nth-of-type(odd) {
  background-color: rgba(0, 0, 0, 0.05);
}
.THIS .table-hover tbody tr:hover {
  color: #575a65;
  background-color: rgba(0, 0, 0, 0.075);
}
.THIS .table .btn {
  border: none;
  color: white;
  padding: 10px 15px;
  text-align: center;
  text-decoration: none;
  display: inline-block;
  font-size: 12px;
  margin: 4px 2px;
  max-width: 100%;
  border-radius: 5px;
}
.THIS .table .preview {
  background-color: #7ad1dc;
}
.THIS tr:not(:last-child) {
    border-bottom: 2px solid #e9ecef;
}
/* SEARCH AND ICON STYLES */
.THIS button.slds-button.slds-button_icon.border-0.slds-button_icon-border {
  border: none;
}
.THIS .empty-sl {
    padding: 1rem 2rem 2rem .2rem;
}
.THIS .empty-header h3 {
    font-size: 1.2rem;
}
.THIS .empty-text {
  padding-top: 1rem;
}
.THIS .search-icon {
  display: inline-flex;
  cursor: pointer;
}
.THIS .slds-form-element__label:empty {
    display: none;
}
.THIS .table-search lightning-input {
    display: inline-block;
    width: 85%;
}
.THIS .table-search lightning-primitive-icon {
    display: inline-block;
}
.THIS .table-search {
  padding-bottom: 0.75rem;
}
.THIS .table-search lightning-primitive-icon {
    padding-left: 10px;
}

Creating The App File

We're now ready to create our application file so that we can utilize this functionality as part of a custom app or as a standalone item in a SF page.

<aura:application >
	<c:SendLogSearch />
</aura:application>

Conclusion

SearchSendLog

That's it! We're now able to provide our Sales and Service Cloud users with an easy and efficient way of searching our Send Logging data extension from the core system UI.

With some simple modifications to the above scripting, we could provide the user with more complex filtering criteria or even allow them to dynamically define the data extensions and criteria they would like to use in their search.

To see the source for this post, you can view the repository on github.