Amazon Business Intelligence Analyst (Staff Level) - Comprehensive Interview Preparation Guide

Requirements & objective:

• Measure incremental ROAS of a new paid channel (causal lift = incremental revenue / incremental ad spend).
• Use geo-based randomized holdout to avoid user-level targeting issues.

1. Geo selection (treatment vs control)

• Unit = contiguous DMAs/metro areas with sufficient sample size.
• Pre-filter geos by historical revenue volume, population, and homogeneity (exclude tiny/noisy or outlier geos).
• Pair-match geos on pre-period revenue, trend, seasonality, and demographics using propensity-score matching or Mahalanobis distance; then randomly assign one of each pair to Treatment or Control to balance covariates.
• Aim for >= 20 geos per arm if possible; power calculation (see below) sets minimum.

2. Pre/post measurement windows

• Pre-period: 8–12 weeks to estimate baseline and match; longer if business is highly seasonal.
• Test (post) period: 6–12 weeks minimum to capture learning and ad fatigue; use daily/weekly granularity.
• Allow a short ramp (1–2 weeks) excluded from measurement or modeled separately.

3. Compute incremental revenue and costs

• For each geo, compute observed revenue_post and predicted baseline revenue_post_hat (extrapolated from pre-period using time-series model or ANCOVA adjusting for pre-period revenue).
• Incremental revenue = sum_treatment (revenue_post - revenue_post_hat) - sum_control (revenue_post - revenue_post_hat) if controls adjust for common shocks, or compute difference-in-differences (DiD):
  Incremental = (mean_rev_T_post - mean_rev_T_pre) - (mean_rev_C_post - mean_rev_C_pre)
• Ad costs = actual spend in treatment geos (and any spend leakage into control). Incremental ROAS = Incremental revenue / Incremental ad spend.

4. Statistical testing & significance

• Primary: hierarchical (geo-level) DiD regression:
  Revenue_it = α + β1Treatment_i + β2Post_t + β3*(Treatment_i * Post_t) + γ*PreControls_i + ε_it. β3 is incremental effect.
• Cluster standard errors at geo level.
• Bootstrap geo-level treatment assignment (block bootstrap) to get confidence intervals for incremental ROAS.
• Pre-test balance and run placebo tests by assigning fake treatment dates or placebo geos.
• Use minimum detectable effect and power analysis before experiment; aim for 80% power.

5. Handle spillover and seasonality

• Exclude adjacent geos likely to receive spillover (or mark them as buffer and not used for primary inference).
• Track cross-geo user movement and conversions (if available) and measure any conversion lift in control near borders.
• Model seasonality using seasonal terms (week/month fixed effects) or include control geo trends as covariates.
• If large national events occur, extend test or use synthetic control methods.

6. Practical deployment constraints

• Creative rollout: ensure identical creative/landing behavior across treatment geos; stagger rollouts only if intentional and modeled.
• Budget allocation: set spend caps per-geo to hit target CPM/CPC and avoid saturating small geos; monitor pacing to keep spend proportional across treatment geos.
• Measurement latency: agree on attribution window (e.g., 7/14/28 days) and consistently apply across arms.
• Tracking & QA: verify impression/attribution tagging, dedupe events across platforms, and log spend by geo daily.
• Governance: pre-register analysis plan, hold blameless monitoring, and avoid mid-test major product changes.

7. Deliverables & dashboarding

• BI dashboard showing geo-level trends (pre/post), cumulative incremental revenue, spend, ROAS, CI bands, and diagnostics (balance, placebo tests, spillover indicators).
• Automated alerting if spend deviates or significance flips.

Key trade-offs:

• More geos vs larger geos: prioritize balance and power; synthetic control helps when few geos available.
• Longer test reduces noise but increases cost/time; include ramp and saturation checks.

This design yields an interpretable incremental ROAS with proper causal controls, statistical rigor, and operational safeguards for rollout.

Direct answer

Default to deterministic (exact-key) deduplication whenever a stable, trustworthy join key exists, because it is fast, fully explainable, and has zero false-positive risk. Reach for probabilistic (similarity/fuzzy) matching only when no reliable key exists across the records you need to merge, for example customer records arriving from systems with no shared identifier, because it trades perfect precision for the ability to catch real duplicates a key-based approach would simply miss.

Structured elaboration

• Deterministic dedup: match on an exact key (a primary key, a hashed and canonicalized email). Runtime cost is a GROUP BY/DISTINCT/window-function pass, it is trivially explainable to an auditor ("these two rows have the same customer_id"), and it never merges two genuinely different entities. Its failure mode is under-merging: two records for the same real person with typos in the key are left as separate rows.
• Probabilistic dedup: score candidate pairs on similarity across several fields (name, email, phone, address) using a blocking step to avoid comparing every row to every other row, then merge pairs above a tuned threshold. It catches the entity-resolution cases deterministic matching misses, at the cost of needing a labeled sample to tune the threshold, ongoing maintenance as data patterns shift, and a nonzero, tunable false-positive rate (two different people wrongly merged).
• Decision rule: use deterministic dedup as the default first pass on every table. Only add a probabilistic layer where a real business cost is attached to the residual duplicates a key-based pass leaves behind (e.g., double-counted customers inflating revenue-per-customer, or duplicate mail sent to the same household).

Worked example

A retailer discovers that dashboard "unique customers" is inflated because the same person signed up twice with j.smith@gmail.com and johnsmith@gmail.com. Deterministic dedup on customer_id or even canonicalized email finds nothing (the emails are genuinely different strings). A probabilistic pass scoring name similarity ("John Smith" vs "John Smith") at 100, combined with matching phone and shipping address, would surface this pair as a high-confidence merge candidate even though no single field ties them together exactly.

Trade-offs and pitfalls

The single biggest mistake is presenting probabilistic dedup results to stakeholders as if they were as certain as deterministic ones. A responsible answer states the expected false-positive and false-negative rate up front (measured against a labeled sample), keeps a human-in-the-loop review queue for borderline-confidence pairs rather than auto-merging everything, and never silently discards the pre-merge records: an auditable trail of what was merged into what, and why, is what lets a bad merge be reversed later.

Direct answer

Batch processes data in scheduled, bounded chunks, trading latency for simplicity and easier correctness reasoning over large windows; streaming processes each event (or small micro-batch) continuously as it arrives, trading operational complexity for low end-to-end latency. Which one fits a given pipeline stage comes down to how quickly the downstream consumer of that specific stage needs the data, and whether the transformation genuinely needs to see many records together to be correct.

Structured elaboration

What's actually different, beyond speed

• Latency floor: batch's floor is the schedule interval; streaming's floor is close to network plus per-event processing time.
• Processing model: batch works over a bounded, known dataset (you can look at "all of yesterday" at once); streaming works over an unbounded sequence, which needs explicit mechanisms (windows, watermarks) to decide when a group of events is "done," something batch gets for free by waiting for the whole file or table partition to land.
• Resource pattern: batch spins compute up for a burst and releases it; streaming holds resources continuously, so idle-time cost matters more.
• Failure and replay model: batch failures resume naturally by re-running the job over the same input range; streaming failures need checkpointing and offset tracking to resume from where processing actually stopped.

Decision criteria for a given stage

Applying this per stage, not to the whole pipeline

The choice is not binary across an entire pipeline. An ingestion stage might run as streaming so no event is ever lost on the way in, while a downstream aggregation stage stays batch because it genuinely needs a full day's data to compute a stable metric. Decide stage by stage based on what that stage's own consumer needs, not what the source technology happens to support end to end.

Worked example

Say a stakeholder's service-level agreement (SLA) requires a metric to be no more than 5 minutes stale, but the relevant stage currently runs on a 24-hour batch schedule:

$\frac{24 \times 60 \text{ min}}{5 \text{ min}} = 288$

The batch cadence is 288 times looser than the freshness requirement, which rules batch out for that stage regardless of how fast the job itself runs once triggered; something closer to streaming or frequent micro-batch is needed there. Contrast a weekly executive report with the same 24-hour batch cadence: nightly batch already clears that bar with days to spare, so adding a streaming layer there would add operational cost without changing the outcome anyone sees.

Trade-offs & pitfalls

• Choosing streaming because it feels more modern, when the downstream consumer only checks the data once a day, means paying continuous infrastructure cost for freshness nobody uses.
• Trying to shrink a batch stage's schedule down to minutes eventually rebuilds a fragile streaming system, without the tooling (checkpointing, backpressure, idempotent writes) that makes real streaming safe.
• Streaming generally raises the bar on ordering and duplicate handling, since there is no "look at the whole day at once" moment to reconcile against; batch defers that complexity to reprocessing time instead.
• Treating the choice as pipeline-wide, rather than per stage, is the most common wrong turn; most real pipelines end up as a mix once examined stage by stage.

Requirements and constraints:

• Cross-platform (web, iOS, Android), single canonical event stream for BI
• Measure Acquisition, Activation, Retention, Monetization (AARR)
• Low-latency for experiments, high data quality, schema versioning, attribution support
• Events must be small, consistent, and include identity + context

High-level approach:

• Define a canonical event taxonomy (event name, properties with types and required flag)
• Use a strict naming convention and shared schema registry (JSON Schema / Avro)
• Client SDKs emit events to an ingestion layer (e.g., Segment / Snowplow / custom collector) → streaming (Kafka) → raw lake + validated warehouse (dbt transforms)
• Enforce validation and backfill policies; keep immutable raw events

Core events (required) — example fields (type: required/optional):

1. user_identify

• user_id: string (required)
• anon_id: string (optional)
• traits: object (optional)
• timestamp: iso8601 (required)

2. page_view / screen_view

• event: string (page_view / screen_view)
• user_id: string (nullable)
• anon_id: string (string, required)
• url / screen_name: string (required)
• referrer: string (optional)
• campaign_source: string (optional)
• timestamp: iso8601 (required)

3. acquisition_source (fire once per session if present)

• source: string (utm_source/click_id) (required)
• medium: string (optional)
• campaign: string (optional)
• click_id: string (optional)
• timestamp

4. sign_up (activation)

• user_id: string (required)
• method: enum [email, google, apple, phone] (required)
• referral_code: string (optional)
• timestamp

5. first_key_action (custom per product - e.g., first_upload, first_search)

• user_id
• action_name: string (required)
• metadata: object (optional)
• timestamp

6. session_start / session_end

• session_id: string (required)
• user_id / anon_id
• platform: enum [web, ios, android] (required)
• device_info: object (optional)
• timestamp

7. retention_ping (heartbeat daily)

• user_id
• day_number: int (days since signup)
• active: boolean
• timestamp

8. purchase / subscription_event (monetization)

• user_id
• order_id: string (required)
• sku: string
• price_usd: float (required)
• currency: string (required)
• payment_method: enum
• revenue_type: enum [one_time, subscription]
• subscription_status: enum [start, renew, cancel] (if applicable)
• timestamp

9. experiment_exposure

• user_id / anon_id
• experiment_id: string (required)
• variant: string (required)
• timestamp

10. attribution_click / install

• user_id / anon_id
• click_time: iso8601
• ad_network: string
• campaign_id: string
• creative_id: string
• click_id: string
• timestamp

Naming convention:

• event names: snake_case, verb_noun for actions (sign_up, purchase, first_upload), nouns for state (session_start)
• property names: snake_case, clear types, avoid platform prefixes
• enums declared in schema registry

Schema versioning:

• Store schemas in a central registry (e.g., Confluent Schema Registry or Git + docs)
• Use semantic versioning: event_name:v1, event_name:v2
• Each event payload includes schema_version: "v2" and event_name field
• Backwards-compatible changes allowed (add optional fields). Breaking changes require new version and migration plan.
• Deprecation policy: maintain old versions for N months; include transformer jobs to map old -> canonical warehouse model.

Data quality & validation:

• Client-side input validation + server-side schema validation in ingestion
• Rejection queue for invalid events; automatic alerting for schema drift
• Automated nightly data quality checks (counts by platform, null rates, unique user counts) with thresholds

How this supports A/B testing:

• experiment_exposure event ties users to variants; store exposures as first-touch and all exposures
• Exposures include experiment_id, variant, timestamp, and assignment_source
• Use consistent user_id/anon_id and session_id to deduplicate exposures
• Warehouse transforms produce experiment cohorts, attribution windows, and incrementality metrics (p-values, lift)
• Track events as raw facts; derived metrics (conversion rates, revenue per user) computed per variant

How this supports attribution:

• Capture campaign params at first touch and latest touch (first_touch_campaign, last_touch_campaign) on acquisition_source and attribution_click
• Persist campaign attribution on user profile (first_touch_attribution object) when available
• Include click_id and ad_network to enable deterministic matching (e.g., SKAdNetwork, MMP)
• Build funnels joined on user_id/anon_id and timestamps; compute cohort-level LTV by acquisition cohort

Practical notes for BI:

• Provide canonical event-to-model mapping docs for analysts
• Materialize common aggregates: DAU/MAU, new_users_by_source, 7/30-day retention, ARPU/ARPPU, LTV tables
• Tag platform and SDK versions to track instrumentation rollout
• Expose experiment and attribution dimensions in shared semantic layer (Looker/Metabase) for self-serve reporting

This plan ensures consistent cross-platform telemetry, reliable experiment measurement, and clear attribution while giving BI the structured, versioned raw and transformed data needed for accurate dashboards and analyses.

Direct answer

When a small number of superusers dominate an average behavioral metric, the average stops representing a typical user, so robust reporting means leading with metrics less sensitive to extreme values, such as the median or a specific percentile, and reporting alternative cuts like top-decile share separately rather than folding everything into one mean.

Structured elaboration

A mean is pulled arbitrarily far by a small number of extreme values because it weights every unit of activity equally regardless of who generated it; a median is far more robust because it only cares about the middle of the distribution's ordering, not the magnitude of the extremes. For behavioral data specifically, a useful complementary pair is the median (what a typical user looks like) alongside a measure of concentration, such as what share of total activity comes from the top decile of users, since that number directly answers "how dependent is this metric on a small group" in a way neither the mean nor the median does alone. When stakeholders need to see the effect of a change on power users specifically, reporting a percentile (p90 or p99) alongside the median gives visibility into that population without letting it distort the headline number.

Worked example

import numpy as np
np.random.seed(5)

n = 1000
activity = np.random.poisson(5, n).astype(float)          # most users: modest activity
superuser_idx = np.random.choice(n, size=int(0.02 * n), replace=False)   # top 2%
activity[superuser_idx] += np.random.gamma(shape=2, scale=150, size=len(superuser_idx))

print('mean:', round(activity.mean(), 2), ' median:', round(np.median(activity), 2))
top_decile_share = activity[np.argsort(activity)[-100:]].sum() / activity.sum()
print('top-decile share of total activity:', f"{top_decile_share:.1%}")
print('superuser mean:', round(activity[superuser_idx].mean(), 1),
      ' non-superuser mean:', round(np.delete(activity, superuser_idx).mean(), 2))

Simulating 1,000 users' weekly event counts, where most users generate a modest, Poisson-distributed amount of activity but the top 2% (20 users) are superusers with much higher activity, gives a mean of 12.24 events per user but a median of only 5.00, a mean over twice the median. The top 100 users (the top decile) account for 65.5% of all activity in the simulation, and the 20 identified superusers alone average 361.8 events each, compared to 5.11 for everyone else. Reporting "average weekly engagement is 12.24 events" without qualification would meaningfully overstate what a typical user actually experiences, since more than 90% of users generated fewer events than that average.

Trade-offs and pitfalls

The main pitfall is reporting only the mean, or only the median, since each hides something the other reveals: the mean alone hides how unrepresentative it is of a typical user, and the median alone hides how much of the metric's total the tail contributes, which matters for questions like revenue or infrastructure cost that scale with total activity rather than with a typical user's activity. A second pitfall is treating superusers purely as a reporting nuisance to smooth away, when in practice they are often the most valuable and highest-retention segment of the user base, worth understanding and serving deliberately rather than only excluding from headline metrics.

Direct answer. Implement masking as a policy-driven layer between the warehouse and the BI (business intelligence) tool, evaluated at query time based on the requesting user's role, using format-preserving or reversible transformations rather than blank-out so aggregations still compute correctly and a small privileged group can request unmasking through an audited, separate step. Push enforcement into the database or warehouse layer wherever the platform supports it, rather than the BI-tool layer, because a database-layer control also protects anyone who queries the warehouse outside the BI tool, which a BI-tool-only control cannot.

Design constraints from the requirements

• Role-varying: the same column shows a different value depending on who's asking.
• Query-time: no separate masked copy of the table to keep in sync; masking is computed as part of query execution.
• Aggregation-safe: SUM, COUNT, AVG, and GROUP BY on masked columns must still produce correct results, which rules out simple redaction on numeric columns feeding aggregates. Redacting a salary column to NULL breaks AVG(salary); the transform has to hide the display value without disturbing the value the aggregate function actually reads.
• Reversible for a small privileged group: that group needs the real value back on request, which means format-preserving encryption or deterministic tokenization under a controlled key for that class of column, not a one-way hash.

Database layer versus BI-tool layer

Default to the database layer as the source of truth for enforcement, and treat any BI-tool-level control as a convenience on top, not a substitute, because auditability and coverage, the two properties that matter most for a PII (personally identifiable information) control, only come from the database layer regardless of which client connects.

Architecture

flowchart LR
    A[Warehouse: raw sensitive columns] --> B[Masking policy engine]
    B -->|role check at query time| C[Query result: masked or real value]
    C --> D[BI tool: dashboards, ad hoc queries]
    C --> E[Direct SQL clients]
    F[Privileged unmask request] --> G[Key/token service, audited]
    G --> B
    B --> H[Audit log: who saw what, when]

Privileged users don't hold a standing "see everything" role; they issue a scoped unmask request gated by a separate authorization check and a key or token service, and every request is logged with who, which column, which row, and when, so an auditor can answer "who saw the real value and why" without relying on the requester's own account.

Worked example. A customers table has a phone_number column. The masking policy shows analysts the last four digits with the rest replaced by X (XXX-XXX-1234); a COUNT(DISTINCT customer_id) tile still works because masking operates on the display value, not on the join key, which stays untouched. A fraud analyst with the privileged role calls the unmask function on one flagged row, which decrypts the format-preserving-encrypted value with a key scoped to that role, and the access is written to the audit log with the analyst's identity, the row's primary key, and a timestamp.

Trade-offs and pitfalls

• Column-level masking alone doesn't stop inference: filtering on unmasked quasi-identifiers together (WHERE city = 'X' AND age = 34) can narrow a masked-name row to one person; policy review has to look beyond single-column rules.
• Reversible tokenization makes key management as security-critical as the data itself; losing control of the key is equivalent to losing the masking.
• BI-tool-only masking is faster to ship but creates a false sense of security once anyone with warehouse credentials, a new hire, a data engineer debugging a pipeline, can query around it; that's the most common wrong turn in this design.

Direct answer: An event-level metric describes individual occurrences (one row per click, one row per page view); a user-level metric aggregates those occurrences up to one row per user (per some time window), answering "how many distinct users did X" rather than "how many times did X happen."

Structured elaboration: Compute at event-level when the question is inherently about volume or rate of occurrences (total page views, click-through rate on a specific banner). Compute at user-level when the question is about reach or behavior of people (DAU, percent of users who converted, average sessions per user). The SQL pattern to go from one to the other is a two-step aggregation: first collapse events to a per-user-per-day flag or count, then aggregate that intermediate table.

Worked example (executed): given clicks(user_id, click_time) with user 1 clicking 3 times on June 1 and once on June 2, and user 2 clicking once on June 1:

WITH daily_activity AS (
  SELECT user_id, date(click_time) AS day, COUNT(*) AS clicks_that_day
  FROM clicks
  GROUP BY user_id, date(click_time)
)
SELECT day, COUNT(DISTINCT user_id) AS daily_active_clickers
FROM daily_activity
GROUP BY day;

Run against a small SQLite table with exactly these rows, this returns (June 1, 2) and (June 2, 1): an event-level COUNT(*) on the same raw data would instead give (June 1, 4) and (June 2, 1), a materially different number for June 1 because it's answering a different question (how many clicks happened, not how many people clicked).

Trade-offs & pitfalls: The most common bug is silently mixing the two grains within one report: e.g., reporting "average clicks per user" as total_clicks / distinct_active_users_this_week when total_clicks was accumulated over a different window than the user count, producing a number that isn't a genuine per-user average for any single period. Always name the grain (event-level count, user-level count, or a ratio of the two over the SAME window) in the metric's label, not just in internal documentation, because dashboard consumers otherwise assume "clicks" and "active clickers" are interchangeable.

Direct answer
A classifier-threshold-selection dashboard should let a product manager see, for any candidate threshold, the resulting precision, recall, expected monetary cost, and predicted manual-review volume simultaneously, using a small set of linked, synchronized visuals (a precision-recall curve with a movable threshold marker, plus derived cost and volume tiles) so changing the threshold updates every downstream number at once.

Structured elaboration

1. Required data sources: the model's predicted scores and true labels on a representative validation set, plus a cost model (dollar cost of a false positive, dollar cost of a false negative, and the operational cost of a manual review).
2. Visual elements: a precision-recall (or ROC) curve with an interactive slider or draggable marker representing the candidate threshold; as the threshold moves, linked tiles recompute and display precision, recall, expected total cost, and predicted manual-review volume at that threshold.
3. Calculations: for a candidate threshold, compute confusion-matrix counts (true/false positives and negatives) on the validation set, derive precision and recall from those counts, multiply false positives and false negatives by their respective per-unit costs to get expected monetary cost, and count predictions falling into any "uncertain" band as the manual-review volume.
4. Controls: allow the user to see cost and volume plotted directly against threshold (a simple line chart of "cost vs. threshold" and "review volume vs. threshold") so a PM can visually identify the threshold that minimizes cost or meets a review-capacity constraint, not just read one static number.
5. Validating the dashboard's correctness: recompute the confusion matrix and derived metrics by hand for a couple of specific threshold values and confirm they match what the dashboard displays, and verify against a held-out or time-split validation set (not the same data the model was tuned on) to avoid overstating performance.

Worked example
Moving the threshold slider from 0.5 to 0.7 on the linked view shows precision rising, recall falling, expected cost first decreasing (fewer costly false positives) and then rising again past a certain point (missed true positives become the dominant cost), letting the PM visually identify the cost-minimizing threshold rather than picking an arbitrary round number. Concretely, at threshold 0.5 the linked tiles read precision 61%, recall 88%, expected cost $18,200, and review volume 640; moving to threshold 0.7 they read precision 79%, recall 55%, expected cost $14,900 (the minimum on this curve), and review volume 210; pushing further to threshold 0.9 they read precision 92%, recall 21%, expected cost $22,600, and review volume 40, with cost rising again because the false negatives missed at that threshold (real fraud or defects let through uncaught) now cost more than the false positives it avoids. A PM reading this curve would land close to 0.7, not 0.5 or 0.9, because that's where the expected-cost line actually bottoms out.

Trade-offs and pitfalls
A dashboard that shows precision/recall but omits the cost and volume translation leaves the actual business decision (where to set the threshold) unsupported; the whole point of this dashboard is translating a statistical trade-off into business-relevant units a PM can act on directly.

Approach (brief): Build a parametric sensitivity model that recalculates ROI given simultaneous changes to LTV uplift and adoption rate. Start from baseline: +5% LTV ⇒ $500K annual uplift. Define percent variation pairs: ±2%, ±5%, ±10% around both parameters (relative to baseline). Produce deterministic scenarios and an optional probabilistic Monte Carlo to communicate uncertainty.

Model details:

• Inputs: baseline LTV uplift % (5%), baseline uplift $ (500K), baseline adoption rate (A%), program cost (C), time horizon (T), discount rate (r).
• Calculations: uplift $ = baseline $ * (1 + ΔLTV), adjusted uplift = uplift $ * (1 + ΔAdoption), NPV/ROI = (adjusted uplift discounted over T − C) / C.

Visualizations to produce:

• Sensitivity table (matrix): rows = LTV variation (-10%, -5%, -2%, 0, +2%, +5%, +10%), columns = adoption variation same; cells show ROI or NPV. Conditional formatting heatmap to highlight ranges.
• Tornado chart: rank-order single-variable impact on ROI (vary LTV only and adoption only) to show which driver matters most.
• 2D contour / heatmap: LTV variation (x) vs adoption variation (y) with color scale for ROI bands; annotate break-even contour where ROI = 0 or target ROI threshold.
• Line chart: ROI vs LTV variation for multiple adoption scenarios (small multiples).

Decision thresholds and actions:

• Define thresholds up front: break-even ROI = 0, minimum acceptable ROI (e.g., 20%), and a “go/no-go” band.
• On the heatmap, mark safe (ROI ≥ target), marginal, and reject zones. Recommend conditional approvals (e.g., pilot if in marginal zone; full rollout if in safe zone).

Communicating uncertainty:

• Show deterministic grid plus a Monte Carlo density (if input uncertainty distributions available) to provide probability of ROI ≥ target.
• Use clear annotations: “With ±5% variation in both inputs, probability of meeting 20% ROI = X%.”
• Provide plain-English summary slide: key drivers, worst/best cases, likelihoods, recommended next steps (pilot, guardrails, KPIs to monitor).
  Practical deliverables: Excel model + interactive Power BI/Tableau dashboard with filters for LTV/adoption %, hover tooltips, and exportable slides for execs.

Direct answer. A REGEXP_CONTAINS scan with no date filter against a 5 TB unpartitioned table would need to read the entire dataset regardless of how few rows actually match, at an assumed illustrative rate of $5 per TB scanned that's roughly $25 for a single run of this one query (check your actual account's current rate card, since exact per-TB pricing varies by engine, region, and over time); partitioning by date and adding a date filter, or precomputing a boolean flag so the regex only has to run once (at write time) instead of on every read, would each cut that cost roughly in proportion to how much of the 5 TB the rewritten query actually needs to touch.

Structured elaboration. In a bytes-scanned billing model, the cost of a query is essentially independent of how selective its WHERE clause is UNLESS that WHERE clause can be resolved through partition or clustering metadata before reading the underlying data; a regex applied over an unbounded date range with no partition filter forces every byte of every column referenced (here, the payload column across the entire table) to be read and evaluated, regardless of how rare the matching rows actually are. The two effective levers, in order of how directly they attack the actual cost driver: adding a date filter against a date-partitioned version of the table, so the engine can skip entire partitions outside the query's range before reading anything, cutting bytes scanned roughly in proportion to what fraction of the full date range the query actually needs; and precomputing a boolean has_error flag at write/ingestion time (running the expensive regex once, when the row is written, rather than once per query execution), so read-time queries filter on a cheap boolean instead of re-evaluating a regex over the full payload column every single time.

Worked example. If the query genuinely only needs, say, the last 7 days out of roughly two years of retained data, and that 7-day slice is a representative roughly 1% of the table's total date range, partitioning and filtering to just that range would cut bytes scanned from the full 5 TB to roughly 50 GB; at that same assumed $5/TB rate that's a drop from about $25 per run to about $0.25 per run, a rough two-orders-of-magnitude reduction, purely from letting partition pruning skip the other 99% of the date range before reading it at all. Precomputing the boolean flag stacks on top of that: it removes the need to scan and evaluate the (often large) payload text column at query time altogether for the filtering step, leaving only whatever narrower set of columns the query actually needs to return.

Trade-offs and pitfalls. These numbers are illustrative, not audited against this specific engine's real billing edge cases (minimum billable scan sizes, metadata overhead, and caching behavior all vary by system and can shift the exact dollar figure); treat the ROUGH MAGNITUDE of the improvement (an order-of-magnitude-plus reduction from restricting the date range, an additional meaningful reduction from avoiding a full-column regex re-scan) as the reliable takeaway, and verify the exact billed figure against your specific account and query before reporting a precise number to a stakeholder.

Complexity

Bytes scanned drops roughly in proportion to (fraction of the date range actually queried) once partition pruning applies, and drops further once the expensive column (or the expensive regex evaluation over it) is removed from the read path entirely.

Edge cases

A minimum billable scan size or fixed per-query overhead, present in some billing models, means very small, highly-pruned queries don't necessarily scale their cost all the way down to zero linearly; check your specific engine's billing documentation for that floor before promising a stakeholder a cost reduction proportional all the way down.