Query Studio Schema Reference

Complete reference for all queryable tables available in Zenovay Query Studio. Use this guide to explore the database schema, understand column types, and write accurate SQL queries.

Core Analytics

visitors

Individual visitor records with geolocation, device information, UTM tracking parameters, and behavioral engagement scores. This is the primary table for granular visitor-level analysis.

analytics_daily

Pre-aggregated daily statistics for fast dashboard queries. Use this table instead of visitors when you need daily summaries — it is significantly faster for date-range reports.

page_views

Individual page view events with performance and engagement metrics. Each row represents a single page load by a visitor within a session.

live_visitors

Currently active visitors on your website. This table is updated in real-time and reflects visitors who have been active within the last few minutes.

Revenue & Payments

payments

Payment transactions synced from connected payment providers (Stripe, LemonSqueezy, Polar). Each row represents a single transaction.

revenue_attribution

Revenue attributed to marketing channels over defined time periods. Use this table for channel ROI analysis and marketing performance reporting.

identified_users

Known users who have been identified via the visitor identification API. Includes subscription status and lifetime revenue data.

Goals & Funnels

custom_goals

Goal definitions configured in the dashboard. Each goal tracks a specific user action such as visiting a URL or triggering a custom event.

goal_completions

Individual goal conversion events. Each row represents a single instance of a visitor completing a defined goal.

funnels

Funnel definitions for multi-step conversion tracking.

funnel_steps

Individual steps within a funnel, ordered sequentially. Join with funnels on funnel_id to get the full funnel configuration.

Errors & Performance

error_groups

Grouped error types with occurrence counts and severity. Errors are automatically deduplicated and grouped by type and message.

error_occurrences

Individual error events with full context including stack traces and environment details.

performance_metrics

Core Web Vitals measurements (LCP, CLS, INP) collected from real user monitoring. Each row is a single measurement from a page load.

Uptime Monitoring

uptime_checks

Individual uptime check results from the monitoring system. Each row represents a single health check ping to your website.

uptime_daily

Daily uptime aggregates. Use this for SLA reporting and trend analysis instead of querying individual checks.

User Behavior

heatmap_data

Aggregated click and scroll heatmap data on a grid overlay. Each row represents a grid cell with accumulated interaction counts.

heatmap_sessions

Session-level summaries of heatmap interaction data per page.

replay_sessions

Session replay recording metadata. Each row represents a recorded browsing session that can be replayed in the dashboard.

frustration_events

Detected user frustration signals including rage clicks, dead clicks, and error clicks.

scroll_depth_stats

Aggregated scroll depth statistics by page, showing how far visitors scroll on each page.

session_history

Completed session summaries with engagement and conversion metrics.

B2B & Companies

b2b_companies

Identified companies visiting your website, enriched with firmographic data from IP-to-company resolution.

b2b_company_visits

Company-level visit aggregates. Join with b2b_companies on company_id = id for full company details.

Marketing & Campaigns

campaign_performance

UTM campaign performance metrics aggregated by date. Use this for marketing ROI analysis and campaign comparison.

gsc_keyword_data

Google Search Console keyword performance data synced via the GSC integration. Shows how your pages perform in Google search results.

Custom Events

custom_events

User-defined tracking events sent via the custom events API. Each row represents a single event occurrence with optional properties and revenue data.

events

System-level events tracked automatically by the Zenovay tracker. Includes page views, clicks, form submissions, and other built-in event types.

Other

chart_notes

Dashboard annotations attached to specific dates. These appear as markers on time-series charts to provide context for data trends.

alerts

Configured alert rules that trigger notifications when specified conditions are met.

websites

Website configuration records. Each tracked website has an entry here with its settings and tracking code.

Example Queries

Core Analytics

SELECT page_url, COUNT(*) as visits, AVG(time_on_page) as avg_time
FROM visitors
WHERE website_id = 'YOUR_WEBSITE_ID'
AND visited_at >= date_trunc('month', now())
GROUP BY page_url
ORDER BY visits DESC
LIMIT 10;

SELECT date, visitors, page_views, bounce_rate
FROM analytics_daily
WHERE website_id = 'YOUR_WEBSITE_ID'
AND date >= now() - interval '30 days'
ORDER BY date ASC;

Revenue & Payments

SELECT marketing_channel, SUM(amount) as total_revenue, COUNT(*) as transactions
FROM payments
WHERE website_id = 'YOUR_WEBSITE_ID'
AND status = 'succeeded'
AND created_at >= date_trunc('month', now())
GROUP BY marketing_channel
ORDER BY total_revenue DESC;

SELECT email, name, total_revenue, subscription_status, subscription_plan
FROM identified_users
WHERE website_id = 'YOUR_WEBSITE_ID'
ORDER BY total_revenue DESC
LIMIT 20;

Goals & Funnels

SELECT utm_source, COUNT(*) as completions, SUM(completion_value) as total_value
FROM goal_completions
WHERE website_id = 'YOUR_WEBSITE_ID'
AND completed_at >= date_trunc('week', now())
GROUP BY utm_source
ORDER BY completions DESC;

Errors & Performance

SELECT error_type, error_message, occurrence_count, user_count, severity, last_seen
FROM error_groups
WHERE website_id = 'YOUR_WEBSITE_ID'
AND status = 'open'
ORDER BY occurrence_count DESC
LIMIT 10;

SELECT page_url,
ROUND(AVG(lcp_value), 0) as avg_lcp_ms,
ROUND(AVG(cls_value), 3) as avg_cls,
ROUND(AVG(inp_value), 0) as avg_inp_ms
FROM performance_metrics
WHERE website_id = 'YOUR_WEBSITE_ID'
AND recorded_at >= now() - interval '7 days'
GROUP BY page_url
ORDER BY avg_lcp_ms DESC
LIMIT 10;

Uptime Monitoring

SELECT date, uptime_percentage, avg_response_time_ms, incidents_count
FROM uptime_daily
WHERE website_id = 'YOUR_WEBSITE_ID'
AND date >= now() - interval '30 days'
ORDER BY date ASC;

User Behavior

SELECT page_url, frustration_type, COUNT(*) as occurrences
FROM frustration_events
WHERE website_id = 'YOUR_WEBSITE_ID'
AND timestamp >= now() - interval '7 days'
GROUP BY page_url, frustration_type
ORDER BY occurrences DESC
LIMIT 10;

SELECT visitor_id, duration_ms / 1000 as duration_seconds, total_events, started_at
FROM replay_sessions
WHERE website_id = 'YOUR_WEBSITE_ID'
AND duration_ms > 120000
ORDER BY started_at DESC
LIMIT 20;

B2B & Companies

SELECT c.name, c.industry, c.employee_count, v.total_visits, v.engagement_score, v.last_visit
FROM b2b_company_visits v
JOIN b2b_companies c ON c.id = v.company_id
WHERE v.website_id = 'YOUR_WEBSITE_ID'
ORDER BY v.engagement_score DESC
LIMIT 15;

Marketing & Campaigns

SELECT utm_source, utm_campaign, SUM(visitors) as total_visitors,
SUM(conversions) as total_conversions, SUM(revenue) as total_revenue,
ROUND(AVG(bounce_rate), 3) as avg_bounce_rate
FROM campaign_performance
WHERE website_id = 'YOUR_WEBSITE_ID'
AND date >= now() - interval '30 days'
GROUP BY utm_source, utm_campaign
ORDER BY total_revenue DESC;

SELECT query, SUM(clicks) as total_clicks, SUM(impressions) as total_impressions,
ROUND(AVG(ctr), 4) as avg_ctr, ROUND(AVG(position), 1) as avg_position
FROM gsc_keyword_data
WHERE website_id = 'YOUR_WEBSITE_ID'
AND date >= now() - interval '28 days'
GROUP BY query
ORDER BY total_clicks DESC
LIMIT 20;

Custom Events

SELECT event_name, event_category, COUNT(*) as occurrences, SUM(revenue) as total_revenue
FROM custom_events
WHERE website_id = 'YOUR_WEBSITE_ID'
AND properties->>'source' IS NOT NULL
GROUP BY event_name, event_category
ORDER BY occurrences DESC
LIMIT 20;

Other

SELECT note_date, content
FROM chart_notes
WHERE website_id = 'YOUR_WEBSITE_ID'
AND note_date >= date_trunc('month', now())
AND show_on_chart = true
ORDER BY note_date ASC;

SELECT name, alert_type, status, conditions
FROM alerts
WHERE website_id = 'YOUR_WEBSITE_ID'
AND status = 'active'
ORDER BY name;