How to create a question or a data request in Transparent BI with a question builder, custom expression or an SQL?
When setting up a question, you should already have an idea in mind of which tables you will have to employ, as well as how you expect it to be visualized.
As you remember, Supply table is responsible for storing static data, not related to the calendar and bookings. Here from we can retrieve any question of location, amenities or, for example, average bedroom count in a certain subtype of your market. Listing Nights table is giving you results based on scraping the platform's calendar. This table would help you to request the information of rates, lengths of stay or, for example, compare your occupancy with your competitors.
The purple cell “Filter” is responsible for narrowing down the table we have. Are we looking at a certain city? Date range? Or specifically at Booking.com?
The two green cells under “Summarize” are metrics. The first green cell — asks you how the data will be calculated. Are we interested in property count? Average daily rate (ADR) or a sum of reviews in the area?
The second green cell — will tell us how we want to arrange the data, or what we want to break the metric down by. For example, we calculated the ADR, but maybe we want to see it broken down now per subtype, or per property management company.
This will affect the various ways in which we can visualize the data in the next step, as breaking down a metric over time would suggest a line graph, whereas a categorical analysis of each bedroom count might suggest a bar graph.
Please watch the video on how to calculate your occupancy and ADR (average daily price), as well as how to edit the existing questions and make custom expressions:
For more information on questions, please visit: Create charts with explorable data by Metabase
For more information on creating SQL queries, please visit: Best practices for writing SQL queries by Metabase