Grow’s Master SQL Report tool is useful when you need to write some advanced queries and combine multiple reports together. The master report is very versatile, but sometimes tricky to use.
There are multiple ways to combine reports in a master report. Grow’s Master SQL Report supports Union, Union All, Left, Right, Full, and Inner Joins as PostgreSQL queries. Grow also provides three metadata tables—a dates table, a months table, and the countries table.
When using a Master SQL report, you can view all of the available columns (including for the metadata tables) in the left-hand panel, under the section titled Report Headers.
Note: You can read more about all the available types of joins in PostgreSQL in their documentation.
What it is: Union stacks reports on top of each other (as long as they have a matching number of similar columns) and removes any duplicate rows.
What it is: Union All stacks reports on top of each other (as long as they have a matching number of columns). The difference between Union All and Union is that Union All doesn’t remove duplicate rows.
Example: I have two reports that show the amount of revenue per date, with different types of revenue in each chart. I want to view all of the data on one report. To do this, I would use a UNION ALL clause (see below).
SELECT "Id", "CloseDate", "Lead Source" FROM report1
SELECT "Id", "CloseDate", "Lead Source" FROM report2;
What it is: The Left Join brings in all values from the LEFT table, even if they have no corresponding rows in the table being joined to it (the RIGHT table). Combines the two tables on a common column.
Example: Report 1 and report 2 both have user ID information, but report 2 has dates that I want to bring on to report 1’s data. I can use a left join (see screenshot below) on the user IDs to bring in those dates onto report 1. I then group the data by the user ID on report 1.
What it is: The Right Join brings in all values from the RIGHT table, even if they have no corresponding rows in the table being joined to it (the LEFT table). Combines the two tables on a common column. This is basically the converse of a Left Join.
What it is: An inner join gets all the rows that are common between both tables.
Example: One of my reports has some order information (report 5) and another has the transaction information (report 6). Using the code below, I can use an inner join on the order and transaction ID numbers to get the transaction information onto a report with the order information I pull from report 5.
You can also join data to some metadata we store there that contain the last two years worth of dates (in cases of missing dates data) or countries around the world. Grow offers three different tables: the dates table, months table, and the countries table.
What it is: The dates table fills in any missing dates that reports don’t have.
Other Notes: When joining to the dates table, you’ll need to know the following:
- Perform a LEFT JOIN onto the dates table.
- The Dates Table is referenced as
_datesTable. We often abbreviate it to
- The metadata is in the following format: 2017-05-22T00:00:00:0000. In order to join dates, you’ll need to use SUBSTRING in the SELECT statement to get the dates table to 10 digits:
- Ensure the dates from the other reports are in a YYYY-MM-DD format.
Example: I have some sales data for my company, but some dates are missing. I can use a left join to get those dates from the dates table for the past 2 years.
What it is: The countries table is a comprehensive list of all countries and their two- and three-character country codes. The countries table is useful for processing and streamlining reports that contain country data that you want to display.
Other Notes: When joining to the countries table, you’ll need to know the following:
- The countries table is referenced as
_countriesTable. We often abbreviate it to
- If you use a LEFT JOIN on the countries table, it will show bring in all of the rows from the countries table, even if there are no matches for the corresponding data. Instead, you could use an INNER JOIN.
- There are three columns in the countries table:
iso3. The ISO codes are the two- and three-letter codes that are internationally recognized abbreviations for country names.
Example: On one of my sites in Google Analytics, I have a list of visits by country. I need the two- or three-digit country codes, because they work better in Grow’s map chart. I can use an inner join using the countries table to get the country code of each user based on the full country name.
Additionally, Grow provides a Months Table that you can connect to. The months table has entries that can be used to map numbers to the names or shortened names of the months of the year.