Fun With Salesforce Marketing Cloud: Deliverability by Domain (Part 2)

In this article you will learn how to create email content that will show the last six months of deliverability statistics for each domain that you send mail to from Marketing Cloud. It utilizes the data created in part 1 of this tutorial where I explained how you can create an SQL Query and Data Extension to gather these statistics using Marketing Cloud's system data views.

If you have read and/or implemented the code from the previous article, note that I've updated the SQL slightly. Please see the updated article for Part 1 with the extra required field and updated SQL Query. These changes were made because in order to look up ordered rows with AMPScript we'll need a field to match on. By matching on a date it not only allows you to ensure that you're pulling data from the day it was queried, but also leaves a date stamp in the data extension as to when the data was last queried.

This article runs long because I go into more depth explaining the necessary AMPScript, so any HTML elements will be bare-bones for simplicity's sake.

Completed Code:

%%[
   SET @GetDate = FormatDate(Now(),'yyyy-MM-dd',,'en-US')
   SET @DeliverabilityRowset = LookupOrderedRows('Deliverability By Domain',100,'Total Send Desc','ReportDate',@GetDate)
   SET @RowCountDeliverabilityRowset = ROWCOUNT(@DeliverabilityRowset)
   SET @TableColor = 'b4d977'VAR @Domain,@Deliverability,@TotalSend,@TotalBounce
]%%

<table width="100%">
   <tr>
      <td>Domain</p></td>
      <td>Deliverability</td>
      <td>Total Send</td>
      <td>Total Bounce</td>
   </tr>

%%[
   FOR @i = 1 to @RowCountDeliverabilityRowset do 
   SET @DeliverabilityRow = ROW(@DeliverabilityRowset, @i)
   SET @Domain = Field(@DeliverabilityRow,'Domain')
   SET @Deliverability = Field(@DeliverabilityRow,'Deliverability')
   SET @TotalSend = Field(@DeliverabilityRow,'Total Send')
   SET @TotalBounce = Field(@DeliverabilityRow,'Total Bounce')
   IF @Deliverability >= 98 
      THEN SET @TableColor = 'b4d977'
   ELSEIF @Deliverability >= 95 AND @Deliverability < 98THEN SET @TableColor = 'DACD89'
   ELSEIF @Deliverability < 95THEN SET @TableColor = 'DA9189'
   ENDIF
]%%

  <tr>
    <td style="background-color:#%%=v(@TableColor)=%%">%%=v(@Domain)=%%</td>
    <td style="background-color:#%%=v(@TableColor)=%%">%%=v(@Deliverability)=%%</td>
    <td style="background-color:#%%=v(@TableColor)=%%">%%=v(@TotalSend)=%%</td>             
    <td style="background-color:#%%=v(@TableColor)=%%">%%=v(@TotalBounce)=%%</td>
  </tr>

%%[NEXT @i]%%

</table>

Tutorial

Below I will break down the AMPScript line by line, and explain how each element works.

Section 1: Set Variables & Run Lookup

%%[
/* 1 */SET @GetDate = FormatDate(Now(),'yyyy-MM-dd',,'en-US')
/* 2 */SET @DeliverabilityRowset = LookupOrderedRows('Deliverability By Domain',100,'Total Send Desc','ReportDate',@GetDate)
/* 3 */SET @RowCountDeliverabilityRowset = ROWCOUNT(@DeliverabilityRowset)
/* 4 */SET @TableColor = 'eaf4d8'
/* 5 */VAR @Domain,@Deliverability,@TotalSend,@TotalBounce
]%%

The AMPScript above does the following things:

• (1) Sets a variable for today's date in a yyyy-MM-dd format.
• (2) Retrieves our row set of the Data Extension we made in the last article (Deliverability By Domain) and sorts them in descending order by the quantity of sends. I do this so I can order my results by the domains that receive the most mail, but you can certainly change this sort to any other field such as # of bounces or total deliverabiliy. Please refer to Salesforce's documentation on how to best use the LookupOrderedRows function. To limit the results I'm going to include in my email, I'm only going to gather the first 100 records. To make it easy I'm calling this rowset @DeliverabilityRowset. A row set is an array of data stored in memory that we can access as long as we know which row to access.
• (3) We get the total number of rows in @DeliverabilityRowset retrieved from line 2. If it's less than 100 this will make sure we get the right number of rows which we'll need to reference in our FOR loop. This variable will be called @RowCountDeliverabilityRowset.
• (4) We set the default color for our table cells (Green) as a variable called @TableColor.
• (5) We declare the remaining variables used in the next section's FOR loop.

Section 2: Build a basic Table

<!--06--> <table width="100%">
<!--07-->            <tr>
<!--08-->              <td>Domain</p></td>
<!--09-->              <td>Deliverability</td>
<!--10-->              <td>Total Send</td>
<!--11-->              <td>Total Bounce</td>
<!--12-->            </tr>

The above HTML creates a basic table and and a header row that contains the information we'd like to display in our report.

Section 3: Create a FOR Loop and iterate through each row of our data

<!--13-->  %%[
/* 14 */   FOR @i = 1 to @RowCountDeliverabilityRowset do 
/* 15 */   SET @DeliverabilityRow = ROW(@DeliverabilityRowset, @i)
/* 16 */   SET @Domain = Field(@DeliverabilityRow,'Domain')
/* 17 */   SET @Deliverability = Field(@DeliverabilityRow,'Deliverability')
/* 18 */   SET @TotalSend = Field(@DeliverabilityRow,'Total Send')
/* 19 */   SET @TotalBounce = Field(@DeliverabilityRow,'Total Bounce')

• (13) We begin our ampscript. Commented lines within this section will look different to accommodate the code change.
• (14) Begin our FOR statement. @i is a variable that will keep track of each row we're going to access. We start it at 1 to pull the first row of data and we'll stop iterating once we reach the total row count of our row set in @DeliverabilityRowset. DO tells Marketing Cloud that we're done with the basic logic of our FOR loop, and are ready to start doing things!
• (15) We declare the active row of our Rowset by using the ROW function. It needs two inputs, the row set, and the row number we're accessing which in this case our variable @i. We'll be able to reference these fields now that we've set our active row.
• (16-19) Here's where we get real data from our rows! The FIELD function retrieves the field of a given ROW with a matching field name. We're getting the Domain, Deliverability, Total Bounce, and Total Sent here.

Section 4: Set Conditional Formatting for Row Data

/* 20 */   IF @Deliverability >= 98 
/* 21 */   THEN SET @TableColor = 'eaf4d8'
/* 22 */   ELSEIF @Deliverability >= 95 AND @Deliverability < 98
/* 23 */   THEN SET @TableColor = 'DACD89'
/* 24 */   ELSEIF @Deliverability < 95
/* 25 */   THEN SET @TableColor = 'DA9189'
/* 26 */   ENDIF
/* 27 */   ]%%

• (20-26) This part allows us to set some conditional formatting for our table cells to really call out deliverability issues if we see them. If the @Deliverability score is 98% or greater, we mark it green, if it's between 95 and 98, it's yellow, and if it's below 95, we're going to mark it red, then close our IF statement. For a basic documentation on how to create an IF statement, please check the Salesforce Ampscript 201 Documentation page.
• (27) We close our AMPScript, so we can use some HTML next!

<!--28-->     <tr>
<!--29-->       <td style="background-color:#%%=v(@TableColor)=%%">%%=v(@Domain)=%%</td>
<!--30-->       <td style="background-color:#%%=v(@TableColor)=%%">%%=v(@Deliverability)=%%</td>
<!--31-->       <td style="background-color:#%%=v(@TableColor)=%%">%%=v(@TotalSend)=%%</td>             
<!--32-->       <td style="background-color:#%%=v(@TableColor)=%%">%%=v(@TotalBounce)=%%</td>
<!--33-->     </tr>
<!--34-->   %%[NEXT @i]%%
<!--35-->   </table>

Section 5: Create HTML that uses our FIELD data.

• (28-33) Here we use HTML to build a table row with our row data. In each <td> we style the background to be one of the values we set in lines 15-22 and then use the variables we set in lines 11-14.
• (34) The NEXT @i command increases the value of @i by 1 and then loops back up to the top of the FOR statement. Once @i hits our total count of rows, the NEXT command will stop and we can get on with our lives, starting with line 30.
• (35) We close the table - that's it for the code!

Section 6: Preview & Automate

Now that our code is complete, we can preview it and make sure that everything is looking ready to send. I have added a bit more styling and table headers in order to help this email get properly identified by its audience. It's pretty bare-bones since it's an internal report. If you are having any problems at this stage, please feel free to leave a comment with whatever error you're seeing. I do test my code before I publish it but I'm far from perfect. Here's a screenshot of the table I've created, with the awesome conditional formatting:Try it yourself

Next step: Automate!

In my next article I'll show you how to use Automation Studio to send this report monthly to your stakeholders.