26th Nov 2021 8 minutes read Here’s Why You Should Use SQL for Sales Analytics András Novoszáth sql data analysis Would you like to understand how your sales efforts play out so that you know where to put your focus? Do you want to empower your sales teams with sales analytics? This article shows you how you can use SQL to use your sales data to your advantage. If you work in sales, understanding the activities that produce results has financial consequences for you, your team, and your company. This is where sales analytics can really help you. Knowing what to sell, to whom, when, through what channel, and how can all be tackled if you collect and analyze data on your sales efforts. Sales analytics involves analyzing, interpreting, and modeling sales data to create metrics, reports, insights, and predictions for improving the effectiveness of sales efforts. Understanding your sales metrics helps you correct pipeline obstacles and double down on promising segments and practices. In this article, we show you how you can use SQL at your company to follow the current trend of data democratization and make data and analytics accessible to your sales team. The benefits of sales analytics may be obvious, but there can be challenges in implementing it and making it broadly accessible for your sales staff. So, we provide an overview of sales analytics, its different types, and what data democratization means in its context. We show you why learning SQL is a great way to advance self-service analytics for your sales team, present you with SQL examples in sales analytics, and provide you resources for learning more. Curious? Read on! What Is Sales Analytics? Sales analytics is the practice of using data to generate sales-related insights, including sales history, results, trends, and forecasts. Its specific meaning can vary by context. Sales analytics overlaps with other disciplines (e.g., marketing, customer, or product analytics) and may cover processes that are not directly sales-related. Types of Sales Analytics There are many ways to categorize sales analytics. A meaningful approach is to consider what you want to analyze and for what purpose (e.g., gain new insights, report, monitor, understand, or predict). Here are typical sales analytics areas: KPIs: Annual or monthly sales numbers broken down by product, team, geography, or individual sales rep. Activity Metrics: Attributes of sales efforts the managers can influence (e.g., form of outreach, customer group priorities, frequency, channel, etc.). Sales Pipeline Metrics: Measures of the “health” and the “speed” of sales pipeline components. Sales Operations Metrics: Measures about processes, tools, and infrastructures related to the company’s sales activity. Pricing Analytics: Determining appropriate pricing for products and customer groups. Benefits of Sales Analytics There are many benefits of analyzing your sales activity: Sales analytics helps you understand the outcome of your sales efforts and establish accountability within your sales team. You can use insights from sales analytics to identify key levers of your sales processes, prioritize them, and improve your sales efficiency. Thinking through and evaluating the components of your sales pipeline identifies steps that can be automated. If you are a sales rep or a sales manager working on commission, knowing the outcome and effectiveness of your or your teams’ day-to-day activity has even more value. From sales analytics, you can expect increased sales, faster sales cycles, and lower sales costs. Democratizing Sales Analytics With the current trend in data democratization, more and more companies are recognizing the value of tooling up their domain experts to do analytics. The sales domain is no different. However, you may not have the means or the skills to access and investigate your company’s sales data. Your analytical inquiries probably depend on the availability and the priorities of your data team. You understand your domain and how to sell. Your data team understands how to collect, store, and manage data. You both are busy. Nagging your data team about producing reports every time you have some hunch is slow and creates lots of friction. Wouldn’t it be better if you could ask questions, generate reports, and make a case for your arguments based on the knowledge you already have? Self-service sales analytics is the idea that you can analyze sales data for meaningful and actionable insights quickly and on your own. However, you need the means to do it. If your company wants to be data-driven, it should provide you access to data related to your job and the skills to get meaningful insights from it. Achieving data democratization and self-service analytics is a long and elaborate process with multiple components. Why Use SQL for Sales Analytics Learning SQL is perhaps the easiest and fastest way to democratize data and sales analytics – to access and investigate your company’s sales data – with immediate results. SQL is a simple language widely used across different platforms, industries, and use cases. With SQL, you can analyze small and big datasets, investigate problems, or tackle any analytics related to your business. It is a great tool for generating sales reports or conducting revenue, marketing, and financial analysis. Examples of Using SQL for Sales Data Analysis Let’s see examples of SQL use for sales analytics problems using the Microsoft Northwind sample database. They are real-world problems from our SQL Revenue Trend Analysis course, where you can practice more advanced questions in an interactive environment, find detailed explanations, and receive immediate feedback. Sales Volume In sales analytics, the first thing you want to know may be how much you have sold. For example, you may need the total sales volume you have managed to close. Or you may want to understand the sales amount at the order level. Here is a query that shows you how to understand the order sizes with the Northwind database. You need to connect the orders and order_items tables, group the data on your order_id column, and calculate the total amount of sales for each order. SELECT orders.order_id, SUM(order_items.amount) AS total_amount FROM orders JOIN order_items ON order_items.order_id=orders.order_id GROUP BY orders.order_id ORDER BY total_amount DESC order_idtotal_amount 1086516387.5 1098115810 1103012615.05 1088911380 1041711188.4 1081710952.85 1089710835.24 1047910495.6 1054010191.7 1069110164.8 The result gives you the sales amount for each order and shows the top orders for the period for a general idea of the order sizes. You can make it more useful by aggregating them along other analytic dimensions like period and product. Year-on-Year Sales One problem with order-level sales amounts is that they do not tell you how your total sales have changed over the years. So, let’s include the time information by aggregating the order amounts by year: SELECT DATE_PART('year', order_date) AS order_year, SUM(amount) as annual_revenue FROM orders GROUP BY order_year ORDER BY order_year order_yearannual_revenue 2016208083.99 2017623090.04 2018233142.92 201973590.39 202081805.73 202146080.22 This SQL report on year-on-year sales shows your sales volumes have been fluctuating a lot over the years. You may understand better what impacts your sales numbers if you drill down into specific years for further details. Product Categories You can get your sales numbers by product category. The following query shows you how; you need to connect four tables (order_items, orders, products, and categories). SELECT categories.category_name, SUM(orders.amount) as total_amount FROM orders JOIN order_items ON orders.order_id=order_items.order_id JOIN products ON order_items.product_id=products.product_id JOIN categories ON products.category_id=categories.category_id GROUP BY category_name ORDER BY total_amount DESC category_nametotal_amount Beverages769420.6 Dairy Products718529.47 Confections623619.81 Seafood568684.79 Condiments389171.11 Meat/Poultry374488.93 Grains/Cereals311809.41 Produce240514.84 This SQL report ranks the product categories by the amount sold so you can see which ones have sold the most. It gives you a high-level picture of the product categories from which you got the most revenue. Sales Performance One way to learn about the efficiency of your sales efforts is to see how much revenue each of your sales reps have generated. To do so, you need to get the employee names from the employees table and connect it with the amount column from orders. SELECT CONCAT(employees.first_name, ' ', employees.last_name) AS employee_name, SUM(orders.amount) as total_amount FROM employees JOIN orders ON employees.employee_id=orders.employee_id GROUP BY employee_name ORDER BY total_amount DESC employee_nametotal_amount Margaret Peacock231770.99 John Smith203932.78 Nancy Davolio192107.67 Andrew Fuller166537.76 Laura Callahan126862.3 Robert King124568.24 Anne Dodsworth77308.09 Michael Suyama73913.15 Steven Buchanan68792.31 This SQL report shows you how much sales your reps have generated. This insight is helpful, although it needs further explanation. You can drill down for further details to find out the challenges faced by those who have not performed well and the best practices from the top-performing reps. Leverage SQL for Sales Analytics and Get Better Results! We have covered the core concepts of using SQL for sales analytics, outlined its benefits, and shown you how to do data analysis on sales data at your company to get insights. At this point, you may be starting to see the use of SQL and wondering how you can find out more about it. We can help! You can use our resources to learn SQL quickly and efficiently. If you are just starting with SQL, check out our introductory courses. If you want to get serious, you can explore our learning tracks, like the one that gives you a comprehensive knowledge of SQL or the one that teaches you the ins and outs of SQL reporting. We have a wide range of course offerings, whether you are looking to train your sales team or only yourself. Check them out! Tags: sql data analysis