0

We have an email that deploys with 4 content tiles per customer. The four most relevant content tiles are selected per customer, based on the amount that customer has spent on different products.

We'd like to use Ampscript to look up a separate DE that contains the % amount a customer has spent per product, and then select the top 4 content tiles for that customer ranked from highest to lowest.

We've had a play with Adam's Look Up Ordered Rows query (Thanks Adam!), and we can use this to return an ordered list with every product, and the value that customer has spent.

We have two questions: 1) Can we amend the query to only look at specific products? Rather than extracting the whole list that includes every product, we'd like to be able to amend the code to only look at the products that have a featured content tile for that deployment. 2) Once we have the updated list that just includes the products that have content tiles available, how would we write the ampscript to populate the content blocks for the 1st, 2nd, 3rd and 4th highest ranked content tiles based on product sales?

Here is our code example that returns the ordered list with % sales for all products:

%%[
var @rows, @row, @rowCount, @numRowsToReturn, @uid, @i

set @uid = AttributeValue("SubscriberKey")
set @numRowsToReturn = 0 /* all */
set @rows = LookupOrderedRows("Sales By Product",@numRowsToReturn,"ProductScore desc, Productname","SubscriberKey", @uid)
set @rowCount = rowcount(@rows)

if @rowCount > 0 then

    for @i = 1 to @rowCount do

        var @resource_name, @ProductScore
        set @row = row(@rows,@i) 
        set @Productname = field(@row,"Productname")
        set @ProductScore = field(@row,"ProductScore")

        if @i == 1 then
          outputline(concat("<ul>"))
        endif

        ]%%

           <li style="border-bottom: 1px solid #ebeced;">
                %%=v(@Productname)=%%&nbsp;<span style="color:#5c656e;"><i><b>%%=v(@ProductScore)=%%</b></i></span>
           </li>

        %%[ 

        if @i == @rowcount then
          outputline(concat("</ul>"))
        endif


    next @i 

]%%

%%[ else ]%%

No rows found

%%[ endif ]%%
1

You can add as many pairs of criteria to the end of the lookupOrderedRows() function as you need.

For the highest ranked products part of your question, you'll still need to iterate through all of the products by subscriber, just not display all of them:

%%[
var @rows, @row, @rowCount, @numRows, @uid, @i, @ProductName0

set @uid = AttributeValue("SubscriberKey")
set @ProductName0 = AttributeValue("ProductName0")
set @numRows = 0 /* all */

if empty(@ProductName0) then 
    set @rows = LookupOrderedRows("Sales By Product",@numRows,"ProductScore desc, ProductName","SubscriberKey", @uid)
else
    set @rows = LookupOrderedRows("Sales By Product",@numRows,"ProductScore desc, ProductName","SubscriberKey", @uid, "productName", @ProductName0)
endif

set @rowCount = rowcount(@rows)

if @rowCount > 0 then

    for @i = 1 to @rowCount do

        var @ProductScore
        set @row = row(@rows,@i) 
        set @ProductName = field(@row,"ProductName")
        set @ProductScore = field(@row,"ProductScore")
        set @ProductScore = Add(@ProductScore,0) 

        if @i == 1 then
          outputline(concat("<ul>"))
        endif

        if @ProductScore >= 4 or not empty(@ProductName0) then
        ]%%

           <li style="border-bottom: 1px solid #ebeced;">
                %%=v(@ProductName)=%%&nbsp;<span style="color:#5c656e;"><i><b>%%=v(@ProductScore)=%%</b></i></span>
           </li>

        %%[ 
        endif

        if @i == @rowcount then
          outputline(concat("</ul>"))
        endif

    next @i 

]%%

%%[ else ]%%

No rows found

%%[ endif ]%%
  • Thanks so much for your response!! :) How does the SKU attribute work? I wasn't sure if I should be changing it to reference a product name? – Cbass May 8 '17 at 1:12
  • You can change it to whatever makes sense for your data. I just assumed you had a code for each product -- something separate from the product name. – Adam Spriggs May 8 '17 at 2:15
  • Oh right. We don't have a SKU code, so could I configure it to be based around ProductName? Or would I just get rid of it all together? – Cbass May 8 '17 at 2:25
  • Updated my answer. If you want to specify a productName, you'll need another variable. – Adam Spriggs May 8 '17 at 2:34
  • Thanks again for your help. So to specify a product name, can I type them in to the attribute value line? – Cbass May 8 '17 at 3:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.