Export events & user_properties to external DB

Export your quiz analytics data in real-time to your own PostgreSQL, MySQL, or MariaDB 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:

1. User Properties - User attributes and custom properties (email, UTM parameters, custom fields, etc.)
2. 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, or MariaDB
• Network Access: Your database must be accessible from Web2Wave servers (public IP or VPN)

Setup

1. Navigate to your project settings → Analytics tab

2. Scroll to the Remote Database Export section

3. Enable the feature by checking the checkbox

4. Fill in your database connection details:

   • Connection Type: Select PostgreSQL, MySQL, or MariaDB
   • 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_)

5. 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:

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       | [email protected]
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:

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

1. Real-time Export: As soon as a user property changes or an event occurs, it's queued for export to your database
2. Automatic Table Creation: Tables are created automatically on first connection with the correct schema
3. Upsert Logic: User properties use upsert logic (update if exists, insert if new) to prevent duplicates
4. 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:

  SQL

  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

Support

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