Y

Business acumen · Beginner

Define metrics precisely and connect them in a tree

3 min read

Why it matters

Business acumen and KPIs came up in about a third of the Data Analyst job descriptions we read, and in almost every product analyst interview. The hard part is rarely the calculation. It is agreeing what "active customer" or "conversion" means, and knowing which lever moves the number the business cares about.

The concept

A metric is a precise calculation. A KPI is a metric the business has chosen to steer by, usually with a target.

A precise definition answers:

  • numerator and denominator (orders divided by what: sessions, visitors, users?)

  • unit and time window (per day, per calendar month, rolling 28 days?)

  • inclusions and exclusions (cancellations, test accounts, internal staff?)

  • source of truth (which table, which column)

A metric tree breaks a top-line number into drivers, so each team knows which branch it owns:

text
Revenue
├── Customers
│   ├── New customers
│   └── Returning customers  (retention)
└── Revenue per customer
    ├── Orders per customer   (frequency)
    └── Average order value
        ├── Items per order
        └── Price per item

Worked example: Online Retail's revenue tree

Using clean lines from the UCI Online Retail II data (December 2010 to December 2011):

metric_tree.sql
with lines as (
  select "Customer ID" as customer_id, Invoice as invoice, Quantity * Price as revenue
  from sales
  where Invoice not like 'C%' and Quantity > 0 and "Customer ID" is not null
),
orders as (
  select customer_id, invoice, sum(revenue) as order_value
  from lines group by 1, 2
)
select
  sum(order_value)                                       as revenue,
  count(distinct customer_id)                            as customers,
  count(*)                                               as orders,
  round(count(*) * 1.0 / count(distinct customer_id), 2)  as orders_per_customer,
  round(avg(order_value), 2)                             as avg_order_value
from orders;

You should get revenue of about 8.91 million from 4,339 customers placing 18,536 orders: about 4.27 orders per customer and an average order value of about 481. Multiply them back and you recover revenue: 4,339 × 4.27 × 481 ≈ 8.9 million.

Now the tree becomes a planning tool. A 10% revenue goal can come from 434 more customers, or from 0.43 more orders per customer, or from 48 more per order. Each branch belongs to a different team (acquisition, retention, merchandising) and has a different cost.

Write the definition down where everyone can find it:

metrics.yml
average_order_value:
  description: Mean revenue per order.
  numerator: sum(quantity * price) over sale lines
  denominator: count(distinct invoice)
  excludes: [cancellation invoices (prefix C), non-positive quantities, lines without a customer]
  grain: order
  owner: Sales analytics

Common mistakes

  • Ratios without their parts. Conversion rose from 2% to 3%: because orders went up, or because traffic fell? Always show numerator and denominator.

  • Vanity metrics. Page views and app downloads rarely drive decisions. Ask "what would we do differently if this doubled?".

  • Two definitions of the same KPI. Finance and product count "revenue" differently. Agree one, document it, and name the other something else.

  • Averages of averages across groups of different sizes.

Interview angle

"How would you measure the success of feature X?" Answer with one primary metric tied to the business goal, two or three supporting metrics from its branch of the tree, and one guardrail metric that must not get worse. Define each precisely.

Practice task

  1. Build the tree for a quick commerce app (Blinkit, Zepto, Swiggy Instamart): orders, basket size, delivery time, cancellations. Mark which team owns each branch.

  2. Compute the Online Retail tree for the UK alone and for all other countries. Which branch explains the difference in revenue per customer?

Further reading