Customer Success

If you work in customer success, it's crucial to keep track of metrics to stay on top of customer happiness. Let's take a look at some popular metrics to track and sample SQL queries:

Average number of support tickets per customer

SELECT AVG(ticket_count) AS avg_tickets_per_customer
FROM (
SELECT customer_id, COUNT(*) AS ticket_count
FROM support_tickets
GROUP BY customer_id
) AS subquery;

This query calculates the average number of support tickets submitted by each customer. A higher average may indicate a need for improved product documentation or user experience.

Customer retention rate

SELECT (COUNT() * 100.0 / (SELECT COUNT() FROM customers)) AS retention_rate
FROM customers
WHERE status = 'active';

This query calculates the customer retention rate by dividing the total number of active customers by the total number of customers. A higher retention rate indicates a higher level of customer satisfaction and loyalty.

Average customer lifetime value (CLTV)

SELECT AVG(total_revenue) AS avg_cltv
FROM (
SELECT customer_id, SUM(revenue) AS total_revenue
FROM orders
GROUP BY customer_id
) AS subquery;

This query calculates the average customer lifetime value by summing the total revenue generated by each customer and then averaging it. This metric helps to understand the long-term value of customers.

Average response time for support tickets

SELECT AVG(TIMESTAMPDIFF(MINUTE, created_at, resolved_at)) AS avg_response_time
FROM support_tickets;

This query calculates the average response time for support tickets by finding the difference between the created_at and resolved_at timestamps. A shorter response time indicates better support efficiency and customer satisfaction.

Customers with a high net promoter score (NPS)

SELECT COUNT(*) AS high_nps_customers
FROM customer_surveys
WHERE nps >= 9;

This query returns the total number of customers with a high net promoter score (NPS), which is a measure of customer satisfaction and loyalty. A higher number of high-NPS customers indicates better customer success.

Percentage of support tickets resolved within 24 hours

SELECT (COUNT() * 100.0 / (SELECT COUNT() FROM support_tickets)) AS resolved_within_24h_percentage
FROM support_tickets
WHERE TIMESTAMPDIFF(HOUR, created_at, resolved_at) <= 24;

This query calculates the percentage of support tickets resolved within 24 hours. A higher percentage indicates better support efficiency and customer satisfaction.

Average number of orders per customer

SELECT AVG(order_count) AS avg_orders_per_customer
FROM (
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
) AS subquery;

This query calculates the average number of orders placed by each customer. This metric can help to understand customer engagement and purchasing habits.

Number of customers who have made repeat purchases

SELECT COUNT() AS repeat_customers
FROM (
SELECT customer_id, COUNT() AS order_count
FROM orders
GROUP BY customer_id
HAVING order_count > 1
) AS subquery;

This query returns the total number of customers who have made more than one purchase. This metric is useful for understanding customer loyalty and repeat business.

Percentage of customers who have completed onboarding

SELECT (
    SELECT COUNT() * 100.0 / (SELECT COUNT() FROM customers)
) AS onboarding_completion_percentage
FROM customers
WHERE onboarding_status = 'completed';

This query calculates the percentage of customers who have completed the onboarding process. A higher percentage indicates a better onboarding experience, which can contribute to increased customer satisfaction and engagement.

Average customer satisfaction score from customer surveys

SELECT AVG(satisfaction_score) AS avg_customer_satisfaction
FROM customer_surveys;

This query calculates the average customer satisfaction score from customer surveys. This metric helps to understand overall customer satisfaction and identify areas for improvement.

Percentage of support tickets escalated to a higher support tier

SELECT (COUNT() * 100.0 / (SELECT COUNT() FROM support_tickets)) AS escalation_percentage
FROM support_tickets
WHERE escalated = 1;

This query calculates the percentage of support tickets that have been escalated to a higher support tier. A lower percentage indicates better initial support resolution and customer satisfaction.

Total number of customers who have referred others

SELECT COUNT(*) AS referring_customers
FROM customers
WHERE referred_by IS NOT NULL;

This query returns the total number of customers who have referred other customers. This metric can be used to gauge the effectiveness of referral programs and customer advocacy.

Average number of days between customer orders

SELECT AVG(TIMESTAMPDIFF(DAY, previous_order_date, order_date)) AS avg_days_between_orders
FROM (
SELECT customer_id, order_date, LAG(order_date) 
OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order_date
FROM orders
) AS subquery;

This query calculates the average number of days between customer orders. This metric can help to understand customer purchase frequency and engagement.

Percentage of customers who have opted in for marketing communications

SELECT (COUNT() * 100.0 / (SELECT COUNT() FROM customers)) AS marketing_opt_in_percentage
FROM customers
WHERE marketing_opt_in = 1;

This query calculates the percentage of customers who have opted in for marketing communications. A higher percentage indicates better customer engagement and interest in marketing content.

Average product rating from customer reviews

SELECT AVG(rating) AS avg_product_rating
FROM product_reviews;

This query calculates the average product rating from customer reviews. This metric helps to understand overall product satisfaction and identify areas for improvement.

Total number of customers who have contacted support multiple times

SELECT COUNT() AS multiple_support_contact_customers
FROM (
SELECT customer_id, COUNT() AS contact_count
FROM support_tickets
GROUP BY customer_id
HAVING contact_count > 1
) AS subquery;

This query returns the total number of customers who have contacted support multiple times. This metric can be used to identify customers with recurring issues or potential areas for improvement in product usability.

Calculate the average order value (AOV)

SELECT AVG(order_total) AS avg_order_value
FROM orders;

This query calculates the average order value by averaging the order totals. This metric helps to understand customer spending habits and the overall success of sales efforts.

Percentage of customers who have left product reviews

SELECT (
    COUNT(DISTINCT customer_id) * 100.0 / (SELECT COUNT(*) FROM customers)
) AS review_percentage
FROM product_reviews;

This query calculates the percentage of customers who have left product reviews. A higher percentage indicates better customer engagement and feedback on products.