Every click, every purchase, and every customer interaction holds the key to unlocking your brand’s potential.
With SQL (Structured Query Language), you no longer need to rely on guesswork or wait for reports from technical teams. Instead, you can use SQL for marketers to make data-driven decisions that are both timely and effective.
In this article, we’ll share the most useful SQL queries for ecommerce marketing and how you can put this data into action. Triple Whale lets you chat with your data so you don’t have to write SQL queries from scratch. But experimenting with beginner SQL will help you use Triple Whale to ask the right questions about your data.
Mastering certain SQL queries gives you the power to directly influence and optimize every aspect of your marketing strategy. Let’s dive into the most essential SQL queries that will help you proactively shape your campaigns for maximum impact and profitability.
Knowing exactly which products are flying off the shelves can dramatically enhance your marketing strategies. Once you’ve pinpointed yesterday’s top-selling items, you can quickly adapt your tactics to capitalize on current trends.
With this data, you can:
The SQL query you need pulls product IDs along with the total quantities sold the previous day, sorts this data in descending order to highlight top performers, and presents it in an easily digestible format.
Below is what this query could look like. Remember that your organization might use different names for databases and variables, so the SQL queries we share throughout this article are just templates.
SELECT product_id, SUM(quantity) AS total_sold
FROM sales
WHERE sale_date = CURRENT_DATE - INTERVAL 1 DAY
GROUP BY product_id
ORDER BY total_sold DESC;
Here are practical ways you can act on this data:
To optimize your marketing spend, you need to know the cost of acquiring a customer. By calculating the CAC for each marketing channel, you’ll learn where every dollar goes and how effectively it converts browsers into buyers.
With the ability to calculate the CAC by channel at any time, you can:
This SQL query calculates the CAC by dividing the total advertising spend by channel by the number of customers acquired through that channel.
Here’s the query:
SELECT channel, SUM(ad_spend) / COUNT(DISTINCT customer_id) AS cac
FROM marketing
GROUP BY channel;
A few examples of how you can leverage this data include:
Calculating your ROAS for each product enables you to fine-tune your ad spend for maximum profitability. With this metric, you can:
This SQL query joins sales data with advertising spend from multiple data sources and calculates the revenue to ad spend ratio for each product.
It looks like this:
SELECT product_id, SUM(revenue) / SUM(ad_spend) AS roas
FROM sales
JOIN advertising ON sales.product_id = advertising.product_id
GROUP BY product_id;
Here’s how you can use this vital data:
We all know that promotional offers are a surefire way to acquire new customers, win back old ones, and drive sales. But you must also evaluate their effectiveness to learn which strategies resonate best with your target audience.
Checking the conversion rates of promotional offers helps you:
This SQL query produces the conversion rate of your promotional offers by calculating the percentage of transactions involving promotional offers that resulted in a sale. Here’s what this query can look like:
SELECT
promotion_id,
promotion_type,
COUNT(*) AS total_offers,
SUM(CASE WHEN purchase_id IS NOT NULL THEN 1 ELSE 0 END) AS successful_conversions,
ROUND((SUM(CASE WHEN purchase_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) AS conversion_rate
FROM promotions
LEFT JOIN sales ON promotions.promotion_id = sales.promotion_id
GROUP BY promotion_id, promotion_type;
Some practical ways to put this data to use are:
Calculating the customer lifetime value (CLV) pinpoints precisely how much revenue each customer generates over their entire relationship with your brand. This helps you understand how much you can afford to spend on acquiring a customer to maintain profitability. Then you can:
This basic SQL query sums up all revenue from a customer and averages it across all customers. Here’s what it looks like:
SELECT customer_id, SUM(order_amount) AS lifetime_value
FROM orders
GROUP BY customer_id;
Because this SQL query only calculates CLV, you’ll likely need to use other queries to act on this metric. Here are some practical applications of this customer data:
Analyzing customer retention by cohort helps you understand how effectively your business maintains customer relationships over time. These insights allow you to:
A cohort analysis is typically grouped by a customer’s initial purchase month. But you can also create cohorts based on how they were acquired, which product customers first purchased, and other behaviors.
For the example below, we focused on cohorts based on the first purchase month, so that’s what the “cohort” variable represents. This SQL query correlates initial and subsequent transaction data to reveal the retention strength of each cohort. Here’s what that might look like:
SELECT cohort, COUNT(DISTINCT customer_id) AS total_customers,
COUNT(DISTINCT CASE WHEN month(purchase_date) > month(first_purchase_date) THEN customer_id ELSE NULL END) AS retained_customers,
ROUND((COUNT(DISTINCT CASE WHEN month(purchase_date) > month(first_purchase_date) THEN customer_id ELSE NULL END) * 100.0) / COUNT(DISTINCT customer_id), 2) AS retention_rate
FROM (
SELECT customer_id, DATE_FORMAT(MIN(purchase_date), '%Y-%m') AS first_purchase_date, purchase_date
FROM orders
GROUP BY customer_id
) AS customer_purchases
GROUP BY cohort;
Here are some actionable use cases for this data:
You need to monitor advertising spend on products with high return rates to identify any potentially inefficient marketing efforts. Understanding the correlation between ad spend and product returns allows you to:
This SQL query links ad spend to products and filters by those with the highest returns to determine if marketing funds are being wasted. It looks like this:
SELECT product_id, SUM(ad_spend) AS total_ad_spend
FROM advertising
JOIN returns ON advertising.product_id = returns.product_id
GROUP BY product_id
ORDER BY total_ad_spend DESC;
Here are some practical applications of this data:
After experimenting with the fundamentals of SQL, you might want to explore this programming language further. But you can skip learning SQL and still take advantage of its capabilities.
If you’re ready to dive deeper into customer segmentation, churn, attribution, campaign performance analysis, and other data-driven marketing tasks, then just chat with Moby. Ask any questions about your data, or start with our Prompts list. Learn more here.
Every click, every purchase, and every customer interaction holds the key to unlocking your brand’s potential.
With SQL (Structured Query Language), you no longer need to rely on guesswork or wait for reports from technical teams. Instead, you can use SQL for marketers to make data-driven decisions that are both timely and effective.
In this article, we’ll share the most useful SQL queries for ecommerce marketing and how you can put this data into action. Triple Whale lets you chat with your data so you don’t have to write SQL queries from scratch. But experimenting with beginner SQL will help you use Triple Whale to ask the right questions about your data.
Mastering certain SQL queries gives you the power to directly influence and optimize every aspect of your marketing strategy. Let’s dive into the most essential SQL queries that will help you proactively shape your campaigns for maximum impact and profitability.
Knowing exactly which products are flying off the shelves can dramatically enhance your marketing strategies. Once you’ve pinpointed yesterday’s top-selling items, you can quickly adapt your tactics to capitalize on current trends.
With this data, you can:
The SQL query you need pulls product IDs along with the total quantities sold the previous day, sorts this data in descending order to highlight top performers, and presents it in an easily digestible format.
Below is what this query could look like. Remember that your organization might use different names for databases and variables, so the SQL queries we share throughout this article are just templates.
SELECT product_id, SUM(quantity) AS total_sold
FROM sales
WHERE sale_date = CURRENT_DATE - INTERVAL 1 DAY
GROUP BY product_id
ORDER BY total_sold DESC;
Here are practical ways you can act on this data:
To optimize your marketing spend, you need to know the cost of acquiring a customer. By calculating the CAC for each marketing channel, you’ll learn where every dollar goes and how effectively it converts browsers into buyers.
With the ability to calculate the CAC by channel at any time, you can:
This SQL query calculates the CAC by dividing the total advertising spend by channel by the number of customers acquired through that channel.
Here’s the query:
SELECT channel, SUM(ad_spend) / COUNT(DISTINCT customer_id) AS cac
FROM marketing
GROUP BY channel;
A few examples of how you can leverage this data include:
Calculating your ROAS for each product enables you to fine-tune your ad spend for maximum profitability. With this metric, you can:
This SQL query joins sales data with advertising spend from multiple data sources and calculates the revenue to ad spend ratio for each product.
It looks like this:
SELECT product_id, SUM(revenue) / SUM(ad_spend) AS roas
FROM sales
JOIN advertising ON sales.product_id = advertising.product_id
GROUP BY product_id;
Here’s how you can use this vital data:
We all know that promotional offers are a surefire way to acquire new customers, win back old ones, and drive sales. But you must also evaluate their effectiveness to learn which strategies resonate best with your target audience.
Checking the conversion rates of promotional offers helps you:
This SQL query produces the conversion rate of your promotional offers by calculating the percentage of transactions involving promotional offers that resulted in a sale. Here’s what this query can look like:
SELECT
promotion_id,
promotion_type,
COUNT(*) AS total_offers,
SUM(CASE WHEN purchase_id IS NOT NULL THEN 1 ELSE 0 END) AS successful_conversions,
ROUND((SUM(CASE WHEN purchase_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) AS conversion_rate
FROM promotions
LEFT JOIN sales ON promotions.promotion_id = sales.promotion_id
GROUP BY promotion_id, promotion_type;
Some practical ways to put this data to use are:
Calculating the customer lifetime value (CLV) pinpoints precisely how much revenue each customer generates over their entire relationship with your brand. This helps you understand how much you can afford to spend on acquiring a customer to maintain profitability. Then you can:
This basic SQL query sums up all revenue from a customer and averages it across all customers. Here’s what it looks like:
SELECT customer_id, SUM(order_amount) AS lifetime_value
FROM orders
GROUP BY customer_id;
Because this SQL query only calculates CLV, you’ll likely need to use other queries to act on this metric. Here are some practical applications of this customer data:
Analyzing customer retention by cohort helps you understand how effectively your business maintains customer relationships over time. These insights allow you to:
A cohort analysis is typically grouped by a customer’s initial purchase month. But you can also create cohorts based on how they were acquired, which product customers first purchased, and other behaviors.
For the example below, we focused on cohorts based on the first purchase month, so that’s what the “cohort” variable represents. This SQL query correlates initial and subsequent transaction data to reveal the retention strength of each cohort. Here’s what that might look like:
SELECT cohort, COUNT(DISTINCT customer_id) AS total_customers,
COUNT(DISTINCT CASE WHEN month(purchase_date) > month(first_purchase_date) THEN customer_id ELSE NULL END) AS retained_customers,
ROUND((COUNT(DISTINCT CASE WHEN month(purchase_date) > month(first_purchase_date) THEN customer_id ELSE NULL END) * 100.0) / COUNT(DISTINCT customer_id), 2) AS retention_rate
FROM (
SELECT customer_id, DATE_FORMAT(MIN(purchase_date), '%Y-%m') AS first_purchase_date, purchase_date
FROM orders
GROUP BY customer_id
) AS customer_purchases
GROUP BY cohort;
Here are some actionable use cases for this data:
You need to monitor advertising spend on products with high return rates to identify any potentially inefficient marketing efforts. Understanding the correlation between ad spend and product returns allows you to:
This SQL query links ad spend to products and filters by those with the highest returns to determine if marketing funds are being wasted. It looks like this:
SELECT product_id, SUM(ad_spend) AS total_ad_spend
FROM advertising
JOIN returns ON advertising.product_id = returns.product_id
GROUP BY product_id
ORDER BY total_ad_spend DESC;
Here are some practical applications of this data:
After experimenting with the fundamentals of SQL, you might want to explore this programming language further. But you can skip learning SQL and still take advantage of its capabilities.
If you’re ready to dive deeper into customer segmentation, churn, attribution, campaign performance analysis, and other data-driven marketing tasks, then just chat with Moby. Ask any questions about your data, or start with our Prompts list. Learn more here.
Body Copy: The following benchmarks compare advertising metrics from April 1-17 to the previous period. Considering President Trump first unveiled his tariffs on April 2, the timing corresponds with potential changes in advertising behavior among ecommerce brands (though it isn’t necessarily correlated).
.webp)
.webp)
