Tracking Database Schema

Overview

This document contains a short description of uProduce's tracking database. The tracking database tracks recipient interaction events on print, email and web activities.

It is intended for Cross-Media customers, who wish to create analytic reports for 1G campaigns. After the deprecation of Marketing Console, the 1G solution requires creation of only custom-made analytic reports.

On the other hand, Circle campaigns (2G) provide built-in analytic capabilities with predefined reports. If you wish to create advanced or custom reports, please contact the Product team.

Note: Hosted customers do not have access to this database.

Relationship diagram

Event Table and its Foreign Table

Event Table

The main table of the database. Each row represents a common information about an event in the system.

• EventID – event ID (primary key and foreign key to AppEvent table)

• EventTypeID – event type ID (foreign key to EventType table)

• MarketingCampaignID – uProduce campaign ID to which this event belongs

• JobID – uProduce job ID to which this event belongs (foreign key to Job table)

• RecipientID – recipient ID to which this event belongs (foreign key to Recipient table)

• SegValueBagID – seg value bag ID – a bag of all additional properties/segments of the event (foreign key to SegValueBag table)

• Context – unused field

• TriggerDate – a date and time when the event occurred

• CreatedDate – a date and time when the event was added to the database

• StatusID – status of an event (usually active but can be marked as deleted) (foreign key to Status table)

SegValueBag Table

The table contains definition of segment value bag (container). Segment is an additional property of an event that can be an ADOR or a property.

• SegValueBagID – segment value bag ID (primary key)

• MarketingCampaignID – uProduce campaign ID

Job Table

The table contains information about uProduce jobs.

• JobID – uProduce job ID (primary key)

• JobTypeID – uProduce job type

• AccountID – uProduce account ID to which this job belongs

• MarketingCampaignID – uProduce campaign ID to which this job belongs

• TouchPointID – uProduce touch point ID to which this job belongs

Recipient Table

The table contains information about recipients.

• RecipientID – recipient ID in tracking database

• MarketingCampaignID – uProduce campaign ID

• RecipientKey – the recipient key of this recipient

EventType Table

The table contains information about event types. This is a lookup table.

• EventTypeID – event type ID

• Name – event type name

• Description – event type description

The following is a list of built-in event types. Not all event types are generated by the system.

ID	Value	Description	Comment
1	ADOR Value Read	An ADOR value was read.
3	ADOR Value Updated	An ADOR value was modified.
4	Recipient Added	Recipient was added.
5	Recipient ID Modified	Recipient ID was changed.
6	Recipient Deleted		For future use only.
10	Printed	Print output was created.
11	Print Delivered		For future use only.
20	Email Sent	Email was sent.
21	Email Send Failed	Email send failed.
22	Email Delivered		For future use only.
23	Email Opened	Email was opened.
24	Email Unsubscribed	Recipient unsubscribed.
25	Email Temporarily Failed		For future use only.
26	Email Subscribed	Recipient subscribed.
27	Email Link Clicked	Recipient clicked a link in an email.
100	Page Visit	Webpage visit.
101	Navigated	Recipient clicked a link in a webpage.
102	Clicked		For future use only.
103	Performed Action	Recipient clicked a button in a webpage.
104	Page Leave	Recipient left a webpage.
200	Custom Event		For future use only.

Status Table

The table contains information about event statuses. This is a lookup table.

• StatusID – event status ID

• Name – event status name

• Description – event status description

AppEvent Table

The table contains additional information for web and email events. The relation to Event table is 1 to 1.

• EventID – event ID

• AppClientIPID – IP ID (foreign key to AppClientIP table)

• AppUserSessionID] – session ID (foreign key to AppUserSession table)

• WebSiteID – website ID (foreign key to WebSite table)

• AppExternalID – external ID (foreign key to AppExternalID table) – contains email address for email events

• WebPageID – web page ID (foreign key to WebPage table)

• ReferringWebPageID – unused field

• WebPageParams – web page parameters

• AppPartNameID – unused field

• AppPartParams – unused field

• AppActionNameID – action ID (foreign key to AppActionNameID table)

  • Contains link name (as it is defined in the XMPL page or email body) for email link clicked and web link clicked events

  • Contains failure reason (Hard Bounce, Soft Bounce, Unsubscribed, etc.) for email failed events

  • Contains reason for email unsubscribed events (Unsubscribed or empty for a regular unsubscribe, Complaint, etc.)

• AppActionParams – action params

  • For Email Link Clicked event (event type 27) – the AppActionParams contains the link’s URL

  • For Email Open event (event type 23) – the AppActionParams contains an XML with an IP address and Useragent of the email client where the email was opened

    • The XML structure: <context><ip>{{IP_ADDRESS}}</ip><ua>{{USER_AGENT}}</ua></context>

  • For Email Unsubscribed event (event type 24) – if the reason for unsubscribe was "Complaint", the AppActionParams contains the complaint type

    Notes:

    • For more information about possible complaint types, see https://docs.aws.amazon.com/ses/latest/dg/notification-contents.html

    • If a recipient has already been unsubscribed, when an email send attempt occurs, no email is sent, and an Email Send Failed event is created with a NULL value in the AppActionParams field.

  • For Email Send Failed event (event type 21) – if the failure reason is "Soft Bounce" or "Hard Bounce", the AppActionParams contains an XML with bounce details

    • The XML structure for Hard Bounce: <BounceProperties><Name>Bounce Category</Name><Value>Permanent</Value><Name>Bounce Type</Name><Value>{{BOUNCE_REASON}}:{{BOUNCE_DETAILS}}</Value></BounceProperties>

    • The XML structure for Soft Bounce: <BounceProperties><Name>Bounce Category</Name><Value>Transient</Value><Name>Bounce Type</Name><Value>{{BOUNCE_REASON}}:{{BOUNCE_DETAILS}}</Value></BounceProperties>

    Notes:

    • For more information about possible bounce reasons, see bounce sub types in https://docs.aws.amazon.com/ses/latest/dg/notification-contents.html

    • If a recipient has already been hard bounced, when an email send attempt occurs, no email is sent, and an Email Send Failed event is created with a NULL value in the AppActionParams field.

  • For Web Page Visit event (event type 100) – the AppActionParams contains an XML with an IP address and Useragent of the browser where the webpage was opened

    • The XML structure: <context><ip>{{IP_ADDRESS}}</ip><ua>{{USER_AGENT}}</ua></context>

• CustomParams – unused field

• SourceJobID – unused field

Additional Tables for Email and Web Events (Foreign Tables for AppEvent Table)

WebSite Table

The table contains information about uProduce websites. The uProduce web site is actually represented by uProduce port. So all sites are actually from this type.

• WebSiteID – website ID

• MarketingCampaignID – uProduce campaign ID

• WebSiteName – website name (actually uProduce port name)

• InternalID – internal website ID (actually uProduce port ID)

• WebSiteTypeID – website ID (foreign key to WebSiteType table)

WebPage Table

The table contains information about web pages.

• WebPageID – web page ID

• MarketingCampaignID – uProduce campaign ID

• WebSiteID – website ID to which this page belongs

• PageURI – web page URL

• IsLandingPage – indicates if this web page is a landing page

• PageName – web page name as it is defined in the XMPL page

AppPartName Table

Unused table.

• AppPartNameID – application part ID

• MarketingCampaignID – uProduce campaign ID

• PartName – application part name

AppActionName Table

The table contains information about uProduce jobs.

• AppActionNameID 0 action name ID

• MarketingCampaignID – uProduce campaign ID

• ActionName – action name

  • Contains link name (as it is defined in the XMPL page or email body) for email link clicked and web link clicked events

  • Contains failure reason (Hard Bounce, Soft Bounce, Unsubscribed, etc.) for email failed events

  • Contains reason for email unsubscribed events (Unsubscribed or empty for a regular unsubscribe, Complaint, etc.)

 AppUserSession Table

The table contains information about web session. Usually unused table.

• AppUserSessionID – session ID

• MarketingCampaignID – uProduce campaign ID

• UserSession – session

AppClientIP Table

The table contains information about IP address. Usually unused table.

• AppClientIPID – IP ID in tracking database

• MarketingCampaignID – uProduce campaign ID

• ClientIP – IP address

AppExternalID Table

The table contains information about external ID of an event.

• AppExternalID – external ID in tracking database

• MarketingCampaignID – uProduce campaign ID

• ExternalKey – actual external ID

  • Contains email address for email events

WebSiteType Table

The table contains information about website types. This is a lookup table.

• WebSiteTypeID – website type ID

• Name – website type name

• Description – website type description

Additional Tables for Segments

Segment is an additional property of an event that can be an ADOR or a property.

SegValueRef Table

The table is a join table between SegValueBag and SegValue tables and actually contain detentions of segment value bags (containers).

• SegValueID – segment value ID (foreign key to SegValue table)

• SegValueBagID – segment value bag ID (foreign key to SegValueBag table)

Seg Table

The table contains definition of segments. Segment is an additional property of an event that can be an ADOR or a property.

• SegID – segment ID

• Name – segment name

• MarketingCampaignID – uProduce campaign ID

• SegValueTypeID – segment value type ID (foreign key to SegValueType table) – number, string, datetime, etc.

• AdorID – ADOR ID in the tracking database (foreign key to ADOR table) – if it is NULL then it is a property and not an ADOR

• InUse – unused field

• IsPartial – unused field

• Description – segment description

SegValue Table

The table contains information about segment values. Segment is an additional property of an event that can be an ADOR or a property.

• SegValueID – segment value ID

• SegID – segment ID (foreign key to Seg table)

• Value – actual value

• ModeTypeID – value mode type ID (read or write value) (foreign key to ModeType table)

SegValueType Table

The table contains information about segment value types. This is a lookup table.

• SegValueTypeID – segment value type ID

• Name – segment value type name

• Description – segment value type description

ADOR Table

The table contains information about ADORs.

• AdorID – ADOR ID in the tracking database

• AdorName – ADOR name

• AdorTypeID – ADOR type ID (foreign key to ADORType table)

• GroupName – ADOR group as defined in the plan file

• MarketingCampaignID – uProduce campaign ID

• ExpressionType – ADOR expression type (read, write or read-write)

• CampaignDial – indicates if this ADOR is a campaign dial as defined in the plan file

• Description – ADOR description as defined in the plan file

ADORType Table

The table contains information about ADOR types. This is a lookup table.

• ADORTypeID – ADOR type ID

• Name – ADOR type name

• Description – ADOR type description

ModeType Table

The table contains information about segment value modes. This is a lookup table.

• ModeTypeID – mode type ID

• Name – mode type name

• Description – mode type description