How to update a data extension after each recipient send

Question

How do you all deal with vouchers? Ideally we’d like to just create a single Data Extension (DE) with two columns for this:

| voucherCode| used |

| abc | 1 |
| def | 0 |
| ghi | 0 |
| jkl | 0 |
| mno | 0 |

We’ve managed to loop through this DE and display the first voucherCode that has not been used yet to a customer. Unfortunately, we’ve got two issues:

1. The same (first unused) voucherCode is displayed to each customer
2. We can’t figure out how to update the DE after each email to a customer as opposed to after each email send, which is what is happening now

Examples:

What we have now, using a scenario with 3 customers receiving an email:

1. Customer 1, receives ghi
2. Customer 2, receives ghi
3. Customer 3, receives ghi
4. Data extension is updated and used is toggled to “1” for vouchercode “ghi”

What we are trying to achieve:

1. Customer 1, receives ghi
2. Data extension is updated and used is toggled to “1” for vouchercode “ghi”
3. Customer 2, receives jkl
4. Data extension is updated and used is toggled to “1” for vouchercode “jkl”
5. Customer 3, receives mno
6. Data extension is updated and used is toggled to “1” for vouchercode “mno”

Answer 1

I found the answer to my question here: http://sprignaturemoves.com/coupon-claiming-in-ampscript/

%%[

var @em, @couponRow, @couponCode, @rows, @row

/* include your sendable attribute/column here */ 
set @em = AttributeValue("emailAddr") 

set @rows = LookupRows("couponCodes","EmailAddress",@em)

if rowcount(@rows) > 0 then
  set @row = Row(@rows,1)
  set @couponCode = field(@row,"couponCode")
else

    if _messagecontext == "PREVIEW" then
           set @couponCode = "XX TEST XX"
    else

      set @couponRow = ClaimRow("couponCodes","IsClaimed","EmailAddress",@em) 

      if empty(@couponRow) then
         /* You can do other error handling here if you want.*/
         /* This aborts the send */
         raiseError("no more coupons available")
      else 
         set @couponCode = field(@couponRow,"couponCode") 
      endif

    endif 

endif

]%%

Here's your coupon code: %%=v(@couponCode)=%%

Answer 2

It sound to me that you have two data extensions. One contains your sending audience and the other contains your inventory of vouchers. Is that correct?

Have you looked into using the ClaimRow() ampscript function? This will require addition custom ampscript in your email, but should meet your needs. It supports updating a boolean flag for indicated claimed vouchers (as long as the field is name isClaimed). It will also allow you to capture many more details like JobId, EmailAddress, & ClaimDate