LookML Metric Governance: Define and Version Metrics

📌 Series: GA4 × SFCC Reconciliation — Part 4 of 5

This article is part of a technical series on reconciling e-commerce order data between Salesforce Commerce Cloud (SFCC) and Google Analytics 4 (GA4). Parts 1–3 cover what Looker is, why GA4 and SFCC never match, and how to fix the transaction ID join. This part covers LookML metric governance. If you are arriving here directly, the section below gives you the context to make this article standalone.

Context: The Problem This Article Solves

Salesforce Commerce Cloud (SFCC) is an enterprise e-commerce platform. It records every order server-side: the order ID, value, channel, customer type, fulfilment status. It is the system of record for revenue.

Google Analytics 4 (GA4) tracks user behaviour client-side via a JavaScript tag in the browser. When a purchase completes, GA4 fires a purchase event. It is the system of record for digital attribution.

The reconciliation problem is that these two systems never agree on the same revenue number. SFCC usually reports more orders than GA4, because some purchases never reach GA4 (consent refused, ad blockers, confirmation page not loaded, orders placed offline). Safari’s ITP mostly damages attribution — the order is recorded, but often as a “new” direct visitor (WebKit Tracking Prevention). GA4 can also over-count: its transaction ID deduplication only works for web streams, not app streams (GA4 Help). In the projects I’ve audited, the gap has ranged from roughly 10% to 35% of web orders; measure yours before you set a target.

Figure 0 — SFCC and GA4 are two different systems measuring overlapping but not identical realities. SFCC is the revenue source of truth. GA4 is the attribution source of truth.

Parts 1–3 dealt with the data layer: extracting both datasets into BigQuery, normalising order IDs so they can be joined. The result is a reconciliation_base table with one row per SFCC order, enriched with its GA4 match (or absence).

This article addresses the semantic layer: how do you define your metrics in a way that is consistent, documented, version-controlled, and cannot accidentally diverge across dashboards? That is what LookML solves.

Monday morning. The CFO opens three revenue reports side by side. Three different numbers, all for Q3. She asks which one is correct. Nobody has a confident answer. This is not caused by bad data. It is caused by business logic living in individual dashboards rather than a shared, governed model.

The Root Cause: Logic in the Wrong Layer

In many dashboard tools, metrics end up defined at the dashboard level. Every analyst redefines the metrics they need, copies a formula, makes a small adjustment, and forgets to note what changed. Over time: the same metric has fifteen definitions, business rule changes require hunting down every dashboard, new team members can’t find the canonical version, audits are painful.

LookML solves this by moving business logic from the dashboard layer to the model layer. A metric is defined once, documented inline, version-controlled in Git, and consumed identically by every dashboard and user.

Figure 1 — Without LookML, business logic is distributed and diverges. With LookML, it is centralised, documented, and version-controlled.

Dimensions vs Measures: The Foundational Distinction

Every field in LookML is either a dimension or a measure. A dimension is a non-aggregated attribute (order ID, date, channel, status) used to group, filter, and segment. A measure is an aggregation (sum, count, average, ratio) that cannot be used to group. Looker won’t let a sum/average/count measure reference another measure, and — when you declare primary keys and join relationships — symmetric aggregates stop joins from double-counting (symmetric aggregates).

Complete dimension examples

view: sfcc_orders {
  sql_table_name: `project.reconciliation.norm_sfcc_orders` ;;

  dimension: order_id {
    type: string
    sql: ${TABLE}.norm_order_id ;;
    primary_key: yes
    hidden: yes
    description: "Normalised SFCC order ID. Used as join key."
  }

  dimension_group: created {
    type: time
    timeframes: [raw, time, date, week, month, quarter, year]
    sql: ${TABLE}.created_at ;;
    datatype: timestamp
    convert_tz: yes
  }

  dimension: channel {
    label: "Order Channel"
    type: string
    sql: ${TABLE}.channel ;;
    description: "Values: web, store, phone, api, b2b."
  }

  dimension: order_status {
    type: string
    sql: ${TABLE}.status ;;
  }

  dimension: order_size_tier {
    label: "Order Size Tier"
    type: string
    sql: CASE
      WHEN ${TABLE}.order_value < 50    THEN '1. Small (<€50)'
      WHEN ${TABLE}.order_value < 200   THEN '2. Medium (€50-200)'
      WHEN ${TABLE}.order_value < 1000  THEN '3. Large (€200-1000)'
      ELSE '4. Enterprise (>€1000)'
    END ;;
    description: "Prefix numbers ensure correct sort order in visualisations."
  }

  dimension: order_value {
    type: number
    sql: ${TABLE}.order_value ;;
    hidden: yes
  }
}

Complete measure examples with documentation

  measure: confirmed_order_count {
    label: "Confirmed Orders"
    type: count_distinct
    sql: CASE WHEN ${order_status} NOT IN ('cancelled', 'fraud', 'test')
              THEN ${order_id} END ;;
    description: "Excludes cancelled, fraud, and test orders."
  }

  measure: gross_revenue {
    label: "Gross Revenue (€)"
    type: sum
    sql: ${TABLE}.order_value ;;
    value_format_name: decimal_2
    description: "Total order value before refunds."
    filters: [order_status: "-cancelled,-fraud,-test"]
  }

  measure: net_revenue {
    label: "Net Revenue (€)"
    type: sum
    sql: ${TABLE}.order_value - COALESCE(${TABLE}.refund_amount, 0) ;;
    value_format_name: decimal_2
    description: "Gross revenue minus processed refunds. Does not include pending refunds."
    filters: [order_status: "-cancelled,-fraud,-test"]
  }

  measure: average_order_value {
    label: "Average Order Value (€)"
    type: number
    sql: ${net_revenue} / NULLIF(${confirmed_order_count}, 0) ;;
    value_format_name: decimal_2
  }

  measure: web_revenue {
    label: "Web Revenue (€)"
    type: sum
    sql: ${TABLE}.order_value - COALESCE(${TABLE}.refund_amount, 0) ;;
    filters: [channel: "web,storefront"]
    value_format_name: decimal_2
    description: "Net revenue from web storefront channel only."
  }

Cross-Source Metrics: GA4 + SFCC in a Single Governed Model

The real governance payoff comes with cross-source metrics — measures that combine data from GA4 and SFCC. These are the most frequently redefined metrics in organisations lacking a governance layer.

view: order_reconciliation {
  sql_table_name: `project.reconciliation.reconciliation_base` ;;

  dimension: order_origin {
    label: "Order Origin"
    type: string
    sql: ${TABLE}.order_origin ;;
    description: "tracked_web = matched GA4 session | offline_store = POS | b2b_wholesale = B2B | dark_traffic_web = web order no GA4 match | unknown = unclassified"
  }

  dimension: tracking_confidence {
    label: "Tracking Confidence"
    type: string
    sql: CASE
      WHEN ${TABLE}.order_origin = 'tracked_web'    THEN '1. High'
      WHEN ${TABLE}.order_origin IN ('offline_store','offline_callcenter','b2b_wholesale','subscription') THEN '2. Structural gap'
      WHEN ${TABLE}.order_origin = 'dark_traffic_web' THEN '3. Fixable gap'
      ELSE '4. Unknown'
    END ;;
  }

  measure: sfcc_net_revenue {
    label: "SFCC Net Revenue (€)"
    type: sum
    sql: ${TABLE}.sfcc_net_revenue ;;
    value_format_name: decimal_2
    description: "SFCC gross revenue after deducting processed refunds."
  }

  measure: ga4_reported_revenue {
    label: "GA4 Reported Revenue (€)"
    type: sum
    sql: ${TABLE}.ga4_revenue ;;
    value_format_name: decimal_2
    description: "Revenue as reported by the GA4 purchase event."
  }

  measure: revenue_delta {
    label: "Revenue Gap SFCC vs GA4 (€)"
    type: number
    sql: ${sfcc_net_revenue} - ${ga4_reported_revenue} ;;
    value_format_name: decimal_2
    description: "Positive = SFCC reports more than GA4 (normal, expected)."
  }

  measure: revenue_delta_pct {
    label: "Revenue Gap %"
    type: number
    sql: (${sfcc_net_revenue} - ${ga4_reported_revenue}) / NULLIF(${sfcc_net_revenue}, 0) ;;
    value_format_name: percent_1
    description: "Internal target: below 15% for web-only orders (set from our baseline)."
  }

  measure: web_match_rate {
    label: "Web Order Match Rate"
    type: number
    sql: COUNT(DISTINCT CASE WHEN ${TABLE}.session_id IS NOT NULL AND ${TABLE}.channel IN ('web','storefront') THEN ${TABLE}.order_id END)
         / NULLIF(COUNT(DISTINCT CASE WHEN ${TABLE}.channel IN ('web','storefront') THEN ${TABLE}.order_id END), 0) ;;
    value_format_name: percent_1
    description: "Match rate for web storefront orders only. Internal target: above 85%."
  }

  measure: tracked_revenue {
    label: "Tracked Revenue (€)"
    type: sum
    sql: CASE WHEN ${TABLE}.order_origin = 'tracked_web' THEN ${TABLE}.sfcc_net_revenue END ;;
    value_format_name: decimal_2
  }

  measure: fixable_gap_revenue {
    label: "Fixable Gap Revenue (€)"
    type: sum
    sql: CASE WHEN ${TABLE}.order_origin = 'dark_traffic_web' THEN ${TABLE}.sfcc_net_revenue END ;;
    value_format_name: decimal_2
    description: "Revenue from web orders with no GA4 match. Financial cost of tracking gaps."
  }
}

Governance Best Practices

1. Document every measure with a description that answers two questions

The description field surfaces as a tooltip in the Explore UI. It must answer: what does this metric include? and what does it explicitly exclude? “Total revenue” is useless. “Net revenue from confirmed web orders, after deducting processed refunds. Excludes cancelled, fraud, and B2B orders.” is genuinely useful.

2. Version control every model change in Git

Every change to the LookML model is a commit. This creates a complete audit trail that makes answering “why did net revenue drop 8% on March 15th?” a solvable question.

# commit a3f9b12 — Update net_revenue to exclude pending refunds
# - sql: ${TABLE}.order_value - COALESCE(${TABLE}.refund_amount, 0) ;;
# + sql: ${TABLE}.order_value - COALESCE(
# +        CASE WHEN ${TABLE}.refund_status = 'processed'
# +        THEN ${TABLE}.refund_amount END, 0) ;;
# Once deployed to production, every dashboard using net_revenue picked up the change (after its cache refreshed).

3. Use labels that business users can read

sfcc_orders.net_revenue is ambiguous. "Net Revenue (€) — SFCC, post-refund" is not. For reconciliation metrics, always include the source system in the label.

4. Hide technical fields aggressively

Join keys, intermediate IDs, raw numeric fields — hide with hidden: yes. They remain available for calculations and joins, but do not clutter the Explore field picker.

5. Use value format names consistently

measure: net_revenue  { value_format_name: decimal_2 }  # 12,345.67
measure: order_count  { value_format_name: decimal_0 }  # 12,346
measure: match_rate   { value_format_name: percent_1 }  # 87.4%
measure: revenue_delta { value_format: "+0.00;-0.00;0.00" }

The Complete Governance Workflow

Analyst builds a dashboard. They select SFCC Net Revenue and Revenue Gap % from the Explore. Governed definitions, no formula to write.
Finance requests a definition change. One measure update, one pull request, one deploy. Every dashboard picks it up on its next query (cached results expire with your datagroup policy; see deploying changes). Documented in Git forever.
New hire asks what “web dark traffic rate” means. They hover over the metric. They read the description. No need to ask anyone.
Audit requires proof of revenue calculation. Share the Git commit history. Complete, traceable record.
Deployment breaks the tag. Looker alert on web_match_rate fires via Slack. Investigated same day.

What Comes Next

Part 5: Dark traffic and orphan orders — the complete classification system, the metrics to build on top, and the full reconciliation dashboard architecture.

Sources and Further Reading

LookML dimension reference: cloud.google.com/looker/docs/reference/param-field-dimension
LookML measure reference: cloud.google.com/looker/docs/reference/param-field-measure
LookML filters on measures: docs.cloud.google.com/looker/docs/reference/param-field-filters
Looker Git integration: cloud.google.com/looker/docs/version-control-and-deploying-changes
LookML best practices: docs.cloud.google.com/looker/docs/best-practices/best-practices-lookml-dos-and-donts

LookML Metric Governance: The Complete Guide to Defining, Enforcing, and Versioning Business Metrics