Business Problem Decomposition Template for Data Analysts

When to use this template

Use this framework when a stakeholder asks a broad question like "why is revenue down?", "why did conversion get worse?", "which customer segment should we focus on?", or "what should we do next?"

Your first job is not to pull every chart. Your first job is to turn the question into a structure that can be measured and explained.

Copy this decomposition template

Business question:
Decision owner:
Metric definition:
Time window:
Comparison baseline:
Main drivers:
Priority segments:
Data quality checks:
Initial hypotheses:
SQL checks:
Likely driver:
Recommended action:
Follow-up analysis:

Step 1: translate the question into a metric

Vague questions need clear metric definitions. Define the numerator, denominator, time window, exclusions, and business owner before analyzing.

Example:
Question: Why did revenue drop?
Metric: completed order revenue
Time window: this week versus prior four-week average
Exclusions: refunded orders, test orders, internal accounts
Owner: ecommerce growth team

Step 2: build a driver tree

A driver tree breaks one metric into parts the business can act on. It prevents a generic answer and shows where the movement came from.

Revenue
  = Traffic
  x Conversion rate
  x Average order value
  x Purchase frequency

For subscription businesses, the tree may use signups, activation, retention, churn, and expansion. For marketplaces, it may use supply, demand, match rate, fill rate, take rate, and cancellation rate.

Step 3: separate movement from contribution

The segment with the biggest percentage decline is not always the segment that explains the business-wide problem. Contribution depends on segment size and change size.

driver | previous | current | abs_change | rel_change | contribution
paid users | 12000 | 9800 | -2200 | -18.3% | -42%
organic users | 18000 | 17600 | -400 | -2.2% | -8%
conversion rate | 8.0% | 7.4% | -0.6pp | -7.5% | -35%

SQL example: driver table by channel

This query summarizes buyers, orders, revenue, purchase frequency, and average order value by channel. Adapt the dimension to country, campaign, device, customer tier, or product category.

SELECT
  acquisition_channel,
  COUNT(DISTINCT user_id) AS buyers,
  COUNT(DISTINCT order_id) AS orders,
  SUM(order_value) AS revenue,
  1.0 * COUNT(DISTINCT order_id) / NULLIF(COUNT(DISTINCT user_id), 0) AS orders_per_buyer,
  AVG(order_value) AS average_order_value
FROM orders
WHERE order_date BETWEEN DATE '2026-06-01' AND DATE '2026-06-30'
  AND order_status = 'completed'
GROUP BY 1
ORDER BY revenue DESC;

Step 4: write hypotheses with mechanisms

A weak hypothesis says "users changed." A useful hypothesis explains the mechanism and the data that would support it.

Paid-search traffic shifted toward lower-intent keywords.
Mobile checkout completion dropped after a release.
A discount campaign pulled demand into the previous period.
One product category went out of stock and reduced average order value.
Tracking changed and the metric is no longer comparable.

Step 5: deliver an action-oriented answer

A useful analyst answer includes what changed, where it changed, why it likely changed, confidence level, and what the team should do next.

Summary:
Revenue decreased 11% versus the prior four-week average.
The largest contribution came from paid search traffic, which declined 18%.
Conversion rate and average order value were mostly stable.
The likely driver is lower paid-search volume, not checkout friction.
Recommendation: review campaign budget and keyword mix before changing product experience.