10.10. Cross Join in Looker Studio

Cross join is the final type of join we'll discuss. In essence, a cross join returns every possible combination of rows from the left and right tables. This is different from a full outer join, which only returns all the rows.

For instance, let's say we have two small tables, each with three rows and three values. With a cross join, the result would contain nine rows, as it produces every possible combination (3 x 3). This can be particularly useful when working with datasets such as dates or different categories.

Creating a Basic Table with Cross Join

Imagine we have a set of dates for the year (365 dates) and different stores, geographic areas, or sales managers. We want to create a basic table with every date repeated for each value in the second category.

For example, if we have three dates and three products, the table would have each date repeated three times for each product, like this:

January 1st: A, B, C
January 2nd: A, B, C
January 3rd: A, B, C

A cross join can be a good candidate for a complete left join when using left outer join. Let's say we have three different types of campaigns and dates per year. We run campaigns on Facebook, Microsoft, and Google, but on some days, we don't have a campaign in Google or Facebook. And we only started Microsoft ads from the middle of the year.

None of the three data sources have a list of all different dates and campaigns. In this case, we can start with a table of all dates (365) and all campaigns. The cross join of these two will give us a complete dataset with all different combinations of dates and campaign names. Then, we can add data sources with left joins.

This is one of the few instances where cross joins are incredibly useful.

Blending Data with Cross Joins

Looker Studio automatically uses cross joins for blending two datasets together, as it produces every single value from every different combination of the two values. For example, when we choose two different scorecards and blend them together, the result will be a cross join.

Cross joins do not have a join key or join condition because they grab all the values and return all the different combinations of the two datasets. As a result, there's no join condition needed. In the resulting blended dataset, we have access to both metrics.

In this lesson, we've explored various join types in Looker Studio, starting with left join, right join, inner join, cross join, full outer join and union in Looker Studio. We've also seen how they can be applied in real-life scenarios for data enrichment, metric re-aggregation, and multi-channel cost reporting.

Enroll in Looker Studio Masterclass →

📩 Receive my weekly Looker Studio tips

🖇 Connect with me on LinkedIn