📅 Anchor Date & Time Windows

• Flexible Anchoring: Set any date as your reference point (defaults to today). All renewal calculations, time windows, and metric cards update relative to this anchor date.
• Time Windows: Filter renewals by expiration timeline - 30 days (urgent), 60 days (high priority), 90 days (standard), 180 days (extended), or view all.
• Forward-Looking: Time windows apply forward from the anchor date. Example: If anchor = Dec 31, 2024 and window = 90 days, you see contracts expiring between Dec 31, 2024 and Mar 31, 2025.
• Historical Analysis: Set anchor to past date to see what renewals looked like at that point in time. Set anchor to future date for forward planning.

🔢 How Calculations Work

• DB Month: Months generated between subscription start and end dates to track ARR over time. We use the latest db_month to get current contract state.
• Days to Renewal: DATEDIFF('day', anchor_date, up_for_renewal_date). Negative = expired, 0-30 = urgent, 31-60 = high priority, etc.
• OBP-Specific Renewal Logic: For OBP contracts, renewal dates are calculated differently:
  • If subscription ends between 1st-15th: Renewal date = End date + 1 day
  • If subscription ends between 16th-end of month: Renewal date = 1st of next month
  This logic is automatically applied when viewing OBP contracts. Other products use standard logic (End date + 1 day).
• Customer Count: Distinct customers (id_source_system) with contracts up for renewal
• Contract Count: Distinct contracts (fin_invoice_id) up for renewal, deduplicated by customer + contract + product group + end date
• ARR Totals: Sum of unified_arr_annual across all product lines within each contract

✅ What We Include/Exclude

• ✓ Included: Active contracts (ARR > 0) with end dates not yet expired relative to anchor date
• ✓ Included: Contracts with status "Up for Renewal" (no renewal detected after end date)
• ✗ Excluded: Contracts already renewed (new contract found with same customer/product starting after end date)
• ✗ Excluded: Expired contracts (subscription_end < anchor_date)
• ✗ Excluded: Contracts with no ARR or missing end dates

📊 Metric Card Definitions

🎯 Multiple Filters Work Together

• Anchor Date + Time Window: Time window applies forward from your chosen anchor date
• Anchor Date + Product Group: See selected products' renewals at any point in time
• All Three Combined: Example: "Show OBP contracts expiring within 60 days from Dec 31, 2024"
• Independent Filters: Change any filter and the others are preserved - all update together seamlessly