RPI Realtime and the data warehouse

Overview

Data captured due to site visitors’ interactions with RPI landing and other web pages, and stored in the Realtime cache, can then be persisted in a series of tables in the RPI data warehouse.

Below is an entity relationship diagram (ERD) for the tables that are created in the data warehouse for Realtime data capture.

Refer to Admin: Load web cache data task setup for a table that lists out the system configuration options and how they affect the extraction of this data to the database or a file, including the FormSubmissionPersistFieldsAsJSON setting, which affects the behavior of how the form data is captured.

Web visitors and devices

RPI_WebVisitors

Persists details of RPI Realtime visitors.

VisitorID: ID uniquely identifying a site visitor.
SessionID: ID uniquely identifying session (e.g., browser) within which activity occurred.
FirstVisitDate: date and time of first visit by a visitor.
LastVisitDate: date and time of most recent visit by a visitor.
IsNoTrack: when set, indicates that the visitor will be treated as a new individual on each interaction with the RPI Realtime API.

RPI_WebDevices

Stores details of devices upon which RPI Realtime content was viewed.

DeviceID: ID of the device to which content was served.
VisitorID: ID uniquely identifying a site visitor.
SessionID: ID uniquely identifying session (e.g., browser) within which activity occurred.
IPAddress: the visitor's IP address.
UserAgent: user agent string, as provided by the visitor's device.
FirstVisitDate: date and time of first visit by a visitor.
LastVisitDate: date and time of most recent visit by a visitor.

Web forms

RPI_WebForm

Stores details of RPI web forms.

FormID: unique identifier of a web form.
FormName: name of a web form.

RPI_WebFormSubmission

Stores records of visitors’ submissions of RPI web forms.

FormSubmissionID: unique ID representing a visitor's submission of a web form.
FormID: unique identifier of a web form.
SubmissionDate: date and time at which a web form was submitted.
EmailAddress: email address captured at web form submission.
VisitorID: ID uniquely identifying a site visitor.
SessionID: ID uniquely identifying session (e.g., browser) within which activity occurred.
ChannelExecutionID: unique ID representing an instance of outbound fulfilment having been undertaken by RPI.
RPContactID: unique ID referencing an RPI offer history record.
PublishHistoryID: unique ID representing the publishing of an RPI web form.
Fields: when system configuration setting FormSubmissionPersistFieldsAsJSON set to true, this field is used to persist form fields as JSON.

RPI_WebForm_Meta

PublishHistoryID: unique ID representing the publishing of an RPI web form.
PublishDate: date and time at which the web form was published.
FormMeta: any RPI file metadata captured in respect of the web form (list of name/value pairs).
FormName: name of the web form.

RPI_WebFormFieldSubmission

Stores records of values captured for specific RPI web form fields at web form submission.

FormFieldSubmissionID: unique ID associated with capture of data within a specific form field.
FormSubmissionID: unique ID representing a visitor's submission of a web form.
FormID: unique identifier of a web form.
FieldName: name of the field.
FieldValue: data captured in the field.

Web visitor profiles

RPI_WebVisitorsParameters

Persists details of parameters passed in respect of RPI Realtime visitors when making calls to API endpoints.

VisitorID: ID uniquely identifying a site visitor.
DeviceID: ID of the device to which content was served.
SessionID: ID uniquely identifying session (e.g., browser) within which activity occurred.
FieldName: name of parameter.
Value: parameter value.
UpdateDate: date and time of capture of parameter value.

A CacheOutputExclusions array property within the RPI Realtime appSettings file allows you specify one or more parameter names. Inclusion of a parameter name within the setting results in its being ignored when the Web Cache Data Importer task populates RPI_WebVisitorsParameters.

RPI_WebVisitorsDecisions

Persists details of decisions made in respect of RPI Realtime visitors.

VisitorID: ID uniquely identifying a site visitor.
DeviceID: ID of the device to which content was served.
SessionID: ID uniquely identifying session (e.g., browser) within which activity occurred.
ImpressionID: ID representing a visit to a web page, for example. Note that a given impression may reference multiple results.
DecisionID: publish ID uniquely identifying the RPI file in respect of which a decision was made.
PublishHistoryID: unique ID representing the publishing of an RPI web form.
ContentName: RPI file name.
DecisionResult: ordinal number of content served.
PageInstanceID: unique ID of instance of landing page in which decision made, or unique ID of interaction activity if decision made in outbound Realtime mode.
DecisionDate: date and time when decision was made.
ResultID: unique ID representing the instance of the content's being served to the visitor.
DecisionContent: stores text content published to the cache (up to a maximum of 100 characters). If more than 100 characters are returned, nothing is stored in this column.
ContentPath: populated if content published to a web publish site.
ContentFormat: one of TEXT, HTML, JSON or XML.
ContextName: populated if content published using a context.
ContextSlotName: populated if content published using a context slot (if only a context supplied, set to “rpidefault”.
DefaultResult: ordinal number of default content to be served (if appropriate).
Source: e.g., landing page name, “API”.
ChannelExecutionID: unique ID representing an instance of outbound fulfilment having been undertaken by RPI.
RPContactID: unique ID referencing an RPI offer history record.

RPI_WebEvents

Persists details of RPI web events. The table is populated if system configuration settings WebCacheOutputEventData is set to true.

VisitorID: ID uniquely identifying a site visitor.
DeviceID: ID of the device to which content was served.
SessionID: ID uniquely identifying session (e.g., browser) within which activity occurred.
ImpressionID: typically a unique viewing of a page by a visitor.
EventTime: date and time of the event's occurrence.
EventName: name of the event.
EventDetail: additional detail captured along with an event (e.g., the URL associated with a Link Click event).
MetricValue: numerical value captured along with an event.
RPContactID: unique ID referencing an RPI offer history record.
ChannelExecutionID: unique ID representing an instance of outbound fulfilment having been undertaken by RPI.
PageInstanceID: RPI landing pages only; represents unique instance of publishing of the same.
SourceReference: URL (external page only).
EventMetadata: any metadata associated with the event (list of name/value pairs).

RPI_WebPages

Persists details of landing or external web pages visited by RPI Realtime visitors.

VisitorID: ID uniquely identifying a site visitor.
DeviceID: ID of the device to which content was served.
SessionID: ID uniquely identifying session (e.g., browser) within which activity occurred.
ImpressionID: typically a unique viewing of a page by a visitor.
PageName: name of the page.
HostDomain: domain of site hosting the web page.
PageInstanceID: RPI landing pages only; represents unique instance of publishing of the same.
VisitDate: date and time of page visit.

RPI_WebGoals

Persists details of goal smart asset goal attainment by RPI Realtime visitors.

VisitorID: ID uniquely identifying a site visitor.
DeviceID: ID of the device to which content was served.
SessionID: ID uniquely identifying session (e.g., browser) within which activity occurred.
GoalName: name of the goal.
PageInstanceID: RPI landing pages only; represents unique instance of publishing of the same.
AttainedDate: date and time at which the goal was attained.

RPI_WebOfferLookup

Persists details of Realtime activity initiated from context of outbound RPI offer (e.g., web page visit following link click in email).

VisitorID: ID uniquely identifying a site visitor.
DeviceID: ID of the device to which content was served.
SessionID: ID uniquely identifying session (e.g., browser) within which activity occurred.
ChannelExecutionID: unique ID representing an instance of outbound fulfilment having been undertaken by RPI.
RPContactID: unique ID referencing an RPI offer history record.
ChannelType: class name of plugin channel through which offer executed.
ActivityName: interaction offer activity that engendered realtime engagement.
VisitDate: date and time of page visit.

RPI_WebVisitorsScores

Persists details of RPI Realtime visitors’ decision scorecard scores.

VisitorID: ID uniquely identifying a site visitor.
DeviceID: ID of the device to which content was served.
SessionID: ID uniquely identifying session (e.g., browser) within which activity occurred.
ScorecardIdentifier: identifier property, captured at decision scorecard toolbox.
PublishID: ID of smart asset in which decision scorecard utilized.
Score: score assigned to the visitor in this context.
UpdateDate: date and time of score's being captured.

RPI_WebDecisions_Meta

PublishHistoryID: unique ID representing the publishing of a smart asset.
DecisionID: publish ID uniquely identifying the RPI file in respect of which a decision was made.
DecisionResult: ordinal number of content served.
LastPublishDate: date and time of asset's most recent publishing.
DecisionMeta: any RPI file metadata captured in respect of the smart asset (list of name/value pairs).

RPI_WebPages_Meta

PageInstanceID: RPI landing pages only; represents unique instance of publishing of the same.
PublishDate: date and time at which the landing page was published.
FileName: file name of the landing page.
PageName: page name of the landing page.
SiteName: name of the RPI web publish site to which published.
SiteFolderName: name of the folder within the same.
PageMeta: any RPI file metadata captured in respect of the landing page (list of name/value pairs).