Account Churn Risk Processor

Overview

The Account Churn Risk processor identifies accounts at high risk of churning by analyzing subscription cancellation patterns over the past 30 days. When 50% or more of an account's subscription value is canceled, a churn risk pulse is created for proactive retention efforts.

Source File: queue-manager/services/projects/accountChurnRisk.js
Queue File: queue-manager/queues/projects/accountChurn.js
Execution: Part of projects cron (every 5 minutes)
Business Impact: CRITICAL - Customer retention

Processing Flow

sequenceDiagram
    participant CRON as Projects Cron
    participant SVC as Churn Service
    participant SUB as Subscriptions
    participant PULSE as Projects Pulse
    participant QUEUE as Churn Queue

    CRON->>SVC: calculateChurnRisk()
    SVC->>SUB: Aggregate canceled subscriptions<br/>(last 30 days)
    SUB-->>SVC: Cancellation data by account

    loop For Each Account
        SVC->>SVC: Calculate risk ratio<br/>(canceled / total)

        alt Risk >= 50%
            SVC->>PULSE: Check existing pulse

            alt No Recent Pulse
                SVC->>QUEUE: Add to queue
                QUEUE->>PULSE: Create pulse
            end
        end
    end

Key Logic

Churn Risk Calculation

churnRisk = canceledAmount / totalSubscriptionValue;

// Example:
// Canceled: $500
// Total: $1000
// Risk: 0.5 (50%)

// Threshold: >= 50% triggers pulse

Time Window

• Period: Last 30 days
• Canceled Field: Stripe timestamp (seconds)
• Current Time: Math.floor(Date.now() / 1000)

Subscription Filtering

Only managed subscriptions (excluding sites and listings):

MANAGED_SUBSCRIPTIONS.filter(sub => !['site', 'listings'].includes(sub));

MongoDB Aggregation Pipeline

Stage 1: Match Canceled Subscriptions

{
  $match: {
    status: 'canceled',
    canceled_at: { $gt: thirtyDaysAgo },
    'plan.metadata.product_type': { $in: MANAGED_SUBSCRIPTIONS }
  }
}

Stage 2: Project Key Fields

{
  $project: {
    main_acc: '$account',
    canceled_at: { $toDate: { $multiply: ['$canceled_at', 1000] } },
    amount: '$plan.amount'
  }
}

Stage 3: Group by Account

{
  $group: {
    _id: '$main_acc',
    total_caceled_amount: { $sum: '$amount' },
    canceled_subscriptions: { $push: '$_id' }
  }
}

Stage 4: Calculate Total Active Value

Uses $lookup to get active subscription costs for same account.

Stage 5: Calculate Risk Ratio

{
  $project: {
    account_id: '$_id',
    has_risk: {
      $divide: [
        '$total_caceled_amount',
        { $first: '$total.total_cost' }
      ]
    },
    canceled_subscriptions: 1
  }
}

Stage 6: Filter High Risk

{
  $match: {
    has_risk: {
      $gte: 0.5;
    }
  }
}

Stage 7: Check Existing Pulses

Prevents duplicate pulses within 31 days.

Pulse Structure

{
  account_id: ObjectId,
  type: 'account_churn_risk',
  status: 'pending',
  metadata: {
    risk_ratio: 0.65,
    canceled_amount: 50000,
    total_amount: 77000,
    canceled_subscriptions: [...]
  }
}

Collections Used

Input: store-subscription

• Queries canceled subscriptions in last 30 days
• Queries active subscriptions for total value

Check: projects-pulse

• Prevents duplicate pulses
• Checks for recent (31 days) churn risk pulses

Output: projects-pulse (via queue)

• Creates new churn risk pulses

Error Handling

try {
  // Aggregation and queue processing
} catch (error) {
  logger.error({
    initiator: 'QM/projects/accountChurnRisk',
    error: error,
  });
  throw error;
}

Performance

Execution Time: 2-5 seconds
Frequency: Every 5 minutes
Typical Results: 0-20 accounts per run

---

Complexity: MEDIUM
Lines of Code: 210