How do you send multiple emails to one person via Triggered Sends?

Question

I'm testing sending transactional email through the Exact Target API and the Fuel SDK for PHP. I've successfully sent a test email but I don't appear to be able to send a second email to the same address as I get the following error from the API

[ErrorCode] => TriggeredSendSubscriberProcessingError
[ErrorDescription] => Error Code: 21 - Cannot insert duplicate row into data extension for subscriber 21.

The code used is fairly similar to the Fuel SDK example code

$sendTrigger = new ET_TriggeredSend();
$sendTrigger->props = array('CustomerKey' => 'test');
$sendTrigger->authStub = $myclient;
$sendTrigger->subscribers = array(array(
    "EmailAddress"  => "[email protected]",
    "SubscriberKey" => "[email protected]",
    "Attributes" => array(
        array('Name' => 'MemberEmailAddress', 'Value' => '[email protected]'),
        array('Name' => 'MemberFirstName',    'Value' => 'Firstname'),
        array('Name' => 'MemberLastName',     'Value' => 'Lastname'),
        array('Name' => 'ActivationLink',     'Value' => 'http://www.example.com.au/test-verify')
    )
));

$sendResult = $sendTrigger->send();

The data extension has the following fields

SubscriberKey Text 100
EmailAddress EmailAddress 100
MemberEmailAddress EmailAddress 100 Primary Key
MemberFirstName Text 255
MemberLastName Text 255
ActivationLink Text 512

The data extension appears to require me to set a primary key that is not one of the inherited template fields, SubscriberKey and EmailAddress. I suspect the current primary key MemberEmailAddress is what is causing the error when subsequent calls to the API are trying to insert extra records into the DE and clashing with previous records.

Should I just be using a numeric 'id' field or similar for the primary key instead?

Answer 1

You really should not have primary keys on a triggered send data extension. The purpose of it is intended to be more like a send log, which also shouldn't have primary keys. The DE provides a mechanism for passing data from the API call into the resulting email while also functioning as a log for those send-time values.

If you are forcing unique values in your trigger call by passing yet another column as part of a compound key then it really isn't accomplishing anything and will most likely cause you problems in the future. You cannot perform an update on the rows via the trigger so you may as well not have any keys assigned. Also, MemberEmailAddress should already be represented in the EmailAddress column. If there is another identifier needed that is typically stored in the SubscriberKey column but that is a whole different topic that needs careful evaluation as it can have an impact on how the entire account is managed.

If you need a master table that you run updates against you can have a separate process that extracts what you need from the triggered send DE and populates an independent one that would have no impact on the production trigger's operation.

If archiving the current triggered send definition and building a new one isn't an option, you are better off pausing your trigger, adding new columns with temporary names to the DE that represent those currently making up the PK, running a query to copy those values over to the new columns, deleting the original columns and then renaming the new column names to the original value. It's a bit of a process but it's doable. As you found, once you set the DE on the definition you cannot change it. Just be sure to save the DE after deleting the original columns and renaming the new ones. Also, back up the data in the DE before doing anything.

Just some advice from someone who has been through all these scenarios before.

Answer 2

You are correct, MemberEmailAddress is causing the issue. Once that address is in the DE, you won't be able to send to it again.

You need to use a primary that will be unique for each send. An ID column would work; you could also use a composite key consisting of MemberEmailAddress + something else, like %%jobid%% etc.

Good luck!