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 listTo 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 idThis 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 type | Tracking Extract |
| File pattern | Tracking_Extract_Campaign_ID_%%Year%%%%Month%%%%Day%%.zip |
| Format | csv |
| Column Delimiter | [comma] |
Check the following options:
- Extract sent
- Include Campaign ID
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
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.
| Extract type | Tracking Extract |
| File pattern | Sent.csv |
| Respect double quotes ” as delimiter | checked |
| Delimiter | [comma] |
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.
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