Build a Data-Driven Email

Learning Objectives

After completing this unit, you’ll be able to:

Describe lookup functions.
Code an email that uses lookup and loop functions.
Follow best practices and troubleshoot errors.

Data Retrieval

With some practice, you can solve for even more advanced AMPscript use cases. Let’s review some concepts you need to complete our final NTO use case. AMPscript is used extensively to pull data from data extensions using either Lookup() or LookupRows(). These functions are helpful for when you need to grab information from a data extension that isn’t the one you’re using for sending.

First, it’s helpful to review the structure of a data extension and how data is found using AMPscript. Let’s look at a simple data extension that stores product information.

Data Extension Name: NTO Products

Name	Type	imageURL	Description	Price
Ironman 50-Lap Watch	Watch	https://s3-us-west-2.amazonaws.com/nto-products/products370/501400.jpg	The Ironman 30-Lap watch has triathlon-ready features.	$54.95
Leap Wireless Earbuds	Technology	https://s3-us-west-2.amazonaws.com/nto-products/products370/502100.jpg	With no wires to get in the way of your workout.	$99.95
Oregon 600t GPS	Watch	https://s3-us-west-2.amazonaws.com/nto-products/products370/503300.jpg	With preloaded 100K mapping and WAAS.	$480.00

Now that you’ve seen the data in the data extension, let's review some terms to be aware of when using AMPscript for data retrieval.

Row: A row is all the information stored in a database row, for example, all the information stored about the Ironman 50-Lap Watch, from its imageURL to price.

Field: A field is an attribute stored for all rows, so for example imageURL. This field can contain data or not (which is referred to as null).

Column: Column refers to the data stored in a specific column in a data extension. Description is the fourth column.

Rowset: A rowset contains one or more collections of data from rows. If you want to use data from multiple rows in your email, it’s a rowset.

RowCount: The RowCount function returns the number of rows in a rowset.

Ordered Rows: Ordered rows are used to return a specific number of rows in a rowset.

LookUp Functions

With those basic concepts in mind, let’s review a few specific functions. Click on the name of each function to see how to use it and example code with output.

Email performance optimization is an important consideration when using Lookup functions. Lookups are the most common use of AMPscript but also the most costly from a performance perspective, because they must reach out to the database to gather data for your email.

Process Loops

Another concept that is important when considering data retrieval is the FOR loop, or process loop. The loop structure allows code to be repeated until an end goal is reached, called an index expression. Let’s review the list of required elements of a process loop.

Opening FOR statement
Loop counter, defined as a variable such as @i
Starting index expression, like = 1
A direction keyword of either TO (to increase) or DOWNTO (to decrease)
An ending index expression (for example a data extension row set)
A DO keyword
Action to take
A NEXT keyword

This can be helpful when looking for multiple values in a data extension.

Example Code

FOR @i = 1 TO RowCount(@rs) DO
    SET @row = Row(@rs, @i)
    SET @productInterest = Field(@row, 'interest')
  IF [Interest] == @productInterest THEN
    SET @prodname = Field(@row,'name')
    SET @productdesc = Field(@row,'description')
    SET @productprice = Field(@row,'price')
    SET @productimage = Field(@row,'imageUrl')
  ENDIF
NEXT @i

To bring all these concepts together, let’s now review Michele’s final solution for NTO.

Solution 4: Build a Data-Driven Email

Isabelle wants to create a new monthly email campaign for loyalty members that have an interest in running or camping. Loyalty members who have an interest in one of these areas will receive an email promoting a new product that is listed as either running or camping. Here are the email content and requirements.

Email Content:

Greetings, FIRST NAME!

As a loyalty member who likes INTEREST, we want to give you a sneak peek into a new product you might be interested in.

Product Photo

Product Name

Product Description

$Price

(CTA): Shop Now!

Requirements:

Use variables from the NTO Customer DE with field names:

First_Name
Interest

Send an email only if the customer has an interest in running or hiking.
Display NTO Rockstar if a customer’s first name is null.
Display Our Products if a customer’s activity interest is null.
Use variables from the NTO New Products data extension with field names:

interest
Name
imageURL
Description
price

Display the matching new product photo, name, product description, and price in the email.

Try It Out

How would you code this email? What functions would you use? AMPscript is a flexible language, so there are multiple ways of coding emails. Click on each code sample to see three possible ways Michele could code this email for NTO.

Troubleshooting

Now that you’ve solved a few challenges using AMPscript, it’s a good time to review how to troubleshoot your work. After all, errors happen. Be prepared by reviewing these common missteps.

Nested functions are calculated from the inside out.
Commas are not permitted in numeric constants.
Alternative quote characters, such as smart quotes, are not permitted in string constants.
Be careful using null. An empty string (for example "") or a zero value (0) is not the same as null.
Review spacing. The system views spaces as two different attributes. For example, First Name would search for a data field named "First" instead of "First Name."
The lookup function only returns a single value. If the criteria used in the lookup function matches multiple records, the function returns the first found value, regardless if more values exist.
Certain functions are not intended for send time use with high volume. These include EncryptSymmetric, CreateSalesforceObject, and InsertDE functions.

In general, when you are troubleshooting your code, it’s important to understand the data you are expecting. What do you do if you expect data to be there and it isn’t? Output all your data in order to see what is missing and work backward from there.

Sum It Up

Let’s wrap up with some final guidelines.

When writing AMPscript code, use the Code Snippet Blocks in Content Builder when possible. Code snippets aren’t wrapped in HTML tables and therefore using them doesn’t add extra markup to your code.
When pulling URLs from data extensions or variables use RedirectTo.
Limit the number of Lookup functions to improve performance, especially if you have large data extensions with a lot of data.
Merge data into one sendable data extension before send to improve send performance.
Test with data identical to your live sendable data (but be careful to not accidentally send anything).
Surround variables in brackets even if they don't have spaces or special characters.
Use code comments to explain your code.

Ready to get back to coding emails? Go for it! And remember, learning a new language (like AMPscript) takes time. Start experimenting with beginner use cases and build up to advanced AMPscript skills.

Looking for Help?

Discover More