Company Research and Business Understanding Questions

Primary user journey (high-level):

• Acquisition: User clicks an ad or referral -> lands on landing page -> signs up (email or SSO).
• Activation/Onboarding: User completes first key action(s) (e.g., completes profile, creates first project/item).
• Engagement: Regular use of core product features (search, create/edit, share).
• Monetization (if applicable): Upgrades to paid plan or completes transaction.
• Retention: Returns over days/weeks; receives notifications, uses saved items.

Three product events/data points to instrument first:

1. user_signup (user_id, timestamp, acquisition_channel, campaign_id, device, region) — captures acquisition source and identity.
2. first_key_action (user_id, timestamp, action_type, metadata) — marks activation (e.g., created first project).
3. session_start (user_id, session_id, timestamp, device, app_version, entry_point) — enables engagement and frequency tracking.

Two derived metrics to compute:

1. Activation rate = count(distinct users with first_key_action within 7 days of signup) / count(distinct user_signups). Helps assess onboarding effectiveness.
2. 7-day retention rate = count(distinct users with >=1 session in days 1–7 after signup) / count(distinct new users). Indicates short-term stickiness.

Implementation notes for a Data Engineer:

• Send events to a unified event pipeline (e.g., Kafka), enforce a schema (Schemas in Avro/Protobuf), validate at ingestion, store raw events in a landing zone and transform into an analytics table (daily user_facts) for metric computation. Add alerts for schema drift and backfill strategy for late-arriving events.

Situation: Joining a mid-stage SaaS analytics company as a data engineer, my first priority is to understand and validate the business model so data systems align with product and revenue needs.

Plan (first 30 days):

1. Stakeholder interviews (week 1–2)

• Product manager(s): product portfolio, roadmap, feature usage goals.
• Head of Sales / Account Execs: ideal customer profile, pricing negotiation, contract terms.
• Customer Success: churn reasons, upsell paths, common requests.
• Finance/Revenue Ops: pricing plans, MRR/ARR definitions, billing events, discounts.
• Analytics/BI consumers and Data Science: current metrics, pain points.
• Engineering/Platform: product telemetry and event instrumentation.

2. Internal data sources to consult

• Product analytics (Amplitude/GA/Heap), event logs, telemetry.
• Billing & subscription system (Stripe/Chargebee/Zuora).
• CRM (Salesforce) and contract metadata.
• Support/ticketing (Zendesk/Intercom).
• Data warehouse schemas, ETL docs, lineage tools.
• Signup/onboarding logs and cohort exports.

3. External sources

• Public pricing pages, competitor features/pricing, market reports, customer reviews.
• Customer interviews / reference calls (via CSM).

4. Validation approach

• Map features → product SKUs → pricing plans → revenue events.
• Reconcile event-derived usage metrics to billing records (e.g., active seats, API calls).
• Define canonical metric definitions and SQL queries; run spot checks and backfill reconciliations for last 6–12 months.

5. Artifacts to produce

• Canonical metrics catalogue (names, definitions, owner, SQL, tags: revenue/product/engagement).
• Product-to-revenue mapping diagram (features → SKU → billing events).
• Stakeholder contact & responsibility matrix.
• Data-source inventory and lineage draft (where each metric comes from).
• Short audit report: gaps, instrumentation issues, immediate fixes and prioritized roadmap for data quality.

Result: These deliverables give the analytics team and stakeholders aligned definitions, identify inconsistency risks between usage and billing, and create a prioritized list of instrumentation and ETL fixes I can implement next (pipelines, tests, monitoring). This approach ensures data infrastructure supports product and revenue decision-making from day one.

Unit economics in a subscription business measure the profitability of acquiring and keeping customers. Key metrics:

• CAC (Customer Acquisition Cost): total marketing + sales spend attributed to acquiring customers / number of new customers.
• LTV (Customer Lifetime Value): expected gross margin from a customer over their lifetime (sum of discounted or simple gross margin from recurring revenue).
• Payback period: time to recoup CAC from contribution margin (monthly gross margin).

Raw datasets & events a data engineer must capture reliably:

• User events: account_signup, trial_start, trial_convert, subscription_start, subscription_cancel, subscription_pause, subscription_resume.
• Revenue/billing: invoice_issued, invoice_paid, refund, discount_applied, plan_change.
• Payments: charge_success, charge_failed, chargeback.
• Marketing & Sales: ad_click, ad_impression, campaign_id, channel, lead_touch, attribution windows, offline lead import, sales_credit.
• Cost data: marketing spend per campaign/channel (daily), sales salaries/commissions (allocations), platform costs (optional).
• Product metadata: product_line, plan_tier, price, cost_of_goods_sold (COGS) or gross margin rate.
• Identity & mapping: user_id ↔ account_id, conversion windows, first_touch.

Simple pipeline & data model (high-level):

1. Ingest raw events into event store (streaming) + billing/finance tables (batch/CDC).
2. Clean & unify identities; materialize an "customer_master" table with customer_id, acquisition_date, channel, campaign, product_line.
3. Build event-derived tables:
   • subscriptions: one row per subscription_period (start, end, plan, revenue, cogs).
   • invoices/payments: invoice_id, customer_id, amount, revenue_date, status.
4. Attribution logic: compute first_touch and/or multi-touch attribution; write customer_acquisition table (customer_id, acquisition_date, channel, campaign, spend_allocated).
5. Aggregations:
   • CAC by cohort/channel/product_line = sum(spend_allocated where acquisition_date in cohort) / count(new_customers in cohort).
   • LTV by cohort/channel/product_line = SUM(revenue - COGS) per customer over N months (use rolling/window functions); optionally discounted.
   • Payback period = smallest t where cumulative contribution_margin(customer, 0..t) >= CAC(customer_cohort).

Implementation notes:

• Use daily batch jobs or incremental Spark jobs to update cohort aggregates; store results in a fact_cohort_metrics table (cohort_date, channel, product_line, month_offset, cac, ltv, payback_months).
• Ensure idempotency, clear event ordering (use event_time + ingestion_time), and reconciliation against finance ledger.
• Track attribution and billing changes (prorations, refunds) to compute net LTV and avoid double-counting.

1. Launch of real‑time analytics feature (announced: product blog, Month Year)

• Impact: We’ll need streaming ingestion and low‑latency processing instead of batch-only jobs. Concretely, this creates work to build Kafka/Flink or Kinesis/Spark Structured Streaming pipelines, add event schemas, implement exactly‑once semantics, and provision autoscaling compute for peak traffic. Priorities shift toward SLA monitoring (end‑to‑end latency), backpressure handling, and retention policies for hot vs. cold data.

2. New data residency / regional cloud controls (press release, Month Year)

• Impact: Data must be stored and processed per region. This requires partitioning storage (separate buckets/databases per region), adding location metadata to pipelines, and enforcing data‑movement guards in ETL (deny cross‑region copies). Governance work: update data catalog with residency tags, implement automated validations in CI to prevent policy violations, and adjust disaster‑recovery plans.

3. Public SDK / API v2 release and deprecation of v1 (release notes, Month Year)

• Impact: Clients will migrate producing different event shapes and fields; pipelines must handle mixed-version payloads. Work includes updating parsers/serde logic, adding schema registry entries, creating transformation steps to normalize v1→v2, and expanding integration tests. Also prioritize phased rollout: feature flags, shadow pipelines for v2 traffic, and run data quality checks to ensure backward compatibility.

1. Commitment: Data minimization and retention limits — policy states we only keep personal data as long as necessary and publish retention schedules.
   Engineering requirement: Enforce automated retention at storage and pipeline layers. Implement TTL lifecycle policies (e.g., partition-level expiry) for raw and curated tables: for example, set Hive/Spark table partitions with date-based expiration and configure S3/GCS lifecycle rules to transition then permanently delete objects after X days. Operational control mapping: retention policy = automated lifecycle jobs + periodic attestation reports; include a scheduled job that scans tables/partitions, deletes expired partitions, and emits retention-compliance metrics to monitoring.

2. Commitment: Protect data in transit and at rest (encryption & key management) — privacy statements promise strong encryption and key control.
   Engineering requirement: Enforce encryption-at-rest and envelope encryption in storage and tooling. Ensure all object stores and databases have server-side encryption with customer-managed KMS keys; implement application-layer encryption for sensitive fields before landing to raw buckets. Operational control mapping: enforce via Infrastructure-as-Code (Terraform) that denies creation of unencrypted buckets/instances, and pipeline CI jobs that fail if keys/policies not present; audit KMS usage logs for access controls.

3. Commitment: User control (consent, access, deletion/erasure) and auditability — users can withdraw consent and request deletion; company maintains transparency/audit logs.
   Engineering requirement: Build a consent-aware ingestion and deletion pipeline. Tag records at ingestion with consent_status and provenance metadata; implement a deletion workflow that queries tag/index to identify records to erase across systems, runs deterministic deletion or anonymization jobs, and writes immutable audit entries (who/when/what) into a tamper-evident log (append-only storage with retention). Operational control mapping: consent enforcement = runtime gate in ingestion and ETL (reject/transform data if no consent), plus a deletion orchestration service integrated with audit logging and regular reconciliation reports for privacy team.