Metrics are an important measurement instrument for each firm that desires to develop, as they supply insights into efficiency and areas for enchancment. Nevertheless, defining and sustaining a single supply of fact for metrics will be difficult. Totally different departments would possibly use various definitions for a similar metric, resulting in confusion and misalignment. How is it attainable?

Let’s say your advertising and marketing division has one definition for a “Income” metric and your gross sales division has an identical definition for a similar metric, however these definitions should not the identical. What’s going to occur is that each departments will depend on completely different numbers that aren’t the identical, doubtlessly resulting in completely different selections!

You might want to have consistency in these vital metrics, equivalent to income. To deal with this challenge, a unified layer with metric definitions is crucial. You simply wish to have one definition of metrics, not a number of.

Our strategy to the unified layer with metric definitions is the semantic mannequin. It entails the usage of a language (MAQL) that abstracts customers from the data of bodily knowledge constructions of their knowledge warehouse. This semantic mannequin permits customers to generate SQL that runs of their database. This strategy presents a number of benefits:

• Single supply of fact: all metrics are saved in a single place and uncovered to all shoppers.
• Self-service for enterprise finish customers: they’re able to utilizing semantic entities with MAQL, not bodily entities with SQL.
• Fear-free adjustments to bodily construction: remap the logical knowledge mannequin to the brand new bodily mannequin whereas preserving metrics, experiences, and dashboards the identical.
• Seamless migration between database engines: MAQL stays the identical, the platform generates the fitting SQL dialect for you.

With the idea that this strategy is one of the simplest ways to outline metrics, we have invested in MAQL enhancements to boost metric definition capabilities. Let’s discover these enhancements by way of sensible examples. 🚀

Outer Joins

Suppose you have got two tables, prospects and orders, and also you wish to show each prospects and orders in a single desk. If a buyer hasn’t ordered any items but, they would not be proven, even for those who wished to incorporate them. Outer joins make this attainable.

To make use of outer joins inside a MAQL expression, use the FOR EACH clause to create a metric that counts prospects with none orders:

This new characteristic is especially helpful in timeline eventualities the place you wish to show all days of the week, together with these with no knowledge, by deciding on the “present lacking values” choice.

Date Arithmetics

Working with dates is now extra accessible. ⏰ Wish to know the depend of orders inside seven days earlier than the final order? Use the DATETIME_ADD operate:

In one other instance, say you are a gross sales consultant who desires to see the typical variety of days to shut a deal. DATETIME_DIFF is the proper resolution:

Extra Enhancements

Expertise extra MAQL enhancements with new capabilities FIRST_VALUE and LAST_VALUE, that are just like their SQL counterparts. As an example, the LAST_VALUE operate permits you to show the latest income worth, preserving you up to date on the most recent enterprise developments.

Along with FIRST_VALUE and LAST_VALUE, you can too use MAX and MIN capabilities.

Strive It on Your Personal!

To discover MAQL and outline your personal metrics, begin with the GoodData Cloud 30-day trial and create clear, insightful metrics for your small business.