Skip to content
Faisal Mahmud TusherDigital marketing, SEO & analytics
with Faisal Tusher

Glossary term

Join Key

The shared field, such as date and channel, a campaign ID or a click ID, on which rows from two data sources are matched in a blend or a query.
AnalyticsconceptUpdated

Definition

A join key is one or more fields present in both tables, with the same meaning, the same type and the same grain, used to decide which row of one table belongs with which row of the other. The SQL guide for marketers describes it as the shared dimension between the spend side and the outcome side: usually date and channel, sometimes campaign, and occasionally a click ID when both systems carry it.

The key is a property of a pair of tables, not of either one alone. Campaign name is a perfectly good column in an ad export; it becomes a join key only when another source records the same campaign in the same form. That is why the key has to be chosen, written down and checked, rather than assumed from the column names.

Choosing a key: IDs, names, dates and grain

Four questions decide whether a key will hold:

  • Is it stable? The guide to loading ad platform data into a warehouse puts it briefly: IDs are the join keys; names are descriptions that change. A campaign renamed mid-month splits its history in two if the name is the key.
  • Is it written the same way on both sides? Where names must be used, they match only if a Naming Convention is followed in every system. Case, spacing and spelling differences break matches without any error. The Looker Studio blending tutorial builds a normalised key, lower-case and trimmed, at the source when the convention is not enforced.
  • Is it the same type? Text joins to text and dates to dates. A numeric ID in one source and a text ID in another, or a date beside a date-and-time, will not line up until one is converted.
  • Is it unique at the grain being joined? If one key value appears on several rows on one side, every matching row on the other side is repeated and its metrics are multiplied. The SQL guide calls this fan-out and avoids it by aggregating each side to the key's grain before joining.

Some fields should never be keys in reporting tables. The marketing data model guide rules out an email address or phone number, hashed or not, and builds reporting tables at aggregated grains that carry no personal identifier at all.

In practice: blends, warehouse tables and CRM reports

The same idea appears in every layer of reporting. In a dashboard tool, a blend joins sources inside the report using join conditions set in its editor. In a Data Warehouse, the join is written in SQL over tables loaded at a known grain. In a CRM-to-ads report, the guide to connecting the CRM to ads and analytics groups leads and deals by the campaign value captured in the source fields, so the key is the campaign name, and naming has to match between the ad platform and what the UTM tags carried into the CRM.

Because a mismatched key fails quietly, the dashboard specification template asks for the join key to be written down for every blended pair of sources, with a check that both sides share it. The practical test is to compare the joined totals with each source's own totals: the side that must keep every row should match exactly, and any gap on the other side is a list of rows whose keys found no partner.

Illustrative example — not a real result

A tutoring company loses half its bookings in a blend

Vorquaith Tutoring blended its ad spend with bookings from its booking system, joined on campaign name and date. The chart showed £3,600 of spend for the month against 52 bookings, while the booking system itself recorded 104 bookings from paid campaigns. A full outer join exposed the gap: one ad platform stored campaign names in capitals, such as "GCSE-MATHS-SPRING", while the booking system's tags were lower-case. The 52 bookings from that platform's campaigns never met their spend. The team added a lower-case, trimmed key to both sources and rejoined. Bookings in the blend rose to 104, and the cost per booking fell from about £69 to about £35.

Table Grain is what one row of each table represents; a key only behaves when both sides are at the grain the key describes. A Data Warehouse is where most durable joins live, with IDs loaded beside names so the stable field is available. A Naming Convention is what makes a name-based key possible at all, and UTM parameters are often how a campaign's name reaches the outcome system in the first place.

Common confusions

  • The key is whichever column has the same name. Two columns called "campaign" can hold an ID in one source and a display name in the other.
  • A blend that runs is a blend that works. Unmatched rows are dropped or left empty without warning, so a working chart can still be missing half its outcomes.
  • More key fields make a safer join. Every extra field must match on both sides and changes the grain; a key that includes a field one side does not split by produces duplicates or gaps.
  • Hashing makes a personal identifier safe to join on. The data model guide's position is that a hash still identifies the person, so reporting tables avoid such keys entirely.
  • Join type and join key are the same choice. The key decides which rows match; the join type decides what happens to rows that do not. Both have to be chosen.

More terms to look up

Where this term is used

Related