A sports e-commerce site adds a block to its monthly email showing the customer last three orders. The developer reads the orders table with LookupRows on the customer identifier, then displays the first, second and third row of the result. The three test profiles each have five orders, the preview looks perfect, QA signs it off.
On send day, two tickets arrive. The CRM team sees a gap between the targeted volume and the sent volume, with errors reported on part of the audience. These are the customers with only one or two orders, for whom the code asks for a third row that does not exist.
The second ticket comes from customer service. A loyal customer sees orders from 2021 in her email when she ordered the week before. Two functions were misunderstood, and test data that was too uniform hid both defects.
All of them take the data extension name, then one or more pairs made of a search column and a value being searched for. Several pairs combine as an AND. What separates them is what they return and what they promise.
| Function | Returns | Order | Case | Volume |
|---|---|---|---|---|
Lookup | One value | First match found | Sensitive according to the documentation | One value only |
LookupRows | A rowset | No guaranteed order | Insensitive | Up to 2,000 rows |
LookupRowsCS | A rowset | No guaranteed order | Sensitive | Up to 2,000 rows |
LookupOrderedRows | A sorted rowset | The sort you specify | Insensitive | The number requested, 2,000 at most |
LookupOrderedRowsCS | A sorted rowset | The sort you specify | Sensitive | The number requested, 2,000 at most |
Lookup(dataExt, colonneRetour, colonneRecherche1, valeur1 [, colonneRecherche2, valeur2 ])
LookupRows(dataExt, colonneRecherche1, valeur1 [, ])
LookupOrderedRows(dataExt, nbLignes, tri, colonneRecherche1, valeur1 [, ])The sort parameter is written like a SQL clause, a column name followed by a space and ASC or DESC, with several columns separated by commas. If nbLignes is below 1, the function returns every row up to the 2,000 limit. If nothing matches, it returns an empty rowset.
The documentation describes Lookup as returning the first value found when several rows match. First found means neither the most recent nor the oldest, it is any row among those that match. So this function belongs only on a search that identifies a single row, typically the primary key.
Correct usage takes two lines: read the field from the send data extension with AttributeValue, check that it is not empty, then search on the primary key of the reference table. Every lookup avoided for a customer who lacks the data is send time saved, and across a few hundred thousand recipients the difference shows in the job duration.
⚠️ The sneakiest case: reading the status of a customer order with Lookup on the customer identifier. The code appears to say the status of their order, and in fact returns the status of a random order among all of theirs. The result looks plausible, so nobody questions it.
The choice comes down to three questions, in this order.
Lookup fits. Several, and you need a function that returns a rowset.LookupOrderedRows with an explicit sort and the number of rows you want. If not, LookupRows does the job.CS variant. Otherwise the insensitive version tolerates data entry differences.On the case sensitivity of Lookup, the documentation and field reports do not always agree. Test the behaviour on your own account before you rest a business rule on it, rather than copying what a blog post claims.
Two further considerations follow. Volume first: if the need covers more than 2,000 rows or a calculated total, the lookup has no place in the email and belongs in SQL. Performance next: Salesforce states that these calls slow the send down and advises reading several values at once with LookupRows rather than chaining Lookup calls. If the email displays three columns from the same row, one rowset beats three calls.
A rowset is a collection of rows that does not display on its own. You count its rows with RowCount, extract a row with Row, then a value with Field. The first row sits at position 1, not 0.
%%[
VAR @clientId, @commandes, @nb
SET @clientId = AttributeValue("ClientId")
SET @commandes = LookupOrderedRows("Commandes", 3, "DateCommande DESC, NumCommande DESC", "ClientId", @clientId)
SET @nb = RowCount(@commandes)
]%%
%%[ IF @nb > 0 THEN ]%%
%%[
VAR @i, @ligne, @num, @date
FOR @i = 1 TO @nb DO
SET @ligne = Row(@commandes, @i)
SET @num = Field(@ligne, "NumCommande")
SET @date = Field(@ligne, "DateCommande")
]%%
<p>Commande %%=v(@num)=%% du %%=Format(@date, "dd/MM/yyyy", "Date")=%%</p>
%%[ NEXT @i ]%%
%%[ ENDIF ]%%The @nb variable holds 0, 1, 2 or 3, and it is what drives the loop, never the constant 3. The second sort criterion on the order number separates two orders placed on the same day and makes the result stable from one send to the next. Salesforce also recommends checking that a rowset holds data before walking it.
Each row is extracted once, then its fields are read from the variable. The third parameter of Field deserves a conscious decision: by default, a field name that does not exist raises an error, which is often the right choice. A typo in a column name should break the preview, not quietly produce an empty email. Passing false makes sense when the table structure legitimately varies.
This limit causes no trouble for a block showing three orders. It becomes a trap as soon as you want a counter.
Vous avez passé %%=RowCount(LookupRows("Commandes", "ClientId", _subscriberkey))=%% commandes.A trade account with 3,500 orders will never see more than 2,000 counted, and reading the whole history is paid for on every recipient. That kind of figure is calculated in SQL before the send and added as a column to the send data extension.
SELECT c.ClientId, COUNT(o.NumCommande) AS NbCommandes
FROM Clients AS c
LEFT JOIN Commandes AS o
ON o.ClientId = c.ClientId
GROUP BY c.ClientIdThe LEFT JOIN combined with a COUNT on a column of the orders table gives 0 to customers who have never ordered, where a count across all columns would give them 1. The email then does nothing but display a column, with no lookup during rendering.
💡 The test data that is always missing: a customer with no order, a customer with a single order, a customer with two orders dated the same day, a customer with no store attached. Those four rows take ten minutes to create and reveal most of the defects in this kind of block.
From a child business unit, Lookup, LookupRows and LookupOrderedRows accept the ENT. prefix in front of the data extension name, to read a table at enterprise level.
In Adobe Campaign, reading the database during personalisation is generally avoided. Additional data is added upstream, in the targeting or through a workflow enrichment activity, then read from the target data. A query inside a personalisation script is possible, but stays the exception you justify in code review.
Marketing Cloud makes reading during the send so easy that it becomes the default reflex, including where it has no business being. The Adobe Campaign habit remains the right one at volume: prepare the data beforehand, and keep the lookup functions for what cannot be prepared.
When the rank of a row carries meaning in your email, ask for the sort explicitly. When a loop walks a rowset, bound it with RowCount and never with the number of rows you hope for. Those two rules would have prevented both tickets in the incident, and they can be checked by reading the code, without opening the platform.
LookupRows returns a rowset with no guaranteed order. LookupOrderedRows returns a rowset sorted the way you specify, limited to the number of rows requested and to 2,000 rows at most. As soon as the rank of a row carries meaning in your email, you need LookupOrderedRows with an explicit sort, written like a SQL clause: a column name followed by ASC or DESC.
The documentation describes Lookup as returning the first value found. First found means neither the most recent nor the oldest: it is any row among those that match. So this function belongs only on a search that identifies a single row, typically the primary key. Reading an order status with Lookup on the customer identifier returns the status of a random order, with a plausible result that nobody questions.
A rowset does not display on its own: you count its rows with RowCount, extract a row with Row, then a value with Field. The first row sits at position 1, not 0. The loop is bounded by the RowCount value, never by the number of rows you hope for. Salesforce also recommends checking that a rowset holds data before walking it.
The lookup functions stop at 2,000 rows, so an account with 3,500 orders will never see more than 2,000 counted, and reading the whole history is paid for on every recipient. That kind of figure is calculated in SQL before the send and added as a column to the send data extension. A LEFT JOIN with a COUNT on a column of the orders table gives 0 to customers who have never ordered. The email then does nothing but display a column, with no lookup during rendering.
I can review your AMPscript blocks, the way you read your data extensions and your test data, then send you a written diagnosis with the corrections to apply.
Describe your context →