While Bindle founders were dealing with partnerships with other companies and book stores Facebook campaigns got some traction. Users from DACH region and from English speaking countries started to sign up.
The only question was – how did the campaigns actually perform?
Were they profitable? Profitable means we earned more money than we spent. A marketer would say something like “Do our campaigns have positive ROI?”
What was the impact? How many users signed up from a certain country we targeted? How many signups and customers did we acquire day by day?
Which campaigns are good and should we keep them? Which campaigns are bad? We need to learn from these ones and shut them down.
In this chapter and next chapters we’ll learn in detail how to answer all these questions Now let’s start with segmenting our users by country.
We already know how to count signups in a specific country, US for example:
SELECT COUNT(*) FROM users WHERE country = 'us'
If we want to count signups in 20 countries – we just need to run 20 queries. Sounds scary, right?
As usual, SQL has our back in this challenge as well. The query above uses aggregate function
COUNT(*). Let’s briefly remember how it works. It calculates a value (
COUNT calculates the number of records) for a group of records. The query above has a single group – all users with country “us”.
Same way we can have multiple groups to aggregate. It’s possible with
GROUP BY clause:
SELECT country, COUNT(*) AS users_count FROM users GROUP BY country
If the query has
GROUP BY clause the records will be first split into different groups. Each group then will be aggregated into a number (via an aggregate function).
Here’s some intuition to understand and remember how
GROUP BY works.
GROUP BY splits all records into groups (one group for each value inside country column). After we calculate an aggregated value for each group. This is why in our
SELECT statement we should have a column we split our records by (in our case it’s country) and the result of the aggregate function.
Hi, it’s Anatoli, the author of SQL Habit.
SQL Habit is a course (or, as some of the students say, “business simulator”). It’s based on a story of a fictional startup called Bindle. You’ll play a role of their Data Analyst and solve real-life challenges from Business, Marketing, and Product Management.
SQL Habit course is made of bite-sized lessons (you’re looking at one atm) and exercises. They always have a real-life setting and detailed explanations. You can immediately apply everything you’ve learned at work.