One of the primary ways to explore data in Analytical is by creating questions. A question is a query you build to retrieve data from your database. This will always result in some form of a table or number that you can visualize. In this guide, we will explain how to create and save questions in Analytical using the Question Builder. We will cover the basic syntax and concepts involved in creating queries, as well as how to filter and join data.

Creating Questions

To create a question, start by clicking on the "+ New" button in the top right corner of the interface and click on “Question”. This will open the Question Builder, which is the tool you will use to build your question. The Question Builder consists of three main sections: the "Data" section, the "Filter" section, and the "Summarize" section.

Data

When you first enter the question builder, you will be prompted to choose a database and then select a table from that database, like Events. Once you have selected the table or view, the fields or columns from that table will appear in the "Data" section. You can select columns you want to include by clicking on the appropriate checkboxes. In the "Data" section, you will choose which table you want to include in your question with the possibility of joining data from another table.

Filter

In the "Filter" section, you can add filters to your question to limit the returned data. This can be particularly useful when working with large datasets, as it enables you to focus on specific subsets of data. You can filter data based on a specific value or using comparison operators such as greater than or less than..

In the filter menu, you will see a list of available columns and operators that you can use to create your filter. You can select a column from the list and then choose an operator such as "equals", "less than", or "contains" to define the condition you want to filter on.

For example, you can filter on Datetime to filter a dynamic period (last month) or a static period which can be a custom date range

Summarize

In the "Summarize" section, you can group your data by a specific field. This lets you quickly gain insights into your data by viewing summary statistics such as counts, sums, averages, etc. For example, if you have a dataset that contains sales data for various products and regions, you can use the "Summarize" feature to group the data by product and region, and then calculate the total sales for each product in each region. This allows you to quickly see which products are selling well in which regions and identify trends or patterns in the data.

Some of the common aggregations that you can perform using the "Summarize" feature in Metabase include:

Joining Data

In addition to selecting data and applying filters, you can join tables to create more complex queries. Joining data in the question builder is the process of combining data from two or more database tables into a single dataset that can be queried and analyzed. This is often necessary when the data for a report or analysis is spread across multiple tables.

Click the "Join" button in the "Data" section to add a join clause to your query. A join clause specifies the table you want to join to your main table. Then, select the table that you want to join and choose the type of join that you want to perform. Analytical supports several types of joins, including inner join, left join, right join, and full outer join.

Specify the join condition. This is the criteria that determines how the two tables should be joined. The join condition typically involves matching columns between the two tables. Click "Add another join" to join more tables if needed. You can add as many joins as you need to build the desired dataset.

A widely-used aggregation is the Events-Audience join (using the UUID column), which allows you to view and filter Events combined with Audience data. You can view an example in the screenshot at the top of this article. This shows you an example of processing a number of loyal customers.

Saving Questions

Once you have built your question, you can save it as a question in Analytical. To do this, click on the "Save" button in the top right corner of the Question Builder. Give your question a name and description, and choose which collection to save it to. Collections are used to organize questions and make them easier to find.

Creating questions is a simple and powerful way to explore your data and gain valuable insights. By following the basic syntax and concepts outlined in this guide, you can build queries that filter, group, and join data to meet your specific needs. And by saving your questions, you can easily access and reuse them in the future.

Click here to learn more about how to create visualizations, or here to view some valuable examples.