Sales

Win rate by sales representative

SELECT sales_rep_id, (COUNT() * 100.0 / (SELECT COUNT() FROM deals WHERE sales_rep_id = d.sales_rep_id)) AS win_rate
FROM deals AS d
WHERE deal_status = 'closed'
GROUP BY sales_rep_id;

This query calculates the win rate for each sales representative by dividing the total number of closed deals by the total number of deals handled by each rep. A higher win rate indicates better sales performance.

Customer lifetime value (CLTV) by sales representative

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

This query calculates the average customer lifetime value by sales representative by summing the total revenue generated by each customer for each rep and then averaging it. This metric helps to evaluate the long-term impact of individual sales efforts.

Number of upsells by sales representative

SELECT sales_rep_id, COUNT(*) AS upsell_count
FROM upsells
GROUP BY sales_rep_id;

This query returns the total number of upsells for each sales representative. Upsells are an important aspect of sales performance, as they represent additional revenue opportunities with existing customers.

Revenue by quarter

SELECT EXTRACT(QUARTER FROM order_date) AS quarter, 
EXTRACT(YEAR FROM order_date) AS year, 
SUM(revenue) AS quarter_revenue
FROM orders
GROUP BY year, quarter;

This query calculates the total revenue generated per quarter. This metric helps to track sales performance over time and can be used for quarterly sales reporting and forecasting.

Average order value (AOV)

SELECT AVG(revenue) AS avg_order_value
FROM orders;

This query calculates the average order value by averaging the revenue from all orders. AOV is a key metric for understanding customer spending habits and the effectiveness of sales strategies.

Revenue by product category

SELECT product_category, SUM(revenue) AS category_revenue
FROM orders
GROUP BY product_category;

This query calculates the total revenue generated per product category. This metric is useful for understanding which product categories are the most successful and where to focus sales efforts.

Revenue by sales region

SELECT sales_region, SUM(revenue) AS region_revenue
FROM orders
GROUP BY sales_region;

This query calculates the total revenue generated per sales region. This metric helps to identify high-performing regions and inform regional sales strategies.

Top 10 best-selling products

SELECT product_id, COUNT(*) AS units_sold
FROM order_items
GROUP BY product_id
ORDER BY units_sold DESC
LIMIT 10;

This query returns the top 10 best-selling products by the number of units sold. This metric can inform inventory management and sales efforts.

Revenue by sales representative

SELECT sales_rep_id, SUM(revenue) AS rep_revenue
FROM orders
GROUP BY sales_rep_id;

This query calculates the total revenue generated per sales representative. This metric can be used to evaluate individual sales performance and identify top performers.

Total number of new customers acquired

SELECT COUNT(*) AS new_customers
FROM customers
WHERE first_order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

This query returns the total number of new customers acquired within the last 30 days. This metric is essential for tracking the success of customer acquisition efforts.

Conversion rate

SELECT (COUNT() * 100.0 / (SELECT COUNT() FROM leads)) AS conversion_rate
FROM customers
WHERE lead_id IS NOT NULL;

This query calculates the conversion rate by dividing the total number of converted leads by the total number of leads. A higher conversion rate indicates a more effective sales process.

Average deal size

SELECT AVG(revenue) AS avg_deal_size
FROM deals;

This query calculates the average deal size by averaging the revenue from all deals. This metric helps to understand the typical value of sales opportunities.

Revenue by sales channel

SELECT sales_channel, SUM(revenue) AS channel_revenue
FROM orders
GROUP BY sales_channel;

This query calculates the total revenue generated per sales channel. This metric can help to identify the most effective sales channels and inform channel-specific strategies.

Percentage of deals closed

SELECT (COUNT() * 100.0 / (SELECT COUNT() FROM deals)) AS close_rate
FROM deals
WHERE deal_status = 'closed';

This query calculates the close rate by dividing the total number of closed deals by the total number of deals. A higher close rate indicates a more effective sales process.

Revenue growth rate month-over-month

SELECT EXTRACT(MONTH FROM order_date) AS month, 
(SUM(revenue) - LAG(SUM(revenue)) 
OVER (ORDER BY EXTRACT(MONTH FROM order_date))) 
/ LAG(SUM(revenue)) 
OVER (ORDER BY EXTRACT(MONTH FROM order_date)) * 100 AS revenue_growth_rate
FROM orders
GROUP BY EXTRACT(MONTH FROM order_date);

This query calculates the month-over-month revenue growth rate by comparing the sum of revenue for each month with the previous month's revenue. This metric helps to track sales performance over time and identify trends.

Average sales cycle length

SELECT AVG(TIMESTAMPDIFF(DAY, opportunity_created_date, close_date)) AS avg_sales_cycle_length
FROM deals;

This query calculates the average sales cycle length by finding the difference between the opportunity creation date and close date for each deal. A shorter sales cycle length indicates a more efficient sales process.