Revenue tracking on Admaxxer

Three revenue datasources

Each source carries a different signal and survives a different failure mode. The pixel survives platform-attribution drift (iOS ATT, ad-blockers); the orders datasource survives the pixel itself; signed webhooks survive both for non-Shopify storefronts.

What gets captured

visitor_payments (46 columns)

The pixel-side ledger. Schema-strict ingest with DEFAULT '' on every String column per GL#357 — a partially-mapped row never silently drops to quarantine. The mapper still must populate every field; the defaults are belt-and-braces.

workspace_id String

Owning workspace UUID — every read filters on this.

website_id LowCardinality(String)

Pixel-issued site identifier (admx_…).

visitor_id Nullable(String)

Stable per-browser ID written by the pixel; nullable for server-side rows.

ts DateTime64(3, 'UTC')

Event timestamp; ReplacingMergeTree version key.

provider LowCardinality(String)

shopify_pixel · stripe · paddle · polar · lemonsqueezy · dodo · shopify_webhook.

external_payment_id String

Provider-side payment ID. Refund rows use 'refund:<id>' so they never replace the original revenue row.

amount_cents Int64

Order total in minor currency units. Negative on refund rows.

currency LowCardinality(String)

ISO 4217 (USD, EUR, GBP, …).

email_hash String DEFAULT ''

SHA-256(email) for CAPI match-rate joins. Never raw email.

attributed_utm_source LowCardinality(String) DEFAULT ''

Last-touch utm_source at payment time.

attributed_utm_campaign String DEFAULT ''

Last-touch utm_campaign at payment time.

subtotal_cents Int64 DEFAULT 0

Items only — excludes tax, shipping, discounts.

total_tax_cents Int64 DEFAULT 0

Tax collected at checkout.

total_discount_cents Int64 DEFAULT 0

Discount codes + automatic discounts applied.

total_shipping_cents Int64 DEFAULT 0

Shipping line.

refund_cents Int64 DEFAULT 0

Negative on refund rows; carries the partial-refund delta linked back to the original via external_payment_id='refund:<id>'.

units_sold Int32 DEFAULT 0

Sum of line-item quantities.

line_item_count Int32 DEFAULT 0

Distinct line items in the cart.

is_new_customer UInt8 DEFAULT 0

1 if this customer's first paid order; 0 if returning. Drives NC-ROAS / NCPA.

customer_order_count Int32 DEFAULT 0

Lifetime order count up to and including this row.

customer_total_spent_cents Int64 DEFAULT 0

Lifetime revenue from this customer up to this row — feeds cohort LTV.

financial_status LowCardinality(String) DEFAULT ''

paid · pending · refunded · partially_refunded · voided.

fulfillment_status LowCardinality(String) DEFAULT ''

fulfilled · partial · unfulfilled · null.

gateway LowCardinality(String) DEFAULT ''

shopify_payments · stripe · paypal · klarna · etc. — payment method.

landing_site String DEFAULT ''

First page of the session (full URL, query string included).

referring_site String DEFAULT ''

document.referrer at session start; smart-classified (l.facebook.com → facebook).

source_name LowCardinality(String) DEFAULT ''

web · pos · mobile_app · api · draft_order — Shopify's order origin tag.

gross_sales_cents Int64 DEFAULT 0

Subtotal + tax + shipping (Shopify-equivalent definition); excludes discounts.

gclid String DEFAULT ''

Google click identifier captured at payment time. Survives 90-day localStorage TTL (client-pixel/src/pixel.ts:1036-1037).

fbclid String DEFAULT ''

Facebook / Instagram click identifier; captured at payment time within the 90-day window.

ttclid String DEFAULT ''

TikTok click identifier.

msclkid String DEFAULT ''

Microsoft / Bing Ads click identifier.

twclid String DEFAULT ''

X / Twitter Ads click identifier.

li_fat_id String DEFAULT ''

LinkedIn first-party ad identifier.

epik String DEFAULT ''

Pinterest click identifier.

rdt_cid String DEFAULT ''

Reddit click identifier.

sccid String DEFAULT ''

Snapchat click identifier.

yclid String DEFAULT ''

Yandex Metrica click identifier.

klaviyo_id String DEFAULT ''

Klaviyo profile identifier (_kx) — joins email LTV onto the same row as paid acquisition.

amzn_cid String DEFAULT ''

Amazon Ads click identifier (DSP / Sponsored Products).

first_utm_source LowCardinality(String) DEFAULT ''

First-touch utm_source persisted in localStorage for 365 days.

first_utm_medium LowCardinality(String) DEFAULT ''

First-touch utm_medium.

first_utm_campaign String DEFAULT ''

First-touch utm_campaign — survives every cart abandonment within the 365-day window.

first_utm_term String DEFAULT ''

First-touch utm_term (paid-search keyword).

first_referrer String DEFAULT ''

Smart-classified first-touch referrer (google · facebook · direct · tiktok · …).

first_landing_path String DEFAULT ''

First-touch landing path (no host, no query) — primary attribution surface for content marketing.

revenue_events (15 columns)

Provider-signed server-side webhook ledger. Stripe / Paddle / Polar / Lemon / Dodo all write here. Idempotent on (provider, event_id).

workspace_id String

Owning workspace UUID.

website_id LowCardinality(String)

Pixel-issued site identifier.

ts DateTime64(3, 'UTC')

Provider-emitted event timestamp.

provider LowCardinality(String)

stripe · paddle · polar · lemonsqueezy · dodo.

event_id String

Provider event UUID — idempotency key, dedup on (provider, event_id).

event_type LowCardinality(String)

charge.succeeded · payment_intent.succeeded · invoice.paid · charge.refunded · checkout.session.completed · subscription.created · etc.

external_payment_id String

Provider's payment / charge / invoice ID. Refund deltas keyed back to the original here.

amount_cents Int64

Net amount on this event (negative on refund / chargeback events).

currency LowCardinality(String)

ISO 4217.

visitor_id Nullable(String)

Pixel visitor stitched via metadata.admaxxer_visitor_id (Stripe / Polar / Dodo) · custom_data.admx_visitor_id (Paddle) · meta.custom_data.admx_visitor_id (Lemon).

email_hash String DEFAULT ''

SHA-256(customer email) for CAPI joins.

is_recurring UInt8 DEFAULT 0

1 for subscription renewals so MRR / one-time can be split downstream.

fee_cents Int64 DEFAULT 0

Stripe / Paddle gateway fee — net-of-fees MER subtracts this from the numerator.

raw_payload String DEFAULT ''

JSON-encoded provider payload (truncated; verbatim for audit).

orders (19 columns)

The authoritative Shopify ledger. Streamed via admin webhooks; backfilled via OAuth-pulled GraphQL Admin API. Survives every ad-blocker / ITP / CSP failure that would silently drop a pixel row.

workspace_id String

Owning workspace UUID.

website_id LowCardinality(String)

Pixel-issued site identifier.

ts DateTime64(3, 'UTC')

Order creation timestamp from Shopify.

shop_domain LowCardinality(String)

<store>.myshopify.com — multi-store joins use this.

order_id String

Shopify GID — dedup key on (shop_domain, order_id).

order_number Int32 DEFAULT 0

Human-readable Shopify order number (e.g., 1042).

currency LowCardinality(String)

ISO 4217.

subtotal_price_cents Int64 DEFAULT 0

Items only.

total_tax_cents Int64 DEFAULT 0

Tax collected.

total_discounts_cents Int64 DEFAULT 0

Discount codes + automatic discounts.

total_shipping_cents Int64 DEFAULT 0

Shipping line.

total_price_cents Int64 DEFAULT 0

Gross-of-discounts gross-of-tax order total.

total_refunded_cents Int64 DEFAULT 0

Cumulative refunded to date — partial-refund-aware.

financial_status LowCardinality(String) DEFAULT ''

paid · partially_paid · refunded · partially_refunded · voided · pending.

fulfillment_status LowCardinality(String) DEFAULT ''

fulfilled · partial · unfulfilled · null.

is_new_customer UInt8 DEFAULT 0

Shopify-native flag (customer.orders_count = 1).

customer_id String DEFAULT ''

Shopify customer GID.

email_hash String DEFAULT ''

SHA-256(customer.email).

source_name LowCardinality(String) DEFAULT ''

web · pos · mobile_app · api · draft_order.

tags String DEFAULT ''

Comma-separated Shopify tags — useful for cohort filtering.

Example payloads

Shopify Custom Pixel checkout_completed

Posted by the canonical v3 Custom Pixel snippet to /api/event. Carries the full 46-column visitor_payments row, including 12 click-IDs and the first-touch UTM five-tuple.

POST /api/event HTTP/1.1
Host: admaxxer.com
Content-Type: application/json

{
  "event": "checkout_completed",
  "website_id": "admx_a1b2c3d4e5",
  "visitor_id": "v_8f3a2c91d4e0b7f6",
  "ts": "2026-05-05T14:32:11.482Z",
  "provider": "shopify_pixel",
  "external_payment_id": "5731234567890",
  "amount_cents": 12950,
  "currency": "USD",
  "email_hash": "9f86d081884c7d659a2feaa0c55ad015a3bf4f1b2b0b822cd15d6c15b0f00a08",
  "subtotal_cents": 11000,
  "total_tax_cents": 950,
  "total_discount_cents": 500,
  "total_shipping_cents": 1000,
  "units_sold": 2,
  "line_item_count": 2,
  "is_new_customer": 1,
  "customer_order_count": 1,
  "customer_total_spent_cents": 12950,
  "financial_status": "paid",
  "fulfillment_status": "unfulfilled",
  "gateway": "shopify_payments",
  "source_name": "web",
  "gross_sales_cents": 12950,
  "attributed_utm_source": "facebook",
  "attributed_utm_campaign": "spring-launch-2026",
  "first_utm_source": "google",
  "first_utm_medium": "cpc",
  "first_utm_campaign": "brand-search-2026-q1",
  "first_referrer": "google",
  "first_landing_path": "/best-running-shoes",
  "gclid": "EAIaIQobChMI8eqU7vqB_QIVC_7jBx2Lrw1iEAAYAyAAEgI3HfD_BwE",
  "fbclid": "IwAR2k4xZ9...",
  "ttclid": "",
  "msclkid": "",
  "klaviyo_id": "01HXY3K6Q9R0M2"
}

Stripe payment_intent.succeeded

Server-to-server, signed with HMAC-SHA256 via the Stripe-Signature header. Visitor stitch via metadata.admaxxer_visitor_id.

POST /api/pixel/webhooks/stripe/admx_a1b2c3d4e5 HTTP/1.1
Host: admaxxer.com
Stripe-Signature: t=1714915931,v1=5257a869e7ecebeda32affa62cdca3fa51cad7e77a1a8b8cd0e0fa1631...

{
  "id": "evt_1Pa9X2L1abCdEfGhIjKlMnOp",
  "object": "event",
  "type": "payment_intent.succeeded",
  "created": 1714915931,
  "data": {
    "object": {
      "id": "pi_3Pa9X2L1abCdEfGh0001Aaaa",
      "amount": 4900,
      "amount_received": 4900,
      "currency": "usd",
      "status": "succeeded",
      "customer": "cus_QabCdEfGhIjKlMn",
      "metadata": {
        "admaxxer_visitor_id": "v_8f3a2c91d4e0b7f6"
      },
      "charges": {
        "data": [{
          "id": "ch_3Pa9X2L1abCdEfGh0001Aaaa",
          "balance_transaction": "txn_3Pa9X2L1abCdEfGh0001Bbbb",
          "fee": 172
        }]
      }
    }
  }
}

# → ingested as one revenue_events row:
#   provider          = 'stripe'
#   event_id          = 'evt_1Pa9X2L1abCdEfGhIjKlMnOp'
#   event_type        = 'payment_intent.succeeded'
#   external_payment_id = 'pi_3Pa9X2L1abCdEfGh0001Aaaa'
#   amount_cents      = 4900
#   currency          = 'usd'
#   visitor_id        = 'v_8f3a2c91d4e0b7f6'   ← stitched
#   fee_cents         = 172

Attribution model

Admaxxer's attribution model is built for modern DTC: ad-blockers + iOS ATT have made third-party cookies unreliable, so every signal lives in first-party storage on the customer's own browser — localStorage on the merchant's domain, never a tracking cookie.

Click-IDs (90-day localStorage TTL)

Every supported click-ID parameter (gclid, fbclid, ttclid, msclkid, twclid, li_fat_id, epik, rdt_cid, sccid, yclid, klaviyo_id, amzn_cid) is captured on first arrival and persisted in localStorage for 90 days — the constant lives at client-pixel/src/pixel.ts:1036-1037. When checkout_completed fires, the pixel reads localStorage and stamps every click-ID onto the visitor_payments row.

First-touch UTMs (365-day persistence)

The first_utm_source / first_utm_medium / first_utm_campaign / first_utm_term / first_referrer / first_landing_path tuple is written exactly once per browser — on the very first session where any of those fields is set — and persisted for 365 days. A customer who clicks a Meta ad in March and buys in May still attributes to the original campaign without any cookie.

Smart referrer classification

Raw document.referrer values are noisy: l.facebook.com, m.facebook.com, lm.facebook.com, and www.facebook.com are all the same source. The pixel runs a domain-classification table at capture time so l.facebook.com → facebook, t.co → twitter, out.reddit.com → reddit, android-app://com.google.android.gm/ → gmail. The classified value lands in first_referrer; the raw value is preserved in referring_site for forensics.

Last-touch vs first-touch

Both are emitted on every visitor_payments row. attributed_utm_* carries the LAST-touch UTM (whatever was on the URL when checkout fired); first_utm_* carries the FIRST-touch UTM (persisted from the first session). The summary_kpis pipe lets attribution model selection (Last Click / First Click / Linear / Time-Decay / Position-Based) pick which side feeds the dashboard.

Refunds and dedup

Pixel: negative-row pattern

The pixel emits a NEGATIVE row with external_payment_id='refund:<original_id>' and a negative refund_cents. Because the dedup key includes external_payment_id, the refund row does NOT replace the parent revenue row — both rows coexist. summary_kpis sums both, so net revenue automatically deducts the refund amount and partial refunds are computed correctly.

Webhooks: ReplacingMergeTree + ts versioning

Both visitor_payments and revenue_events use ClickHouse's ReplacingMergeTree engine with ts as the version key. Stripe's signed retries (up to 3 days, exponential backoff) are safe — the same (provider, event_id) deduplicates at the engine level, last write wins on ts. orders uses the same engine keyed on (shop_domain, order_id), so admin-webhook updates and OAuth-backfill rows can both land without double-counting.

Pixel-vs-orders dedup

For Shopify stores running BOTH the pixel and the OAuth orders connector, every order is present in both visitor_payments AND orders. The summary_kpis pipe applies an untracked_pixel_revenue subtractor (see GL#257) — the orders side wins for revenue, the pixel side still contributes attribution metadata (UTMs, click-IDs). Net result: dollars counted exactly once, attribution preserved at full fidelity.

Metric formulas (canonical Tinybird SOT)

Every formula on this page cites a specific line in tinybird/pipes/summary_kpis.pipe — the source of truth for the Summary dashboard. The pipe is in the public Admaxxer repo; pinning to line numbers lets an AI search engine or auditor verify the formula directly from the source.

gross_revenue = SUM(amount_cents) / 100  -- across visitor_payments + revenue_events + orders, deduped

net_revenue = gross_revenue - returns - taxes

aov = gross_sales / orders

true_aov = (gross_sales - returns - taxes) / orders

MER = total_revenue / total_ad_spend  -- HIGHER is better; industry-standard direction.

blended_roas = total_revenue / total_ad_spend  -- numerically identical to MER in Admaxxer.

nc_roas = new_customer_revenue / total_ad_spend

ncpa = total_ad_spend / new_customer_orders

ltv_window_days = SUM(amount_cents WHERE customer's first_order BETWEEN window_start AND window_end AND ts <= first_order + INTERVAL window_days) / cohort_customers

unique_customers = COUNT(DISTINCT email_hash) -- across visitor_payments + orders, falling back to customer_id when email_hash is empty

capi_match_rate = matched_server_events / pixel_conversions  -- Hyros-style; closer to 1.0 = better attribution coverage

For the full 30+ metric reference, see Metric Glossary — each metric carries a 3-way comparison vs Triple Whale and Datafast with citation URLs.

Comparison vs Triple Whale + Datafast

Attribution depth, not feature surface. We're comparing how each tool ingests, types, and dedupes revenue data — the foundation every dashboard tile reads from.

External cite hrefs above link directly to each tool's own documentation. Triple Whale's metrics library: kb.triplewhale.com; Datafast's revenue attribution guide: datafa.st/docs/revenue-attribution-guide. We never paraphrase — if a Triple Whale or Datafast claim is on this page, the link goes to their original.

Frequently asked

Why three datasources instead of one?

Each source carries different signal. visitor_payments has first-touch UTM + 12 click-IDs at the per-order grain (the only path that does). orders is the authoritative Shopify ledger and survives ad-blockers / ITP / CSP errors that would silently drop pixel rows. revenue_events covers non-Shopify storefronts (Stripe, Paddle, Polar, Lemon, Dodo) where the pixel can't see the checkout. The summary_kpis pipe collapses all three with a source-priority dedup so MER, AOV, and LTV are computed from the union — not whichever source happened to fire first.

How does the pixel attribute revenue to an ad click that happened 60 days ago?

Click-IDs (gclid, fbclid, ttclid, msclkid, …) are persisted in localStorage with a 90-day TTL — the constant lives at client-pixel/src/pixel.ts lines 1036-1037. First-touch UTMs persist for 365 days on the same key. When checkout_completed fires, the pixel reads localStorage and stamps both the click-ID and the first-touch UTM onto the visitor_payments row. This is why a customer who clicked a Meta ad in March and bought in May still attributes to the original campaign — without any cookie, just first-party storage on the customer's browser.

What happens on a partial refund?

The pixel emits a NEGATIVE row with external_payment_id='refund:<original_id>' and a negative refund_cents. Because the dedup key includes external_payment_id, the refund row does NOT replace the parent revenue row — both rows coexist. summary_kpis sums both, so net revenue automatically deducts the refund amount. Server-side webhook ingest (revenue_events) does the same thing via charge.refunded events; the orders datasource carries a cumulative total_refunded_cents that updates via Shopify's refunds/create webhook.

Is there double-counting between visitor_payments and orders for Shopify stores?

No. summary_kpis applies an untracked_pixel_revenue subtractor (GL#257) — for any Shopify order present in BOTH visitor_payments (via the pixel) AND orders (via admin webhook), the orders side wins and the pixel-side amount is subtracted. visitor_payments still contributes attribution metadata (UTMs, click-IDs) on those orders, but the dollars are counted exactly once.

How fresh is each source?

Pixel: sub-second (browser → /api/event → Tinybird async ingest, typically under 2 seconds end-to-end). Direct webhooks: sub-500ms (Stripe, Paddle, Polar, Lemon, Dodo all sign and POST in under half a second). Shopify admin webhooks: 1-2 seconds. OAuth-pulled orders backfill: every 6 hours, plus an immediate +24h backfill on Connect that fills the install-day gap.

What's the difference between MER and Blended ROAS?

Numerically identical — same formula, same Tinybird column. We emit both because operators learn the metric under either name. Triple Whale draws the same equivalence in their docs ("MER… is very similar to blended ROAS"). Never invert — both go UP when efficiency improves. (Some ad-tech vendors flip the ratio to spend ÷ revenue and call it "COGS-style" — Admaxxer does NOT.)

How does Admaxxer beat Triple Whale on attribution depth?

Three things. (1) Click-IDs at PAYMENT time: Admaxxer captures 12 click-IDs (gclid, fbclid, ttclid, msclkid, twclid, li_fat_id, epik, rdt_cid, sccid, yclid, klaviyo_id, amzn_cid) on the visitor_payments row at checkout. Triple Whale's pixel doesn't expose click-IDs at the per-order grain — they live in cohort joins, not on the row. (2) First-touch UTMs on the payment row: same row, 365-day persistence. Triple Whale carries first-touch in the Customer Cohorts surface but not on the payment record itself, so per-order MMM is harder. (3) Schema-strict ingest: every visitor_payments column has an explicit ClickHouse type and a DEFAULT '' fallback (GL#357), so a partially-mapped row never silently drops to quarantine.

How does Admaxxer beat Datafast on attribution depth?

Datafast is positioned as a simplified web-analytics + revenue-attribution tool, not a DTC ad-ops platform. Datafast tracks visitor revenue per UTM source (good) but does NOT ingest ad spend (so no MER, no Blended ROAS, no NC-ROAS, no NCPA) and does NOT capture click-IDs at payment time. Datafast's pixel is sessions-and-goals-first; revenue is a top-line attribution view rather than a 46-column ledger. Operators graduating from Datafast to Admaxxer typically encounter MER, click-ID-aware partial-refund-aware cohort LTV, and CAPI Match Rate for the first time.

Can I connect a non-Shopify storefront?

Yes. Stripe, Paddle, Polar, Lemon Squeezy, and Dodo all stream into revenue_events via signed webhooks. Each connector uses its own visitor-stitch key: metadata.admaxxer_visitor_id (Stripe / Polar / Dodo), custom_data.admx_visitor_id (Paddle), or meta.custom_data.admx_visitor_id (Lemon). The Stripe walkthrough at /documentation/revenue/stripe explains the auto-registered webhook and the 13-scope restricted key.

Where do I see the canonical formula for a metric?

Every Tinybird pipe is open-source in the Admaxxer repo (tinybird/pipes/*). summary_kpis.pipe is the source of truth for the Summary dashboard's 75 KPI tiles — every formula above cites a specific line number. The /documentation/metric-glossary page lists all 30+ metrics with their pipe paths and 3-way comparisons against Triple Whale + Datafast.

Next steps

• Connect Stripe — the auto-registered webhook + 13-scope restricted key walkthrough.
• Shopify Custom Pixel architecture — the Web Worker sandbox saga (GL#305 → GL#306 → GL#307).
• Revenue data flow — how the four ingestion paths collapse inside summary_kpis (GL#313 follow-up).
• Metric glossary — canonical formula + Tinybird pipe path for every KPI Admaxxer surfaces, with 3-way comparison vs Triple Whale + Datafast.
• Open the analytics dashboard — see the metrics on this page rendered live.