Shopify Admin Subscription Mrr Tracker

Purpose

For stores selling subscription products via Shopify's native subscriptions, this skill aggregates active subscription contracts into the standard SaaS-style metrics finance teams care about: monthly recurring revenue (MRR), annualized recurring revenue (ARR), active subscriber count, average revenue per subscriber (ARPU), and a rolling churn rate. Read-only — no mutations.

Prerequisites

• Authenticated Shopify CLI session: shopify store auth --store <domain> --scopes read_own_subscription_contracts
• API scopes: read_own_subscription_contracts

Parameters

Safety

ℹ️ Read-only skill — no mutations are executed. This skill is only meaningful if the store sells subscription products. If subscriptionContracts returns 0 contracts, the report exits with $0 MRR — that is the correct answer, not an error.

Recurring Revenue Model

For each subscription contract, the contribution to MRR depends on its billing interval:

Copy code
billing_amount = sum(line.currentPrice × line.quantity) for each line in contract
mrr_contribution =
  billing_amount                       if interval = MONTH and intervalCount = 1
  billing_amount / intervalCount       if interval = MONTH (e.g., every 3 months → /3)
  billing_amount × 4.345               if interval = WEEK (avg weeks per month)
  billing_amount × 4.345 / intervalCount if interval = WEEK with intervalCount > 1
  billing_amount / 12                  if interval = YEAR
  billing_amount × 30 / intervalCount  if interval = DAY

Aggregations:

• MRR = Σ mrr_contribution for all ACTIVE (and PAUSED if include_paused) contracts
• ARR = MRR × 12
• Active subscribers = unique customer.id count
• ARPU = MRR / active subscribers
• Churn rate (period) = contracts cancelled inside churn_window_days ÷ contracts that were active at the start of the window
• Net new subscribers = new contracts inside the window − cancelled contracts inside the window

Workflow Steps

1. OPERATION: subscriptionContracts — query Inputs: first: 250, select id, status, createdAt, updatedAt, nextBillingDate, customer { id, displayName }, currencyCode, billingPolicy { interval, intervalCount }, lines { edges { node { currentPrice { amount, currencyCode }, quantity, productId, variantId, title } } }, pagination cursor Expected output: All subscription contracts; paginate until hasNextPage: false

2. Classify each contract by status: ACTIVE, PAUSED, CANCELLED, EXPIRED, FAILED

3. Compute mrr_contribution for each active (and paused if requested) contract using the model above

4. Aggregate MRR / ARR / subscribers / ARPU

5. For churn: count cancellations where updatedAt is within churn_window_days and status moved to CANCELLED; count contracts that existed and were ACTIVE at as_of - churn_window_days

6. Build a per-product breakdown so finance can see which subscription SKUs drive MRR

GraphQL Operations

graphqlCopy code
# subscriptionContracts:query — validated against api_version 2025-01
query SubscriptionMRR($after: String) {
  subscriptionContracts(first: 250, after: $after) {
    edges {
      node {
        id
        status
        createdAt
        updatedAt
        nextBillingDate
        currencyCode
        customer {
          id
          displayName
          defaultEmailAddress { emailAddress }
        }
        billingPolicy { interval intervalCount minCycles maxCycles }
        deliveryPolicy { interval intervalCount }
        lines(first: 50) {
          edges {
            node {
              id
              productId
              variantId
              title
              quantity
              currentPrice { amount currencyCode }
            }
          }
        }
      }
    }
    pageInfo { hasNextPage endCursor }
  }
}

Session Tracking

Claude MUST emit the following output at each stage. This is mandatory.

On start, emit:

Copy code
╔══════════════════════════════════════════════╗
║  SKILL: Subscription MRR Tracker             ║
║  Store: <store domain>                       ║
║  As of: <YYYY-MM-DD>                         ║
║  Started: <YYYY-MM-DD HH:MM UTC>             ║
╚══════════════════════════════════════════════╝

After each step, emit:

Copy code
[N/TOTAL] <QUERY|MUTATION>  <OperationName>
          → Params: <brief summary of key inputs>
          → Result: <count or outcome>

On completion, emit:

For format: human (default):

Copy code
══════════════════════════════════════════════
SUBSCRIPTION MRR REPORT  (as of <date>)
  Active contracts:        <n>
  Paused contracts:        <n>
  Cancelled (last <n>d):   <n>
  ─────────────────────────────
  MRR:                     $<amount>
  ARR:                     $<amount>
  Active subscribers:      <n>
  ARPU:                    $<amount>
  Churn rate (<n>d):       <pct>%
  Net new (<n>d):          <n>

  Top subscription products by MRR:
    "<title>"   Subs: <n>   MRR: $<n>
    "<title>"   Subs: <n>   MRR: $<n>

  Output: subscription_mrr_<date>.csv
══════════════════════════════════════════════

For format: json, emit:

jsonCopy code
{
  "skill": "subscription-mrr-tracker",
  "store": "<domain>",
  "as_of": "<YYYY-MM-DD>",
  "active_contracts": 0,
  "paused_contracts": 0,
  "cancelled_in_window": 0,
  "mrr": 0,
  "arr": 0,
  "active_subscribers": 0,
  "arpu": 0,
  "churn_rate_pct": 0,
  "net_new": 0,
  "currency": "USD",
  "by_product": [],
  "output_file": "subscription_mrr_<date>.csv"
}

Output Format

CSV file subscription_mrr_<YYYY-MM-DD>.csv with columns: contract_id, status, customer_id, customer_email, created_at, next_billing_date, interval, interval_count, billing_amount, mrr_contribution, currency, product_titles

Error Handling

Best Practices

• This skill is only meaningful if the store actually sells subscription products. If you don't run subscriptions, MRR will be $0 — that's correct.
• Run on the first day of every month so MRR snapshots align with finance's monthly close cadence.
• Track MRR week-over-week to catch payment-failure spikes early — FAILED contracts that don't recover become churn within 30 days.
• Pre-paid annual subscriptions inflate ARR without inflating cash flow in any given month — keep them separate when reporting to investors who want a cash-flow view.
• Pair with churn-risk-scorer (transactional churn) to triangulate subscription churn vs. one-time-purchase churn — they deserve separate retention strategies.
• Multi-currency stores: report MRR per currency; converting via spot FX rates produces a misleading number that fluctuates with exchange rates rather than business performance.