Skip to main content

Reporting Suite API Contracts & Schemas

This reference document defines the programmatic query contracts, request payloads, response structures, and underlying telemetry schemas for DriveMetaData's reporting suite:

  • Funnel Analytics API — Saved and dynamic inline conversion funnel execution.
  • Pivot Aggregation API — Multi-dimensional OLAP query engine, filtering, and Cardinality Quarantine error specifications.
  • Target Attribution Telemetry & KPIs — ClickHouse target_attribution_logs_ad_data_v4 schema, financial endpoints, and exact mathematical KPI formulas.

1. Global Tenant & Security Headers​

All reporting endpoints require tenant context headers. If tenant headers are missing, the gateway rejects the request with HTTP 403 Forbidden.

X-Client-ID: 1635
X-Workspace-ID: 1637
Content-Type: application/json
Authorization: Bearer <jwt_access_token>

2. Funnel Analytics API​

The Funnel API calculates step-by-step conversion progression, drop-off rates, step latency, and cohort trends.

2.1 Endpoints​

MethodEndpointPurpose
POST/api/v2/analytics/funnel/queryExecutes a saved funnel report by ID and version.
POST/api/v2/analytics/funnel/{dynamicId}/queryExecutes an unsaved, ad-hoc inline funnel definition.

2.2 Inline Query Request Contract (POST /api/v2/analytics/funnel/{dynamicId}/query)​

{
  "query": {
    "name": "Acquisition to Purchase Journey",
    "conversionWindow": {
      "value": 14,
      "unit": "DAYS"
    },
    "sequenceMode": "LOOSE",
    "countingMethod": "UNIQUE_USERS",
    "breakdownDimension": "channel",
    "steps": [
      {
        "id": "step-1",
        "stepIndex": 1,
        "name": "Ad Click",
        "eventName": "ad_click",
        "filters": [
          {
            "field": "channel",
            "operator": "IN",
            "values": ["Google Ads", "Meta Ads", "TikTok Ads"]
          }
        ]
      },
      {
        "id": "step-2",
        "stepIndex": 2,
        "name": "Landing Page View",
        "eventName": "page_view",
        "filters": [
          {
            "field": "page_path",
            "operator": "STARTS_WITH",
            "values": ["/products/"]
          }
        ]
      },
      {
        "id": "step-3",
        "stepIndex": 3,
        "name": "Checkout Started",
        "eventName": "checkout_started",
        "filters": []
      },
      {
        "id": "step-4",
        "stepIndex": 4,
        "name": "Purchase Completed",
        "eventName": "order_completed",
        "filters": [
          {
            "field": "order_value",
            "operator": "GREATER_THAN",
            "values": ["0"]
          }
        ]
      }
    ]
  },
  "reportQuery": {
    "dateRange": {
      "startDate": "2026-08-01",
      "endDate": "2026-08-31"
    }
  }
}

2.3 Funnel Query Response Structure​

{
  "funnelId": "dynamic-f71b-90a2",
  "funnelVersion": 0,
  "overallConversionRate": 3.42,
  "totalEnteredUsers": 128500,
  "totalConvertedUsers": 4395,
  "steps": [
    {
      "stepIndex": 1,
      "stepName": "Ad Click",
      "enteredCount": 128500,
      "convertedCount": 128500,
      "dropoffCount": 0,
      "stepConversionRate": 100.0,
      "cumulativeConversionRate": 100.0,
      "medianTimeSeconds": 0
    },
    {
      "stepIndex": 2,
      "stepName": "Landing Page View",
      "enteredCount": 128500,
      "convertedCount": 118220,
      "dropoffCount": 10280,
      "stepConversionRate": 92.0,
      "cumulativeConversionRate": 92.0,
      "medianTimeSeconds": 85
    },
    {
      "stepIndex": 3,
      "stepName": "Checkout Started",
      "enteredCount": 118220,
      "convertedCount": 18915,
      "dropoffCount": 99305,
      "stepConversionRate": 16.0,
      "cumulativeConversionRate": 14.72,
      "medianTimeSeconds": 14400
    },
    {
      "stepIndex": 4,
      "stepName": "Purchase Completed",
      "enteredCount": 18915,
      "convertedCount": 4395,
      "dropoffCount": 14520,
      "stepConversionRate": 23.23,
      "cumulativeConversionRate": 3.42,
      "medianTimeSeconds": 28800
    }
  ],
  "trendBuckets": [
    {
      "date": "2026-08-01",
      "enteredCount": 4200,
      "convertedCount": 142,
      "conversionRate": 3.38
    }
  ],
  "comparisons": [
    {
      "dimensionValue": "Google Ads",
      "enteredCount": 65000,
      "convertedCount": 2600,
      "overallConversionRate": 4.0
    },
    {
      "dimensionValue": "Meta Ads",
      "enteredCount": 45000,
      "convertedCount": 1350,
      "overallConversionRate": 3.0
    }
  ]
}

3. Pivot Report Aggregation API​

The Pivot API powers multi-dimensional cross-tabulated queries with hierarchical row groupings and pivoted column labels.

3.1 Endpoints​

MethodEndpointPurpose
POST/api/v2/reports/pivot/queryExecutes an ad-hoc or saved OLAP pivot aggregation.
POST/api/v2/reports/pivot/exportStreams results formatted as a CSV spreadsheet download.

3.2 Query Request Payload​

{
  "dataModelId": "dm_ad_attribution_v4",
  "rowGroups": ["country", "campaign_name"],
  "columnLabels": ["channel"],
  "measures": [
    { "field": "event_id", "aggregation": "COUNT", "alias": "total_events" },
    { "field": "order_value", "aggregation": "SUM", "alias": "gross_revenue" },
    { "field": "order_value", "aggregation": "AVG", "alias": "aov" }
  ],
  "filters": [
    {
      "field": "country",
      "operator": "IN",
      "values": ["IN", "US", "GB", "AE"]
    },
    {
      "field": "channel",
      "operator": "NOT_EQUALS",
      "values": ["Unknown"]
    }
  ],
  "dateRange": {
    "preset": "30D",
    "startDate": "2026-08-15",
    "endDate": "2026-09-14"
  },
  "options": {
    "showRowSubtotals": true,
    "showColumnSubtotals": true
  },
  "pagination": {
    "page": 1,
    "pageSize": 100
  }
}

3.3 Cardinality Quarantine Error Specification (HTTP 422)​

When an incoming query's Cartesian combination of rows and columns exceeds system safety thresholds, the query halts immediately:

{
  "error": "Unprocessable Entity",
  "code": "QUARANTINE_CARDINALITY_EXCEEDED",
  "message": "The requested table configuration produces an estimated 480,000 cells, exceeding the safety ceiling of 250,000 cells.",
  "details": {
    "projectedCells": 480000,
    "maxThreshold": 250000,
    "estimatedRows": 80000,
    "estimatedColumns": 6,
    "suggestedActions": [
      "Remove high-cardinality dimension 'dmd_uid' from Row Groups",
      "Add a selective filter on 'country' or 'channel'",
      "Narrow the selected date range"
    ]
  }
}

4. Target Attribution Telemetry & Schemas​

4.1 ClickHouse Table: target_attribution_logs_ad_data_v4​

CREATE TABLE target_attribution_logs_ad_data_v4 (
    -- Time & Date Partitioning
    timestamp DateTime64(3, 'UTC'),
    touchpoint_timestamp Nullable(DateTime64(3, 'UTC')),
    event_datet Date,
    touchpoint_datet Nullable(Date),

    -- Multi-Tenancy & Application
    client_id UInt32,
    workspace_id UInt32,
    app_id LowCardinality(String),
    event_code LowCardinality(String),
    event_type LowCardinality(String),
    request_from LowCardinality(String),

    -- Attribution Mechanics
    attribution_model LowCardinality(String),
    lookback_window_seconds UInt32,
    touchpoint_type LowCardinality(String),  -- 'click', 'impression'
    is_view_through UInt8,
    is_deterministic UInt8,

    -- Identity Graph
    dmd_uid String,
    external_user_id String,
    session_id String,
    anonymous_id String,
    fbp String,
    pub_click_id String,

    -- Supply & Publisher Hierarchy
    pub_id LowCardinality(String),
    sub_pub String,
    pub_bundle_id String,
    publisher_platform LowCardinality(String),

    -- Campaign Hierarchy
    agency_id String,
    campaign_id String,
    campaign_name String,
    adgroup_id String,
    ad_id String,
    ad_objective_name LowCardinality(String),

    -- Creative, Channel & UTM
    channel LowCardinality(String),
    utm_source String,
    utm_medium String,
    utm_campaign String,
    utm_term String,
    utm_content String,

    -- Financial Values
    cost_currency LowCardinality(String),
    cost_value Float64,
    order_currency_code LowCardinality(String),
    order_value Float64,

    -- Device & Environment
    device_platform LowCardinality(String),
    device_os_name LowCardinality(String),
    device_type LowCardinality(String),
    is_mobile UInt8,
    is_ad_tracking_enabled UInt8,

    -- Data Quality & Risk
    fraud_score Float32
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
PRIMARY KEY (client_id, workspace_id)
ORDER BY (client_id, workspace_id, toStartOfDay(timestamp), event_code, pub_id, dmd_uid, timestamp);

4.2 Telemetry Query Endpoints​

KPI Metrics (POST /api/v2/attribution/stats/kpi)​

Returns aggregate executive summary metrics across the selected date range:

{
  "spend": 84250.00,
  "grossOrderValue": 348900.00,
  "roas": 4.14,
  "activeUsers": 42180,
  "avgTimeToConvertDays": 3.8,
  "aov": 82.50,
  "assistedOrderValue": 142100.00,
  "cac": 19.97,
  "cpa": 19.93,
  "cvr": 3.85,
  "newCustomerMix": 64.2
}

Channel Attribution Model Comparison (POST /api/v2/attribution/stats/channel)​

Returns attributed order values split across attribution models:

{
  "rows": [
    {
      "channel": "Google Ads",
      "firstTouchAttributedOrderValue": 145000.00,
      "lastTouchAttributedOrderValue": 112000.00,
      "linearAttributedOrderValue": 128500.00,
      "positionBasedAttributedOrderValue": 136200.00
    },
    {
      "channel": "Meta Ads",
      "firstTouchAttributedOrderValue": 120000.00,
      "lastTouchAttributedOrderValue": 148000.00,
      "linearAttributedOrderValue": 134000.00,
      "positionBasedAttributedOrderValue": 139100.00
    }
  ]
}

Time-to-Conversion Distribution (POST /api/v2/attribution/stats/time-to-conversion)​

Returns customer conversion counts grouped by latency buckets:

{
  "rows": [
    { "bucket": "< 1 hour", "conversions": 1240, "share": 29.3 },
    { "bucket": "1 - 24 hours", "conversions": 1680, "share": 39.7 },
    { "bucket": "1 - 7 days", "conversions": 780, "share": 18.4 },
    { "bucket": "7 - 30 days", "conversions": 410, "share": 9.7 },
    { "bucket": "> 30 days", "conversions": 120, "share": 2.8 }
  ]
}

5. Mathematical KPI Calculation Formulas​

All metrics displayed in the Target Attribution dashboard are calculated deterministically using the following equations:

MetricFormulaDescription
ROAS (Return on Ad Spend)ROAS = Gross Order Value / Ad SpendRevenue generated per currency unit of media expenditure.
CPA (Cost per Acquisition)CPA = Total Ad Spend / Total ConversionsNet cost required to generate one transaction.
CAC (Customer Acquisition Cost)CAC = Total Ad Spend / First-Time Converting UsersMedia cost required to acquire one brand-new paying customer.
CVR (Conversion Rate)CVR = (Total Conversions / Total Click Events) * 100Percentage of ad clicks that successfully conclude in a conversion.
AOV (Average Order Value)AOV = Gross Order Value / Total OrdersAverage revenue generated per completed transaction.
New Customer Mix %New Customer Mix = (First Purchase Revenue / Gross Order Value) * 100Share of top-line revenue originating from new customer acquisitions vs. returning customers.
Assistance RatioAssistance Ratio = Assisted Conversions / Direct ConversionsProportion of times a channel assisted a customer journey relative to directly closing it.