Meta Data Analyst Interview Preparation Guide - Entry Level

Direct answer

Designing a communication cadence across a stakeholder map means deciding, for each group, how often, through what channel, and at what level of detail they hear from you, based on where they sit on the map, and building in a clear trigger for moving someone between tracks rather than treating the initial assignment as permanent.

Structured elaboration

1. Tie cadence to the stakeholder's actual position, not a one-size-fits-all schedule. An executive sponsor track might get a concise monthly summary focused on outcomes and risk; a working-team track might get detailed weekly updates focused on progress and blockers.
2. Match format to the audience's real need, not just frequency. The executive track likely wants a short written summary they can read in two minutes; the working-team track likely benefits from a live discussion where questions can surface in real time.
3. Define explicit triggers for moving someone between tracks. A stakeholder whose interest or power shifts (a reorg, a new deadline that suddenly involves them, an escalation) should move tracks based on a stated trigger, not be forgotten in whichever track they started in.
4. Build in a feedback loop. Periodically checking whether the cadence still fits (are executive-track people asking for more detail than the summary provides, are working-team members feeling over-communicated to) catches drift before it becomes disengagement.

Worked example

An executive sponsor track for a multi-quarter initiative gets a one-page monthly summary focused on milestones hit, risks, and decisions needed from them specifically; the working-team track gets a weekly quarter-hour sync focused on blockers and near-term work. When a scope change suddenly makes a previously executive-track stakeholder need working-level detail (because their team now owns a piece of execution), they move to the working-team track with an explicit note on why, rather than continuing to receive only the high-level monthly summary that no longer serves their actual need.

Trade-offs and pitfalls

Too many tracks becomes as unmanageable as no structure at all; two or three clear tracks, each with an explicit trigger for movement between them, is usually enough, and adding more granularity than that mostly adds overhead without improving anyone's actual experience.

Direct answer

Pick a mentor relationship where you can point to something concrete the mentor actually did, not just "they were supportive," describe specifically how their input changed your day-to-day behavior, not just your mindset, and describe the outcome honestly, including where it shows up in ordinary, checkable ways rather than an invented statistic.

Structured elaboration

• What the mentor did. Name the concrete mechanism: regular pairing sessions (working through problems together in real time), specific feedback on a recurring type of mistake, or career advice at a decision point, rather than a vague "they mentored me." The more specific the mechanism, the more credible the story.
• How you incorporated it into day-to-day work. This is the part that shows real internalization: describe a habit or practice you adopted, not a one-time change. If a mentor consistently pushed you to think about failure modes before shipping, describe how you now build that into your process by default, not just that one time you did it because they were watching.
• Measurable outcomes. Be honest about what "measurable" means here. Sometimes it's a genuinely countable change, fewer of a specific kind of mistake recurring, a skill you can now do independently that you couldn't before. Sometimes it's more qualitative, being trusted with more ambiguous work, being asked to help someone else in that same area. Don't manufacture a false precision to sound rigorous; describe what you actually observed changing.

Worked example

A mentor runs regular pairing sessions on debugging approach, and the recurring feedback is that the recipient jumps to fixing the first plausible cause instead of first confirming the actual root cause. Incorporation into day-to-day work: a habit of writing down what to expect to see before running a test, specifically to catch the moment an assumption is wrong rather than just going straight to a fix. Outcome: over the following months, the number of "fixes" that had to be reverted because they addressed the wrong root cause drops from roughly two a month to zero over the following quarter, and eventually others start asking to pair on hard bugs, itself a sign the skill transferred rather than just being useful with the mentor watching.

Trade-offs and pitfalls

Describing the mentor relationship only in feelings, "they believed in me," with no concrete mechanism or behavior change, doesn't demonstrate you actually acted on anything. Claiming a precise, invented metric to sound impressive is worse than describing what was genuinely observed. Crediting the mentor for a skill you'd have developed anyway, rather than being specific about what was actually different because of them, weakens the story. And treating the relationship as finished, rather than describing anything ongoing, can read as a one-off rather than a real habit change.

Reach for INTERSECT and EXCEPT (with UNION/UNION ALL as the third member of the family). Given campaign A's converted users and campaign B's converted users as two sets, "only in A" is A EXCEPT B, "only in B" is B EXCEPT A, and "in both" is A INTERSECT B. That maps directly onto the business question, and all three set operators automatically deduplicate, so you don't need a separate DISTINCT.

Why name the set operators specifically

The three-way split (only A, only B, both) is textbook set algebra, and SQL's set operators express it more directly than reconstructing the logic with joins or CASE flags:

• UNION / UNION ALL: combine two row sets. UNION dedupes, UNION ALL keeps every row including duplicates. Use UNION ALL when you explicitly want to preserve or count duplicates; use UNION when you want the combined distinct set.
• INTERSECT: rows present in both inputs, deduplicated.
• EXCEPT (a.k.a. MINUS in Oracle): rows in the first input that are not in the second, deduplicated.

Both sides of a set operation must have the same number of columns with compatible types; here that's just user_id, so it's a clean fit.

Worked example

Run in sqlite3. INTERSECT/EXCEPT are ANSI-standard and supported by Postgres, SQL Server, Snowflake, BigQuery, and MySQL 8.0.31+; older MySQL versions lack INTERSECT/EXCEPT entirely and need the flag/CASE approach discussed below instead. Oracle spells EXCEPT as MINUS.

Seed two audience lists, deliberately including a duplicate row in campaign A's list (a common reality: audience exports aren't always pre-deduplicated):

-- campaign_a_conversions(user_id): 1, 2, 3, 4, 2  (note the duplicate 2)
-- campaign_b_conversions(user_id): 3, 4, 5, 6

SELECT 'only_A' AS segment, COUNT(*) AS user_count
FROM (SELECT user_id FROM campaign_a_conversions EXCEPT SELECT user_id FROM campaign_b_conversions)
UNION ALL
SELECT 'only_B', COUNT(*)
FROM (SELECT user_id FROM campaign_b_conversions EXCEPT SELECT user_id FROM campaign_a_conversions)
UNION ALL
SELECT 'both', COUNT(*)
FROM (SELECT user_id FROM campaign_a_conversions INTERSECT SELECT user_id FROM campaign_b_conversions);

Run output (A = {1,2,3,4} once deduplicated, B = {3,4,5,6}):

segment  user_count
-------  ----------
only_A   2
only_B   2
both     2

That matches the sets directly: only A is {1, 2}, only B is {5, 6}, both is {3, 4}.

INTERSECT/EXCEPT dedupe on their own, which is worth proving rather than assuming: campaign A's raw list has 5 rows (the duplicate 2), but SELECT DISTINCT user_id from it returns 4:

raw_a_rows        distinct_a_users
----------        ----------------
5                 4

The only_A/both counts above already reflect that dedup, no extra DISTINCT was needed in the set-operation query.

Trade-offs and pitfalls

• UNION ALL + GROUP BY flag approach as an alternative: instead of three separate set-operation subqueries (each a full scan of one or both tables), you can tag every row with its source, UNION ALL them into one stream, then aggregate with MAX(CASE ...) flags per user in a single pass. That trades readability for fewer full scans, worth it once the underlying tables are large and read from the same event log rather than two clean pre-built lists.
• Column alignment: set operators match columns positionally, not by name; if A and B ever gain a differently-ordered or extra column, the query breaks or silently misaligns. Always SELECT explicit columns on both sides.
• UNION vs UNION ALL cost: UNION's implicit dedup means an extra sort/hash step; if you already know the two inputs are disjoint or you want raw counts including duplicates, UNION ALL is cheaper and correct.
• NULLs in the key column: if user_id can be NULL on either side, INTERSECT/EXCEPT treat NULL as matching NULL (unlike a WHERE a.id = b.id join predicate, where NULL never equals NULL), which can be surprising if you're used to join semantics.

Direct answer

Beyond CUPED (using a pre-experiment covariate to residualize the outcome), the two other standard variance-reduction levers are stratified (blocked) randomization, which forces balance on a known factor at assignment time instead of hoping random chance balances it, and covariate or regression adjustment, which is the general case of "adjust for a predictive covariate" that CUPED is one specific, pre-experiment-only instance of. Both work by removing a source of outcome variance that is not related to treatment, so the same true effect becomes easier to distinguish from noise; both reduce required sample size roughly in proportion to how much outcome variance the factor explains, and neither invents a new number, they trade a known, explainable source of variance for a smaller residual.

Structured elaboration

Stratified (blocked) randomization

Instead of randomizing the whole population as one pool, split the population into strata on a factor known before assignment (country, device type, new vs. returning user), then randomize independently within each stratum so each arm gets a matched share of every stratum. This removes between-stratum variance from the treatment-effect estimator's variance, because the strata are balanced by design rather than by luck: with plain randomization on a highly imbalanced population, an unlucky split (e.g., treatment skewing toward the low-baseline country) inflates the observed variance of the effect estimate even though the true effect is unaffected.

It is applicable whenever you have a discrete, pre-assignment factor that is known to correlate with the outcome and is stable at randomization time. It differs from covariate adjustment in when the correction happens: stratification acts at assignment time (balance is enforced), while regression adjustment acts at analysis time (balance is estimated and subtracted after the fact). The two are complementary, not substitutes: stratify at assignment for the factors you can, and adjust for continuous covariates at analysis.

Covariate / regression adjustment

This is the general technique of fitting a model for the outcome on one or more covariates (not restricted to pre-experiment-only, unlike CUPED) and using the model to remove predictable variance from the outcome before comparing arms, most simply via ANCOVA (analysis of covariance), a linear regression of $Y$ on the treatment indicator and covariates that removes the variance those covariates explain from the comparison, the same variance-reduction logic as CUPED and stratification, just carried out as a regression rather than a pre-experiment covariate or a balanced split. It is applicable whenever you have covariates, pre-experiment or otherwise as long as they cannot themselves have been affected by treatment, that are predictive of the outcome. CUPED is the special case where the covariate is restricted to a pre-experiment value of the outcome metric itself; regression adjustment generalizes this to any number of eligible covariates and lets you combine several weak predictors into one stronger adjustment.

Effect on sample size and power

For both techniques, if the factor being controlled for explains a fraction $R^2$ of the outcome's variance, the variance of the treatment-effect estimator shrinks by roughly that same factor, and required sample size for a fixed target precision shrinks proportionally, since sample size for a fixed effect and power scales with the variance of the metric. A factor that explains little of the outcome variance buys little; a strong, well-chosen factor can meaningfully shorten the required test duration for the same statistical bar.

Worked example: stratifying a multi-country test

A test is planned across three countries with very different baseline conversion rates: Country A at 4%, Country B at 12%, Country C at 22%, in roughly equal traffic shares (each about one third of total users). Without stratification, plain randomization can by chance send more of one country's traffic to one arm, and even without that bad luck, the pooled outcome variance includes the between-country spread of baseline rates as extra noise the estimator has to average out.

Using the law of total variance, the overall variance of the outcome decomposes as:

$\operatorname{Var}(Y) = \underbrace{\mathbb{E}\big[\operatorname{Var}(Y \mid \text{country})\big]}_{\text{within-country variance}} + \underbrace{\operatorname{Var}\big(\mathbb{E}[Y \mid \text{country}]\big)}_{\text{between-country variance}}$

Stratifying by country and analyzing as a weighted average of within-country treatment effects removes the second (between-country) term from the treatment-effect estimator's variance, since each stratum is separately balanced and the between-stratum spread no longer contributes noise to the comparison. Concretely: with baseline rates of 4%, 12%, 22% and equal stratum weights, the between-country component of variance is

$\bar p = \frac{0.04+0.12+0.22}{3} = 0.1267$

$\operatorname{Var}(\bar p) = \frac{1}{3}\Big[(0.04-\bar p)^2 + (0.12-\bar p)^2 + (0.22-\bar p)^2\Big] = \frac{1}{3}(0.00751 + 0.0000445 + 0.00871) = 0.00542$

That 0.00542 is exactly the between-country variance component the stratified analysis removes from the pooled estimator's variance, computed directly from the three stated baseline rates, not asserted; how large a share of total variance that is depends additionally on the within-country binomial variance at each rate, which you would combine with this term using the same decomposition to get the full picture before quoting an overall percentage reduction.

Implementation for the multi-country case

• Assign the stratum at randomization time using the same deterministic hash-bucketing approach as the overall unit assignment, but nest it: hash within each country separately (or include country in the hash key) so each country independently hits its target split ratio.
• At analysis time, estimate the treatment effect within each country and combine as a weighted average (weighted by stratum size or by inverse variance), rather than pooling raw counts across countries, which is what actually realizes the variance reduction shown above.

Trade-offs and pitfalls

• Stratifying on too many dimensions at once shrinks individual strata until some contain too few units to balance meaningfully, and can create empty or near-empty cells, especially when crossing multiple categorical factors (country times device times cohort).
• A stratification factor chosen because it is convenient rather than because it is predictive buys little variance reduction while adding real implementation complexity; check the factor's explanatory power on historical data before committing the assignment pipeline to it.
• Regression adjustment on covariates measured close to, but not strictly after, the treatment start needs the same scrutiny as CUPED's pre-experiment-only requirement: any covariate that could plausibly be influenced by treatment invalidates the adjustment's unbiasedness, not just its efficiency.

For a new recommendation engine, the primary metrics should directly measure whether the recommendations are being acted on and monetized, while supporting metrics catch whether that lift is coming at the expense of user trust or content diversity.

Metric set

Perverse incentive to watch for

Optimizing purely for CTR rewards recommending sensational, clickbait-adjacent, or already-popular items rather than genuinely useful ones, since a system can raise clicks by surfacing items the user was likely to buy anyway (cannibalizing organic discovery) or by choosing attention-grabbing but low-relevance items that get clicked once and never again. This shows up as CTR rising while post-click satisfaction signals (return visits to the same category, low bounce, revenue per click) stay flat or fall, so the supporting diversity and session-quality metrics exist specifically to catch this pattern before it's mistaken for a genuine win.

Trade-offs and pitfalls

A pure revenue-per-click primary metric alone can reward recommending expensive items over items the user actually wants, so pairing CTR with revenue (not either alone) keeps the incentive aligned with genuine relevance, not just monetary value per click.

When designing strategic dashboards you must balance three competing dimensions: accuracy (how correct the metric is), timeliness (how fresh/fast it updates), and cost (engineering, compute, storage, and licensing). Improving one often worsens another: real‑time, row‑level accuracy is expensive; sampling or aggregation cuts cost and latency but reduces precision.

Prefer faster, slightly less accurate metrics when decisions are exploratory or operational and speed matters:

• Example: A marketing live-dashboard showing “estimated active users last 5 minutes” where you use event sampling or approximate distinct-count (HyperLogLog). Stakeholders need trends to react to campaign spikes; a ±2–5% error is acceptable versus the cost of exact counts every minute.
• Example: Fraud triage queue length using stream-aggregates to route investigators. Fast, approximate counts help prioritize workloads even if they’re not perfectly precise.

Prioritize accuracy despite latency when decisions are high-stakes, audited, or financially material:

• Example: Monthly revenue recognition, invoice totals, or billing reconciliations must be exact; use batch ETL with full joins and source-of-truth reconciliation even if it takes hours/days.
• Example: Regulatory compliance reports (KYC, financial disclosures) where errors cause legal risk — prioritize deterministic pipelines and lineage.

Practical guidance:

• Classify metrics by decision criticality and set SLAs (e.g., “tactical: <5m, ±5%; strategic: daily, ±0.1%”).
• Use hybrid approaches: real-time approximations for monitoring + periodic exact reconciliations; show confidence intervals and data freshness on dashboards.
• Optimize cost: materialize high-value aggregates, compress raw logs, and use approximate algorithms only where acceptable.

This trade-off framework ensures dashboards deliver the right fidelity at the right cost for each business decision.

Direct answer
Most large employers that codify their interview values name broadly similar underlying traits, even when their specific vocabulary differs: a customer or user-first orientation, taking ownership beyond a narrow scope, moving with appropriate urgency, holding a high quality bar, and being trustworthy and transparent recur across nearly every published framework, just under different labels.

Structured elaboration

Worked example
The table above is itself the worked example. A strong candidate should be able to reproduce a table like this from memory for whichever specific company's list they are asked about, translating each of that company's named principles onto one of these five underlying traits, rather than treating an unfamiliar company's vocabulary as an entirely new set of ideas to learn from scratch.

Trade-offs and pitfalls
Treating every company's list as identical is itself a mistake; the values differ in emphasis, and in what is explicitly left off the list. A company whose published list omits any explicit ownership language may culturally deprioritize individual initiative in favor of process, for example, and that is worth noticing rather than flattening away. A candidate who can only speak the vocabulary of one company, fluent in one set of terms but unable to translate the same underlying trait into a different company's language, reads as having memorized rather than internalized the competencies involved.

Direct answer

Linear attribution splits each conversion's value equally across every touchpoint that falls inside a bounded conversion window before it: find the conversion window for each converting user, count the touches inside it, and divide the conversion's revenue by that count. At scale, the two things that determine whether this stays fast are the join key (partition and cluster by user_id so a user's touches and conversion live together) and whether the window filter is applied before or after the count, since counting first and filtering second silently attributes revenue to touches outside the intended window.

Structured elaboration

Approach. Three steps, expressed as common table expressions (CTEs), which are standard American National Standards Institute (ANSI) SQL and portable across engines:

1. conversions: pull every row where is_conversion = 1, giving each converting user's conversion timestamp and revenue.
2. window_touches: join every non-conversion touch back to that user's conversion, keeping only touches whose elapsed time before the conversion falls inside a fixed conversion window (a business-defined parameter, 30 days below).
3. touch_counts and the final aggregation: count touches per (user, conversion) pair, then for each surviving touch, credit revenue / touch_count to its channel, and sum across all conversions grouped by channel.

Performance at scale. Three concerns dominate once the touches table is large:

• Join and partition strategy. The join between touches and conversions is keyed on user_id; on a distributed warehouse (a columnar engine such as Snowflake, BigQuery, or Redshift), clustering or partitioning the physical table by user_id (or a date-bucketed user_id) keeps this join local to each partition instead of triggering a full shuffle across the cluster.
• Window functions over self-joins where the engine supports it. The self-join pattern above is correct and portable, but on very large tables a window-function form (COUNT(*) OVER (PARTITION BY user_id, conversion_id) computed directly on a pre-filtered, already-windowed row set) avoids materializing an intermediate join result twice, since the count and the later per-row division can share a single pass over the same partitioned data. This is a real optimization worth reaching for on a large warehouse table, described here as a design recommendation, not something separately benchmarked in this answer.
• Filter before count, always. The date-window predicate must be applied inside the join that produces window_touches, never as a HAVING or post-aggregation filter; applying it too late would count touches outside the window into touch_counts while still excluding their revenue from the numerator, silently understating every channel's credit. The edge-case check in the Worked example below verifies this ordering is correct, not just assumed.

Worked example

Schema and pinned synthetic data (four users: a 3-touch conversion, a 1-touch conversion as a sanity check, a 5-touch conversion to make the equal-split arithmetic non-trivial, and a 3-touch user where two touches fall outside a 30-day conversion window and must be excluded), executed with Python's stdlib sqlite3:

CREATE TABLE touches (
    user_id       TEXT NOT NULL,
    touch_id      INTEGER NOT NULL,
    channel       TEXT NOT NULL,
    occurred_at   TIMESTAMP NOT NULL,
    is_conversion INTEGER NOT NULL DEFAULT 0,  -- 1 marks the conversion row itself
    revenue       DECIMAL(10,2)                -- non-NULL only on the conversion row
);

INSERT INTO touches VALUES
 -- user_1: 3-touch conversion, revenue $90 (each touch worth $30)
 ('user_1',1,'paid-search','2024-04-20 09:00:00',0,NULL),
 ('user_1',2,'email','2024-04-30 09:00:00',0,NULL),
 ('user_1',3,'organic-search','2024-05-05 09:00:00',0,NULL),
 ('user_1',4,'conversion','2024-05-10 09:00:00',1,90.00),
 -- user_2: 1-touch conversion, sanity check, revenue $60
 ('user_2',1,'social','2024-05-09 09:00:00',0,NULL),
 ('user_2',2,'conversion','2024-05-10 09:00:00',1,60.00),
 -- user_3: 5-touch conversion, revenue $250 (each touch worth $50); paid-search at touch 1 and touch 5
 ('user_3',1,'paid-search','2024-04-15 09:00:00',0,NULL),
 ('user_3',2,'social','2024-04-20 09:00:00',0,NULL),
 ('user_3',3,'display','2024-04-25 09:00:00',0,NULL),
 ('user_3',4,'email','2024-04-30 09:00:00',0,NULL),
 ('user_3',5,'paid-search','2024-05-08 09:00:00',0,NULL),
 ('user_3',6,'conversion','2024-05-10 09:00:00',1,250.00),
 -- user_4: 3 raw touches, 2 outside the 30-day window (email, display), 1 inside (paid-search), revenue $120
 ('user_4',1,'email','2024-03-31 09:00:00',0,NULL),
 ('user_4',2,'display','2024-04-05 09:00:00',0,NULL),
 ('user_4',3,'paid-search','2024-04-25 09:00:00',0,NULL),
 ('user_4',4,'conversion','2024-05-10 09:00:00',1,120.00);

Linear attribution query (the date-difference expression below uses SQLite's julianday() so this exact query is directly runnable; on a production warehouse, swap only that expression for the engine's own day-difference function, DATEDIFF, date_diff, or interval subtraction, the surrounding ANSI SQL structure, CTEs, joins, and aggregation, is unchanged):

WITH conversions AS (
    SELECT user_id, touch_id AS conversion_touch_id, occurred_at AS conversion_at, revenue
    FROM touches
    WHERE is_conversion = 1
),
window_touches AS (
    SELECT t.user_id, c.conversion_touch_id, t.touch_id, t.channel, c.revenue
    FROM touches t
    JOIN conversions c
        ON c.user_id = t.user_id
       AND t.is_conversion = 0
       AND julianday(c.conversion_at) - julianday(t.occurred_at) BETWEEN 0 AND 30
),
touch_counts AS (
    SELECT user_id, conversion_touch_id, COUNT(*) AS n_touches
    FROM window_touches
    GROUP BY user_id, conversion_touch_id
)
SELECT wt.channel, ROUND(SUM(wt.revenue * 1.0 / tc.n_touches), 4) AS attributed_revenue
FROM window_touches wt
JOIN touch_counts tc ON tc.user_id = wt.user_id AND tc.conversion_touch_id = wt.conversion_touch_id
GROUP BY wt.channel
ORDER BY attributed_revenue DESC;

Output (actually executed with python3, sqlite3 stdlib module):

-- Linear attribution, per channel --
('paid-search', 250.0)
('social', 110.0)
('email', 80.0)
('display', 50.0)
('organic-search', 30.0)

sum(attributed_revenue) = 520.0
sum(conversion revenue) = 520.0

By hand: the $250 5-touch conversion splits into $50 per touch, and paid-search appears twice in that one path (touch 1 and touch 5), so it alone carries $100 of that conversion; added to $30 from the 3-touch conversion and $120 from the single in-window touch of the 30-day-filtered conversion, paid-search totals $250, matching the query exactly.

A second useful query: last-touch vs. linear, side by side, for the same conversions. Built by adding a ROW_NUMBER() OVER (PARTITION BY user_id, conversion_touch_id ORDER BY occurred_at DESC, touch_id DESC) ranking to the same window_touches CTE, taking rn = 1 as the last touch, and joining the two credit tables with a FULL OUTER JOIN on channel so a channel that only ever appears in one model still shows a 0 in the other rather than being silently dropped:

last_touch_rank AS (
    SELECT wt.*, ROW_NUMBER() OVER (
        PARTITION BY wt.user_id, wt.conversion_touch_id
        ORDER BY wt.occurred_at DESC, wt.touch_id DESC
    ) AS rn
    FROM window_touches wt
),
last_touch_credit AS (
    SELECT channel, SUM(revenue) AS last_touch_revenue
    FROM last_touch_rank WHERE rn = 1 GROUP BY channel
)
SELECT COALESCE(l.channel, lt.channel) AS channel,
       ROUND(COALESCE(l.linear_revenue, 0), 4)      AS linear_revenue,
       ROUND(COALESCE(lt.last_touch_revenue, 0), 4) AS last_touch_revenue
FROM linear_credit l
FULL OUTER JOIN last_touch_credit lt ON lt.channel = l.channel
ORDER BY channel;

Output (actually executed):

('display', 50.0, 0.0)
('email', 80.0, 0.0)
('organic-search', 30.0, 90.0)
('paid-search', 250.0, 370.0)
('social', 110.0, 60.0)

sum(linear_revenue) = 520.0
sum(last_touch_revenue) = 520.0
total conversion revenue = 520.0

Both columns independently sum to the full $520 of conversion revenue, since each model fully partitions every conversion on its own; they simply disagree about WHICH channel gets the credit. display and email never happen to be a last touch across these four conversions, so they read $0 under last-touch despite earning real linear credit, a concrete illustration of why the two models can tell very different stories about the same underlying data.

Complexity

• The core query is one join (touches to conversions, both filtered), one GROUP BY for counts, and one more aggregation: $O(T)$ where $T$ is the number of touch rows within the conversion window, assuming the join uses an index or a partition-local hash join on user_id rather than a full cross-table scan.
• The window-function form (ROW_NUMBER() OVER (...)) used for the last-touch comparison adds a sort within each (user_id, conversion_touch_id) partition; on a columnar warehouse this is typically implemented as a partitioned sort, $O(T \log k)$ where $k$ is the average touches per conversion, which is small (single digits to low tens) even when $T$ is enormous, so this stays cheap in practice.
• Space: the intermediate window_touches CTE materializes at most one row per (touch, conversion) pair inside the window, bounded by $T$, not by the full unfiltered touch table, provided the window predicate is pushed into the join as shown rather than applied afterward.

Edge cases

• Single-touch conversion (user_2 above): linear and last-touch degenerate to the same answer, verified directly and in isolation: [('social', 1, 60.0)], one touch, full $60 credited, which both models must agree on by construction.
• Conversion window filter must actually exclude stale touches, not just compile. user_4 has three raw touches but only one within 30 days of its conversion; verified directly: all raw touches for user_4: [(1, 'email', ...), (2, 'display', ...), (3, 'paid-search', ...)] versus touches surviving the 30-day window filter: [(3, 'paid-search')], confirming exactly two of three were correctly excluded.
• The same channel appearing more than once in one conversion path (user_3's two paid-search touches): each occurrence is still one of the five equally-weighted touches, so the channel's total credit for that conversion is $2 \times \frac{250}{5} = \$100$, not capped at a single share; this is a deliberate property of linear attribution, not a bug.
• A user with touches but no conversion never enters conversions at all, so none of their touches are ever counted or attributed, correctly excluding non-converting activity from revenue attribution entirely.
• NULL revenue only ever appears on non-conversion rows in this schema; the query never reads revenue from a non-conversion row, so a NULL there is inert by construction rather than something the query needs to guard against separately.

Trade-offs and pitfalls

• Linear attribution assumes every touchpoint contributed equally, which is rarely true. A touch two minutes before conversion and a touch three weeks before conversion get identical credit here; a time-decay or position-based model (more credit to first and last touch, as one common alternative) captures that intuition, linear does not, and that is a modeling choice to make explicitly, not an oversight to fix inside this query.
• Common mistake: filtering the conversion window after computing touch_counts instead of before. Doing the filter late would count out-of-window touches into the denominator while still excluding their revenue share from ever being attributed, quietly shrinking every in-window touch's credit for no principled reason; the CTE order above avoids this specifically by filtering inside window_touches, the very first step.
• Common mistake: assuming every conversion has at least one touch. If instrumentation gaps mean some conversions genuinely have zero touches in the window, that conversion's revenue is silently excluded from sum(attributed_revenue), which is why validating that the attributed total equals the true conversion-revenue total (done explicitly above) is not optional, it is the only way to catch this kind of silent data loss.
• FULL OUTER JOIN support is not universal. It rendered correctly in this environment's SQLite build, but some engines and older SQLite builds do not support it directly; the portable fallback is a UNION of two LEFT JOINs (one anchored on each side), which produces the identical result set and is worth knowing as the fallback pattern rather than assuming FULL OUTER JOIN is always available.

Direct answer

Contrast, before-and-after, and the 'so what' chain are three different ways to make a number land; contrast works by comparison, before-and-after works by showing change over the SAME thing, and the 'so what' chain works by repeatedly answering "and why does that matter" until you reach a decision.

Structured elaboration

• Contrast: place the finding next to something the audience already has intuition for. For a conversion decline, contrast it against a competitor benchmark or against the company's own historical best, so the number has a reference point rather than floating in isolation.
• Before-and-after: show the SAME metric at two points in time, ideally visually side by side, so the change itself is the story rather than either snapshot alone.
• 'So what' chain: state the fact, then ask "so what does that mean" and answer it, then ask "so what does THAT mean" again, continuing until you reach something the audience can act on, rather than stopping at the first, still-abstract answer.

Worked example

For a conversion rate decline from 5% to 3% over six months:

• Contrast: "Our 3% conversion rate is now below the 4% industry benchmark for our category, when six months ago we were above it."
• Before-and-after: a simple two-bar chart, "5% six months ago" next to "3% today," with the six-month trend line in between showing it wasn't a single cliff but a steady erosion.
• So-what chain: "Conversion fell from 5% to 3%. So what? At our current traffic, that's about 2,000 fewer customers a month. So what? At our average order value, that's roughly $180,000 in monthly revenue. So what? That's larger than the cost of the fix we're proposing, which is why I think this is worth prioritizing this sprint."

Trade-offs and pitfalls

Each technique fails if pushed past its natural stopping point: a contrast against a benchmark that isn't truly comparable (different industry, different customer base) misleads more than it clarifies; a before-and-after chart with too many intermediate points buries the headline change in noise; and a so-what chain that keeps going past the point of actionability starts to feel like a lecture rather than a build-up to a decision. Stop the chain exactly at the sentence that makes the case for action, and no further.

Direct answer: Treat this as a governance problem, not a math problem: get both stakeholders to agree on what the launch decision is actually FOR (what business outcome the launch is supposed to serve), and use that to arbitrate which metric is the right proxy, rather than trying to average or split the difference between two competing metrics.

Structured elaboration

• Ask what decision this metric is meant to inform. If the launch's real purpose is topline revenue growth, revenue per visitor is the more direct proxy; if the purpose is funnel health or the CFO's revenue-per-visitor framing is actually capturing a mix effect (e.g., driven by fewer, higher-value visitors rather than a real conversion improvement), conversion rate may better isolate the mechanism the feature actually changed.
• Check whether the two metrics disagree because they are measuring genuinely different things, or because one of them is contaminated by a confound (a change in traffic mix, a pricing change, a seasonal effect) that has nothing to do with the feature; resolving that confound often resolves the apparent disagreement.
• If both metrics are clean and they still tell different stories, do not treat this as something to average; instead compute what each stakeholder's preferred metric is actually a PROXY for, and report both numbers alongside the underlying business objective, letting the decision-maker choose with full information rather than letting the metric choice make the decision by default.
• Where possible, pre-agree the primary metric before running the pricing experiment in the first place, since litigating the metric AFTER seeing results (when each side has already seen which metric favors their view) is far harder to resolve fairly than agreeing on it in advance.

Worked example: A pricing experiment shows conversion rate up 8% but revenue per visitor down 3%. Investigation shows the conversion gain is concentrated in a lower-price-point segment the experiment nudged people toward, meaning more people convert but at a lower average price, which is exactly the mechanism a growth lead would want to see (funnel health, more paying customers) but also exactly what a CFO focused on revenue-per-visitor would flag as concerning if the company's near-term goal is revenue growth rather than customer count. Once both sides see that the two metrics are telling a consistent, non-contradictory story about the SAME mechanism (a price-sensitivity effect), the decision becomes about company priorities (grow paying-customer count now vs. protect near-term revenue per visitor) rather than about which metric is "right."

Trade-offs and pitfalls: A common mistake is treating disagreement between two legitimate metrics as a data problem to be resolved by more analysis, when it is often a priorities problem that no amount of analysis can resolve without an explicit decision about what the company is optimizing for right now. The opposite mistake is picking whichever metric was pre-agreed as "the" primary metric and ignoring the other entirely, even when the second metric reveals something the first metric's narrow lens genuinely misses (like the price-sensitivity mechanism above).