Sales transaction normalization & upsell classification

Canonical architecture

Version: 3.0 Date: 2026-08-18 Connectors: All order-producing commerce and CRM connectors

Decision

marketing_sales is the sole canonical transaction ledger. Connectors do not write a second classification or pre-aggregated sales ledger. They normalize their native transaction facts into complete marketing_sales rows, and the replayable classifier upserts those complete rows with derived fields.

Order and attribution surfaces continue to join and aggregate from marketing_sales. marketing_crm_order_events is a derived unique-purchase surface; it is not the transaction source of truth.

This contract is connector-wide, not HYROS-specific. Checkout Champ, Konnektive, Shopify, Sticky.io, Converge, CAKE, ClickBank and UltraCart all land order facts in marketing_sales and run the same classifier. HYROS keeps its vendor /attribution stream as reporting credit while its sales stream is the lifecycle ledger. Northbeam is an attribution-only provider: it must be labelled as such and must not fabricate an order ledger from aggregate credit.

Normalized fields

Every supported source provides, where available:

• order_id and distinct txn_id
• trustworthy transaction_at plus transaction_timestamp_source
• hashed customer_identity_sha plus non-secret identity provenance
• vendor transaction_type and lifecycle flags
• products, revenue, attribution dimensions and raw source payload

The classifier adds transaction_class, classification_method, classification_confidence, initial_transaction_id, window_started_at, upsell_sequence, and classification_version.

Fixed 24-hour customer window

The window is customer-anchored, not order-grouped and not a calendar day.

1. The first eligible sale opens a window at T0.
2. A second transaction at exactly T0 is an initial_component.
3. A transaction strictly after T0 and at or before T0 + 24 hours is an upsell.
4. Upsells never move or extend the window end.
5. The first eligible transaction after T0 + 24 hours opens a new initial_sale window.

Mon 11:30  initial_sale       window starts
Mon 11:30  initial_component same-time initial order component
Mon 11:42  upsell #1
Tue 10:15  upsell #2         still inside original fixed window
Tue 11:31  initial_sale      new window

Lifecycle precedence

Vendor lifecycle facts override time-derived position and do not open or extend a sale window:

chargeback > refund > rebill > fixed-24h sale classification > unknown

Missing customer identity or a missing/untrustworthy timestamp remains unknown. The system never guesses by globally ordering unrelated customers, using midnight boundaries, or treating all older records as rebills.

Connector mappings

Connector	Transaction identity	Customer identity	Timestamp	Explicit lifecycle
Checkout Champ	orderId + transactionId	hashed customer ID/email	transaction dateCreated	sale/refund/recurring
Hyros	sale.id	hashed lead ID; email fallback	trusted creationDate; batch-import time rejected	recurring/refund
Converge	conversion_id	hashed profile_id	conversion_timestamp	source properties retained for audit
Sticky.io	child/order ID	hashed customer ID; email fallback	acquisition/order timestamp	rebill_depth, status
Shopify	order + line-item ID	hashed email	order creation; exact SalesAgreement.happenedAt for OCU edits	refund; OCU attributes; Zipify ORDER_EDIT product sale
Konnektive	order + transaction ID	hashed customer ID/email	transaction creation timestamp	sale/refund/rebill
ClickBank	receipt	hashed customer email	source order timestamp	refund/chargeback
UltraCart	merchant order ID	hashed billing email	creation/payment timestamp	refund/auto-order

HYROS /sales and /attribution are both official endpoints documented at https://api-docs.hyros.com/. A HYROS sale's recurring flag is authoritative rebill evidence; it is not used as a proxy for an upsell. creationDate is trusted only when it is present and not the observed 04:00 UTC batch-import placeholder. The fallback ladder may use same-sale-day lead/click clocks for classification, but rows without trustworthy source chronology remain a reported coverage gap rather than being assigned an invented sequence. | CAKE | conversion ID | unavailable in the source | conversion date | vendor-explicit initial sale |

Sticky parent-child order IDs remain preserved, but parent grouping does not replace customer/time classification. Converge's explicit Zipify order tags are mapped at ingest: the first paid line remains the checkout, fully discounted bonus lines remain components with zero revenue, and later paid lines on the tagged order are vendor-explicit upsells. Vendor-explicit upsells override same-timestamp component inference. Hyros product/lead tags remain audit metadata unless the source supplies an equally deterministic transaction-level lifecycle fact.

Shopify / Zipify OCU enrichment

Shopify order lines are the canonical revenue grain. In-checkout OCU offers carry _ocu_* line attributes and can be classified directly. Zipify post-purchase and thank-you-page offers do not consistently carry those line attributes, so orders tagged OCU Zipify Post-Purchase Upsell Bought or OCU Zipify TY Upsell Bought receive a targeted GraphQL enrichment:

• only SalesAgreement.reason = ORDER_EDIT created by app One Click Upsell is accepted as vendor evidence;
• each ProductSale.lineItem.id identifies the exact edited line;
• SalesAgreement.happenedAt is the transaction timestamp;
• ProductSale.totalAmount is the exact edited-line revenue;
• a successful same-amount SALE/CAPTURE transaction is retained in raw audit evidence when available.

An OCU order tag by itself never turns every sibling product into an upsell. Exact edited-line amounts are locked first, then the residual order total is allocated across initial components. This preserves both the source order total and the exact OCU edit amount. Attribution purchase weight remains 1 for the initial checkout and 0 for every component/upsell line.

The Shopify Admin API supplies accepted-order facts, not OCU offer views. Zipify defines offer conversion rate using offer views and exposes the native funnel/offer/device breakdown through its Statistics CSV. Therefore ledger take rate (unique checkout accepted at least one upsell / eligible initial checkouts) is intentionally distinct from Zipify's offer-view conversion rate. Native offer-view parity cannot be certified without importing that export.

Replay behavior

Classification reads the source connector's marketing_sales FINAL rows for the trailing 400 days, derives windows per (client, connector, customer), and upserts only changed complete rows. This matters during backfill: an older transaction can change the initial anchor for later transactions.

Hyros runs classification after its one-year history walker reaches the target. Checkout Champ replaces each requested source window authoritatively from transactions/query before classification, so legacy aggregate rows and source-deleted transactions cannot survive beside hydrated transaction facts. Converge, Sticky.io, Shopify, Konnektive, ClickBank, UltraCart and CAKE classify after their transaction inserts. A completed historical source snapshot must be inserted before source-absent legacy rows are removed; an empty response is never allowed to erase a revenue window. Connectors whose APIs expose only incremental deltas retain previous facts during steady-state sync and use their complete-window backfill endpoint for authoritative replacement. Repeated execution is idempotent through ReplacingMergeTree(synced_at) and classification_version.

Attribution ownership

The transaction ledger preserves every revenue-bearing line, but it does not permit every line to mint a purchase.

• Hyros /attribution owns canonical CRM purchases, revenue and hierarchy. Hyros /sales remains order-to-ad drill evidence with attribution_eligible = 0; it never enters generic UTM attribution.
• Checkout Champ has no equivalent vendor attribution endpoint. Its canonical attribution stream is LaunchPad's utm_last_touch resolver over the normalized transaction ledger. Only initial_sale has purchase weight 1. Components, upsells, rebills and refund transactions preserve their revenue with purchase weight 0. Legacy unknown rows keep the previous policy only until their source window is replaced.
• Sync Health selects the direct canonical stream for direct-attribution connectors and the UTM stream for commerce connectors. It never allows a stale account-only UTM row to shadow Hyros hierarchy.
• Direct and UTM reporting both read canonical latest views. Raw physical attribution tables are ingest evidence only and are never a serving source.

Reporting rules

• Unique orders: uniqExact(order_id) at the desired scope.
• Initial sales: transaction_class = 'initial_sale'.
• Initial-order revenue includes initial_sale and initial_component.
• Upsell count/revenue uses transaction_class = 'upsell'.
• Rebill/refund/chargeback remain separate lifecycle measures.
• Unknown records must be shown as coverage, not silently folded into another class.

Money remains decimal dollars throughout the ledger.

Paid acquisition lifecycle-revenue contract

As of 2026-08-18, the paid-acquisition report no longer estimates primary and upsell revenue as an 80/20 split of ad-platform revenue. The columns now read the normalized marketing_sales FINAL ledger directly:

• Commerce Initial Rev $ = sumIf(net_value, transaction_class IN ('initial_sale', 'initial_component')).
• Commerce Upsell Rev $ = sumIf(net_value, transaction_class = 'upsell').
• Only reportable sales statuses participate. Unknown/unclassified rows remain coverage gaps and are not silently assigned to either value.

These values are currently authoritative at client grain. They are not automatically spread across ad accounts, campaigns, ad sets, ads, or creatives. Some connectors—most importantly HYROS aggregate attribution—do not expose a stable transaction-to-ad join. At those deeper grains the report renders the value as unavailable rather than inventing a proportional allocation. The same rule applies when a paid-platform, account, funnel, campaign, product, ad-set, ad, or creative filter is active: the full-client lifecycle values are suppressed because they would not represent the narrowed paid scope.

This creates three intentionally separate revenue concepts:

Metric	Source	Meaning
Total revenue	Ad platform facts	Revenue reported by Meta/Google/etc.
CRM revenue	v_attribution_latest	Commerce/CRM revenue credited to paid hierarchy
Commerce initial/upsell revenue	marketing_sales FINAL	Full normalized lifecycle ledger at client grain

They should not be expected to sum to one another: each has a different source, scope and attribution policy. A campaign-level lifecycle split may be shown only after its connector supplies a verified one-to-one sale association.

Client-facing explanation

The upgrade replaces a directional estimate with measured transaction data. Initial revenue includes the first checkout and same-time checkout components; upsell revenue includes only transactions positively classified as follow-on offers within the fixed lifecycle window or explicitly tagged by the source. Unclassified orders stay visible as a coverage issue, and revenue is never assigned to an ad or campaign without source evidence.