Data volume trends for Marketing Lists in salesforce marketing cloud.
This blog post outlines a solution to track changes in the volume of marketing lists (sendable data extensions) over time in Marketing Cloud. This is achieved by implementing a solution that involves exporting daily counts of the sendable data extensions from each business unit to FTP and importing the data into an analytics platform to create a custom dashboard that displays trends in the volume of the marketing lists over time. By tracking these trends, clients can gain valuable insights into the effectiveness of their marketing campaigns and make data-driven decisions to optimize their strategies.
The post describes how to create a simple, daily scheduled automation to export counts of sendable data extensions in a business unit. The automation includes three primary activities: an SSJS script activity, a SQL query activity, and data extraction and file transfer activities. This post will focus primarily on the script activity.
The SSJS script activity is the most critical component of the automation. It can be divided into three parts:
- Retrieving the sendable data extension details in a BU: Server-Side JavaScript (SSJS) is used to retrieve all the sendable data extensions from the business unit. This involves using the WSProxy library to make API calls to the Marketing Cloud API and retrieve the relevant data.
- Writing the sendable data extension details to a data extension: Once the relevant data is retrieved, it is stored in a pre-defined data extension within the Marketing Cloud by using SSJS.
- Updating the count of records for each data extension using AMPscript: AMPscript is used to update the count of records for each sendable data extension in the data extension created in step 2. This enables the tracking of changes in the volume of records in each sendable data extension over time.
To retrieve all sendable data extensions in a business unit using the MID, a function called 'getAllDeInfo' is created. This function uses WSProxy to create an object and impersonate the business unit. A filter is defined to retrieve sendable data extensions only, and the columns to retrieve are defined as Name, CustomerKey, and IsSendable. The function retrieves data extension information and stores it in an array, returning an array of sendable data extensions.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38 | // Function to retrieve all sendable data extensions in a business unit using the MID
function getAllDeInfo(mid) {
// Create a WSProxy object and impersonate the business unit
var prox = new Script.Util.WSProxy();
prox.setClientId({ "ID": mid });
// Define a filter to retrieve sendable data extensions only
var filter = {
Property: "IsSendable",
SimpleOperator: "equals",
Value: true
};
// Define columns to retrieve
var cols = ["Name","CustomerKey","IsSendable"];
var reqID = null
var moreData = true;
var desc = [];
// Retrieve data extension information and store in an array
while(moreData) {
moreData = false;
var data = reqID == null ?
prox.retrieve("DataExtension", cols, filter):
prox.getNextBatch("DataExtension", reqID);
if(data != null) {
moreData = data.HasMoreRows;
reqID = data.RequestID;
if(data && data.Results) {
desc.push(data);
}
}
}
// Return an array of sendable data extensions
return desc;
}
|
A data extension is created to hold the details of sendable data extensions for the BU, with the attributes Name, CustomerKey, IsSendable, Return, Date, and Record count as below.
Text
Text
Text
Text
Date
Number
To write the details retrieved by the above function to a data extension, an SSJS script is used. The function is called to retrieve all sendable data extensions and store them in a variable. The CustomerKey of the data extension to store the results is set as 'DE_Inventory.' The script then loops through each retrieved data extension object and collects the properties of the data extension. Finally, the corresponding data extension info is upserted into the Inventory data extension.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24 | // Call the function to retrieve all sendable data extensions and store them in a variable
// Replace * with actual MID
var desc = getAllDeInfo("********");
// CustomerKey of the data extension to store the results
var custKey = 'DE_Inventory'
// Verify that the function returned valid data
if (desc) {
// Loop through each retrieved data extension object
for(var e=0; e < desc.length; e++) {
var deData = desc[e];
// Iterate through each object to collect the properties of the data extension
for(var i=0; i < deData.Results.length; i++) {
var name = deData.Results[i].Name
var customerKey = deData.Results[i].CustomerKey
var deIsSendable = deData.Results[i].IsSendable
// Upsert the corresponding data extension info into the Inventory data extension
var upsertDeInfo = Platform.Function.UpsertData(custKey, ["Name", "CustomerKey"], [name, customerKey], ["IsSendable"], [deIsSendable])
}
}
}
|
To retrieve the total record count from a data extension, an AMPscript block is used, and it is called from SSJS. The AMP script block retrieves the total record count from the data extension. The return attribute in the data extension is created to help with the criteria in the lookuprows function to get all the records in the above DE.
1
2
3
4
5
6
7
8
9
10
11
12
13
14 | %%[
var @nmaratasDE, @rsRow, @i,@name, @rowCount,@CustomerKey
SET @DE_Inventory = LookupRows("DE_Inventory","Return","1")
IF RowCount(@DE_Inventory) > 0 THEN
FOR @i = 1 to RowCount(@DE_Inventory) DO
SET @rsRow = Row(@DE_Inventory, @i)
SET @name = Field(@rsRow, "Name")
SET @CustomerKey = Field(@rsRow, "CustomerKey")
set @rowCount = DataExtensionRowCount(@name)
UpdateDE("DE_Inventory",1,"CustomerKey", @CustomerKey, "Record count", @rowCount, "Date",Format(Now(), "MMMM d, yyyy"))
NEXT @i
ENDIF
]%%
|
The complete SSJS code looks like below:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71 | <script runat=server>
Platform.Load("Core","1.1.1");
// Function to retrieve all sendable data extensions in a business unit using the MID
function getAllDeInfo(mid) {
// Create a WSProxy object and impersonate the business unit
var prox = new Script.Util.WSProxy();
prox.setClientId({ "ID": mid });
// Define a filter to retrieve sendable data extensions only
var filter = {
Property: "IsSendable",
SimpleOperator: "equals",
Value: true
};
// Define columns to retrieve
var cols = ["Name","CustomerKey","IsSendable"];
var reqID = null
var moreData = true;
var desc = [];
// Retrieve data extension information and store in an array
while(moreData) {
moreData = false;
var data = reqID == null ?
prox.retrieve("DataExtension", cols, filter):
prox.getNextBatch("DataExtension", reqID);
if(data != null) {
moreData = data.HasMoreRows;
reqID = data.RequestID;
if(data && data.Results) {
desc.push(data);
}
}
}
// Return an array of sendable data extensions
return desc;
}
// Call the function to retrieve all sendable data extensions and store them in a variable
// Replace * with actual MID
var desc = getAllDeInfo("********");
// CustomerKey of the data extension to store the results
var custKey = 'DE_Inventory'
// Verify that the function returned valid data
if (desc) {
// Loop through each retrieved data extension object
for(var e=0; e < desc.length; e++) {
var deData = desc[e];
// Iterate through each object to collect the properties of the data extension
for(var i=0; i < deData.Results.length; i++) {
var name = deData.Results[i].Name
var customerKey = deData.Results[i].CustomerKey
var deIsSendable = deData.Results[i].IsSendable
// Upsert the corresponding data extension info into the Inventory data extension
var upsertDeInfo = Platform.Function.UpsertData(custKey, ["Name", "CustomerKey"], [name, customerKey], ["IsSendable"], [deIsSendable])
}
}
}
// Call an AMPScript code block to update counts against each data extension
var stream = Platform.Function.ContentBlockByID("357842");
</script>
|
Posts
No comments:
Post a Comment