Earned Income Report

Overview

The Earned Income Report system tracks and calculates revenue earned from subscriptions, issue sales, and redemptions. It provides detailed breakdowns by region (US vs International), issue numbers, and subscription types.

Key Components

Data Structure

The report consists of several key data elements:

  1. Basic Report Information
    • Title
    • Date range (start/end dates)
    • Filters (SKUs, new/renewal, self/gift)
    • Issue ID (optional)
  2. Entry Types
    • Paid Copies
    • Earned Income
    • Average per Copy
    • Comped Copies
    • Free Reships
    • Total Copies
  3. Redemption Data
    • Number of Redemptions
    • Earned Income from Redemptions
    • Breakdown by Gift vs Non-Gift
  4. Expired Monthly Data
    • Total Earned Income from Expired Monthlies
    • Count of Expired Subscriptions

Database Tables

The system uses four main tables:

  1. jc_earned_income_reports - Main report metadata
  2. jc_earned_income_report_entries - Individual report entries
  3. jc_earned_income_report_redemptions - Redemption tracking
  4. jc_earned_income_report_expired_monthlies - Expired monthly subscription data

Functionality

Report Generation Process

  1. Creates a new report record with basic metadata
  2. Processes redemptions if applicable
  3. Processes expired monthly earnings if applicable
  4. Processes shipping batches sequentially:
    • Handles initial shipping list
    • Processes reships
    • Updates totals and averages

Regional Breakdowns

Reports separate data into:

  • US (includes US territories)
  • International
  • Combined totals

Report Output

Generates a CSV file containing:

  1. Report metadata and filters
  2. Redemption summary (if applicable)
  3. Expired monthlies summary (if applicable)
  4. Issue-by-issue summary
  5. Detailed breakdown by region and SKU

Known Limitations

  1. Country codes for redemptions are currently hardcoded to ‘US’
  2. Processing is done in batches of 50 items
  3. Requires ‘customer_screens’ capability to generate/download reports

Database Schema Note

Tables are created with WordPress dbDelta to ensure proper schema management and updates.