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. This article will explain what you can do with master reports and how to use them.
There are multiple ways to combine reports in a master report. Grow’s Master Report supports UNION, UNION ALL, LEFT JOIN, and INNER JOIN as SQLite queries. Grow also provides three metadata tables---a dates table, a months table, and the countries table.
If you want to copy the query syntax for the examples below, check out the Joins & Grow Metadata Tables section in this article.
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.
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 similar columns). The difference between Union All and Union is that Union All doesn’t remove duplicate rows.
This tutorial provides a good overview of SQLite UNION and UNION All clauses.
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).
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. This tutorial provides a nice overview of the Left Join.
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: 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 substr 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.
Questions about the Master Report? Email us at firstname.lastname@example.org or chat in on the site.