1

I have two different Data Extensions that I would like to merge together.

I have on one side the Subscribers data and on other side voucher code table.

I would like to assign to every customer automatically with a query a voucher code but I don't have any common field to merge it.

4
  • Do you want to include all the fields in two DE into one DE Apr 9, 2017 at 13:39
  • Hi @Josyula, thanks for replying. I would like to call only one field (email_address) from Subscribers and only (voucher_code) from the table voucher so I can run an AMP Script. Do you have any suggestions? 'Thanks a lot.
    – M.Giorgio
    Apr 9, 2017 at 13:52
  • My thoughts: Create another DE and pull the data from that two DE using queries and dump those fields data into the DE that you newly created. Did You try this?? Apr 9, 2017 at 14:01
  • Hi Josyula, I already tryed and it's not working because the datas are not on the same row: es. email xx@xx.xx and voucher code is empty and I have other rows with voucher_code xxxx and email empty
    – M.Giorgio
    Apr 9, 2017 at 14:22

1 Answer 1

1

If you're looking for a solution in AMPScript, the ClaimRow() AMPScript function is what you want.

Here's how I typically claim coupons for a subscriber:

%%[

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

I have a more details here on my blog: Coupon Claiming in AMPScript

UPDATE

If you wanted to just mach emails to voucher codes, you can generate a key to use in your JOIN between the two DEs. In this case, just assigning a row_number() for both.

select
s.emailaddress
, s.subscriberkey
, v.voucherCode
from (
    SELECT 
        [emailaddress]
        , [subscriberkey]
        , row_number() over (order by emailaddress) emailRanking
    FROM [testSubscribers] 
) s
inner join (
  select 
  voucherCode
  , row_number() over (order by voucherCode) voucherRanking
  from [testVouchers] 
) v on (v.voucherRanking = s.emailRanking)
9
  • Hi Adam, thank you for the answer. I already checked this topic before but here seems that I need to have the field "Email_address" inside the Coupon Data Extension in order to run the "Lookup".
    – M.Giorgio
    Apr 11, 2017 at 12:35
  • So? If you're doing an email send, you'll have it. Wouldn't you want to know what vouchers are assigned? Apr 11, 2017 at 12:43
  • Hi Adam, the problem that I have is not related to the coding but on data extension, as I mentioned before. I would like to push via Query datas from both tables in one (subscribers + voucher code) in order to asign automatically inside the new data extension the voucher to each user.
    – M.Giorgio
    Apr 11, 2017 at 13:56
  • For example I have those emails: abc@abc.com, def@abc.com, ghi@abc.com and another table with those coupons XX123, XX124, XX125. I would like to push those datas in a data extensions in order to asign the first VC to the first user imported.
    – M.Giorgio
    Apr 11, 2017 at 14:00
  • What's the reason for pre-assigning the vouchers? Apr 11, 2017 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.