API Reference

Access your analytics data programmatically with Databuddy's REST API. All endpoints require authentication and are rate-limited for security.

Try it live! Test all these endpoints interactively in our API Playground with real data and see instant responses. Perfect for exploring query types and understanding the API structure.

Authentication

All API requests require authentication using either session cookies (for browser-based requests) or API keys (for programmatic access). The API supports both authentication methods seamlessly.

For browser-based requests, use your session cookies from the dashboard:

bash

curl -H "Cookie: your-session-cookie" \
https://api.databuddy.cc/v1/query/websites

API Key Authentication

For programmatic access, use your API key in the x-api-key header:

bash

curl -H "x-api-key: dbdy_your_api_key_here" \
https://api.databuddy.cc/v1/query/websites

API Key Scopes:

read:analytics - Read analytics data and query types
write:custom-sql - Execute custom SQL queries
read:data - Access specific website data (requires website-specific or global access)

API Key Access Levels:

Global Access: Access all websites in your account/organization
Website-Specific Access: Access only specified websites

Base URLs

Analytics API: https://api.databuddy.cc/v1
Event Tracking (Basket): https://basket.databuddy.cc

Analytics Query API

Get Accessible Websites

Get all websites you have access to through your authentication method.

http

GET /v1/query/websites

Authentication: Requires session cookie or API key with appropriate access.

Response:

json

{
"success": true,
"websites": [
  {
    "id": "web_123",
    "name": "My Website",
    "domain": "example.com",
    "isPublic": false,
    "createdAt": "2024-01-01T00:00:00.000Z"
  }
],
"total": 1
}

Get Available Query Types

Get all available query types and their configurations.

http

GET /v1/query/types

Parameters:

include_meta (optional, boolean) - Include detailed metadata about query types including descriptions, output fields, and visualization hints

Response:

json

{
"success": true,
"types": ["summary", "pages", "traffic", "devices", "geo", "errors", "performance", "sessions", "custom_events", "profiles", "links", "engagement"],
"configs": {
  "summary": {
    "allowedFilters": ["date", "country", "device", "browser"],
    "customizable": true,
    "defaultLimit": 100
  },
  "pages": {
    "allowedFilters": ["date", "path"],
    "customizable": true,
    "defaultLimit": 50
  },
  "browser_name": {
    "allowedFilters": ["date", "browser", "country"],
    "customizable": true,
    "defaultLimit": 100,
    "meta": {
      "title": "Browser Usage",
      "description": "Website traffic breakdown by browser type showing which browsers your visitors use most.",
      "category": "Technology",
      "tags": ["browsers", "technology", "devices", "compatibility"],
      "output_fields": [
        {
          "name": "name",
          "type": "string",
          "label": "Browser Name",
          "description": "The browser name (Chrome, Firefox, Safari, etc.)"
        },
        {
          "name": "pageviews",
          "type": "number",
          "label": "Pageviews",
          "description": "Total pageviews from this browser"
        }
      ],
      "default_visualization": "pie",
      "supports_granularity": ["hour", "day"],
      "version": "1.0"
    }
  }
}
}

Execute Dynamic Analytics Query

Execute analytics queries with advanced parameter support, including batch processing and flexible date ranges.

http

POST /v1/query

Query Parameters:

website_id (required) - The website ID to query
start_date or startDate (optional) - Default start date for all parameters
end_date or endDate (optional) - Default end date for all parameters
timezone (optional) - Timezone for date calculations

Single Query Request:

json

{
"id": "my-query-123",
"parameters": ["summary", "pages", "browser_name"],
"limit": 100,
"page": 1,
"filters": [
  {
    "field": "country",
    "op": "in",
    "value": ["US", "CA"]
  },
  {
    "field": "device_type",
    "op": "eq",
    "value": "desktop"
  }
],
"granularity": "daily"
}

Advanced Parameter Objects:

json

{
"id": "advanced-query",
"parameters": [
  "summary",
  {
    "name": "pages",
    "start_date": "2024-01-01",
    "end_date": "2024-01-07",
    "granularity": "hourly",
    "id": "pages-last-week"
  },
  {
    "name": "browser_name",
    "start_date": "2024-01-15",
    "end_date": "2024-01-31",
    "id": "browsers-recent"
  }
],
"limit": 50
}

Batch Query Request:

json

[
{
  "id": "query-1",
  "parameters": ["summary", "pages"],
  "limit": 100
},
{
  "id": "query-2", 
  "parameters": ["browser_name", "os_name"],
  "limit": 50,
  "filters": [
    {
      "field": "country",
      "op": "eq",
      "value": "US"
    }
  ]
}
]

Available Query Types:

summary - Overall website metrics and KPIs
pages - Page views and performance data
traffic - Traffic sources and referrers
browser_name - Browser usage analytics
os_name - Operating system analytics
device_types - Device category breakdown
countries - Geographic visitor data by country
cities - Geographic visitor data by city
errors - JavaScript errors and issues
performance - Web vitals and performance metrics
sessions - Session-based analytics
custom_events - Custom event tracking data
profiles - User profile analytics
links - Outgoing link tracking
engagement - User engagement metrics

Filter Operations:

eq - Equals
ne - Not equals
like - Contains (case-insensitive)
gt - Greater than
lt - Less than
in - In array
notIn - Not in array

Single Query Response:

json

{
"success": true,
"queryId": "my-query-123",
"data": [
  {
    "parameter": "summary",
    "success": true,
    "data": [
      {
        "date": "2024-01-01",
        "pageviews": 1250,
        "visitors": 890,
        "sessions": 1100
      }
    ]
  },
  {
    "parameter": "pages",
    "success": true,
    "data": [
      {
        "path": "/",
        "pageviews": 450,
        "visitors": 320
      }
    ]
  }
],
"meta": {
  "parameters": ["summary", "pages"],
  "total_parameters": 2,
  "page": 1,
  "limit": 100,
  "filters_applied": 2
}
}

Batch Query Response:

json

{
"success": true,
"batch": true,
"results": [
  {
    "success": true,
    "queryId": "query-1",
    "data": [...]
  },
  {
    "success": true,
    "queryId": "query-2", 
    "data": [...]
  }
]
}

Compile Query (Advanced)

Compile a query to see the generated SQL without executing it. Useful for debugging and understanding query structure.

http

POST /v1/query/compile

Query Parameters:

website_id (optional) - Website ID for domain-specific compilation

Request:

json

{
"projectId": "web_123",
"type": "summary",
"from": "2024-01-01",
"to": "2024-01-31",
"timeUnit": "day",
"filters": [
  {
    "field": "country",
    "op": "in",
    "value": ["US", "CA"]
  }
],
"limit": 100,
"offset": 0
}

Response:

json

{
"success": true,
"sql": "SELECT toDate(time) as date, count(*) as pageviews, count(distinct anonymous_id) as visitors FROM analytics.events WHERE time >= '2024-01-01' AND time <= '2024-01-31' AND country IN ('US', 'CA') GROUP BY date ORDER BY date LIMIT 100",
"parameters": {
  "projectId": "web_123",
  "from": "2024-01-01",
  "to": "2024-01-31",
  "filters": [{"field": "country", "op": "in", "value": ["US", "CA"]}]
}
}

New Feature! 🚀 Use properties.X syntax for easy JSON property extraction. Write properties.browser_name instead of JSONExtractString(properties, 'browser_name'). Supports type annotations like properties.user_id:int, properties.is_active:bool.

Execute Custom SQL Queries

Execute custom SQL queries directly against your ClickHouse analytics database with enterprise-grade security.

http

POST /v1/custom-sql/execute

Authentication:

Requires API key with write:custom-sql or read:analytics scope
API key must have read:data access to the specified clientId

Request:

json

{
"query": "SELECT count() as events FROM analytics.events WHERE time >= now() - INTERVAL 7 DAY",
"clientId": "your-client-id",
"parameters": {
  "customParam": "value"
}
}

Example with Properties.X Syntax:

json

{
"query": "SELECT properties.browser_name AS browser_name, properties.user_id:int AS user_id, count() as events FROM analytics.custom_events WHERE properties.browser_name IS NOT NULL GROUP BY properties.browser_name, properties.user_id:int ORDER BY events DESC LIMIT 10",
"clientId": "your-client-id"
}

Headers:

bash

curl -X POST https://api.databuddy.cc/v1/custom-sql/execute \
-H "Content-Type: application/json" \
-H "x-api-key: dbdy_your_api_key_here" \
-d '{"query": "SELECT count() FROM analytics.events", "clientId": "client_123"}'

Available Tables:

analytics.events - All tracked events and page views
analytics.errors - JavaScript errors and exceptions
analytics.custom_events - Custom event tracking data
analytics.web_vitals - Core Web Vitals performance metrics

Properties.X Syntax Helper: Databuddy provides a convenient syntax helper that automatically converts properties.X notation to ClickHouse JSONExtract functions:

-- Instead of writing:
SELECT JSONExtractString(properties, 'browser_name') AS browser_name FROM analytics.events

-- You can simply write:
SELECT properties.browser_name AS browser_name FROM analytics.events

Important: Always use AS aliases when selecting properties to get clean column names in your response. Without aliases, you'll get ugly column names like "JSONExtractString(properties, 'browser_name')" instead of "browser_name".

Supported Type Annotations:

properties.property_name → JSONExtractString(properties, 'property_name') (default)
properties.user_id:int → JSONExtractInt(properties, 'user_id')
properties.is_active:bool → JSONExtractBool(properties, 'is_active')
properties.score:float → JSONExtractFloat(properties, 'score')
properties.metadata:raw → JSONExtractRaw(properties, 'metadata')

Security Features:

Automatic client isolation - All queries automatically filtered by your API key's client access
Smart WHERE injection - Client filtering added to your queries without breaking syntax
SQL injection prevention with parameterized queries
Query complexity limits (max 5 SELECT statements, 2 UNION operations, 5000 characters)
Forbidden operations blocked (INSERT, UPDATE, DELETE, DROP, CREATE, ALTER, etc.)
Attack pattern detection for known injection techniques
Comments and multiple statements not allowed
Balanced parentheses validation
Properties.X syntax automatically transformed to secure JSONExtract calls

Response:

json

{
"success": true,
"data": [
  {
    "events": 15420
  }
],
"meta": {
  "rowCount": 1,
  "columns": ["events"],
  "executionTime": 0.045,
  "rowsRead": 15420,
  "clientId": "client_123",
  "apiKeyId": "key_456"
}
}

Get Custom SQL Schema

Get information about allowed tables, operations, and security constraints.

http

GET /v1/custom-sql/schema

Response:

json

{
"success": true,
"schema": {
  "allowedTables": [
    "analytics.events",
    "analytics.errors",
    "analytics.custom_events",
    "analytics.web_vitals"
  ],
  "allowedOperations": [
    "SELECT", "WITH", "FROM", "WHERE", "GROUP BY", "ORDER BY", "HAVING", "LIMIT", "OFFSET", "JOIN", "LEFT JOIN", "RIGHT JOIN", "INNER JOIN", "UNION", "UNION ALL", "JSONExtract", "JSONExtractString", "JSONExtractInt", "JSONExtractFloat", "JSONExtractBool", "JSONExtractRaw", "CASE", "WHEN", "THEN", "ELSE", "END", "AS", "AND", "OR", "NOT", "IN", "EXISTS", "BETWEEN", "LIKE", "ILIKE"
  ],
  "forbiddenOperations": [
    "INSERT", "UPDATE", "DELETE", "DROP", "CREATE", "ALTER", "TRUNCATE", "REPLACE", "MERGE", "CALL", "EXEC", "EXECUTE", "DECLARE", "SET", "USE", "SHOW", "DESCRIBE", "EXPLAIN", "ANALYZE", "OPTIMIZE", "REPAIR", "LOCK", "UNLOCK", "GRANT", "REVOKE", "COMMIT", "ROLLBACK", "SAVEPOINT", "RELEASE", "START TRANSACTION", "BEGIN", "INFORMATION_SCHEMA", "SYSTEM", "ADMIN", "SUPER", "FILE", "PROCESS", "RELOAD", "SHUTDOWN", "REFERENCES", "INDEX", "TRIGGER", "EVENT", "ROUTINE"
  ],
  "maxQueryLength": 5000,
  "maxNestedSelects": 5,
  "maxUnionOperations": 2,
  "parameterization": {
    "clientIdParameter": "{clientId:String}",
    "required": "All queries must use parameterized client filtering"
  },
  "propertiesSyntax": {
    "description": "Automatic JSONExtract transformation from properties.X syntax",
    "syntax": "properties.property_name[:type]",
    "supportedTypes": ["string", "int", "float", "bool", "raw"],
    "examples": [
      "properties.browser_name",
      "properties.user_id:int",
      "properties.is_active:bool",
      "properties.metadata:raw"
    ],
    "defaultType": "string (JSONExtractString)"
  }
}
}

Get Query Examples

Get example queries to help you get started with custom SQL.

http

GET /v1/custom-sql/examples

Response:

json

{
"success": true,
"examples": [
  {
    "name": "Monthly Events Count",
    "description": "Get monthly event counts for your client",
    "query": "SELECT toStartOfMonth(time) as month_start, count() as event_count FROM analytics.events WHERE time >= now() - INTERVAL 6 MONTH GROUP BY month_start ORDER BY month_start DESC"
  },
  {
    "name": "Top Pages by Views",
    "description": "Get most popular pages",
    "query": "SELECT path, count() as page_views, uniq(session_id) as unique_sessions FROM analytics.events WHERE time >= now() - INTERVAL 30 DAY AND event_name = 'page_view' GROUP BY path ORDER BY page_views DESC LIMIT 10"
  },
  {
    "name": "Browser Analytics (with properties.X syntax)",
    "description": "Analyze browser usage using properties.X syntax",
    "query": "SELECT properties.browser_name, count() as events, uniq(anonymous_id) as unique_users FROM analytics.events WHERE time >= now() - INTERVAL 7 DAY AND properties.browser_name IS NOT NULL GROUP BY properties.browser_name ORDER BY events DESC"
  },
  {
    "name": "User Analytics with Typed Properties",
    "description": "Analyze user behavior with typed property extraction",
    "query": "SELECT properties.user_id:int as user_id, properties.is_premium:bool as is_premium, properties.session_duration:float as session_duration, count() as total_events FROM analytics.events WHERE time >= now() - INTERVAL 30 DAY AND properties.user_id:int IS NOT NULL GROUP BY properties.user_id:int, properties.is_premium:bool, properties.session_duration:float ORDER BY total_events DESC LIMIT 20"
  },
  {
    "name": "Error Events Analysis",
    "description": "Analyze error events",
    "query": "SELECT url, count() as error_count FROM analytics.errors WHERE time >= now() - INTERVAL 7 DAY GROUP BY url ORDER BY error_count DESC LIMIT 10"
  }
]
}

Error Responses:

json

{
"success": false,
"error": "API key does not have access to client ID: client_123",
"code": "CLIENT_ACCESS_DENIED"
}

Common Error Codes:

AUTH_REQUIRED - API key missing or invalid
INSUFFICIENT_SCOPE - API key lacks required scopes
CLIENT_ACCESS_DENIED - No access to specified clientId
FORBIDDEN_OPERATION - Query contains forbidden SQL operations
QUERY_TOO_COMPLEX - Query exceeds complexity limits
ATTACK_PATTERN_DETECTED - Query matches known attack patterns

AI Assistant

Stream Analytics Query

Use natural language to query your analytics data with streaming responses.

http

POST /v1/assistant/stream

Request:

json

{
"websiteId": "web_123",
"message": "Show me the top pages by pageviews last week",
"context": {
  "dateRange": {
    "start": "2024-01-01",
    "end": "2024-01-07"
  }
}
}

Response (Server-Sent Events):

json

data: {"type": "thinking", "content": "Analyzing your page views..."}

data: {"type": "query", "content": "SELECT path, COUNT(*) as pageviews FROM analytics.events..."}

data: {"type": "complete", "content": "Here are your top pages:", "data": {"results": [{"path": "/", "pageviews": 1250}]}}

Data Export

Export Analytics Data

Export analytics data in various formats (CSV, JSON).

http

POST /v1/export

Request:

json

{
"websiteId": "web_123",
"type": "summary",
"format": "csv",
"startDate": "2024-01-01",
"endDate": "2024-01-31",
"timezone": "UTC"
}

Response: Returns a downloadable file in the requested format.

Event Tracking

Send Custom Events

Track custom events programmatically using the basket endpoint.

http

POST basket.databuddy.cc/?client_id={website_id}

Single Custom Event Request:

json

{
"type": "custom",
"name": "purchase",
"anonymousId": "anon_user_123",
"sessionId": "session_456",
"timestamp": 1704067200000,
"properties": {
  "value": 99.99,
  "currency": "USD",
  "product_id": "prod_123"
}
}

Minimal Custom Event (without session/user tracking):

json

{
"type": "custom",
"name": "newsletter_signup",
"properties": {
  "source": "footer_form"
}
}

Batch Custom Events Request:

http

POST basket.databuddy.cc/batch?client_id={website_id}

json

[
{
  "type": "custom",
  "name": "purchase",
  "anonymousId": "anon_user_123",
  "sessionId": "session_456",
  "timestamp": 1704067200000,
  "properties": {
    "value": 99.99,
    "currency": "USD",
    "product_id": "prod_123"
  }
},
{
  "type": "custom",
  "name": "signup",
  "anonymousId": "anon_user_124",
  "sessionId": "session_457",
  "timestamp": 1704067260000,
  "properties": {
    "plan": "premium",
    "source": "landing_page"
  }
}
]

Required Fields:

type: Must be "custom"
name: Event name (1-128 characters)

Optional Fields:

anonymousId: Anonymous user identifier (max 128 characters) - not needed for all use cases
sessionId: Session identifier (max 128 characters) - not needed for all use cases
timestamp: Unix timestamp in milliseconds (defaults to current time)
properties: JSON object with custom event properties

Response:

json

{
"status": "success",
"type": "custom"
}

Batch Response:

json

{
"status": "success",
"batch": true,
"processed": 2,
"results": [
  {
    "status": "success",
    "type": "custom",
    "eventId": "evt_12345"
  },
  {
    "status": "success", 
    "type": "custom",
    "eventId": "evt_12346"
  }
]
}

Health Check

API Health Status

Check the health status of the API and its dependencies.

http

GET /health

Response:

json

{
"clickhouse": true,
"database": true,
"redis": true,
"success": true,
"version": "1.0.0",
"timestamp": "2024-01-01T12:00:00.000Z"
}

Rate Limits

API endpoints have different rate limits based on their computational cost and your subscription plan:

Plan-Based Rate Limits

Rate limits vary by your subscription tier:

Free: 50 requests per 10 seconds (300/minute)
Hobby: 100 requests per 10 seconds (600/minute)
Pro: 200 requests per 10 seconds (1,200/minute)
Scale: 500 requests per 10 seconds (3,000/minute)

Endpoint-Specific Rate Limits

Certain endpoints have additional restrictions:

Public endpoints: 100 requests per minute
Authentication endpoints: 30 requests per minute
Custom SQL: 30 requests per minute (heavy operations)

Rate limit headers are included in responses:

http

X-RateLimit-Limit: 100
X-RateLimit-Remaining: 95
X-RateLimit-Reset: 1640995200

Error Handling

All errors follow a consistent format:

json

{
"success": false,
"error": "Website not found or access denied",
"code": "WEBSITE_NOT_FOUND"
}

Common Error Codes:

AUTH_REQUIRED - Authentication required (missing session cookie or API key)
WEBSITE_NOT_FOUND - Website not found or no access
INVALID_QUERY_TYPE - Unknown query type
VALIDATION_ERROR - Request validation failed
RATE_LIMIT_EXCEEDED - Too many requests
INTERNAL_SERVER_ERROR - Server error occurred
CLIENT_ACCESS_DENIED - API key does not have access to specified resource
INSUFFICIENT_SCOPE - API key lacks required scopes
FORBIDDEN_OPERATION - Operation not allowed with current permissions

Practical Examples

Getting Started Workflow

Get your accessible websites:

bash

curl -H "x-api-key: dbdy_your_api_key_here" \
https://api.databuddy.cc/v1/query/websites

Explore available query types:

bash

curl -H "x-api-key: dbdy_your_api_key_here" \
"https://api.databuddy.cc/v1/query/types?include_meta=true"

Execute a simple query:

bash

curl -X POST -H "x-api-key: dbdy_your_api_key_here" \
-H "Content-Type: application/json" \
-d '{
  "id": "dashboard-summary",
  "parameters": ["summary", "pages", "browser_name"],
  "limit": 10
}' \
"https://api.databuddy.cc/v1/query?website_id=web_123&start_date=2024-01-01&end_date=2024-01-31&timezone=UTC"

Advanced Multi-Period Analysis

Compare different time periods with parameter-specific date ranges:

bash

curl -X POST -H "x-api-key: dbdy_your_api_key_here" \
-H "Content-Type: application/json" \
-d '{
  "id": "period-comparison",
  "parameters": [
    {
      "name": "summary",
      "start_date": "2024-01-01",
      "end_date": "2024-01-15",
      "id": "first-half-jan"
    },
    {
      "name": "summary", 
      "start_date": "2024-01-16",
      "end_date": "2024-01-31",
      "id": "second-half-jan"
    }
  ]
}' \
"https://api.databuddy.cc/v1/query?website_id=web_123&timezone=America/New_York"

Batch Processing Multiple Websites

Process multiple queries for different websites simultaneously:

bash

curl -X POST -H "x-api-key: dbdy_your_api_key_here" \
-H "Content-Type: application/json" \
-d '[
  {
    "id": "website-1-analysis",
    "parameters": ["summary", "pages"],
    "filters": [{"field": "country", "op": "eq", "value": "US"}]
  },
  {
    "id": "website-2-analysis", 
    "parameters": ["browser_name", "os_name"],
    "limit": 20
  }
]' \
"https://api.databuddy.cc/v1/query?website_id=web_123&start_date=2024-01-01&end_date=2024-01-31"

Introduction

Implementation

Integrations

Dashboard & Analytics

Privacy & Compliance

Performance

Security

Authentication

API Key Authentication

Base URLs

Analytics Query API

Get Accessible Websites

Get Available Query Types

Execute Dynamic Analytics Query

Compile Query (Advanced)

Custom SQL API

Execute Custom SQL Queries

Get Custom SQL Schema

Get Query Examples

AI Assistant

Stream Analytics Query

Data Export

Export Analytics Data

Event Tracking

Send Custom Events

Health Check

API Health Status

Rate Limits

Plan-Based Rate Limits

Endpoint-Specific Rate Limits

Error Handling

Practical Examples

Getting Started Workflow

Advanced Multi-Period Analysis

Batch Processing Multiple Websites

On this page