Data Schema

Data Connect creates the following tables in your data warehouse, each representing a different aspect of user behavior data. Understanding this schema is essential for effective analysis and joining with other data sources.

Data Connect adopts a hierarchical schema. By default, Data Connect will sync the following tables:

• One users table (for all user information)
• One sessions table (for all session information)
• One pageviews table (for all pageview information)

The users table contains a row for each unique user that has visited your site.

The sessions table represents individual user sessions.

The pageviews table contains detailed information about each pageview.

Contentsquare will create one table for every labeled event you've created within Contentsquare and synced downstream. See how to create, manage, and sync Events ↗.

Several columns across tables track time in different ways. Understanding their differences is important for writing accurate queries.

-- Find users and their first action within 30 seconds of session start
SELECT u.user_id, u.joindate, e.event_table_name, e.session_time
FROM users u
JOIN all_events e ON u.user_id = e.user_id
WHERE e.session_time <= 30
ORDER BY u.joindate DESC;

The api_errors table contains information about API errors detected during user sessions.

The js_errors table contains information about JavaScript errors detected during user sessions.

The custom_errors table contains information about custom errors detected during user sessions.

The following data schema table will be the same for every frustration factor (rage click, excessive hovering, multiple use element, multiple field interactions, multiple button interactions, loading time high and low activity).

The ecommerce_transactions table contains information about e-commerce transactions.

Table Does Not Sync For S3. The all_events table does not sync for S3. You can create an all_events view of all synced tables after the data synced to S3 has gone through ETL processing.

The all_events table is a UNION of all individual event tables. Because a single event can qualify for multiple event definitions, the same event_id may appear multiple times in this table with different event_table_name values.

For data warehouses (Redshift, BigQuery, Snowflake):

• The composite primary key for all_events should be: (user_id, event_id, event_table_name)
• Individual event tables can use: (user_id, event_id)

For S3:

• You must handle deduplication in your ETL process after identity resolution
• Use the same composite key pattern when creating views

See Troubleshooting for more information about duplicate event IDs.

Contentsquare creates a table called _sync_history that contains a record for every attempted sync. This table is helpful for monitoring sync performance and scheduling dependent jobs. All timestamps are in UTC for Redshift and BigQuery and in local time for Snowflake.

Contentsquare creates a table called _sync_info that contains metadata around the sync process and last updated times.

When a user is identified, a migration occurs to aggregate the data under the new identity. These migrations are recorded in this table. Time is a column for Redshift and S3 destinations only. Snowflake and BigQuery do not have a column for time.

This table contains metadata about the names of events, which allows you to build automated jobs that use this metadata. This table is re-created on each sync to stay updated with the latest event metadata.

The event_metadata table shows up differently for S3. You can query it as follows:

{
  "ui_name": "Stripe charge",
  "table_name": "stripe_charge",
  "source": "stripe",
  "last_updated_at": "1609459200000",
  "last_updated_by": "sales@company.com",
  "notes": "This is a note",
  "verified_at": "1640995200000",
  "verified_by": "admin@company.com"
}