How to Query Your Data with the SQL Explorer

​

Part 1: Navigate to Explore

• Left panel: Schema browser with tables and columns
• Center panel: SQL editor with syntax highlighting
• Bottom panel: Results table with pagination and export options

​

Part 2: Browse the Schema

​

events

• timestamp: When event occurred (DateTime)
• type: Event category (LowCardinality String): ‘page’, ‘connect’, ‘transaction’, ‘decoded_log’, ‘track’, etc.
• event: Specific event name for ‘track’ and ‘decoded_log’ types (e.g., ‘swap’, ‘Transfer’)
• session_id: User’s session identifier
• anonymous_id: Anonymous user ID (for unconnected wallets)
• address: Connected wallet address (null if not connected)
• page_path: Page path (e.g., /swap)
• page_url: Full page URL
• page_title: Page title
• referrer: Referrer header
• referrer_url: Referrer URL
• utm_source, utm_medium, utm_campaign: Attribution data
• properties: JSON string with event-specific data (chain_id, tx_hash, etc. stored here)
• location: Country/geo (LowCardinality String)
• device: Device type (LowCardinality String)
• browser: Browser name (LowCardinality String)

​

users

• address: Primary key (wallet address)
• first_seen: First activity timestamp (SimpleAggregateFunction)
• last_seen: Most recent activity (SimpleAggregateFunction)
• num_sessions: Total sessions (AggregateFunction - access via uniqMerge(num_sessions))
• revenue: Total revenue (SimpleAggregateFunction sum)
• volume: Total volume (SimpleAggregateFunction sum)
• points: Total points (SimpleAggregateFunction sum)
• activity_dates: Lifecycle calculation data (AggregateFunction)
• first_utm_source, last_utm_source, etc.: Attribution (AggregateFunction - access via argMinMerge/argMaxMerge)

​

anonymous_users

• anonymous_id: Primary key
• first_seen, last_seen: Activity timestamps
• num_sessions: Total sessions (AggregateFunction - access via uniqMerge(num_sessions))

​

Part 3: Write Your First Query

SELECT 
  count(*) as event_count,
  count(DISTINCT address) as unique_wallets
FROM events
WHERE timestamp >= now() - INTERVAL 7 DAY

• count(*): Total rows
• count(DISTINCT column): Unique values
• INTERVAL 7 DAY: Time span
• WHERE: Filter rows
• GROUP BY: Aggregate by column
• ORDER BY: Sort results

​

Part 4: 10 Essential Queries

​

Query 1: Daily Active Users

SELECT 
  toDate(timestamp) as date,
  count(DISTINCT address) as daily_active_users
FROM events
WHERE timestamp >= now() - INTERVAL 30 DAY
GROUP BY date
ORDER BY date DESC

​

Query 2: Top Pages by Visits

SELECT 
  page_path,
  count(*) as visits,
  count(DISTINCT address) as unique_users
FROM events
WHERE type = 'page'
GROUP BY page_path
ORDER BY visits DESC
LIMIT 10

​

Query 3: Wallet Connect Rate

SELECT 
  connected_wallets,
  total_visitors,
  round(100.0 * connected_wallets / total_visitors, 2) as connect_rate_percent
FROM (
  SELECT 
    count(DISTINCT case when type = 'connect' then address end) as connected_wallets,
    count(DISTINCT anonymous_id) as total_visitors
  FROM events
)

​

Query 4: Transaction Volume by Day

SELECT 
  toDate(timestamp) as date,
  count(*) as transaction_count,
  count(DISTINCT address) as transacting_wallets
FROM events
WHERE type = 'decoded_log' AND event IN ('Swap', 'Transfer', 'Approve')
GROUP BY date
ORDER BY date DESC

​

Query 5: Users by Referral Source

SELECT
  utm_source,
  count(*) as users
FROM (
  SELECT
    address,
    argMaxMerge(last_utm_source) as utm_source
  FROM users
  GROUP BY address
)
WHERE utm_source != ''
GROUP BY utm_source
ORDER BY users DESC

​

Query 6: Average Sessions Before First Transaction

SELECT 
  avg(sessions) as avg_sessions
FROM (
  SELECT
    address,
    uniqMerge(num_sessions) as sessions
  FROM users
  WHERE address IN (
    SELECT DISTINCT address 
    FROM events 
    WHERE type = 'decoded_log' AND event IN ('Swap', 'Transfer')
  )
  GROUP BY address
)

​

Query 7: New vs Returning Users

SELECT 
  user_type,
  count(*) as count
FROM (
  SELECT 
    address,
    case 
      when uniqMerge(num_sessions) = 1 then 'New'
      when uniqMerge(num_sessions) <= 10 then 'Returning'
      else 'Power User'
    end as user_type
  FROM users
  WHERE first_seen >= now() - INTERVAL 30 DAY
  GROUP BY address
)
GROUP BY user_type

​

Query 8: Whale Analysis

SELECT 
  address,
  sum(volume) as total_volume,
  sum(revenue) as total_revenue,
  uniqMerge(num_sessions) as sessions
FROM users
WHERE volume > 0
GROUP BY address
ORDER BY total_volume DESC
LIMIT 10

​

Query 9: Volume by UTM Campaign

SELECT 
  utm_source,
  utm_campaign,
  count(DISTINCT case when type = 'decoded_log' AND event = 'Swap' then address end) as converters,
  count(*) as swap_count
FROM events
WHERE type = 'decoded_log' AND event = 'Swap' AND utm_source IS NOT NULL
GROUP BY utm_source, utm_campaign
ORDER BY swap_count DESC

​

Query 10: Hourly Activity Heatmap

SELECT 
  toHour(timestamp) as hour_of_day,
  count(*) as events,
  count(DISTINCT address) as active_wallets
FROM events
WHERE timestamp >= now() - INTERVAL 7 DAY
GROUP BY hour_of_day
ORDER BY hour_of_day

​

Part 5: Visualize Results

• Line Chart: Trends over time
• Bar Chart: Comparisons across categories
• Pie Chart: Proportions
• Number Card: Single metric
• Table: Raw data

​

Part 6: Export Results as CSV

• Reports to stakeholders
• Import into Excel for further analysis
• Feed into other tools (BI dashboards, email lists, etc.)

​

Part 7: Ask AI to Generate SQL

​

Part 8: Connect BI Tools

• Metabase
• Grafana
• Tableau
• Looker
• Power BI
• Custom integrations

1. In Metabase, click Settings > Databases > Add Database
2. Choose “ClickHouse”
3. Paste your Formo ClickHouse credentials (provided in workspace settings)
4. Create queries and dashboards in Metabase that query your Formo data

​

Best Practices

​

Click Schema Columns to Insert

​

Use CTEs for Readability

WITH first_transactions AS (
  SELECT
    address,
    min(timestamp) as first_tx_time
  FROM events
  WHERE type = 'decoded_log' AND event = 'Swap'
  GROUP BY address
)
SELECT
  address,
  first_tx_time
FROM first_transactions
ORDER BY first_tx_time DESC

​

Use Autocomplete

​

Save Query Results as Charts

​

Filter for Performance

​

Next Steps

• See How to Build a Custom Dashboard for dashboard best practices
• Learn How to Analyze Conversion Funnels for funnels
• Explore Attribution Guide for UTM analysis

​

FAQ

SELECT e.type, e.event, u.address
FROM events e
JOIN users u ON e.address = u.address