Case and Left Join Problem
I am attempting to find the cost per lead, however they are on different tables. I am using the Case and Left Join Functions to do so.
The following Queries are working correctly. These are below:
SELECT
"Campaign",
COUNT("Id") AS 'total_leads'
FROM "Deals"
GROUP BY"Campaign"
| Campaign | total_leads |
1 |
| 885 |
2 | Attention Small Business | 69 |
SELECT
"Campaign Name",
SUM("Amount Spent") AS 'total_spent'
FROM "Campaign Insights (Facebook Ads)"
GROUP BY"Campaign Name"
| Campaign Name | total_spent |
1 | Cold... 3/13/24 | 5467.8 |
2 | Cold... 5/8/24 | 2672.97 |
3 | In Need? | 39.04 |
4 | Attention Small Business | 299.73 |
However, when I combine these using the Case and Join Function, the "Attention Small Business" Campaign is giving different values. I am unsure of the reasoning behind this. Any help is useful. Query and OutPut below.
SELECT
"Campaign Name",
SUM("Amount Spent") AS 'total_spent',
COUNT("Id") AS 'total_leads',
CASE
WHEN COUNT("Id") > 0 THEN SUM("Amount Spent") / COUNT("Id")
ELSE 0
END AS cost_per_lead
FROM "Campaign Insights (Facebook Ads)"
LEFT JOIN "Deals" ON "Campaign Name" = "Campaign"
GROUP BY "Campaign Name"
| Campaign Name | total_spent | total_leads | cost_per_lead |
1 | Attention Small Business | 20681.37 | 276 | 74.93 |
2 | In Need? | 39.04 | 0 | 0 |
3 | Cold...3/13/24 | 5467.79 | 0 | 0 |
4 | Cold...5/8/24 | 2672.96 | 0 | 0 |