Page tree
Skip to end of metadata
Go to start of metadata

Email Marketing Tables and Fields

Email marketing makes use of several tables and fields for tracking and reporting. These entities are included when you select sales and marketing features when creating your KB.

If you choose not to use the default Campaign table, you might want to use this list to make sure the table you choose instead has all the necessary elements for you to replicate the reporting features.

All Communications/Emails Table

Email marketing uses the following fields in the All Communications/Emails table:

Field Label

Data Type, Field Name, etc.

Notes

Email Status

Choice: Bounced, Unsubscribed, Link Clicked, Opened, Sent; default value is Sent.

Choice list name: email status. This field shows the highest priority action performed in response to the email. If an email is opened and a link inside it is clicked, that email shows a status of Link Clicked; an email with a link clicked and the Unsubscribe link clicked shows Unsubscribed.

Email Activities

Multi-choice: same choice list as Email Status with Sent selected by default

This field is updated with all actions taken in response to an email. For example, if a user opens an email and unsubscribes, both the Opened and Unsubscribed options would be checked.

Campaign ID, Campaign Name

Link to selected fields: Campaigns, ID, and Campaign Name

Strict link, display Auto.

Recipient Lead Name, Recipient Lead ID, Recipient Phone

Link to Lead table: ID, Full Name, Direct Phone

Default value: the record in which the email address matches the To field of the communication, with the field set to always update.

Last Email Status Update

Date/time

Default value: creation date, with the field set to update each time the Email Status field changes.

Additional Information Log

Append Only Text

Used for a bounce log, other status notes, or error messages related to tracking.

The link to the Leads table is used to create a link to the source record if the communication was sent to records in the default Leads table. This makes it easy to see prospect name and phone information in the communications shown in the campaign table. This enables sales personnel to follow up with people who clicked or opened an email directly from within the campaign, which shows all the emails sent.

Email Clicks Table

This table tracks all URLs that are clicked in emails sent with marketing tracking on. Email marketing uses these fields:

Field Label

Data Type, Field Name, etc.

Notes

URL Clicked

Text

Shows the URL that was clicked.

Linked Email ID

Link to Communications/Emails: id

The ID of the email record from which the link was clicked.

Source Record ID

Link to single field from multiple tables, with People, Leads: ID by default

This is the source record the email was sent from. This field is initially configured to use the People and Leads tables, as they are the most likely sources. If the source is some other table, then the admin should add that table to the field as well.

Campaign ID, Campaign Name

Link to campaign table

The campaign for which the email was linked. 

It is possible to add fields to the linked sets to hold more information in the click record itself, such as the lead or contact name, email, and so on. CRM KBs are configured to include additional fields by default, such as name and email, so that they are accessible directly from the click record.

This table is hidden by default, so if you want to use it, you might want to unhide it and add it to your left pane.

Campaigns Table

The following fields are added to the default Campaigns table or the table chosen by the admin when enabling email marketing.

Field Label

Data Type, Field Name, etc.

Notes

All Email Clicks

Related table: Email Clicks table from linked campaign field

This shows all email clicks.

All Sent Emails

Related Table: All Communications/Email

Based on linked field to campaign, or campaign substitute, table.

Number of Bounced Emails

Integer

A script increments this tally by 1 when a received email is bounced.

Number of Opened, Clicked, or Unsubscribed Emails

Integer

A script increments this tally by 1 when a received email is opened, a link is clicked, or unsubscribed.

Number of Recipients who Clicked links

Integer

A script increments this tally by 1 when the Email Status field of an email is changed to Link Clicked, only once per email.

Number of Sent Emails

Integer

A script increments this tally by 1 when emails are sent.

Number of Unsubscribes

Integer

A script increments this tally by 1 when an unsubscribe link is clicked.

Number Opened but not Clicked or Unsubscribed

Integer

A script increments this tally by 1 when an email is opened only.

Number Opened or Clicked but not Unsubscribed

Integer

A script increments this tally by 1 when an email is opened or a link is clicked.

Percent Bounced

Percentage, formula updated on all updates

100 * Number of Bounced Emails / Number of Sent Emails

Percent Clicked

Percentage, formula updated on all updates

100 * Number of Recipients who clicked links / Number of Sent Emails

Percent Opened, Clicked or Unsubscribed

Percentage, default formula updated on all updates

100 * Opened, Clicked, or Unsubscribed Emails / Number of Sent Emails

Percent Opened or Clicked

Percentage, formula updated on all updates

100 * (Number Opened or Clicked but not Unsubscribed / Number of Sent Emails)

Percent Opened and not Clicked or Unsubscribed

Percentage, formula updated on all updates

100 * (Number Opened but not Clicked or Unsubscribed / Number of Sent Emails)

Percent Unsubscribed

Percentage, formula updated on all updates

100 * Number of Unsubscribed / Number of Sent Emails

Total Number of Clicks

Calc on Email Clicks related table, count

Gives a count of how many links were clicked.

The "Number of" fields are created as integer fields without default values. They are updated by the email marketing engine script each time an email response is captured. 


Leads Table

The following fields are used in the Leads table:

Field Label

Data Type, Field Name, etc.

Notes

Email Clicks from Outbound Emails

Related Table: Email Clicks

Based on source record ID field, this shows all links clicked by a given lead.

Email Status

Choice: same choices as for the Email Status field in Communications

This is automatically updated when an email to the lead is updated.

People Table

The following fields are used in the People table:

Field Label

Data Type, Field Name, etc.

Notes

Email Clicks from Outbound Emails

Related Table: Email Clicks

Based on source record ID field.

Email Status

Choice: same choices as for the Email Status field in Communications

This is automatically updated when an email to the contact is updated.