No items found.

# SQL for Marketers — Your Most Profitable Channels

Knowing how your customer acquisition costs compare to your customer lifetime value is a vital part of marketing. When split across channels, this data allows you to decide whether to double down on a channel or take resources away.

### Calculating Your Customer Acquisition Costs

To calculate your customer acquisition costs (CAC), you’ll need your marketing spend data for each channel and the number of customers per channel.

In addition to direct spend, we’ll include tool and labor costs in each channel’s marketing spend. This will make paid channel performance more comparable to organic channel performance.

We’ll start out by grabbing channel, spend and customer data in a query:

select
channel,
sum(daily_spend) as total_spend,
count(1) as customers,
where customer is true
group by 1

Our lead data is in a separate table from our marketing spend data, so we’ll join them using the channel column that each table has.

Now, for something new! To figure out the average customer acquisition cost per channel, we need to do a little math in our query:

select
channel,
sum(daily_spend) as total_spend,
sum(daily_spend) / count(distinct lead_id) as cac
where customer is true
group by 1

With sum(daily_spend) / count(distinct lead_id), we’re dividing our marketing costs by our number of customers per channel. This gives us our CAC per channel.

Here are our results:

We’re looking at the all time CAC for each channel. You’ll want to measure this over different time periods, though, to see how costs are changing.

There are many different ways to calculate Customer Lifetime Value (CLTV). Some are more complex than others.

For now, we’ll keep it simple and define CLTV as the average number of months a customer stays multiplied by the average monthly recurring revenue per customer.

Let’s grab our customer and revenue data from our customers table:

select
channel,
sum(payment_amount) as total_revenue,
sum(payment_amount) / count(distinct customer_id) as cltv
group by 1

With sum(payments) / count(distinct customer_id), we’re dividing our total revenue by number of customers.

This gives us the following table:

### Comparing Your CAC and CLTV

We’ve streamlined our CAC query and taken a couple things out of the select statement we don’t need:

select
channel,
sum(daily_spend) / count(distinct lead_id) as cac
where customer is true
group by 1

We’ve also streamlined our CLTV query:

select
channel,
sum(payment_amount) / count(distinct customer_id) as cltv
from payments join leads using (customer_id)
group by 1

To combine them, we’ll need to use a new tool: subqueries.

A subquery is a query nested within another query. We use them in the query below to manipulate our data and create the temporary tables, cac_table and cltv_table. The parent  select statement draws from cac_table and cltv_table to further filter our data.

In order to use data from both subqueries, we need to join them using their channel columns.

select
channel,
cac,
cltv,
cltv - cac as net_income
from (
select
channel,
sum(daily_spend) / count(distinct lead_id) as cac
where customer is true
group by 1
) cac_table join (
select
channel,
sum(payment_amount) / count(distinct customer_id) as cltv