This section provides conceptual information about data extensions and data relationships. Please review the links below for additional information on creating and using data extensions:

This document is intended for application users who want to use data extensions to satisfy the need for flexible data storage. Using data extensions may require understanding of relational database structures and other technical expertise.

You can import data into data extensions with the application interface. You can create, update, and delete rows in a data extension with the SOAP API. In the application you can export data from a data extension using a data extension extract activity.

What Is a Data Extension

A data extension is a table within the application database that contains your data. Usually, the data you keep in a data extension relates to a subscriber but does not fit in the subscriber profile and preference attributes for one of the following reasons:

  • A subscriber can have varying numbers of values. For example, a subscriber's first name fits as a subscriber attribute because you will probably refer to a subscriber by only one first name. On the other hand, a list of the subscriber's transactions in the last month might not fit well because it is difficult to predict how many transactions a subscriber will have completed in the last month.

/Global/Documentation/6508/1-data_extension.png

  • The information is related to the subscriber through a third piece of information. For example, you maintain the subscriber's preferred airport code as a subscriber attribute, but maintain the name of the airport in a data extension.

/Global/Documentation/6508/2-data_extensions.png

After you create a data extension to contain your external data, you create data relationships to relate your data extensions to each other and to your subscribers. Data extensions must relate to subscribers, either directly or through another data extension, to be useful to include in messages.

Creating a Data Extension

You can create a data extension on the Subscribers tab in the ExactTarget application. When you create a data extension, you provide the following information for each field:

  • Name
  • Data type
  • Field length
  • Whether the field is included in the primary key of the table
  • Whether the field is required to have a value

In some cases, such as when you use a data extension as part of a  live offers solution, you must use a data extension template to ensure that all of the required fields are included in the data extension. A limited number of data extension templates are provided with the application. When you use a data extension template, you must include the specified fields, and you can also choose to add additional fields.

If your account is integrated with a Salesforce account, you can set up your import activity to create and populate a data extension with the information in a Salesforce Object or Report.

You can set a default value for a field when creating a data extension. Default values are validated to ensure that they satisfy the restrictions of the field. For example, you cannot have a default value for a primary key field.

NOTE: You cannot include the following characters in a data extension name or field name:
  • \
  • /
  • "
  • :
  • *
  • ?
  • |
  • &
  • ""

Using Data Extensions for Sending and Test Sending

You specify whether the data extension is used for sending during the creation process or on the properties page for existing data extension. See How to Create a Data Extension for steps on how to create a data extension to be used for sending. In order for a data extension to be used for sending the Used for Sending must be checked. Additionally, it must meet one of the following criteria:

  • Contains an email address field type
  • Is linked to the subscriber table via the Subscriber ID
  • Is linked to the subscriber table via the Subscriber Key

A data extension you use for sending should include only one email address column as a best practice. This allows for predictable behavior when sending to the data extension. Sendable data extensions in the top-level account of an Enterprise account must also contain a field (with a Number data type) named ChannelMemberID. That field must include the member ID (MID) of the On Your Behalf account to which that subscriber belongs. 

You can use sendable data extensions to exclude subscribers from a send definition. You can also select the sendable data extension as a source for a dynamic subject line when you create an email. Any subscriber attributes in the data extension will then be available in the personalization drop-down menu when you create the subject line for your email. You can also use the name of the field in your personalization strings. For example, if your sendable data extension has a field named FavoriteAlbum, you can use the personalization string %%FavoriteAlbum%% in your email.

If the data extension is used for sending, you can specify whether it is used for test sending. If you identify a data extension as being used for test sending, it is available to be selected when you click the Test Send button on an email message.

You can also use a data extension for sending SMS messages, which does not require an email address but does require a telephone number (entered in a field with the Phone data type).

Populating a Data Extension

You must create a data extension before you can populate it with data. You can use the import activity or the import wizard to populate your data extension. If you set up an import activity to create a data extension from the schema of a Salesforce Object or Report, the import activity also populates the data extension with the data.

After you have populated a data extension, you can include the data in your messages with AMPscript functions. For example, you could summarize the customer's transactions for the month in an email.

Viewing the Data in a Data Extension

You can view the data in a data extension, as well as a count of the records. From the Data Extension workspace, select the checkbox next to the desired data extension and click the View Data button.

Sending to a Data Extension

If you use the Subscriber Key to identify your subscribers, the application deduplicates your data extension list based on the Subscriber Key value instead of the email address.

If you can send to a data extension, you can also use the send preview feature with the data extension. The send preview lets you see what the personalization and dynamic content will look like for an individual message recipient.

When you send an email to a data extension, the system uses the List Detective tool to scan the email addresses in the data extension. The List Detective protects your deliverability by preventing the sending of email to bad addresses. The bad email addresses identified by the List Detective are not sent to, so the sends do not appear in your tracking. The email addresses in the data extension are not removed or changed by List Detective.

Using a Triggered Send Source Data Extension Template

You can use a data extension to gather subscriber information on subscribers who trigger a triggered send. When you make a triggering API call, you add a row to the data extension for each recipient within the call. The triggering API call must include all required fields of the data extension that do not have a default. The triggering API call may also include subscriber attributes that, depending on configuration, update the subscriber data. Data extensions that collect triggering subscriber information must be built using a Triggered Send Source Data Extension template.

Using Exclusion Scripts and Data Extensions

You can enter an exclusion script to apply to the triggered send. (This functionality is available regardless of whether the send source is a data extension.) You can also specify an exclusion list and a domain exclusion list. Domain exclusion lists are data extensions built with a Domain Exclusion Data Extension template.

Using Dynamic Content with Data Extensions

If you use dynamic content in an email content area, you can use data extensions to populate your content field.  Select the name of the data extension when you create the dynamic content, and that extension determines the column names you can choose when you create the criteria for the dynamic content.  The data extension's fields take the place of the attributes taken from the subscriber attribute list.

Using Numeric Identity Columns

Every data extension has a numeric identity column named _CustomObjectKey. You can access this identity column using a substitution string or AMPscript.

The following examples demonstrate how to retrieve data extension content using  _CustomObjectKey:

  1. Retrieving the _CustomObjectKey on a sendable data extension.
    CustomObjectKey: %%_CustomObjectKey%%
  2. Retrieving the _CustomObjectKey after inserting into a row into data extension.
    This functionality is only usable in a landing page because an email inserts rows after the email is completely built.
    /* Here is a code sample that only uses one */
    /* ID column to first set a random number, then overwrite that number with the */
    /* _CustomObjectKey that we store at the data extension level. */
    
    %%[Var @rows, @row, @key, @insert, @custkey
    Set @key = 12345
    Set @rows = LookupRows("InsertTest","ID",@key)
    
    if RowCount(@rows) == 0 then
        Set @key = Concat(Random("100","100000"))
        Set @insert = InsertData("InsertTest","ID",@key)
    
        if @insert == 1 then
            Set @rows = LookupRows("InsertTest","ID",@key)
            Set @row = Row(@rows,1)
           
            if not Empty(Field(@row,"ID")) then
                 Set @custkey = Field(@row,"_CustomObjectKey")
                 Set @insert =
    UpdateData("InsertTest",1,"ID",@key,"ID",@custkey)
                 if @insert == 1 then ]%%
                    ClientID: %%= v(@custkey) =%%<br />
                %%[ endif
            endif
        endif ]%%
    %%[ else ]%%
    Rows were found
    %%[ endif ]%% 

Using Retention Policy Support for Triggered Sends

If you use a TriggeredSendDataExtension with a triggered send, the data extension will support automatic data retention policies. When defining a triggered send with a data extension source, you specify how long to retain the data. The default is six months. You may select a retention period ranging from one day to forever. The system deletes the data at the end of the retention period. Subscriber data is not removed by the data retention policy; only the data extension data is removed.

You may delete data from the data extension, as well as import the data back in using AMPscript.

Using Data Retention Policy for a Data Extension

If you have this feature enabled, the data admin user for your ExactTarget account can enable or change the data retention policy for new data extensions. By default, the data extension retention policy deletes unused data extensions after 6 months. The deletion process runs nightly. You must set these options when creating the data extension, and you cannot modify these settings for an existing data extension.

For more granular control over the data retention period, you may select the Use Object Based Retention checkbox to set the retention period, a fixed date on which the data rows or the data extension is deleted, and other options.

Data Retention Options

  • Use Object-Based Retention - Checking this box ensures that the application deletes all records in the data extension at the same time.
  • Retention Period - Enter the number of days after data extension creation to wait before deleting data.
  • Use Fixed Date - Check this box and specify the date to delete data extension information after that date.
  • Delete at End of Retention Period - Check this box to retain the data extension but delete the records inside that data extension.
  • Reset Retention Period on Import - Check this box to extend the retention date following a new import to the data extension.

Use the chart below to understand how these options affect the data in your data extension:

Use Object-Based RetentionDelete at End of Retention PeriodResults
Not selected Not selected Unused data extensions deleted by default after 6 months
Selected Not selected Both the data extension and data are deleted at the time specified by the retention period settings
Selected Selected The data extension remains, but the data itself is deleted at the time specified by the retention period settings

Contact your account representative if you would like to activate the data retention feature.

Obscuring Data for Triggered Sends

This feature obscures any information stored in ExactTarget (other than the subscriber key) that would personally identify a subscriber. This includes email address and subscriber name. ExactTarget encrypts the information that comes into our system, and ExactTarget has the only key. Data is encrypted and decrypted by ExactTarget. For future data reconciliation purposes, you are encouraged to keep a copy of all your subscriber records if you use this feature.

Contact your account representative if you would like to activate the data obscuring feature.

Using Send Logging

Contact your ExactTarget account representative to enable the send logging feature.

You may want to be able to have subscriber or send-specific attributes returned with tracking data.  You do not want this data to be overwritten by future campaigns, as are subscriber attributes or data extension fields.

Send logging also could be accomplished by persisting the desired data elements to the Always On database.  This would increase the complexity of the lookup process, however, and would also increase storage costs, because the period of time the data must be available to support data extracts is significantly less than the time the data is needed to serve up links.

Our solution is to allow you to log sends, including specified subscriber attributes, data extension fields, and calculated values, to a data extension.  You then can use this data extension in queries, filters, and extracts.

To enable send logging, you first define a data extension to hold the log.  You do this by creating a data extension from the Send Logging Data Extension template.  That data extension will be used for logging for all sends.  You can create only one data extension from the Send Logging Data Extension template.  You will be able to suppress send logging at a send definition level.

The following rules are used to auto-populate log field values:

  1. If the send is to a data extension and that data extension has a field with the same name as the log field, the data extension field value will be used to populate the log field.
  2. If there is a subscriber attribute with the same name as the log field, the subscriber attribute value will be used to populate the log field.
  3. If there is a variable defined within the email with the same name as the log field (except for the @ prefix), the variable value will be used.
  4. Required fields without a value will be set to an appropriate empty default for the data type (text will default to '', numbers to 0, Booleans to false, and dates to current date).  Non-required fields will receive a null value.  A notification will be sent once per job when this occurs to the notification email address of the owner of the send.
  5. In the event of data type mismatches, required log fields will be set as described in step 4.  Non-required fields will receive a null value.  A notification will be sent once per job when this occurs to the notification email address of the owner of the send.
  6. Any errors that occur as a result of send logging should be logged to the error log and result in a notification email being sent to the owner of the job.
  7. The system will log both successful and unsuccessful sends to allow you to retrieve your metadata around failed sends.

Why Use a Data Extension

Use a data extension to contain your data that relates to subscribers but that doesn't fit into the subscriber attributes. With data extensions, you can:

  • Use information from other systems in your ExactTarget application.
  • Send a message to subscribers in a data extension.
  • Exclude subscribers in a data extension from a message send.
  • Include customer-specific information from a data extension in a message.
  • Collect the subscriber information of people who trigger a triggered send.
Was This Page Helpful?
Last updated by bhobbs at 11:58, 18 Feb 2014