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.

Data Connect creates one table per frustration signal type, each prefixed with frustration_events_. All frustration tables share a common set of columns, with a few table-specific columns listed under each table.

A rage click occurs when a user clicks on the same element multiple times in quick succession, indicating frustration.

A multiple button interaction occurs when a user clicks the same button multiple times, suggesting the button may not be responding as expected.

A multiple field interaction occurs when a user interacts with the same form field multiple times, which may indicate confusion or an error in the form.

A multiple use target event occurs when a user clicks the same element multiple times across the page.

An excessive hover event occurs when a user hovers over an element for an unusually long time, which may indicate hesitation or confusion.

A loading time event is recorded when a page or resource takes longer than expected to load, which can negatively impact user experience.

A low activity event is recorded when a user shows very little interaction with a page (fewer than 2 clicks and less than 2 seconds of engagement), suggesting disengagement.

The frustration_scores view is automatically created when a customer syncs at least one frustration table. It aggregates frustration signals at the pageview level by joining the pageviews table with all active frustration_events_* tables. Each row corresponds to one pageview and includes all pageview columns plus one count column per synced frustration table.

The count columns are dynamically named number_of_<table_name> based on which frustration tables are enabled. For example:

Only columns for frustration tables actively synced by the customer will appear in the view.

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"
}