Business Problem Structuring and Case Frameworks Questions

Requirements & goal: increase actionable liquidity (matched transactions per active user) by identifying bottlenecks across both sides and prioritizing experiments with measurable metrics. Focus on funnels, cross-side leading indicators, hypothesis-driven tests and BI dashboarding.

Core funnel stages

• Demand (Buyers): Awareness → Activation (listing/view or signup → first search) → Intent (add to cart/booking intent) → Match (found relevant supplier) → Conversion (completed transaction) → Retention/Repeat.
• Supply (Sellers/providers): Awareness → Activation (signup → profile/listing created) → Availability (time slots/prices set) → Discoverability (views/requests) → Engagement (acceptance rate/respond time) → Transaction → Retention/Re-listing.

Cross‑side metrics to monitor (BI dashboard)

• Liquidity ratio: matches / active buyers; matches / active sellers
• Match rate: % demand queries with ≥1 relevant supply within X km / category
• Time-to-match: median time from intent → matched supplier
• Fill rate: accepted bookings / requests
• Supply depth per demand unit: average suppliers per search
• Price spread, conversion funnel dropoffs by cohort, geography, time of day
• Lead/lag metrics: new listings per week vs transactions per week

Hypotheses for low liquidity

1. Supply scarcity: not enough active sellers in specific categories/locations.
2. Poor discoverability: listings exist but not surfaced (search ranking, filters).
3. Mismatch on expectations: pricing, service quality or minimum order size misaligned.
4. Response friction: long response time or low acceptance rates.
5. Demand seasonality or onboarding drop-off (buyers churn before intent).
6. Measurement/data issues: duplicates, stale listings inflate supply counts.

Priority experiments & analyses (BI-driven)

• Analyze supply-demand heatmap by category/geo/time (SQL cohort + visualization) to surface cold spots.
• Cohort retention and time-to-first-transaction for sellers; identify onboarding drop-off steps.
• A/B test search ranking tweaks: boost nearby/high-rated suppliers; track match rate and conversion lift.
• Incentive experiment: targeted subsidies to suppliers in low-depth areas; measure supply activation and fill rate.
• Messaging/UX test to reduce buyer funnel friction (pre-filled forms, estimated times); track intent→match conversion.
• Build alerting: real‑time KPI anomalies (match rate, time-to-match) and root-cause drilldowns (query -> supplier set).
• Simulate supply requirements model: compute expected sellers needed to reach target conversion given historical match/acceptance rates.

BI deliverables

• Executive liquidity dashboard (high-level KPIs, trends, top pain geos/categories)
• Funnel explorer allowing segmentation (cohort, geo, channel, time)
• Root-cause drilldowns (listings by freshness, acceptance rates, search exposures)
• Experiment tracker with KPI lift, p-values, and suggested next steps

Prioritization rule: prioritize high-impact, low-effort fixes that increase local supply depth or improve discoverability (e.g., re-ranking + targeted incentives), instrument measurables, then scale.

Objective & scope: determine whether our pricing is competitive across segments, quantify gaps, estimate demand sensitivity where possible, and deliver an actionable recommendation framework (immediate price actions, tests, and monitoring).

1. Clarify requirements & KPIs

• Stakeholders: Revenue/Finance, Product, Sales, Marketing.
• KPIs: price gap (ours vs. median/lowest competitor), share of clicks/units at each price point, margin impact, estimated revenue change, conversion uplift from price moves.

2. Data sources

• Internal: product catalog (SKUs, attributes), sales ledger (price, units, revenue, discounts, channel, date), customer segmentation, returns.
• External: scraped competitor prices (timestamped), product descriptions/images, promotion flags, marketplace reviews/stockouts.
• Enrich: category taxonomy, exchange rates, shipping/tax rules.

3. Normalize product mappings

• Build a product-resolution pipeline: standardize attributes (brand, model, size, color), fingerprint text (TF-IDF + embedding), image similarity (hashing/ML) where available, and use a blocking + fuzzy-match algorithm to link competitor SKUs to our SKUs with match scores. Keep human-in-the-loop review for low-confidence matches and log mapping confidence.

4. Handle sparse/irregular competitor data

• Aggregate to time windows (daily/weekly) and compute observed price distributions and last-seen price.
• For missing periods: impute using hierarchical smoothing (category-average trend + competitor-specific random effects) or carry-forward with uncertainty bounds; flag high-uncertainty items.
• Use survival/availability models to detect stockouts vs. scraping gaps.
• Weight competitor signals by freshness and match confidence.

5. Estimate price elasticity

• Prefer causal approaches: exploit natural experiments—promotions, temporary competitor price changes, our historical price changes, geo-price differences.
• Model: panel fixed-effects regression at SKU-segment-week level: log(q) = α + βlog(price) + γcontrols (promotions, seasonality, marketing spend, availability) + SKU and time FE. β is elasticity.
• If endogeneity (price correlated with demand shocks), use instruments (cost shocks, supplier price changes, competitor price shifts) or difference-in-differences around exogenous price changes. Where possible run randomized price A/B tests for high-impact SKUs.

6. Recommendation framework

• Compute for each SKU-segment: competitive gap, mapped competitor percentile, margin sensitivity (contribution margin), estimated elasticity and revenue-maximizing price range with confidence intervals.
• Priority tiers:
  • Tier 1 (High impact, low uncertainty): actionable automated adjustments (raise/lower price within guardrails) + A/B test for larger changes.
  • Tier 2 (High impact, high uncertainty): run experiments or targeted promotions.
  • Tier 3 (Low impact): monitor and re-evaluate.
• Create a decision score = f(revenue_at_risk, margin_delta, competitor_gap, elasticity_confidence). Use thresholds to recommend: "match lowest within X%", "maintain premium at Y% with added value", or "test -10% for 2 weeks".

7. Dashboards & monitoring

• Executive dashboard: competitive heatmap by category/segment, top opportunities, confidence bands, experiment results.
• Operational dashboard: mapping confidence, scrape freshness, automated alerts for large competitor moves or stockouts.
• Automation: ETL for nightly scrape ingestion, weekly elasticity re-estimates, and rules engine to push recommended price tags to pricing ops with audit logs.

8. Risks & governance

• Monitor margin erosion, channel conflicts, legal/compliance for price scraping/use.
• Human review for brand-sensitive SKUs.
• Iterate: run experiments, recalibrate models, and embed learnings into the decision score.

Outcome: deliver a prioritized list of recommended price moves with expected revenue/margin impact and confidence, plus dashboards and an experimentation plan to validate and scale changes.

Primary KPI — Weekly Active Users (WAU) with Engagement Depth

• Calculation: Numerator = count of distinct users who performed ≥1 meaningful action (e.g., open + click, post, comment, play >30s) during the week. Denominator = total invited/registered users or MAU baseline depending on use (choose registered users for retention focus). Time window = rolling 7-day window.
• Why: WAU measures how many users return and perform product actions; adding an “engagement depth” threshold filters out passive opens and reflects active use.
• Pitfall: Changes in definition of “meaningful action” (e.g., adding/removing events) will create artificial jumps; needs stable event schema and instrumentation.

Secondary KPI — Avg. Sessions per Engaged User per Week (ASU)

• Calculation: Numerator = total sessions during the week by users meeting engagement threshold; Denominator = number of those engaged users. Time window = weekly rolling.
• Why: Captures frequency of return/use per engaged user — helps identify stickiness beyond unique counts.
• Pitfall: Session definition inconsistencies (timeout rules, background app behavior) can skew numbers; bots or automated traffic can inflate sessions if not filtered.

Notes: Track both KPIs together on dashboards, annotate instrumentation or product changes, and segment by cohort/channel to avoid misleading aggregate trends.

Top-level goal: improve checkout conversion rate. MECE issue tree (top-level branches are mutually exclusive and collectively exhaustive):

1. Traffic quality & intent

• Diagnostic metrics: conversion rate by source/medium; new vs returning conversion
• Experiment: Run targeted paid search audience segmentation (high-intent vs broad) and A/B compare landing pages tailored to intent; measure lift in add-to-cart and checkout conversion.

2. Product discovery & merchandising

• Diagnostic metrics: product detail view → add-to-cart rate; product list click-through rate (CTR)
• Experiment: A/B test product sorting (personalized-relevance vs best-sellers) on category pages; track changes in add-to-cart and revenue per session.

3. Add-to-cart friction

• Diagnostic metrics: add-to-cart rate by device; % sessions with item added
• Experiment: Remove/condense optional upsell modals on product pages for test group; measure add-to-cart and cart size.

4. Cart experience & abandonment

• Diagnostic metrics: cart abandonment rate; cart abandonment by step (view cart → proceed to checkout)
• Experiment: Add persistent mini-cart + progress CTA vs control; measure conversion to checkout and abandonment drop.

5. Checkout flow & form friction

• Diagnostic metrics: per-step drop-off in checkout funnel; time-to-complete checkout
• Experiment: Test single-page checkout vs multi-step for a randomized cohort; measure completion rate and time.

6. Payments & trust

• Diagnostic metrics: payment failure rate by method; drop-off on payment step
• Experiment: Add/announce additional payment options (e.g., BNPL, Apple Pay) and trust badges; run A/B and measure payment completion and conversion.

7. Pricing, shipping & returns

• Diagnostic metrics: checkout exits after shipping cost reveal; conversion by shipping option displayed
• Experiment: A/B test free-shipping threshold messaging (show vs hide, different thresholds) and offer limited-time free shipping; measure conversion lift and AOV.

8. Technical performance & reliability

• Diagnostic metrics: page load time vs conversion; error rate during checkout
• Experiment: Optimize critical JS/CSS to reduce checkout page load time for test traffic; measure conversion and error reductions.

As a BI analyst, implement these by building funnel dashboards (segmented by device/source/product), anomaly alerts on drop-offs, and tracking experiment cohorts to attribute causality.

Situation: Executive expects a crisp, evidence-backed diagnostic in 60 minutes explaining an 8% month-over-month revenue drop. Below is a prioritized minute-by-minute investigation, quick SQL checks, stakeholders to contact, likely hypotheses, and deliverables.

0–5 min: Clarify scope

• Confirm revenue definition (booked vs recognized), time windows (compare M-1 vs M-2), geography/products included, and acceptable confidence level.

5–30 min: Data health & high-level triage (run queries + checks)

• Data completeness: rows/tables loaded this month vs prior

-- row counts by day
SELECT date(order_date) as d, COUNT(*) as orders, SUM(amount) as rev
FROM orders
WHERE order_date BETWEEN '2025-10-01' AND '2025-10-31'
GROUP BY d ORDER BY d;

• Top-line change by dimension (product, region, channel, customer cohort)

-- month vs prior month by product
SELECT product_id, SUM(amount) filter (where month=curr) as rev_curr,
SUM(amount) filter (where month=prev) as rev_prev
FROM revenue_by_day
GROUP BY product_id ORDER BY (rev_curr - rev_prev) DESC LIMIT 20;

• Transaction-level anomalies: % orders canceled/refunded, average order value (AOV), transaction count
• Time-series check: hourly/daily revenue to spot last-week or last-day drops

30–45 min: Drill into highest-impact deltas

• If revenue drop concentrated in specific product(s): check inventory, pricing, promotions
• If concentrated by region/channel: check tracking changes, third-party attribution, partner outages
• If order count down vs AOV down: focus marketing/traffic vs checkout/fulfillment

45–55 min: Quick stakeholder outreach (concurrent with queries)

• Ops/Logistics: shipping delays, inventory outages
• Product/Pricing: recent price changes or promotions ended
• Marketing/Acquisition: campaign spend, tracking pixel/tag changes, paid channel pauses
• Sales/Customer Success: large account churn or contractual changes
• Data Engineering: ETL jobs, schema/deploys, measurement/tagging changes

55–60 min: Create deliverables & recommended next steps
Deliverable(s):

• 1-slide executive summary: headline (8% drop), primary driver(s) with % contribution, confidence level, immediate action(s)
• 2–3 supporting charts: revenue trend, top 5 dimensions by delta, transaction count vs AOV
• Short investigation plan (next 24–72 hrs): deep-dive SQL queries, ownership, and required data (logs, raw events, inventory)

Likely hypotheses to test (priority order):

1. Traffic/acquisition drop (fewer sessions or campaigns paused)
2. Conversion funnel issue (checkout bug, payment provider outages)
3. Data/ETL measurement error (missing events or double-counting fixed prior month)
4. Promo/price changes or inventory stockouts
5. Large-customer churn or delayed enterprise invoicing

Why this approach: start with data integrity, then isolate where the delta lives (volume vs price, product vs region), contact stakeholders who can confirm operational causes, and deliver a concise, action-oriented summary the exec can act on immediately.