Creating Tracking Extract Data Extensions in SFMC

 Introduction 

Creating tracking extract data extensions in Salesforce Marketing Cloud (SFMC) can be a time-consuming task, especially when done manually. To streamline this process, I've created a JSON package that includes all the necessary data extensions, fields, primary keys, and nullable/non-nullable fields. This package can be used to quickly set up tracking extract data extensions in your SFMC instance, saving you valuable time and effort.

Data Extensions Overview 

The JSON package contains several data extensions, each designed to track different aspects of email performance. Below is a detailed overview of each data extension, including its fields and structure.

1. Tracking_Extract_Opens
Description: Tracks email open events.

Field Name	Data Type	Primary Key	Nullable	Length
ClientID	Number	Yes	No
SendID	Number	Yes	No
SubscriberKey	Text	No	No	254
EmailAddress	EmailAddress	No	No	254
SubscriberID	Number	Yes	No
ListID	Number	Yes	No
EventDate	Date	Yes	No
EventType	Text	No	No	15
BatchID	Number	Yes	No
TriggeredSendExternalKey	Text	No	Yes	100
IsUnique	Boolean	No	Yes
IpAddress	Text	No	Yes	15
Country	Text	No	Yes	50
Region	Text	No	Yes	50
City	Text	No	Yes	50
Latitude	Text	No	Yes	10
Longitude	Text	No	Yes	10
MetroCode	Text	No	Yes	10
AreaCode	Text	No	Yes	10
Browser	Text	No	Yes	50
EmailClient	Text	No	Yes	50
OperatingSystem	Text	No	Yes	50
Device	Text	No	Yes	50
InsertDate	Date	No	Yes
UpdateDate	Date	No	Yes

2. Tracking_Extract_Clicks
Description: Tracks email click events.

Field Name	Data Type	Primary Key	Nullable	Length
ClientID	Number	Yes	No
SendID	Number	Yes	No
SubscriberKey	Text	No	No	254
EmailAddress	EmailAddress	No	No	254
SubscriberID	Number	Yes	No
ListID	Number	Yes	No
EventDate	Date	Yes	No
EventType	Text	No	No	6
SendURLID	Number	Yes	No
URLID	Number	Yes	No
URL	Text	No	No	4000
Alias	Text	No	Yes	4000
BatchID	Number	Yes	No
TriggeredSendExternalKey	Text	No	Yes	100
IsUnique	Boolean	No	Yes
IsUniqueForURL	Boolean	No	Yes
IpAddress	Text	No	Yes	100
Country	Text	No	Yes	50
Region	Text	No	Yes	50
City	Text	No	Yes	50
Latitude	Text	No	Yes	10
Longitude	Text	No	Yes	10
MetroCode	Text	No	Yes	10
AreaCode	Text	No	Yes	10
Browser	Text	No	Yes	50
EmailClient	Text	No	Yes	50
OperatingSystem	Text	No	Yes	50
Device	Text	No	Yes	50
InsertDate	Date	No	Yes
UpdateDate	Date	No	Yes

3. Tracking_Extract_SendJobImpression
Description: Tracks send job impressions.

Field Name	Data Type	Primary Key	Nullable	Length
SendID	Number	Yes	No
SubscriberKey	Text	Yes	No	254
EmailAddress	Text	Yes	No	254
EventDate	Date	Yes	No
ImpressionRegionName	Text	Yes	No	70
BatchID	Number	Yes	No
InsertDate	Date	No	Yes

4. Tracking_Extract_Sent
Description: Tracks sent emails.

Field Name	Data Type	Primary Key	Nullable	Length
ClientID	Number	Yes	No
SendID	Number	Yes	No
SubscriberKey	Text	No	No	254
EmailAddress	EmailAddress	No	No	254
SubscriberID	Number	Yes	No
ListID	Number	Yes	No
EventDate	Date	Yes	No
EventType	Text	No	No	15
BatchID	Number	Yes	No
TriggeredSendExternalKey	Text	No	Yes	100
CampaignID	Text	No	Yes	100
InsertDate	Date	No	Yes
UpdateDate	Date	No	Yes

5. Tracking_Extract_NotSent
Description: Tracks emails that were not sent.

Field Name	Data Type	Primary Key	Nullable	Length
ClientID	Number	Yes	No
SendID	Number	Yes	No
EmailAddress	EmailAddress	No	No	254
SubscriberID	Number	Yes	No
ListID	Number	Yes	No
EventDate	Date	Yes	No
EventType	Text	No	No	15
BatchID	Number	Yes	No
TriggeredSendExternalKey	Text	No	Yes	100
SubscriberKey	Text	No	No	254
Reason	Text	No	Yes	300
InsertDate	Date	No	Yes
UpdateDate	Date	No	Yes

6. Tracking_Extract_Subscribers
Description: Tracks subscriber information.

Field Name	Data Type	Primary Key	Nullable	Length
ClientID	Number	Yes	No
SubscriberKey	Text	No	No	254
EmailAddress	EmailAddress	No	No	254
SubscriberID	Number	Yes	No
Status	Text	No	No	20
DateHeld	Date	No	Yes
DateCreated	Date	No	Yes
DateUnsubscribed	Date	No	Yes
InsertDate	Date	No	Yes
UpdateDate	Date	No	Yes

7. Tracking_Extract_Unsubs
Description: Tracks unsubscribe events.

Field Name	Data Type	Primary Key	Nullable	Length
ClientID	Number	Yes	No
SendID	Number	Yes	No
SubscriberKey	Text	No	No	254
EmailAddress	EmailAddress	No	No	254
SubscriberID	Number	Yes	No
ListID	Number	Yes	No
EventDate	Date	Yes	No
EventType	Text	No	No	15
BatchID	Number	Yes	No
TriggeredSendExternalKey	Text	No	Yes	100
UnsubReason	Text	No	Yes	100
InsertDate	Date	No	Yes
UpdateDate	Date	No	Yes

8. Tracking_Extract_Bounces
Description: Tracks bounce events.

Field Name	Data Type	Primary Key	Nullable	Length
ClientID	Number	Yes	No
SendID	Number	Yes	No
SubscriberKey	Text	No	No	254
EmailAddress	EmailAddress	No	No	254
SubscriberID	Number	Yes	No
ListID	Number	Yes	No
EventDate	Date	Yes	No
EventType	Text	No	No	6
BounceCategory	Text	No	Yes	100
BounceReason	Text	No	Yes	1000
SMTPCode	Number	No	Yes
BatchID	Number	Yes	No
TriggeredSendExternalKey	Text	No	Yes	100
InsertDate	Date	No	Yes
UpdateDate	Date	No	Yes

Useful resource: https://help.salesforce.com/s/articleView?id=mktg.mc_as_configure_a_tracking_extract.htm&type=5