0

We are using an API Entry Event that populates information into a Data extension called Purchase to trigger a first purchase journey. The Purchase DE contains just the basic purchase information and we have another data extension called ShoppingCart that has the details of their cart (this is updated at the same time but does not go through the entry event in journey builder). The two data extensions are connected in contact builder by the field 'orderId' that they both share.

We want to personalize some information in the actual email send that is in the 'ShoppingCart' data extension - is this possible with AMPscript Lookup? From what I can tell it should be able to lookup the 'orderId' in the journey data with the 'orderId' in the ShoppingCart data extension.

Is this true? And if so can someone point me in the direction of where to get stared.

Improve this question

1 Answer 1

Reset to default
1

Correct, it's a fairly basic usecase that you're trying to implement.

If you are trying to retrieve just one specific value, say Product Name, you can use just LookUp function with the following script:

%%[
var @CustomerOrder, @Product
set @CustomerOrder = AttributeValue("OrderId") 
set @Product = LookUp("ShoppingCart","Product Name","OrderId", @CustomerOrder)
]%%

This way you can use the variable %%=v(@Product)=%% in your Email.

In case you want to retrieve multiple values like Product Name and Product Quantity, it's a bit trickier, as you will need to utilize LookUpRows instead.

%%[
var @rows, @row, @rowCount, @i
set @CustomerOrder = AttributeValue("OrderId") 
set @rows = LookupRows("ShoppingCart","OrderId", @CustomerOrder)
set @rowCount = rowcount(@rows)

IF @rowCount > 0 then
FOR @i = 1 to @rowCount DO

    var @Product, @ProductQuantity
    set @row = row(@rows, @i)
    set @Product = field(@row,"Product Name")
    set @ProductQuantity = field(@row,"Product Quantity")
ENDIF
]%%

This way you can grab as many attributes as necessary from your ShoppingCart DE and use them as personalization strings the same way, like %%=v(@ProductQuantity)=%%

In case you want to retrieve multiple Products for the same OrderID, do it like this:

%%[
    var @rows, @row, @rowCount, @i
    set @CustomerOrder = AttributeValue("OrderId") 
    set @rows = LookupRows("ShoppingCart","OrderId", @CustomerOrder)
    set @rowCount = rowcount(@rows)

    IF @rowCount > 0 then
    FOR @i = 1 to @rowCount DO

        var @Product, @ProductQuantity
        set @row = row(@rows, @i)
        set @Product = field(@row,"Product Name")
        set @ProductQuantity = field(@row,"Product Quantity")
]%%

Product Name: %%=v(@Product)=%%
Product Quantity: %%=v(@ProductQuantity )=%%

%%[ NEXT @i ]%%

%%[ ELSE ]%%

No Products Found 

%%[ ENDIF ]%%
Improve this answer
3
  • What if one orderId has multiple different Products? For example I want to pull the two Product Names from the same orderId in the ShoppingCart DE but both Product Names have unique orderItemId values - is this still possible?
    – Hailee Marcinek
    Commented Mar 10, 2020 at 20:47
  • That is also possible yes, with the same LookUpRows function, then you just need to loop over the results. I will update the code in a moment.
    – Rain
    Commented Mar 10, 2020 at 20:49
  • @Rain very neat and useful explanation of the standard approach to this!
    – Stephan de Paly
    Commented Mar 11, 2020 at 15:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .