AMPscript Course for Marketers - Data Extensions Functions (Lookup and LookupRows) - Chapter 4

Published by: Santiago Tabuenca on September 18, 2023
 Santiago Tabuenca

In the world of digital marketing, message personalization is key to engaging your audience and creating meaningful interactions. Salesforce Marketing Cloud is a platform that allows marketing professionals to create and send highly personalized messages,

and one of the essential tools to achieve this is AMPscript.


In this article, we will delve into how AMPscript combines with Data Extensions to personalize your marketing communications.

 Lookup and LookupRows funtions


The Importance of Data Extensions


In Salesforce Marketing Cloud, Data Extensions are flexible containers that store data that you can use to personalize your messages. You can think of them as spreadsheets, where each row represents a record and each column represents a data field. For example, you can have a Data Extension named "Customers" with columns like "Name," "EmailAddress," "LastPurchase," and more.


These Data Extensions can contain a wide variety of information about your customers and their interactions with your company.


But it doesn't end there; you can create a system of Data Extensions that represents a relational model.


For instance, consider an educational institution:

It could have a structure like:

  • Student 1
    • Courses enrolled in
      • Course 1
        • Subject 1
          • Grade 1
          • Grade 2
          • Grade 3
        • Subject 2
          • Grade 1
          • Grade 2
      • Course 2
        • Subject 1
          • Grade 1
          • Grade 2
          • Grade 3
        • Subject 2
          • Grade 1
          • Grade 2
  • Student 2
    • ....

Imagine all the personalization you can build with a data structure like this, or an equivalent one for customers with purchases, or a loyalty club.


This is one of the differentiating features of Marketing Cloud.



Data-Driven Personalization


The true magic of Data Extensions lies in how you can use the data stored in them to personalize your messages.


This is where AMPscript comes into play. Through AMPscript functions like Lookup, LookupRows, LookupOrderedRows, and LookupRowsCS, you can access data from your Data Extensions and use it to tailor your communications to each recipient.



The Lookup Function


The Lookup function is very useful when you need to retrieve a specific value from a Data Extension based on a particular criterion. For example, you can use Lookup to insert a customer's name into a welcome email.


Suppose you have a Data Extension called "Customers" with a "EmailAddress" column and a "Name" column.


The following AMPscript code demonstrates how to use Lookup:


/* Suppose you have the current customer's email address */
SET @EmailAddress = ""

/* Use Lookup to retrieve the customer's name */
SET @CustomerName = Lookup("Customers", "Name", "EmailAddress", @EmailAddress)

Hello, [@CustomerName]!

Welcome to our community.


The LookupRows Function


The LookupRows function allows you to retrieve multiple rows of data from a Data Extension based on specific criteria. Suppose you have a Data Extension called "Cart_Abandonment," where you store products that a user has abandoned in their shopping cart. With this code, you could automate a system to send them a reminder of their abandoned cart and a discount coupon to encourage them to complete their purchase.





SET @email = ""
SET @abandonedProducts = LookupRows("Cart_Abandonment", "Email", @email)
SET @discountCode = CONCAT("DISCOUNT_", Random(10000,99999))




<p>Abandoned products in your cart:</p>

<table border="1">





%%[IF RowCount(@abandonedProducts) > 0 THEN]%%

   %%[FOR @i = 1 TO RowCount(@abandonedProducts) DO]%%


      SET @product = Field(Row(@abandonedProducts, @i), "Product")

      SET @price = Field(Row(@abandonedProducts, @i), "Price")






   %%[NEXT @i]%%



      <td colspan="2">You have no abandoned products in your cart.</td>





<p>Complete your purchase now and use the discount code %%=v(@discountCode)=%% for a special offer!</p>





LookupOrderedRows and LookupRowsCS Functions


  • LookupOrderedRows: This function allows you to retrieve rows from a Data Extension in a specific order. For example, you can retrieve the 10 most recent subscribers based on the registration date.

  • LookupRowsCS: This variant of LookupRows is useful when you need to perform a case-sensitive row search, ensuring exact matches.




The combination of AMPscript and Data Extensions in Salesforce Marketing Cloud allows you to create highly personalized and effective marketing communications. These tools enable you to access specific data about your customers and use it to deliver messages that resonate with each recipient.


The possibilities are endless, from personalized welcome messages to product recommendations based on individual preferences. With a smart focus on personalization, you can enhance your customers' experience and make a greater impact on your marketing campaigns.



In this video, you can see this topic of functions explained in detail:




Download our ebook to support the online course here:


ebooks amscript for marketers


If you enjoyed this chapter, you may be interested in reading the previous chapters of the Ampscript Course for Marketers:


If you liked this article, share it!

Topic: SF Marketing Cloud