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)=%% <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 ]%%