SQL for Marketing: 10 Real Queries You'll Use Every Week

When I first started hiring marketing analysts at Jobsolv, I gave every candidate a SQL test. Not a textbook exercise with perfectly normalized tables, but a messy real-world scenario with campaign data, user events, and revenue tables that actually looked like what they would face on day one. About 80 percent of candidates who claimed SQL proficiency on their resume could not write a basic window function. That experience shaped how I now train every analyst who joins my team.

SQL is the backbone of marketing analytics. With 941,700 market research analyst jobs in 2024 and the data analytics market projected to reach $402.70 billion by 2032, SQL fluency is not optional. It is the single skill that appears in virtually every marketing analyst job description I have written or reviewed. The median salary for market research analysts sits at $76,950 according to BLS, but analysts with strong SQL skills consistently land in the higher brackets, often above $100,000.

Here are the 10 SQL queries I teach during the first month of onboarding. These are not toy examples. They are the actual patterns you will use every single week in a marketing analytics role.

Key Takeaways

SQL is the most in-demand technical skill for marketing analysts. The 10 queries covered here handle campaign performance analysis, funnel conversion tracking, cohort retention, revenue attribution, customer segmentation, time-series trending, anomaly detection, channel comparison, LTV calculation, and executive reporting. Master these patterns and you can handle 90 percent of the SQL work in any marketing analytics role.

Query 1: Campaign Performance Breakdown With ROI

The first query every marketing analyst needs is a campaign performance summary that calculates true ROI. This query joins your campaigns table with your spend table and conversions table to produce a single view showing campaign name, total spend, total conversions, revenue generated, cost per acquisition, and return on ad spend. The key here is using LEFT JOINs so you can see campaigns that spent money but generated zero conversions. As a hiring manager, I always ask candidates why a LEFT JOIN matters here instead of an INNER JOIN. The ones who understand that you need to see the failures, not just the successes, are the ones I hire.

The pattern uses GROUP BY campaign_name with SUM aggregations on spend and revenue columns, then a calculated field for ROAS as SUM(revenue) divided by NULLIF(SUM(spend), 0). That NULLIF prevents division by zero errors, which is a detail that separates production-ready SQL from tutorial SQL.

Query 2: Marketing Funnel Conversion Rates by Stage

This query uses a CTE (Common Table Expression) to calculate step-by-step conversion rates through your marketing funnel. You start by counting users at each stage: page view, signup, activation, and purchase. Then you calculate the conversion rate between each consecutive stage. Having trained analysts from entry-level to senior, I can tell you that the CTE approach is dramatically more readable than nested subqueries, and readability matters when your VP of Marketing asks you to explain the numbers in a meeting. The query should also include a date filter and a source filter so you can compare funnel performance across channels and time periods.

Query 3: Weekly Cohort Retention Analysis

Retention analysis is where window functions become essential. This query groups users by their signup week, then tracks what percentage return in week 1, week 2, week 4, and week 8. The pattern uses DATE_TRUNC to normalize signup dates to week boundaries, then a self-join or window function to check for activity in subsequent weeks. When I was building Jobsolv, this exact query told us that users who completed three job applications in their first week had 4x higher 30-day retention. That single insight reshaped our entire onboarding flow.

Queries 4-6: Revenue Attribution, Customer Segmentation, and Trending

Query 4 handles multi-touch revenue attribution using window functions like FIRST_VALUE and LAST_VALUE to assign credit to the first and last marketing touchpoints before a conversion. This is one of the most valuable skills for a marketing analyst because it directly answers the question every CMO asks: where should we spend the next dollar. Query 5 builds customer segments using CASE statements combined with aggregations, grouping customers into tiers like high-value, mid-value, and at-risk based on recency, frequency, and monetary value. Query 6 creates a time-series trend analysis using LAG and LEAD functions to calculate week-over-week and month-over-month growth rates for key metrics. These three queries together form the analytical backbone of any marketing team.

Queries 7-8: Anomaly Detection and Channel Comparison

Query 7 uses a rolling average with window functions to flag days where a metric deviates more than two standard deviations from its 30-day moving average. As a startup founder who also hires analysts, I can tell you that the ability to automatically detect when something breaks in your marketing data is worth its weight in gold. Catching a tracking issue 24 hours earlier can save thousands of dollars in wasted ad spend. Query 8 builds a comprehensive channel comparison that normalizes metrics across different scales. It compares paid search, organic, social, email, and direct channels on cost efficiency, conversion quality, and customer lifetime value. The key SQL technique here is using PARTITION BY in your window functions to calculate percentile ranks within each channel.

Queries 9-10: LTV Calculation and Executive Dashboard Query

Query 9 calculates customer lifetime value by acquisition channel, which is arguably the most important metric in marketing analytics. It combines user-level revenue data across all time periods, divides by user count per channel, and applies a retention-adjusted projection. I have mentored dozens of analysts on this calculation, and the most common mistake is using average order value multiplied by purchase frequency without accounting for churn. The SQL pattern uses a subquery to calculate per-user totals, then aggregates at the channel level. Query 10 is your executive reporting query that pulls together all key metrics into a single weekly snapshot: total spend, total revenue, blended ROAS, new customers acquired, retention rate, and LTV. This query uses multiple CTEs to build up each metric independently, then joins them in a final SELECT. With the BLS projecting 87,200 new openings annually for market research analysts through 2034, mastering these 10 queries positions you ahead of the majority of candidates in this growing field.

How to Practice These Queries on Real Marketing Data

You do not need a company database to practice. Google BigQuery offers free public datasets including the Google Analytics sample dataset and the Google Ads transparency data. Set up a free BigQuery sandbox, load these datasets, and start writing queries. Another excellent option is Mode Analytics, which provides a free SQL editor with sample datasets specifically designed for marketing analytics practice. The important thing is to practice with messy, real-world data rather than perfectly clean tutorial datasets. When 42 percent of HR pros spend less than 10 seconds on an initial resume review, being able to demonstrate genuine SQL skills in a portfolio project or technical interview is what gets you past that initial screen.

Frequently Asked Questions

Do marketing analysts really use SQL every day?

In my experience as a hiring manager, yes. Every analyst on my team writes SQL at least three to four times per week, and most use it daily. Even with tools like Looker and Tableau that have visual query builders, you will inevitably need to write custom SQL for complex analyses that the GUI cannot handle. The analysts who earn toward the top of the $144,610 range are the ones who can write production-quality SQL without hand-holding.

Which SQL dialect should marketing analysts learn?

Start with PostgreSQL or BigQuery SQL, as these are the most common in marketing analytics environments. The core syntax is nearly identical across dialects. The differences are mainly in date functions and some window function syntax. If you can write proficient PostgreSQL, you can adapt to any other dialect in a day. BigQuery is particularly worth learning because so many marketing teams run their analytics on Google Cloud.

How long does it take to learn SQL for marketing analytics?

With focused daily practice of 30 to 60 minutes, most people can write competent marketing analytics SQL within 6 to 8 weeks. The basics like SELECT, WHERE, GROUP BY, and JOINs take about two weeks. Window functions and CTEs take another two to three weeks. The remaining time is spent on developing fluency with real marketing datasets. I have seen career changers go from zero SQL knowledge to passing technical interviews in under 90 days using this approach.