Analytics Data Dictionary

The following database tables are available from Monetate Analytics Cloud.

Experience Configuration Data

These tables contain metadata about experiences, how they're configured, and the rules that define them.

Experience Metadata

The METADATA_EXPERIENCE table contains all configuration metadata for experiences. It's the base table to join against for information about experiences.

Page Event Metadata

The METADATA_PAGE_EVENT table contains all the metadata information for page events.

Experience–Page Event Metadata

The METADATA_PAGE_EVENT table links experiences to page events. You can query this table to obtain a list of which page event IDs are associated with an experience.

Experience Tag Metadata

The METADATA_EXPERIENCE_TAG table contains metadata about any tags an experience has.

Experience Splits Metadata

The METADATA_SPLIT table contains metadata about an experience's splits.

Experience Variants Metadata

The METADATA_VARIANT table contains metadata about variants of a split. For ease of use, it includes the experience ID so that you can directly query what experience a variant is attached to. Keep in mind, though, that the hierarchy is still experience > split > variant.

This table is the anchor between the session tables and the other metadata tables. Specifically, you can use VARIANT_ID in the SESSION_FIRST_OFFER table to join against this variants table. From there, you can access any of the session tables via MONETATE_ID, or you can access any of the metadata tables by joining against the METADATA_EXPERIENCE table.

Session and Interaction Data

These tables contain information about visitor sessions, their interactions, and conversion events.

Session Summary

The SESSION_SUMMARY table contains metadata about individual sessions. It includes all single-value dimensions and measures for each session. You can calculate aggregate metrics—such as conversion rate, add-to-cart rate, bounce rate, and revenue per session—by using the provided measures.

This table is the anchor for all other joins of session-based data.

For more information about how geolocation-related data is determined, refer to MaxMind's GeoIP2 documentation. See the DeviceAtlas documention for additional information about technographics-related data.

Variants Assigned to Sessions

The SESSION_FIRST_OFFER table contains metadata about the variants that a session was assigned, or offered, for each of the experiences it saw. Keep in mind the following points about sessions and variants:

• A single session is assigned to only one variant of any eligible experience.
• Variant assignments don't change during a session.
• Variant assignment may change between sessions if the experience's split configuration changes, or if the variant assigned to a customer ID differs from the variant assigned to the session's Monetate ID.

For each experience Monetate only records an offer if any page view in the session was eligible for an action in any of the splits in the experience. To be eligible for an action, the page view must meet all action conditions configured for the action. A variant is assigned after this eligibility check, which means that a session isn't assigned to the control variant of a split unless it could have been eligible to display an action had it been assigned to the experiment variant instead.

Join this table with the METADATA_VARIANT table to associate metadata for each assigned variant.

Page Events in Sessions

The SESSION_FIRST_PAGE_EVENT table contains metadata about the set of distinct page events seen by a session.

Join this table with the METADATA_PAGE_EVENT table to associate metadata for individual page event IDs.

Page Types in Sessions

The SESSION_DISTINCT_PAGE_TYPE table contains metadata about the distinct page types the customer saw in a session.

Purchases in Sessions

The PURCHASE_FACT table contains metadata about the individual line items of purchases the customer made in a session.

Query Examples

You can use the following query examples to extract values from the database tables.

List Variants

The following query lists all variants and the splits to which they belong across all experiences.

SELECT
  me.experience_id,
  ms.split_id,
  mv.variant_id 
FROM
  public.dt_metadata_experience me 
  JOIN
    public.dt_metadata_split ms 
    ON ms.experience_id = me.experience_id 
  JOIN
    public.dt_metadata_variant mv 
    ON mv.split_id = ms.split_id;

List Sessions in a Variant

The following query lists all sessions that qualified for a specified variant.

SELECT
  * 
FROM
  public.dt_metadata_variant mv 
  JOIN
    public.dt_session_first_offer sfo 
    ON sfo.variant_id = mv.variant_id 
  JOIN
    public.dt_session_summary ss 
    ON ss.monetate_id = sfo.monetate_id 
    AND ss.start_time = sfo.session_start_time 
WHERE
  mv.variant_id = '12345-0'

Filter Experiences by Integration Type

The following query lists only experiences deployed using the Engine API. To view a lit of experiences deploying using the Monetate JavaScript API, replace engine-api with js-tag.

SELECT
  * 
FROM
  public.dt_metadata_experience 
WHERE
  integration_type = 'engine-api';

List Page Events by Experience

The following query lists all page events associated with an experience.

SELECT
  * 
FROM
  public.dt_metadata_experience_page_event mepe 
  JOIN
    public.dt_metadata_page_event mpe 
    ON mepe.page_event_id = mpe.page_event_id 
WHERE
  mepe.experience_id = 1234;

Filter and List Distinct Tags

The following query lists distinct tags for all experiences that started in a specified time period.

SELECT DISTINCT
(met.tag_name) 
FROM
  public.dt_metadata_experience_tag met 
  JOIN
    public.dt_metadata_experience me 
    ON met.experience_id = me.experience_id 
WHERE
  me.start_time >= '2023-11-01 00:00:00' 
  AND me.start_time < '2023-12-01 00:00:00';

Count Sessions by Experience Tag

By joining through the SESSION_FIRST_OFFER table, the following query lists for each specified tag the number of sessions that qualified for experiences over a specified period of time.

SELECT
  met.tag_name,
  COUNT(*) 
FROM
  public.dt_metadata_experience_tag met 
  JOIN
    public.dt_metadata_experience me 
    ON me.experience_id = met.experience_id 
  JOIN
    public.dt_metadata_variant mv 
    ON me.experience_id = mv.experience_id 
  JOIN
    public.dt_session_first_offer sfo 
    ON mv.variant_id = sfo.variant_id 
WHERE
  sfo.session_start_time >= '2023-11-01 00:00:00' 
  AND sfo.session_start_time < '2023-12-01 00:00:00' 
GROUP BY
  met.tag_name;