Carrier Churn Analysis

-- Churn Risk Analysis by Premium Increase Bucket (2024)

-- Analyzes renewal outcomes for all policies renewed in 2024

-- Categorizes each renewal into a premium increase bucket (Decrease, 0-10%, 10-20%, 20%+)

-- and calculates churn rate (Cancelled + Non-Renewed) per bucket

-- Results include both a per-carrier breakdown and an overall all-carriers rollup

-- Carrier names are masked using carrier_id to protect proprietary information

-- Source tables: agency_db.renewals

 

WITH renewal_buckets AS

(

SELECT

  r.outcome,

  r.carrier_id,

CASE

  WHEN r.pct_increase < 0 THEN '1. Decrease'

  WHEN r.pct_increase < 0.10 THEN '2. 0-10%'

  WHEN r.pct_increase < 0.20 THEN '3. 10-20%'

  ELSE '4. 20%+'

  END AS premium_increase_bucket

FROM `agency_db.renewals` AS r

WHERE EXTRACT(YEAR FROM r.renewal_date) = 2024

)

 

-- Per-carrier breakdown

SELECT

  CONCAT('Carrier ', CAST(carrier_id AS STRING)) AS carrier_label,

  premium_increase_bucket,

  COUNT(*) AS total_renewals,

  SUM(CASE WHEN outcome IN ('Cancelled','Non-Renewed') THEN 1 ELSE 0 END) AS churned_count,

  ROUND(SUM(CASE WHEN outcome IN ('Cancelled','Non-Renewed') THEN 1 ELSE 0 END) * 100.0 /     COUNT(*), 1) AS churn_rate_pct

FROM renewal_buckets

GROUP BY carrier_id, premium_increase_bucket

 

UNION ALL

 

-- Overall (all carriers combined)

SELECT

  'All Carriers' AS carrier_label,

  premium_increase_bucket,

  COUNT(*) AS total_renewals,

  SUM(CASE WHEN outcome IN ('Cancelled','Non-Renewed') THEN 1 ELSE 0 END) AS churned_count,

  ROUND(SUM(CASE WHEN outcome IN ('Cancelled','Non-Renewed') THEN 1 ELSE 0 END) * 100.0 /     COUNT(*), 1) AS churn_rate_pct

FROM renewal_buckets

GROUP BY premium_increase_bucket

 

ORDER BY carrier_label, premium_increase_bucket;