Revenue Analytics & Reporting

Source: internal/api/v1/store/Controllers/reporting.js

Overview

The Reporting controller provides comprehensive revenue analytics for the DashClicks Store platform. It aggregates data from invoices, orders, subscriptions, disputes, refunds, and customer accounts to generate detailed sales and revenue metrics with period-over-period comparisons.

Key Capabilities

• Revenue Metrics: Gross/net volume, MRR calculations, dispute/refund tracking
• Sales Analytics: Product-level breakdown, active subscriptions by product
• Customer Metrics: New customers/subscribers, spend per customer
• Time-Series Data: Daily breakdowns with timezone support
• Period Comparisons: Current vs previous period analysis (date range-based)

---

MongoDB Collections

Collection	Operations	Purpose
_store.orders	Read	Revenue from orders (new/renewing)
_store.invoices	Read	Gross volume, payment tracking
_store.disputes	Read	Dispute volume and count
_store.refunds	Read	Refund volume and count
_accounts	Read	Customer acquisition metrics
_store.prices	Read	Product pricing information
_store.products	Read	Product catalog
_store.subscriptions	Read	Active subscription filtering

---

Service Methods

getSalesOverview

Generates comprehensive sales and revenue analytics with 17 parallel aggregations for current and previous periods.

Endpoint: GET /store/reporting/sales-overview

Query Parameters:

{
  filters: {
    start_date: string,  // ISO date string
    end_date: string     // ISO date string
  }
}
// If filters not provided, defaults to last 30 days

Response Structure:

{
  success: true,
  message: "SUCCESS",
  data: {
    overview: {
      renewing: number,              // Revenue from recurring orders
      new: number,                   // Revenue from new orders
      total: number,                 // Combined revenue
      by_date: [{ date, total }]     // Daily breakdown
    },
    sales_by_product: [{
      product: { name, ... },
      price: { nickname, ... },
      total: number,
      sales_count: number
    }],
    active_subscriptions_by_product: [...],
    gross_volume: {
      current: { total, by_date },
      previous: { total, by_date }
    },
    net_volume: {                    // Gross - disputes - refunds
      current: { total, by_date },
      previous: { total, by_date }
    },
    successful_payments: {
      current: { total, by_date },
      previous: { total, by_date }
    },
    dispute_count: {
      current: { total, by_date },
      previous: { total, by_date }
    },
    refund_count: {
      current: { total, by_date },
      previous: { total, by_date }
    },
    spend_per_customer: {
      current: { total, by_date },
      previous: { total, by_date }
    },
    new_customers: {
      current: { total, by_date },
      previous: { total, by_date }
    },
    new_subscribers: {
      current: { total, by_date },
      previous: { total, by_date }
    }
  }
}

---

Business Logic Flows

Period Calculation Logic

graph TD
    A[Receive Date Range] --> B{Filters Provided?}
    B -->|No| C[Default: Last 30 Days]
    B -->|Yes| D[Use Provided Range]
    C --> E[Calculate Days Count]
    D --> E
    E --> F[Create Second Period]
    F -->|Same Duration| G[Previous Period Start/End]
    G --> H[Execute Parallel Aggregations]

Date Range Logic:

• Current Period: start_date to end_date (or last 30 days)
• Previous Period: Same duration, shifted backwards
• Example: If current is Jan 1-30, previous is Dec 2-31 (30 days prior)

17 Parallel Aggregations (Current Period)

The method executes 17 MongoDB aggregations in parallel using Promise.all():

1. Renewing Revenue Total

// Orders created BEFORE period but updated DURING period
// With paid invoices during the period
Order.aggregate([
  {
    $match: {
      seller_account,
      created_at: { $lt: start },
      updated_at: { $gte: start, $lte: end },
      type: 'recurring',
    },
  },
  {
    $lookup: {
      /* Join latest_invoice with paid filter */
    },
  },
  { $group: { _id: 'renewing', total: { $sum: '$latest_invoice.amount_paid' } } },
]);

2. New Revenue Total

// Orders created DURING period
Order.aggregate([
  {
    $match: {
      seller_account,
      created_at: { $gte: start, $lte: end },
    },
  },
  {
    $lookup: {
      /* Join latest_invoice */
    },
  },
  { $group: { _id: 'new', total: { $sum: '$latest_invoice.amount_paid' } } },
]);

3. Revenue By Date

// Daily breakdown of all revenue in period
Order.aggregate([
  {
    $match: {
      /* Orders with paid invoices in period */
    },
  },
  {
    $group: {
      _id: { month, day, year }, // Using account timezone
      total: { $sum: '$latest_invoice.amount_paid' },
    },
  },
  { $sort: { '_id.year': 1, '_id.month': 1, '_id.day': 1 } },
]);

4. Sales By Product

// Product-level sales breakdown for period
Order.aggregate([
  {
    $match: {
      /* New orders in period with paid invoices */
    },
  },
  {
    $lookup: {
      /* Join prices */
    },
  },
  {
    $lookup: {
      /* Join products */
    },
  },
  {
    $group: {
      _id: { product, price },
      total: { $sum: '$latest_invoice.amount_paid' },
      sales_count: { $sum: 1 },
    },
  },
]);

5. Active Subscriptions By Product

// Currently active subscriptions (not time-filtered)
Order.aggregate([
  { $match: { type: 'recurring', seller_account } },
  {
    $lookup: {
      /* Join subscriptions WHERE status = 'active' */
    },
  },
  {
    $group: {
      _id: { product, price },
      total: { $sum: '$latest_invoice.amount_paid' },
      sales_count: { $sum: 1 },
    },
  },
]);

6. Gross Volume Total

// Total paid invoice amount in period
Invoice.aggregate([
  {
    $match: {
      account,
      connected_account,
      'status_transitions.paid_at': { $gte: start_unix, $lte: end_unix },
      paid: true,
    },
  },
  {
    $group: {
      _id: null,
      total: { $sum: '$amount_paid' },
      count: { $sum: 1 },
    },
  },
]);

7. Gross Volume By Date

// Daily invoice payment breakdown
Invoice.aggregate([
  {
    $match: {
      /* Paid invoices in period */
    },
  },
  {
    $group: {
      _id: { month, day, year }, // From status_transitions.paid_at
      total: { $sum: '$amount_paid' },
      count: { $sum: 1 },
    },
  },
  { $sort: { '_id.year': 1, '_id.month': 1, '_id.day': 1 } },
]);

8. Dispute Volume Total

// Total dispute amount in period (net of balance transactions)
Dispute.aggregate([
  { $match: { account, connected_account, created_at: { $gte: start, $lte: end } } },
  {
    $group: {
      _id: null,
      total: {
        $sum: {
          $reduce: {
            /* Sum balance_transactions.net */
          },
        },
      },
      count: { $sum: 1 },
    },
  },
  { $addFields: { total: { $multiply: ['$total', -1] } } }, // Make positive
]);

9. Dispute Volume By Date

Similar to #8 but grouped by day

10. Refund Volume Total

// Total refund amount in period
Refund.aggregate([
  { $match: { requestee, completed_on: { $gte: start, $lte: end } } },
  {
    $group: {
      _id: null,
      total: {
        $sum: {
          $reduce: {
            /* Sum lines.refund_amount */
          },
        },
      },
      count: { $sum: 1 },
    },
  },
]);

11. Refund Volume By Date

Similar to #10 but grouped by day

12. Spend Per Customer Total

// Average revenue per unique customer
Invoice.aggregate([
  {
    $match: {
      /* Paid invoices in period */
    },
  },
  {
    $group: {
      _id: null,
      customers: { $addToSet: '$customer' }, // Unique customers
      amount: { $sum: '$amount_paid' },
    },
  },
  {
    $project: {
      total: { $divide: ['$amount', { $size: '$customers' }] }, // Average
    },
  },
]);

13. Spend Per Customer By Date

Similar to #12 but grouped by day with daily averages

14. New Customers Total

// Accounts that became customers in period
Account.aggregate([
  {
    $match: {
      parent_account,
      became_customer_on: { $gte: start, $lte: end },
    },
  },
  { $group: { _id: null, total: { $sum: 1 } } },
]);

15. New Customers By Date

Similar to #14 but grouped by day

16. New Subscribers Total

// Accounts that became subscribers in period
Account.aggregate([
  {
    $match: {
      parent_account,
      became_subscriber_on: { $gte: start, $lte: end },
    },
  },
  { $group: { _id: null, total: { $sum: 1 } } },
]);

17. New Subscribers By Date

Similar to #16 but grouped by day

Previous Period Aggregations

The same 12 aggregations (6-17) are run for the previous period with shifted dates, enabling period-over-period comparison.

Net Volume Calculation

Net volume is calculated post-aggregation by combining gross volume, disputes, and refunds:

// For each day in period
net_volume = {
  current: {
    total: gross_volume.total - dispute_total - refund_total,
    by_date: gross_volume_by_date.map(day => day - disputes_on_day - refunds_on_day).sort(),
  },
  previous: {
    /* Same calculation for previous period */
  },
};

Calculation Steps:

1. Copy gross volume by date arrays
2. Subtract dispute amounts from matching dates
3. Subtract refund amounts from matching dates
4. Add new days if disputes/refunds exist without gross volume
5. Sort chronologically

---

Timezone Handling

All date groupings respect the account's timezone:

const timezone = req.auth.account.timezone
  || req.auth.default_config.timezone
  || 'CST';

// MongoDB date operators with timezone
{
  $group: {
    _id: {
      month: { $month: { date: '$created_at', timezone: timezone }},
      day: { $dayOfMonth: { date: '$created_at', timezone: timezone }},
      year: { $year: { date: '$created_at', timezone: timezone }}
    }
  }
}

Supported Formats:

• created_at: JavaScript Date objects
• status_transitions.paid_at: Unix timestamps (seconds) - converted with $multiply: [field, 1000]
• Output dates: YYYY-MM-DD format via moment()

---

Edge Cases & Business Rules

1. Order Revenue Attribution

• Renewing: Orders created before period but updated during period (renewal charges)
• New: Orders created during period (first-time purchases)
• Both must have latest_invoice.paid = true and paid_at within period

2. Active Subscriptions vs Sales

• sales_by_product: Filtered by creation date (new sales in period)
• active_subscriptions_by_product: Not time-filtered (current snapshot)

3. Multi-Tenant Filtering

• Orders: Match seller_account (agency selling) and buyer_account/buyer_customer
• Invoices: Match account (owner) and connected_account
• Disputes/Refunds: Match both account and connected_account

4. Dispute Amount Calculation

// Disputes have negative net amounts in Stripe
// Must multiply by -1 to show as positive loss
total: {
  $multiply: [{ $sum: balance_transactions.net }, -1];
}

5. Division By Zero Protection

Spend per customer handles zero customers:

{
  $divide: ['$amount', { $size: '$customers' }];
}
// Returns null if customers array is empty

6. Missing Data Handling

salesSummary[0][0]?.total || 0; // Default to 0 if no results

7. Lookup Filters in Aggregation

Uses let and $expr for complex joins:

{
  $lookup: {
    from: '_store.invoices',
    as: 'latest_invoice',
    let: { id: '$latest_invoice' },
    pipeline: [{
      $match: {
        $expr: {
          $and: [
            { $eq: ['$_id', '$$id'] },
            { $eq: ['$paid', true] },
            { $gte: ['$status_transitions.paid_at', start.unix()] }
          ]
        }
      }
    }]
  }
}

---

Performance Considerations

Optimization Strategies

1. Parallel Execution: 34 total aggregations (17 current + 17 previous) run in parallel

2. Index Usage: Requires indexes on:

   • _store.orders: seller_account, created_at, updated_at, type
   • _store.invoices: account, connected_account, status_transitions.paid_at, paid
   • _store.disputes: account, connected_account, created_at
   • _store.refunds: requestee, completed_on
   • _accounts: parent_account, became_customer_on, became_subscriber_on

3. Memory Usage: Large date ranges (1+ years) can produce 365+ data points per metric

4. Execution Time: Typically 2-5 seconds for 30-day periods with 1000+ orders

Recommended Limits

• Date Range: 90 days maximum for optimal performance
• Pagination: Not implemented - returns all data for period
• Caching: Consider caching results for 1+ hour for frequently accessed periods

---

Important Notes

Revenue Calculation Nuances

1. MRR Calculation: Not directly provided - calculated from active_subscriptions_by_product
2. Churn: Not directly calculated - inferred from subscriber count changes
3. Application Fees: Not deducted from gross volume (shows total payment amounts)
4. Currency: All amounts in cents (Stripe standard)

Date Comparison Logic

• Previous Period: Always same duration as current period
• Example:
  • Current: Jan 15 - Feb 14 (31 days)
  • Previous: Dec 15 - Jan 14 (31 days prior)

Timezone Impact

All date groupings use account timezone, which affects:

• Daily breakdowns (same Unix timestamp can be different days)
• Period boundaries (midnight varies by timezone)
• Comparison accuracy (daylight saving time transitions)

Connected Account vs Platform

• Platform invoices: connected_account = 'platform'
• Connected account invoices: connected_account = stripe_connected_account
• Analytics always scoped to authenticated account

---

Related Documentation

• Order Management - Order lifecycle and revenue tracking
• Subscriptions - Recurring revenue management
• Invoices - Invoice payment tracking
• Webhooks - Payment event synchronization
• Disputes - Chargeback management
• Refunds - Refund processing