Running marketing Сounting signups per country: intro to GROUP BY

# 59. Сounting signups per country: intro to GROUP BY

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.

## Count users per 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.