🧠 MAQL Basics: A Beginner’s Guide to Writing Metrics

Start building custom metrics in Learn Amp using MAQL — Multidimensional Analytical Query Language.

🧭 Overview

MAQL (Multidimensional Analytical Query Language) is the language used to create custom metrics in the Advanced Analytics tool.

It supports a wide range of functions — from simple calculations like counts and averages to more advanced statistical analysis such as skewness, standard deviation, or running totals.

Whether you're just getting started or exploring more complex use cases, this guide will walk you through the fundamentals of writing MAQL expressions.

📐 What You Should Know Before You Start

Before building metrics, it's important to understand a few key rules about how MAQL works:

📊 Metrics always return a numerical value
🧱 Metrics return values in context — based on the dimensionality (e.g. user, team, time) of the visualisation or filter
🏁 All MAQL expressions begin with SELECT
➕ Basic aggregation functions look like this: SELECT SUM({fact/quantity})

MAQL operates on top of Learn Amp’s logical data model — meaning the structure of the data determines which attributes, facts, and filters are available.

🧩 Common Functions in MAQL

Here are some core building blocks you'll use frequently when writing MAQL metrics:

Category	Examples

Category	Examples
Aggregation Functions	`COUNT()`, `SUM()`, `AVG()`, `MAX()`, `MIN()`
Logical Operators	`AND`, `OR`, `NOT`
Conditional Logic	`WHERE`, `IFNULL()`
Running Totals	`RUNSUM()`, `RUNAVG()`
Mathematical Functions	`ABS()`, `ROUND()`, `POWER()`, `SQRT()`

🧪 Getting Started: Examples Explained

Here’s a progression of common MAQL examples to help you understand how to structure and apply metrics using different function types. These examples highlight aggregation, conditional logic, date calculations, and metric composition.

🔢 Example 1 – Basic Aggregation: Count Content Views

Metric: Count of views of content

SELECT COUNT({label/views.id})

🧠 This uses the COUNT() function to return the total number of content views. It’s one of the most basic but essential metric types — useful for tracking user interaction with learning materials across your platform. Ideal for evaluating content reach and popularity over time or by content type.

✅ Example 2 – Conditional Count: Completed Tasks Only

Metric: Number of tasks marked as completed

SELECT COUNT({label/tasks.id}) 
WHERE {label/tasks.completed} = "true"

🧠 Adds a WHERE condition to limit the count to only those tasks that have been completed. This type of metric is essential when you need to measure compliance, engagement, or task completion performance within workflows or learning journeys.

📉 Example 3 – Conditional Ratio: Event Drop-Out Rate

Metric: Percentage of approved enrolments who did not attend an event (excluding hosts)

SELECT 
    IFNULL(
        (SELECT COUNT({label/enrollments.id}) 
         WHERE {label/enrollments.status} = "approved" 
         AND {label/enrollments.attendance} = "did not attend"
         AND {label/enrollments.host} = "false"), 
        0
    ) 
    / 
    (SELECT COUNT({label/enrollments.id}))

🧠 This metric uses a conditional ratio with IFNULL() . It tracks how many non-host attendees failed to show up after approval. Great for measuring attendance reliability and identifying no-show rates for in-person or virtual events.

⏱ Example 4 – Time Difference (Date Math): Average Days to Complete

Metric: Time taken (in days) between task assignment and completion

SELECT AVG(SELECT DATETIME_DIFF({label/assigned_at.day}, {label/completed_at.day}))

🧠 Calculates the average duration between when a task was assigned and when it was completed using DATETIME_DIFF() within AVG(). This helps monitor pacing, efficiency, or user delay patterns — useful for assessing content difficulty or workflow friction.

⚠️ Example 5 – Conditional Count: At-Risk Objectives

Metric: Number of objectives flagged as "at risk"

SELECT COUNT({label/objectives.id}) 
WHERE {label/completion_status} = "at_risk"

🧠 This metric uses a conditional WHERE clause to count only objectives with the status "at risk." It helps identify learners or teams that may be falling behind. This is particularly useful for surfacing risks early during performance reviews or tracking OKR progress.

🧩 Example 6 – Composed Metric with Conditional Filter: Count of Active Users with Skill Ratings

Metric: Count of active users who have submitted at least one skill self-rating

SELECT ({metric/count_of_active_users_with_associated_user_skill}) 
WHERE NOT ({fact/ownrating} = NULL)

🧠 This is a composed metric — it reuses an existing metric (count_of_active_users_with_associated_user_skill) and adds a conditional filter using WHERE NOT. The WHERE NOT ({fact/ownrating} = NULL) condition ensures it only includes users who have submitted at least one skill self-rating. This combination is useful when narrowing down high-quality engagement within a broader user group.

🧭 Metric Context: Why Results Change

MAQL is context-sensitive — meaning the result of your metric will depend on:

The filters applied to the visualisation
The filters applied to Analytics dashboards
The attributes used in the visualisation

🔍 For example: A metric will return different values when in a pivot report sliced by User Full name vs by Team name.

🌍 Beyond the Basics: What Else Can MAQL Do?

Once you’re comfortable writing simple metrics, there’s a lot more you can do with MAQL to support deeper analysis and insight.

Beyond counts and averages, MAQL supports:

Running total functions — such as cumulative completions or engagement trends over time (RUNSUM(), RUNAVG())
Time-based comparisons — track differences between dates using DATETIME_DIFF() or build offset comparisons with DATETIME_ADD() (e.g. compare this month vs. last month or calculate days to completion)
Mathematical transformations — including ratios, percentage change, or formatting adjustments
… and more

🧠 If you're measuring it, you can likely model it with MAQL — as long as the required data is available.
Check out all the MAQL detailed documentation to explore all possibilities.

❓ FAQs

❓ What kind of results can MAQL metrics return?

All MAQL metrics return numerical values — for example, counts, percentages, averages, or time durations. You can’t return text or labels using MAQL, but you can group or slice numeric values by attributes in your visualisation. If you have 0/1 results and want to show a YES / NO label instead, you can apply custom formatting to do so. See https://learnamp.atlassian.net/wiki/spaces/KB/pages/1649344544 to apply formatting in this case.

❓ When should I use `WHERE` in MAQL?

Use WHERE when you want to narrow down your metric to specific records that meet a condition.

For example:

SELECT COUNT({label/tasks.id}) 
WHERE {label/tasks.completed} = "true"

This counts only tasks that are marked as completed — excluding everything else.

❓ What does `WHERE NOT` do?

WHERE NOT is the opposite of WHERE. It excludes records that match a specific condition.