What this means for your store
A relationship join links tables on shared keys before queries run - fine when cardinality is controlled. A data blend queries each source separately and aligns on common dimensions at viz time. Joining daily ad spend to order lines on Date alone fans cost across every SKU row and wrecks ROAS denominators. Blending aggregated spend to aggregated revenue on Date + Campaign keeps campaign reporting aligned with finance.
Scenario on a real storefront
Glossier's UK team connects BigQuery orders to Google Ads cost. One join pattern inflates revenue; the other holds up in the Monday merchandising stand-up:
// Risky - join at line grain
Orders (line) JOIN Ads (daily campaign)
ON Orders.Date = Ads.Date
AND Orders.utm_campaign = Ads.Campaign
→ Cost repeats per line; SUM(Revenue)/SUM(Cost) looks wrong
// Safer - aggregate first, then relationship
Order_daily AS (
SELECT date, campaign, SUM(revenue) rev, COUNT(DISTINCT order_id) orders
FROM orders GROUP BY 1,2
)
Relationship: Order_daily.Campaign = Ads.Campaign
Order_daily.Date = Ads.Date
// Blend alternative (separate connections)
Primary: Order_daily | Secondary: Ads
Blend on: Date, Campaign
Metric: SUM(rev) / SUM(Cost)What to do next
- For production ROAS, prefer a single BigQuery model; use Tableau joins or blends to prototype before IT publishes the warehouse view.
- After any join, check row counts - if orders jump 10×, the grain is wrong.
- Campaign name drift between GA4 and Ads breaks both patterns; fix UTM mapping upstream before the agency re-trusts the dashboard.
Bottom line
Joins suit well-keyed tables at the same grain; blends suit separate connectors. For store ROAS, aggregate orders and spend before you relate them - never at SKU line grain.