Metric Definition and Implementation Questions

Direct answer: Correct for sampling by inverse-weighting each sampled record (dividing by the sampling rate) before aggregating, estimate margin-of-error from the sample size and observed variance using standard proportion/mean confidence-interval formulas, and treat metrics as unusable once the effective sample size for a specific segment becomes too small for the resulting margin-of-error to be practically meaningful.

Structured elaboration:

• Impact of sampling: a naive count on a 10%-sampled stream understates the true total by roughly (but not exactly) a factor of 10; the imprecision compounds when segmenting (a segment that's rare in the underlying population becomes even rarer, and noisier, within a 10% sample of it).
• Adjusting via weights: each sampled row represents 1 / sampling_rate real events (e.g., a 10% sample means each observed row stands in for ~10 real events); multiply raw counts by this inverse weight to get an unbiased estimate of the true total, e.g., estimated_total = observed_count / sampling_rate.
• Estimating margin-of-error: for a proportion metric, the standard error is approximately sqrt(p*(1-p)/n) where n is the SAMPLE size (not the estimated true population size), and the margin-of-error at a given confidence level scales with that standard error; report the margin-of-error alongside any sampled estimate, since a 10% sample of a small segment can have a genuinely wide interval even though the point estimate looks precise.
• When sampling makes metrics unusable: once the sample size for a specific segment drops low enough that the margin-of-error becomes a large fraction of the estimate itself (e.g., a rare segment with only a handful of sampled rows), the resulting number is not usable for a decision requiring real precision, even though the weighted point estimate can still be computed and will look like a normal number if the margin-of-error isn't shown alongside it.

Worked example: a 10% sample observing 50 conversions out of 2,000 sampled visitors gives a weighted estimate of 500 conversions out of 20,000 total visitors (inverse-weighted by 10x); the sample proportion is 50/2000 = 2.5%, with a standard error of about sqrt(0.025*0.975/2000) ≈ 0.0035, giving roughly a ±0.7 percentage-point margin-of-error at 95% confidence, which is usually fine for an aggregate rate; the SAME calculation for a segment with only 20 sampled visitors and 1 conversion would give a much wider, far less trustworthy margin-of-error, illustrating exactly why the same sampling rate is fine in aggregate but can make a granular segment-level metric unusable.

Trade-offs & pitfalls: presenting only the inverse-weighted point estimate, without its margin-of-error, is misleading precisely because the weighted number LOOKS just as precise as an unsampled count; always show or at least document the margin-of-error, and set an explicit minimum-sample-size threshold below which a segment's sampled metric is flagged as unreliable rather than reported at face value.

Direct answer: Define an explicit, deterministic tie-break rule (e.g., "the promotion with the largest discount wins; if tied, the one applied first wins") for the case where multiple promotions overlap in one session, implement it as a single deterministic pass over the session's promotions ordered by the rule's priority, and explicitly enumerate and test the nested/stacking edge cases rather than leaving them to fall out of the implementation accidentally.

Structured elaboration and pseudocode:

WITH ranked_promos AS (
  SELECT session_id, promo_id, discount_pct, applied_at,
    ROW_NUMBER() OVER (
      PARTITION BY session_id
      ORDER BY discount_pct DESC, applied_at ASC   -- highest discount wins; earliest breaks ties
    ) AS rn
  FROM session_promotions
)
SELECT session_id, promo_id AS attributed_promo
FROM ranked_promos
WHERE rn = 1;

Rule choice and why it matters: "highest-discount wins" is a defensible default because it reflects which promotion most influenced the purchase decision economically, but it is a CHOICE, not a fact; "first-promotion" (attribution to whichever was applied first) or "last-promotion-within-24h" (a recency-weighted rule similar to S25's attribution windows) are equally legitimate alternatives serving different business questions (which promo the customer responded to FIRST vs which one was economically decisive vs which one was most recent), and the choice should be stated explicitly, not left implicit in the code.

Nested promotions and promo stacking, as explicit edge cases:

• Stacking allowed: if the business genuinely allows two promotions to apply simultaneously (a discount PLUS free shipping), the attribution rule needs to handle MULTIPLE winners per session for different promo TYPES, not force a single winner across incompatible categories; the rule above should be scoped to "the winning DISCOUNT promo" specifically, with a separate rule for other promo types.
• Nested/conditional promotions: a promotion that only activates if another is also present (a "stack bonus") needs its OWN qualifying logic checked before it even enters the ranking, since it may not have been genuinely eligible on its own.
• Both should be enumerated in the documentation and covered by dedicated test fixtures (S18's pattern), not discovered as bugs later when a real customer's overlapping-promo session produces an unexpected attribution.

Worked example: a session with a 10%-off promo applied at 10:00 and a 20%-off promo applied at 10:05 (a customer who found a better deal moments later): the "highest-discount-wins" rule attributes the purchase to the 20%-off promo, while a "first-promotion" rule would instead attribute it to the 10%-off one; running the query on a small synthetic fixture with exactly this scenario confirms the SQL's ORDER BY discount_pct DESC correctly picks the 20%-off promo as rn = 1.

Trade-offs & pitfalls: implementing the rule without first explicitly deciding and documenting WHICH rule the business wants (rather than picking whichever is easiest to write in SQL) is the most common mistake here; the rule is a business decision with real revenue-attribution consequences (it determines which promotion's cost is "credited" with driving each sale), not a technical detail to be decided incidentally by the engineer writing the query.

Direct answer: Three dedup strategies cover most cases: order-id (or event-id) based dedup for a system with a stable idempotency key, session-window dedup for click/view-type events that lack a natural key, and cross-device probabilistic dedup when the same real action is logged from multiple identifiers (web + mobile) with no shared key at all.

Structured elaboration:

1. Order-id / event-id based dedup: keep one row per business key (e.g., order_id), typically the latest by ingestion time. Fails when: the upstream system does NOT guarantee a stable key across retries (e.g., a client-side retry generates a new event_id for what is logically the same user action), producing silent over-counting no dedup step catches, because there is nothing to key on.
2. Session-window dedup: collapse events from the same user within an inactivity gap (commonly 30 minutes) into one session, then count once per session rather than once per raw event. Fails when: a genuinely fast second visit (user leaves and comes back in 10 minutes for an unrelated reason) gets merged into the same session and undercounts distinct usage occasions; the window boundary is also timezone/DST-sensitive.
3. Cross-device probabilistic dedup: match events across web_events and mobile_events using signals like hashed email, IP + user-agent pattern, or timing proximity, when there is no shared login identifier. Fails when: two DIFFERENT users share a device fingerprint (a household on one Wi-Fi network, a shared kiosk), causing a false-positive merge that undercounts unique users, or conversely a probabilistic match set with too strict a threshold fails to merge a real duplicate and overcounts.

Worked example: for a "purchase" metric, order-id dedup is nearly always correct if the checkout system assigns a stable order_id before retries; you would NOT reach for session-window or probabilistic dedup here, because a stable key already exists, and using a fuzzier method would only introduce risk for no benefit. Session-window dedup is the right tool for a "page view" metric with no natural key. Probabilistic dedup is reserved for genuinely cross-surface identity problems (e.g., "how many distinct people visited," not "how many purchases"), because it trades exactness for coverage.

Trade-offs & pitfalls: The choice is not a checklist to apply all three; it is matched to whether a reliable key exists. Applying session-window dedup where an order-id already exists throws away precision for no reason; applying probabilistic dedup by default (instead of only when cross-device identity is genuinely required) introduces false merges into every downstream metric. State which strategy you used and why in the metric's documentation, because a reviewer cannot tell from the output number alone which failure mode is live.

Direct answer: Attribute the refund to the ORIGINAL purchase month by default (so a month's revenue reflects net revenue for purchases made that month, a "vintage" view), and additionally expose a refund-date view for operational/cash-flow purposes; doing both, clearly labeled, resolves the trade-off rather than forcing a single choice that serves one audience and misleads another.

Structured elaboration:

• Attribute to original purchase month (vintage/net-revenue view): answers "how much revenue did purchases made in June ultimately generate, after all refunds," which is the more meaningful number for evaluating a specific month's SALES quality and for financial metrics like LTV that need to reflect true net value per acquisition cohort. Cost: this view changes RETROACTIVELY as later refunds come in, so "June's revenue" reported today may differ from "June's revenue" reported a month from now as more refunds land, which needs to be documented as an expected property of this view, not a bug.
• Attribute to refund month (cash/operational view): answers "how much net cash moved this month," which is what an operational cash-flow dashboard cares about; this view is stable once a month closes (refunds affect the month they occur in, not a past month), but conflates "how good was June's sales" with "how much refund activity happened in whatever month that activity landed."
• Recommendation: implement BOTH as separate, clearly named metrics (monthly_revenue_by_purchase_vintage vs monthly_net_cash_by_transaction_month) rather than picking one and hoping it serves every audience; most disputes here come from two teams silently assuming different conventions while calling the result by the same ambiguous name ("monthly revenue").

Worked example: a $100 purchase in June refunded in August: the vintage view attributes -$100 back to June (so June's TRUE net revenue, once fully settled, is 0 for that purchase), which is the right number for evaluating June's cohort quality; the cash view attributes -$100 to August (June showed +$100 at the time, August shows the -$100 offset), which is the right number for August's actual cash-flow statement.

Trade-offs & pitfalls: The vintage view requires the ETL to support RETROACTIVE updates to a previously-published month's number as refunds continue arriving (which needs the same versioned/data_version discipline discussed for other retroactively-adjusted metrics in this topic, so consumers can tell a "final" June number from a "still settling" one); implementing the vintage view without that retroactive-update discipline, and instead freezing June's number the day the month closes, silently produces an inaccurate vintage number that never gets corrected, which is arguably worse than not offering the vintage view at all.

Direct answer: Join on the most durable shared key available (typically user_id), explicitly document what happens to rows where that key is missing or inconsistent between the two systems, and treat event latency and any product-vs-billing discrepancy as expected, monitored phenomena rather than errors to eliminate entirely.

Structured elaboration:

• Join keys: engineering telemetry usually keys on an application-level user_id or a device/session identifier; billing systems usually key on an ACCOUNT or CUSTOMER identifier, which may not be 1:1 with the application's user_id (a household or company account can map many app users to one billing account). Establish and document the actual cardinality of this relationship before assuming a simple 1:1 join.
• Missing/inconsistent IDs: a user active in the product but never billed (a free-tier user) will have no billing-side match at all, which is EXPECTED and should not be treated as a join failure; a user with a billing record but no matching telemetry (perhaps billed via an offline/manual invoice process) is a different, genuinely worth-investigating case. Distinguish these two "unmatched" categories explicitly in the join's output rather than lumping all unmatched rows into one undifferentiated bucket.
• Event latency: telemetry can arrive with delay (seconds to hours depending on the pipeline); a revenue-impact analysis joining "today's" telemetry to "today's" billing events should use a consistent, documented latency buffer (e.g., only compare data at least 24 hours old) rather than comparing a fully-arrived billing feed against a still-arriving, incomplete telemetry feed, which would understate the correlation and mislead the analysis.
• Reconciling product vs billing discrepancies: expect them, and build the comparison to explain the gap (e.g., billing includes taxes/currency conversion the product event stream never carried) rather than treating the mere existence of a gap as evidence something is broken.

Worked example: measuring "does using feature X correlate with higher revenue" requires joining feature-usage telemetry (keyed by app user_id) to billing (keyed by account_id), via an account-to-user mapping table; a naive direct join on mismatched key types would silently produce zero matches or (if the systems happen to share a coincidentally similar-looking ID space) a WRONG join that looks like it worked. Validating row counts on both sides of the join, before and after, is what catches this class of bug early.

Trade-offs & pitfalls: The most damaging failure mode here is a join that runs without error and returns a plausible-looking row count, while actually matching the wrong entities due to an unstated cardinality mismatch; always validate the join's fan-out (rows-in vs rows-out on each side) against the expected cardinality before trusting the result.