Skip to main content

Hyros & commerce ledger architecture audit

Canonical Reference & Client Review

Document Version: 1.0 Date: 2026-08-18 Scope: Ingestion, Storage Ledgers, Attribution Resolution, and Mathematical Parity Across All 8 Connectors

1. Executive Summary​

During our deep review of attribution and conversion metrics across direct connectors (Hyros, Sticky.io) and standard e-commerce platforms (Checkout Champ, Shopify, CAKE), we audited how V1 (legacy Laravel) processed and stored customer transactions compared to V2 (ClickHouse App Router).

Key Takeaways:​

  1. Mathematical Accuracy: Both V1 and V2 group multi-item shopping carts (e.g., 1 Front-End product + 2 Order Bumps) into 1 conversion event per checkout, ensuring that Cost Per Acquisition ($\text{CPA} = \frac{\text{Spend}}{\text{Conversions}}$) and Average Order Value ($\text{AOV} = \frac{\text{Revenue}}{\text{Conversions}}$) reflect true customer orders rather than inflated line-item counts.
  2. The V1 Architectural Flaw (Lossy Ingestion): V1 aggregated raw line items in memory before writing to the database, permanently discarding raw transaction IDs (sale.id), item-level prices, and individual upsell timestamps.
  3. The V2 Canonical Architecture (Lossless 3-Stage Pipeline): V2 stores the full, unaggregated transaction stream in marketing_sales while simultaneously deriving grouped unique orders in marketing_crm_order_events and credited ad attribution in marketing_attribution_events.

2. V1 Forensics: How the Legacy Engine Worked​

A forensic review of the V1 codebase (launchpad-old-v1) confirmed the exact mechanics of its pipeline:

A. Raw Sales Fetch​

In app/Services/HyrosOnDemandService.php:874, V1 fetched raw sales from the Hyros /sales endpoint:

$sales = $this->fetchSalesForDateRange($headers, $startDate, $endDate);

B. In-Memory Grouping (The Lossy Step)​

In app/Services/HyrosOnDemandService.php:906-909 and lines 1214-1233, V1 iterated through the raw sales and collapsed them by uniquePurchaseGroupKey:

foreach ($events as $event) {
    $groupKey = $this->uniquePurchaseGroupKey($event, $timezone);
    if (!isset($groups[$groupKey])) {
        $groups[$groupKey] = $event;
        $groups[$groupKey]['quantity'] = 1; // Forces quantity to 1
        $groups[$groupKey]['metadata']['merged_line_count'] = 1;
        $groups[$groupKey]['metadata']['merged_order_ids'] = [$event['metadata']['order_id']];
        continue;
    }

    $existing = &$groups[$groupKey];
    $existing['revenue'] += (float) $event['revenue']; // Sums revenue across items
    $existing['metadata']['merged_line_count'] += 1;
}

C. Discarding the Raw Transaction Stream​

In app/Services/HyrosOnDemandService.php:921-926, V1 wrote only the collapsed $conversionEvents to MySQL (crm_conversion_events). The raw $sales array was never written to any database table.

3. V2 Canonical Architecture: The Unified 3-Stage Pipeline​

To preserve 100% data fidelity while maintaining exact V1 CPA/ROAS parity, LaunchPad V2 implements a unified 3-stage ledger across all connectors:

$$\begin{array}{ccccc} \textbf{Source APIs} & \longrightarrow & \mathbf{marketing_sales} & \longrightarrow & \mathbf{marketing_crm_order_events} & \longrightarrow & \mathbf{marketing_attribution_events} \ \text{(Hyros, Shopify, CC, Sticky)} & & \text{(Lossless Raw Lines)} & & \text{(Unique Customer Orders)} & & \text{(Credited Ad Hierarchy)} \end{array}$$

The 3 Ledgers Explained:​

Ledger / TableGrain & IdentityRole & PurposeData Preserved
marketing_sales(client_id, date, order_id, txn_id)Lossless Source Ledger: Every raw line item, order bump, and upsell transaction.Full raw JSON, item SKUs, individual item prices, refund flags, and customer email SHA.
marketing_crm_order_events(client_id, event_date, purchase_key, ad_id)Unique Purchase Ledger: Grouped 1-to-1 customer checkouts with netted revenue.V1 parity ledger for order-level conversion counting and historical audits.
marketing_attribution_events(client_id, date, sale_id, ad_id, model)Credited Ad Attribution Ledger: Attributed revenue and conversion weights resolved to ad hierarchy.Mapped to exact Ad IDs, Ad Set IDs, Campaign IDs, and Ad Account IDs.

4. Cross-Connector Implementation Matrix​

Every commerce, CRM, and attribution connector in LaunchPad V2 is aligned to this standard:

ConnectorPlatform TypeRaw Lines in marketing_salesGrouped Orders in marketing_crm_order_eventsAd Resolution in marketing_attribution_events
Checkout ChampE-commerce / CRMYes (order_id + txn_id)DerivedUTM Resolved
ShopifyE-commerceYes (order_id + line_id)DerivedUTM Resolved
CAKE Sheet & RESTAffiliate / CRMYes (order_id + conv_id)DerivedUTM Resolved
UltraCartE-commerceYes (order_id + item_id)DerivedUTM Resolved
ClickBankGateway / CRMYes (receipt + item_no)DerivedUTM Resolved
HyrosAttribution & SalesYes (orderId + sale.id)Grouped CheckoutsAd-Attributed Hierarchy
Sticky.ioCRM / GatewayYes (order_id + txn_id)Grouped CheckoutsAd-Attributed Hierarchy
Converge BigQueryWarehouse ViewN/A (Direct BQ Export)N/AMulti-Model Attribution

5. Mathematical Parity & Formula Verification​

In both V1 and V2, reporting formulas operate on the clean conversion and revenue totals:

  1. Attributed Purchases: $$\text{Purchases} = \sum \text{attribution_weight} \quad (\text{Exact integer count of unique orders})$$

  2. Attributed Sales Revenue: $$\text{Revenue} = \sum \max(\text{attributed_revenue}, 0) \quad (\text{positive sales only; refunds remain audit-only ledger facts})$$

    Refund and chargeback events are not merely displayed as zero. They are ineligible for every marketing-report dataset, so they cannot create a campaign, ad set, ad, creative, product, breakdown, or dashboard row. The original positive sale remains reportable on its sale date; its later reversal remains available only in the lifecycle and audit surfaces.

  3. Cost Per Acquisition (CPA): $$\text{CPA} = \frac{\text{Ad Spend}}{\text{Purchases}}$$

  4. Return on Ad Spend (ROAS): $$\text{ROAS} = \frac{\text{Attributed Revenue}}{\text{Ad Spend}}$$

  5. Average Order Value (AOV): $$\text{AOV} = \frac{\text{Attributed Revenue}}{\text{Purchases}}$$

6. Live Production Audit Results (2026-08-18)​

A. Raw Transactions in marketing_sales​

  • Total Raw Lines Stored: 305,439 rows
  • Total Gross/Net Revenue Tracked: $33,251,800.61
  • iPhone Freedom (Hyros): 11,345 raw lines ($2,087,969.26)
  • Credit Secrets (Hyros): 34,731 raw lines ($781,585.27)
  • Top Speed (Checkout Champ): 123,155 raw lines ($15,686,577.76)
  • Bigly (Shopify): 53,316 raw lines ($3,319,131.86)
  • Pup Labs (CAKE): 18,167 raw lines ($2,977,496.56)

B. Attribution Health (/admin/sync-health)​

  • iPhone Freedom: 849 orders • 849 eligible • 849 credited ($199,745.45) • 0 unresolved
  • Credit Secrets: 6,754 orders • 6,754 eligible • 6,754 credited ($266,782.97) • 0 unresolved
  • UpWellness: 3,597 orders • 3,597 eligible • 3,597 credited ($403,183.54) • 0 unresolved

7. Client Presentation Summary​

When presenting to the client, highlight the following:

"In V1, whenever a customer bought an offer with upsells or order bumps, those products were instantly merged into a single number before being stored. In V2, we have upgraded the architecture so that every individual product SKU, upsell line, and price point is stored permanently in our raw sales ledger, while still giving you the exact, un-inflated Cost Per Order and ROAS on your dashboard."