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've made it easier with the Joined Report.
To use Joins, you need to at least two reports that each have a column with common values. They don't have to 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 counts for uppercase and lowercase letters as well. However, the column headers do not have to be the same.
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 select the column that contains common data to the first report.
- 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 by default. If you wish to use a different report, select another report in the dropdown to override the default. Select the common column from Join Result A that you wish to use in the Join. Next, select the second report you would like to use in the Join and select the column that contains common data to Join Result A.
- 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.
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 instance, 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” and it will bring in all values from both reports. The Full join will bring in all the data from both reports even if there is no data in some of the 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 click 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.