Skip to main content

💰 Affiliate Commission Calculation

📖 Overview​

The Affiliate Commission Calculation module is a revenue-critical system that calculates monthly commissions for affiliate accounts based on their referred accounts' software and managed subscription payments. It runs on the 1st of each month, aggregating invoice data, calculating tiered commissions, and creating payout records with full transaction tracking.

Cron Schedule: Monthly on 1st day at 10:00 AM

Source Files:

  • Cron: queue-manager/crons/affiliates/commissions.js
  • Service: queue-manager/services/affiliates/commissions.js (~40 lines)
  • Queue: queue-manager/queues/affiliates/commissions.js (~450 lines - COMPLEX)

🎯 Business Purpose​

Powers the affiliate referral program by:

  • Revenue Sharing: Automatically calculates commissions for affiliates
  • Tiered Commissions: Different rates for software vs managed subscriptions
  • Monthly Payouts: Processes previous month's earnings on 1st of month
  • Audit Trail: Complete tracking of invoices and commission calculations
  • Stripe Integration: Prepares data for Stripe payout processing
  • Partner Incentivization: Motivates partners to refer quality customers

💵 Commission Structure​

COMMISSION_RATE = {
  SOFTWARE: 0.2, // 20% on software subscriptions
  MANAGED_SUBSCRIPTIONS: 0.1, // 10% on managed services
};

Product Categories:

  • Software: Platform subscriptions, CRM, analytics tools
  • Managed Subscriptions: SEO, PPC, social media management (excludes sites and listings)

🔄 Complete Processing Flow​

sequenceDiagram
    participant CRON as Monthly Cron (1st @ 10AM)
    participant SERVICE as Commissions Service
    participant DB as MongoDB
    participant QUEUE as Bull Queue
    participant PROCESSOR as Queue Processor
    participant STRIPE_PRICES as Store Prices
    participant INVOICES as Store Invoices
    participant PAYOUTS as Payout Models

    CRON->>SERVICE: Execute on 1st of month
    SERVICE->>DB: Aggregate all accounts with referrals
    DB-->>SERVICE: List of affiliate accounts

    loop For each affiliate
        SERVICE->>QUEUE: Add commission calc job
    end

    QUEUE->>PROCESSOR: Process affiliate job

    PROCESSOR->>DB: Find referred accounts
    PROCESSOR->>STRIPE_PRICES: Get software price IDs
    PROCESSOR->>STRIPE_PRICES: Get managed service price IDs
    PROCESSOR->>INVOICES: Aggregate software invoices (last month)
    PROCESSOR->>INVOICES: Aggregate managed invoices (last month)

    PROCESSOR->>PROCESSOR: Calculate software commission (20%)
    PROCESSOR->>PROCESSOR: Calculate managed commission (10%)
    PROCESSOR->>PROCESSOR: Sum total commission

    alt Commission > 0
        PROCESSOR->>PAYOUTS: Create Payout document (transaction)
        PROCESSOR->>PAYOUTS: Create PayoutAccount records (transaction)
        PAYOUTS-->>PROCESSOR: Payout saved
    else No Commission
        PROCESSOR->>PROCESSOR: Skip payout creation
    end

🔧 Service Layer​

services/affiliates/commissions.js​

Purpose: Identifies all affiliate accounts and creates a queue job for each.

const AccountModel = require('../../models/account');

module.exports = async Queue => {
  try {
    // Step 1: Aggregate all unique affiliates
    const accounts = await AccountModel.aggregate([
      {
        $match: {
          referred_by: { $ne: null },
        },
      },
      {
        $group: {
          _id: null,
          affiliates: { $addToSet: '$referred_by' },
        },
      },
    ]);

    const affiliates = accounts?.[0]?.affiliates || [];

    // Step 2: Create queue job for each affiliate
    for (let affiliate of affiliates) {
      await Queue.add(
        {
          account: affiliate.toString(),
        },
        {
          attempts: 10, // High retry count for reliability
          backoff: 'exponential', // Exponential backoff
          delay: 60000, // 1 minute delay
        },
      );
    }
  } catch (err) {
    console.log(err.message, err.stack);
  }
};

Step 1: Affiliate Aggregation​

const accounts = await AccountModel.aggregate([
  {
    $match: {
      referred_by: { $ne: null },
    },
  },
  {
    $group: {
      _id: null,
      affiliates: { $addToSet: '$referred_by' },
    },
  },
]);

Logic:

  • $match: Finds all accounts that were referred (has referred_by field)
  • $group: Groups all referrals and creates unique set of affiliate IDs
  • $addToSet: Ensures each affiliate appears only once

Example:

// Input: Accounts collection
[
  { _id: 'acc1', referred_by: 'affiliate123' },
  { _id: 'acc2', referred_by: 'affiliate123' },
  { _id: 'acc3', referred_by: 'affiliate456' },
  { _id: 'acc4', referred_by: null },
];

// Output: Unique affiliates
{
  affiliates: ['affiliate123', 'affiliate456'];
}

Step 2: Queue Job Creation​

for (let affiliate of affiliates) {
  await Queue.add(
    {
      account: affiliate.toString(),
    },
    {
      attempts: 10,
      backoff: 'exponential',
      delay: 60000,
    },
  );
}

Job Options:

  • attempts: 10: Critical revenue job - retry many times
  • backoff: 'exponential': 1min, 2min, 4min, 8min, 16min, 32min, 64min, 128min, 256min, 512min
  • delay: 60000: Start after 1 minute (spreads load)

💼 Queue Processor Logic​

Date Range Calculation​

let date = moment();
let today = date.clone().date();
let daterange;

if (today == 1) {
  daterange = {
    start: date.clone().subtract(1, 'M').set('date', 1).startOf('D'),
    end: date.clone().subtract(1, 'M').endOf('M'),
  };
} else {
  return done(); // Only run on 1st of month
}

Date Logic:

  • Only runs on day 1 of each month
  • Calculates previous month date range
  • Example (Oct 1, 2025):
    • Start: Sep 1, 2025 00:00:00
    • End: Sep 30, 2025 23:59:59

Find Referred Accounts​

let referrals = await Account.find(
  {
    referred_by: account,
  },
  '_id',
);

referrals = referrals.map(r => r._id);

Purpose: Get all accounts referred by this affiliate

Price ID Aggregation​

const [softwarePricesResult, managedSubscriptionsResult] = await Promise.all([
  // Software prices
  StorePrice.aggregate([
    {
      $match: {
        'metadata.software': 'true',
        platform_type: 'dashclicks',
        connected_account: 'platform',
      },
    },
    {
      $group: {
        _id: null,
        stripe_ids: { $addToSet: '$stripe_id' },
      },
    },
  ]),
  // Managed subscription prices
  StorePrice.aggregate([
    {
      $match: {
        'metadata.product_type': { $in: filteredManagedSubs },
        platform_type: 'dashclicks',
        connected_account: 'platform',
      },
    },
    {
      $group: {
        _id: null,
        stripe_ids: { $addToSet: '$stripe_id' },
      },
    },
  ]),
]);

const softwarePrices = softwarePricesResult[0]?.stripe_ids || [];
const managedSubscriptions = managedSubscriptionsResult[0]?.stripe_ids || [];

Logic:

  1. Parallel queries for efficiency
  2. Software: Matches metadata.software: 'true'
  3. Managed: Matches product types (SEO, PPC, etc.) excluding 'site' and 'listings'
  4. Returns: Array of Stripe price IDs

Example Output:

softwarePrices: ['price_abc123', 'price_def456'];
managedSubscriptions: ['price_ghi789', 'price_jkl012'];

Invoice Aggregation (Software)​

Invoice.aggregate([
  {
    $match: {
      $and: [
        {
          'status_transitions.paid_at': { $gte: daterange.start.unix() },
        },
        {
          'status_transitions.paid_at': { $lte: daterange.end.unix() },
        },
        {
          'lines.data': { $size: 1 }, // Single line item
        },
        {
          'lines.data': {
            $elemMatch: { 'price.id': { $in: softwarePrices } },
          },
        },
      ],
      status: 'paid',
      amount_paid: { $gt: 0 },
      subscription: { $ne: null },
    },
  },
  {
    $lookup: {
      from: '_store.subscriptions',
      as: 'subscription',
      localField: 'subscription',
      foreignField: 'stripe_id',
    },
  },
  {
    $unwind: '$subscription',
  },
  {
    $match: {
      'subscription.metadata.account_id': { $in: referrals },
    },
  },
  {
    $facet: {
      by_referral: [
        {
          $group: {
            _id: '$subscription.metadata.account_id',
            invoices: { $addToSet: '$_id' },
            total: {
              $sum: {
                $cond: [
                  {
                    $gt: ['$amount_paid', '$lines.data.price.unit_amount'],
                  },
                  '$lines.data.price.unit_amount',
                  '$amount_paid',
                ],
              },
            },
          },
        },
      ],
      total: [
        /* count */
      ],
      invoices: [
        /* all invoice IDs */
      ],
      software: [
        /* total revenue */
      ],
    },
  },
]);

Complex Aggregation Breakdown:

  1. $match Stage 1: Filter invoices

    • Paid within date range
    • Single line item (software subscriptions)
    • Matches software price IDs
    • Status: paid, amount > 0

  2. $lookup: Join subscription data

    • Links invoice to subscription record
    • Gets metadata with account_id

  3. $match Stage 2: Filter to referrals only

    • Only include invoices from referred accounts

  4. $facet: Multiple aggregations

    • by_referral: Group by referred account, sum amounts
    • total: Count of invoices
    • invoices: List all invoice IDs
    • software: Total software revenue

Amount Calculation Logic:

$cond: [
  { $gt: ['$amount_paid', '$lines.data.price.unit_amount'] },
  '$lines.data.price.unit_amount', // Use unit amount if amount_paid includes fees
  '$amount_paid', // Otherwise use amount_paid
];

This handles cases where amount_paid includes transaction fees or taxes.

Invoice Aggregation (Managed Subscriptions)​

Similar to software but with key differences:

{
    $match: {
        $and: [
            // ... date range filters ...
            {
                'lines.data': { $exists: true, $gt: { $size: 0 } },  // Any line items
            },
            {
                'lines.data': {
                    $elemMatch: { 'price.id': { $in: managedSubscriptions } },
                },
            },
        ],
        // ... other filters ...
    },
}

Key Difference: Managed invoices can have multiple line items (bundled services)

Complex Total Calculation:

total: {
    $sum: {
        $reduce: {
            input: { $ifNull: ['$lines.data', []] },
            initialValue: 0,
            in: {
                $cond: [
                    {
                        $and: [
                            // Only subscription line items (not setup fees)
                            { $eq: ['$$this.type', 'subscription'] },
                            // Only matching price IDs
                            { $in: ['$$this.price.id', managedSubscriptions] },
                        ],
                    },
                    {
                        $add: [
                            '$$value',
                            {
                                $subtract: [
                                    '$$this.amount',  // Line item amount
                                    {
                                        $sum: {
                                            $map: {
                                                input: {
                                                    $ifNull: ['$$this.discount_amounts', []],
                                                },
                                                as: 'discount',
                                                in: '$$discount.amount',
                                            },
                                        },
                                    },
                                ],
                            },
                        ],
                    },
                    '$$value',
                ],
            },
        },
    },
}

Logic:

  1. Loop through line items ($reduce)
  2. Filter: Only 'subscription' type items with matching price IDs
  3. Calculate: Line amount minus discount amounts
  4. Sum all matching line items

Commission Calculation​

const evaluateCommission = (total = 0, subs = 0, percentage = null) => {
  let commission = percentage;
  return Math.round(total * commission);
};

let sComm = evaluateCommission(
  softwareSubs[0]?.software?.[0]?.total,
  null,
  COMMISSION_RATE.SOFTWARE, // 0.20
);

let mComm = evaluateCommission(
  managedSubs[0]?.managed?.[0]?.total,
  null,
  COMMISSION_RATE.MANAGED_SUBSCRIPTIONS, // 0.10
);

let totalCommission = sComm + mComm;

Example:

  • Software revenue: $10,000 → Commission: $2,000 (20%)
  • Managed revenue: $5,000 → Commission: $500 (10%)
  • Total Commission: $2,500

Payout ID Generation​

const generateID = async () => {
  shortid.characters('0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ-_');
  let id = shortid.generate();
  let hasPayout = await Payout.findOne({ ref: id });

  // Regenerate if exists or starts with _ or -
  while (['_', '-'].includes(id[0]) || hasPayout) {
    id = shortid.generate();
    hasPayout = await Payout.countDocuments({ ref: id });
  }
  return id;
};

Purpose: Generate unique, URL-safe payout reference ID

Payout Document Creation​

let payoutPayload = {
  account,
  amount: {
    total: totalCommission,
    software: sComm,
    managed_subscriptions: mComm,
    net: (softwareSubs[0]?.software?.[0]?.total || 0) + (managedSubs[0]?.managed?.[0]?.total || 0),
    gross:
      (softwareSubs[0]?.software?.[0]?.total || 0) + (managedSubs[0]?.managed?.[0]?.total || 0),
  },
  cutoff_date,
  invoices: allInvoices,
  commission_rate: {
    software: COMMISSION_RATE.SOFTWARE,
    managed_subscriptions: COMMISSION_RATE.MANAGED_SUBSCRIPTIONS,
  },
  ref: await generateID(),
};

if (payoutPayload.amount.total) payout = await new Payout(payoutPayload).save({ session });

Transaction Safety: Uses MongoDB session for atomic operations

PayoutAccount Creation (Per Referral)​

let payoutAccountsMap = new Map();

// Phase 1: Process software referrals
for (let r of softwareSubs[0]?.by_referral || []) {
    let rsComm = evaluateCommission(r.total, null, COMMISSION_RATE.SOFTWARE);

    payoutAccountsMap.set(r._id.toString(), {
        payout: payout.id,
        account: r._id,
        amount: {
            total: rsComm,
            software: rsComm,
            managed_subscriptions: 0,
        },
        metadata: {
            type: 'software',
            commission_rate: { ... },
        },
        invoices: r.invoices,
    });
}

// Phase 2: Process managed referrals (merge with existing)
for (let r of managedSubs[0]?.by_referral || []) {
    let rmComm = evaluateCommission(r.total, null, COMMISSION_RATE.MANAGED_SUBSCRIPTIONS);

    const accountKey = r._id.toString();

    if (payoutAccountsMap.has(accountKey)) {
        // Update existing (account has both software and managed)
        let existingPayoutData = payoutAccountsMap.get(accountKey);
        existingPayoutData.amount.total += rmComm;
        existingPayoutData.amount.managed_subscriptions = rmComm;
        existingPayoutData.invoices = [
            ...existingPayoutData.invoices,
            ...r.invoices,
        ];
        existingPayoutData.metadata.type = 'combined';
    } else {
        // Create new (managed only)
        payoutAccountsMap.set(accountKey, { ... });
    }
}

// Phase 3: Save all
const savePromises = Array.from(payoutAccountsMap.values()).map(payoutData =>
    new PayoutAccount(payoutData).save({ session }),
);

await Promise.all(savePromises);

Two-Phase Processing:

  1. Process software subscriptions
  2. Merge or create managed subscriptions
  3. Batch save all PayoutAccount records

Why Map?: Efficiently merges accounts with both software and managed subscriptions

Transaction Commit​

await session.commitTransaction();
logger.log({
  initiator: 'QM/affiliates/commissions',
  message: `Account ${account} settled on ${new Date()}`,
});
done();

Atomicity: All database operations succeed or fail together

📊 Data Structures​

Payout Document​

{
    _id: ObjectId,
    account: ObjectId('affiliate_account_id'),
    amount: {
        total: 2500,                // Total commission in cents
        software: 2000,             // Software commission
        managed_subscriptions: 500, // Managed commission
        net: 15000,                 // Total revenue (after discounts)
        gross: 15000                // Total revenue (before discounts)
    },
    cutoff_date: Date,              // Payment cutoff (10 AM on 1st)
    invoices: [ObjectId, ...],      // All invoice IDs included
    commission_rate: {
        software: 0.20,
        managed_subscriptions: 0.10
    },
    ref: 'abc123XYZ',               // Unique payout reference
    status: 'pending',              // pending, paid, failed
    createdAt: Date,
    updatedAt: Date
}

PayoutAccount Document​

{
    _id: ObjectId,
    payout: ObjectId('payout_id'),
    account: ObjectId('referred_account_id'),
    amount: {
        total: 250,
        software: 200,
        managed_subscriptions: 50
    },
    invoices: [ObjectId, ...],
    metadata: {
        type: 'combined',           // 'software', 'managed_subscription', 'combined'
        commission_rate: {
            software: 0.20,
            managed_subscriptions: 0.10
        }
    },
    createdAt: Date,
    updatedAt: Date
}

⚙️ Configuration​

Required Environment Variables​

# MongoDB
MONGO_DB_URL=mongodb://...

# Redis (Bull)
REDIS_HOST=localhost
REDIS_PORT=6379

Commission Rate Configuration​

Located in utilities/constants.js:

COMMISSION_RATE: {
    SOFTWARE: 0.20,                // 20%
    MANAGED_SUBSCRIPTIONS: 0.10    // 10%
}

MANAGED_SUBSCRIPTIONS: [
    'seo', 'ppc', 'social', 'content',
    // Excludes: 'site', 'listings'
]

🚨 Error Handling​

Service Level​

try {
  // Service logic
} catch (err) {
  console.log(err.message, err.stack);
}

No retry - will try again next month

Processor Level​

try {
  // Commission calculation
  payout = await new Payout(payoutPayload).save({ session });
} catch (err) {
  await session.abortTransaction();
  logger.error({ initiator: 'QM/affiliates/commissions', error: err });
  return done(err); // Job fails, will retry
}

Transaction Rollback: Ensures data consistency on failures

📈 Performance Considerations​

Optimization Strategies​

  1. Parallel Price Queries: Software and managed prices fetched concurrently
  2. Parallel Invoice Aggregations: Two complex aggregations run simultaneously
  3. Map for Merging: Efficient O(1) lookup for combining referrals
  4. Batch Saves: All PayoutAccount records saved in parallel

Scalability​

  • Per-Affiliate Jobs: Each affiliate processed independently
  • Exponential Backoff: Spreads retry load over hours
  • Transaction Isolation: No lock contention between affiliates
  • Index Requirements:
    • Account.referred_by
    • Invoice.status_transitions.paid_at
    • StorePrice.metadata.software

Typical Performance​

  • Service execution: 5-10 seconds (100 affiliates)
  • Per-affiliate job: 30-60 seconds (complex aggregations)
  • Total monthly processing: 1-2 hours (100 affiliates)

🔗 Related Documentation​

📝 Notes​

Why Run on 1st?​

  • Gives time for invoices to settle
  • Aligns with monthly accounting cycles
  • 10 AM cutoff ensures previous day's invoices included

Commission Tiers​

  • Software (20%): Higher margin products
  • Managed (10%): Labor-intensive services

Stripe Payout Integration​

This creates payout records. Separate process:

  1. Reviews payouts
  2. Creates Stripe payouts
  3. Updates payout status

Complexity: Very High (450+ lines, complex aggregations)
Business Impact: CRITICAL - Revenue distribution
Dependencies: Stripe, MongoDB transactions, Bull queues
Last Updated: 2025-10-10

💬

Documentation Assistant

Ask me anything about the docs

Hi! I'm your documentation assistant. Ask me anything about the docs!

I can help you with:
- Code examples
- Configuration details
- Troubleshooting
- Best practices

Try asking: How do I configure the API?
09:31 AM