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

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

    • Contains link URL and some additional context for email link clicked and web link clicked events

      • For email link clicked: <context><ip>IP_ADDRESS</ip><ua>USER_AGENT</ua><url>URL</context>

    • Contains additional information about failure for email failed events

  • 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