Sales & Revenue Performance Analytics Questions

Start by defining requirements: reps must see only their assigned accounts; managers see accounts belonging to their direct/indirect reports; admins see all. Key choices: push filtering into the warehouse (recommended) or enforce in BI layer.

Data model options

• Warehouse-driven (recommended): Maintain a mapping table user_account(user_id, account_id, role, manager_id, team_id). Create a materialized view or secure view that joins sales -> accounts -> user_account and filters by current_user. Benefits: single source of truth, better performance, auditable.
• BI-layer driven: Use BI tool RLS features to filter datasets per user. Easier to implement but can be less secure if users can access underlying DB and may have performance hit for large extracts.

Implementations (examples)

• Looker (LookML access_filter):

# in model.lkml
access_filter: {
  field: user_account.user_id
  user_attribute: user_id
}

Use user attributes (email or id) and a derived table that maps users → accounts.

• Power BI (RLS using DAX):

[UserEmail] = USERPRINCIPALNAME() &&
[AccountID] IN VALUES(UserAccount[AccountID])

Create role with table filter on UserAccount that filters AccountID by USERPRINCIPALNAME() lookup.

• Tableau (data source filter / user filter):
  Publish a UserFilter (mapping file or query):

SELECT AccountID FROM UserAccount WHERE email = USERNAME()

Use that result as a join to the main extract or a dynamic data source filter.

Dynamic vs Static RLS

• Static: precomputed permission snapshots (fast, simpler). Good when org changes rarely.
• Dynamic: evaluate USER/attributes at query time (accurate for frequent org changes). Slightly higher query cost; prefer warehouse secure views or cached tokens.

Performance implications

• Push compute to DB: use indexed mapping tables, partitioning, and materialized views. Avoid row-by-row lookups; use joins with indexed keys.
• In-tool filtering can force full-table scans if extracts are large—use incremental extracts or published secure views.
• Cacheability: static filters enable aggressive caching; dynamic user-based queries often bypass caches—consider session-level caching or group-based permissions to reduce cardinality.

Testing and validation

• Unit tests: create test user accounts across roles, sample datasets; run queries asserting expected row counts.
• Impersonation: use BI tool impersonation (Looker “View as”, Power BI “Test as role”, Tableau “Preview as user”) to verify.
• Auditing: log queries and failed access attempts; compare exported rowsets against expected lists.
• Edge cases: manager of multiple regions, indirect reports, re-assigned accounts, multiple roles, disabled users.
• Performance tests: measure query latency with representative user load; test cache hit rates.

Operational best practices

• Centralize permission mapping in warehouse; expose secure views to BI tools.
• Use user attributes (email/ID) consistently.
• Automate synchronization from HR/CRM systems.
• Document and review permissions periodically; include stakeholders.

This approach balances security, performance, and maintainability for a BI analyst responsible for cross-tool dashboards.

ARR (Annual Recurring Revenue): the run-rate of recurring subscription revenue annualized. For a pure subscription, ARR = monthly recurring revenue * 12. It reflects the expected revenue over the next 12 months if nothing changes.

ACV (Annual Contract Value): the contract’s normalized annual value. For a 12-month subscription, ACV = total contract value / contract years = $24,000.

Bookings: the value of contracts signed in the period (often reported as TCV or bookings). Bookings are a sales/commitment metric recognized at signing, not by delivery.

Recognized revenue: accrual/accounting revenue recognized over time as service is delivered (per ASC 606).

Example: 12-month, $24,000 contract signed in the middle of a quarter (assume signed Feb 15; quarter = Jan–Mar).

• ARR (this quarter): +$24,000 (adds $24k to run-rate from the signing date forward)
• ACV: $24,000 (the contract’s annual value)
• Bookings (this quarter): $24,000 (full contract value is booked at signing)
• Recognized revenue (this quarter): revenue for service delivered in-quarter = 1.5 months of 12 = 1.5/12 * $24,000 = $3,000

BI implications: show bookings as top-of-funnel spike at sign date, ARR/ACV as run-rate/health metrics, and recognized revenue on P&L/time-based revenue charts. Make sure dashboard definitions are explicit so stakeholders don’t confuse bookings with recognized revenue.

Analysis framework
I would break the problem into four layers: territory design, market potential, account assignment, and rep execution.

1) Normalize performance
First, I would compare quota attainment after adjusting for segment, ACV mix, cycle length, and tenure. A raw attainment gap may simply reflect harder books, not weaker reps.

2) Measure territory quality
I would assess each territory’s addressable market: account count, historic win rates, average deal size, pipeline density, and whitespace. If one territory has far less opportunity, the issue is likely design rather than execution.

3) Check account assignment
I would review whether high-potential accounts are clustered unfairly, whether house accounts were distributed well, and whether book changes created instability. A frequent cause is uneven assignment, not territory labels themselves.

4) Evaluate rep execution
For equalized territories, I would compare activity quality, stage conversion, follow-up speed, deal aging, and forecast discipline. If one rep consistently underperforms after normalization, the issue is likely execution or coaching.

What I’d present
To leadership, I would show a heat map of quota attainment vs. market potential, a root-cause summary by territory, and clear recommendations: redesign territory boundaries, rebalance accounts, or coach specific reps. I would avoid a single blunt conclusion and instead separate structural issues from people issues.

Approach: build a monthly snapshot for each cohort customer for the 12 months, capture each customer’s starting MRR at month 0, then for each month compute:

• NRR numerator: sum of current MRR (includes expansions, contractions, reactivations)
• GRR numerator: sum of least(current MRR, start_mrr) — this excludes expansions above the start MRR
  Denominator for both = sum(start_mrr). Churn is represented by current MRR = 0; reactivations restore MRR in later months and will count up to start_mrr in GRR and fully in NRR.

Example PostgreSQL (assumes months are calendar months; adjust generate_series as needed):

-- params
WITH params AS (
  SELECT date_trunc('month', DATE '2024-01-01') AS cohort_start  -- set cohort start
),

-- expand subscriptions into one row per customer-month with effective mrr for that month
months AS (
  SELECT generate_series(0,11) AS month_offset
),
customer_cohort AS (
  -- customers active at cohort start
  SELECT DISTINCT s.customer_id
  FROM subscriptions s, params p
  WHERE s.period_start <= p.cohort_start
    AND (s.period_end IS NULL OR s.period_end > p.cohort_start)
),
monthly_snap AS (
  SELECT
    c.customer_id,
    p.cohort_start + (m.month_offset || ' months')::interval AS month_start
  FROM customer_cohort c
  CROSS JOIN months m
  CROSS JOIN params p
),

-- for each customer-month find the mrr effective on the first day of that month
customer_month_mrr AS (
  SELECT
    ms.customer_id,
    ms.month_start,
    COALESCE(s.mrr_amount, 0) AS mrr
  FROM monthly_snap ms
  LEFT JOIN LATERAL (
    SELECT s2.mrr_amount
    FROM subscriptions s2
    WHERE s2.customer_id = ms.customer_id
      AND s2.period_start <= ms.month_start
      AND (s2.period_end IS NULL OR s2.period_end > ms.month_start)
    ORDER BY s2.period_start DESC
    LIMIT 1
  ) s ON true
),

-- starting mrr per customer (at cohort_start)
start_mrr AS (
  SELECT
    c.customer_id,
    COALESCE(s.mrr_amount, 0) AS start_mrr
  FROM customer_cohort c
  LEFT JOIN LATERAL (
    SELECT s2.mrr_amount
    FROM subscriptions s2, params p
    WHERE s2.customer_id = c.customer_id
      AND s2.period_start <= p.cohort_start
      AND (s2.period_end IS NULL OR s2.period_end > p.cohort_start)
    ORDER BY s2.period_start DESC
    LIMIT 1
  ) s ON true
),

-- combine to compute per-month aggregation for cohort
agg AS (
  SELECT
    cm.month_start,
    SUM(sm.start_mrr) AS total_start_mrr,
    SUM(cm.mrr) AS total_mrr,  -- used for NRR numerator
    SUM(LEAST(cm.mrr, sm.start_mrr)) AS retained_mrr_excluding_expansions  -- GRR numerator
  FROM customer_month_mrr cm
  JOIN start_mrr sm ON sm.customer_id = cm.customer_id
  GROUP BY cm.month_start
  ORDER BY cm.month_start
)

SELECT
  month_start,
  total_start_mrr,
  total_mrr,
  retained_mrr_excluding_expansions,
  ROUND(retained_mrr_excluding_expansions / NULLIF(total_start_mrr,0)::numeric, 4) AS grr,  -- Gross Revenue Retention
  ROUND(total_mrr / NULLIF(total_start_mrr,0)::numeric, 4) AS nrr  -- Net Revenue Retention
FROM agg;

Key points / reasoning:

• Expansions: increase current mrr; included in NRR numerator (total_mrr) but excluded from GRR by capping at start_mrr (LEAST).
• Contractions: decrease current mrr; reduce both GRR and NRR proportionally.
• Churn: modeled as absence of a subscription row -> mrr = 0; drops retention in both metrics.
• Reactivations: when a churned customer returns, mrr > 0 in later months — counts toward GRR up to start_mrr and fully toward NRR.
  Edge cases:
• start_mrr = 0: exclude from cohort or handle NULLIF to avoid div by zero.
• Multiple overlapping subscription rows: choose latest effective record per month (we use ORDER BY period_start DESC).
• Different billing windows: this monthly-snapshot approach assumes MRR is monthly-uniform within period; adjust if daily granularity needed.

This gives monthly GRR and NRR for the cohort of customers active at cohort_start, suitable for BI dashboards and trend analysis.

Week 1 — Immediate (must-have, trust-building)

• Daily/weekly MRR & New MRR (adds/expansions/contractions): primary health metric for subscription startups; shows revenue velocity.
• Active trials & Trial → Paid conversion rate: immediate signal on top-of-funnel effectiveness.
• Customer count & Logo churn rate (monthly): flags retention problems fast.
• Sales pipeline basics: SQLs, demo-to-trial, average deal size — sanity-checks for growth forecasts.
  Why: These are quick to compute, high-signal for founders/ops, and expose data gaps early so decisions (pricing, urgent retention fixes) can be made within days.

Week 3 — Operational (process optimization)

• Cohort retention (7/30/90-day) and churn (dollar & logo) by cohort/segment: reveals whether retention is improving or degrading.
• Net Revenue Retention (NRR) and Expansion MRR: measures land-and-expand viability.
• CAC & CAC payback (by channel): operationalize marketing spend.
• Funnel conversion times & activation rate; support ticket volume/triage: surfaces friction points.
  Why: These enable tactical fixes — onboarding flows, sales playbooks, channel reallocation — and prioritize investments with measurable ROI.

Week 8 — Strategic (scaling & forecasting)

• LTV, LTV:CAC, unit economics by segment (industry, ARR band): informs go-to-market focus.
• ARR growth rate, cohort lifetime value curves, and scenario-based revenue forecasts: support fundraising and hires.
• Product engagement metrics tied to retention (DAU/MAU, key feature usage): link product changes to revenue.
• Churn drivers analysis + leading indicators (usage dropoffs): predicts problems before revenue moves.
  Why: These support long-term GTM decisions, resource prioritization, and investor conversations — turning operational wins into a scalable plan.