🎯 Overview Analytics

📖 Overview

internal/api/v1/billing/services/overview.service.js centralises every KPI that feeds the billing overview screens. It aggregates MongoDB collections populated by Stripe webhooks, reconciles them with live Stripe lookups, and normalises the values into consistent currency/period windows. The service expects pagination and date range inputs from controllers and returns presentation-ready DTOs with pre-filled gaps for graph continuity.

File Path: internal/api/v1/billing/services/overview.service.js

🗄️ Collections Used

📚 Full Schema: See Database Collections Documentation

billing.subscriptions

• Operations: Read via aggregation pipelines.
• Model: shared/models/billing-subscription.js
• Usage Context: Counts new vs renewing subscriptions and drives product-level cohort metrics.

billing.charges

• Operations: Read via aggregation pipelines.
• Model: shared/models/billing-charge.js
• Usage Context: Revenue KPIs, growth calculations, and risk analysis.

billing.disputes

• Operations: Read via aggregation pipelines.
• Model: shared/models/billing-dispute.js
• Usage Context: Offsets net revenue and tracks dispute counts/costs.

billing.refunds

• Operations: Read via aggregation pipelines.
• Model: shared/models/billing-refund.js
• Usage Context: Deducts refund totals from net volume analytics.

billing.customers

• Operations: Read via aggregation pipelines.
• Model: shared/models/billing-customer.js
• Usage Context: Identifies new customers/subscribers within a period.

billing.products

• Operations: findOne / updateOne writes when cache misses occur, otherwise read.
• Model: shared/models/billing-product.js
• Usage Context: Enriches subscription-level analytics with human-friendly product names.

🔄 Data Flow

flowchart TD
    A[Controller payload] --> B[overview.service]
    B --> C{Metric type}
    C -->|Revenue| D[Mongo Aggregations on billing.charges]
    C -->|Subscriptions| E[Mongo Aggregations on billing.subscriptions]
    C -->|Disputes/Refunds| F[Mongo Aggregations]
    C -->|Stripe Lookups| G[Stripe SDK via stripeConfig]
    D & E & F --> H[fillMissingDateData]
    G --> I[Optional cache update (billing.products)]
    H --> J[DTO assembly]
    I --> J
    J --> K[Controller Response]

    classDef mongo fill:#f5f7ff,stroke:#4a65ff;
    classDef external fill:#e8fff4,stroke:#00a86b;
    class D,E,F mongo;
    class G external;

🔧 Business Logic & Functions

---

salesOverview({ startDate, endDate, stripeBilling })

Purpose: Calculates revenue breakdown for new vs renewing subscriptions within a reporting window.

Parameters:

• startDate/endDate (String  optional) – ISO date strings; defaults to trailing 30 days when omitted.
• stripeBilling (Object) – Includes stripe_user_id for connected-account scoping.

Returns: Promise<{ currency, new, renewing, total, by_date: Array<{ date, total }> }>

• by_date is gap-filled to ensure chart continuity even on zero-revenue days.

Business Logic Flow:

1. Normalise the date window to moment objects; fallback to last 30 days.
2. Derive the comparative period (secondSetStart, secondSetEnd) to calculate renewals.
3. Run three aggregations on billing.subscriptions:
   • New activations with created within the range.
   • Renewals where current_period_start crosses into the range.
   • Daily totals grouped by converted UTC dates.
4. Convert cents to dollars and sum totals.
5. Call fillMissingDateData to ensure the by_date array contains every day between start and end.
6. Return the composed summary including a total property (new + renewing).

Key Business Rules:

• Aggregations always filter by stripe_user_id from the connected account.
• Revenue uses the first price item in items.data; multi-price subscriptions inherit that unit amount.

Error Handling:

• Relies on Promise.all and lets aggregation errors bubble up; controller wraps.

Example Usage:

const summary = await overviewService.salesOverview({
  startDate: '2025-09-01',
  endDate: '2025-09-30',
  stripeBilling,
});

Side Effects: None (read-only).

---

salesByProduct({ limit = 25, next, stripeBilling })

Purpose: Ranks products by sales count and revenue, enriching missing Stripe metadata.

Parameters:

• limit (Number) – Page size for product groups.
• next (ObjectId string) – Mongo _id cursor for pagination.
• stripeBilling (Object) – Connected account context.

Returns: Promise<{ sales_by_product: Array, has_more, next }>

Business Logic Flow:

1. Aggregate active subscriptions grouped by items.data.price.product.
2. Sort descending by _id and facet into data and total buckets to support pagination metadata.
3. For each grouped product:
   • Attempt to fetch cached metadata from billing.products.
   • Fallback to live stripe.products.retrieve call and upsert the cache.
   • Record unit amounts and currencies per price tier.
4. Determine has_more and next cursor using aggregated total vs limit.

Key Business Rules:

• Uses $lt pagination on _id for deterministic paging.
• Caches Stripe product names to minimise API calls.

Error Handling:

• Stripe errors are caught and logged (logger.error) but processing continues for other products.

Example Usage:

const page = await overviewService.salesByProduct({ limit: 10, stripeBilling });

Side Effects:

• ⚠️ Upserts billing.products when a product name is missing locally.
• ⚠️ Makes external Stripe API calls.

---

activeSubscriptionsByProduct({ limit = 25, next, stripeBilling })

Purpose: Mirrors salesByProduct but only counts subscriptions with status: 'active'.

Differences:

• Adds { status: 'active' } to the aggregation match.
• Returns payload under active_subscriptions_by_product.

Error Handling:

• Surrounds the entire method with try/catch; logs and rethrows errors to controller.

---

payouts({ firstPayoutId, lastPayoutId, limit = 25, stripeBilling })

Purpose: Lists Stripe payouts for a connected account with pagination cursors.

Parameters:

• firstPayoutId / lastPayoutId (String) – Stripe cursor IDs for reverse/forward pagination.
• limit (Number) – Page size.
• stripeBilling – Connected account context.

Returns: Promise<{ payouts, has_more, first_payout_id, last_payout_id }> with currency-normalised amounts.

Business Logic Flow:

1. Retrieve payouts via stripe.payouts.list, expanding destinations for bank metadata.
2. Map each payout to DTO with dollars, bank name, and card last4.
3. Derive pagination state (has_more, first_payout_id, last_payout_id).

Error Handling:

• Logs Stripe failures (payout fetch) and rethrows to controller.

Side Effects:

• ⚠️ External Stripe API call.

---

grossVolume({ startDate, endDate, stripeBilling })

Purpose: Calculates gross charge volume for the selected period and prior window, including day-level series and growth rate.

Business Logic Flow:

1. Normalise the window and compute comparative period.
2. Build aggregation pipelines on billing.charges for:
   • Total volume & count for current period.
   • Daily breakdown for current period.
3. Run equivalent pipelines for the previous period.
4. Convert cents to dollars, format into chart-friendly arrays, and compute growth rate with zero-division guards.
5. Fill missing dates for both current and previous ranges using fillMissingDateData.

Key Business Rules:

• Growth rate rounds to one decimal and defaults to 0 when not finite.
• Uses timezone offset derived from startDate so daily buckets align with the account’s locale.

Example Usage:

const gross = await overviewService.grossVolume({ startDate, endDate, stripeBilling });
console.log(gross.current.total); // "12345.67"

Side Effects: None.

---

netVolume({ startDate, endDate, stripeBilling })

Purpose: Produces net revenue by subtracting disputes and refunds from gross totals.

Business Logic Flow:

1. Reuse charge aggregation structure but sum balance_transaction.net.
2. Aggregate disputes and refunds separately, multiplying dispute totals by -1 for offsets.
3. Merge day-level data to subtract disputes/refunds from charge totals per day.
4. Compute current vs previous totals and growth rate, guarding against zero denominators.
5. Gap-fill daily arrays via fillMissingDateData.

Key Business Rules:

• When previous net total is <= 0 and current is positive, growth defaults to 100.
• Sorted daily arrays ensure chart order even when dispute days insert new entries.

Side Effects: None.

---

successfulPayments({ startDate, endDate, stripeBilling })

Purpose: Counts paid charges for current vs prior period.

Returns: Totals and daily counts (not amounts), useful for conversion metrics.

Business Rules:

• Mirror logic of grossVolume but sum count not amounts.

---

disputeCount({ startDate, endDate, stripeBilling })

Purpose: Tallies Stripe disputes and their net losses for comparison periods.

Business Rules:

• Aggregates billing.disputes, summing the reduced balance transactions and multiplying by -1.
• Growth rate follows successful payments pattern.

---

spendPerCustomer({ startDate, endDate, stripeBilling })

Purpose: Calculates average spend per unique customer.

Business Logic Flow:

1. Aggregate charges to count distinct customer IDs and total amount.
2. Divide amount by customer count (guarding against zero) to compute average.
3. Provide day-level averages by dividing per-group amounts by per-group unique customers.

Edge Cases:

• When no customers exist, totals default to 0 to avoid NaN.

---

newCustomers({ startDate, endDate, stripeBilling })

Purpose: Counts customers created in the period.

Business Logic Flow:

1. Match billing.customers by created timestamp window.
2. Sum totals and produce daily counts.

Special Handling:

• Uses fillMissingDateData for chart consistency.

---

newSubscribers({ startDate, endDate, stripeBilling })

Purpose: Counts unique customers who started subscriptions within the period.

Business Logic Flow:

1. Match billing.subscriptions by created date.
2. $lookup into billing.customers to ensure the customer was also created during the same window.
3. Count distinct subscribers and produce daily counts.

Key Business Rules:

• Ensures the subscriber is new to the ecosystem (customer and subscription created in window).

---

highRiskPayments({ startDate, endDate, stripeBilling })

Purpose: Surfaces charges flagged by Stripe Radar as highest or elevated risk.

Business Logic Flow:

1. Filter billing.charges on outcome.risk_level values.
2. Aggregate totals and daily counts similar to disputes.

Use Cases:

• Alerts risk teams for manual review thresholds.

---

🔀 Integration Points

Internal Dependencies

• ../utils/stripe-config – Instantiates Stripe SDK per connected account.
• ../utils (fillMissingDateData) – Injects zero-value days between gaps.
• ../utils/stripe-config + BillingProduct – Hydrates product metadata when caching misses.
• shared/utilities/logger – Logs Stripe lookup failures during product cache fills.

External Services

• Stripe API
  • stripe.products.retrieve
  • stripe.payouts.list
  • Only invoked when local caches require enrichment or when listing payouts.

🧪 Edge Cases & Special Handling

Sparse Data Windows

• Condition: No documents within the date range.
• Handling: Aggregations return empty arrays; service defaults totals to 0 and fillMissingDateData outputs day entries with zero values.

Timezone Offsets

• Condition: Start date includes timezone offset.
• Handling: Converts offset to seconds to align $toDate conversions, preventing shifted day buckets.

Stripe Cache Misses

• Condition: billing.products lacks a product referenced by subscription data.
• Handling: Fetch from Stripe, upsert cache, continue without blocking remaining products.

⚠️ Important Notes

• 🚨 Large Aggregations: All pipelines project only required fields but still scan significant data sets. Keep indexes on stripe_user_id and date fields in sync.
• 💡 Pagination: salesByProduct and activeSubscriptionsByProduct rely on _id cursor paging; controllers must pass the last id returned.
• 🐛 Growth Rate Division: Growth metrics protect against division by zero yet can still spike to large values when prior period is tiny—surface as-is for transparency.

🔗 Related Documentation

• Billing Module Overview
• Customer Service
• Webhook