- 3 Minutes to read
- Print
- DarkLight
Linking the Raw Data Tables
- 3 Minutes to read
- Print
- DarkLight
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.