Using Joined Reports

A very common step in analyzing your data is combining multiple reports by a common identifier. For example, if you want to compare daily sales from one data source and daily ad spend from a second data source. The common identifier would be the dates.

You can use SQL to accomplish this, but we have made it easier with the Joined Report.

To use Joins, you need to at least two reports that each have at least one column with common values. They don't need all of the same values in both reports, but they do need to have some in common for the join to work.

The values also need to be identical. For example, in one report, you might have the dates formatted as 2019-01-01, and in the other as 01/01/2019. They are the same dates, but Grow will see them as different values. This applies to uppercase and lowercase letters as well. However, the column headers do not have to be the same.

You will also need to verify that the column types you are trying to join on are the same. If you are trying to join a numeric and a text column together, you'll need to first use the Data Type transform to change of the column's data type so that they match.

In this article, we will cover:

Combining Your Reports

Growallows you to join reports on multiple levels. You can join two, or more reports, or even combine reports that do not contain a common column.

Joining Two Reports

Here are the steps to use Joined Reports:

  1. In Grow, navigate to + Add New > Metric.
  2. Search and select the data you want from the list of available Dataset/Warehouse table/Connection.
  3. Click on + Add Report > Joined Report.

  4. Select the Join Type from the drop-down.
  5. Select the two reports you want to use in the Join along with the common column from the respective reports.
  6. Click Run.

Upon clicking Run, the data from the two reports get joined together and outputted in the Data Table of the Joined Report.

Joining Three or More Reports

  1. Click +Add a join to add another join block to the Joined Report.

    +Add a join is disabled if you have not selected all of the required parameters for previous Join Blocks.

  2. Select the Join Type.
  3. Join Result A, the output table from the first join block will populate in the first report dropdown. Next, select the common column from Join Result A that you wish to use in the Join. Now select the second report you would like to use in the Join, and choose the column that contains common data to Join Result A.
  4. Click Run to see the output of the two join blocks, or click +Add a join to continue joining more reports together.
    You can add upto 15 join blocks in a Dataset or Metric.

Joining on Multiple Columns

Sometimes you need to join two reports but you don't have a unique column on each report that you can join on. If this is the case, you can select two or more columns to get a unique combination of columns to accurately join your reports.

  1. After selecting your first set of columns to join on, click the + Add a column button to join a second set of columns.
  2. If you need more than 2 columns, you can continue to click the + Add a column button up to 4 times to join on a maximum of 5 columns per join.
  3. When you've selected all the columns that need to be included in the join, hit Run.

Understanding the Join Types

When creating a joined report, there are five ways you can join them: Left Join, Right Join, Inner Join, Full Join, and Cross Join.

Left Join

Left joins are the most common type of join. You use this type when you have a primary report that has all of the data you want to use (for instance, online orders from the last 30 days) and the second report has extra data that you want to add to the first report (for example, customer info that you'd like to attach to each order).

When making a left join with the Joined Report, the first report selected in the join block is treated as the primary report.

Right Join

Right joins work the same way as left joins, except the second report selected in the join block is the primary report.

Inner Join

An inner join only includes the rows that have common values between the two reports. For example, you could join a report of email newsletter subscribers with a report that has customers who have purchased from you to get a list of only customers who also subscribe to your newsletter.

Full Join

The Full join is also called the full outer join. It brings in all values from both reports. The Full join bring in all data from both reports even if there is no data in fields from one of the reports.

Cross Join

Cross join combines the two reports so that all of the data from one report gets added to each row of the second report.
For example, if you had a table that had the color options for T-shirts and the second table had the size, a cross join would create a row for each combination of colors and sizes.
Basically, it multiples the number of rows because it is joining all of the rows from one report to each row of the second.

There are a few resources that explain the kind of joins for PostgreSQL. Here's one that we've found to be helpful: https://www.w3resource.com/PostgreSQL/postgresql-join.php

Removing a Join Block

You can remove a Join Block by selecting the icon at the top right corner of the Join Block. We require that at least one join block exists in the report, therefore you cannot remove the first Join Block. Please note that removing Join Blocks could break Joins downstream that reference the output of the deleted block.

Was this article helpful?