Formel Skin — Data Analyst Case Study

Overview

A two-part data analyst case study completed for an interview at Formel Skin, a Berlin-based digital dermatology startup offering personalised skincare subscriptions. The case study covers financial reconciliation and operations performance analysis, reflecting the OPS & Finance Data Analyst role.

Tools used: Google BigQuery, SQL (dbt-style layered modelling), Looker Studio, Google Slides

Task 1 — Financial Reconciliation

Objective

Investigate discrepancies between Formel Skin's backend order system and its invoicing system, quantify the revenue gap, and build an automated reconciliation model to prevent future drift.

Approach

Used a layered SQL modelling approach (staging → intermediate → mart) in BigQuery to clean, normalise, and join order and invoice data. Applied EUR FX normalisation to handle multi-currency records and flagged each order-invoice pair by discrepancy type.

Key Findings

  • Identified a total gap of €19,500 — approximately 0.9% of net revenue — across five distinct discrepancy categories.
  • Orphan refunds accounted for 72% of the gap: refunds processed in the invoicing system with no matching order.
  • Currency mismatches (16%): orders recorded in non-EUR currencies without FX conversion in the invoice system.
  • Missing invoices (5%): fulfilled orders with no corresponding invoice record.
  • Built an automated daily reconciliation mart in BigQuery to surface discrepancies on an ongoing basis.

Task 2 — Operations Performance Dashboard

Objective

Design a metrics framework and Looker Studio dashboard to give the operations team visibility into case resolution performance and identify bottlenecks in the consultation workflow.

Approach

Defined a metrics tree anchored on a single North Star KPI. Modelled consultation, order, and customer data through staging and intermediate SQL layers to produce an analysis-ready mart, then built the Looker Studio dashboard on top of it.

North Star KPI

On-time case resolution rate — the share of consultations resolved within the target SLA window.

Key Findings

  • Median resolution time is 12.8 hours, but the P90 reaches 134 hours — indicating a long tail of severely delayed cases that drags overall service quality.
  • 27.4% of consultations are stuck in a pending state due to unresolved payment issues upstream.
  • 5% of orders generate three or more consultations, signalling recurring issues that could be addressed with proactive intervention.
  • Dashboard built in Looker Studio to give ops managers a live view of resolution rates, queue depth by agent, and payment-blocked consultation counts.

SQL Architecture

Queries are organised in a three-layer dbt-style structure:

  • Staging — column renaming, type casting, surrogate key generation for orders, invoices, consultations, and customers.
  • Intermediate — business logic, EUR FX normalisation, and discrepancy flagging.
  • Mart — analysis-ready tables powering the reconciliation report and the ops KPI dashboard.