Data Field and Table Dictionary: Marketing Activities

The following tables describe core marketing activity record fields in Sugar Market. See the Data Field and Table Dictionary: Contacts, Accounts, Opportunities, and Users page for descriptions of core contact, account, opportunity, and user record fields.

Campaigner

The following table describes the basic email campaign fields.

Column Name	Description	Data Type
ABTest	Indicates if campaign is AB test with True/False values	varchar(5)
ActivatedDate	Date when a campaign was activated	datetime
CampaignerID	Unique identity	int
CampaignName	Name of campaign	nvarchar(100)
CreatedByID	User ID of the user who created the record	int
CreateDate	Date when record was created	datetime
DialogID	DialogID of Dialog table, used on the ODS campaign designer to associate one landing page to an email	int
DisplayName	Display name of the email	nvarchar(100)
DisplayNameB	Same as DisplayName above, but the B version of the email	nvarchar(100)
FromEmail	From email address	nvarchar(150)
FromEmailB	From email address, B version	nvarchar(150)
LastUpdateByID	User ID of user who last updated the record	int
LastUpdateDate	Date when record was last updated	datetime
PostDate	The release date and time of the campaign	datetime
PostDateB	The release date and time of the campaign, B version	datetime
Sender	Email address used to indicate the sender of the email, viewed as the reply-to email address in the designer.	nvarchar(150)
Subject	Email subject line	nvarchar(500)
SubjectB	Email subject line, B version	nvarchar(500)
Tool	Version of Email Builder used to build the campaign: EMB = New Email Builder 2.0; ODS = Old Design System	nvarchar(3)

Campaigner Actual

The following table describes the individual recipient fields for each campaign.

Column Name	Description	Data Type
Body	HTML version of the email	nvarchar(max)
BodyNoHTML	Text version of the email	nvarchar(max)
BodySource	Which version of the email was used in A/B Test	varchar(20)
CampaignerActualID	Unique ID set by identity	int
CampaignerID	Campaigner ID of Campaigner table	int
Clicked	Number of times the email was clicked	int
ContactID	Unique identity of contact	int
CreateByID	User ID of the user who created the record	int
CreateDate	Date when record was created	datetime
DeliveryMessage	Text version of the delivery status (e.g. delivered, bad-domain, bad-mailbox, policy-related)	nvarchar(150)
DeliveryStatus	Numerical version of the delivery status: 0 = unknown, 1= delivered, 2 = bounced	int
DisplayName	Display name of the email from address	nvarchar(100)
Forms	No longer used	int
FriendForward	Number of times a link was clicked in a email forwarded to a friend	int
FromEmail	From email address	nvarchar(150)
ID	Reserved for external CRM's unique ID	nvarchar(100)
IDClick	Reserved for external CRM's unique ID for click rate	nvarchar(100)
IDOpen	Reserved for external CRM's unique ID for open rate	nvarchar(100)
Opened	Number of times the email was opened	int
PostDate	Date and time when the email should be sent	datetime
RecipientID	Unique recipient identity	int
Sender	Sender email address, also known as the reply-to email	nvarchar(150)
Status	Status of the individual email. Values include: -1 = Pending encoding 0 = Pending to be sent 1 = Send 2 = Rejected by mail server 6 = Previously opted out 7 = Previously undeliverable 8 = Excluded domain	int
Subject	Subject line of email	nvarchar(500)
ToEmail	Recipient's email address	nvarchar(400)
Unsub	Number of times that link was clicked	int

CampaignerLinkClick

The following table maintains a record for every time a user clicks on a link in a campaign.

Column Name	Description	Data Type
CampaignerActualID	CampaignerActualID of Campaigner Actual table	int
CampaignerID	Campaigner ID of Campaigner table	int
CampaignerLinkClickID	Unique ID set by identity	int
CampaignerLinkID	CampaignerLinkID of Campaigner Link table	int
ClickDate	Date of the click	datetime
ClientIP	The IP address of the recipients desktop	Nvarchar(50)
ID	Reserved for the external CRM's unique ID	varchar(100)
linkOrder	Order in which the link appeared in the email, derived from the CampaignerLink table or CampaignerLinkTemplate table	int
rURL	Actual URL that was used in the email campaign	nvarchar(3000)
Source	Source of the email from an A/B test. Two possible values A or B	Varchar(1)
toEmail	Recipient's email address	nvarchar(500)

CampaignerLinkOpen

The following table maintains a record for every time a user opens a campaign.

Column Name	Description	Data Type
CampaignerActualID	CampaignerActualID of Campaigner Actual table	int
CampaignerID	Campaigner ID of Campaigner table	int
CampaignerLinkOpenID	Unique ID set by identity	int
ClientIP	The IP address of the recipients desktop	Nvarchar(50)
ID	Reserved for the external CRM's unique ID	varchar(100)
OpenDate	Date the open was recorded	datetime
Processed	Determines if the record needs to be picked up for processing the campaigner warehouse. Values are: 0 = Pending; 1 = Completed	int
Source	Source of the email from an A/B test. Two possible values A or B	Varchar(1)
toEmail	Recipient's email address	nvarchar(500)
WebSessionID	WebSessionID of Web Session table. This is only recorded if the email was derived from the Event system.	varchar(150)

Distribution List

The Distribution List table contains the actual distribution list members.

Column Name	Description	Data Type
ContactID	Contact ID from Contact table. Not always present, but important for duplicate email addresses and mail merge.	int
CreatedByID	User ID of the user who created the record	int
CreatedDate	Date the record was created	datetime
DistributionListID	Unique ID set by identity	int
DistributionListNameID	Foreign key to the Distribution list name table (the parent table)	int
Email	Recipient's email address	nvarchar(250)
ExternalCRMID	If list is synced down from the external CRM, then this will contain the unique ID. This should also match to Contacts.ID. A value is not always present since it depends on the external CRM and where the list was loaded.	varchar(100)
ListName	List name	nvarchar(200)
UpdatedByID	User ID of the user who last updated the record	int
UpdatedDate	Updated date from the last update	datetime
UserID	UserID of user table. This is similar to an owner ID. In cases like the external CRM, this is just set to 1. In cases of the file upload system, this is set to the person who uploaded the file.	int

Dialog (Landing Pages)

The following is the primary data table for landing pages.

Column Name	Description	Data Type
Completes	This column is derived by internal processes. Warehouse of the number of completes for a given landing page.	int
CreatedByID	User ID of the user who created the record	int
CreatedDate	Date when record was created	datetime
DialogDescription	Landing page description*	nvarchar(500)
DialogID	Unique ID set by identity	int
DialogName	Name of landing page	nvarchar (100)
DialogStartLink	Uniquely generated code for the landing page URL (e.g. CH00000001eR00000001AD)	nvarchar (250)
DialogType	Type of landing page (e.g. Survey, Product Selection, Other, Product Configuration, Redirect Form, Landing Page, Event Registration)*	nvarchar (100)
Expire	Whether the landing page automatically expires at a certain date/time. True = Auto-expire; False = Do not auto-expire	varchar(5)
ExpireDate	If "Expire" is set to True, then this is the date/time when it will expire.	datetime
InactiveNotice	If the landing page is no longer active, then an HTML page displays. This column contains the full HTML for the inactive notice.	nvarchar(max)
LastCompleteDate	Landing page last completed date	datetime
LastStartDate	Landing Page last start date	datetime
MaxSeats	Total number of seats the landing page supports. The landing page becomes inactive when the max seats is reached.*	int
Opens	Derived by internal processes, this is a warehouse of the number of landing page starts/opens.	int
PrivacyPolicyLink	The landing page has a merge field for the privacy policy. This column holds the URL that should be merged to the privacy policy.	nvarchar(100)
StartFormID	The form/page that should start the landing page	int
Status	The state of the landing page: A = Active; D = Deleted; I = Inactive	nvarchar(10)
SubmitSubject	User defined subject line of the landing page complete alert	nvarchar(200)
UpdatedByID	User ID of user who last updated the record	int
UpdatedDate	Date when the record was last updated	datetime

RequestForm (Landing Pages)

A record is created in the RequestForm table when someone starts a landing page. Each page visited by a recipient will have a request form logged with the questions and answers.

Column Name	Description	Data Type
CampaignerActualID	CampaignerActualID of Campaigner Actual table. If the landing page was a link in a Sugar Market campaign, then the campaign ID is recorded in the CampainerActualID field.	int
CampaignerID	CampaignerID of Campaigner table. If the landing page was a link in a Sugar Market campaign, then the campaign ID is recorded in the CampaignerID field.	int
Completed	Whether the request form was completed (the recipient made it to the last page). 0 = Not completed; 1 = Completed	int
ContactID	ContactID of Contact table. When the request form is processed, the contact is identified or created, then the contact ID is written to the record.	int
CreatedDate	Date when record was created	datetime
EmailAddress	Recipient's email address. This value is only entered into the request form if the email address was identified at the beginning of the session. The email address can be filled in if the URL to the landing page contains ++TriggerEmail++ or the user is already cookied.	varchar(150)
FormID	The form ID used to create the Request form record.	int
ID	Reserved for the external CRM's unique ID. When pushing landing page results to the external CRM, the external CRM returns a unique ID of the new record, which is recorded here. If the ID is null, then the record has not been pushed.	nvarchar(100)
OwnerID	The user to whom the new lead should be assigned.	int
RequestFormID	Unique, set by identity	int
SessionID	Unique GUID that tracks the landing page. All forms tied to the same GUID are part of the same landing page.	nvarchar(100)

WebActivity

The Web Activity table is the main table for tracking the customer's web analytics. Web Activity hits will create a record in this table.

Column Name	Description	Data Type
AreaCode	Area code of the IP address set from GeoIP	int
City	City of the IP address set from GeoIP	nvarchar(100)
ClientHostname	Host name of client from request object	nvarchar(100)
ClientIP	IP address of client from request object	nvarchar(50)
ContactID	Unique ID of Contact table	int
CookiesEnabled	Whether cookies are enabled from the request object	varchar(10)
CountryCode	Country code from the GeoIP	nvarchar(50)
CountryName	Name of country from the GeoIP	nvarchar(50)
DoNotTrack	An opt out by the client browser that sends a signal to DoNotTrack browser. A true value indicates the recipient has enabled this option.	varchar(20)
Duration	The total duration of the user's session, represented as a numeric value that sums the Web Activity Detail sessions.	Numeric(18,2)
EmailAddress	The recipient's email address	nvarchar(150)
EndDate	The time out of the session	datetime
ID	Reserved for the external CRM's unique ID	nvarchar(100)
ISP	The ISP of the web activity as identified by GEOIP	nvarchar(100)
OrganizationName	Organization name of web activity as identified by GEOIP	nvarchar(100)
OwnerID	UserID of User table when an email address is detected. This is the owner of the record for the email address.	int
PostalCode	The postal code as identified by the GEOIP	nvarchar(25)
RecipientID	The unique recipient ID as detected by the cookie. This is the ID that will translate to the email address.	int
ReferrerID	The referrer domain as detected by the request object	nvarchar(50)
ReferrerKeywords	The referrer keyword as detected by the request object	nvarchar(1000)
ReferrerQuery	The referrer query as detected by the request object	nvarchar(1000)
ReferrerReferrer	The referrer as detected by the request object	nvarchar(1000)
Region	The region (state or province) as identified by GeoI	nvarchar(50)
StartDate	The start date of the web activity session	datetime
TimeZone	The timezone of the browser as detected by JavaScript.	nvarchar(10)
TouchPoint	Sugar Market classification of web hit. Touchpoint is also be picked up by UTM parameters. Classifications are: Organic, Email Campaign, AdSense, Paid Search, External Link.	nvarchar(100)
WebActivityID	Unique ID, set by identity	int
WebSessionID	WebSessionID of WebSession table	nvarchar(150)

WebActivityDetail

The Web Activity Detail table is the table for tracking the detail of the customer's web analytics.

Column Name	Description	Data Type
Duration	Total time spent on the web page, computed at session close as the difference between the current interaction date and the previous hit's Interaction date.	numeric(8, 2)
EntryPage	Whether this was the first page to be hit for the session. Only one entry page per Session ID is allowed: 1 = entry page; 0 = non-entry page	nvarchar(1)
ExitPage	Whether this was the last page to be hit for the session. Only one exit page per Session ID is allowed: 1 = exit page; 0 = non-exit page	nvarchar(1)
HostName	The host name of the URL that was hit. For example, for the page http://www.salesfusion.com/ /downloads/Nurture%20Marketing%20for%20Dynamics%20CRM.pdf the host name is www.salesfusion.com.	nvarchar(250)
ID	Reserved for the external CRM's unique ID	nvarchar(100)
InteractionDate	The date/time when the web page was hit	datetime
Parameters	Any parameters of the URL, represented by everything after the "?" in the url	nvarchar(250)
Path	The full page name (e.g. www.salesfusion.com/pricing/ hit would pull in a path of /pricing/)	nvarchar(250)
Protocol	The web page protocal: HTTP or HTTPS	nvarchar(250)
Referrer	The referrer as captured by the request object	nvarcha(1000)
Title	Title of the web page	nvarchar(250)
WebActivityDetailID	Unique ID, set by identity	int
WebActivityID	WebActivityID of WebActivity table	int
WebSessionID	WebSessionID of Web Session table	nvarchar(100)