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 of the column's data type so that they match. (For help with the Data Type transform, click here.)
Joining Two Reports
- Add the two reports to the Metric or Dataset Builder that you want to join, and run any transforms to get the data ready to join. (Often, if you want to join by date, you will use the Group Data Transform to help standardize the data.)
- Click on the arrow next to "+ Add Report" and select Joined Report from the list. (Or click "+ Add Report" on the right side, and search for the Joined Report option in the "Connection" section.)
- Select the Join Type.
- Select the first report you would like to use in the Join and select the common column from that report. Select the second report you would like to use in the Join and choose the column that contains common data to the first report.
- If you need to join a report on more than one column, see the section below titled "Joining on Multiple Columns"
- Then click Run.
Upon clicking Run, the data from the two reports will be joined together and outputted in the Data Table of the Join Report.
Joining Three or More Reports
- Click "+Add a Join" to add another join block to the Join Report. Please note "+Add a Join" is disabled if you have not selected all of the required parameters for previous Join Blocks.
- Select the Join Type
- 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.
- If you need to join a report on more than one column, see the section below titled "Joining on Multiple Columns"
- Click Run to see the output of the two join blocks, or click "+Add a Join" to continue joining more reports together. Please note you can not add more than 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 2 or more columns to get a unique combination of columns to accurately join your reports.
- After selecting your first set of columns to join on, click the "+ Add a column" button to join a second set of columns.
- 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.
- When you've selected all the columns that need to be included in the join, hit "Run".
Types of Joins
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 will use this 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 will be 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 will be the primary report.
Inner Join
An inner join will only include 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 will bring in all values from both reports. The Full join will 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 will be added to each row of the second report. An example of this is 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
Remove a Join Block
You can remove a Join Block by selecting the red "X" in 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.