Linking the Raw Data Tables

In this chapter, you get to know the relationships between the tables of the Mapp raw data export. For a reference of the fields contained in each table, visit Contents of the Raw Data Tables.

• Classification of Export Tables in Context Levels
• Linking between the Levels

Classification of Export Tables in Context Levels

Mapp data has three levels, each of which represents a different context:

• visits
• page calls
• clicks (or actions)

In general, there are 1 : n relationships between the levels. Within a visit, there is therefore at least one page call, and at least one click is assigned to a page call. However, the initial click that subsequently leads to a page call is not always attributed to a page call (if the visit was interrupted for a specific time and the visitor clicks a link, a new visit is started, and the first click is not assigned to a page call, since the page call occurred in a previous visit). The other export tables can also be assigned to the levels shown in the diagram.

Example

The "getFullCustomer" table represents a vertical partition (at the level of the table columns) of the "getFullVisitors" table. The columns contained in the "getFullCustomer" table, but not in the "getFullVisitors" table, could theoretically also be integrated into the "getFullVisitors" table (same primary key). However, since a customer ID is not measured/ transferred for every visit, the field of the corresponding data record would remain empty. This information is therefore delivered in a separate table.

Between the "getFullVisitors" table and the "getFullCustomers" table, there is a 1:1 relationship, whereby the occurrence of a corresponding data record in the "getFullCustomer" table is optional. A maximum of one customer ID can be assigned to each visit, but this does not have to be the case. However, if there is a record in the "getFullCustomer" table, there is always exactly one corresponding record in the "getFullVisitors" table. The same relationship exists between the "getFullVisitors" table and the getFullCustParaSession_X tables.

Raw Data Levels

The relationships are represented in the following (min, max) notation: each visit has at least 0, and at most one reference in the "getFullCustomer" table, this applies analogously to all "getFullCustParaSession" tables. These tables are linked via the session ID (SID).

The relationships also exist on level 2 between the table "getContents" and the other tables on this level, as shown in the graphic below: All tables displayed here can be linked to the table "getContents" via their REQUEST_ID field. For example, you can determine which page call triggered an order (usually the order is linked to a particular page at the end of the order process), whether a referrer was measured or which page parameters were included in the page call.

Corresponding relationships also apply at level 3, as shown in the following graphic: Some level 3 tables can be linked to the "getFullClicks" table using the REQUEST_ID. However, this does not apply to the tables "getFullForms", "getFullFormFields", "getFullMedia" and "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 the Levels

The basic relations between the tables of a level were introduced in the previous chapter "Classification of export tables in context levels." Here you will learn how to link the tables together in a meaningful way.

Each raw data export table (except the "getFullCustParaTimes" tables for the time categories) has the SID (Session ID) column. This column allows you to link the records to the corresponding visit. For example, you can determine which pages a visitor has visited during a particular visit by using the SID to connect the "getContents" table to the "getFullVisitors" table using a JOIN operation. To obtain information about the identity of the visitor who visited, you can add information to the "getFullCustomer" table.

The tables "getFullClicks", "getFullForms", "getFullMedia", "getFullFormFields" and "getFullMediaAction" of the action context (raw data level 3) each have the foreign key field REQUEST_ID. You can use this field to link the corresponding tables or data records directly to page calls. The primary key REQUEST_ID of the table "getContents" must be used for this purpose. For example, you can determine which forms or media are placed on which page (implicitly via the information with which page call a corresponding form or media action is linked). Of course, you can also determine the clicks associated with a page call.