Getting the correct data extension rows in AMPScript

Question

Our subscriber row has many rows, two of which are SubscriberKey, and ModifiedDate

I have a data extension that contains info about what the subscriber purchased

it is a one to many relationship, meaning that a subscriber can have multiple products associated to it.

Here is an example

subscriberKey     name            dateModified
1231              joe             12/15/2001 

subscriberkey     productName     datePurchased
1231              coke            10/04/2001 
1231              pizza           10/04/2001 
1231              cake            12/15/2001 
1231              apple           12/15/2001 

What happens is when a customer of our checks out, we store the purchase history of that customer in a data extension, and triggerSend an Email. The Email that will be sent out, has an area that is dynamic in size, which is the list of items and their associated details.

What I am trying to understand is, how do I get the right rows using the AMPScript?

When the TriggerSend is activated, how do I get the data extension rows that are associated with that session?

I can use LookupRows() using the subscriberKey and date to get the rows that took place on lets say 12/15/2001. But there is a problem with that, if the same customer returns on the same date and makes a new purchase as follows:

subscriberkey     productName     datePurchased
1231              coke            10/04/2001 
1231              pizza           10/04/2001 
1231              cake            12/15/2001 <--- Order #1
1231              apple           12/15/2001 <--- Order #1

1231              pie             12/15/2001 <--- Order #2
1231              orange          12/15/2001 <--- Order #2

Then using the date restriction will not work. What I need is a means to grab the new rows I inserted into the date extension, so that when triggerSend is called, it knows which rows to look at.

How do I do this?

Answer 1

I don't think this is really an issue with how ExactTarget handles your data, I think it's a data issue. If you are only passing Subscriber Key and datePurchased, it would be difficult for any system of record to know which order is which.

I would suggest adding a field like orderID, potentially adding the time as well as the date, or some other data point that would allow you to be more specific when looking up your rows.

Add an attribute to your subscriber that would allow you to pass the orderID at send time, and you should be able to do this easily.