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 email is AB test with True/False values | varchar(5) |
ActivatedDate | Date when an email was activated | datetime |
CampaignerID | Unique identity | int |
CampaignName | Name of email | 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 email 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 email | datetime |
PostDateB | The release date and time of the email, 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 email: 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: |
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 | 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 |
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) |