02. Customer Satisfaction Metrics Analysis (NPS & CSAT)

1. Overview and Scenario

Situation: We’re working hard to process tickets, but we don’t actually know if customers are satisfied. The CEO asks in a meeting:

“How many people would recommend our service? (NPS)” “Are there any complaints from people who received support? (CSAT)”

In this chapter, we’ll directly calculate and analyze two key metrics: NPS (Net Promoter Score) and CSAT (Customer Satisfaction Score).

2. Data Preparation

This time we’ll use a new table called survey_cs_dummy.

# BigQuery connection setup (same as 01. CS Ticket Exploration)
from google.cloud import bigquery
client = bigquery.Client()

import pandas as pd
 
# Load survey data
surveys = pd.read_csv('survey_cs_dummy.csv')
print(f"✅ Survey data: {len(surveys):,} records")

✅ Survey data: 1,500 records

Data Structure

Key columns of the survey_cs_dummy table:

• user_id: Customer ID
• nps_score: 0-10 points (likelihood to recommend to a friend)
• csat_score: 1-5 points (support satisfaction)
• related_ticket_id: Related ticket ID (may not exist)

3. NPS (Net Promoter Score) Analysis

NPS is the most powerful metric for measuring customer loyalty. The formula is as follows:

NPS = (Promoter %) - (Detractor %)

• Promoter: 9-10 points
• Passive: 7-8 points
• Detractor: 0-6 points

❓ Problem 1: Classify Groups

Q. Classify customers into Promoter, Passive, and Detractor based on nps_score, and calculate the ratio of each group.

WITH nps_classification AS (
    SELECT
        nps_score,
        CASE
            WHEN nps_score >= 9 THEN 'Promoter'
            WHEN nps_score >= 7 THEN 'Passive'
            ELSE 'Detractor'
        END as nps_category
    FROM `your-project-id.retail_analytics_us.survey_cs_dummy`
    WHERE nps_score IS NOT NULL
)
SELECT
    nps_category,
    COUNT(*) as count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) as percentage
FROM nps_classification
GROUP BY nps_category;

def classify_nps(score):
    if score >= 9: return 'Promoter'
    elif score >= 7: return 'Passive'
    else: return 'Detractor'
 
# Apply classification
surveys['nps_category'] = surveys['nps_score'].apply(classify_nps)
 
# Check ratios
print(surveys['nps_category'].value_counts(normalize=True) * 100)

nps_category
Detractor    78.4
Promoter     11.4
Passive      10.2
Name: proportion, dtype: float64

❓ Problem 2: Calculate NPS Score

Q. Calculate the final NPS score based on the ratios calculated above.

SELECT
    ROUND(
        (COUNTIF(nps_score >= 9) - COUNTIF(nps_score <= 6)) * 100.0 / COUNT(*),
        1
    ) as nps_score
FROM `your-project-id.retail_analytics_us.survey_cs_dummy`
WHERE nps_score IS NOT NULL;

nps_counts = surveys['nps_category'].value_counts(normalize=True)
nps_score = (nps_counts.get('Promoter', 0) - nps_counts.get('Detractor', 0)) * 100
 
print(f"Final NPS: {nps_score:.1f} points")

Final NPS: -67.0 points

4. CSAT (Customer Satisfaction) Analysis

CSAT measures satisfaction with a specific interaction (in this case, support). Typically, 4 points (satisfied) and 5 points (very satisfied) are combined for calculation.

❓ Problem 3: Calculate CSAT Score

Q. Calculate the percentage (%) of respondents who gave 4 points or higher out of all responses.

SELECT
    ROUND(COUNTIF(csat_score >= 4) * 100.0 / COUNT(*), 1) as csat_pct
FROM `your-project-id.retail_analytics_us.survey_cs_dummy`
WHERE csat_score IS NOT NULL;

satisfied = surveys[surveys['csat_score'] >= 4]
csat_score = (len(satisfied) / len(surveys)) * 100
 
print(f"CSAT: {csat_score:.1f}%")

CSAT: 29.5%

5. Advanced: Connecting Tickets and Surveys

Rather than looking at survey data separately, it’s more important to see “Which ticket resolutions led to higher satisfaction?”

❓ Problem 4: Average Satisfaction by Ticket Status

Q. Join with the cs_tickets_dummy table and calculate the average CSAT score by ticket status (status).

SELECT
    t.status,
    ROUND(AVG(s.csat_score), 2) as avg_csat
FROM `your-project-id.retail_analytics_us.survey_cs_dummy` s
JOIN `your-project-id.retail_analytics_us.cs_tickets_dummy` t
  ON s.related_ticket_id = t.ticket_id
GROUP BY t.status
ORDER BY avg_csat DESC;

# Merge data
merged = pd.merge(surveys, tickets, left_on='related_ticket_id', right_on='ticket_id')
 
# Average CSAT by status
status_csat = merged.groupby('status')['csat_score'].mean().sort_values(ascending=False)
print(status_csat)

Error: name 'tickets' is not defined

💡 Summary and Insights

In this chapter, we quantified the customer’s voice.

1. NPS: Measures loyalty (Promoter - Detractor). Shows the long-term health of our service.
2. CSAT: Measures experience satisfaction (ratio of 4-5 points). Shows support quality immediately.
3. Correlation: Typically, better main product or service experience (High CSAT) tends to correlate with higher recommendation intent (High NPS).

Next Steps: Why are customers dissatisfied? We’ll analyze “Returns” data, one of the biggest sources of complaints, in the next session.