HowToSFMC

HowToSFMC: 12 DAYS OF SCRIPTMAS

Like many of you, the holiday season is a favorite for all of How2's directors, admins and other community members! In that light, we are looking to share some of the 'gifts of script' we have accumulated over the years with you!

We will reveal a single script each day from Dec. 13th right up to Christmas Day (Dec. 25th) to provide you with the 12 DAYS OF SCRIPTMAS! We hope you enjoy all the 'gifts' we have been busy crafting these last few days to share with all you trailblazers and email-geeks as our gifts to you!

Happy Holidays and have a GREAT SCRIPTMAS!!

The First Day....A Debugging Process for SSJS

We are excited to share our first day with you all and for the beginning we are coming out strong with a debug process for SFMC SSJS:

<script runat='server'>
Platform.Load("core", "1.1.1");
/*
 @param { string } identify what you're writing
 @param { response } function/api response
 @param { debug } toggle if function should run or not


 debug = 1 will have output
 debug = 0 will hide output
*/
var debug = 1;

function deBug(string, response, debug) {
  if (response) {
    debug ? Write("<br><b>" + string + ":</b><br> " + Stringify(response) + "<br><br>") : null;
  } else {
    debug ? Write("<br><b>" + string + "</b><br> ") : null;
  }
} //End deBug

</script>

A special thanks to our own Tony Zupancic for the script.

The Second Day....A Trick To Dedupe with SQL

The second day is here! Are you excited?! We are! For today's script, we are sharing a quick SQL trick to help dedupe your results. Hope it helps you as much as it has helped us.

/* SQL dedupe using partition window and 
targeting first row match found */ 

SELECT x.SubscriberKey
, x.Email
FROM (SELECT a.SubscriberKey
  , a.Email
  , MAX(a.EventDate) AS lastOpenDate
  , row_number() Over(
        Partition By 
            a.SubscriberKey 

            Order By 
                a.Email
        ) row
  FROM DataExtension a
  WHERE ISNULL(a.EventDate,'') != ''
  Group By a.SubscriberKey
  , a.Email) x 
WHERE x.row = 1

A special thanks to our own Genna Matson for this script.

The Third Day....Magic of Dynamic AMPScript Variables

Day three! This is so exciting for us! Today, we are sharing a small AMPScript snippet that illustrates how to create dynamic variable creation/setting. The possibilities with this are near endless!

%%[
SET @deRows = LookupRows(...)

FOR @i = 1 TO RowCount(@deRows) DO
  SET @row = Row(@deRows,@i)
  SET @tempValue = Field(@Row,'Field')
  SET @FieldSet = TreatAsContent(CONCAT('%','%[SET @Field', @i, ' = @tempValue]%', '%'))
NEXT @i 
]%%
  

A special thanks to our own Greg (Gortonington) Gifford for this script.

The Fourth Day....Protection from SQL Timeouts

We are a third of the way through! It is so exciting....yet also sad that there are only 8 more days left. Today is a trick to help you break up your SQL queries that could time out into multiple queries to get around the 30 minute limit.

/* Avoid time outs by breaking large volume DEs down
by targeting the _customObjectKey attribute on the DE.

This attribute indexes at 0. 

The below example breaks the DE in two. 
First half of the DE -- Query 1: */ 

SELECT a.EmailAddress
, a.SubscriberKey
, a.Status
FROM DataExtension a
WHERE _customObjectKey % 1 = 0


/* Second half of the DE -- Query 1: */ 

SELECT a.EmailAddress
, a.SubscriberKey
, a.Status
FROM DataExtension a
WHERE _customObjectKey % 1 = 1

A special thanks to our own Genna Matson for this script.

The Fifth Day....A SSJS Timeout Script

FIVE. GOLDEN. RIii....I mean hello and welcome to the fifth day of Scriptmas!! We are excited to provide this wondersful SSJS function that can be used to make sure to elegantly end your SSJS scripts rather than have them timeout and error. Hope it helps to save you some hair and keep that blood pressure low!

<script runat=server>
Platform.Load("Core","1.1.1");
​
var dev = 1;
​
var now = new Date();
var start = now.getTime();
var timeOut = 1500000; //25 minutes
//60000 milliseconds in a minute
​
​
if (dev) {
  timeOut = 10000;
}
​
​
​
do {
​

​//Your Code Here
​
} while((new Date().getTime() - start) < timeOut)
​
</script>

A special thanks to our own Greg (Gortonington) Gifford for this script.

The Sixth Day....A Sendable DE Summary

OHHHHH...WE'RE HALFWAY THEERRREE!!! OHHHH OHH AMPSCRIPT CUZ WE CARE! Ahem. Sorry couldn't resist some Bon Jovi. Today is our halfway point to the end! It has been a WONDERFUL journey and we want to thank everyone for coming along with us so far. Our elves have been cooking up quite a few wonderful things that we are excited to share over the next few days, so make sure you keep checking back every day!

<h1>
  Sendable Data Extension - Summary
</h1>
<table>
<thead>
  <tr>
    <th>DE Name</th>
    <th>Description</th>
    <th>Rowcount</th>
  </tr>
</thead>
%%[
SET @GetSendableDEs = CreateObject('RetrieveRequest')
SetObjectProperty(@GetSendableDEs, "ObjectType", "DataExtension")
SET @SimpleFilter = CreateObject("SimpleFilterPart")
SetObjectProperty(@SimpleFilter, "Property", "IsSendable")
SetObjectProperty(@SimpleFilter, "SimpleOperator", "Equals")
AddObjectArrayItem(@SimpleFilter, "Value", "True")
AddObjectArrayItem(@GetSendableDEs,"Properties","Name")
AddObjectArrayItem(@GetSendableDEs,"Properties","Description")
SetObjectProperty(@GetSendableDEs, "Filter", @SimpleFilter)
SET @DERows = InvokeRetrieve(@GetSendableDEs,@status,@reqID) 
SET @DECount = ROWCOUNT(@DERows)

IF @DECount > 0 THEN
FOR @Loop = 1 to @DECount DO

SET @DERow = ROW(@DERows,@Loop)
SET @DEName = FIELD(@DERow,'Name')
SET @DEKey = FIELD(@DERow,'CustomerKey')
SET @DEDesc = FIELD(@DERow,'Description')
SET @DERowcount = DataExtensionRowCount(@DEName)
]%%
  <tr>
    <td>%%=V(@DEName)=%%</td>
    <td>%%=V(@DEDesc)=%%</td>
    <td>%%=V(@DERowcount)=%%</td>
  </tr>
%%[ NEXT @Loop ELSE ENDIF ]%%
</table>

A special thanks to our own Jason Cort for this script.

The Seventh Day....A Function to use Console Log

We know how much console.log is important to JS developers and this script helps to fill that big hole that is left behind when running SSJS. We hope this helps to bring you a happy holiday season and a very merry Scriptmas!

<script runat=server>
var json = ["one","two","three"]
consoleLog(json);

function consoleLog(value) {
   if (typeof value === 'object' && value !== null) {
      value = Platform.Function.Stringify(value);
   }

   Platform.Response.Write('<script>console.log(' + value + ');</script>');
}
</script>

A special thanks to our own Shibu Abraham for this script.

The Eighth Day....Time Zones in SFMC SQL

Who else is excited for the holidays? We have had a ton of fun so far and are very excited for the next few days! We hope these scripts have been useful or at least helped in some way or another. This script helps tons with those that need to use Time Zones in SQL.

/* Get around some timezone challenges with the AT TIME ZONE 'function' within your SQL Queries */
select
eventdate AT TIME ZONE 'Central America Standard Time' AT TIME ZONE 'UTC' AS 'eventdate'
,SubscriberKey
,jobID
from
_Sent**

A special thanks to our own Jason Cort for this script.

The Ninth Day....Lookups for AMPScript

Hello again and happy SCRIPTMas! Our elves cooked up something special today sharing examples on how to utilize the LookupRows and LookupOrderedRows AMPScript functions!

%%[ /* AMPscript Lookups */

/* Lookup Rows */
set @lookupVariable = 'rando'
set @lookupRows = LookupRows('DataExtension','Field',@lookupVariable)
set @countRows = RowCount(@lookupRows)
if @countRows > 0 then
  set @row = Row(@lookupRows,1)
  set @field = Field(@row,'Field')
endif


/* Lookup Ordered Rows -- returns 5 ordered rows */
set @lookupVariable = 'rando'
set @lookupOrderedRows = LookupOrderedRows('DataExtension',5,'Field asc','variable',@lookupVariable)
set @countOrderedRows = RowCount(@lookupOrderedRows)
if @countOrderedRows > 0 then
  set @row = Row(@lookupOrderedRows,1)
  set @field = Field(@row,'Field')
endif

]%%

A special thanks to our own Genna Matson for this script.

The Tenth Day....A SSJS API Shell

Holy Cow! Our own Mr. Tony Zups provides us with a powerhouse of a script - a SSJS function to run your API calls via a config file. These next few days are ramping up to be great ones!

<script runat='server'>
Platform.Load("core", "1.1.1");
var accessToken = getAccessToken();//get accessToken

var getJourneyConfig = {
    endpoint: "interaction/v1/interactions/"
    method: "GET"
}


function scriptUtil(config, accessToken) {

 var method = config.method;
 var url = "restBase" + config.endpoint;

 try {
  var req = new Script.Util.HttpRequest(url);
  req.emptyContentHandling = 0;
  req.retries = 2;
  req.continueOnError = true;
  req.contentType = "application/json"
  req.method = method;
  req.setHeader("Authorization", "Bearer " + accessToken);

  if (config.payload) {
   req.postData = Stringify(config.payload);
  }

  var resp = req.send();

  var resultStr = String(resp.content);
  var resultJSON = Platform.Function.ParseJSON(String(resp.content));

  return resultJSON;

 } catch (e) {

  Write("API (e)" + Stringify(e));
  return
 };
}
</script>

A special thanks to our own Tony Zupancic for the script.

The Eleventh Day....SQL Subqueries

One more day! Today we are sharing a wonderful script showing SQL subqueries and how to use them. They are instrumental in most complex SQL queries. With scripts like these last few...what could be the final one? Come back tomorrow and see!

/* Subqueries are a great way to extend your SQL capabilities and they come in many formats.

Eg: Using a Subquery to create a reference dataset from a single location to fFind every subscriber who has opened any email.*/

Select
Subscriberkey
from
_Subscribers
Where
SubscriberKey in (select Subscriberkey from _Open)

/* You can create Subqueries within your Subqueries to get a single output for a specific set of requirements.
Eg: Using a nested Subquery to finding all Subscribers who have Opened an email of a specific Email Name format  */

Select
Subscriberkey
from
_Subscribers
where
Subscriberkey in (select Subscriberkey from _Open
                  where
                  JobID in (Select JobId from _Job
                            where emailname like '%PromoCampaign%')
                  )
                  
/* You can use Subqueries if you need to calculate or derive something to use within your main query, it can save you needing to maintain an additional query and you can join your subquery in to your original query

Level 3 - Finding how many times a person has opened and the first time someone opened an email of the PromoCampaign type with one query rather than 3 */

select
sub.SubscriberKey
,tot.totalopens
,fir.firstopen
from
_subscribers sub
join (select subscriberkey, count(*) as 'TotalOpens' from _Open
      where
      JobID in (select JobId from _Job
                where emailname like '%PromoCampaign%')
      group by subscriberkey ) tot on sub.subscriberkey = tot.subscriberkey
join (select subscriberkey, min(eventdate) as 'FirstOpen' from _open
      where
      Jobid in (select JobId from _Job
                where emailname like '%PromoCampaign%')
     group by subscriberkey ) fir on sub.subscriberkey = fir.subscriberkey

A special thanks to our own Jason Cort for this script.

The Twelfth Day....A DE Field Retrieve Script

We did it! Today's script is a SSJS script to output a list of all the field names inside of a data extension. This can be super helpful in multiple ways, from using in a DE inventory or using it to output fields for a SQL query.

<script runat=server>
Platform.Load("Core","1");  
var dataExtensionFields = DataExtension.Init('myDE').Fields.Retrieve()
if (dataExtensionFields.length > 0) {
    for (var i = 0; i < dataExtensionFields.length; i++) {
        Write('<br>'+dataExtensionFields[i].Name);
    }
}
</script>

A special thanks to our own Jacob Edwards for this script.

Thank you everyone for reading! If you would like to contribute or be a part of HowToSFMC, please reach out to us using our contact form or on our Slack channel.