0

I am new to SSJS and have not used it yet.

I have a use case where I need to extract JobID and Data Extention Name from SendLog Data Extension.

I use JobID and DE_Name in SQL query but right now I need to run it individually for each email.

Based on some recommendation, I was told to use SSJS, run a loop to extract ALL DISTINCT JobID and DE_Name and push the values to SQL Query in a variable.

Can anyone help with the SSJS and steps as I am new to SSJS?

UPDATE - Progress made so far:

Step 1 - Created a Data Extension "Test_SendLog_Get_DistinctJobs" that is populated by a SQL query (it will run on regular based to get JobID and DE_Name)

SELECT DISTINCT
  JobID,
  DE_Name
FROM [SendLog_DE]

*Test_SendLog_Get_DistinctJobs DE includes an additional column LU with default value 1 (Not sure how to use it yet in the logic)

Step 2 - Created the SSJS (made an attempt to create a for loop logic)

<script runat="server" language="JavaScript">
    Platform.Load("core","1");
    // instantiate the WSProxy object to make API calls
    var api = new Script.Util.WSProxy();

    // Retrieve DISTINCT JOB IDs
    var de1 = DataExtension.Init("Test_SendLog_Get_DistinctJobs");
    var tables = de1.Rows.Retrieve();
    
    var sql = "";
    var targetKey = '1234-1234-1234';
    var targetName = 'Test_SendLog_SSJS_Output';
    var queryKey = '2222-22222-22222-22222';
    var objectId = '1000-10000-110000-10000';

    // Build UNION ALL SQL
    for (var i = 0; i < tables.length; i++) {
        var table = tables[i];
        sql += "SELECT EMAIL_Address AS SubscriberKey FROM '" + table.DE_NAME + "' WHERE EMAIL_Address NOT IN (SELECT SubscriberKey from _sent where jobid = '" + table.JobID + "';
        
    }

    // Define query definition to update by API
    var qdf = {
        "CustomerKey": queryKey,
        "ObjectID": objectId,
        "QueryText": sql,
        "TargetType": "DE",
        "TargetUpdateType": "Overwrite",
        "DataExtensionTarget": {
            "CustomerKey": targetKey,
            "Name": targetName
        }
    };

    // Update the query def
    var options = {};
    var response = api.updateItem("QueryDefinition",qdf,options);
    Write(Stringify(response));
</script>

ISSUE - Getting an error message

ERROR:  A runtime error occurred while the SSJS Activity executed the SSJS script. Please review the SSJS activity to ensure that errors are handled properly.
9
  • SSJS is not meant for bulk data operations -- especially not the SendLog, which tends to be a large data set. Jun 27 at 20:51
  • Hi @AdamSpriggs I plan on using a filter of sendLog for optimizing the performance.
    – kuml2
    Jun 28 at 0:18
  • I would appreciate any help with the solution so I can learn how to use SSJS in this use case, from there I can see what can be done to optimize it.
    – kuml2
    Jun 28 at 0:19
  • Items when I'm having a tough time - (1) how to run a loop that is capable of picking all distinct JobID and EmailName from SendLog data extension; (2) how to save the looped values in variables and pass it to SQL query. (3) understand what is writing to output to date extension, SQL query or SSJS?
    – kuml2
    Jun 28 at 1:20
  • 1
    Why don’t you simply use SQL for this? Jun 28 at 5:28

1 Answer 1

0

So first thing you would need to do is get the list of JobID and Email Names out of SFMC. Now COULD you do this via SSJS? Yes. Should you? NO.

So my first step would be to create a DE that has these fields in it and a third field named LU that is a text datatype with a length of 1 and a default value of 1. (This will be used to be able to pull all records from DE via AMPscript or SSJS lookup later)

Then you would run the following Query to get all the Unique combinations of JobID and Email Name from your send log (This can very quickly become an issue due to size and processing draw considering the 30 min timeout window, so might want to look at a more efficient way to do this part that does not involve the send log)

SELECT DISTINCT
  JobID,
  EmailName
FROM [mysendLog]

This will then fill your newly created DE above.

You then use this DE to fill in your variables via a Lookup/Retrieve in SSJS. Once you have the rowset returned from there, you create a for loop to run through each and then utilize WSProxy to update your query activities and then run them. Now as to logic for which Query activities to update and such, that is something you will need to come up with yourself, but I can give example functions for WSProxy to update the SQL Queries as well as to run one.

Update:

function updateQuery(queryObjID,queryText, deTargetObj,updateType) {

  var opts = {SaveOptions: [{'PropertyName': '*', SaveAction: 'UpdateAdd'}]};

  var queryObj = {
    ObjectID: queryObjID,
    QueryText: queryText,
    DataExtensionTarget: deTargetObj,
    TargetUpdateType: updateType
  }

  var res = prox.updateItem("QueryDefinition",queryObj,opts)

  return res;
}

Although there is a way via WSProxy to run a query definition, the Core functions are likely your best bet as they are fairly easy to use and work well.

Perform Query Activity (https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/ssjs_queryDefinitionPerform.html)

var qd = QueryDefinition.Init("myQueryDef");
var status = qd.Perform();

and if you want to update it via Core as well, see an example here: (https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/ssjs_queryDefinitionUpdate.html)

var qd = QueryDefinition.Init("myQueryDef");

var status = qd.Update({
    Name : "Updated Query Definition Name",
    QueryText : "SELECT SubKey, Email, Name FROM [Example Target DE] where FavoriteItemID=12"
});
3
  • Hi @Gortonington thank you so much for your guidance. I used your pointers and made an attempt in creating a solution (POSTED ABOVE) but still not sure on how this is going to work. I have posted my questions above, would appreciate if you could please guide me? Thanks!
    – kuml2
    Jun 28 at 22:07
  • hi @Gortonington i have made some progress and posted my new code / logic. I think i have now better understanding of how the process will work. i am still looking for help with the ssjs since i am getting an error. any advise please?
    – kuml2
    Jun 30 at 3:04
  • Hi @Gortonington I just wanted to follow up if you got a chance to review my updates? any kind of help or guidance would be appreciated.
    – kuml2
    Jun 30 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.