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.
NOTE: This feature is only available for accounts on the Basic and Professional plans. If you would like to upgrade your plan, talk to your Grow account manager or visit your account's billing page.
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 "Add New Report" on the right side, and chose the Joined Report option. (Or click on the arrow next to "Add New Report" and select Joined Report from the list.)
- Select one report as Connection 1 and the other as Connection 2, and select the columns from each one that contain the common data.
- Then click Join.
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 three ways you can join them: Left Join, Right Join, and Inner 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 Connection 1 will be treated as the primary report.
Right joins work the same way as left joins, except the report set as Connection 2 will be the primary report.
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.
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. Read our article about using the Master SQL report to join multiple reports.