In 2012, Jonathan Rosenberg, former SVP of Products of Google, wrote that in the future every business decision would be made by those who know how to read the data. He did not refer to data scientists, but to marketers, product managers and commercial directors. Today, almost three quarters of CMOs indicate “data literacy” as the missing competence in their teams, and the biggest barrier is the SQL language.
I’ve seen marketing teams lose dozens of hours a week wAIting for answers from the data team for simple questions like “how many new customers have we taken this month from Google?” or “what is the ROAS of the Black Friday campAIgn?” This addiction is expensive and frustrating, but avoidable.
SQL is not a programmer language, but the tool to query the databases. Here I present 15 ready queries covering 90% of the questions that a marketing team poses weekly. They are written for BigQuery, but easily adaptable to PostgreSQL, Snowflake or Redshift. They present typical tables of an e-commerce or SaaS, such as “orders”, “sessions”, “marketing_spend”, “emAIl_campAIgns” and others.
Real problem
The difficulty in making quick and informed decisions stems from the lack of autonomy in accessing and analysing data. WAIting for the data team slows down the decision-making process, increases costs and limits the ability to react to the market in real time.
Conceptual model
Learn how to write effective SQL queries allows you to transform raw data into operable insights. Queries use advanced functions such as window functions (LAG, LEAD, ROW_NUMBER), aggregations, and segmentations to analyze key metrics such as revenue, CAC, retention, ROAS, conversion funnel, churn, and more.
Strict formalisation
The queries presented are built with Common Table Expressions (CTE) for modularity and clarity. They use analytical functions for time comparisons, rankings and percentili. For example, the query for revenue per channel uses LAG() to compare the current month with the previous one and calculate the percentage variation. The query on churn scoring combines recency, frequency and monetary value in a composite score to identify customers at risk.
Example or case study
The query on the conversion funnel (Query 7) analyzes the steps from the page view to the purchase, calculating drop-offs and identifying the step with the greatest loss of users. This allows you to focus the optimization efforts on the most critical steps of the customer path.
The historical CLV query (Query 11) measures the aveRAGe and median value for acquisition cohorts, highlighting customer quality trends over time. This helps validate acquisition strategies and retention.
Lab / exercise
Basic level: Activate BigQuery export to GA4 and run Query 7 on the conversion funnel. Edit parameters and view results.
Intermediate Level: Adapt Query 1 (Revenue for Channel) and Query 4 (ROAS for CampAIgn) to your advertising data. Save and automate queries with Scheduled Queries.
Research-grade level: Build a multi-touch attribute model (Query 12) and compare results with linear and time-delay models.
Datasets and recommended materials: e-commerce or SaaS datasets with order tables, sessions, marketing expenses, GA4 events. BigQuery documentation and GinnyTech tutorials on advanced SQL.
Typical error to avoid
Thinking that SQL is just for programmers. It is actually an accessible and basic analysis tool for decision makers. Another mistake is relying exclusively on pre-packaged reports without exploring the data with custom queries, thus losing critical insights.
Quiz or checkpoint
- Which SQL function allows you to compare consecutive line values? (Answer: LAG), Why is it important to automate reporting queries?, How do you interpret a high score in churn scoring?
Summary table: 15 queries and when to use them
| # | Query | Main Metric | Ideal Frequency | Use case |
|---|---|---|---|---|
| 1 | Revenue for Canal | Revenue YoY | Weekly | Budget allocation |
| 2 | CAC for Channel | Customer Acquisition Cost | Monthly | Channel efficiency analysis |
| 3 | Cohort Retention | Retention Rate | Weekly | Product monitoring |
| 4 | ROAS for CampAIgn | Return on Ad Spend | DAIly | Optimization of campAIgns |
| 5 | Top Landing Page | Conversion Rate | Weekly | Prioritization A/B test |
| 6 | AOV Trend | AveRAGe Order Value | Weekly | Pricing strategy |
| 7 | Funnel Analysis | Drop-off Identification | Weekly | UX Optimization |
| 8 | Churn Scoring | Churn Risk | Weekly | Targeting retention |
| 9 | Best/Worst Sellers | Product Revenue | Monthly | Inventory management |
| 10 | EmAIl Performance | Revenue by EmAIl | Weekly | EmAIl marketing strategy |
| 11 | Historical CLV | Customer Lifetime Value | Monthly | Acquisition validation |
| 12 | Attribution Multi-Touch | Channel Credit Allocation | Monthly | Optimization mix marketing |
| 13 | RFM Segmentation | Segment Health | Monthly | Targeting customers |
| 14 | Device Funnel | Device-Specific Conversion | Weekly | Mobile Optimization Priority |
| 15 | Repeat Purchase | Repeat Rate | Monthly | Product-market fit signal |
How to get started in practice
The first step is to overcome the psychological barrier. Activate BigQuery export to GA4, run the query on the conversion funnel, edit and observe. Then adapt the queries to your needs, automate and connect the results to automatically updated dashboards. This process transforms raw data into quick and autonomous decisions.
Window functions: the secret skill
Window functions such as OVER, PARTITION BY, LAG, LEAD, ROW_NUMBER and RANK are the key to advanced analysis. They allow you to calculate moving aveRAGes, time comparisons and rankings without complications. Mastering them means freeing yourself from the limits of Excel and spreadsheets.
Because this skill has become non-negotiable
In 2023, SQL was placed among the top 10 skills required in digital marketing, with a premium wage of 15-25%. But above all, a marketing manager who can write query SQL is autonomous, fast and precise in decisions, qualities indispensable in an uncertain market.