Using Tracking Extracts

What are Tracking Extracts

You can use tracking extracts to export granular data regarding several different aspects of email send jobs (such as clicks, bounces, and survey data) from the ExactTarget application and import that information into another program or system.

Why Use Tracking Extracts

Tracking extracts retrieve data before sends are executed, meaning that the data includes information on any subscriber that would be listed as undeliverable or excluded by List Detective. Tracking extracts can also be scheduled via a program. Tracking extracts also include column heading information.

How to Create a Tracking Extract Via the Application

Follow the instructions on creating a data extract activity and select Tracking Extract from the Extract Type dropdown menu. Complete the following fields depending on the data you want to be included in your tracking extract:

• Specify the range of time you want to include in the data extension extract:
  • Rolling Range - Select the Rolling Range radio button and set the dropdown menu to 1, 7, 30, 60, or 90 days, depending on the time range you want to include every time you run the data extension extract.
  • Specific Range - Select the Specific Range radio button and enter the start and end dates in the calendar fields under the radio button. You can select up to 30 days in your range.
• AccountIDs: Enter the accounts from which you want to extract tracking information.
  • A blank field extracts information from the account running the extract.
  • An asterisk extracts information from all accounts and subaccounts in an Enterprise account.
  • A comma-delimited list of account ID numbers extracts information from only those accounts inside an Enterprise account.
• Attributes: Enter the accounts for which you want to include the Attributes file. Use of this section depends on whether ExtractAttributes is enabled for your account.
  • A blank field returns no attributes.
  • A comma-delimited list of attribute names returns attribute files for those accounts. If the attribute does not exist for an account or subaccount, this action returns no attributes.
• EmailSendDefinitionExternalKey: Enter an email send definition external key to filter data from a specific send.
• Output Checkboxes: Click the checkbox for each tracking extract output you wish to view
  • Attributes
  • Bounces
  • ClickImpressions
  • Clicks
  • Conversions
  • ListMembershipChanges
  • Lists
  • NotSent
  • Opens
  • SendJobImpressions
  • SendJobs
  • Sent
  • SentImpression
  • StatusChanges
  • Subscribers
  • SurveyResponses
  • Unsubs
• ExtractRange: Enter the date range from which you wish to create the tracking extract.
  • A blank field uses the date range associated with the tracking extract.
  • "Previous Month" overrides any other information and creates the tracking extract from the previous month's data.
• Format: Enter the format in which you want the tracking extract to be created.
  • "csv" returns a comma-delimited file. This is the default option.
  • "tab" returns a tab-delimited file.
  • "xml" returns an XML file. This option is not supported for all output types. Check the documentation for your chosen output to see if XML is a valid option.

    Do not use txt to indicate a tab-delimited file. Although .txt is the file extension for a tab-delimited file, entering txt as the format will cause an error.

• IncludeAllListMembers: Click this checkbox to include all list members in the tracking extract. Leaving this box unchecked means the extract only includes list members with activity during the specified period of time.
• IncludeAllSubscribers: Click this checkbox to include all subscribers in the Subscribers file of the tracking extract. Leaving this box unchecked means the extract only includes subscribers with activity (clicks, opens, unsubscribes, sent, or bounced) during the specified period of time.
• IncludeInferredOpens: Click this checkbox to include all inferred opens (clicks on an email without a recorded open). Leaving this box unchecked means the extract only includes specific opens where the email was clicked and rendered.
• QuoteText: Click this checkbox to surround all text with double quotation marks. Existing double quotes in the tracking extract are replaced by two single quotation marks.
• SendIDs: Enter a comma-delimited list of SendIDs from which to filter the tracking extract. If you specify the external key of an email send definition in the extract as well, the extract will include information from all SendIDs and the email send definition.
• TextQualifier: Enter a character to use as a text qualifier. The default value is a double quotation mark.
• UnicodeOutput: Check this box to save the extract in UTF-16 format instead of ASCII.

Click Save to finish your tracking extract. Once you've finished, start your activity to get the results.

How to Create Automated Tracking Extracts with Programs

Follow these steps to automate the creation of a tracking extract using Programs.

1. Create your tracking extract as outlined previously on this page.
   

   Note: File Naming Pattern needs to be a zip file (filename.zip) to contain the multiple files in the extract.

2. Create your program and add your tracking extract as a step in the process, followed by a File Transfer step. (Add additional steps as you wish.)
3. Create a schedule to make your program run on a regularlly scheduled basis.

Depending on how you've set up your program, your tracking extract updates its information periodically.

How to Create a Tracking Extract via the SOAP API

Using the SOAP API like the example listed below, you can create your tracking extract via an API call. Please refer to the Extract method for more information on executing this API call.

public DataTable ByMonth(DateTime startDate, DateTime endDate, string notificationEmail)
        {
            int cntr = 0;

            DataTable resultTable = new DataTable();
            resultTable.Columns.Add(new DataColumn("FileName"));
            resultTable.Columns.Add(new DataColumn("Details"));

            DataRow dr = null;

            ExtractRequest request = new ExtractRequest();
            ExtractRequest[] requests = new ExtractRequest[1];
            List<ExtractParameter> extractParameters = new List<ExtractParameter>();
            ExtractParameter extractParam = null;
            ExtractResult[] responses;

            // first, we need to get the age as a TimeSpan
            TimeSpan tsEnd = startDate - (new DateTime(1, 1, 1));
            // then we can subtract that TimeSpan from the current date to return a DateTime object
            // note that subtracting a DateTime from a DateTime returns a TimeSpan object, which is
            // the reason for the first step above (we need a DateTime to get the years without
            // having to go through some ugly calculation)
            DateTime difference = endDate.Subtract(tsEnd);
            // we'll need to subtract a year to account for that fact that we were forced to use the
            // year 1 above in the new DateTime statement (throws an exception if you use 0)

            int numMonths = difference.Month + (difference.AddYears(-1).Year * 12);

            for (int i = 0; i < numMonths; i++)
            {
                request = new ExtractRequest();
                requests = new ExtractRequest[1];

                extractParameters = new List<ExtractParameter>();

                request.ID = "c7219016-a7f0-4c72-8657-1ec12c28a0db";  // This is a constant value used to identify the desired extract.  This is required.
                extractParam = new ExtractParameter();

                extractParam.Name = "StartDate";
                extractParam.Value = new DateTime(startDate.AddMonths(i).Year, startDate.AddMonths(i).Month, startDate.AddMonths(i).Day).ToString();  // In this example, we will grab 5 days worth of data ending now.
                extractParameters.Add(extractParam);

                DateTime endDateTime = new DateTime(startDate.AddMonths(i + 1).Year, startDate.AddMonths(i + 1).Month, startDate.AddMonths(i + 1).Day);

                if (endDateTime > DateTime.Now)
                {
                    endDateTime = new DateTime(startDate.AddMonths(i).Year, startDate.AddMonths(i).Month, DateTime.Now.AddDays(-1).Day);
                }

                extractParam = new ExtractParameter();
                extractParam.Name = "EndDate";
                extractParam.Value = endDateTime.ToString(); 
                extractParameters.Add(extractParam);

                extractParam = new ExtractParameter();
                extractParam.Name = "ExtractOpens";
                extractParam.Value = "true";   // Get opens, set to false to not return opens
                extractParameters.Add(extractParam);

                extractParam = new ExtractParameter();
                extractParam.Name = "ExtractClicks";
                extractParam.Value = "true"; // Get clicks, set to false to not return clicks\
                extractParameters.Add(extractParam);

                extractParam = new ExtractParameter();
                extractParam.Name = "ExtractSent";
                extractParam.Value = "true"; // Get sent events, set to false to not return sent envents
                extractParameters.Add(extractParam);

                extractParam = new ExtractParameter();
                extractParam.Name = "ExtractUnsubs";
                extractParam.Value = "true";  // Get unsubscribes, set to false to not return unsubscribes.
                extractParameters.Add(extractParam);

                extractParam = new ExtractParameter();
                extractParam.Name = "ExtractConversions";
                extractParam.Value = "false";  // Get conversion events, set to false to not return conversions.
                extractParameters.Add(extractParam);

                extractParam = new ExtractParameter();
                extractParam.Name = "ExtractSurveyResponses";
                extractParam.Value = "false";  // Get survey respones, set to false to not return survey respones.
                extractParameters.Add(extractParam);

                extractParam = new ExtractParameter();
                extractParam.Name = "ExtractBounces";
                extractParam.Value = "true";  // Get bounces, set to false to not reutrn bounces.
                extractParameters.Add(extractParam);

                extractParam = new ExtractParameter();
                extractParam.Name = "ExtractSubscribers";
                extractParam.Value = "true";  // Get subscribers, set to false to not return subscribers.
                extractParameters.Add(extractParam);

                extractParam = new ExtractParameter();
                extractParam.Name = "ExtractSends";
                extractParam.Value = "true";  // Get sends, set to false to not return sends
                extractParameters.Add(extractParam);

                extractParam = new ExtractParameter();
                extractParam.Name = "QuoteText";
                extractParam.Value = "true";  // Quote the text
                extractParameters.Add(extractParam);
                //Specify FileTransferLocation only if an external FTP site is used, other EFTP used automatically.
                //extractParam = new ExtractParameter();
                //extractParam.Name = "FileTransferLocation";
                //extractParam.Value = "ExactTarget FTP Export";
                //extractParameters.Add(extractParam);

                extractParam = new ExtractParameter();
                extractParam.Name = "OutputFileName";
                extractParam.Value = String.Format("tracking_{0}-{1}-{2}_to_{3}-{4}-{5}.zip", startDate.AddMonths(i).Year.ToString(), startDate.AddMonths(i).Month.ToString(), startDate.AddMonths(i).Day.ToString(), startDate.AddMonths(i + 1).Year.ToString(), startDate.AddMonths(i + 1).Month.ToString(), startDate.AddMonths(i + 1).Day.ToString());  // the name of the output file you want generated.
                extractParameters.Add(extractParam);


                //            extractParam = new ExtractParameter();
                //          extractParam.Name = "SendIDs";
                //        extractParam.Value = "13332311,13269052,13277798,13278152,13209321"; // Used to filter results to only theses sends
                //      extractParameters.Add(extractParam);


                //extractParam = new ExtractParameter();
                //extractParam.Name = "SendIDs";
                //extractParam.Value = send.ID.ToString();
                //extractParameters.Add(extractParam);

                extractParam = new ExtractParameter();
                extractParam.Name = "NotificationEmail";
                extractParam.Value = notificationEmail;
                extractParameters.Add(extractParam);

                request.Parameters = extractParameters.ToArray();
                requests[0] = request;

                overallResult = integrationFramework.Extract(requests, out requestID, out responses);

                // Log results
                dr = resultTable.NewRow();
                dr["FileName"] = String.Format("tracking_{0}-{1}-{2}_to_{3}-{4}-{5}.zip", startDate.AddMonths(i).Year.ToString(), startDate.AddMonths(i).Month.ToString(), startDate.AddMonths(i).Day.ToString(), startDate.AddMonths(i + 1).Year.ToString(), startDate.AddMonths(i + 1).Month.ToString(), startDate.AddMonths(i + 1).Day.ToString());
                dr["Details"] = String.Format("{0} - [{1}] {2}", overallResult, responses[0].ErrorCode, responses[0].ErrorCode);
                resultTable.Rows.Add(dr);
            }
            return resultTable;
        }

How Much Data is Contained in a Tracking Extract?

The application returns tracking extract data in a ZIP file, and the size of that file can vary depending on the amount of activity, the accounts involved, and the types of data requested.

How Do I Import Tracking Extract Data Into My Program?

No primary key is defined in the event tables of the tracking extract. Any relationships between primary key tables and event tables should be enforced by code and not foreign keys.

Primary key tables include the following tables:

• Attributes
  
• Lists
  
• ListMembership
  
• SendJobs
  • ClientID
  • SendID
  • SubscriberKey
  • EventDate
  • BatchID
• Sent
  
• Subscribers
  • SubscriberID
    
• StatusChange

 Event tables include the following tables:

• Soft Keys
  • ClientID
  • SendID
  • SubscriberKey
  • EventDate
  • BatchID 
• Bounces
• Clicks
  
• Conversions
  
• Opens
  
• Surveys
  
• Unsubs

Event tables may include duplicate rows due to differences in time by seconds or larger