📢 Introducing Enhanced Metrics: Outer Joins and Advanced Date Arithmetic in MAQL

Metrics are a crucial measurement tool for every company that wants to grow, as they provide insights into performance and areas for improvement. However, defining and maintaining a single source of truth for metrics can be challenging. Different departments might use varying definitions for the same metric, leading to confusion and misalignment. How is it possible?

Let’s say your marketing department has one definition for a "Revenue" metric and your sales department has a similar definition for the same metric, but these definitions are not the same. What will happen is that both departments will rely on different numbers that are not the same, potentially leading to different decisions!

You need to have consistency in these important metrics, such as revenue. To address this issue, a unified layer with metric definitions is essential. You just want to have one definition of metrics, not several.

Our approach to the unified layer with metric definitions is the semantic model. It involves the use of a language (MAQL) that abstracts users from the knowledge of physical data structures in their data warehouse. This semantic model allows users to generate SQL that runs in their database. This approach offers several advantages:

• Single source of truth: all metrics are stored in a single place and exposed to all consumers.
• Self-service for business end users: they are capable of using semantic entities with MAQL, not physical entities with SQL.
• Worry-free changes to physical structure: remap the logical data model to the new physical model while keeping metrics, reports, and dashboards the same.
• Seamless migration between database engines: MAQL stays the same, the platform generates the right SQL dialect for you.

With the belief that this approach is the best way to define metrics, we've invested in MAQL improvements to enhance metric definition capabilities. Let's explore these improvements through practical examples. 🚀

Outer Joins

Suppose you have two tables, customers and orders, and you want to display both customers and orders in a single table. If a customer hasn't ordered any goods yet, they wouldn't be shown, even if you wanted to include them. Outer joins make this possible.

To use outer joins within a MAQL expression, use the FOR EACH clause to create a metric that counts customers without any orders:

This new feature is particularly useful in timeline scenarios where you want to display all days of the week, including those with no data, by selecting the "show missing values" option.

Date Arithmetics

Working with dates is now more accessible. ⏰ Want to know the count of orders within seven days before the last order? Use the DATETIME_ADD function:

In another example, say you're a sales representative who wants to see the average number of days to close a deal. DATETIME_DIFF is the perfect solution:

Additional Improvements

Experience more MAQL enhancements with new functions FIRST_VALUE and LAST_VALUE, which are similar to their SQL counterparts. For instance, the LAST_VALUE function allows you to display the most recent revenue value, keeping you updated on the latest business trends.

In addition to FIRST_VALUE and LAST_VALUE, you can also use MAX and MIN functions.

Try It on Your Own!

To explore MAQL and define your own metrics, start with the GoodData Cloud 30-day trial and create clear, insightful metrics for your business.