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​

The current query-engine response groups step counters under rows[].steps[]. This illustrative closed-funnel example uses User counting with one session and one selected event per reached customer at each step. Distinct customer, session, and event counts need not be equal in real data. Trended responses use trendBuckets, with breakdown rows inside each bucket.

{
  "funnelId": "checkout-example",
  "funnelVersion": 1,
  "funnelName": "Checkout conversion",
  "countingEntity": "USER",
  "scopeType": "user_window",
  "breakdownMode": "entry",
  "visualization": "standard",
  "measuredAs": "conversion",
  "dateFrom": "2026-09-01",
  "dateTo": "2026-09-07",
  "rows": [
    {
      "breakdown": {},
      "steps": [
        {
          "stepIndex": 1,
          "stepName": "Checkout",
          "countReached": 1000,
          "countUniqueUsersReached": 1000,
          "countUniqueSessionsReached": 1000,
          "countEventsReached": 1000,
          "countAdvanced": 600,
          "countDropped": 400,
          "countConverted": 180,
          "stepConversionRate": 0.6,
          "stepDropOffRate": 0.4,
          "overallConversionRate": 0.18
        },
        {
          "stepIndex": 2,
          "stepName": "Payment",
          "countReached": 600,
          "countUniqueUsersReached": 600,
          "countUniqueSessionsReached": 600,
          "countEventsReached": 600,
          "countAdvanced": 180,
          "countDropped": 420,
          "countConverted": 180,
          "stepConversionRate": 0.3,
          "stepDropOffRate": 0.7,
          "overallConversionRate": 0.3
        },
        {
          "stepIndex": 3,
          "stepName": "Purchase",
          "countReached": 180,
          "countUniqueUsersReached": 180,
          "countUniqueSessionsReached": 180,
          "countEventsReached": 180,
          "countAdvanced": 0,
          "countDropped": 0,
          "countConverted": 180,
          "stepConversionRate": 1,
          "stepDropOffRate": 0.0,
          "overallConversionRate": 1.0
        }
      ]
    }
  ]
}

Funnel counter and rate definitions​

Definitions checked against the local query-engine and frontend source on 9 October 2026. API rates are ratios: 0.18 represents 18%. They are not preformatted percentages.

FieldDefinition
countReachedSelected journeys reaching the displayed step, using the definition's counting entity.
countAdvancedSelected journeys with a later reached step.
countDroppedJourneys reaching this non-final step with no later reached step.
countConvertedSelected journeys reaching the final step.
stepConversionRatecountAdvanced / countReached at non-final steps; countConverted / countReached at the final step.
stepDropOffRatecountDropped / countReached.
overallConversionRatecountConverted / countReached at the displayed step, not necessarily relative to Step 1.
countUniqueUsersReachedDistinct customer IDs attached to selected reached events; empty IDs excluded.
countUniqueSessionsReachedDistinct customer/session pairs attached to selected reached events; empty session IDs excluded.
countEventsReachedDistinct selected reached-event identities, including events shared by multiple journeys only once.

A zero denominator leaves the API rate unavailable. Distinct counts are deduplicated within each breakdown or time bucket and cannot be summed into a unique overall total.

The frontend step table separately calculates Conversion % as countReached at this step / countReached at Step 1 * 100 within each breakdown. It calculates Drop Rate as countDropped / countReached * 100. Do not map either API conversion-rate field directly to the table's Conversion %.

For open funnels, Advanced and Converted may include later-entry journeys that did not reach the displayed step. The resulting counters and rates use different populations; they should not be treated as precise progression measures. See the business-user guidance.

The current event-resolution planner applies conversionWindowSeconds between consecutive steps, with maxWaitSeconds overriding the default for a transition. It does not enforce a total first-to-final deadline. The report scan period still bounds available events.

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.