Ecommerce deep dive

Three sessions in, you have the general engine: build a tree (b1), name its shape (b2), run the diagnosis playbook with leading indicators and baselines (b3). Now we specialise. Ecommerce is the ideal first domain because its top line - GMV - is a clean, deep, multiplicative tree that every other domain borrows from. Get this one into your hands and marketing (b5), branding (b6), and the rest will feel familiar. Today the tree goes two levels deep and the diagnosis gets specific.

In b1 the GMV tree was one level: Traffic x Conversion x AOV. That is enough to name a driver, but not to route a fix. So we go deeper. Traffic is really a sum of acquisition channels - SEO + Paid + Direct + Email. AOV is really a product - Units-per-order x Price-per-unit. Now a drop lands on "Paid traffic" or "Price per unit", not just "Traffic" or "AOV" - a branch a single team owns and can act on this afternoon.

{
  "unit": "$",
  "root": {
    "label": "GMV", "op": "x",
    "children": [
      { "label": "Traffic", "op": "+", "children": [
        { "label": "SEO",    "value": 52000, "unit": "visits" },
        { "label": "Paid",   "value": 38000, "unit": "visits" },
        { "label": "Direct", "value": 20000, "unit": "visits" },
        { "label": "Email",  "value": 10000, "unit": "visits" }
      ] },
      { "label": "Conversion", "value": 0.028, "pct": true },
      { "label": "AOV", "op": "x", "children": [
        { "label": "Units per order", "value": 2,  "unit": "units" },
        { "label": "Price per unit",  "value": 31, "unit": "$" }
      ] }
    ]
  }
}

GMV is the ultimate lagging number - it lands at the end. Near the leaves live the early warnings: add-to-cart rate and channel traffic move before GMV, so a dip there is your reaction time. Refunds, by contrast, lag GMV - they show up weeks after the sale. Steer by the leading leaves; report the lagging root. The deeper your ecommerce tree, the earlier the signal you can act on.

Almost every ecommerce GMV drop is one of three stories, and each maps to a specific branch of the tree. Learn the map and diagnosis starts before you open the data: the symptom already tells you which branch to walk to. This is the payoff of a two-level tree - the shape of the fall names the cause.

The full loop on the deep tree. Simulate a drop, read the coral path down to the exact leaf, then confirm the fall in SQL by segmenting revenue by channel. Tree says which branch; SQL says which slice - and now the branch is specific enough to route the fix to one team.

{
  "unit": "$",
  "root": {
    "label": "GMV", "op": "x",
    "children": [
      { "label": "Traffic", "op": "+", "children": [
        { "label": "SEO",    "value": 52000, "unit": "visits" },
        { "label": "Paid",   "value": 38000, "unit": "visits" },
        { "label": "Direct", "value": 20000, "unit": "visits" },
        { "label": "Email",  "value": 10000, "unit": "visits" }
      ] },
      { "label": "Conversion", "value": 0.028, "pct": true },
      { "label": "AOV", "op": "x", "children": [
        { "label": "Units per order", "value": 2,  "unit": "units" },
        { "label": "Price per unit",  "value": 31, "unit": "$" }
      ] }
    ]
  }
}

SELECT o.channel,
       COUNT(DISTINCT o.order_id)                 AS orders,
       ROUND(SUM(oi.quantity * oi.unit_price), 0) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'completed'
GROUP BY o.channel
ORDER BY revenue DESC;

Two live queries and one prose call. First a conversion proxy by channel, then AOV by channel, then reason from a symptom to the next query without any data at all - the skill this whole session builds.

SELECT channel,
       COUNT(*)                                       AS all_orders,
       COUNT(*) FILTER (WHERE status = 'completed')   AS completed,
       ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'completed')
             / COUNT(*), 1)                           AS completion_pct
FROM orders
GROUP BY channel
ORDER BY completion_pct DESC;

SELECT o.channel,
       COUNT(DISTINCT o.order_id)                      AS orders,
       ROUND(SUM(oi.quantity * oi.unit_price) * 1.0
             / COUNT(DISTINCT o.order_id), 2)          AS avg_order_value
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'completed'
GROUP BY o.channel
ORDER BY avg_order_value DESC;

• Draw your store's GMV tree two levels deep. Split Traffic into your real channels and AOV into units and price. Paste your numbers into the deep tree above.
• For each of the three common drops, write the branch it lands on and the owner who gets the ticket. Pin it as your symptom-to-branch map.
• Hang one leading indicator on each top branch: ad impressions under Paid, add-to-cart rate under Conversion, discount depth under Price. Watch those daily.
• Run the completion-rate-by-channel query on your own data. A channel far below the others is a checkout leak worth a look this week.
• Bring your channel mix to b5 - the marketing deep dive splits acquisition into spend, CAC, and channel efficiency.

The ecommerce GMV tree is the domain canon - the same decomposition Lean Analytics and every store's analytics stack use, applied here live instead of described. This page draws on: