Adding Rows to Data Extensions

Data Extensions in the Salesforce Marketing Cloud platform are simply database tables in which you can store data you need for your campaigns.

You can add rows to data extensions using:

With the API, you have a few options — SOAP being the most complex, AMPScript the simplest:

You can use AMPScript in emails, landing pages or SMS keyword configurations. I’ll use AMPScript in this example.

Here’s how you add or update a single row in a data extension named My_Test_DataExtension using the UpsertDE() function:

Here’s a breakdown of the UpsertDE() arguments in this example:

  1. The name of the data extension to receive the data
  2. Number of columns you’re using to check to see if there’s a matching row already in the data extension. (This means there will be only 1 pair of name and value arguments following this parameter.  You could specify more.)
  3. The lookup column
  4. The lookup value
  5. Column 1 to update
  6. Column 1 update value
  7. Column 2 to update
  8. Column 2 update value

For further reading:
UpdateDE()
AMPScript Functions
UpsertData() (for SMS keywords)
UpdateData() (for SMS keywords)



Coupon Claiming in AMPScript

One cool thing you can do in the Salesforce Marketing Cloud is offer your subscribers a unique coupon code as part of a promotional email campaign. Here’s how to do it.

To start, create a “CouponCodes”Data Extension with the following columns:
coupon-claimrow2

Next, generate your own list of unique coupon codes in Excel. Include a “CouponCode” column header. Save the worksheet as CSV or Unicode (tab-delimited).
coupon-claimrow1

Then, import your coupons into the data extension with the adhoc import button:
coupon-claimrow3

Finally, add this AMPScript to your email. This code will try to find a coupon row for the specified email address in the CouponCodes data extension. If it finds one, it’ll return the coupon code. If not, it will “claim” an unclaimed code and associate it with the specified email address. The bit about the message context — that will prevent you from claiming coupon rows when you’re doing a send preview when building the email.

Keep these things in mind:

  • Make sure you upload enough coupons for your target audience so you don’t run out of unclaimed coupons. For recurring coupon campaigns, you should build a system to monitoring unused coupon codes.
  • Make sure the target of the coupon code (e-commerce site, POS system, etc) is using the same set of coupon codes as your campaign.
  • You can also do this in an SMS campaign.
  • The data extension name in the claimrow() function cannot be a variable
  • Any data extension referenced in a claimrow() function must exist

For further reading:

Data Extensions
Data Extension AMPScript Functions
MobileConnect Coupon Scenario
Live Offers

Update 5/28/2013: Added additional gotchas about claimrow() function
Update 2/9/2015: Added empty() check after claimrow() function
Update 3/9/2016: Move code snippet to gist, added variable declaration for emailaddr
Update 7/24/2017: Removed unnecessary lookup. Thanks, Brian (see comments below).

Prettying up AMPScript in emails

When you’re creating a dynamic email in Salesforce Marketing Cloud using AMPScript, things can get muddled up with non-HTML code real fast — so muddled it’s hard to recognize the basic layout of the email.  Sometimes it’s helpful to hide the AMPScript in a way that doesn’t affect the functionality. Here’s a trick that I use on a regular basis.

Hide your init block in with a styled DIV

I normally use these at the top of an email for initialization of variables, etc. Simply edit the content area to view the full code.

DO NOT use HTML comments to hide AMPScript. It will screw up the automatic text version of your email!

Checking lookupRow() results

Even though your email will pass validation in Salesforce Marketing Cloud, if you don’t check for positive lookupRows() results with rowcount() before using the row() function, then you may end up with a run-time error that aborts your send.

These kinds of errors are a pain to track down in the SFMC platform. Save yourself some debugging/support call time.

Don’t do this:

Do this:

AMPScript Lookup Examples

In Marketing Cloud, there are several ways to retrieve external data with AMPScript inside your email, landing page or SMS message. Here are few ways to go about it.

Suppose you have a Data Extension that contains these rows (and is not your sending audience):

LookupColumnDEColumn1DEColumn2
wheeNeoKeanu Reeves
wheeTrinityCarrie-Anne Moss
wheeMorpheusLaurence Fishburne
whoaAgent SmithHugo Weaving

Lookup value of single column value from a certain row

Output

DEColumn1 is Agent Smith

Lookup multiple column values from a single row

Output

DEColumn1 is Neo, DEColumn2 is Keanu Reeves

Lookup multiple column values from multiple ordered rows

Output

Row 1, DEColumn1 is Trinity, DEColumn2 is Carrie-Anne Moss
Row 2, DEColumn1 is Neo, DEColumn2 is Keanu Reeves
Row 3, DEColumn1 is Morpheus, DEColumn2 is Laurence Fishburne

For further reference:

Updates

  • 2017-10-19 – Added example Data Extension and output