How would I cleanly pull data from a second Data Extension that doesn;t use subscriber ID?

Question

We're developing several automated sends that are going to use copy that's populated to certain criteria. The user data is indicated in the main sendable Data Extension, but based on a pre sale or post sale flag in Salesforce we will populate copy that exists in a separate Data Extension.

Example:

Person buys shoes and the color is red. Their purchase ID is tied to their Subscriber ID, but the SKU details are in a separate Data Extension. The Purchasing Data Extension contains only the Sub ID, the Purchase ID, purchase date and transaction details, email, and SKU. No details though of the product (as those live in the SKU DE)

How can I tie the SKU from that Data Extension into details for a send? I've seen a lot of examples of having data that's all tied to the Subscriber ID or Email but not to a different identifier that isn't system specific.

Answer 1

Let's think you have a table of ALL_Purchases in which you have all the purchases with the fields of SubscriberKey, Purchase IDs, SKU etc. We can see a SubscriberKey many times because a person can buy lots of things. Primary Key is Purchase ID.

Then, you would have SKU_Details table which has SKU, Product_name, etc. Primary Key is SKU.

So you would use Ampscript to pull the data like:

SET @row = Row(LookupRows("ALL_Purchases", 'PurchaseID', '435435'), 1) / this is the PurchaseID of the purchase
SET @sku = Field(@row, 'SKU')
SET @row = Row(LookupRows("SKU_Details", 'SKU', @sku), 1)
SET @productname = Field(@row, 'Product_name') / Product name is a field in the SKU_Details table