Learning Objectives
- Identify the core marketing analytics tables and explain what each one stores and how they connect across the customer journey.
- Distinguish between customer-level, campaign-level, and transaction-level data and explain how each level shapes SQL query design.
- Explain how CRM platforms, marketing automation tools, advertising platforms, and web analytics systems store and link customer interactions.
- Identify key marketing fields — acquisition_channel, campaign_id, conversion_flag, order_value — and explain their role in marketing analysis.
SQL for Marketing & Customer Analytics is structured as a progressive journey — from foundational data concepts through practical querying to advanced campaign and revenue reporting. Each part builds directly on the knowledge established in the previous one.
How marketing data is organized across core tables, the distinction between customer-level, campaign-level, and transaction-level data, and how CRM, automation, and ad platforms store customer interactions.
Hands-on querying — retrieving customer and campaign records, filtering marketing data, joining tables, analyzing website sessions, and aggregating metrics to identify high-value customers and campaign responses.
Campaign ROI reporting, customer segmentation, cohort analysis, conversion funnel and attribution analysis, customer lifetime value, retention and churn analysis, and building marketing performance dashboards.
By the end of all three parts, learners will have a complete, practical SQL foundation tailored specifically to the real analytical needs of marketing professionals working with customer data, campaign performance, and digital engagement metrics.
Marketing data is structured around customers, the campaigns that reach them, the interactions they have with digital channels, and the purchases they make. Every query in this course draws from one or more of these six core tables.
Customer segmentation · Acquisition channel analysis · Cohort analysis · Lifetime value calculation. Almost every marketing query begins here — by identifying which customers are being analyzed.
Campaign performance analysis · ROI reporting · Channel comparison · Budget tracking. Joins to engagement and orders tables to measure how campaigns translate into revenue.
Revenue analysis · Repeat purchase tracking · Customer lifetime value · Product performance. The primary table for any question about what customers bought and how much they spent.
Product performance reporting · Category analysis · Price point analysis · Revenue mix. Joined to orders to understand which products drive revenue and repeat purchases.
Traffic source analysis · Conversion funnel tracking · Campaign attribution · Bounce and engagement metrics. Connects digital activity to customer records and eventual purchases.
Email open and click-through rates · Conversion tracking · Campaign response analysis · Attribution modeling. The bridge between campaigns and customer actions.
How the Tables Connect
All six tables connect through shared keys — customer_id links customers to orders, sessions, and engagement records, while campaign_id links campaigns to engagement interactions. Understanding these connections is the foundation of multi-table marketing queries.
These tables mirror the data structure found in platforms such as Salesforce, HubSpot, Google Analytics, Klaviyo, Shopify, and Meta Ads Manager — the systems marketing teams use every day.
Before writing any query, always ask: Am I analyzing customers, campaigns, or transactions? The answer determines which table to start from and how every join should be structured.
Stores information that does not change per interaction — name, email, acquisition channel, signup date, segment. Use for cohort definitions, segmentation, and lifetime value analysis.
Stores information about each distinct marketing activity — channel, dates, budget, objectives. Use for performance comparisons, ROI reporting, and channel attribution.
Stores information about each individual interaction or purchase — orders, sessions, engagement events. One customer generates many transaction-level rows over time.
The Full Data Hierarchy
A customer can have many campaign interactions, many website sessions, and many orders. Joining across these levels without accounting for row multiplication is the most common source of inflated counts in marketing analytics.
| Level | Table | Relationship | Example Question |
|---|---|---|---|
| Customer | customers | 1 row per customer | "Who are our active customers?" |
| Campaign | campaigns | 1 row per campaign | "Which campaigns ran last quarter?" |
| Engagement | engagement | Many rows per customer per campaign | "Which customers clicked on campaign X?" |
| Session | website_sessions | Many rows per customer | "How many sessions came from paid search?" |
| Transaction | orders | Many rows per customer | "What is the average order value per segment?" |
Always verify row counts after joining. A customer joined to their orders will produce one row per order — not one row per customer. Summing revenue across that join without accounting for duplicates will inflate totals. Use COUNT(DISTINCT customer_id) when counting customers, not COUNT(*).
Marketing data is built around a set of core fields that appear across nearly every system and query. Understanding what each field means — and how it behaves — is essential before writing any SQL for marketing analysis.
Acquisition Channel — acquisition_channel
Records how a customer first discovered the brand — the source that brought them in. Common values include Organic Search, Paid Search, Email, Social, Referral, and Direct.
- Identifies which channels drive the most new customers.
- Supports cost per acquisition (CPA) analysis by channel.
- Enables cohort analysis by acquisition source — comparing lifetime value across channels.
- Anchors attribution modeling — understanding which touch points deserve credit for conversions.
Campaign ID — campaign_id
A campaign_id is the key that links engagement and order records back to a specific marketing campaign. Without it, it is impossible to measure whether a campaign actually drove any behavior.
| What campaign_id Enables | Why It Matters |
|---|---|
| Linking engagement records to campaigns | Measures opens, clicks, and conversions per campaign |
| Joining campaigns to orders | Calculates revenue attributable to each campaign |
| Comparing campaigns side by side | Identifies highest- and lowest-performing activities |
| Filtering by channel or date range | Enables time-based and channel-based performance slicing |
Conversion Flag — conversion_flag
A binary field (typically 1 or 0, or TRUE / FALSE) recorded in the engagement table that marks whether a customer interaction resulted in a desired outcome — such as a purchase, a sign-up, or a form completion.
- Calculates conversion rate — the percentage of interactions that resulted in the target action.
- Identifies which campaigns, channels, or segments convert at the highest rate.
- Powers funnel analysis — tracking the drop-off from impression to click to conversion.
- Feeds into attribution models that assign credit across multiple touch points.
Order Value — order_value
The monetary value of a single transaction, recorded in the orders table. Order value is the foundational field for all revenue analysis in marketing.
- Calculates average order value (AOV) — a key metric for understanding purchase behavior.
- Sums into total revenue by campaign, channel, segment, or time period.
- Feeds into customer lifetime value (CLV) calculations when summed across repeat purchases.
- Supports ROI reporting — comparing revenue generated against campaign spend.
Why These Fields Are Foundational
| Marketing Question | Field(s) Required |
|---|---|
| "Where are our customers coming from?" | acquisition_channel |
| "Which campaign performed best?" | campaign_id · conversion_flag · order_value |
| "What percentage of clicks converted?" | conversion_flag |
| "What is our average order value by segment?" | order_value · segment |
| "What is each customer's lifetime value?" | order_value · customer_id |
Marketing teams use many different platforms — CRM systems, email automation tools, advertising platforms, and web analytics dashboards. Behind every interface, all of these systems store data in structured tables connected by shared keys. Understanding how each type of platform organizes information helps identify which table to query for each type of question.
Stores the core customer record — contact details, segment, acquisition source, purchase history, and engagement history. Maps primarily to the customers table. Examples: Salesforce, HubSpot, Zoho.
Manages campaign creation, email sends, opens, clicks, and conversions. Maps to the campaigns and engagement tables. Examples: Klaviyo, Mailchimp, Marketo, ActiveCampaign.
Tracks impressions, clicks, conversions, and spend per campaign and ad set. Maps to the campaigns table with spend and performance fields. Examples: Meta Ads, Google Ads, LinkedIn Ads.
Records website sessions, traffic sources, page views, and conversion events. Maps to the website_sessions table. Examples: Google Analytics 4, Adobe Analytics, Mixpanel.
How Data Flows Across the Customer Journey
- Customer is acquired — a new customer record is created in the CRM (customers table) with their acquisition channel recorded.
- Campaign is launched — the campaign is created in the marketing automation or ad platform (campaigns table) with channel, budget, and dates.
- Customer engages — opens, clicks, or interactions are recorded in the engagement table, linked by both customer_id and campaign_id.
- Customer visits the website — a session record is created in website_sessions with traffic source and behaviour data.
- Customer purchases — a transaction is recorded in orders, linked to the customer and optionally to the campaign that influenced the purchase.
- Analysis begins — SQL joins these tables together to measure how campaigns translated into revenue, and which customers are most valuable.
Always identify which platform domain — CRM, campaign, session, or transaction — the question belongs to. That domain points to the primary table. Every other table in the query is either a join to add context or a filter to narrow the results.
Overview & Introduction — Key Takeaways
Five foundational principles from this introductory section.
Customers, campaigns, orders, products, website sessions, and engagement — knowing each table's level and purpose is the starting point for every marketing query.
Customer-level, campaign-level, and transaction-level data. Understanding which level a table belongs to — and joining correctly — prevents inflated counts and misleading aggregations.
Acquisition channel, campaign_id, conversion_flag, and order_value are the backbone of marketing analytics. These fields answer the most fundamental questions about where customers came from, what campaigns drove, and how much revenue was generated.
CRM, marketing automation, advertising platforms, and web analytics each map to a distinct set of tables. Identifying the correct platform domain before writing a query saves time and prevents errors.
Conceptual clarity prevents analytical errors. A solid grasp of data structure, table levels, and key marketing fields produces accurate, trustworthy results from the very first query.