Basic business metrics

You already know enough to write complex SQL queries. In this topic, we will get acquainted with common metrics used to evaluate a company's performance, and figure out how to calculate them using SQL.

In the fast-paced world of business, data-driven decisions are crucial for success. Companies strive to evaluate their performance and gain valuable insights to make informed choices. One useful tool for this is SQL, an universal language that can be used to calculate and analyze key business metrics.

In this topic, we will explore some common metrics that help businesses assess their operations and customer interactions. We will leverage SQL's capabilities to calculate conversion rates, perform funnel analyses, and track daily average users. By understanding and mastering these techniques, businesses can gain deeper insights and make data-backed decisions to stay competitive in their industries.

Conversion rate

The interaction between a customer, which wants to buy some goods or services, and a business is necessary to make a deal. This interaction may start differently: it can be a visit of offline or online shop, a call, or an app download. For some customers, this first step is the only one, and others proceed further to the deal. The conversion rate (CR) determines a share of customers that made a transition from one state to another. Example: if your shop has 300 daily visitors, and 75 of them make a purchase, the CR from visitors to buyers is $75/300 = 0.25$.

Let's calculate the one-day CR from visitors to buyers using the events table, several rows of which are given below. The table lists several types of action a customer may make in a shop, and the corresponding timestamps.

WITH visits AS
  (SELECT DISTINCT customer_id AS ids
   FROM EVENTS
   WHERE CAST(event_dt AS DATE) = '2022-11-15' -- selected date

     AND event_name = 'visit'),
     purchases AS
  (SELECT DISTINCT customer_id AS ids
   FROM EVENTS
   JOIN visits v ON ids = v.ids
   WHERE CAST(event_dt AS DATE) = '2022-11-15'
     AND event_name = 'purchase')
SELECT
  (SELECT COUNT(*)
   FROM purchases) /
  (SELECT COUNT(*)
   FROM visits) AS cr

We have counted all the purchases and visits for a given date and divided the former by the latter. Here is the result:

Marketing (product) funnels

Despite CR is a very useful metric, usually a business needs more advanced tool to determine how the sales are going, because there are several steps from a customer's interest to a deal. Let's imagine an online shop. To buy something, a user should go through the following steps: 1) open a shop's main page 2) open a page of needed item 3) add it to cart 4) proceed to checkout 5) place an order. One day 5000 customers visited a shop's main page, 3000 found goods they would like to buy, and 2000 added it to the cart. 1000 users opened the cart and 200 made a purchase.

Knowing how many customers we have on every step, we can calculate the share of customers that reached the certain step with respect to the initial quantity and to the previous step. In fact, we just calculate the CR several times. Such a sequence of CRs is called a product or marketing funnel as it is usually visualized as a funnel: (random image from google, should be replaced)

The funnel analysis allows us to determine if there are any pitfalls on every step of a customer's journey to a deal. Let's see how we can implement such an analysis in SQL. We will use the same events table assuming that we have the following event types listed there: 'visit', 'item_page_opened', 'item_added_to_cart', 'cart_opened', 'purchase'.

WITH visits AS
  (SELECT DISTINCT customer_id AS ids
   FROM EVENTS
   WHERE CAST(event_dt AS DATE) = '2022-11-15'
     AND event_name = 'visit'),
     pages_opened AS
  (SELECT DISTINCT customer_id AS ids
   FROM EVENTS
   JOIN visits v ON ids = v.ids
   WHERE CAST(event_dt AS DATE) = '2022-11-15'
     AND event_name = 'item_page_opened'),
     added_to_cart AS
  (SELECT DISTINCT customer_id AS ids
   FROM EVENTS
   JOIN visits v ON ids = v.ids
   WHERE CAST(event_dt AS DATE) = '2022-11-15'
     AND event_name = 'item_added_to_cart'),
     cart_opened AS
  (SELECT DISTINCT customer_id AS ids
   FROM EVENTS
   JOIN visits v ON ids = v.ids
   WHERE CAST(event_dt AS DATE) = '2022-11-15'
     AND event_name = 'cart_opened'),
     purchases AS
  (SELECT DISTINCT customer_id AS ids
   FROM EVENTS
   JOIN visits v ON ids = v.ids
   WHERE CAST(event_dt AS DATE) = '2022-11-15'
     AND event_name = 'purchase'),
     steps AS
  (SELECT 'visits' AS step,
          COUNT(*) AS quantity
   FROM visits
   UNION SELECT 'item_page_opened',
                COUNT(*)
   FROM pages_opened
   UNION SELECT 'added_to_cart',
                COUNT(*)
   FROM added_to_cart
   UNION SELECT 'cart_opened ',
                COUNT(*)
   FROM cart_opened
   UNION SELECT 'purchase',
                COUNT(*)
   FROM purchases)
SELECT *,
       quantity / LAG(quantity) OVER() AS step_cr
FROM steps

Here is the result:

Daily average users

Another useful metric – in fact, the family of metrics – is the number of unique users that use a product within a certain period. Common examples: daily average users (DAU), weekly average users (WAU), or monthly average users (MAU). These metrics are easy to calculate but yet useful business indicators:

SELECT CAST(event_dt AS DATE) AS date,
       COUNT(DISTINCT customer_id) AS dau
FROM EVENTS
WHERE CAST(event_dt AS DATE) BETWEEN '2022-11-10' AND'2022-11-12'
GROUP BY CAST(event_dt AS DATE)
ORDER BY CAST(event_dt AS DATE)

The WAU and MAU are calculated in the similar manner. We should only remember that as we should take into account unique users only, WAU and MAU are not equal to the sum of every included day DAUs.

Conclusion

In the ever-evolving landscape of business, harnessing the power of data is essential to drive success. SQL provides a robust platform to calculate and analyze key business metrics, enabling companies to evaluate their performance and optimize their strategies.

Throughout this exploration, we have discovered the significance of conversion rates, funnel analyses, and daily average users in assessing a company's growth and customer engagement. Armed with SQL knowledge, businesses can now leverage these metrics to uncover valuable insights and make data-driven decisions that propel them towards prosperity.

By integrating SQL into their data analysis toolkit, companies can continue to thrive and stay ahead in an increasingly competitive world. So, let's embrace the power of SQL and pave the way for business success through insightful data analysis.

Now we know how to calculate important business metrics – CR, funnel, and DAU/WAU/MAU – using SQL. Let's practice!