0

SSJS isn't my strong suit, but I'm hoping to use it to accomplish a task. I'm going to use AMPscript syntax to explain what I'm trying to do so I can explain it best.

In our campaign object in SF CRM we're storing the Data Extensions names as they correspond to particular campaigns. So we have an entry DE and a Log DE. The entry DE is updated via an Overwrite SQL Query once a day and adds net new people. The Log DE is updated via an Append SQL Activity this is to keep a log of what was supposed to have been deployed.

There are times where an ampscript fails in an email and while i know we can set up a campaign alert, I want to also setup an alert that basically checks all of our journey log DE (All_Logs_DE) against the main SFMC sendlog. We're managing 50+ campaigns at a time, and new campaigns and DEs are added all the time so we don't want to manually manage a list.

Instead, we want to do the following:

  1. Pull all the DE names into a Data Extension from the synchronized DE. This will be the All_Logs_DE.
  2. Then we want to get all those records inside of those DEs and check them against the sendlog.
  3. We want to log those unsent records into a DE and send an email to our campaign managment team to let them know something is wrong.

I feel like I have 2 options depending on what's possible in teh SSJS activity.

Automation that looks like this

  1. SSJS Activity
%%[
set @status = "True" /* just to be able to select all */
set @rows = LookupRows("All_Logs_DE","status", @status)
set @rowCount = rowcount(@rows)

if @rowCount > 0 then

  for @i = 1 to @rowCount do

    set @Log_DE_Name = field(@row,"Name")
    
    set @rows = LookupRows(@Log_DE_Name,"status", @status)
    set @rowCount = rowcount(@rows)
    
    if @rowCount > 0 then

        for @i = 1 to @rowCount do
                  
            Option 1: Check each row against the sendlog and then append to Unsent_Log_DE
            Option 2: Log all records into Queued_Log_DE 

    %%[next @i]%%
    %%[else]%%
    %%[endif]%%


    ]%%

    %%[

  next @i ]%%

%%[ else ]%%

No rows found

%%[ endif ]%%
  1. If we were able to check all records against the sendlog within the loop, then this step would just be sending the email (which we'll only send if the Unsent_Log_DE, isn't empty)

  2. If we aren't ableto check agaisnt the sendlog within the loop, then this step would be checking against the sendlog in an SQL query and outputting the result to the Unsent_Log_DE.

  3. We only need three steps if we haven't sent the email yet.

I know that was alot of info, but I'm hoping to get some help here.

3
  • 1
    Why not do it via SQL and Verification activity in Automation Studio? In SQL you compare the two DEs (campaign members vs sent emails) and in verification step, in case there are differences, send an alert to a predefined recipient?
    – zuzannamj
    Dec 13, 2022 at 21:46
  • @zuzannamj -- So I thought about that. Is there a way I can still reference a list of DE names in an SQL activity versus having to create an extra set of steps in every campaign automation. I'm trying to create a universal solution that can be scaled up without having to add additional steps to campaign operations.
    – en-rep
    Dec 13, 2022 at 22:17
  • No unfortunately you cannot reference a list of DEs
    – zuzannamj
    Dec 14, 2022 at 7:11

0

You must log in to answer this question.