SQL for Marketers: 7 Essential SQL Queries Every Ecommerce Marketer Must Know

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.

7 Essential SQL Queries for Ecommerce Marketers

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.

1. Top Products Sold Yesterday

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:

• See which products are gaining traction, allowing for swift marketing adjustments.
• Redirect your marketing resources more efficiently by focusing on what’s currently popular.

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:

• Highlight top sellers in ads: Use these insights to feature top-selling products in your online ads or email marketing. This can help increase click-through (CTR) and conversion rates (CVR).
• Allocate your budget more efficiently: Shift your advertising budget to focus on promotions for bestsellers. This can improve your return on ad spend (ROAS).
• Conduct A/B testing on product placement: Test different placements of the top-selling items on your site to determine which positions yield the highest visibility and conversion rates. This could involve testing whether these products perform better on the home page, category pages, or as featured items in search results.

2. Customer Acquisition Cost (CAC) by Channel

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:

• Reallocate your budget towards channels with the lowest CAC to maximize return on investment.
• Measure the impact of ad spend optimizations to see if they lowered acquisition costs.
• Plan your future marketing campaigns to exploit your most efficient channels further.

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:

• Adjust marketing tactics seasonally: Monitor CAC by channel to quickly pivot during peak shopping seasons, like Black Friday and Cyber Monday or back-to-school promotions, when ad costs may be unpredictable. If certain channels show a spike in cost without a corresponding increase in conversion, consider shifting your focus to more profitable channels.
• Shift your focus to the most cost-effective channels: Use CAC by channel data to see which channels bring in valuable customers at a lower cost. Then you can focus your efforts on the channels to maximize your ROAS.

3. ROAS by Product

Calculating your ROAS for each product enables you to fine-tune your ad spend for maximum profitability. With this metric, you can:

• Direct your advertising dollars towards products that have historically shown a high ROAS, ensuring that every dollar spent is an investment towards higher returns.
• Adjust pricing strategies or offer bundle discounts for low-ROAS products to enhance their appeal.
• Plan aggressive promotional activities for high-ROAS products to boost sales further.

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:

• Focus ad spend: Prioritize high-ROAS products in your digital marketing campaigns to enhance overall marketing ROI. This selective focus will maximize the impact of your marketing budget.
• Revamp marketing strategies: Consider revising marketing strategies for products with poor ROAS. This might involve exploring new marketing channels, revising ad creatives, or even enhancing product features or customer service.
• Forecast and plan: Analyze ROAS trends over time to forecast future performance and plan budgets accordingly. This helps you align your marketing strategies with changing consumer preferences and market trends.

4. Promotional Offer Conversion Rate

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:

• Identify which promotions yield the highest returns and allocate more resources to these successful strategies.
• Reevaluate low-converting promotional strategies to adjust offers or better target your intended audience.

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:

• Adjust email campaigns: For promotions that saw high conversion rates, design similar offers or re-send them to customers who didn’t purchase during the initial campaign. For promotions that had low conversion rates, use the insights to tweak the message or offer type before re-engaging your audience.
• Segment customer offers: Use a segmentation query to group customers based on their responsiveness to different types of promotions. For example, some segments respond better to discount offers, while others prefer buy-one-get-one (BOGO) deals.
• Evaluate influencer partnerships: If you create unique promotional offers for individual influencers, use this query to determine which ones convert best. Then, consider reallocating more of your influencer marketing budget to the most successful collaborations. Alternatively, revisit the terms or content of partnerships that don’t yield high conversions.

5. Customer Lifetime Value (CLV)

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:

• Determine how much to spend on acquiring new customers and retaining existing ones to ensure these costs do not exceed the projected lifetime value.
• Focus on nurturing high-value customers with targeted engagement and retention strategies to increase their CLV.
• Guide product development to meet the needs and preferences of your most profitable customer segments.

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:

• Target high-value customers: Use this data to develop marketing strategies catering to customers with a high CLV. For instance, if a customer segment typically buys mid-range products but has a high CLV, you can target them with upsell opportunities featuring premium products.
• Create a loyalty program tailored to increase average order value (AOV): For customers with a high CLV, offer incentives that encourage larger basket sizes, such as a point system where they earn more points per dollar when their order exceeds a certain threshold. This can motivate customers to add more items to their carts for higher reward levels.
• Optimize your marketing mix: Adjust your marketing mix to focus more on channels and strategies that attract customers with higher potential lifetime value. If marketing data reveals that customers acquired through paid search campaigns have a higher CLV than those from social media, increase your budget for paid search while scaling back on less profitable platforms.

6. Customer Retention by Cohort

Analyzing customer retention by cohort helps you understand how effectively your business maintains customer relationships over time. These insights allow you to:

• Identify successful elements in the cohorts that stick around for a long time.
• Tailor your offerings to meet the demands of your most loyal customers to boost satisfaction and retention.
• Forecast revenue more accurately based on the expected duration of your customer relationships to optimize long-term profitability.

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:

• Refine your customer retention strategy: Identify which monthly cohorts show the highest customer attrition, then target them with retention campaigns. For example, if customers acquired in May tend to drop off faster than other months, send special follow-up emails, loyalty offers, or personalized recommendations to increase engagement and reduce churn.
• Analyze seasonal buying patterns: This enables you to plan marketing campaigns that align with when customers are most likely to make their initial purchases. For example, cohorts that start purchasing in November might be more responsive to future Black Friday or Cyber Monday promotions.
• Optimize resource allocation: Focus on the acquisition channels and strategies that have historically led to high-retention cohorts. More targeted investment will improve efficiency and boost ROAS.

7. Ad Spend by Top Returned Products

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:

• Shift advertising funds from high-return products to those with better retention and conversion rates, maximizing the overall return on ad spend.
• Adjust strategies to reduce spend or enhance the product’s appeal to lower return rates.
• Set up a feedback loop to help align product development with customer behavior and expectations.

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:

• Shift your targeting strategy: Analyze the customer demographics that lead to high returns and adjust your campaigns to target audiences less likely to return products. Try refining the messaging or changing promotional channels to align more closely with your more loyal customers’ preferences.
• Improve high-return products: Use this data to identify the common characteristics of frequently returned products. This could guide product development teams to improve product quality, features, or usability, thus reducing future returns and increasing customer satisfaction.
• Iterate on product descriptions and visuals: To help customers make better-informed purchase decisions, include more accurate descriptions, clearer usage instructions, and more realistic product imagery. This could help reduce the mismatch between customer expectations and product reality, often a major reason for returns.

How to Easily Run SQL Queries With Moby

Explore Moby: Chat With 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.