Link campaign to delivery send in Marketing Cloud

in  Salesforce Marketing Cloud
3 minutes read

Hope we will live up to the day when the campaign information will be available in the data views but until that day comes we would need to do following, to establish such link inside Salesforce Marketing Cloud.

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

Extract typeTracking Extract
File patternTracking_Extract_Campaign_ID_%%Year%%%%Month%%%%Day%%.zip
Format csv
Column Delimiter[comma]

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

Settings for file transfer activity

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
Campaign to delivery mapping data extension

Set default import folder to take extracted file from.

Extract typeTracking Extract
File patternSent.csv
Respect double quotes ” as delimiterchecked
Delimiter[comma]
Settings for file import

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