Skip to content

Case study 05

Status: Analysis and recommendations deliveredData Science

Customer and product analytics

Get the grain right, then let the history speak.

Analyzed usage and purchasing patterns in a legacy business-data product with SQL, to inform the design of its replacement.

Role
Data Science Co-op
Period
2026
Status
Analysis and recommendations delivered

01 — My contribution

I wrote the analytical SQL, reconciled figures across systems, moved recurring analyses into Python, and wrote up the findings and recommendations.

02 — Problem and context

Before building a replacement product, the team wanted evidence about how the existing one was actually used: who buys, what they buy, how often, and through which purchase paths.

The history lived in a production relational database but had not been analyzed systematically.

From transactions to recommendations

  1. Define grain

    Order, line, or pull

  2. Query

    Joins, distinct counts, buckets

  3. Reconcile

    Across systems

  4. Automate

    Repeatable Python scripts

  5. Write up

    Findings, caveats, recommendations

Simplified flow of the analysis.

03 — Approach and key decisions

  1. Decision 01

    Define the grain first

    Orders, line items, and individual report pulls are different units. Each question was answered at an explicit grain, with distinct counts so composite orders were not double counted.

  2. Decision 02

    Standard analytical SQL

    Multi-table joins, conditional aggregation, time bucketing, threshold filters, and cohort comparisons between periods.

  3. Decision 03

    Reconcile before concluding

    Figures were reconciled across systems before any conclusion was drawn from them.

  4. Decision 04

    Exclude what isn't real use

    Internal and test accounts were filtered out of every analysis.

  5. Decision 05

    Make it repeatable

    Recurring analyses moved from ad-hoc queries into Python scripts so they could be re-run as data changed.

  6. Decision 06

    Say what the data cannot answer

    Where the schema could not support a question — for example, events that were never recorded — the write-up said so instead of estimating.

Illustrative example — synthetic schema

-- Orders and line items are different grains.
SELECT
  DATE_FORMAT(o.ordered_at, '%Y-%m')          AS month,
  COUNT(DISTINCT o.order_id)                   AS orders,
  COUNT(*)                                     AS line_items,
  COUNT(DISTINCT CASE WHEN o.channel = 'web'
                      THEN o.order_id END)     AS web_orders
FROM orders o
JOIN order_lines l ON l.order_id   = o.order_id
JOIN accounts    a ON a.account_id = o.account_id
WHERE a.is_internal = FALSE
GROUP BY month
ORDER BY month;

Counting rows after the join would count line items, not orders. Distinct counts and conditional aggregation keep each figure at the grain it claims.

04 — Evaluation and evidence

This was not a modeling project, so evaluation meant consistency and transparency.

Explicit grain definitions
Every figure states the unit it counts.
Reconciled totals
Totals agree across the systems they were drawn from, or the difference is explained.
Documented exclusions and caveats
What was filtered out, and what could not be measured, is written down.

05 — What shipped

Analysis workbooks and written recommendations on adoption, product use, and what the replacement should support from the start.

06 — Limitations and lessons

Limitations

  • Delivering recommendations does not establish that they were adopted or what impact they had.
  • Historical usage reflects the old product's design; it is not a forecast for a new one.

Lessons

  • Most analytics errors in SQL are grain errors.
  • Writing down what the data cannot answer is part of the analysis.

07 — Tools

  • SQL
  • MySQL
  • DBeaver
  • Python
  • pandas
  • Excel