[Pro] MRR calculation doesn't normalize billing periods - annual subscriptions inflating monthly revenue

[ItsDoms]

Summary

The Monthly Recurring Revenue (MRR) calculation in WPSubscription Pro's stats dashboard incorrectly adds all subscription prices without normalizing for billing periods. This causes annual subscriptions to be counted at their full annual price instead of being divided by 12, resulting in significantly inflated MRR metrics.

Environment

• WPSubscription Pro: 1.16.1
• WPSubscription (Free): [Latest]
• WooCommerce: [Latest]
• WordPress: [Latest]

Expected Behavior

MRR should show the monthly equivalent of all active subscriptions:

• Monthly subscription at 1,000 kr → 1,000 kr MRR ✅
• Annual subscription at 12,000 kr → 1,000 kr MRR ✅
• Weekly subscription at 250 kr → 1,082.50 kr MRR ✅
• Daily subscription at 35 kr → 1,065.40 kr MRR ✅

Actual Behavior

MRR calculation sums all subscription prices regardless of billing period:

• Monthly subscription at 1,000 kr → 1,000 kr MRR ✅
• Annual subscription at 12,000 kr → 12,000 kr MRR ❌ (12x too high!)
• Weekly subscription at 250 kr → 250 kr MRR ❌ (4.3x too low!)
• Daily subscription at 35 kr → 35 kr MRR ❌ (30x too low!)

Before

After

Impact

• Critical for businesses with mixed billing periods
• Annual subscriptions inflate MRR by 12x
• Makes financial forecasting and reporting inaccurate
• ARR calculation is also incorrect (MRR × 12)
• Affects business decisions based on metrics

Example

Scenario: Store with 7 active subscriptions

• 5 monthly at 1,000 kr = 5,000 kr MRR ✅
• 2 annual at 12,000 kr = 2,000 kr MRR (should be)

Current calculation shows: 5,000 + 24,000 = 29,000 kr MRR ❌
Correct calculation should show: 5,000 + 2,000 = 7,000 kr MRR ✅

---

Root Cause

File: subscription-pro/includes/Admin/Stats.php

Lines 179-186 (in get_comprehensive_metrics() method):

// Get MRR (Monthly Recurring Revenue) - only active subscriptions, excluding trash
$mrr = $wpdb->get_var(
    "SELECT SUM(pm.meta_value) FROM {$wpdb->postmeta} pm
    INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
    WHERE p.post_type='subscrpt_order'
    AND p.post_status='active'
    AND pm.meta_key='_subscrpt_price'"
);
$metrics['mrr'] = floatval( $mrr );

Problem: Simply sums _subscrpt_price without checking _subscrpt_timing_option (billing period).

Same bug exists in:

• Lines 380-387 in get_revenue_metrics() method

---

Proposed Fix

Solution: Normalize all billing periods to monthly

The fix requires:

1. JOIN with _subscrpt_timing_option meta (billing period type: day/week/month/year)
2. JOIN with _subscrpt_timing_per meta (period multiplier: e.g., "2" for bi-weekly)
3. Calculate monthly equivalent based on period type

Implementation

Replace lines 179-186 with:

// Get MRR (Monthly Recurring Revenue) with proper billing period normalization
$active_subs = $wpdb->get_results(
    "SELECT
        p.ID,
        price.meta_value as price,
        timing.meta_value as timing_option,
        timing_per.meta_value as timing_per
    FROM {$wpdb->posts} p
    INNER JOIN {$wpdb->postmeta} price ON p.ID = price.post_id
    LEFT JOIN {$wpdb->postmeta} timing ON p.ID = timing.post_id AND timing.meta_key='_subscrpt_timing_option'
    LEFT JOIN {$wpdb->postmeta} timing_per ON p.ID = timing_per.post_id AND timing_per.meta_key='_subscrpt_timing_per'
    WHERE p.post_type='subscrpt_order'
    AND p.post_status='active'
    AND price.meta_key='_subscrpt_price'"
);

$mrr = 0;

foreach ( $active_subs as $sub ) {
    $price = floatval( $sub->price );
    $timing_option = $sub->timing_option ?: 'month';
    $timing_per = floatval( $sub->timing_per ) ?: 1;

    // Normalize to monthly based on billing period
    switch ( $timing_option ) {
        case 'day':
        case 'days':
            // Daily: multiply by 30.44 (average days per month)
            $monthly_value = ( $price * 30.44 ) / $timing_per;
            break;

        case 'week':
        case 'weeks':
            // Weekly: multiply by 4.33 (average weeks per month)
            $monthly_value = ( $price * 4.33 ) / $timing_per;
            break;

        case 'year':
        case 'years':
            // Annual: divide by 12
            $monthly_value = $price / ( 12 * $timing_per );
            break;

        case 'month':
        case 'months':
        default:
            // Already monthly
            $monthly_value = $price / $timing_per;
            break;
    }

    $mrr += $monthly_value;
}

$metrics['mrr'] = round( $mrr, 2 );

Apply the same fix to lines 380-387 in get_revenue_metrics() method.

---

Why These Conversion Factors?

• Days → Monthly: × 30.44 (365.25 / 12 = 30.4375 days per month on average)
• Weeks → Monthly: × 4.33 (52.18 / 12 = 4.348 weeks per month on average)
• Years → Monthly: ÷ 12 (12 months per year)
• Months → Monthly: No conversion needed

These are industry-standard conversion factors used in SaaS metrics.

---

Testing

Test Case 1: Annual Subscription

Setup:

• 1 active subscription
• Price: 12,000 kr
• Billing: Annual

Before fix: MRR = 12,000 kr ❌
After fix: MRR = 1,000 kr ✅

Test Case 2: Mixed Billing Periods

Setup:

• 5 monthly subscriptions at 1,000 kr each
• 2 annual subscriptions at 12,000 kr each

Before fix: MRR = 5,000 + 24,000 = 29,000 kr ❌
After fix: MRR = 5,000 + 2,000 = 7,000 kr ✅

Test Case 3: Weekly Subscription

Setup:

• 1 weekly subscription at 250 kr

Before fix: MRR = 250 kr ❌
After fix: MRR = 1,082.50 kr (250 × 4.33) ✅

---

Verification

I've tested this fix on a live site using an mu-plugin implementation:

• ✅ MRR calculation now correctly normalizes all billing periods
• ✅ ARR calculation (MRR × 12) is now accurate
• ✅ Stats dashboard shows realistic revenue metrics
• ✅ No performance impact (same number of queries)
• ✅ Backwards compatible with existing subscriptions

---

Additional Notes

Performance

The fix performs the same number of database queries (1 query), just retrieves additional meta fields. The normalization calculation happens in PHP, which is negligible for typical subscription counts (< 10,000).

Backwards Compatibility

The fix is fully backwards compatible:

• Uses LEFT JOIN for timing meta (handles missing data)
• Defaults to 'month' if timing_option is not set
• Defaults to 1 if timing_per is not set

Cache Invalidation

The existing cache invalidation hooks in the Stats class will work correctly with this fix.

---

Workaround (Temporary)

Users can deploy this mu-plugin until the fix is released:

File: wp-content/mu-plugins/wpsubscription-mrr-fix.php

<?php
/**
 * Plugin Name: WPSubscription MRR Fix
 * Description: Fixes Monthly Recurring Revenue calculation to normalize billing periods
 * Version: 1.0.0
 */

// [Full mu-plugin code available in the PR or upon request]

---

Related Issues

• Affects MRR display in Executive Summary dashboard
• Affects ARR calculation (Annual Recurring Revenue = MRR × 12)
• May affect Pro reports and exports that use MRR data

---

Priority: High - Affects financial reporting and business metrics

Tested on: WPSubscription Pro 1.16.1
Fix verified: ✅ Working on production site

Would be happy to provide the complete mu-plugin code or create a pull request if you provide access to the Pro repository. Let me know if you need any additional details or testing!