Product

If you work in product, it's crucial to keep track of metrics to stay on top of product health. Let's take a look at some popular metrics to track and sample SQL queries:

Monthly Active Users (MAU)

MAU measures the number of unique users who have interacted with the product in the past month.

SELECT COUNT(DISTINCT user_id) as monthly_active_users
FROM user_activity
WHERE activity_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'

💡 Explanation

This query calculates MAU assuming there is a table called "user_activity" that tracks user activity on the product, with columns for "user_id" and "activity_date". This query counts the number of distinct user IDs that appear in the "user_activity" table in the current calendar month. The DATE_TRUNC function truncates the current date to the first day of the current month, and subtracting an interval of one month gives us the first day of the previous month. The WHERE clause filters the "user_activity" table to include only activity dates that occurred in the previous month or later, effectively giving us the count of monthly active users for the current month.

Daily Active Users (MAU)

DAU measures the number of unique users who have interacted with the product in the past day.

SELECT COUNT(DISTINCT user_id) as daily_active_users
FROM user_activity
WHERE activity_date = CURRENT_DATE

💡 Explanation

This query calculates DAU assuming that there is a table called "user_activity" that tracks user activity on the product, with columns for "user_id" and "activity_date". This query counts the number of distinct user IDs that appear in the "user_activity" table on the current date. The WHERE clause filters the "user_activity" table to include only activity dates that match the current date, effectively giving us the count of daily active users. Note that this query assumes that the activity_date column contains a date data type (not a datetime or timestamp data type) that matches the format of the CURRENT_DATE function, which may vary depending on the SQL dialect and database management system being used.

User retention rate

The percentage of users who continue to use the product over time.

WITH cohort AS (
  SELECT user_id, MIN(activity_date) as cohort_date
  FROM user_activity
  GROUP BY 1
),
active_users AS (
  SELECT DISTINCT user_id
  FROM user_activity
  WHERE activity_date BETWEEN 'cohort_date' AND 'cohort_date' + INTERVAL '30 days'
),
retained_users AS (
  SELECT a.user_id
  FROM active_users a
  JOIN user_activity b
  ON a.user_id = b.user_id
  AND b.activity_date BETWEEN a.cohort_date + INTERVAL '1 day' AND a.cohort_date + INTERVAL '30 days'
)
SELECT COUNT(DISTINCT retained_users.user_id) / COUNT(DISTINCT cohort.user_id) as retention_rate
FROM cohort
LEFT JOIN retained_users
ON cohort.user_id = retained_users.user_id

💡 Explanation

This query calculates user retention rate assuming there is a table called "user_activity" that tracks user activity on the product, with columns for "user_id" and "activity_date". This query calculates user retention rate as the proportion of users who are still active on the product 30 days after their initial activity date.

The query uses three common table expressions (CTEs) to create intermediate tables for each step of the calculation:

cohort defines the initial cohort of users and their respective cohort dates, which correspond to the earliest activity dates for each user.
active_users identifies the set of distinct user IDs who were active during the 30-day period following their respective cohort dates.
retained_users identifies the subset of active users who also had at least one activity date during the period from day 1 to day 30 after their respective cohort dates.

The final query calculates the retention rate as the ratio of the number of retained users to the total number of users in the cohort. Note that this query assumes that the activity_date column contains a date data type (not a datetime or timestamp data type) that matches the format of the date literals and INTERVAL expressions used in the query, which may vary depending on the SQL dialect and database management system being used.

Churn rate

The percentage of users who stop using the product over time.

WITH last_activity AS (
  SELECT user_id, MAX(activity_date) as last_activity_date
  FROM user_activity
  GROUP BY 1
),
inactive_users AS (
  SELECT user_id
  FROM last_activity
  WHERE last_activity_date < CURRENT_DATE - INTERVAL '30 days'
)
SELECT COUNT(DISTINCT inactive_users.user_id) / COUNT(DISTINCT last_activity.user_id) as churn_rate
FROM last_activity
LEFT JOIN inactive_users
ON last_activity.user_id = inactive_users.user_id

💡 Explanation

This query calculates the churn rate assuming there is a table called "user_activity" that tracks user activity on the product, with columns for "user_id" and "activity_date". This query calculates churn rate as the proportion of users who have not had any activity on the product for the past 30 days.

The query uses two common table expressions (CTEs) to create intermediate tables for each step of the calculation:

last_activity defines the last activity date for each user in the "user_activity" table.
inactive_users identifies the set of user IDs whose last activity date is more than 30 days before the current date.

The final query calculates the churn rate as the ratio of the number of inactive users to the total number of users in the "last_activity" table. Note that this query assumes that the activity_date column contains a date data type (not a datetime or timestamp data type) that matches the format of the CURRENT_DATE function and the INTERVAL expression used in the query, which may vary depending on the SQL dialect and database management system being used.

Average Revenue Per User (ARPU)

The average amount of revenue generated per user.

SELECT SUM(payment_amount) / COUNT(DISTINCT user_id) as arpu
FROM user_payments

💡 Explanation

This query calculates ARPU assuming there is a table called "user_payments" that tracks payments made by each user, with columns for "user_id" and "payment_amount". This query calculates ARPU as the total revenue generated from all payments divided by the number of unique users who made those payments. Note that this query assumes that the payment_amount column contains a numerical data type (such as integer or float) that represents the revenue generated by each payment. If the revenue is stored in a different currency, the query may need to include a currency conversion factor or use a separate table that maps each payment to a specific currency.

Lifetime Value (LTV)

The total revenue generated by a user over their entire lifetime.

WITH ltv_data AS (
  SELECT user_id,
         SUM(payment_amount) as revenue,
         MIN(payment_date) as first_purchase_date
  FROM user_payments
  GROUP BY 1
)
SELECT AVG(revenue * 12) as ltv
FROM ltv_data
WHERE first_purchase_date < DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '12 months'

💡 Explanation

Calculating LTV (lifetime value) of a customer can involve different methods and assumptions depending on the business model and data available. This example SQL query to calculate LTV is based on the assumption that the customer lifetime is defined as the period from the first purchase to the end of a 12-month period, and that the revenue generated by each customer is constant over that period.

This query first creates a common table expression (CTE) called ltv_data, which aggregates the total revenue generated by each user and the date of their first purchase.

The main query then calculates LTV as the average revenue per customer per month (i.e., the revenue divided by the number of months in the customer lifetime, which is assumed to be 12 in this case), for all users who made their first purchase more than 12 months ago. The DATE_TRUNC function is used to truncate the current date to the beginning of the current month, and the INTERVAL expression subtracts 12 months to get the date 12 months ago.

Note that this calculation assumes that the revenue generated by each customer is constant over the 12-month period, and that there is no churn or change in customer behavior during that period. In reality, LTV can be more complex to calculate and may require different assumptions and data sources, such as customer retention rates, purchase frequency, and customer acquisition costs.

Customer Acquisition Cost (CAC)

The cost of acquiring a new customer.

SELECT SUM(total_spent) / COUNT(DISTINCT new_customers) as cac
FROM marketing_data
WHERE period = '2022 Q1'

💡 Explanation

CAC (customer acquisition cost) is the cost of acquiring a new customer, and can be calculated in different ways depending on the business model and data available. This example SQL query to calculate CAC is based on the assumption that the total marketing spend and the number of new customers acquired during a specific time period are available in a table called "marketing_data".

This query calculates CAC as the total marketing spend divided by the number of unique new customers acquired during the specified time period (in this case, the first quarter of 2022). Note that this query assumes that the "marketing_data" table has columns for "total_spent" (the total amount spent on marketing during the period) and "new_customers" (the number of new customers acquired during the period).

If the marketing spend is allocated to different channels or campaigns, the query may need to include additional filtering or grouping clauses to calculate CAC per channel or campaign. Additionally, if the business model involves recurring revenue or subscription fees, the CAC calculation may need to account for the lifetime value (LTV) of the acquired customers, which can be calculated using a separate SQL query as shown in the previous answer.

Conversion Rate

The percentage of users who take a desired action, such as making a purchase or signing up for a subscription.

SELECT 100.0 * COUNT(DISTINCT users_who_took_action) / COUNT(DISTINCT users_who_visited) as conversion_rate
FROM website_data
WHERE period = '2022 Q1'

💡 Explanation

Conversion rate is the percentage of users who take a desired action, such as making a purchase or filling out a form, out of the total number of users who visit a website or use a service. This example SQL query calculates conversion rate based on the assumption that the number of users who visited a website and the number of users who took a desired action during a specific time period are available in a table called "website_data".

This query calculates conversion rate as the percentage of users who took the desired action (in this case, users_who_took_action) out of the total number of users who visited the website (in this case, users_who_visited) during the specified time period (in this case, the first quarter of 2022). The query uses the COUNT and DISTINCT functions to count the number of unique users who took the action and the number of unique users who visited the website during the period. Finally, the query multiplies the result by 100 to convert the decimal value to a percentage.

Note that this query assumes that the "website_data" table has columns for "users_who_visited" (the number of users who visited the website during the period) and "users_who_took_action" (the number of users who took the desired action during the period). If the desired action is different or requires a different type of tracking (e.g. tracking email opens or app installs), the query may need to be modified accordingly.

Cart Abandonment Rate

The percentage of users who add items to their shopping cart but do not complete the purchase.

SELECT 100.0 * COUNT(DISTINCT users_who_added_to_cart) / COUNT(DISTINCT users_who_completed_purchase) as cart_abandonment_rate
FROM purchase_data
WHERE period = '2022 Q1'

💡 Explanation

Cart abandonment rate is the percentage of users who add items to their cart but do not complete the purchase. This example SQL query calculates cart abandonment rate based on the assumption that the number of users who added items to their cart and the number of users who completed the purchase are available in a table called "purchase_data".

This query calculates cart abandonment rate as the percentage of users who added items to their cart (in this case, users_who_added_to_cart) out of the total number of users who completed the purchase (in this case, users_who_completed_purchase) during the specified time period (in this case, the first quarter of 2022). The query uses the COUNT and DISTINCT functions to count the number of unique users who added items to their cart and the number of unique users who completed the purchase during the period. Finally, the query multiplies the result by 100 to convert the decimal value to a percentage.

Note that this query assumes that the "purchase_data" table has columns for "users_who_added_to_cart" (the number of users who added items to their cart during the period) and "users_who_completed_purchase" (the number of users who completed the purchase during the period). If the desired action is different or requires a different type of tracking (e.g. tracking clicks on a "Buy Now" button), the query may need to be modified accordingly.

Average Order Value (AOV)

The average value of orders placed by users.

SELECT AVG(order_total) AS AOV
FROM orders
WHERE order_date >= '2022-01-01' AND order_date < '2023-01-01'

💡 Explanation

AOV (Average Order Value) is a metric that represents the average value of each order placed by a customer. This example SQL query calculates AOV based on the assumption that order data is stored in a table called "orders".

This query calculates AOV as the average of the "order_total" column in the "orders" table. The WHERE clause specifies the time period for which AOV should be calculated (in this case, all orders placed in 2022). The AVG function calculates the average order value for the specified time period.

Note that this query assumes that the "orders" table has a column for "order_total" (the total value of each order). If the table has a different column name for order value, the query may need to be modified accordingly.

Time to Conversion

The amount of time it takes for a user to convert, from their first interaction with the product to the point of purchase.

SELECT AVG(DATEDIFF(conversion_date, interaction_date)) AS time_to_conversion
FROM user_interactions
JOIN user_conversions ON user_interactions.user_id = user_conversions.user_id
WHERE user_interactions.interaction_date >= '2022-01-01' AND user_interactions.interaction_date < '2023-01-01'

💡 Explanation

Time to conversion is the time it takes for a user to convert after their first interaction with a product or service. This example SQL query calculates time to conversion based on the assumption that user interaction data is stored in a table called "user_interactions" and conversion data is stored in a table called "user_conversions".

This query calculates time to conversion as the average number of days between the user's first interaction and their conversion. The query uses the DATEDIFF function to calculate the difference between the conversion date and the interaction date in days. The JOIN clause combines the user interaction data with the user conversion data based on the user ID. The WHERE clause specifies the time period for which time to conversion should be calculated (in this case, all user interactions that occurred in 2022).

Note that this query assumes that the "user_interactions" table has a column for "user_id" (the unique identifier for each user) and "interaction_date" (the date and time of the user's interaction) and that the "user_conversions" table has a column for "user_id" (the unique identifier for each user) and "conversion_date" (the date and time of the user's conversion). If the tables have different column names for user ID, interaction date, or conversion date, the query may need to be modified accordingly.

Funnel Conversion Rates

The percentage of users who move from one stage of the sales funnel to the next.

WITH step1 AS (
    SELECT COUNT(DISTINCT user_id) AS total_users
    FROM step1_table
    WHERE step1_condition = true
), 
step2 AS (
    SELECT COUNT(DISTINCT user_id) AS total_users
    FROM step2_table
    WHERE step2_condition = true
), 
step3 AS (
    SELECT COUNT(DISTINCT user_id) AS total_users
    FROM step3_table
    WHERE step3_condition = true
), 
step4 AS (
    SELECT COUNT(DISTINCT user_id) AS total_users
    FROM step4_table
    WHERE step4_condition = true
) 

SELECT 
    step1.total_users AS "Step 1 Users",
    step2.total_users AS "Step 2 Users",
    step3.total_users AS "Step 3 Users",
    step4.total_users AS "Step 4 Users",
    ROUND((step2.total_users/step1.total_users)*100,2) AS "Step 1 to Step 2 Conversion Rate",
    ROUND((step3.total_users/step2.total_users)*100,2) AS "Step 2 to Step 3 Conversion Rate",
    ROUND((step4.total_users/step3.total_users)*100,2) AS "Step 3 to Step 4 Conversion Rate"
FROM step1
JOIN step2 ON step1.total_users = step2.total_users
JOIN step3 ON step2.total_users = step3.total_users
JOIN step4 ON step3.total_users = step4.total_users;

💡 Explanation

Funnel conversion rates are typically calculated by tracking the number of users or visitors that complete a series of steps or actions that lead to a desired outcome, such as making a purchase or signing up for a service. This example SQL query calculates funnel conversion rates based on the assumption that each step in the funnel is tracked in a separate table.

This query uses a series of CTEs (Common Table Expressions) to calculate the total number of users that completed each step in the funnel. Each CTE calculates the total number of users that completed a specific step, based on the criteria defined in the WHERE clause.

The main SELECT statement then combines the total number of users for each step with the conversion rates between each step, calculated as a percentage of users that completed the previous step. The conversion rates are calculated using the ROUND function to round the result to two decimal places.

Note that this query assumes that each step in the funnel is tracked in a separate table, and that each table has a "user_id" column to identify unique users and a condition column to track whether the user completed that step (e.g., "step1_condition" for step 1, "step2_condition" for step 2, etc.). If the tables have different column names or structures, the query may need to be modified accordingly.

User Engagement

The level of user interaction with the product, such as time spent on the site or number of pages viewed.

SELECT COUNT(DISTINCT user_id) AS engaged_users
FROM events
WHERE event_time >= DATE_SUB(NOW(), INTERVAL 30 DAY);

💡 Explanation

Calculating user engagement can be done in a number of ways, depending on how you define it. This example SQL query that calculates user engagement by counting the number of events a user has performed in the last 30 days.

In this example, we're selecting the count of distinct user IDs from the events table where the event time is within the last 30 days. This will give us the number of users who have performed at least one event in the last 30 days, which we can consider to be engaged.

Of course, this is just one way to define user engagement. Depending on your business and the types of events you're tracking, you may want to modify this query to better reflect your specific definition of user engagement.

Click-through Rate (CTR)

The percentage of users who click on a specific link or button.

SELECT (clicks / impressions) * 100 AS CTR
FROM (
  SELECT COUNT(*) AS impressions
  FROM ad_impressions
  WHERE campaign_id = '123'
) AS imp,
(
  SELECT COUNT(*) AS clicks
  FROM ad_clicks
  WHERE campaign_id = '123'
) AS cl;

💡 Explanation

Calculating click-through rate (CTR) requires data on the number of clicks and impressions for a particular item or campaign. This example SQL query calculates CTR based on those metrics.

In this example, we're selecting the number of clicks and impressions for a specific campaign (campaign_id = '123') and dividing the number of clicks by the number of impressions to get the CTR. We then multiply by 100 to get the percentage value.

Note that this query assumes that you have separate tables for ad impressions and ad clicks, and that you're able to match them up based on the campaign ID. Depending on your data structure and requirements, you may need to modify the query accordingly.

Retargeting Effectiveness

The percentage of users who return to the site after being retargeted with ads.

SELECT (converted_ret / total_ret) - (converted_new / total_new) AS retargeting_effectiveness
FROM (
  SELECT COUNT(DISTINCT user_id) AS total_ret
  FROM retargeting_events
  WHERE event_type = 'retargeted'
), (
  SELECT COUNT(DISTINCT user_id) AS converted_ret
  FROM retargeting_events
  WHERE event_type = 'converted' AND source = 'retargeted'
), (
  SELECT COUNT(DISTINCT user_id) AS total_new
  FROM conversion_events
  WHERE event_type = 'converted' AND source = 'new'
), (
  SELECT COUNT(DISTINCT user_id) AS converted_new
  FROM conversion_events
  WHERE event_type = 'converted' AND source = 'new'
);

💡 Explanation

Calculating retargeting effectiveness requires data on how many users were retargeted, how many of those users converted, and how many users would have converted without being retargeted. This example SQL query calculates retargeting effectiveness based on those metrics.

In this example, we're selecting the counts of total retargeted users, converted retargeted users, total new users, and converted new users, and using those to calculate the retargeting effectiveness.

Note that this query assumes that you have separate tables for retargeting events and conversion events, and that you're able to match them up based on the user ID and source. Depending on your data structure and requirements, you may need to modify the query accordingly.

Net Promoter Score (NPS)

The likelihood that a user will recommend the product to others.

SELECT
  (SUM(CASE WHEN score >= 9 THEN 1 ELSE 0 END) / COUNT(*)) -
  (SUM(CASE WHEN score <= 6 THEN 1 ELSE 0 END) / COUNT(*))
  AS nps_score
FROM survey_responses;

💡 Explanation

Net Promoter Score (NPS) is a metric used to measure customer loyalty and satisfaction. It's calculated based on a single question: "How likely are you to recommend [product/service] to a friend or colleague?" Respondents answer on a scale of 0-10, and are then classified into one of three categories: detractors (0-6), passives (7-8), and promoters (9-10).

In this example, we're selecting the count of responses that fall into the promoter (score 9-10) and detractor (score 0-6) categories, and using those to calculate the NPS score. We then subtract the percentage of detractors from the percentage of promoters to get the final NPS score.

Note that this query assumes that you have a table called survey_responses that contains the survey data, including the score field. Depending on your data structure and requirements, you may need to modify the query accordingly.

Customer Satisfaction Score (CSAT)

The level of satisfaction users have with the product.

SELECT
  AVG(satisfaction_score) AS csat_score
FROM survey_responses;

💡 Explanation

Customer Satisfaction (CSAT) is a metric used to measure how satisfied customers are with a specific product or service. It's typically calculated based on a survey question that asks customers to rate their satisfaction with a product or service on a scale of 1-5, where 1 is very unsatisfied and 5 is very satisfied.

In this example, we're selecting the average satisfaction score from the survey_responses table, which contains the survey data. The resulting score represents the overall CSAT score for the product or service being measured.

Note that this query assumes that you have a table called survey_responses that contains the survey data, including the satisfaction_score field. Depending on your data structure and requirements, you may need to modify the query accordingly.

Feature Adoption Rate

The percentage of users who use a specific feature or functionality of the product.

SELECT
  (COUNT(DISTINCT users_who_used_feature) / COUNT(DISTINCT total_users)) * 100 AS feature_adoption_rate
FROM user_activity
WHERE feature_name = 'feature_name';

💡 Explanation

Feature adoption rate is a metric used to measure the percentage of users who have adopted a particular feature of a product or service. This example SQL query calculates feature adoption rate.

In this example, we're selecting the count of distinct users who have used the feature of interest, and dividing it by the count of distinct total users, multiplied by 100 to get a percentage. We're filtering the results to only consider the activity related to the specific feature we're interested in, by using the WHERE clause to specify the feature_name.

Note that this query assumes that you have a table called user_activity that contains user activity data, including the users_who_used_feature and total_users fields. Depending on your data structure and requirements, you may need to modify the query accordingly.

Time to complete onboarding

The amount of time it takes for a user to complete onboarding

SELECT 
  AVG(DATEDIFF(end_time, start_time)) AS time_to_complete_onboarding
FROM user_activity
WHERE activity_type = 'onboarding'

💡 Explanation

Time to complete onboarding is a metric used to measure the average time it takes for users to complete the onboarding process of a product or service. This example SQL query that calculates time to complete onboarding.

In this example, we're selecting the average difference between the end_time and start_time fields for all activities that have an activity_type of onboarding. This gives us the average time it takes for users to complete the onboarding process.

Note that this query assumes that you have a table called user_activity that contains user activity data, including the start_time, end_time, and activity_type fields. Depending on your data structure and requirements, you may need to modify the query accordingly.

Error rate

The percentage of times users encounter errors or issues while using the product.

SELECT 
  COUNT(CASE WHEN error_encountered = true THEN 1 END) / COUNT(*) AS error_rate
FROM user_activity

💡 Explanation

To calculate the error rate, we need to count the number of times users encountered an error and divide it by the total number of actions performed by the users. This example SQL query that calculates the error rate.

In this example, we're selecting the count of rows where error_encountered is true, and dividing it by the total count of rows in the user_activity table. This gives us the error rate as a percentage.

Note that this query assumes that you have a table called user_activity that contains user activity data, including a boolean field called error_encountered that indicates whether an error was encountered during the activity. Depending on your data structure and requirements, you may need to modify the query accordingly.

PreviousSQL Templates NextFraud

Last updated 2 years ago

WITH cohort AS ( SELECT user_id, MIN(activity_date) as cohort_date FROM user_activity GROUP BY 1 ), active_users AS ( SELECT DISTINCT user_id FROM user_activity WHERE activity_date BETWEEN 'cohort_date' AND 'cohort_date' + INTERVAL '30 days' ), retained_users AS ( SELECT a.user_id FROM active_users a JOIN user_activity b ON a.user_id = b.user_id AND b.activity_date BETWEEN a.cohort_date + INTERVAL '1 day' AND a.cohort_date + INTERVAL '30 days' ) SELECT COUNT(DISTINCT retained_users.user_id) / COUNT(DISTINCT cohort.user_id) as retention_rate FROM cohort LEFT JOIN retained_users ON cohort.user_id = retained_users.user_id

WITH last_activity AS ( SELECT user_id, MAX(activity_date) as last_activity_date FROM user_activity GROUP BY 1 ), inactive_users AS ( SELECT user_id FROM last_activity WHERE last_activity_date < CURRENT_DATE - INTERVAL '30 days' ) SELECT COUNT(DISTINCT inactive_users.user_id) / COUNT(DISTINCT last_activity.user_id) as churn_rate FROM last_activity LEFT JOIN inactive_users ON last_activity.user_id = inactive_users.user_id

WITH ltv_data AS ( SELECT user_id, SUM(payment_amount) as revenue, MIN(payment_date) as first_purchase_date FROM user_payments GROUP BY 1 ) SELECT AVG(revenue * 12) as ltv FROM ltv_data WHERE first_purchase_date < DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '12 months'

SELECT AVG(DATEDIFF(conversion_date, interaction_date)) AS time_to_conversion FROM user_interactions JOIN user_conversions ON user_interactions.user_id = user_conversions.user_id WHERE user_interactions.interaction_date >= '2022-01-01' AND user_interactions.interaction_date < '2023-01-01'

WITH step1 AS ( SELECT COUNT(DISTINCT user_id) AS total_users FROM step1_table WHERE step1_condition = true ), step2 AS ( SELECT COUNT(DISTINCT user_id) AS total_users FROM step2_table WHERE step2_condition = true ), step3 AS ( SELECT COUNT(DISTINCT user_id) AS total_users FROM step3_table WHERE step3_condition = true ), step4 AS ( SELECT COUNT(DISTINCT user_id) AS total_users FROM step4_table WHERE step4_condition = true ) SELECT step1.total_users AS "Step 1 Users", step2.total_users AS "Step 2 Users", step3.total_users AS "Step 3 Users", step4.total_users AS "Step 4 Users", ROUND((step2.total_users/step1.total_users)*100,2) AS "Step 1 to Step 2 Conversion Rate", ROUND((step3.total_users/step2.total_users)*100,2) AS "Step 2 to Step 3 Conversion Rate", ROUND((step4.total_users/step3.total_users)*100,2) AS "Step 3 to Step 4 Conversion Rate" FROM step1 JOIN step2 ON step1.total_users = step2.total_users JOIN step3 ON step2.total_users = step3.total_users JOIN step4 ON step3.total_users = step4.total_users;

SELECT (clicks / impressions) * 100 AS CTR FROM ( SELECT COUNT(*) AS impressions FROM ad_impressions WHERE campaign_id = '123' ) AS imp, ( SELECT COUNT(*) AS clicks FROM ad_clicks WHERE campaign_id = '123' ) AS cl;

SELECT (converted_ret / total_ret) - (converted_new / total_new) AS retargeting_effectiveness FROM ( SELECT COUNT(DISTINCT user_id) AS total_ret FROM retargeting_events WHERE event_type = 'retargeted' ), ( SELECT COUNT(DISTINCT user_id) AS converted_ret FROM retargeting_events WHERE event_type = 'converted' AND source = 'retargeted' ), ( SELECT COUNT(DISTINCT user_id) AS total_new FROM conversion_events WHERE event_type = 'converted' AND source = 'new' ), ( SELECT COUNT(DISTINCT user_id) AS converted_new FROM conversion_events WHERE event_type = 'converted' AND source = 'new' );