Periscope Data
The world’s fastest visualization platform for data analysts.

SQL for Marketers — Your Most Profitable Channels

March 26, 2015

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,
from leads join spend on leads.channel = spend.channel
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, 
  count(distinct lead_id) as customers,
  sum(daily_spend) / count(distinct lead_id) as cac
from leads join spend on leads.channel = spend.channel
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.

Calculating Your Customer Lifetime Value

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 
from payments join leads on payments.company = leads.company
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
from leads join marketing_spend 
on leads.channel = marketing_spend.channel
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
  from leads join spend on leads.channel = spend.channel
  where customer is true
  group by 1
) cac_table join (
  select 
    channel, 
    sum(payment_amount) / count(distinct customer_id) as cltv 
  from payments join leads on payments.company = leads.company
  group by
) cltv_table on cac_table.channel = cltv_table.channel

Here are our results:

SEO turns out to be our most profitable channel, while PR is our least. It’s time to take resources away from PR and pour them into SEO!

As you improve your SQL skills, be sure to try writing more complex CLTV calculations in SQL.

Haven't tried Periscope Data yet?
Start a trial and we’ll send you one of our famous coffee mugs.
Read More
Haven’t tried Periscope Data yet?