SFMC | Link campaign to delivery send

Are you aware of Salesforce Marketing Cloud’s additional marketing feature known as Campaign? This tool allows you to group similar journeys together, providing greater organization for your marketing activities.

However, one drawback is that the campaign information is not available in the data views. While waiting for a fix from Salesforce, you can establish a link within the platform using the following workaround.

Get campaign list

To get campaign list we need to use REST API to get all campaign as such information is not available in any data view as well.

Before that we create package with Server-to-Server type component in Marketing Cloud setup and add the following scope to it:

• Data Extensions: Read, Write
• Campaign: Read

Create data extension to store campaign data:

• name and external key should be the same. In my case I use prefix_campaigns both external key and name

Once we have a package with our client secret and client id we can move on to create SSJS GET request call to receive campaigns and save them to our data extension.

<script runat="server">
  
   Platform.Load("core", "1");
   
   var getToken = function(setup) {
       var config = {
           url : setup.authBaseURI + "v2/token",
           contentType : "application/json",
           payload : {
               "client_id": setup.clientId,
               "client_secret": setup.clientSecret,
               "grant_type": "client_credentials"
           }
       }
       var req = HTTP.Post(config.url, config.contentType, Stringify(config.payload));
       if (req.StatusCode == 200) {
           var res = Platform.Function.ParseJSON(req.Response[0]);
           return res.access_token;
       }
       return false;
   },
   //https://ampscript.xyz/how-tos/how-to-loop-rest-api-get-requests-in-ssjs/
   performGetRequest = function(url, config, token) {

        var req = new Script.Util.HttpRequest(url);
            req.emptyContentHandling = 0;
            req.retries = 2;
            req.continueOnError = true;
            req.setHeader("Authorization", "Bearer " + token);
            req.method = "GET";
            req.contentType = config.contentType;
            req.encoding = "UTF-8";

        var res = req.send();
        return Platform.Function.ParseJSON(String(res.content));
    },
    getCampaigns = function(token, setup) {
        var currentPage = 1,
        perPage = 50,
        config = {
            contentType : "application/json",
            headerName : ["Authorization"],
            headerValue : ["Bearer " + token]   
        },
        campaigns = [],
        nextPage = true;
        while(nextPage){
            nextPage = false;
            var url = setup.restBaseURI + "hub/v1/campaigns?$page="+currentPage+"&$pageSize="+perPage,
                res =   performGetRequest(url, config, token);
            if (res && res.items.length){
                for (var i = 0;i<res.items.length;i++)
                campaigns.push(res.items[i]);
                nextPage = true;
            }
            currentPage++;
        }
        return campaigns;
    },
    setup = {
        authBaseURI:"https://<EXAMPLE>.auth.marketingcloudapis.com/",
        restBaseURI: "https://<EXAMPLE>.rest.marketingcloudapis.com/",
        clientId : "<EXAMPLE>",
        clientSecret: "<EXAMPLE>"
    },
    token = getToken(setup),       
    campaigns = getCampaigns(token, setup),
    rows = [],
    campaignDe =  DataExtension.Init("campaigns"),
    deliveries = [];

    for (var i = 0;i<campaigns.length;i++){
        if(!campaignDe.Rows.Lookup(["id"], [campaigns[i].id],1))
            campaignDe.Rows.Add(campaigns[i]);             
    }
</script>

More information about the campaigns GET request

https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/getCampaignCollection.html

Get campaign ids mapped to delivery send id

This part is a bit strange as I would think that campaign id information is available on data views but it is not and only way how to access this information is with help of tracking extract.

Create “Data extract” activity

Check the following options:

• Extract sent
• Include Campaign ID

Create “File transfer” activities

This file transfer is just to move our extracted file from the safe house to sftp. We will use the same pattern defined before and destination we can select Export

Create another file transfer activity that its function will be to extract the archived export. As before we will use the same file name pattern

Create “File import” activity

Last but not least we need to create file import that actually will take the campaign id and send id and stores it in data extension with only two columns and both set as primary key. This will deduplicate the unnecessary rows for us. Also if desired set the data retention policy before you create the data extension.

• name and external key should be the same. In my case I use prefix_Sends_To_Campaign_Map both external key and name

Set default import folder to take extracted file from.

Mapping can be set as Map by ordinal if you do not want to load sample file nor store all columns in your DE (they are not needed as campaign id is taken from sent log and we do not need information like subscriber key)

Data action set to Add only so the automation will automatically not store row that are already saved.

Reconcile with _Job data view

Now that we have both tables and we can easily map campaign information to our send data extension and reconcile with _Job data view

SendId from Sends_To_Campaign_Map DE is equal to the JobId on _Job view

FROM _Job as J
LEFT JOIN prefix_Sends_To_Campaign_Map as STCM on J.JobID = STCM.SendID
LEFT JOIN prefix_campaigns CMP on STCM.CampaignId = CMP.id

Please leave this field empty

Oh hi there
I have a FREE e-book for you.

Sign up now to get an in-depth analysis of Adobe and Salesforce Marketing Clouds!

Email Address *

We don’t spam! Read our privacy policy for more info.

Check your inbox or spam folder to confirm your subscription.