Household Profitability Analysis
Using SQL and Tableau to identify high-value customer segments and uncover cross-selling opportunities within an insurance agency.
Tools Used
BUSINESS PROBLEM
The agency wanted to better understand which customer segments generated the greatest commission revenue and identify opportunities to increase customer lifetime value through cross-selling.
Specifically:
-
Which household segments are most profitable?
-
How does bundling impact commission revenue?
-
Should sales efforts prioritize bundled customers?
MY APPROACH
1
Gather and clean household profitability data
2
Analyze customer segments using SQL
3
Visualize findings in Tableau
4
Deliver business recommendations
SQL ANALYSIS
To identify which customer segments generated the highest commission revenue, I built a multi-step SQL query using Common Table Expressions (CTEs). The query first classified each household by the lines of business they held — auto, home, and umbrella — then assigned each household to a segment such as single-line auto, single-line home, bundled, or bundled with umbrella. I then aggregated total commission at the household level by combining agent and agency commission, calculated the average commission for each segment, and ranked the results from highest to lowest profitability.
-- Line of Business Bucket CTE
WITH household_lines AS
(
SELECT
household_id,
MAX(CASE WHEN line_of_business = 'Personal Auto' THEN 1 ELSE 0 END) AS has_auto,
MAX(CASE WHEN line_of_business = 'Home' THEN 1 ELSE 0 END) AS has_home,
MAX(CASE WHEN line_of_business = 'Umbrella' THEN 1 ELSE 0 END) AS has_umbrella
FROM `agency_db.policies`
GROUP BY household_id
),
-- Household Segment Bucket CTE
household_segments AS
(
SELECT
household_id,
CASE
WHEN has_auto = 1 AND has_home = 0 THEN 'single_auto'
WHEN has_home = 1 AND has_auto = 0 THEN 'single_home'
WHEN has_auto = 1 AND has_home = 1 AND has_umbrella = 1 THEN 'bundled_with_umbrella'
WHEN has_auto = 1 AND has_home = 1 THEN 'bundled'
ELSE 'other'
END AS household_segment
FROM household_lines
),
-- Commission Revenue CTE
household_commission AS
(
SELECT
household_id,
SUM(agent_commission + agency_commission) AS total_commission
FROM `agency_db.policies`
GROUP BY household_id
)
-- MAIN QUERY
SELECT
s.household_segment,
COUNT(*) AS num_households,
ROUND(AVG(c.total_commission), 2) AS avg_commission
FROM household_segments AS s
JOIN household_commission AS c
ON s.household_id = c.household_id
GROUP BY s.household_segment
ORDER BY avg_commission DESC
SQL QUERY RESULTS
The query returned four household segments ranked by average commission.
Key Finding: Bundled + Umbrella households generated the highest average commission, while bundled households overall significantly outperformed single-line customers. Bundled customers generated approximately 2.2x more commissionthan single-line auto customers.
household_segment | num_households | avg_commission |
|---|---|---|
bundled_with_umbrella | 166 | 840.61 |
bundled | 1377 | 790.58 |
single_home | 150 | 444.56 |
single_auto | 652 | 387.13 |
KEY FINDINGS
-
Bundled + Umbrella households generated the highest average commission at $840.61.
-
Bundled households generated 2.2x more commission than single-line auto customers.
-
Households with multiple policies consistently outperformed single-line customers in profitability.
-
The results highlight a strong opportunity to increase revenue through cross-selling and policy bundling.
INTERACTIVE TABLEAU DASHBOARD
The dashboard allows users to explore household profitability across customer segments and compare commission performance visually.
NOTE: If the Tableau dashboard does not load properly, please click here to view it directly in Tableau Public.
BUSINESS RECOMMENDATIONS
The dashboard allows users to explore household profitability across customer segments and compare commission performance visually.
1
Prioritize bundle-first sales strategies
Encourage producers to cross-sell home and auto policies to existing single-line customers.
2
Target high-value households
Develop campaigns focused on customers most likely to add umbrella coverage.
3
Track bundling conversion rates
Measure how effectively sales efforts convert single-line households into bundled relationships.
BUSINESS IMPACT
This analysis demonstrated that bundled customer relationships are significantly more valuable than single-line policies from a commission perspective. The findings support a bundle-first sales strategy, where cross-selling home and umbrella coverage to existing customers can increase average household profitability and strengthen long-term customer relationships.