Page last modified 12:20, 16 Mar 2010 by Admin

Documentation Wiki > Solution Guides > Remarketing Solution Guide

Remarketing Solution Guide

1. About this Guide
1. 1. 1.1.1. Prerequisites
  2. 1.1.2. Audience
  3. 1.1.3. Remarketing Solution
2. 1.2. What is Remarketing
3. 1.3. Why Use a Remarketing Solution
2. Scenario
3. Procedures
4. Appendix A: Database Table Layouts
1. 4.1. _Job
2. 4.2. _Subscribers
3. 4.3. _Open
4. 4.4. _Click
5. 4.5. _Unsubscribe
6. 4.6. _Bounce
7. 4.7. _SurveyResponse
8. 4.8. _FTAF
5. Appendix B: Sample Queries
1. 5.1. Get subscribers who clicked on a send in the last 2 hours

About this Guide

This document provides conceptual, scenario, and procedural information about how to use ExactTarget features to create a landing page where subscribers receive a gift card as an incentive for completing a process, such as making a purchase or completing a survey.

Prerequisites

Audience

This document is intended for ExactTarget users responsible for creating incentive programs to reward subscriber behavior. Basic knowledge of SQL is required to create a gift card solution according to this guide.

Remarketing Solution

Subscriber behavior is the best indication of the subscriber's engagement. Subscribers who open and click your emails demonstrate that they are receptive to your messages. With the remarketing solution, you can create lists of subscribers based on their behavior when interacting with your messages so that you can better target your messages to these engaged subscribers.

What is Remarketing

Remarketing is the process of sending follow-up marketing email messages to a certain subset of customers based on their interaction with previous emails messages.

The ExactTarget application database contains the job and behavioral information you use to identify the subset of customers to target with your remarketing campaign. To access this data, you use a query activity to retrieve the information from the ExactTarget database and place it in a data extension.

You can query the database for the following job-related information:

Job - The email sends from your account
Subscribers - The subscribers in your account and their statuses. Subscriber attributes are not available at this time.

You can query the database for the following behavior-related information

Open - The email opens for your account
Click - The link clicks for emails from your account
Unsubscribe - The unsubscribes from emails lists in your account
Bounce - The bounce data for emails sent from your account
SurveyResponse - The responses to survey sent from your account
FTAF - The information related to email message from your account being forwarded to a friend.

All data view names are case-sensitive. You can retrieve up to six months' of data from all of the tables except _Jobs, which contains all data for the account. See Appendix A for the table layout for each data view you can query. See Appendix B for sample queries.

When you create the query activity, you select the data extension to receive the subscriber list. After you run the query, you can send your message to the data extension.

Why Use a Remarketing Solution

Use a remarketing solution to accomplish tasks such as:

Sending emails to engaged customers, as identified by recently clicking a link in an email.
Extracting subscriber behavior data to support ROI calculations to justify your email marketing efforts.

Scenario

A weekly newsletter goes to subscribers on Monday, and on Thursday, a marketer at Northern Trail Outfitters sends a follow-up email to subscribers who clicked links in the newsletter. The marketer decides to automate this process using the remarketing solution available in the ExactTarget product.

First, the marketer creates a data extension to contain the subscribers who clicked.

The data extension is marked as being used for sending because the system will use this data extension as the target of the query activity and the email send later in the process.

The data extension contains only one field to contain the SubscriberKey of the subscribers returned from the query. If the data extension also contains other fields, the query activity must also populate those fields.

Next, the marketer creates a query activity to retrieve the list of engaged subscribers to use to populate the data extension. In this scenario, the marketer is defining engaged subscribers as those who have clicked a link in the weekly newsletter email.

The query activity selects unique subscriber keys from the _Click data view where:

The ListID equals the list ID of the Monday newsletter AND
The EventDate of the click is Monday or after. Because the follow-up message is sent on Thursday, the EventDate must be greater than the current date minus three. The date calculation is done using the DateAdd function.

The marketer specifies the Engaged Newsletter Subscribers data extension from the previous step as the target for the query. This tells the system to put the results of the query into the data extension to be sent to later.

The marketer creates a user-initiated message interaction to send the message to the data extension after the query activity populates it. The message interaction specifies the email message content created with the ExactTarget tools (not covered in this scenario) and the data extension as the target audience.

Finally, the marketer creates a program to schedule the query activity and message interaction to recur on a weekly basis. The query activity appears as a separate step in the program from the email send, as opposed as a task in the same step, because the query activity must complete before the send can occur successfully.

The marketer scheduled the program to recur at 8 a.m. on Thursdays. The newsletter is sent at 8 a.m. on Mondays, and scheduling the follow-up message program to run at the same time of day as the newsletter is sent allows the query activity to successfully pick up all of the engaged subscribers.

When the marketer starts the program, the system automatically creates the list and sends the message every Thursday at 8 a.m.

Procedures

Click on the corresponding link to be directed to the appropriate page.

Data Extensions
How to Create a Data Extension

Activities
How to Create a Query Activity
How to Create an Import Activity

Email
How to Create a User-initiated Email Interaction

Programs
Programs How to Create a Program
How to Schedule a Program
How to Start a Program

Microsites and Landing Pages
How to Create a Microsite (Content)
How to Create a Microsite (Interaction)
How to Start a Microsite Interaction
How to Create a Landing Page

Appendix A: Database Table Layouts

The following layouts show the information available in each table that you can query using the query activity.

_Job

Name	Data Type	Nullable
JobID	int
EmailID	int	X
AccountID	int	X
AccountUserID	int	X
FromName	nvarchar(130)	X
FromEmail	varchar(100)	X
SchedTime	smalldatetime	X
PickupTime	smalldatetime	X
DeliveredTime	smalldatetime	X
EventID	varchar(50)	X
IsMultipart	bit
JobType	varchar(50)	X
JobStatus	varchar(50)	X
ModifiedBy	int	X
ModifiedDate	datetime	X
EmailName	char(100)	X
EmailSubject	nchar(200)	X
IsWrapped	bit
TestEmailAddr	varchar(128)	X
Category	varchar(100)
BccEmail	varchar(100)	X
OriginalSchedTime	smalldatetime	X
CreatedDate	smalldatetime
CharacterSet	varchar(30)	X
IPAddress	varchar(50)	X
SalesForceTotalSubscriberCount	int
SalesForceErrorSubscriberCount	int
Send Type	varchar(128)
DynamicEmailSubject	ntext	X
SuppressTracking	bit
SendClassificationType	nvarchar(32)	X
SendClassification	nvarchar(36)
ResolveLinksWithCurrentData	bit
EmailSendDefinition	nvarchar(36)
DeduplicateByEmail	bit

_Subscribers

Name	Data Type	Nullable
SubscriberID	bigint
DataUndeliverable	smalldatetime	X
DateJoined	smalldatetime	X
DateUnsubscribed	smalldatetime	X
Domain	varchar(250)	X
EmailAdress	varchar(100)
BounceCount	smallint
SubscriberKey	varchar(100)
SubscriberType	varchar(100)
Status	tinyint	X
Locale	varchar(5)	X

_Open

Name	Data Type	Nullable
AccountID	int
OYBAccountID	int	X
JobID	int
ListID	int
BatchID	int
SubscriberID	int
SubscriberKey	varchar(100)
EventDate	datetime
Domain	varchar(128)
IsUnique	bool

_Click

Name	Data Type	Nullable
AccountID	int
OYBAccountID	int	X
JobID	bigint
ListID	int
BatchID	bigint
SubscriberID	int
SubscriberKey	varchar(100)
EventDate	datetime
Domain	varchar(128)
URL	varcahr(900)	X
LinkName	varchar(1024)	X
ContentLink	nvarchar(max)	X
IsUnique	bool

_Unsubscribe

Name	Data Type	Nullable
AccountID	int
OYBAccountID	int	X
JobID	int
ListID	int
BatchID	int
SubscriberID	int
SubscriberKey	varchar(100)
EventDate	datetime
IsUnique	bit
Domain	varchar(128)

_Bounce

Name	Data Type	Nullable
AccountID	int
OYBAccountID	int	X
JobID	int
ListID	int
BatchID	int
SubscriberID	int
SubscriberKey	varchar(100)
EventDate	datetime
IsUnique	bit
Domain	varchar(128)
BounceCategoryID	smallint
BounceCategory	nvarchar(50)	X
BounceCategoryID	smallint	X
BounceSubcategory	nvarchar(50)	X
BounceTypeID	smallint
BounceType	nvarchar(50)	X
SMTPBounceReason	nvarchar(max)	X
SMTPMessage	nvarchar(max)	X
SMTPCode	smallint	X

_SurveyResponse

Name	Data Type	Nullable
AccountID	int
OYBAccountID	int	X
JobID	int
ListID	int
BatchID	int
SubscriberID	int
SubscriberKey	varchar(100)
EventDate	datetime
IsUnique	bit
Domain	varchar(128)
SurveyId	int
SurveyName	varchar(100)
IsUnique	int
QuestionID	int
QuestionName	varchar(50)
Question	varchar(8000)
AnserID	int
AnswerName	varchar(8000)	X
Answer	varchar(8000)	X
AnswerData	nvarchar(max)	X

_FTAF

Name	Data Type	Nullable
AccountID	int
OYBAccountID	int	X
JobID	int
ListID	int
BatchID	int
SubscriberID	int
SubscriberKey	varchar(100)
EventDate	datetime
Domain	varchar(128)
IsUnique	int

Appendix B: Sample Queries

This section contains sample SQL for one common use case for the query activity and the remarketing solution. More sample queries are in development and will be included in this document in the near future.