1. Grow
  2. Organizing Data
  3. Reporting in Grow

Using the Master SQL Report

Article last updated:

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. The 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.

To add a Master SQL report to a dataset or a metric, click on the down arrow button next to the Add Report button and select Master SQL.

master-sql-report.png

Joins

You can read more about all the available types of joins in PostgreSQL in their documentation.

Union

Union stacks reports on top of each other (as long as they have a matching number of similar columns) and removes any duplicate rows.

Union All

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 does not 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
UNION ALL
SELECT "Id", "CloseDate", "Lead Source" FROM report2;

If you already have each report with the same columns in the same order, then you can use this:

SELECT * FROM report1
UNION ALL
SELECT * FROM report2;

Left Join

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 data of report 1. I can use a left join on the user IDs to bring in those dates onto report 1. I then group the data by the user ID on report 1.

Right Join

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.

Inner Join

An inner join gets all the rows that are common between both tables.

Example: One of my reports has some order information and another has the transaction information. 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.

Metadata Tables

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.

Dates Table

The dates table fills in any missing dates that reports do not have.

Other Notes: When joining to the dates table, you will 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 dt.
  • The metadata is in the following format: 2017-05-22T00:00:00:0000. In order to join dates, you will need to use SUBSTRING in the SELECT statement to get the dates table to 10 digits: substring(_datesTable.date,1,10)
  • 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.

Countries Table

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 will need to know the following:

  • The countries table is referenced as _countriesTable. We often abbreviate it to ct.
  • 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: countryName, iso2, and 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 the map chart of Grow. 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.

Was this article helpful?