AMPscript to Order Content based on Scores within the database

Question

I'm trying to write AMPscript that will arrange content in an email based on a user's score across a number of variables. The higher the score, the higher the positioning within the email.

For example, the categories/columns could be:

Baseball - Basketball - Soccer - Tennis - Hockey - Golf

And each user would have a value in the column per sport.

I'd like to re-order stored content so that the highest-valued sport is presented first in the email, and then the next highest and next highest and so forth. There may be situations where there is no content available for the sport, in which case I'd like to move to the next highest score or insert content in a default way.

Some more data as background:
article1 = baseball
article2 = baseball
article3 = basketball
article4 = soccer
article5 = soccer
article6 = tennis
article7 = hockey
article8 = baseball
article9= golf
                                  Baseball -- Basketball -- Soccer -- Tennis -- Hockey -- Golf
[email protected]         35              0               15             0             20         15
[email protected]         2              40               55             0             12         3
[email protected]          55             0                0               0            40         45

Using the example above,
JOHN DOE's Email would have:
ARTICLE 1, 2, 8, 7, 4, 5, 9, 3, 6

JANE DOE's Email would have:
ARTICLE 4, 5, 3, 7, 9, 1, 2, 6, 8, 10

JACK DOE's Email would have:
ARTICLE 1, 2, 8, 9, 7, 3, 4, 5, 6, 10


Complicated? Yes! Doable? Should be!
Show a little AMPscript love!

Answer 1

I agree with Timothy that it would be better to do your segmentation and order manipulation in QueryActivities before the send time, but this approach is possible if you have your articles sitting somewhere in DE.

Even further based on whether or not there is only a limited number of permutations for order (say if you only have 6-10 sports covered) you can pre-generate all of those permutations and then retrieve these for the subscriber based on their preference set.

In AMPScript you can iterate over the preferences of the user, concatenating the sport preference in the "right" order, e.g. in your case for John you'd end up with Baseball|Basketball|Soccer|Tennis|Hockey|Golf string. You'd then split this string with BuildRowSetFromString (see reference for the function) and outputting articles based on the order of the user preferences.

Would be great to know how you actually produce articles (e.g. do they come from a specific content area, data extension or you build them on the fly) as it would help to suggest best performing outcome.

Answer 2

My hunch is that your data is too complicated to just use AMPscript to manipulate. Do all your segmenting with QueryActivities into a new sendable data extension, and send to that.