Analyst run

1import stripe_data
2import json
3 
4def compute_mrr():
5    """
6    Calculate the current Monthly Recurring Revenue (MRR) in USD.
7    Includes subscriptions with status 'active' or 'past_due'.
8    Normalizes each price to a monthly amount, applies any active percent-off discount,
9    and converts to USD using Stripe's reporting exchange rates.
10    """
11    subs = stripe_data.subscriptions()
12    total_mrr_usd = 0.0
13 
14    for sub in subs:
15        status = sub.get("status")
16        # Only include active or past_due subscriptions
17        if status not in ("active", "past_due"):
18            continue
19 
20        items = sub.get("items", {}).get("data", [])
21        subscription_monthly = 0.0
22 
23        for item in items:
24            price = item.get("price", {})
25            unit_amount = price.get("unit_amount", 0)
26            quantity = item.get("quantity", 1)
27            recurring = price.get("recurring", {})
28            interval = recurring.get("interval", "")
29            interval_count = recurring.get("interval_count", 1)
30 
31            # Normalize to monthly amount in the subscription's currency minor units
32            monthly = unit_amount / interval_count
33            if interval == "year":
34                monthly = monthly / 12
35 
36            subscription_monthly += monthly * quantity
37 
38        # Apply any active percent-off discount
39        discount_percent = stripe_data.percent_off(sub)
40        if discount_percent:
41            subscription_monthly = subscription_monthly * (1 - discount_percent / 100)
42 
43        # Convert to USD using the reporting FX table
44        currency = sub.get("currency", "usd")
45        mrr_usd = stripe_data.to_usd(subscription_monthly, currency)
46        total_mrr_usd += mrr_usd
47 
48    return total_mrr_usd
49 
50if __name__ == "__main__":
51    mrr = compute_mrr()
52    answer = f"Our current MRR is ${mrr:,.2f} USD."
53    value = mrr
54    unit = "USD"
55    table = []  # optional; omitted for brevity
56    method = (
57        "MRR was calculated by summing the monthly recurring revenue from all "
58        "active or past‑due subscriptions, normalizing each price to a monthly amount, "
59        "applying any active percent‑off discounts, and converting to USD using "
60        "Stripe’s reporting exchange rates."
61    )
62    result = {
63        "answer": answer,
64        "value": value,
65        "unit": unit,
66        "table": table,
67        "method": method
68    }
69    print(f"RESULT: {json.dumps(result)}")

1import json
2import stripe_data
3 
4subs = stripe_data.subscriptions()
5 
6paying_customers = set()
7for sub in subs:
8    status = sub.get("status")
9    if status in ("active", "past_due"):
10        cust_id = sub.get("customer")
11        if cust_id:
12            paying_customers.add(cust_id)
13 
14count = len(paying_customers)
15 
16result = {
17    "answer": f"We have {count} paying customers right now.",
18    "value": count,
19    "unit": "customers",
20    "table": [],
21    "method": "Counted distinct customers with at least one subscription in 'active' or 'past_due' status."
22}
23 
24print("RESULT: " + json.dumps(result))

1import json
2import stripe_data
3 
4# Fixed FX rates (units of currency per 1 USD)
5FX_RATES = {
6    "usd": 1, "eur": 0.92, "gbp": 0.79, "cad": 1.37, "aud": 1.52,
7    "jpy": 150, "sgd": 1.34, "inr": 84, "brl": 5.4, "mxn": 18,
8    "aed": 3.67, "pln": 4, "thb": 35, "myr": 4.6, "zar": 18, "php": 57,
9}
10 
11def subscription_mrr_usd(sub):
12    """Compute MRR in USD for a single subscription."""
13    currency = sub.get("currency", "usd").lower()
14    rate = FX_RATES.get(currency, 1)
15    total_minor = 0
16    for item in sub.get("items", {}).get("data", []):
17        price = item.get("price", {})
18        unit_amount = price.get("unit_amount", 0)
19        quantity = item.get("quantity", 0)
20        recurring = price.get("recurring", {})
21        interval = recurring.get("interval", "month")
22        interval_count = recurring.get("interval_count", 1)
23        if interval == "year":
24            monthly_minor = unit_amount * quantity / 12.0 / interval_count
25        else:
26            monthly_minor = unit_amount * quantity / interval_count
27        total_minor += monthly_minor
28    # Apply percent-off discount
29    discount_pct = stripe_data.percent_off(sub)
30    total_minor *= (1 - discount_pct / 100.0)
31    # Convert to USD
32    usd = total_minor / rate
33    return usd
34 
35# Build customer lookup
36customers = {c["id"]: c for c in stripe_data.customers()}
37 
38# Determine MRR-contributing statuses
39MRR_STATUSES = {"active", "past_due"}
40 
41# Aggregate MRR by market segment
42mrr_by_market = {"top": 0.0, "emerging": 0.0, "unknown": 0.0}
43total_mrr = 0.0
44 
45for sub in stripe_data.subscriptions():
46    if sub.get("status") not in MRR_STATUSES:
47        continue
48    cust_id = sub.get("customer")
49    cust = customers.get(cust_id, {})
50    metadata = cust.get("metadata", {}) or {}
51    market = metadata.get("market", "unknown").lower()
52    if market not in ("top", "emerging"):
53        market = "unknown"
54    mrr_usd = subscription_mrr_usd(sub)
55    mrr_by_market[market] += mrr_usd
56    total_mrr += mrr_usd
57 
58emerging_mrr = mrr_by_market["emerging"]
59pct_emerging = (emerging_mrr / total_mrr * 100.0) if total_mrr > 0 else 0.0
60 
61answer = (
62    f"Emerging markets account for {pct_emerging:.1f}% of total MRR "
63    f"(${emerging_mrr:,.0f} of ${total_mrr:,.0f})."
64)
65 
66print(f"Total MRR: ${total_mrr:,.2f}")
67print(f"Top market MRR: ${mrr_by_market['top']:,.2f}")
68print(f"Emerging market MRR: ${mrr_by_market['emerging']:,.2f}")
69print(f"Unknown market MRR: ${mrr_by_market['unknown']:,.2f}")
70print(f"Percentage from emerging markets: {pct_emerging:.2f}%")
71 
72result = {
73    "answer": answer,
74    "value": round(pct_emerging, 2),
75    "unit": "percent",
76    "table": [
77        {"market": "top", "mrr_usd": round(mrr_by_market["top"], 2)},
78        {"market": "emerging", "mrr_usd": round(mrr_by_market["emerging"], 2)},
79        {"market": "unknown", "mrr_usd": round(mrr_by_market["unknown"], 2)},
80        {"market": "total", "mrr_usd": round(total_mrr, 2)},
81    ],
82    "method": (
83        "Summed monthly recurring revenue (in USD, after FX conversion and "
84        "percent-off discounts) across all active and past_due subscriptions, "
85        "grouped by customer market segment from customer metadata."
86    ),
87}
88 
89print("RESULT: " + json.dumps(result))

1import json
2import stripe_data
3 
4subs = stripe_data.subscriptions()
5 
6mrr_customers = set()
7canceled_customers = set()
8 
9for sub in subs:
10    status = sub.get("status")
11    customer = sub.get("customer")
12    if not customer:
13        continue
14    if status in ("active", "past_due"):
15        mrr_customers.add(customer)
16    elif status == "canceled":
17        canceled_customers.add(customer)
18 
19mrr_count = len(mrr_customers)
20canceled_count = len(canceled_customers)
21total = mrr_count + canceled_count
22 
23churn_rate = (canceled_count / total * 100) if total > 0 else 0.0
24 
25result = {
26    "answer": f"Our current logo churn rate is {churn_rate:.2f}%, based on {canceled_count} canceled customers out of {total} total (MRR-contributing plus canceled).",
27    "value": round(churn_rate, 2),
28    "unit": "percent",
29    "table": [
30        {"metric": "MRR-contributing customers", "count": mrr_count},
31        {"metric": "Canceled customers", "count": canceled_count},
32        {"metric": "Total customers", "count": total}
33    ],
34    "method": "Counted distinct customers with active or past_due subscriptions as MRR-contributing, counted canceled subscriptions as churned, and divided canceled by the sum of the two."
35}
36 
37print("RESULT: " + json.dumps(result))

1import json
2import stripe_data
3 
4# Fixed FX rates (units of currency per 1 USD)
5FX_RATES = {
6    "usd": 1, "eur": 0.92, "gbp": 0.79, "cad": 1.37, "aud": 1.52,
7    "jpy": 150, "sgd": 1.34, "inr": 84, "brl": 5.4, "mxn": 18,
8    "aed": 3.67, "pln": 4, "thb": 35, "myr": 4.6, "zar": 18, "php": 57,
9}
10 
11def get_interval_months(item):
12    """Return the number of months the billing interval represents."""
13    price = item["price"]
14    recurring = price.get("recurring", {})
15    interval = recurring.get("interval", "month")
16    interval_count = recurring.get("interval_count", 1)
17    if interval == "year":
18        return 12 * interval_count
19    elif interval == "month":
20        return 1 * interval_count
21    elif interval == "week":
22        # Approximate week as 1/4 month
23        return (1 / 4) * interval_count
24    else:
25        return 1  # default to monthly
26 
27def compute_subscription_mrr_usd(sub):
28    """Compute MRR in USD for a single subscription."""
29    currency = sub.get("currency", "usd").lower()
30    total_minor = 0
31    for item in sub.get("items", {}).get("data", []):
32        unit_amount = item.get("price", {}).get("unit_amount", 0)
33        quantity = item.get("quantity", 0)
34        interval_months = get_interval_months(item)
35        if interval_months > 0:
36            total_minor += (unit_amount * quantity) / interval_months
37 
38    # Convert minor units to USD
39    if currency == "jpy":
40        usd_amount = total_minor / FX_RATES.get(currency, 1)
41    else:
42        usd_amount = total_minor / 100 / FX_RATES.get(currency, 1)
43 
44    # Apply percent-off discount
45    discount_percent = stripe_data.percent_off(sub)
46    if discount_percent > 0:
47        usd_amount *= (1 - discount_percent / 100)
48 
49    return usd_amount
50 
51def get_plan(sub):
52    """Extract plan from subscription metadata."""
53    metadata = sub.get("metadata", {})
54    return metadata.get("plan", "unknown")
55 
56def get_billing_interval(sub):
57    """Extract billing interval from the first item's price."""
58    items = sub.get("items", {}).get("data", [])
59    if items:
60        recurring = items[0].get("price", {}).get("recurring", {})
61        interval = recurring.get("interval", "month")
62        interval_count = recurring.get("interval_count", 1)
63        return f"{interval}_{interval_count}"
64    return "unknown"
65 
66# MRR-contributing statuses
67MRR_STATUSES = {"active", "past_due"}
68 
69# Group subscriptions by archetype (plan + billing interval)
70archetypes = {}
71 
72for sub in stripe_data.subscriptions():
73    status = sub.get("status", "")
74    if status not in MRR_STATUSES:
75        continue
76 
77    plan = get_plan(sub)
78    interval = get_billing_interval(sub)
79    archetype_key = f"{plan}|{interval}"
80 
81    mrr_usd = compute_subscription_mrr_usd(sub)
82 
83    if archetype_key not in archetypes:
84        archetypes[archetype_key] = {"count": 0, "mrr": 0.0, "plan": plan, "interval": interval}
85 
86    archetypes[archetype_key]["count"] += 1
87    archetypes[archetype_key]["mrr"] += mrr_usd
88 
89# Build result table
90table = []
91for key, data in archetypes.items():
92    table.append({
93        "archetype": key,
94        "plan": data["plan"],
95        "billing_interval": data["interval"],
96        "subscriptions": data["count"],
97        "mrr_usd": round(data["mrr"], 2)
98    })
99 
100# Sort table by MRR descending
101table.sort(key=lambda x: x["mrr_usd"], reverse=True)
102 
103num_archetypes = len(table)
104 
105result = {
106    "answer": f"There are {num_archetypes} MRR-contributing subscription archetypes grouped by plan and billing interval. The table shows the subscription count and MRR (USD) for each archetype.",
107    "value": num_archetypes,
108    "unit": "archetypes",
109    "table": table,
110    "method": "Filtered subscriptions to active/past_due statuses, computed MRR per subscription by summing item unit_amount × quantity normalized to monthly, converted to USD using fixed FX rates, applied percent-off discounts, then grouped by plan and billing interval."
111}
112 
113print("RESULT: " + json.dumps(result))