Consultative Discovery and Requirements Gathering Questions

Situation: When performing discovery analysis as a data analyst, I document everything so others can reproduce results and verify decisions later.

Checklist of fields to include:

• Title and objective: one-line purpose and business question
• Author, date, and contact
• Data sources: database/schema/table names, file paths, API endpoints
• Query versions: full SQL (or code) with version tag, commit hash or saved query ID
• Extraction time & timezone: timestamp when data was pulled
• Snapshot ID: pointer to dataset snapshot (filename, S3 URI, DB table copy)
• Assumptions: explicit business rules, filters, joins, imputation, sample selections
• Transformations: step-by-step (or pipeline DAG) with code snippets and rationale
• Metrics definitions: exact formulas and edge-case rules
• Validation checks & results: row counts, checksums, null rates, key distributions
• Decisions & rationale: forks considered, chosen approach, trade-offs, stakeholder sign-off
• Reproduction steps: commands/queries to re-run, env, dependencies, expected outputs
• Risks & open questions: known limitations and follow-ups
• Revision history: who changed what and why

Tools & formats:

• Notebooks (Jupyter/Colab): exploratory code + inline narrative; include cells to load snapshot and re-run queries
• Versioned SQL repo or saved-query system (Git + dbt or query tool): track query versions and commit messages
• Dataset snapshots: parquet/csv with immutable names or date-partitioned tables in S3/warehouse; store checksums
• README or discovery doc (Markdown): high-level summary, checklist fields filled
• Data catalog / lineage tool (e.g., Amundsen, DataHub) to register datasets and lineage
• BI artifacts: attach workbook versions (Tableau/Power BI) and dashboard spec
• Automated tests: include unit tests or data-quality assertions (Great Expectations, dbt tests)

Example reproducible flow:

1. Commit SQL to Git with issue number and tag.
2. Save extraction as snapshot s3://bucket/project/date/hash.parquet and record checksum.
3. Update README with assumptions + reproduction commands.
4. Run notebook that reads snapshot, executes transforms, and regenerates charts; export as HTML for stakeholders.

This combination ensures future reviewers can find the exact inputs, understand choices, and re-run analysis end-to-end.

A useful clarifying question: "Do you want timestamps normalized to a single reference timezone (e.g., UTC or company HQ) for all users, or should the dashboard display times in each viewer’s local timezone—and should business-day boundaries follow the viewer’s local business hours or the company’s standard day?"

Why this matters and how it changes implementation/visual presentation:

• Aggregation windows: If you normalize to UTC/HQ, daily/weekly aggregates use consistent cutoffs; if you use viewer-local, the ETL/aggregation must be computed per timezone (or converted on-the-fly), which affects SQL grouping and performance.
• Labeling & trust: Showing local times requires clear timezone labels and tooltips (e.g., "2025-11-22 09:00 PST"); without labels users may misinterpret spikes that cross midnight in another zone.
• UX: For global teams, add a timezone toggle or detect browser locale and indicate which timezone is used. For cross-timezone comparisons, include an overlay or small-multiple views aligned to the same UTC axis.
• Implementation: Best practice—store raw timestamps in UTC, perform aggregations in the canonical business timezone for internal KPIs, and convert to local display in the dashboard layer. This balances consistency, performance, and clarity.

Clarifying questions (targeted)

• What exact latency do you need? (e.g., sub-second, <1m, 5–15m, hourly)
• Is partial/incremental data acceptable or must datasets be complete and final?
• Which metrics/events must be real-time vs. can remain nightly?
• Regional constraints: data residency, cross-region aggregation timing?
• Expected query concurrency and dashboard users (scale)?
• Cost constraints or budgetary guardrails (e.g., acceptable monthly ops spend)?
• Regulatory/accuracy requirements (idempotency, exactly-once)?
• Can we tolerate eventual consistency (timestamps + flags) for trend analysis?

Phased plan to move Nightly → Near‑Real‑Time (minutes)
Phase 0 — Discovery & Prioritization (2–3 wks)

• Inventory sources, events, metrics; classify by realtime need.
• Acceptance: prioritized list + sample volumes; stakeholder sign-off.

Phase 1 — Incremental CDC for high-value metrics (4–6 wks)

• Implement change data capture (Debezium/Kafka Connect) or streaming connectors for key tables; stream into a regional message bus (Kafka/Kinesis).
• Build lightweight materialized views / aggregation service (e.g., ksqlDB, Spark Structured Streaming) to produce minute-level aggregates.
• Acceptance: dashboards refresh every 1–5 min for prioritized metrics; accuracy within agreed delta vs. nightly (e.g., <1% or <N records).

Phase 2 — Regional Aggregation & Cross-region Rollup (4–8 wks)

• Deploy regional stream processors; asynchronously replicate summaries to central OLAP (ClickHouse, BigQuery streaming) for global views.
• Add watermarking and late-arrival handling policies.
• Acceptance: cross-region rollups available within S minutes; late-record reconciliation within T hours with audit logs.

Phase 3 — Full Coverage, Monitoring, Cost Controls (ongoing)

• Extend CDC to remaining sources, implement retention/cold storage, cost alerts, and SLO/SLIs (latency, completeness).
• Implement rollback/reconciliation jobs to compare nightly vs streaming outputs.
• Acceptance: SLOs met (e.g., 95th percentile latency <X mins, completeness >Y%), budget under threshold, automated alerts.

Key trade-offs and notes

• Cost vs freshness: streaming increases compute/storage—start with top metrics to control cost.
• Consistency: streaming favors eventual consistency; use event timestamps and reconciliation jobs for analytics-grade accuracy.
• Operational overhead: add monitoring (Prometheus/Grafana), retry/DLQ for failed events.

As a data analyst I’d lead the prioritization, define accuracy SLAs with stakeholders, validate streaming outputs against nightly ETL, and iterate with engineering for instrumentation and cost tracking.

Clarifying questions

• Which specific "conversion" metric do you mean (purchase rate, sign-up, trial->paid, funnel step)?
• Which segment/s (all users, new vs returning, geo, channel, device, cohort)?
• Exact baseline: which week is "last week" and what comparison period do you want (previous week, same week last month, 4-week average, seasonal baseline)?
• Is this overall site/app or a particular flow/page/campaign?
• Any product changes, experiments, releases, or marketing campaigns deployed in that window?
• Are there known data quality issues, tracking changes, or outages?
• How fast do you need results and what format (slide deck, dashboard, SQL + notebook)?

Stakeholders to involve

• PM (requester) for business context and priority
• Product/Engineering for releases and telemetry changes
• Marketing for campaign timing and spend shifts
• Analytics/Tracking owner for event schema or instrumentation changes
• Customer support/ops for incident reports

Data sources

• Event/BE logs (clicks, conversions), data warehouse (user, orders tables)
• A/B test platform, release/deploy calendar, marketing analytics (ad platforms), CDN/infra monitoring, error logs, customer complaints

Metric definitions

• Precise numerator and denominator (e.g., conversions = purchases post-checkout; rate = purchases / sessions or unique users)
• Attribution rules (last-click? session-based?)
• Filtering rules (bots, internal traffic)

Time windows & granularity

• Primary window: last week (Mon–Sun) vs previous week and vs 4-week rolling mean
• Daily/hourly granularity for anomaly detection; cohort lookback for retention effects

Acceptable assumptions

• If instrumentation gaps exist, state assumptions (e.g., assume no major tracking loss if infra logs show uptime >99.9%)
• Use sessions as denominator if user IDs unknown, but note bias

What success looks like

• Clear, reproducible explanation for the drop: root cause identified (e.g., campaign paused, checkout error, tracking bug, traffic mix shift) with evidence and metrics
• Actionable recommendations and next steps (rollback, fix, deeper analysis)
• Deliverable: short executive summary + supporting dashboard/queries and reproducible notebook/SQL within agreed SLA.

Clarifying questions

• Business: pricing, billing cadence (monthly/annual), trial/free period, gross vs net revenue, discounting policy.
• Product: expected typical lifetime, planned changes (features/price) that could affect retention.
• Data: cohort definitions, churn events tracked, censoring rules, sample sizes per cohort, user segmentation (acquisition channel, geography), any early A/B tests.
• Objective & horizon: planning horizon (3yr, 5yr), acceptable uncertainty, required granularity (overall vs segment-level).

Key assumptions to document

• Cohort stability: future cohorts behave like existing ones (or specific drift assumptions).
• Stationarity of churn after X months, or functional form assumed.
• Revenue per user (ARPU) is constant or follows specified growth/decay.
• Discount rate for NPV; treatment of refunds/returns.
• Censoring is non-informative unless evidence otherwise.

Modeling strategies

• Descriptive baseline: Kaplan–Meier survival curves for the 4-month window by cohort/channel to visualize censoring.
• Parametric survival models: fit Weibull/Exponential/Log-logistic to early hazard patterns; Weibull is flexible (hazard ~ t^{k-1}) and can extrapolate long tail.
• Semi-parametric: Cox model with covariates if proportional hazards plausible; combine with baseline parametric tail for long-term extrapolation.
• Bayesian approach: incorporate priors on long-term churn (e.g., from similar products or industry benchmarks). Bayesian survival gives posterior predictive LTV distributions and handles small-sample uncertainty.
• Simple extrapolation: negative-exponential or geometric decay as quick sanity check; only for conservative planning.

How to compute confidence intervals

• For frequentist parametric fits: use bootstrap (resample users/cohorts) to propagate parameter uncertainty into LTV distribution and derive percentile CIs.
• For Bayesian models: use posterior samples to compute credible intervals directly.
• Always include model uncertainty: compare CIs across multiple model families and report model-averaged CI (e.g., Bayesian model averaging or ensemble weights based on WAIC/LOO).

Acceptance criteria for usefulness

• Decision-driven thresholds: e.g., estimate narrow enough that NPV decisions (go/no-go) are robust — show sensitivity: if downstream decision flips under plausible upper/lower bound, more data required.
• Minimum signal: effective sample size and event counts sufficient (rule of thumb: at least ~50–100 churn events per segment) or aggregate to higher level.
• Plausibility checks: model predicts held-out short-term cohorts accurately and aligns with external benchmarks.
• Actionability: produce recommended ranges (optimistic/base/pessimistic) with clear assumptions and recommended triggers to revise estimates (e.g., every additional month of data or 1000 new subscriptions).

Deliverable

• Provide KM plot, fitted parametric curves, Bayesian posterior LTV distribution, bootstrap CIs, sensitivity table for discount rates/pricing, and a one-page assumption & action log to guide product planning.