Course of the Month: Customer Behavior Analysis in SQL

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(), use CASE WHEN with COUNT(), and how to combine CASE WHEN with 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!