通过计算 TRUE/FALSE 语句来计算转化率 [英] Computing conversion rate by counting TRUE/FALSE statements

问题描述

抱歉，这听起来很基本；忍受我.

Sorry if this sounds very basic; bear with me.

我需要确定 3 个广告的转化率，每个广告代表一个产品；这就是订阅除以点击广告的人数.

I need to determine the conversion rate of 3 ads, each representing a product; that would be subscription divided by the number of people who clicked the ad.

列:

• person_id - 人的唯一标识符
• 日期 - 向他们展示广告的日期
• ad_id - 广告内容:ad_1_product1、ad_2_product2 或 ad_3_product3
• 点击(真/假) - 点击了广告
• signed_up - (TRUE/FALSE) 创建了一个帐户
• subscribed (TRUE/FALSE) - 开始付费订阅

我将 clicked、signed_up 和 subscribed 设置为布尔值.

I set clicked, signed_up and subscribed as boolean.

我的代码:

SELECT ad_id, (count(subscribed) / count(clicked)) as CR
FROM  videoadcampaign
WHERE subscribed = 'TRUE' AND clicked = 'TRUE'
GROUP BY ad_id;

当然，上面的代码给了我 1 的比率，因为 SQL 仍在计算总数并由于这些条件除以相同的数字.

Of course, the code above gives me a ratio of 1, because SQL is still counting the total and dividing by the same number because of those conditions.

我完全被困住了.

我还需要计算点击和已签名的其他 KPI，因此过滤这些布尔值并将它们放入比率是我需要做的核心.

I will also need to calculate other KPIs for clicks and signed_up, so filtering those booleans and put them into a ratio is the core of what I need to do.

有没有办法让 SQL 计算 CR = SUBS (TRUE)/SUBS (TRUE + FALSE) [或总数]，然后按 CLICK = TRUE 进行过滤?

Is there a way I can tell SQL to compute CR = SUBS (TRUE) / SUBS (TRUE + FALSE) [or total count] and then filter by CLICK = TRUE?

非常感谢您的帮助！

推荐答案

这取决于你的数据库，但一般的概念是:

It depends on your database, but the general notion would be:

SELECT ad_id,
       (SUM(CASE WHEN subscribed = 'TRUE' THEN 1.0 ELSE 0 END) /
        SUM(CASE WHEN clicked = 'TRUE' THEN 1 ELSE 0 END)
       ) as CR
FROM  videoadcampaign
GROUP BY ad_id;

在许多数据库中，如果列是整数(0 = false，1 = true)，您可以执行以下操作:

In many databases, you can do something like this if the columns are integers (0 = false, 1 = true):

SELECT ad_id, SUM(subscribed) / SUM(clicked) as CR
FROM  videoadcampaign
WHERE clicked = 'TRUE'
GROUP BY ad_id;

甚至:

SELECT ad_id, AVG(subscribed) as CR
FROM  videoadcampaign
WHERE clicked = 'TRUE'
GROUP BY ad_id;

这篇关于通过计算 TRUE/FALSE 语句来计算转化率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！