Customer Acquisition Cost (CAC) is a metric that represents the average cost incurred to acquire a new customer. This indicator includes all costs directly and indirectly related to customer acquisition, such as advertising expenses, sales personnel costs, and various expenses associated with marketing activities.
To achieve sustainable growth for a company, it is essential to regularly measure and analyze this metric. In particular, monitoring it quarterly or before and after new marketing campaigns allows for proper evaluation of return on investment and, if necessary, course correction of strategies.
This metric is especially important for executives and sales/marketing managers. This is because they need to devise optimal customer acquisition strategies and make investment decisions within limited budgets. Especially for sales department managers, it is an indispensable indicator for evaluating the efficiency of sales activities and considering improvement measures.
The most important thing for sales is to increase revenue, but there are two ways to increase sales.
One is to increase sales from “existing customers”.
The other is to increase sales from “new customers”.
Also, when thinking about acquiring new customers, it’s important to consider how much marketing expense was incurred, or how much sales resource was allocated.
Let’s consider this using an example of a business where the average cost to acquire a new customer is 300,000 yen.
If the average annual revenue from one customer in this business is 100,000 yen, in the year this customer is acquired, the 300,000 yen cost of customer acquisition is offset, resulting in a loss of 200,000 yen.
Also, while there’s no guarantee that this customer will continue to pay the same amount for more than 4 years, it takes 4 years of consistent payment to recover the cost on a revenue basis.
As shown, the larger the “cost of customer acquisition”, the longer it takes to recover the expenses spent on customer acquisition.
Therefore, it’s important to calculate and compare the “cost of customer acquisition” and “revenue per customer” to understand whether the current customer acquisition efficiency is optimal.
“Customer Acquisition Cost” can be calculated as follows, based on the total cost spent on acquiring new customers and the number of new customers over a specific period:
In this case, we will calculate the monthly customer acquisition cost for each “customer segment” using the following two sets of data:
The “order data” represents one order per row, with columns including customer ID, order date, customer segment, sales, and other information.
The “cost data for new customer campaigns” represents the campaign costs for acquiring new customers for each month and customer segment.
In this case, we will calculate the monthly customer acquisition cost for each “customer segment” using the following steps:
For information on how to add order frequency information to order data, please refer to this link.
We’ll proceed assuming the calculation of order frequency has been completed. First, select “Aggregate” from the column header menu of “Order Date”.
When the aggregation dialog opens, select “Order Date” for the group and choose “month” for the date unit truncation.
Next, add “Customer Segment” to the group.
This process is to aggregate the number of new customers for each customer segment. If you don’t need to calculate metrics for each customer segment, this process is unnecessary.
Then, select “Customer ID” for the value and choose “Number of unique values that meet the condition” for the aggregation function.
When the condition setting dialog opens, specify “Purchase Frequency” for the column, “equals” for the operator, and “1” for the value.
Finally, set the new column name to “Number of New Customers” and execute.
This aggregates the number of new customers for each customer segment.
Next, we’ll join the order data with the campaign cost data.
Select “Join” from the column header menu of “Order Date”.
When the join dialog opens, select the new customer campaign cost data for the target data frame and select order date for the key column.
Then, add “Customer Segment” to the key columns.
At this point, multiple key columns are selected for joining to combine the “monthly” cost data for each “Customer Segment”. If you don’t need to calculate metrics for each group, just “Order Date” is sufficient as the key column for joining.
Check the results in the preview, and if there are no issues, click the execute button.
This compiles the monthly number of new customers and campaign costs (expenses) for each customer segment.
Now that we have all the necessary data, we can calculate the customer acquisition cost.
In the joined data, select “Create Calculation” > “Standard” from the column header menu of “Campaign Cost”.
When the “Create Calculation” dialog opens, enter
`Campaign Cost` / `Number of New Customers` in the
calculation editor.
Set the new column name to “Customer Acquisition Cost”, configure it to be created after the “last column”, and execute.
These steps allow us to calculate the customer acquisition cost for each customer segment on a monthly basis.
Once you’ve calculated the customer acquisition cost, you can easily visualize the trend of customer acquisition costs using this data.
If you want to calculate the customer acquisition cost using custom R commands with your order data, please refer to the following R code sample.
# Step 1: Group by customer ID and calculate order count
mutate_group(
# Group by customer ID
group_cols = c(`customer_id` = "customer_id"),
group_funs = c("none"),
# Sort by order date
sort_cols = c("order_date"),
sort_funs = c("none"),
# Calculate order count based on order date sequence (1st, 2nd, etc.)
`order_count` = dplyr::dense_rank(`order_date`)
) %>%
# Step 2: Convert order date to monthly units
# - Truncate date to "month" for monthly analysis
# - For annual analysis, change to unit = "year"
mutate(`order_month` = floor_date(`order_date`, unit = "month")) %>%
# Step 3: Aggregate new customer count by month (for annual analysis, aggregation will be by year)
group_by(`order_month`) %>%
summarize(
#- Count unique customer IDs for first-time orders (order count = 1)
`new_customers` = count_unique_if(`customer_id`, `order_count` == 1)
) %>%
ungroup() %>%
# Step 4: Join with campaign cost data
left_join(
`new_customer_campaign_costs`,
by = join_by(`order_month` == `campaign_month`)
) %>%
# Step 5: Calculate Customer Acquisition Cost (CAC)
mutate(`customer_acquisition_cost` = `campaign_expense` / `new_customers`)# Step 1: Group by customer ID and calculate order count
mutate_group(
# - Group by customer ID
group_cols = c(`customer_id` = "customer_id"),
group_funs = c("none"),
#- Sort by order date
sort_cols = c("order_date"),
sort_funs = c("none"),
#- Calculate order count based on order date sequence (1st, 2nd, etc.)
`order_count` = dplyr::dense_rank(`order_date`)
) %>%
# Step 2: Convert order date to monthly units
# - Truncate date to "month" for monthly analysis
# - For annual analysis, change to unit = "year"
mutate(`order_month` = floor_date(`order_date`, unit = "month")) %>%
# Step 3: Aggregate new customer count by month and customer segment (for annual analysis, aggregation will be by year)
group_by(`order_month`, `customer_segment`) %>%
summarize(
# - Count unique customer IDs for first-time orders (order count = 1)
`new_customers` = count_unique_if(`customer_id`, `order_count` == 1)
) %>%
ungroup() %>%
# Step 4: Join with segment-specific campaign cost data
left_join(
`new_customer_campaign_costs`,
by = join_by(
`order_month` == `order_date`,
`customer_segment` == `customer_segment`
)
) %>%
# Step 5: Calculate Customer Acquisition Cost
mutate(`customer_acquisition_cost` = `campaign_expense` / `new_customers`)