Segmentation

Segmentation lets you analyze the data with measure and dimension pairs. You select a dataset using dataset property and specify the measures and dimensions under the dataset as part of the request, and an SQL query is generated depending on the field definitions. You can also filter the data using field references.

Here is an example dataset:

models/schema.yml

version: 2
sources:
  - name: first_dataset
    tables:
      - name: users
        meta:
          metriql:
            total_users:
              aggregation: count
        columns:
          - name: country
          - nps:
              metriql.measure:
                 - average

You can query the data using as follows:

dataset: source('first_dataset', 'users')
measures: ["total_users"]
dimensions: ["country"]
filters:
  - {dimension: nps, operator: greater_than, value: 10}
limit: 1000

Alternatively, you can compile the SQL as follows:

with top_users_by_country AS (
  {{sql('segmentation', dataset = 'users', 
            measures = ['total_users'], 
            dimensions = ['country'], 
            filters = [dimension: 'nps', operator: 'greater_than', value: 10}])}}
)
select * from top_users_by_country

The Metriql queries above compile to the following query when you use the REST API SQL or JDBC API:

with top_users_by_country AS (
  SELECT country, count(*) FROM users HAVING avg(nps) > 10 GROUP BY country 
)
select * from top_users_by_country

tip

If you have event_timestamp mapping, the dataset will be marked as time-series data.