Lyft Business Intelligence Analyst Interview Preparation Guide (Mid-Level)

Direct answer

A retro after a release with repeated design-engineering friction should produce two things: an honest, specific account of where the handoff actually broke down, not a vague 'communication issues,' and a small number of concrete process changes, each with an owner and a way to tell in a quarter whether it worked. Running it well means separating fact-finding from diagnosis, and diagnosis from blame.

Structured elaboration

Design principles for the session

• Facts before diagnosis: start from a timeline of what actually happened (spec dates, handoff dates, bug counts, points where implementation and design diverged), not from opinions about who was at fault.
• Root cause, not the nearest symptom: 'engineering didn't follow the spec' is a symptom; the root cause might be that the spec didn't capture edge-case states, or that both sides were working from different versions of a shared design system mid-migration.
• Few, high-leverage commitments: two or three process changes people will actually do beat ten action items that quietly get dropped.
• Everyone leaves with the same understanding of what changed, not just what went wrong.

A workable structure

One illustrative shape, adaptable to a team's own rhythm:

What 'actually changes how the two teams work' looks like

The output isn't a list of intentions, it's a specific artifact or habit that exists after the meeting and didn't before: a shared checklist embedded in the handoff process, an automated check that catches a class of mismatch before it ships, or a standing short sync during implementation windows. Whatever it is, it needs a way to tell if it worked, not just that it happened.

Worked example

One team's root cause turned out to be that design tokens (colors, spacing values) were maintained in the design tool but hand-copied into code, so drift was inevitable and nobody could tell which side was 'correct' when they disagreed. The concrete fix was an automated export from the design tool into the codebase, checked by both a design reviewer and a frontend reviewer before merge, plus a short recurring sync during active implementation. A quarter later, the team had a real signal that it worked: noticeably fewer visual-mismatch comments on pull requests and less late-stage rework than the release that triggered the retro. The same root-cause pattern shows up in other domains as a hand-copied data contract or config value instead of a design token, so the same fix shape (automate the handoff, add a lightweight check, add a short sync during the risky window) generalizes well beyond design and engineering specifically.

Trade-offs and pitfalls

• A retro that produces ten action items usually produces zero completed ones; prioritizing ruthlessly matters more than being thorough.
• If the room jumps straight to solutions or blame instead of facts first, the real root cause, often structural or tooling-related rather than a person's failure, never surfaces.
• A retro that isn't revisited becomes theater. Put the check-in on the calendar before the room disperses, not as a vague intention afterward.
• Watch for a fix that only addresses this specific release's symptom (a one-off manual double-check) rather than the structural cause; it holds for one cycle and then quietly stops happening.

Direct answer

DAU, WAU, and MAU are all the SAME underlying computation (distinct active users in a trailing window ending on a reference date), differing only in window length (1, 7, and 30 days respectively); computing all three efficiently for a large sliding history means avoiding a naive per-day full re-scan and instead either pre-aggregating daily distinct-user sets or using an incrementally-maintained approximate structure at very large scale.

Structured elaboration

The shared structure. Each of DAU, WAU, MAU is COUNT(DISTINCT user_id) WHERE event_date BETWEEN (ref_date - window + 1) AND ref_date, with window = 1, 7, 30. Writing all three as parallel subqueries (or CTEs) against the same base events table, sharing a single reference-date parameter, keeps the three numbers internally consistent (computed from the exact same underlying activity definition and reference point) rather than risking drift if they were computed by three separately-written queries at different times.

DAU/WAU as the "within-week" stickiness signal, distinct from DAU/MAU. DAU/WAU answers "of the people active this WEEK, how many show up on a typical single day," a tighter, faster-moving signal than DAU/MAU, useful for products with a strong weekly cadence where the interesting question is short-term consistency rather than month-scale reach.

Efficiency for distinct users over sliding windows, the real engineering question. A naive approach recomputes COUNT(DISTINCT user_id) from scratch for every reference date by re-scanning the full window's raw events, which is wasteful when adjacent reference dates share almost all of their underlying data (today's MAU window and yesterday's MAU window overlap in 29 of their 30 days). Two standard efficiency improvements: (1) PRE-AGGREGATE the base fact to one row per (user_id, activity_date) (deduplicated) once, so that all subsequent sliding-window queries at least start from a much smaller, deduplicated base rather than raw event volume; (2) for the sliding computation itself, either maintain a per-day distinct-user COUNT that can be summed incrementally is NOT valid for a distinct-count sliding window (since a user active on both an entering day and an already-counted day would be double-subtracted on removal), so exact distinct-count sliding windows generally still require a windowed re-aggregation over the deduplicated (user_id, date) base, whereas at very large scale (billions of users), an APPROXIMATE distinct-count structure (a probabilistic sketch that supports incremental merge/unmerge as the window slides) trades a small, bounded error for the ability to update the count incrementally rather than recomputing the full distinct set on every slide.

Worked example

Schema: events(user_id, event_date). Six rows across 4 users, with a duplicate row for one user to test deduplication, reference date 2026-01-31:

CREATE TABLE events (user_id INTEGER, event_date TEXT);
INSERT INTO events VALUES
 (1,'2026-01-31'),                                    -- active today: counts in DAU, WAU, MAU
 (2,'2026-01-28'),                                     -- active this week, not today: WAU, MAU only
 (3,'2026-01-10'),                                     -- active this month, not this week: MAU only
 (4,'2025-12-01'),                                     -- active over 30 days ago: none of the three
 (5,'2026-01-31'), (5,'2026-01-31'), (5,'2026-01-30'); -- duplicate row for u5, must not double count

WITH ref AS (SELECT date('2026-01-31') AS ref_date),
activity AS (SELECT DISTINCT user_id, date(event_date) AS d FROM events)
SELECT
    (SELECT COUNT(DISTINCT user_id) FROM activity, ref WHERE d = ref_date) AS dau,
    (SELECT COUNT(DISTINCT user_id) FROM activity, ref WHERE d BETWEEN date(ref_date,'-6 days') AND ref_date) AS wau,
    (SELECT COUNT(DISTINCT user_id) FROM activity, ref WHERE d BETWEEN date(ref_date,'-29 days') AND ref_date) AS mau;

Executed against SQLite. Output:

dau | wau | mau
2   | 3   | 4

DAU/WAU $= 2/3 \approx 0.6667$, DAU/MAU $= 2/4 = 0.5$. Manual check: DAU (active exactly on 01-31) is users 1 and 5, since 5's duplicate row still counts as one user; WAU (01-25 through 01-31) adds user 2 (active 01-28), giving {1,2,5}=3; MAU (01-02 through 01-31) adds user 3 (active 01-10), giving {1,2,3,5}=4; user 4 (active 2025-12-01, more than 30 days before the reference date) never enters any of the three windows.

Complexity

Time for a single reference date: $O(n)$ over the events considered within the largest window (MAU's 30 days), since the query is effectively three overlapping distinct-count aggregations sharing the same deduplicated base. Space: $O(u)$ for the distinct users in the widest window. Computing this for EVERY day over a long history naively (re-running the full query once per day) costs $O(d \times n)$ for $d$ days of history, which is where the pre-aggregation/incremental-structure discussion above becomes a genuine, not just theoretical, efficiency concern at scale.

Edge cases

• A user active only on the FIRST day of the 30-day MAU window (day 30 back from reference): correctly included in MAU, correctly excluded from WAU and DAU, since they fall outside those narrower windows.
• All activity for a user landing on exactly the reference date, with multiple duplicate rows (as with user 5): collapses correctly to one distinct-user contribution in every window that includes that date, confirming the DISTINCT step is load-bearing, not decorative.

Trade-offs and pitfalls

• A common performance mistake is computing this per reference date by re-scanning raw (non-deduplicated) events every time, rather than building the deduplicated (user_id, date) base once; at real event volumes (many events per user per day), this can mean re-scanning an order of magnitude more rows than necessary for every single day's DAU/WAU/MAU computation.
• Naively trying to maintain an EXACT distinct count incrementally by adding the entering day and subtracting the exiting day is mathematically wrong, since a user active on BOTH the exiting day and a still-in-window day would be incorrectly removed from the running total; this is exactly why exact sliding distinct-counts either re-aggregate over the window or require a more careful structure (a per-user last-seen-date index) rather than a naive running counter.
• Reaching for an approximate (probabilistic) distinct-count structure before the exact approach has actually become a measured bottleneck is premature optimization; the deduplicated-base-plus-windowed-aggregation approach shown here comfortably handles a wide range of real-world scales, and the approximate-structure discussion is included here as the honest answer to "how would this scale further," not as a default recommendation for every retention pipeline.

Direct answer

I start from the initiative's goals and work outward: who is directly affected by the outcome, who has to approve or fund it, who has to execute it, and who will be blamed if it goes wrong. Those four questions surface almost everyone that matters, and I deliberately look past the org chart for the last group.

Structured elaboration

1. Start with the obvious names. The sponsor, the immediate delivery team, and anyone explicitly named in the project charter.
2. Trace dependencies, not titles. I look at who has to change something (a system, a process, a policy) for this to succeed, since that person is a stakeholder even if nobody invited them. Concrete sources for this: org charts (as a starting point, not the final word), CRM or project records showing who has historically owned related decisions, and recurring-attendee patterns in planning meetings.
3. Look for the quiet approvers. Legal, security, finance, and compliance rarely show up in early conversations but can stop a launch cold. I ask "who has to sign off" explicitly rather than assuming I already know.
4. Find the informal influencers. Recurring meeting attendees, people whose name keeps coming up when others hedge ("I'd want to check with X"), and prior decision owners on adjacent work are all signals of real influence that doesn't show up on an org chart.
5. Segment engagement, don't treat everyone the same. Once I have the list, I classify by how much they need to be consulted versus simply informed, so my time goes where it matters (see the power/interest grid discussion for the mechanics of that classification: in short, a 2x2 that plots how much power someone has over the outcome against how much interest they have in it, sorting people into engagement styles like manage closely, keep satisfied, keep informed, or monitor).

Worked example

For a project re-architecting a shared data-ingestion layer, the obvious stakeholders are the analytics team requesting the change and my own engineering lead. Tracing dependencies surfaces four producer teams who will need to change how they publish data, and two downstream consumer teams whose dashboards will briefly go stale during cutover. Asking "who has to approve" surfaces a data-governance reviewer nobody mentioned in the kickoff. Watching who gets referenced repeatedly in planning conversations ("we'd need X's sign-off on schema changes") surfaces a senior engineer with no formal authority over the project but effective veto power because their team owns the shared library everyone depends on.

Trade-offs and pitfalls

The common failure is stopping at the first list and treating it as complete, which is how "hidden" stakeholders surface late and expensively. The other common failure is over-including: mapping everyone remotely touched by the change and giving them all the same engagement, which burns your own time and theirs. The map should change your ACTIONS (who you talk to, how often, how much detail), not just exist as a document.

Direct answer
Code review becomes a coaching tool the moment you separate what has to change before this merges from what's worth teaching, and handle each differently, since blocking mixes poorly with explaining. What counts as the important risk to teach toward also shifts by what's being reviewed: correctness and style for typical application code, reproducibility and data leakage for ML work, and blast radius for infrastructure changes.

Separate blocking feedback from teaching feedback

• Mark comments explicitly as blocking versus non-blocking (or use a similar convention), so the author isn't left guessing what actually has to change before merge. Teaching comments that aren't required for merge belong in the non-blocking bucket, otherwise you either water down real teaching moments to keep the change unblocked, or block a mergeable change to make a point.
• Ask before you tell: a comment phrased as a question ("what happens if this list is empty?") invites the author to find the issue themselves, which teaches the underlying reasoning; a comment phrased as an instruction just transmits the fix.

What "the important risk" means shifts by artifact type

• Typical application code: the coaching focus is usually correctness, readability, and test coverage; the failure mode being taught against is a defect shipping or the next person not being able to follow the change.
• ML notebooks and experiment configs: the review risk is different in kind, not just degree. The critical things to check and teach toward are reproducibility (is the seed pinned, is the environment specified, can someone else get the same result) and data leakage (does the training data have any path back to the evaluation set, directly or through a shared preprocessing step). A notebook can be clean, readable code and still be dangerously wrong for reasons that have nothing to do with code style.
• Terraform and other infrastructure-as-code changes: the review risk is blast radius, not defects in the traditional sense. A small, correct-looking diff can still be catastrophic if it touches a shared resource or removes a safeguard. Coaching here means teaching someone to ask what does this affect beyond what's in the diff before asking is this line correct.

Making it a genuine teaching moment, not just a gate

• When there's something worth teaching, don't just fix it in the comment; explain the why, and where useful, point to a real example elsewhere in the codebase rather than a generic principle.
• For anything too deep to unpack asynchronously in a comment thread, offer a short pairing session instead of a long comment chain; some things teach faster live than in writing.
• Close the loop: after a pattern comes up more than once for the same person, raise it directly in a 1:1 rather than only ever surfacing it inside individual review threads, so it becomes a recognized growth area instead of a recurring surprise.

Worked example
Reviewing a teammate's change that added a new model training script, the code itself was clean and well-tested in the conventional sense. The actual coaching moment was elsewhere: the evaluation split was built after a preprocessing step that had already seen the full dataset, which meant the reported accuracy was optimistic in a way unit tests would never catch. Rather than just fixing the split order and moving on, the comment walked through why that ordering matters (what leakage actually does to the reported number) and pointed to another script in the repo where the split happened correctly, before the shared preprocessing step. That change did get blocked, since the leakage was a real correctness issue, but the teaching part was the explanation of why, not the fact that it was blocked.

Trade-offs and pitfalls

• Making every comment a teaching moment, including on merge-blocking issues, slows delivery and can read as review turning into a lecture; save the deeper explanations for the genuinely worthwhile ones and keep routine fixes routine.
• Applying the same review lens (say, defect-finding) to every artifact type misses the risks that matter most for that artifact; a Terraform change reviewed like application code will pass style and correctness checks while missing blast radius entirely.
• If teaching moments only ever show up as isolated review comments and never get named directly to the person as a pattern, growth stays implicit and slower than it needs to be.

Direct answer

Daily active users divided by monthly active users, expressed as a percentage, is the DAU/MAU ratio, and it is commonly read as a stickiness signal: the higher the ratio, the larger the share of a product's monthly audience that comes back on a typical day. A ratio around 50% is often cited as very sticky (roughly half the monthly base shows up daily), while a ratio in the low single digits suggests most users engage only occasionally within a month.

Structured elaboration

The ratio is attractive because it is simple to compute and easy to explain, but it silently assumes that daily engagement is the right cadence for the product being measured. That assumption breaks for any product whose natural usage rhythm is not daily. A booking or travel product, for example, is used a handful of times a year by design, so even a healthy, engaged user base will show a DAU/MAU ratio that looks alarmingly low next to a social feed's ratio, even though the products are not failing at the same thing.

The ratio can also mislead in the other direction. A small subset of highly frequent users, or a population of bots and automated test traffic, can push the ratio up while the majority of the monthly base barely engages, so a healthy-looking DAU/MAU number does not by itself rule out a lopsided or contaminated user base. Seasonality causes the same kind of misleading movement: a ratio that dips every weekend for a workplace tool is not evidence of declining stickiness, it is evidence that the tool is used at work.

Worked example

For an infrequent-use product type such as a travel-booking app, DAU/MAU is a misleading stickiness signal. Consider a travel app with 100,000 monthly active users where each user books, at most, a handful of trips a year and opens the app mainly around those trips; on a typical day only a small fraction of that base has any reason to open the app at all, so a DAU/MAU ratio of, say, 2 to 3 percent can be entirely consistent with a healthy, retained user base rather than a sign that the product is failing to engage people. The metric that better complements DAU/MAU for this product type is something like trips-booked-per-active-user-per-year, or repeat-booking rate within a 12-month window, because it measures engagement at the cadence the product is actually used, rather than forcing a daily lens onto an inherently infrequent behavior.

Trade-offs and pitfalls

Before trusting a DAU/MAU trend, check that the definitions of "active" for both the daily and monthly windows are consistent (the same qualifying event, the same timezone convention), since a mismatch can move the ratio without any real change in user behavior. It is also worth pairing DAU/MAU with a distribution view, not just the single ratio, since a ratio computed from an average can hide a bimodal population of very frequent and very infrequent users that a single summary number flattens into an unremarkable middle.

Situation: A scheduled Spark job doing joins and aggregations frequently OOMs on worker nodes. Below is a practical step‑by‑step tuning checklist you can follow to reduce memory pressure and stabilize the job.

1. Reproduce & measure

• Run job on representative sample with spark.eventLog / metrics enabled.
• Note executor memory, shuffle/read/write sizes, peak storage and shuffle memory.

2. Memory settings

• Increase executor memory if cluster capacity allows: spark.executor.memory (e.g., 8–16g).
• Tune JVM overhead: spark.executor.memoryOverhead (10–20% or >=1g if high shuffle).
• Allocate cores per executor to balance memory/parallelism: fewer cores → more memory per task (e.g., 3–5 cores).

3. Execution/storage memory

• For Spark 2.0+, adjust unified memory fraction: spark.memory.fraction (default 0.6) and spark.memory.storageFraction (default 0.5) if caching or heavy shuffles require more execution memory.
• If caching small datasets, increase storageFraction; for heavy shuffles reduce storageFraction to prioritize execution.

4. Serialization

• Use Kryo serializer: spark.serializer=org.apache.spark.serializer.KryoSerializer.
• Register common classes (if custom objects): spark.kryo.registrationRequired=true and spark.kryo.classesToRegister=...
• Set spark.kryoserializer.buffer.max to a larger size (e.g., 64m) if you see buffer overflow warnings.

5. Partitioning strategies

• Ensure appropriate number of partitions: use .repartition(n) or spark.sql.shuffle.partitions (default 200). Scale partitions so each task handles ~100–300MB of shuffle data.
• For skewed keys, apply salt or use map-side aggregations (mapGroupsWithState, aggregateByKey) or use repartitionByRange / custom partitioner.
• Coalesce only for reducing partitions post-aggregation to avoid extra shuffles.

6. Shuffle and spill tuning

• Enable external shuffle service and set spark.shuffle.compress=true.
• Set spark.shuffle.spill.compress=true.
• Increase spark.reducer.maxSizeInFlight (default 48m) cautiously to reduce number of fetches.
• Use Tungsten / off-heap: spark.memory.offHeap.enabled=true and spark.memory.offHeap.size=<size> if configured.

7. Query & code-level changes

• Push filters/projections early (select only needed columns).
• Use broadcast joins when one side < spark.sql.autoBroadcastJoinThreshold (default 10MB). Increase threshold if appropriate: spark.sql.autoBroadcastJoinThreshold=50MB.
• For large joins, prefer sort-merge join; for skew use broadcast-hash or skewed join hints.

8. Monitoring & iterative adjustments

• Monitor stages with Spark UI: storage tab, SQL tab, executor metrics.
• Iterate: change one setting at a time and re-run.

Common specific config examples:

• spark.executor.memory=12g
• spark.executor.memoryOverhead=2g
• spark.executor.cores=4
• spark.serializer=org.apache.spark.serializer.KryoSerializer
• spark.kryoserializer.buffer.max=64m
• spark.sql.shuffle.partitions=800 (or tuned per data size)
• spark.memory.fraction=0.6
• spark.memory.storageFraction=0.3
• spark.sql.autoBroadcastJoinThreshold=50MB

Result: These changes reduce per-task peak memory, limit JVM heap churn, minimize shuffle pressure, and reduce OOM frequency. Start conservative, measure, and tune iteratively.

Don't refuse the request outright, and don't comply with it silently either. Acknowledge the real decision the executive is trying to support, make the risk of the specific metric concrete rather than arguing methodology in the abstract, and bring an alternative that meets the underlying need without shipping something misleading.

How to handle it

1. Find the real decision behind the ask. Ask what the executive will actually do with this number, what question it's meant to answer, before pushing back on the number itself.
2. Make the risk visible, don't just argue it. A concrete demonstration on real data, showing how the metric can point in different directions depending on an arbitrary choice, is far more persuasive than a principled objection about methodology.
3. Offer an alternative, not just a no. Publish with a transparent methodology note and caveats, or pair the requested metric with a companion breakdown of what's actually driving it, so the executive still gets a clear headline but nothing is hidden.
4. Protect the decision with process. Document the metric's definition and the reasoning behind it in the dashboard's own metadata or governance log, so this doesn't quietly become an unreviewed exception the next time someone asks for a similar shortcut.

When to comply, caveat, or escalate

Worked example

A VP asks for a single blended "engagement score" on the executive dashboard, aggregating several disparate signals with no stated weighting logic. If shipped as requested, a week-to-week swing in the score could easily be an artifact of how the components happen to be weighted rather than a real change in the business, and a decision made off that swing (say, reallocating budget away from a channel) would trace back to an arbitrary choice nobody examined.

Rather than arguing methodology in principle, the analyst pulls two plausible weighting schemes and applies both to the same period of real data. The two lines diverge noticeably, showing the VP directly that the "score" would tell two different stories depending on a choice nobody had actually made deliberately. The analyst then proposes shipping the metric with an explicit methodology note and a companion view of the underlying components, so the VP still gets a single number to lead with, but anyone drilling in can see what's actually driving it.

The VP is more persuaded by seeing the two divergent lines side by side than by any abstract argument about aggregation risk, and agrees to ship with the documented methodology and the companion breakdown attached.

What a senior person does differently here: leads with a demonstration on real data rather than a principled objection, and turns a "no" into a documented "yes, defined this way," which the executive can accept without it reading as a refusal.

Trade-offs and pitfalls

• Refusing outright with no alternative reads as obstruction, not integrity, and burns the relationship for no gained clarity.
• Complying silently, without raising the concern or documenting it anywhere, creates real exposure later if the metric ends up driving a bad call; there's no record that the risk was ever flagged.
• Offering caveats only works if they're actually visible where the number is used (in the dashboard itself, not buried in a separate document nobody opens).

Customer Acquisition Cost (CAC)
Definition: Average cost to acquire a new rider (or driver) over a period.
Standard formula:
CAC = Total Acquisition Spend / Number of New Customers Acquired
Where Total Acquisition Spend includes paid marketing, referral bonuses, creative/agency costs, and measurable channel-specific costs in the same period. Number of New Customers = users with first trip (or first app install + activation rule) in that period.

Customer Lifetime Value (LTV)
Definition: Present-value (or simple) expected gross contribution from a customer over a chosen horizon.
Simple cohort LTV = Sum over cohort of (Net Revenue per Customer) over T months
Net Revenue per Customer = Gross Fare + Fees − Driver Payouts − Platform Costs − Refunds − Direct Promotions (if recorded as discount) per customer.

Data sources & key table fields

• users table: user_id, signup_date, acquisition_channel, first_touch, campaign_id
• trips/payments table: trip_id, user_id, trip_date, fare_gross, platform_fee, driver_payout, tax, promo_code_id
• marketing_spend table: date, channel, campaign_id, spend, media_type
• referrals table: referrer_id, referee_id, bonus_amount, bonus_type, posted_date
• refunds/adjustments table: transaction_id, user_id, amount, reason, date
• promos table: promo_id, promo_type, face_value, applied_amount, accounted_as_marketing_boolean

How to treat refunds, promotions, and multi-channel acquisition

• Refunds: Treat as negative revenue in the trips/payments stream and attribute them to the original trip/customer date. For LTV, subtract refunds from gross revenue; for CAC, do not include refunds in acquisition spend.
• Promotions:
  • If promo is a marketing acquisition incentive (e.g., first-ride credit tied to acquisition campaign), count its cost in Total Acquisition Spend (CAC) and also reflect reduced first-trip revenue for LTV (or treat promo as both spend and discount but avoid double-counting).
  • If promo is retention/engagement (e.g., loyalty credits), treat as cost to service (subtract from revenue in LTV) but not in CAC.
  • Store promo metadata to categorize promo_type and accounting treatment.
• Multi-channel acquisition:
  • Choose an attribution model consistent with business needs: first-touch (simpler, often used for CAC), last-touch, or multi-touch weighted (e.g., time-decay).
  • Ensure marketing_spend and user acquisition mapping are joined by campaign_id / click/impression logs. For multi-touch, allocate fractional acquisition spend across channels per user journey and use that for channel-level CAC.
    Other considerations
• Define cohort window and horizon (30/90/365 days) and discount rates if using present value.
• Exclude internal transfers and bots; dedupe users (e.g., multiple devices).
• Report both aggregated and channel-level CAC and cohort LTVs for decision-making.

For a strictly multiplicative funnel, the surprising and important result is that a fixed percentage increase in the final output requires the exact same relative lift at every step, so 'which step needs the smallest relative uplift' has a trick answer: they're identical, and the real decision criterion has to be feasibility, not the arithmetic.

Worked example

Given funnel counts: product views = 1,000,000, add-to-cart = 200,000, checkout starts = 80,000, purchases = 40,000, and a target of a 25% increase in purchases without increasing acquisition (views held fixed).

The funnel is a chain of conversion rates:
$\text{views} \to \text{cart}: r_1 = \frac{200{,}000}{1{,}000{,}000} = 0.20$
$\text{cart} \to \text{checkout}: r_2 = \frac{80{,}000}{200{,}000} = 0.40$
$\text{checkout} \to \text{purchase}: r_3 = \frac{40{,}000}{80{,}000} = 0.50$
$\text{Purchases} = \text{Views} \times r_1 \times r_2 \times r_3 = 1{,}000{,}000 \times 0.20 \times 0.40 \times 0.50 = 40{,}000$

Target purchases = $40{,}000 \times 1.25 = 50{,}000$. Solving for the new rate needed if you move ONLY $r_1$ (holding $r_2, r_3$ fixed):
$r_1' = \frac{50{,}000}{\text{Views} \times r_2 \times r_3} = \frac{50{,}000}{1{,}000{,}000 \times 0.40 \times 0.50} = 0.25$
Relative increase $= (0.25 - 0.20)/0.20 = 25%$. Repeating the same algebra for $r_2$ (new rate 0.50, relative increase 25%) and $r_3$ (new rate 0.625, relative increase 25%) gives the identical 25% relative increase in every case. This is not a coincidence: because Purchases is a pure product of independent factors, moving any single factor by a relative fraction $x$ moves the output by exactly $x$ as well.

Which step to actually target, and why

Since the required relative lift is mathematically tied, the real selection criterion is which step is CHEAPEST to move by 25% given the mechanics of that stage: a checkout-to-purchase step (0.50 to 0.625) is often the easiest to move with UX fixes (removing friction, adding trust signals, one-click checkout) because it's closer to a completed decision; a views-to-cart step (0.20 to 0.25) usually requires improving product-market fit or targeting, which is slower and less certain. Historical elasticity data (which step has moved fastest in past experiments) should break the tie, not the arithmetic.

Trade-offs and pitfalls

A candidate who jumps straight to 'target the step with the lowest current conversion rate' is making an unjustified leap; low absolute conversion doesn't imply low required relative lift or low cost to move it. Always do the algebra first, then reason about feasibility.

Direct answer

Match records between the two systems on a natural key, then classify every unmatched or disagreeing pair with an explicit set of CASE/WHEN rules, in a fixed priority order: same amount but different period is a timing difference, different currency but equal after FX conversion is a currency difference, a small residual is rounding, and anything left over is a true mismatch. A single "total discrepancy" number collapses all of these into one figure that tells an investigator nothing about where to look first.

Structured elaboration

1. Key alignment: pick (or build) a natural key both systems share, such as invoice_id. If the two systems do not share a clean key (see fuzzy-matching below), this whole approach has to start from a matching step instead.
2. Dedup / uniqueness guard: before joining, aggregate each side down to one row per key (GROUP BY invoice_id, account_id, period, currency, amount, carrying a COUNT(*) AS posting_count) and treat posting_count > 1 as a signal to investigate, not something to pass through. A FULL OUTER JOIN enforces no uniqueness on its join key: a retried or duplicate GL posting for the same invoice_id fans out against billing's single row and produces two exact_match rows for one real transaction, silently double-counting it in any downstream sum. This is a distinct failure mode from the late-adjustment case in point 4 below (a genuine duplicate of the same transaction, not a correcting entry). See the adversarial check after the worked example, which reproduces this and shows the guard fixing it.
3. Full outer join: join billing to the general ledger on that key with a FULL OUTER JOIN, so rows present on only one side surface as their own bucket (missing_in_gl / missing_in_billing) rather than being silently dropped by an inner join.
4. Ordered classification: apply the bucket rules from most to least specific: exact match first, then timing (equal amount, different period), then currency/FX (different currency, equal after conversion), then rounding (tiny residual), then true mismatch as the catch-all. Order matters the same way it does in a CASE expression: a rule checked too late never fires because an earlier, looser rule already claimed the row.
5. Alternate reconciliation angles worth naming explicitly, beyond the four core buckets:
   • Fuzzy matching: when the two systems do not share a clean natural key (different invoice numbering, or one side batches multiple billing lines into a single GL posting), exact-key joins miss real matches entirely. That case needs a similarity-based match first (amount plus date window plus account, or a normalized reference string), producing a matched-pair candidate set before the same bucket logic can run on top of it.
   • Hierarchy mapping: billing and GL frequently model the account structure at different granularity, billing keyed at the customer/subsidiary level, GL rolled up to a parent cost center. Reconciling at the wrong level either double-counts or drops legitimate entries; the join key needs to go through an account-hierarchy mapping table before comparison, not assume a 1:1 account match.
   • Idempotency-breaking late adjustments (idempotency: re-running the same reconciliation on the same data should always produce the same result): a correcting entry posted after the original transaction (a credit memo, a late GL adjustment) shows up as an unmatched extra row and gets naively bucketed as missing_in_billing or a true_mismatch, when it is really resolving an earlier discrepancy, not creating a new one. The worked example below reproduces exactly this case.

Worked example (executed, sqlite3, FULL OUTER JOIN)

CREATE TABLE billing (invoice_id TEXT, account_id TEXT, period TEXT, currency TEXT, amount NUMERIC);
INSERT INTO billing VALUES
  ('INV001', 'A1', '2025-01', 'USD', 1000.00),
  ('INV002', 'A1', '2025-01', 'USD', 500.00),
  ('INV003', 'A2', '2025-01', 'USD', 300.00),
  ('INV004', 'A2', '2025-01', 'USD', 800.00),
  ('INV005', 'A3', '2025-01', 'USD', 150.00),
  ('INV007', 'A4', '2025-01', 'EUR', 100.00);

CREATE TABLE gl (invoice_id TEXT, account_id TEXT, period TEXT, currency TEXT, amount NUMERIC);
INSERT INTO gl VALUES
  ('INV001', 'A1', '2025-01', 'USD', 1000.00),
  ('INV002', 'A1', '2025-02', 'USD', 500.00),
  ('INV003', 'A2', '2025-01', 'USD', 299.99),
  ('INV004', 'A2', '2025-01', 'USD', 750.00),
  ('INV006', 'A3', '2025-01', 'USD', 90.00),
  ('INV007', 'A4', '2025-01', 'USD', 109.00),
  ('INV004-ADJ', 'A2', '2025-02', 'USD', 50.00);  -- a LATE correcting adjustment for INV004, GL-only

CREATE TABLE fx_rates (currency TEXT, period TEXT, rate_to_usd NUMERIC);
INSERT INTO fx_rates VALUES ('EUR', '2025-01', 1.09);

WITH matched AS (
  SELECT COALESCE(b.invoice_id, g.invoice_id) AS invoice_id,
    b.period AS billing_period, g.period AS gl_period,
    b.currency AS billing_currency, g.currency AS gl_currency,
    b.amount AS billing_amount, g.amount AS gl_amount, fx.rate_to_usd
  FROM billing b
  FULL OUTER JOIN gl g ON b.invoice_id = g.invoice_id
  LEFT JOIN fx_rates fx ON fx.currency = b.currency AND fx.period = b.period
)
SELECT invoice_id, billing_amount, gl_amount,
  CASE
    WHEN billing_amount IS NULL THEN 'missing_in_billing'
    WHEN gl_amount IS NULL THEN 'missing_in_gl'
    WHEN billing_currency = gl_currency AND ROUND(billing_amount,2) = ROUND(gl_amount,2)
         AND billing_period = gl_period THEN 'exact_match'
    WHEN billing_currency = gl_currency AND ROUND(billing_amount,2) = ROUND(gl_amount,2)
         AND billing_period <> gl_period THEN 'timing_difference'
    WHEN billing_currency <> gl_currency AND rate_to_usd IS NOT NULL
         AND ABS(ROUND(billing_amount * rate_to_usd, 2) - gl_amount) <= 0.02 THEN 'currency_fx'
    WHEN billing_currency = gl_currency AND ABS(billing_amount - gl_amount) <= 0.02
         AND ABS(billing_amount - gl_amount) > 0 THEN 'rounding'
    ELSE 'true_mismatch'
  END AS bucket
FROM matched
ORDER BY invoice_id;

Result (8 rows):

Bucket summary: exact_match=1, timing_difference=1, rounding=1, true_mismatch=1, missing_in_gl=1, missing_in_billing=2 (INV006, INV004-ADJ).

This is the idempotency-breaking late adjustment case, reproduced directly: INV004-ADJ gets bucketed identically to INV006 (both missing_in_billing), but they mean opposite things. INV006 is a genuine unexplained entry with no billing counterpart. INV004-ADJ is a $50.00 correcting entry, and $750.00 (the original GL amount for INV004) plus $50.00 (the adjustment) equals exactly $800.00, the billing amount for INV004. The naive bucket rules alone would report two open discrepancies (INV004 as true_mismatch, INV004-ADJ as missing_in_billing) when the underlying reality is one resolved discrepancy. A production version of this classifier needs an adjustment-linking step (match adjustment rows back to the invoice_id they correct, typically via a reference field, and net them against the original before classifying) so late corrections do not get double-counted as new problems.

Adversarial check (executed, sqlite3): duplicate GL postings double-count without the guard

The dedup guard in point 2 above is not decorative. Seed a single billing row and a GL side that received the same posting twice (a retried write, same invoice_id, same amount, same period), then run the classification query without the guard:

CREATE TABLE billing (invoice_id TEXT, account_id TEXT, period TEXT, currency TEXT, amount NUMERIC);
INSERT INTO billing VALUES ('INV001', 'A1', '2025-01', 'USD', 1000.00);

CREATE TABLE gl (invoice_id TEXT, account_id TEXT, period TEXT, currency TEXT, amount NUMERIC);
INSERT INTO gl VALUES
  ('INV001', 'A1', '2025-01', 'USD', 1000.00),
  ('INV001', 'A1', '2025-01', 'USD', 1000.00);  -- duplicate/retried posting, GL-only

SELECT COALESCE(b.invoice_id, g.invoice_id) AS invoice_id,
  b.amount AS billing_amount, g.amount AS gl_amount,
  CASE
    WHEN b.amount IS NULL THEN 'missing_in_billing'
    WHEN g.amount IS NULL THEN 'missing_in_gl'
    WHEN ROUND(b.amount,2) = ROUND(g.amount,2) AND b.period = g.period THEN 'exact_match'
    ELSE 'true_mismatch'
  END AS bucket
FROM billing b
FULL OUTER JOIN gl g ON b.invoice_id = g.invoice_id
ORDER BY invoice_id;

Result (naive, no guard):

One real transaction, reported as two reconciled matches: a downstream SUM(gl_amount) over exact_match rows would come out $1000 too high. Now apply the guard from point 2, pre-aggregating each side to one row per key before the same join and classification logic:

WITH gl_dedup AS (
  SELECT invoice_id, account_id, period, currency, amount, COUNT(*) AS posting_count
  FROM gl
  GROUP BY invoice_id, account_id, period, currency, amount
),
billing_dedup AS (
  SELECT invoice_id, account_id, period, currency, amount, COUNT(*) AS posting_count
  FROM billing
  GROUP BY invoice_id, account_id, period, currency, amount
)
SELECT COALESCE(b.invoice_id, g.invoice_id) AS invoice_id,
  b.amount AS billing_amount, g.amount AS gl_amount, g.posting_count AS gl_posting_count,
  CASE
    WHEN b.amount IS NULL THEN 'missing_in_billing'
    WHEN g.amount IS NULL THEN 'missing_in_gl'
    WHEN ROUND(b.amount,2) = ROUND(g.amount,2) AND b.period = g.period THEN 'exact_match'
    ELSE 'true_mismatch'
  END AS bucket
FROM billing_dedup b
FULL OUTER JOIN gl_dedup g ON b.invoice_id = g.invoice_id
ORDER BY invoice_id;

Result (guarded):

One row, correctly classified, with gl_posting_count = 2 surfaced as a flag: a real system would route rows where posting_count > 1 to a "duplicate posting" review queue instead of silently collapsing them, since a duplicate could also indicate a genuine double-billing error rather than a harmless retry.

Trade-offs and pitfalls

• The bucket order in the CASE expression is a real correctness dependency, not stylistic: check exact_match before rounding (an exact match is a rounding difference of exactly zero and should not be relabeled), and check timing_difference and currency_fx before falling through to true_mismatch, or a row that is genuinely just late gets filed as an error and routed to the wrong queue.
• Rounding and currency tolerances (0.02 here) need to be a deliberate, documented threshold, not a guess; too tight and rounding noise floods the true_mismatch bucket, too loose and it starts absorbing real discrepancies.
• FULL OUTER JOIN is not supported everywhere: MySQL has no native FULL OUTER JOIN at any version (still an unshipped feature per MySQL's own worklog WL#1604) and needs a UNION of a LEFT JOIN and a RIGHT JOIN ... WHERE left.key IS NULL to emulate it, on every MySQL release up to and including the current 8.4/9.x lines.
• Automating this: schedule the classification query to run after each close, persist bucket + root_cause + a link to the source rows for auditability, and route only true_mismatch rows to a human investigator, since the other buckets are self-explanatory once the classification is trusted.