SugarCRM SupportProduct GuidesSugar MarketSugar Market User GuideData Field and Table DictionariesData Field and Table Dictionary: Marketing Activities
Sugar Market was formerly known as Salesfusion.

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)

Last modified: 2020-09-03 16:59:03