Skip to main content

Reporting & Analytics Engine Architecture

The DriveMetaData (DMD) Reporting & Analytics Engine powers real-time, interactive exploration across billions of customer events, advertising touchpoints, and financial conversion records. Built to deliver sub-second query responses without sacrificing row-level auditability, the engine bridges high-throughput streaming ingestion with multi-dimensional analytical workbenches.

This document details the architectural foundations of the reporting suite:

  1. ClickHouse Column-Store Foundations — Partitioning, primary sorting keys, and sparse indexing.
  2. Funnel Sequence Matching Engine — Chronological event processing, windowed state transitions, and step latency.
  3. Pivot OLAP Cube & Cardinality Quarantine — Dynamic multi-dimensional slicing, hierarchical aggregation, and memory circuit breakers.
  4. Target Attribution Graph Engine — Multi-touch attribution modeling, credit distribution algorithms, and time-to-conversion lag telemetry.

1. High-Level Engine Architecture​

The reporting engine operates as an analytical layer over streaming event pipelines and cold storage repositories, utilizing ClickHouse as the primary online analytical processing (OLAP) database.

2. ClickHouse Column-Store Foundations​

Traditional relational databases (PostgreSQL, MySQL) store data row-by-row, requiring disk I/O to read entire records even when only calculating the sum of a single revenue column. DMD leverages ClickHouse column-oriented storage where values of each column are stored contiguously on disk.

2.1 Table Storage Engine: MergeTree​

All core attribution and journey log tables (including target_attribution_logs_ad_data_v4) utilize the MergeTree family of engines:

  • Monthly Partitioning (PARTITION BY toYYYYMM(timestamp)): Events are grouped into monthly directory partitions. Queries containing date filters (e.g., WHERE timestamp >= '2026-09-01') immediately discard non-matching monthly partitions before reading disk blocks.
  • Compound Primary Sorting Key: Data on disk is sorted physically by: 
    ORDER BY (client_id, workspace_id, toStartOfDay(timestamp), event_code, pub_id, dmd_uid, timestamp)
  • Sparse Indexing: ClickHouse creates an index mark every 8,192 physical rows. The engine scans the index marks to locate target disk offsets, skipping irrelevant blocks and achieving scan speeds exceeding tens of millions of rows per second per CPU core.

2.2 Tenant Isolation at the Disk Layer​

Multi-tenancy is enforced by placing client_id and workspace_id at the leading positions of the primary key. When a tenant executes a query, the storage engine immediately seeks to that tenant's key range, providing zero data leakage and predictable performance regardless of total platform data volume.

3. Funnel Sequence Evaluation Engine​

The Funnel Engine calculates how cohorts of users advance through ordered sequences of actions (e.g., Ad Impression → Landing Page View → Product Added → Checkout Started → Purchase Completed).

3.1 Sequence Matching Logic​

Funnels evaluate events per unique user identifier (dmd_uid) ordered chronologically:

Journey(u) = [ (e_1, t_1), (e_2, t_2), ..., (e_n, t_n) ] where t_1 <= t_2 <= ... <= t_n

A customer converts through step k if there exists an event sequence where:

  1. Each event satisfies the defined step criteria.
  2. The timestamps advance chronologically: t_1 < t_2 < ... < t_k.
  3. Each consecutive transition meets its allowed wait: (t_i - t_(i-1)) <= Window_i. The current event-resolution planner applies the default conversion window between steps; a step-specific maximum wait overrides it for that transition. The full journey can exceed the default window. Events must also fall within the report scan period. See the conversion-window example.

3.2 Sequence Modes: Strict vs. Loose​

The engine supports two matching semantics:

  • Loose Sequencing (Default): Intermediate events are permitted between funnel steps. If a user clicks an ad, browses three unrelated pages, and then completes a signup, they convert successfully.
  • Strict Sequential: No intervening customer actions are permitted between consecutive defined steps. If an unconfigured event occurs between Step 2 and Step 3, the journey is classified as dropped off.

3.3 Drop-Off & Latency Metrics​

For each step k:

  • Step-to-Step Conversion Rate: 
    Step CVR = (Users Converting Step k / Users Converting Step k-1) * 100
  • Cumulative Conversion Rate: 
    Cumulative CVR = (Users Converting Step k / Users Entering Step 1) * 100
  • Step Transition Latency: The distribution (median, p75, p90, p95) of elapsed seconds between step timestamps.

4. Pivot OLAP Cube Slicing & Cardinality Quarantine​

The Pivot Engine delivers ad-hoc, multi-dimensional cross-tabulation. Marketers can dynamically drag dimensions into Row Groups (hierarchical rows), Column Labels (pivoted matrix headers), and assign aggregation functions (SUM, COUNT, AVG, MIN, MAX) to Values.

4.1 Multi-Dimensional Aggregation Mechanics​

The backend translates the pivot definition into a grouped OLAP query using ClickHouse aggregation combinators:

SELECT
    country,
    channel,
    campaign_name,
    count() AS total_events,
    sum(order_value) AS gross_revenue,
    avg(order_value) AS avg_order_value
FROM target_attribution_logs_ad_data_v4
WHERE client_id = 1635 
  AND workspace_id = 1637
  AND timestamp >= '2026-08-15'
GROUP BY country, channel, campaign_name
WITH ROLLUP;

4.2 The Cardinality Explosion Hazard​

In an unconstrained pivot table, grouping by high-cardinality dimensions (such as dmd_uid, ip, or raw timestamp) across both rows and columns produces an exponential Cartesian product:

Total Result Cells = (Product of Unique Row Values) * (Product of Unique Column Values) * Measures

If a marketer groups rows by dmd_uid (1,000,000 unique values) and columns by pub_click_id (500,000 unique values), the theoretical matrix requires 500 billion cells. Executing this causes:

  1. Out-of-memory (OOM) termination on the ClickHouse cluster.
  2. Large JSON serialization payloads crashing the API gateway.
  3. Complete browser tab freezing when rendering hundreds of thousands of DOM elements.

4.3 The DMD Cardinality Quarantine Guardrail​

To prevent query runaway and system degradation, DMD implements an active Cardinality Quarantine Circuit Breaker:

Quarantine Threshold Tiers​

TierProjected Grid CellsSystem BehaviorUI Feedback
Safe0 – 100,000Normal executionStandard grid view with full sorting and pagination.
Warning100,001 – 250,000Execution allowed with noticePivotQuarantineBanner (Yellow): Recommends applying filters to preserve responsiveness.
Quarantine Block> 250,000Execution Blocked (HTTP 422)PivotQuarantineBanner (Red): Prevents server crash; user must remove dimensions or filter values.

5. Target Attribution Graph Engine​

The Target Attribution Engine ingests streaming ad impressions, clicks, and conversion events, mapping them onto a unified customer timeline (dmd_uid). It evaluates multi-channel journeys to attribute financial credit (Spend, Gross Order Value, ROAS) accurately across acquisition channels.

5.1 Ad Telemetry Data Model (ad_data_v4)​

Every touchpoint contains:

  • Identity Pointers: dmd_uid, external_user_id, session_id, anonymous_id, fbp, pub_click_id.
  • Supply & Publisher Detail: pub_id, sub_pub, publisher_platform, pub_bundle_id.
  • Campaign Structure: agency_id, campaign_id, adgroup_id, ad_id, ad_objective_name.
  • Financial Value: cost_currency, cost_value, order_currency_code, order_value.
  • Touchpoint Timing: touchpoint_timestamp (time of ad interaction) and timestamp (time of recorded conversion).

5.2 Multi-Touch Attribution Models​

DMD provides four attribution weighting models:

  1. First-Touch Attribution (100% Discovery):
    • Attributes 100% of revenue and conversion credit to the initial touchpoint within the lookback window.
    • Best for: Top-of-funnel brand awareness and partner discovery analysis.
  2. Last-Touch Attribution (100% Closer):
    • Attributes 100% of credit to the final touchpoint immediately preceding the transaction.
    • Best for: Evaluating high-intent closing channels (e.g., branded search, cart-recovery emails).
  3. Linear Attribution (Equal Weight):
    • Divides credit equally across all N valid touchpoints: 
      Credit(T_i) = 1 / N
    • Best for: Long multi-channel consideration cycles where each touchpoint plays an essential nurturing role.
  4. Position-Based Attribution (40 / 20 / 40 U-Shaped):
    • Allocates 40% of credit to the first touchpoint (discovery), 40% to the last touchpoint (closer), and divides the remaining 20% evenly across intermediate touchpoints: 
      Credit(First) = 0.40,  Credit(Last) = 0.40,  Credit(Middle_i) = 0.20 / (N - 2)
    • Best for: Balanced growth marketing strategies recognizing both customer acquisition and closing mechanisms.

5.3 Assisted Conversion Intelligence​

A channel that does not claim the final touchpoint often still contributes critically to conversion momentum. The engine calculates:

  • Direct Order Value: Revenue attributed directly as the closing touchpoint under the active model.
  • Assisted Order Value: Total revenue from customer journeys where the channel participated in intermediate steps without being the final touchpoint.
  • Assistance Ratio: 
    Assistance Ratio = Assisted Conversions / Direct Conversions
    Channels with a high assistance ratio (> 2.0) function primarily as brand educators and nurturers; cutting spend to them typically causes downstream collapse in direct closing channels.

5.4 Time-to-Conversion Lag Analysis​

Conversions rarely occur instantaneously. The engine tracks attribution lag:

Lag = Conversion Timestamp - Touchpoint Timestamp

The distribution of elapsed time is classified into analytical lag buckets:

  • Instantaneous: (&lt; 1 hour)
  • Intraday: 1 to 24 hours
  • Short-Cycle: 1 to 7 days
  • Mid-Cycle: 7 to 30 days
  • Long-Cycle: (&gt; 30 days)

Understanding this distribution prevents premature campaign pausing by accounting for standard buyer deliberation time before computing final Return on Ad Spend (ROAS).

6. Next Steps & Related Documentation​