Export your quiz analytics data in real-time to your own PostgreSQL, MySQL, MariaDB or BigQuery database. This feature allows you to maintain a local copy of all user properties and event logs for custom reporting, data warehousing, or integration with your existing systems.

Overview

When enabled, web2wave automatically exports two types of data to your remote database:

User Properties - User attributes and custom properties (email, UTM parameters, custom fields, etc.)
Event Logs - All quiz events and user interactions (page views, quiz completions, conversions, etc.)

Data is exported in real-time as events occur, ensuring your database is always up-to-date.

Requirements

Subscription Plan: Advanced plan required
Database: PostgreSQL, MySQL, MariaDB or BigQuery
Network Access: Your database must be accessible from web2wave servers (public IP or VPN)

Setup

Navigate to your project settings → Analytics tab
Scroll to the Remote Database Export section
Enable the feature by checking the checkbox
Fill in your database connection details:
- Connection Type: Select PostgreSQL, MySQL, MariaDB or BigQuery
- Host: Your database server hostname or IP address
- Port: Database port (default: 3306 for MySQL/MariaDB, 5432 for PostgreSQL)
- Username: Database username
- Password: Database password
- Database: Name of the target database
- Table Prefix: Optional prefix for table names (e.g., w2w_)
Click Save

The system will automatically create the required tables on first connection. If connection fails, you'll receive an email notification with error details.

Database Tables

`user_properties` Table

Stores user properties and attributes. Each property is stored as a separate row, allowing for flexible property management.

Table Structure:

Column	Type	Description
`id`	BIGINT (Primary Key)	Auto-incrementing unique identifier
`created_at`	TIMESTAMP	When the property was first set (defaults to current time)
`updated_at`	TIMESTAMP	When the property was last updated (auto-updates)
`project_domain`	VARCHAR(255)	Your project domain
`user_id`	VARCHAR(255)	Unique user identifier
`property`	VARCHAR(255)	Property name (e.g., `email`, `utm_source`, `custom_field`)
`value`	TEXT	Property value

Unique Constraint: The combination of project_domain, user_id, and property is unique. If a property is updated, the existing row is updated rather than creating a duplicate.

Example Data:

id | created_at          | updated_at          | project_domain    | user_id | property    | value
---|---------------------|---------------------|-------------------|---------|-------------|------------------
1  | 2024-01-15 10:30:00 | 2024-01-15 10:30:00 | example.com       | user123 | email       | user@example.com
2  | 2024-01-15 10:30:00 | 2024-01-15 10:30:00 | example.com       | user123 | utm_source  | google
3  | 2024-01-15 10:30:00 | 2024-01-15 10:30:00 | example.com       | user123 | utm_medium  | cpc
4  | 2024-01-15 10:31:00 | 2024-01-15 10:35:00 | example.com       | user123 | subscription_status | active

Common Properties Exported:

email - User email address
utm_source, utm_medium, utm_campaign, utm_content, utm_term - UTM parameters
user_country_code - User's country code
user_language - User's language preference
user_platform - Device platform (web, iOS, Android)
Custom properties defined in your project

`events_log` Table

Stores all quiz events and user interactions. Each event is stored as a separate row with full context.

Table Structure:

Column	Type	Description
`id`	BIGINT (Primary Key)	Auto-incrementing unique identifier
`created_at`	TIMESTAMP	When the event occurred
`updated_at`	TIMESTAMP	Last update time (usually same as created_at)
`user_id`	VARCHAR(255)	Unique user identifier
`project_domain`	VARCHAR(255)	Your project domain
`quiz_name`	VARCHAR(255)	Name of the quiz
`event_name`	VARCHAR(255)	Event type (e.g., `PageView`, `QuizStart`, `QuizComplete`, `Subscribe`)
`event_value`	VARCHAR(255)	Optional event value
`event_properties`	JSONB	Additional event data as JSON object
`url`	TEXT	Current page URL
`initial_url`	TEXT	First page URL in the session
`user_agent`	TEXT	Browser user agent string
`user_time`	VARCHAR(255)	User's local time when event occurred
`user_timezone`	VARCHAR(255)	User's timezone
`user_locale`	VARCHAR(255)	User's locale/language
`app_version`	VARCHAR(255)	Application version
`quiz_version`	VARCHAR(255)	Quiz version
`experiment`	VARCHAR(255)	A/B test experiment name (if applicable)
`experiment_group`	VARCHAR(255)	A/B test variant name (if applicable)
`additional_data`	JSONB	Additional custom data as JSON object
`ip`	VARCHAR(255)	User's IP address
`paywall_name`	VARCHAR(255)	Paywall name (if event is paywall-related)
`paywall_version`	VARCHAR(255)	Paywall version (if event is paywall-related)

Example Data:

id | created_at          | user_id | project_domain | quiz_name      | event_name     | event_value | url                    | ip
---|---------------------|---------|----------------|----------------|----------------|-------------|------------------------|---------------
1  | 2024-01-15 10:30:00 | user123 | example.com    | Personality Quiz | PageView      | null        | https://example.com/quiz | 192.168.1.1
2  | 2024-01-15 10:30:15 | user123 | example.com    | Personality Quiz | QuizStart     | null        | https://example.com/quiz | 192.168.1.1
3  | 2024-01-15 10:32:45 | user123 | example.com    | Personality Quiz | QuizComplete  | Result A    | https://example.com/result | 192.168.1.1
4  | 2024-01-15 10:33:20 | user123 | example.com    | Personality Quiz | Subscribe     | Monthly     | https://example.com/paywall | 192.168.1.1

Common Event Types: https://docs.web2wave.com/reference/events#/

How It Works

Real-time Export: As soon as a user property changes or an event occurs, it's queued for export to your database
Automatic Table Creation: Tables are created automatically on first connection with the correct schema
Upsert Logic: User properties use upsert logic (update if exists, insert if new) to prevent duplicates
Error Handling: If export fails, the system retries with exponential backoff. You'll receive email notifications for persistent failures

Query Examples

Get all properties for a specific user

SELECT property, value, updated_at
FROM user_properties
WHERE user_id = 'user123' AND project_domain = 'example.com'
ORDER BY updated_at DESC;

Get all events for a user in the last 7 days

SELECT event_name, event_value, created_at, url
FROM events_log
WHERE user_id = 'user123' 
  AND project_domain = 'example.com'
  AND created_at >= NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;

Get user journey (events in sequence)

SELECT 
  event_name,
  event_value,
  created_at,
  url
FROM events_log
WHERE user_id = 'user123' 
  AND project_domain = 'example.com'
ORDER BY created_at ASC;

Table Prefix

If you specify a table prefix (e.g., w2w_), the tables will be named:

w2w_user_properties
w2w_events_log

This is useful if you want to keep web2wave tables separate from your existing database schema.

Performance Considerations

Indexes: Consider adding indexes on frequently queried columns:

CREATE INDEX idx_user_properties_user_id ON user_properties(user_id, project_domain);
CREATE INDEX idx_events_log_user_id ON events_log(user_id, project_domain);
CREATE INDEX idx_events_log_created_at ON events_log(created_at);
CREATE INDEX idx_events_log_event_name ON events_log(event_name);

Data Volume: For high-traffic projects, consider periodic archiving of old events
Connection Pooling: Ensure your database can handle the connection load

Troubleshooting

Q: Connected database is not receiving any data.

A: Check if you enabled which tables must sync with your database in the "Tables to export" section.

Support

If you encounter issues or need assistance, please contact your account manager or reach out to support.