Data Model
Data model for server-side and cookieless analytics data model
Overview
Hardal Analytics uses a modern data architecture built on Hardal DB with SQL for high-performance analytics. Our cookieless analytics system captures comprehensive event data while respecting user privacy. This guide outlines the complete data model and table structures used in Hardal Analytics.
Core Tables
1. Website Events Table (website_event)
The primary table that stores all user interactions and page views. This table is partitioned by month for optimal performance.
| Column Name | Data Type | Description |
|---|---|---|
id | UUID | Unique identifier for each event (auto-generated) |
website_id | LowCardinality(String) | Unique identifier for the website/app |
session_id | LowCardinality(String) | Unique identifier for the user session |
hostname | LowCardinality(String) | Domain name of the website |
browser | LowCardinality(String) | Browser name (e.g., ‘Chrome’, ‘Safari’) |
browser_version | LowCardinality(String) | Browser version number |
os | LowCardinality(String) | Operating system (e.g., ‘Windows’, ‘macOS’) |
device | LowCardinality(String) | Device type (‘desktop’, ‘mobile’, ‘tablet’) |
screen | LowCardinality(String) | Screen resolution (e.g., ‘1920x1080’) |
viewport_size | LowCardinality(String) | Viewport dimensions |
device_pixel_ratio | Float32 | Device pixel ratio for retina displays |
language | LowCardinality(String) | Browser language setting |
country | LowCardinality(String) | User’s country code (ISO 3166-1) |
timezone | LowCardinality(String) | User’s timezone |
url | String | Complete URL of the page |
url_path | String | Path component of the URL |
url_query | String | Query parameters from the URL |
url_protocol | LowCardinality(String) | Protocol used (e.g., ‘https:‘) |
url_hash | String | URL hash component |
referrer | String | Complete referrer URL |
referrer_path | String | Path component of the referrer |
referrer_query | String | Query parameters from referrer |
referrer_domain | String | Domain of the referring page |
page_title | String | Page title from HTML |
event_type | LowCardinality(String) | Type of event (‘pageview’, ‘event’) |
event_name | LowCardinality(String) | Name of the event (e.g., ‘page_view’, ‘click’) |
created_at | DateTime | Timestamp when the event was created |
2. Event Data Table (event_data)
Stores custom properties and additional data associated with events. This allows for flexible event tracking without schema changes.
| Column Name | Data Type | Description |
|---|---|---|
id | UUID | Unique identifier for each data entry |
website_id | LowCardinality(String) | Website identifier |
session_id | LowCardinality(String) | Session identifier |
event_id | UUID | References the main event in website_event |
event_name | LowCardinality(String) | Name of the associated event |
url_path | String | Path where the event occurred |
data_key | String | Property name/key |
string_value | Nullable(String) | String value for the property |
number_value | Nullable(Float64) | Numeric value for the property |
date_value | Nullable(DateTime) | Date/time value for the property |
data_type | UInt32 | Type indicator for the stored value |
created_at | DateTime | Timestamp when the data was created |
3. Session Data Table (session_data)
Stores session-level properties and user attributes that persist across multiple events within a session.
| Column Name | Data Type | Description |
|---|---|---|
id | UUID | Unique identifier for each session data entry |
website_id | LowCardinality(String) | Website identifier |
session_id | LowCardinality(String) | Session identifier |
data_key | String | Property name/key |
string_value | Nullable(String) | String value for the property |
number_value | Nullable(Float64) | Numeric value for the property |
date_value | Nullable(DateTime) | Date/time value for the property |
data_type | UInt32 | Type indicator for the stored value |
created_at | DateTime | Timestamp when the data was created |
Table Configuration:
- Engine: ReplacingMergeTree (handles updates to session data)
- Ordering:
(website_id, session_id, data_key)
Data Flow and Relationships
Session Management
- Each user session is identified by a unique
session_id - Sessions are cookieless and based on server-side fingerprinting
- Session data persists across multiple page views and events