Stanley's Data Analysis Portfolio

1. 🛠️ PostgreSQL Data Preparation: Structuring a Clean Call Center Dataset


-- Table Creation (DDL): Normalized Call Center Dataset
CREATE TABLE call_center (
    call_id TEXT PRIMARY KEY,
    date DATE,
    agent_first_name TEXT,
    agent_last_name TEXT,
    agent_rating NUMERIC(2,1),
    product_discussed TEXT,
    call_duration_minutes NUMERIC(5,2),
    call_outcome TEXT,
    customer_age INT,
    customer_gender TEXT,
    state TEXT,
    customer_income_bracket TEXT,
    time_of_day TEXT,
    follow_up_call_required TEXT,
    repeat_customer TEXT,
    reason_call_abandoned TEXT
);

📌 Key Preparation Highlights

Schema Clarity: Cleaned up redundant columns (e.g., removed duplicate Column1 mirroring agent_rating).
Data Typing: Used appropriate data types for accurate storage and querying: DATE for temporal data, NUMERIC for precision in ratings and durations.
Semantic Naming: Adopted consistent, self-explanatory field names to improve maintainability and readability for analysts and developers.
Primary Key Integrity: Ensured unique identification via call_id, enforcing record-level precision and supporting future joins.

🔍 Business Use Cases

📞 Call Performance Tracking: Measure agent productivity using call durations, outcomes, and ratings.
🧠 Customer Behavior Analysis: Segment customer patterns by gender, age, income, and repeat interactions.
⏱️ Operational Efficiency: Analyze time-of-day effects on call outcomes or abandonment rates.
📊 Predictive Modeling: Enable churn prediction or escalation likelihood using structured call metadata.
🧩 Dashboard Integration: Easily link with BI tools like Tableau or Power BI for real-time insights into customer service operations.

This PostgreSQL schema lays the groundwork for scalable customer service analytics by ensuring a clean, normalized, and semantically meaningful dataset. Whether analyzing agent efficiency, customer satisfaction, or service gaps, this foundational structure supports deep, actionable insights.

2. 🔍 Exploratory SQL Analysis: Total Calls & Success Rate


-- Total Calls and Success Rate Calculation
SELECT 
    COUNT(*) AS total_calls,
    COUNT(*) FILTER (WHERE call_outcome = 'Success') AS successful_calls,
    ROUND(
        COUNT(*) FILTER (WHERE call_outcome = 'Success') * 100.0 / COUNT(*), 
        2
    ) AS success_rate_pct
FROM call_center;

📊 Output Snapshot

Total Calls: 200,000
Successful Calls: 83,519
Success Rate: 41.79%

📌 Analytical Insights

Conversion Performance: Out of 200,000 total call records, only 41.79% ended in a successful outcome. This benchmark is critical for understanding customer service or sales call efficiency.
Success Rate Derivation: The query uses SQL’s FILTER clause to isolate only those calls marked with 'Success' and calculates the proportion relative to the total.
Rounded Accuracy: The result is rounded to two decimal places to maintain clean, readable KPI values suitable for dashboards and executive reports.

🔍 Business Use Cases

📞 Call Center Effectiveness: Measure how well the team converts calls into meaningful outcomes.
📈 Trend Benchmarking: Track improvements or declines in the success rate over time using time-windowed comparisons.
🎯 Goal Setting: Establish performance targets for agents based on historical baseline success rates.
🛠️ Root Cause Analysis: Use this figure as a starting point to drill deeper into what factors influence success or failure (e.g., time of day, agent rating, customer profile).

This high-level KPI provides a foundational view into the overall performance of customer-facing operations. By embedding it in dashboards or performance reports, business leaders can quickly assess operational success, prioritize training or process improvements, and enhance overall call strategy execution.

3. 👥 Top 5 Performing Agents: Ranked by Call Success Rate


-- Identify Top 5 Agents by Success Rate
SELECT 
    agent_first_name || ' ' || agent_last_name AS agent_name,
    COUNT(*) FILTER (WHERE call_outcome = 'Success') AS successful_calls,
    COUNT(*) AS total_calls,
    ROUND(
        COUNT(*) FILTER (WHERE call_outcome = 'Success') * 100.0 / COUNT(*), 
        2
    ) AS success_rate_pct
FROM call_center
GROUP BY agent_name
ORDER BY success_rate_pct DESC
LIMIT 5;

Agent Name	Successful Calls	Total Calls	Success Rate (%)
Samuel Smith	1,811	2,000	90.55
Monique Lawrence	14,443	16,000	90.27
Olivia Lyons	16,209	18,000	90.05
Liam Martinez	8,993	10,000	89.93
Michael Page	10,788	12,000	89.90

📊 Key Insights

Elite Efficiency: All top 5 agents consistently deliver a call success rate above 89.9%, demonstrating exceptional communication and resolution skills.
Top Performer: Samuel Smith leads with a 90.55% success rate, though with the smallest call volume (2,000). This suggests precision over quantity.
High Volume, High Accuracy: Olivia LyonsMonique Lawrence16,000–18,000 calls while maintaining a 90%+ success rate—highlighting operational excellence at scale.
Consistency Across Board: The narrow range (90.55%–89.90%) suggests a tightly knit, high-performing team with limited performance deviation.

🔍 Business Applications

🏅 Performance Rewards: Use this data to power quarterly performance dashboards and agent incentive schemes.
📈 Best Practice Replication: Leverage top agents' techniques and scripts for team-wide training and coaching sessions.
🧪 Volume-Effect Studies: Analyze how performance scales with workload by comparing low- and high-volume agents.
📉 Underperformance Benchmarks: Establish clear success rate thresholds for identifying and improving weaker performers.

This analysis not only quantifies agent effectiveness but also helps uncover scalable traits of success, ultimately driving stronger customer service outcomes and improved conversion rates. A simple yet powerful SQL aggregation like this turns raw interaction logs into actionable HR and operations intelligence.

4. 🎯 Product Conversion Rates Analysis


-- Calculate success rates per product discussed
SELECT 
    product_discussed,
    COUNT(*) AS total_calls,
    COUNT(*) FILTER (WHERE call_outcome = 'Success') AS successful_calls,
    ROUND(
        COUNT(*) FILTER (WHERE call_outcome = 'Success') * 100.0 / COUNT(*), 
        2
    ) AS success_rate_pct
FROM call_center
GROUP BY product_discussed
ORDER BY success_rate_pct DESC;

Product Discussed	Total Calls	Successful Calls	Success Rate (%)
Travel Package	21,700	11,739	54.10
Loans	50,173	27,085	53.98
Electronics	25,868	13,914	53.79
Internet Package	49,988	26,711	53.43
Insurance	7,627	4,070	53.36
Unknown / Unrecorded	44,644	0	0.00

📊 Key Insights

Highest Conversion: The Travel Package leads with a 54.10% success rate despite lower call volume, indicating strong product-market fit or effective agent training.
Consistent Mid-50s Performance: All marketed products hover between 53.36% and 54.10%, indicating balanced success across offerings.
High-Volume Winners: Loans and Internet Packages had over 50,000 calls each, maintaining solid success rates (~54%), showcasing scalable conversion ability.
Unrecorded Discussions: A significant portion of calls (44,644) had no product tagged, contributing 0 successful calls. This could represent:
- System logging issues
- Unqualified leads
- Data hygiene problems
and should be investigated further.

🚀 Business Applications

📈 Marketing Prioritization: Focus resources on high-performing products like Travel Packages and Loans.
📚 Script Optimization: Reverse-engineer successful pitches to standardize across agents and lower-performing products.
🧼 Data Quality Action: Implement mandatory product tagging to reduce the volume of unclassified interactions.
🔁 Training Feedback Loop: Feed insights into training programs to replicate success across all categories.

This product-level success analysis highlights not just what's working—but how consistent execution, product appeal, and data discipline shape campaign ROI. Through simple yet powerful SQL aggregation, we turn fragmented interaction data into strategic sales guidance.

5. ⏱️ Average Call Duration by Outcome


-- Calculate average call duration for each call outcome
SELECT 
    call_outcome,
    ROUND(AVG(call_duration_minutes), 2) AS avg_duration
FROM call_center
GROUP BY call_outcome;

Call Outcome	Average Duration (minutes)
Success	17.53
Failure	16.20
Abandoned	Not Available

🔍 Insightful Observations

Successful Calls: These had the longest average duration at 17.53 minutes. This suggests that longer, more engaged conversations tend to yield better outcomes.
Failed Calls: Averaging 16.20 minutes, they were only slightly shorter than successful ones. This indicates that time alone isn't the only factor—quality of engagement, customer needs, or agent expertise likely play major roles.
Abandoned Calls: No average duration was recorded—likely because these calls were disconnected before meaningful interaction could begin. This could stem from:
- Long hold times
- Technical issues
- User frustration
Reducing abandonment rate may unlock further opportunities for conversion.

🚀 Business Implications

⏳ Optimize Agent Talk Time: Ensure agents are not being rushed. Encouraging slightly longer conversations may increase success likelihood.
🧠 Training Focus: Provide skill-building sessions to turn longer failed calls into productive ones.
📉 Reduce Abandonment: Investigate abandonment causes and improve call queue processes or callback options.

By tying call outcomes to duration, we uncover how time investment correlates with success—and where operational friction exists. This analysis can guide improvements in training, queue management, and customer engagement strategy.

6. 📈 Impact of Repeat Customers on Call Success


SELECT 
    repeat_customer,
    COUNT(*) FILTER (WHERE call_outcome = 'Success') AS successful_calls,
    COUNT(*) AS total_calls,
    ROUND(
        COUNT(*) FILTER (WHERE call_outcome = 'Success') * 100.0 / COUNT(*), 
        2
    ) AS success_rate_pct
FROM call_center
GROUP BY repeat_customer;

Repeat Customer	Successful Calls	Total Calls	Success Rate (%)
No	62,630	150,170	41.71%
Yes	20,889	49,830	41.92%

📌 Key Insight

Repeat customers had a slightly **higher success rate (41.92%)** than new customers (41.71%).
Despite handling **fewer total calls**, repeat customers showed marginally better conversion outcomes.

🔍 Business Implications

🤝 Customer Loyalty Value: Even a small increase in success rate among repeat customers suggests stronger rapport or pre-existing familiarity with products or processes.
🧠 Targeting Strategy: Tailored scripts or offers might further improve success with repeat customers.
📊 Segmentation Opportunities: Combining repeat status with other variables (age, income) could reveal high-value personas.

By tracking repeat customer interactions, businesses can optimize resource allocation, enhance personalized service, and boost call conversion rates.

7. 👥 Gender-based Success Rates


SELECT 
    customer_gender,
    COUNT(*) AS total_calls,
    COUNT(*) FILTER (WHERE call_outcome = 'Success') AS successful_calls,
    ROUND(
        COUNT(*) FILTER (WHERE call_outcome = 'Success') * 100.0 / COUNT(*), 
        2
    ) AS success_rate_pct
FROM call_center
GROUP BY customer_gender;

Gender	Total Calls	Successful Calls	Success Rate (%)
Female	100,088	41,944	41.91%
Male	99,912	41,575	41.61%

📌 Key Insight

Female customers had a slightly higher call success rate (**41.91%**) compared to male customers (**41.61%**).
Total volume of calls was almost evenly split by gender, ensuring a balanced comparison.

🔍 Business Implications

🎯 Customer Engagement Optimization: Slight variations in success rates may point to differences in communication preferences or product fit across genders.
📢 Marketing Strategy: Tailoring follow-up messaging based on demographic insights could enhance engagement and conversion.
📊 Further Segmentation: Combine gender with other features (e.g., income bracket, repeat status) to uncover more nuanced behavioral patterns.

While the gender-based difference in success rate is modest, consistent patterns can guide refinements in outreach strategy and script personalization — especially in high-volume call environments.

8. 📞 Call Abandonment Reasons (Most Common)


SELECT 
    reason_call_abandoned,
    COUNT(*) AS occurrences
FROM call_center
WHERE reason_call_abandoned IS NOT NULL
GROUP BY reason_call_abandoned
ORDER BY occurrences DESC
LIMIT 5;

Reason for Call Abandonment	Occurrences
Technical Issues	70,230
long Wait Time	41,295
Customer Hung Up	3,793
Long Wait Time	3,791
Agent Not Available	3,787

📌 Key Insight

Technical Issues were the dominant cause of call abandonment, accounting for over 70k cases — indicating possible system instability or platform bugs.
Wait time appears twice due to case inconsistency: long Wait Time vs. Long Wait Time. These should be consolidated to improve data hygiene.
Other significant causes include customers hanging up and agent unavailability, each contributing ~3.7k cases.

🔍 Business Implications

⚙️ System Reliability: High technical failure rates signal the need for urgent IT intervention or platform upgrades.
⏱️ Queue Management: Long wait times — the second most common issue — highlight the need for staffing optimization and real-time monitoring.
📐 Data Cleaning: Case-sensitive duplicates (e.g., “long Wait Time”) reduce clarity and should be normalized in ETL or preprocessing steps.
👥 Agent Resource Planning: Unavailability of agents may warrant call routing improvements or more predictive scheduling.

Reducing abandonment starts with resolving technical issues and wait times. These insights drive both **IT improvements** and **workforce planning**, helping to retain customers and improve satisfaction.

💡 Key Business Insights

📊 Overall Success Rate

Approximately 41.79% of all calls result in a successful outcome. This serves as a benchmark for evaluating agents and campaigns.

🌟 Top Performers

A select group of agents consistently outperform their peers, achieving success rates above 90%. This presents a model for coaching others.

🛍️ High-Converting Products

Products like Travel Packages and Loans show conversion rates above 54%, suggesting these offerings resonate well with customers.

⏱️ Call Duration Matters

Successful calls last an average of 17.53 minutes — longer than failed ones. Quality engagement time appears to drive conversions.

🔁 Return Customers

Repeat customers show higher conversion rates, emphasizing the importance of customer loyalty programs and CRM strategies.

⚖️ Gender Differences

Minor variations in success rates by gender could inform targeted marketing strategies or training interventions.

🚨 Call Abandonment

“Technical Issues” is the leading reason for abandoned calls, highlighting a need for system stability and UX improvements.

✅ Conclusion

This project highlights the power of SQL-driven analytics in a sales call center environment. By dissecting agent performance, product efficiency, customer loyalty, and system challenges, we uncover tangible business opportunities to:

🎯 Coach underperforming agents using top-performer benchmarks.
💼 Prioritize high-conversion products in marketing and outreach.
🛠️ Reduce call abandonment by addressing technical bottlenecks.
📢 Design loyalty-driven campaigns to nurture repeat business.

This analytical approach not only supports operational improvements but also empowers strategic decisions that can boost overall sales performance and customer satisfaction.

📊 Project Title: Sales & Marketing Call Center Analysis Using PostgreSQL

🧾 Dataset Description

1. 🛠️ PostgreSQL Data Preparation: Structuring a Clean Call Center Dataset

📌 Key Preparation Highlights

🔍 Business Use Cases

2. 🔍 Exploratory SQL Analysis: Total Calls & Success Rate

📊 Output Snapshot

📌 Analytical Insights

🔍 Business Use Cases

3. 👥 Top 5 Performing Agents: Ranked by Call Success Rate

📊 Key Insights

🔍 Business Applications

4. 🎯 Product Conversion Rates Analysis

📊 Key Insights

🚀 Business Applications

5. ⏱️ Average Call Duration by Outcome

🔍 Insightful Observations

🚀 Business Implications

6. 📈 Impact of Repeat Customers on Call Success

📌 Key Insight

🔍 Business Implications

7. 👥 Gender-based Success Rates

📌 Key Insight

🔍 Business Implications

8. 📞 Call Abandonment Reasons (Most Common)

📌 Key Insight

🔍 Business Implications

💡 Key Business Insights

📊 Overall Success Rate

🌟 Top Performers

🛍️ High-Converting Products

⏱️ Call Duration Matters

🔁 Return Customers

⚖️ Gender Differences

🚨 Call Abandonment

✅ Conclusion