Linking the Raw Data Tables

  • 3 minute(s) read
Prev Next

This guide explains how tables in the Mapp raw data export are related. For details on the fields in each table, see Contents of the Raw Data Tables.

Classification of Export Tables

Mapp data is organized into three levels, each representing a different scope:

  • Level 1: Visits

  • Level 2: Page calls

  • Level 3: Clicks (Events)

Relationships:

  • A visit (Level 1) contains at least one page call (Level 2).

  • Each page call may have one or more associated clicks (Level 3).

An initial click leading to a page call may not be linked if the visit was interrupted (session timeout).

Example of Table Relationships

The “getFullCustomer” table is a vertical partition of the “getFullVisitors” table, storing additional columns like Customer ID, which isn’t available for every visit.

Key Point:

  • 1:1 Relationship: Each record in “getFullCustomer” corresponds to one in “getFullVisitors,” but not every visit has a customer ID.

This same 1:1 relationship applies between “getFullVisitors” and the “getFullCustParaSession_X” tables.

Raw Data Levels

Level 1: Visits

The relationships are represented in the following (min, max) notation:

  • Each visit has at least 0, and at most 1 reference in the “getFullCustomer” table.

  • Linking Tables: These tables are linked via the session ID (SID).

Level 2: Page calls

The relationships also exist on level 2 between the table "getContents" and the other tables on this level.

  • Linking Tables: Use REQUEST_ID to link page-related tables.

For example, you can determine which page triggered an order, whether a referrer was measured, or which page parameters were included in the page call.

Level 3: Clicks (Events)

Some level 3 tables can be linked to the "getFullClicks" table using the REQUEST_ID. However, this does not apply to the following tables:

  • "getFullForms"

  • "getFullFormFields"

  • "getFullMedia"

  • "getFullMediaAction"

These tables can be linked to the "getFullClicks" table (by CLICK_REQUEST_ID) using the FORM_REQUEST_ID, MEDIA_REQUEST, or MEDIA_ACTION_REQUEST_ID fields (depending on the table, these are its primary keys).

Linking Data Tables

Learn how to meaningfully link the raw data export tables for comprehensive analysis.

Level 1: Visit

Using Session ID (SID)

Each raw data export table (except the "getFullCustParaTimes" tables) includes a SID (Session ID) column. This column is essential for linking records to their corresponding visits.

Example Pages within a visit

To identify pages viewed during a visit, join the “getContents” table with the “getFullVisitors” table using SID:

SELECT * 
FROM getContents 
JOIN getFullVisitors 
ON getContents.SID = getFullVisitors.SID;

Example Visitor Identity

To obtain visitor details, join the “getFullCustomer” table using SID:

SELECT * 
FROM getContents 
JOIN getFullVisitors 
ON getContents.SID = getFullVisitors.SID
JOIN getFullCustomer 
ON getFullVisitors.SID = getFullCustomer.SID;

Level 2: Page Calls

Using Request ID (REQUEST_ID)

REQUEST_ID links tables related to page calls.

Example Forms and Media

To determine which forms or media are associated with specific page calls, join these tables using the REQUEST_ID from the "getContents" table.

SELECT * 
FROM getContents 
JOIN getFullClicks 
ON getContents.REQUEST_ID = getFullClicks.REQUEST_ID;

Example Related Tables

You can also join with other related tables like “getFullForms” or “getFullMedia”:

SELECT * 
FROM getContents 
JOIN getFullForms 
ON getContents.REQUEST_ID = getFullForms.REQUEST_ID;

Level 3: Clicks (Events)

Using Action Context Fields

For action context tables (e.g., clicks, forms, media interactions), specific fields like FORM_REQUEST_ID, MEDIA_REQUEST_ID, or MEDIA_ACTION_REQUEST_ID are used to link records to actions.

Example Media Actions

To see which media actions are linked to page calls, use the appropriate action context field.

SELECT * 
FROM getContents 
JOIN getFullMediaAction 
ON getContents.REQUEST_ID = getFullMediaAction.MEDIA_ACTION_REQUEST_ID;

Practical Applications Across Levels

  • Determine Page Visits: Use SID (Level 1) to link visits to specific page calls (Level 2) and clicks (Level 3).

  • Visitor Information: Add the "getFullCustomer" table (Level 1) to get visitor-specific data.

  • Action Context: Link form submissions, media interactions, and clicks (Level 3) to page calls (Level 2) using REQUEST_ID.

By applying these linking methods across data levels, you can thoroughly analyze visitor behavior within your raw data export system.

Raw Data Export Tables

Click on the image to download the Excel file.

Entity-Relationship Model

Here is the entity-relationship model for the raw data export tables. Click here to download it as PDF.