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 have 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 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 your Join Type
- Select one report as Table 1 and the other as Table 2, and select the columns from each one that contain the common data.
- Then click Run.
This will create a new report in your metric with the joined data. You can use a joined report as one of the reports in a second (or third, etc.) joined report if there are more than two reports that you want to join together.
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 report in Table 1 will be treated as the primary report.
Right Join
Right joins work the same way as left joins, except the report set as Table 2 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. It’s kind of like the opposite of the inner join that only brings in the common values. 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
Joining Three or More Reports
You can use a joined report in another joined report in a process to combine several reports together. However, it is more efficient to join them all together at once with a Master SQL report. Learn more about using the Master SQL report to join multiple reports.