Unit economics: ROI, CAC, LTV

In the movie "Moneyball," Billy Beane, the general manager of the Oakland Athletics baseball team, uses analytics to make data-driven decisions about player acquisitions. He aims to get the maximum performance for the least amount of money.

For example, he targets players who have high on–base percentages but may be undervalued by traditional scouting methods. The success of these cost–effective acquisitions is a representation of one of the key indicators in business – ROI. As a result of implementing this and many other metrics (like CAC, and LTV) Oakland Athletics managed to win 20 consecutive games for the first time in the history of the league.

In this topic, we will talk about ROI, CAC, and LTV, which are the fundamental metrics in business analytics that provide valuable insights into the efficiency and effectiveness of various business activities

ROI calculation using SQL

Definition of ROI:

ROI, or Return on Investment, is a financial metric used to evaluate the profitability or effectiveness of an investment. It's calculated by dividing the net gain or benefit from the investment by the initial cost of the investment, expressed as a percentage. In simple terms, it tells you how much return you're getting compared to what you put in.

In other words, ROI (Return on Investment) is like your business's report card for how smartly it's spending money. It tells you whether the dollars you put into something are giving you more dollars back or if you're throwing them into a black hole.

Here's why it's super important:

Imagine you're running a lemonade stand. You decide to spend 50 dollars on a fancy new lemon squeezer. As the month passes, you start looking at your sales and see that you've made an extra 100 dollars from selling more lemonade thanks to that squeezer you bought earlier. Your ROI is ${(100 – 50)\over 50}$, which equals 1 or 100%. That means for every dollar you spent on that squeezer, you got an extra dollar back. Sounds good, doesn't it?

But if you spent 100 dollars on a golden lemon sign in front of your shop and only made an extra 20 dollars in sales, your ROI is ${(20 – 100)\over 100}$, which equals –0.8 or –80%. Oops! You lost money on that investment because it didn't give you a good return.

So, ROI helps you figure out which investments are winners (they make you more money than you spent) and which are losers (they make you less money or even none at all). It's like a business GPS that guides you toward the profitable path. That's why it's a critical metric for businesses – it keeps them from wasting their hard–earned cash on things that don't pay off.

ROI Formula

The ROI formula, $ROI={(Gain \ from \ investment\ – \ Cost \ of \ investment )\over \ Cost \ of \ investment}$, is a fundamental calculation used in finance and business to assess the performance of investments. It quantifies the return achieved relative to the initial cost of the investment. This metric is expressed as a percentage, providing a clear indicator of the efficiency and profitability of a specific venture or expenditure.

• Gain from Investment: This represents the additional value, revenue, or profit generated by the investment. It's essentially the positive outcome or returns obtained from allocating resources into a particular initiative.

• Cost of Investment: This accounts for the initial financial outlay associated with the investment. It encompasses expenses such as procurement costs, marketing expenditures, or any other relevant financial commitment linked to the investment.

Using this formula, you can evaluate the effectiveness of various investments and compare their performance. A high ROI indicates a more profitable endeavor, while a low or negative ROI suggests that the investment may not be delivering the expected returns.

Applying ROI in SQL:

In SQL (Structured Query Language), you can calculate ROI by manipulating and analyzing data within a database.

Let's assume you have a table named investments with columns investment_id, gain, and cost, representing the unique ID, gain from investment, and cost of investment respectively.

SELECT 
  investment_id,
  gain,
  cost,
  ((gain - cost) / cost) * 100 AS roi_percentage
FROM 
  investments;

In this example, the SQL query calculates ROI by subtracting the cost from the gain, dividing it by the cost, and then multiplying by 100 to get a percentage.

Calculating CAC with SQL

Customer Acquisition Cost (CAC) is like counting the cost of getting a new buddy to join your club (being in your club means being your customer). Imagine you're selling lemonade. If you spend 20 dollars on ads, flyers, and fancy lemonade signs, and you get 10 new customers, your CAC is 2 dollars per customer. It's like saying, "I spent two bucks to get each new friend hooked on my lemonade."

Now, ROI (Return on Investment) is the high–five you give yourself when you get that those 2 dollars you spent on getting each new customer turned into 10 dollars in sales. It's like saying, "I made five times the money back for every dollar I spent!"

Why does CAC matter? Well, if your CAC is too high (like 5 dollars per customer), it's like throwing away money. You're spending more to get customers than they're giving back. But if your CAC is low and your ROI is high, you're turning a nice profit and your business is rocking!

So, CAC is all about figuring out if you're spending wisely to get new customers, while ROI tells you if that spending is turning into a sweet payday. Keeping an eye on both helps you run your business smarter.

The mathematical formula for CAC: $CAC={Total \ Sales \ and\ Marketing \ Costs \over \ Number \ of \ New \ Customers \ Acquired}$.

The CAC formula is straightforward: $CAC={Total \ Cost \ of\ Acquiring\ Customers \over \ Number\ of \ Customers \ Acquired}$.

This means you divide the total amount spent on acquiring customers by the number of new customers gained.

Implementing CAC Calculation in SQL:

Let's assume you have a table named marketing_expenses with columns campaign_id, cost, and customers_acquired, representing the unique ID for a marketing campaign, the cost of the campaign, and the number of customers acquired respectively.

SELECT 
  campaign_id,
  cost,
  customers_acquired,
  (cost / customers_acquired) AS cac
FROM 
  marketing_expenses;

In this example, the SQL query calculates CAC by dividing the cost of the campaign by the number of customers acquired.

Analyzing customer lifetime value (LTV) with SQL

Customer Lifetime Value (LTV) is like knowing the total love your best friend brings into your life.

Let's say you run a pizza shop. You've got a regular customer whose name is Andrey. Andrey comes in every week and spends about 20 dollars on pizza. Now, let's imagine Andrey sticks around for 3 years before moving to another town. Over those 3 years, Andrey will have spent roughly 3,120 dollars (20 bucks a week x 52 weeks x 3 years). That 3,120 dollars is Andrey's Lifetime Value to your pizza business.

LTV is like realizing how much long–term happiness a customer brings. It's not just about one purchase; it's about all the visits and all the orders. So, keeping customers like Andrey happy is really important. They're like your golden ticket to a successful business!

LTV Formula

The Customer Lifetime Value (LTV) formula helps businesses understand the total value a customer brings over their entire relationship with the company. It's calculated by considering the average purchase value, the frequency of purchases, and the expected duration of the customer relationship.

Here's the LTV formula:

$LTV= Average \ Purchase \ Value \times Purchase\ Frequency \times Customer \ Lifespan$

• Average Purchase Value: This is the average amount of money a customer spends on each transaction with your business.

• Purchase Frequency: This represents how often a customer makes a purchase within a given period (like a month or a year).

• Customer Lifespan: This is the expected duration of the customer's relationship with your business. It's important to note that this isn't always a precise figure, as it depends on factors like customer behavior and market trends.

For example, if on average a customer spends about 50 dollars per purchase, shops with you twice a month, and is expected to stay with your business for 2 years, the LTV would be calculated as follows:

$LTV=50 \times2\times24=2400$

This means, on average, this customer is projected to bring in 2400 dollars in revenue over their entire relationship with your business.

Knowing LTV is crucial because it helps you focus on keeping those long–term customers happy. It's like recognizing your most loyal supporters and making sure they stick around for a good, long while. That's why LTV is a big deal in business – it helps you build strong and lasting relationships with your customers!

LTV Analysis Using SQL

To calculate and analyze Customer Lifetime Value (LTV) using SQL, you'll need relevant data in your database, including customer transactions, purchase dates, and customer IDs. Below are some practical examples of SQL queries to help you calculate and analyze LTV:

Assumption:

• You have a table named transactions with columns customer_id, purchase_amount, and purchase_date.

Example 1: Calculating Average Purchase Value

SELECT 
  AVG(purchase_amount) AS avg_purchase_value
FROM 
  transactions;

It calculates the average purchase value, which is part of the LTV calculation.

Example 2: Calculating Purchase Frequency

SELECT 
  COUNT(DISTINCT customer_id) / COUNT(DISTINCT DATE(purchase_date)) AS purchase_frequency
FROM 
  transactions;

This query finds the purchase frequency, indicating how often customers make purchases.

Example 3: Calculating Customer Lifespan

SELECT 
  DATEDIFF(MAX(purchase_date), MIN(purchase_date)) AS customer_lifespan
FROM 
  transactions;

With this query, we determined the customer lifespan, or how long a customer has been active.

Example 4: Calculating LTV

SELECT 
  AVG(purchase_amount) * (COUNT(DISTINCT customer_id) / COUNT(DISTINCT DATE(purchase_date))) * DATEDIFF(MAX(purchase_date), MIN(purchase_date)) AS ltv
FROM 
  transactions;

In the end, we combined the previous metrics to calculate the LTV for the entire customer base.

Conclusion

Let's remind ourselves what we have learned today:

ROI tells you if you're spending your cash wisely or tossing it into a black hole. CAC is about getting new friends (customers) without breaking the bank. And LTV? It's like recognizing your ride–or–die guys who stick around and bring in the good vibes (and most importantly cash!) in the long term.

In summary, these metrics are pivotal tools that, when used effectively, guide businesses toward making informed decisions. Much like Billy Beane's data–driven decisions in "Moneyball" revolutionized baseball, leveraging ROI, CAC, and LTV can lead to the game–changing outcomes in the world of business.