Digital Marketing Academy · Lesson

Joining Marketing Tables

Sessions, users, orders.

Joining Marketing Tables is a free Digital Marketing Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Digital Marketing Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Join?

Real questions span tables. Spend lives in campaigns, revenue in orders, traits in users. To compute ROAS or LTV by segment, you must combine them.

JOINs match rows from two tables on a shared key, like user_id or campaign_id.

SELECT o.order_id, u.country
FROM orders o
JOIN users u ON o.user_id = u.user_id;

INNER JOIN

An INNER JOIN returns only rows that match in both tables. Orders without a matching user, or users without orders, are dropped.

Use it when you only care about records that exist on both sides, such as purchasers.

SELECT u.user_id, u.country, SUM(o.revenue) AS revenue
FROM users u
JOIN orders o ON o.user_id = u.user_id
GROUP BY u.user_id, u.country;

LEFT JOIN

A LEFT JOIN keeps every row from the left table even when there is no match on the right. Missing values come back as NULL.

This is how you find users who never ordered, or sessions that never converted.

SELECT u.user_id, COALESCE(SUM(o.revenue), 0) AS revenue
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id
GROUP BY u.user_id;

Finding the Gaps

Pair a LEFT JOIN with a NULL check to isolate non-matches. "Which signups never purchased?" is a classic retention question.

The right side being NULL means no matching order existed.

SELECT u.user_id, u.signup_date
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id
WHERE o.order_id IS NULL;

Joining Sessions to Orders

Linking sessions to orders connects behavior with outcome. Match on user_id to see which traffic eventually bought.

This is the foundation of channel attribution analysis.

SELECT s.channel, SUM(o.revenue) AS revenue
FROM sessions s
JOIN orders o ON o.user_id = s.user_id
GROUP BY s.channel;

Computing ROAS

True ROAS needs spend and revenue side by side. Join campaigns to orders on campaign_id, then divide totals.

NULLIF guards against dividing by zero spend.

SELECT c.campaign_id,
       SUM(o.revenue) / NULLIF(SUM(c.spend), 0) AS roas
FROM campaigns c
LEFT JOIN orders o ON o.campaign_id = c.campaign_id
GROUP BY c.campaign_id;

Table Aliases

Aliases (o for orders, u for users) keep multi-table queries short and unambiguous. Always qualify columns when a name exists in both tables.

Clear aliases make complex joins far easier to read and debug.

SELECT c.name AS campaign, SUM(o.revenue) AS revenue
FROM campaigns c
JOIN orders o ON o.campaign_id = c.campaign_id
GROUP BY c.name;

Joining Three Tables

Chain JOINs to bring together more than two tables. Here we connect campaigns to orders to users to segment revenue by country.

Each JOIN adds another ON condition linking the new table to the existing set.

SELECT c.name, u.country, SUM(o.revenue) AS revenue
FROM campaigns c
JOIN orders o ON o.campaign_id = c.campaign_id
JOIN users u ON u.user_id = o.user_id
GROUP BY c.name, u.country;

Watch the Grain

Joining a one-to-many relationship can multiply rows and inflate sums. If one campaign has many orders, summing spend per order double-counts it.

Aggregate each side separately, then join the totals, to keep numbers honest.

SELECT c.campaign_id, c.total_spend, r.revenue
FROM campaigns c
JOIN (
  SELECT campaign_id, SUM(revenue) AS revenue
  FROM orders GROUP BY campaign_id
) r ON r.campaign_id = c.campaign_id;

First-Touch Attribution

To credit the first channel a user came from, find each user's earliest session, then join that to their orders.

A subquery isolates the first touch before the revenue join.

SELECT f.channel, SUM(o.revenue) AS revenue
FROM (
  SELECT DISTINCT ON (user_id) user_id, channel
  FROM sessions ORDER BY user_id, session_date
) f
JOIN orders o ON o.user_id = f.user_id
GROUP BY f.channel;

Joins in Practice

JOINs are where marketing SQL gets powerful. Spend plus revenue gives ROAS; sessions plus orders gives attribution; users plus orders gives LTV by segment.

Pick INNER when both sides must exist, LEFT when you want to preserve and inspect the gaps.

Quick Check

You want every signed-up user listed, including those who never placed an order. Which join do you use?

Recap

INNER JOIN keeps matches on both sides; LEFT JOIN keeps all left rows and reveals gaps. Aliases and qualified columns keep queries clean.

Beware the fan-out trap on one-to-many joins; pre-aggregate to keep sums accurate. Next: cohort and funnel queries.

Frequently asked questions

Is the “Joining Marketing Tables” lesson free?

Yes — the full text of “Joining Marketing Tables” is free to read here on the web, and the Digital Marketing Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Digital Marketing Academy course, upgrade to CoddyKit PRO.

What will I learn in “Joining Marketing Tables”?

Sessions, users, orders. You practise Digital Marketing Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Digital Marketing Academy?

No prior experience is required. Digital Marketing Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Joining Marketing Tables” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Digital Marketing Academy lesson?

Yes. Every Digital Marketing Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to Digital Marketing Academy