Back to articles list December 1, 2020 - 8 minutes read SQL Course of the Month – Customer Behavior Analysis in SQL LearnSQL.com Team Tags: sql learn sql course of the month Do you run a business? Do you want to squeeze even more out of your data? Start by analyzing your customers' behavior using SQL! Throughout December, you can learn how to do it for free on LearnSQL.com! I asked Agnieszka Kozubek-Krycuń, Vertabelo's Chief Content Officer, about our December Course of the Month, Customer Behavior Analysis in SQL. Here's what she told me. Let's start with the basics: Why analyze customer behavior? What will it do for us? Analyzing data about customers and their behavior is crucial for any business’ development. First, you should know who your customers are: their age, location, interests, employment status, etc. This is called demographics, and knowing this information will help you effectively communicate with your audience and market your business. Sometimes, this information may be obvious: if you’re a neighborhood grocery store, then your customers are people who live nearby. If you’re an online business, then you can use tools such as Google Analytics to get this data. You should also analyze customer information beyond demographics: when do they register, when do they buy, which promotional campaigns work best, which product features do the customers use most (or least). This information can help you build a better product. It can also help you save some money; if there’s a feature that doesn’t get much use, maybe it’s not needed and you can remove it. Or maybe this feature is difficult to find or use and you need to rebuild your UI. If you don’t analyze customer information, you won’t know how people use your product. You should also analyze the purchasing patterns of your customers: when do they purchase, which product do they purchase most often, when do they quit purchasing from you? This can help you predict your income and revenue. You can analyze which users are buying the most from you. If you identify features of these “good” users, perhaps you can advertise more to them and build your business this way. That’s how data-driven marketing works. You convinced me. But why use SQL? You can use ready-made reporting tools to get some information about user behavior. But ready-made reports have limitations: They are inflexible. They either can’t be changed or they take a long time to change. Doing your analysis in SQL makes you independent from these ready-made reports and from the IT department. You can analyze your data faster and – more importantly – in an interactive manner. If you notice an interesting trend in the data, SQL lets you investigate further. You can make experiments with the date. You can state a hypothesis that matches your business and then test it with data. If you want to see how you can do interesting analytics using SQL and Google Analytics, read my friend Adrian’s article Google Analytics and SQL Can you give me some examples? Christmas is coming up, and many people will shop online. Can customer behavior analysis apply here? Of course! If you’re a shop owner, you can analyze last year’s behavior: Is there a holiday rush in your business? When do customers start Christmas shopping? Which kinds of promotions worked best last year? Even if you don’t have data from last year, you can compare customer behavior during the festive period with behavior during regular times: Do you have more customers than usual? Do they buy more products? Finally, you can analyze customer behavior after the holidays are over to get data for the next year. Does LearnSQL.com use SQL analysis to study the behavior of your users? Of course we do. We use SQL to improve our courses. With the help of SQL, we can check which exercises are difficult for users and have below average completion rates. This helps us identify exercises where users get stuck. Then we can investigate why this particular exercise is difficult: maybe the wording is ambiguous, or maybe this is a hard topic and the users need more hints. And then we can modify the exercise and in a couple of weeks we can check if our modifications actually improved the exercise. So, SQL customer analysis is worth doing. How long will it take me to do this SQL course? How many exercises are there? There are 76 exercises split into 5 parts. The first four parts focus on analyzing various stages in the customer funnel: In Part 1, we learn to analyze customer registration: how many users registered during a given period, how many come from each channel, how many users registered week-over-week or month-over-month. We introduce the notion of the customer cohort, i.e. a group of customers who registered during the same time period. In Part 2, we analyze conversion, or how many users purchase our products. We learn how to prepare customer conversion charts in SQL. In Part 3, we analyze customer activity: which products are the most popular, how much money each user spends, and how to identify the most and least active users. In Part 4, we analyze customer churn, which is the rate at which customers leave our business. We learn to prepare customer retention charts in SQL. Part 5 is a quiz where you prepare a quarterly report summarizing user behavior. We estimate that completing the course will take about 10 hours. Is this free course part of a larger track? Yes. This course is part of our SQL Reporting track, which was designed for users who want to practice using SQL in a business context. It’s all about writing complex SQL queries that resemble the ones you’d write in real business analysis. There are three courses in this track: Creating Basic SQL Reports, which is an introduction to building complex SQL queries (we talked about this course in May); Revenue Trend Analysis in SQL, which explains how to use SQL to analyze revenue information (we talked about this in September); and this course, Customer Behavior Analysis in SQL. Who should take this course? Everyone! This course is perfect for data scientists, marketing managers, product managers, business analysts – anyone who needs to make business decisions based on customer data. The course will give them the opportunity to learn about SQL queries they’d actually write to analyze customer behavior in real life. This course is also perfect for people who just want to practice writing complex SQL queries: students taking database classes, people preparing for an interview, or software engineers looking for ways to practice their SQL. This course covers a lot of intermediate and advanced SQL topics: We review date and time functions in SQL and practice how to use them to group data by year, quarter, month, and week. We show how to compute the difference between two timestamps and how to use that to compute the average time between two events, such as registration and purchase. We practice using the GROUP BY clause in a business context, e.g. grouping by multiple columns or by computed expressions. We discuss the CASE WHEN expression, which is the SQL equivalent of an IF statement in other programming languages. We also practice using CASE WHEN to classify customers based on custom criteria. We demonstrate how to combine CASE WHEN with SUM(), COUNT(), and GROUP BY to create summaries based on custom criteria. This course is good practice for using SQL’s date and time functions, the GROUP BY clause, and combining GROUP BY with CASE WHEN. Will I receive a certificate to post on my LinkedIn profile? Yes. After completing the exercises in this course, you’ll receive a PDF certificate that you can publish on your LinkedIn profile. See how easy it is to do by reading our short guide. Do you remember working on this course? This is a new course, so I remember working on it very well. After creating the courses in our complete SQL From A to Z track, we wanted to focus on developing courses that would teach users to create SQL queries in a realistic business context. That’s how we came up with the idea for the SQL Reporting track. We looked at the queries we write ourselves and the queries we teach in our custom courses for various companies. Often, these focus on analyzing customer behavior: when do they register, when do they purchase, which products do they use most often, can we predict when the user will quit, etc. We decided to build on our experience in using and teaching customer-related queries, so we prepared Customer Behavior Analysis in SQL. The biggest challenge in creating this SQL course was finding a suitable data set. We used the Northwind database, which is a sample Microsoft database that we had already used in Revenue Trend Analysis in SQL. We had to extend the dataset substantially, as it was meant for a small store. In the original Northwind database, there are only 91 customers – not enough to show any trends in customer behavior. We had to generate more customers and more behavior data (i.e. orders). Free SQL Course for November: Customer Behavior Analysis in SQL And now, what you've all been waiting for. We have something special for our users. Throughout December, the course Customer Behavior Analysis in SQL is FREE! You can get full access to this great interactive SQL course without paying anything. Sign up and start today! Learn how to create reports and perform effective data analyses using SQL. Become a SQL expert! Tags: sql learn sql course of the month You may also like SQL Course of the Month – GROUP BY Extensions in SQL Free SQL course on GROUP BY extensions. Learn how to prepare better reports and make it easier to work with data. Read more SQL Course of the Month – How to INSERT, UPDATE, and DELETE Data in SQL Learn how to successfully modify data in your tables. Discover CRUD commands in this SQL course. Hurry up and learn for free! Read more SQL Course of the Month – Analyzing Revenue Trends in SQL During the month of September, the course on Analyzing Revenue Trends in SQL is free. Learn how to create the best SQL reports. Read more SQL Course of the Month – PostGIS PostGIS enables efficient processing of large geographic data sets. Find out why you should take our interactive course. Read more SQL Course of the Month – SQL Practice Set How to remember SQL functions and write better queries? Practice, practice, practice. Learn how to practice SQL the right way. Read more SQL Course of the Month – Window Functions Find out why you should learn SQL window functions in April and why you should do it in our course. Read more SQL Course of the Month – Creating Basic SQL Reports Do you want to do better in business? Get more from your data by learning to create SQL reports. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.