Building your first metric

Welcome to this simple tutorial for creating your first metric in Sightfull. This guide will help you understand the basics of building metrics on our platform. It emphasizes the metric schema and its properties. Let's build a functional metric step-by-step.

What you'll learn in this guide

• Understanding the metric schema and its properties
• Configuring properties for your metric
• Building a basic, functional metric

The metric schema

The first metrics you build on top of your data - Aggregate metrics - have this schema structure:

- name:
  entity:
  filters:
    - sql:
  operation:
  measure:
  x_axis:
    period:
      - sql:

The following table defines schema properties and provides examples:

Property	Definition	Example
name	The unique id of the metric	average_sales_cycle_length
entity	The object this metric relates to and calculates a value for	opportunity
filters (optional)	What criteria should the data meet to be included in the metric?	See filters documentation
joins (optional)	What additional data do we need from related entities to calculate the metric?	See joins documentation
operation	What calculation should be performed on the relevant data values?	avg
measure	What specific dimension or equation represents the metric value for each entity?	$close_date - $created_date
x_axis	What are the criteria for the data to be used in calculating the metric result for each period?	See X-axis documentation

Note that the joins property provides additional advanced capabilities when using data from related entities.

Step-by-step guide

Follow these simple steps to create your first metric.

Step 1: Define the metric

Start by giving your metric a meaningful name. For example, if you're measuring the length of an average sales cycle, name the metric average_sales_cycle_length.

  - name: average_sales_cycle_length
    ...

Step 2: Choose an Entity

Identify the data entity your metric will measure. An entity can be anything, such as an opportunity, a sale, a customer, etc.

In our example, we're measuring the opportunity entity. We'll calculate the average duration from creation to close.

- name: average_sales_cycle_length
  entity: opportunity

Step 3: Define Filters

Filters define the criteria for including data in your metric. These can be written in standard SQL syntax (sort of like WHERE clauses). For instance, to include only closed sales opportunities, we'll use this filter: - sql: $is_closed = true

- name: average_sales_cycle_length
  entity: opportunity
  filters:
    - sql: $is_closed = true

See the Filters documentation for more information.

Step 4: Choose an Operation and Measure

Choose the operation that your metric will perform on the data. These are the available operations to choose from:

• avg
• sum
• count
• min
• max

Specifying the measure determines the value that the metric calculates for each relevant entity.

In our instance, the average_sales_cycle_length metric uses an avg operation. Our measure in each opportunity will be the number of days between the opportunity creation to close. Translated to an SQL expression, it looks like this: $close_date - $created_date.

- name: average_sales_cycle_length
  entity: opportunity
  filters:
    - sql: $is_closed = true
  operation: avg
  measure: $close_date - $created_date

See the Measure documentation for more information.

Step 5: Configure the X-Axis

Finally, the x-axis defines the way the metric calculates the results of each time period. This requires a sort of "anchoring" logic by which entities are assigned the period in which they affect the metric results.

In our example metric, we want to measure the average_sales_cycle_length of opportunities that closed during each time period, based on their close_date:

- name: average_sales_cycle_length
  entity: opportunity
  filters:
    - sql: $is_closed = true
  operation: avg
  measure: $close_date - $created_date
  x_axis:
    period:
      - sql: $macros.PERIOD_IN($close_date)

Learn more about X-axis configuration here.

Wrapping up

Building your first metric in Sightfull is a foundational step toward leveraging data analytics for your business. Remember, the keys to building effective metrics are clarity in your objective, accurate configuration, and continuous refinement.

For further guidance on specific metric properties visit our comprehensive syntax documentation on filters, measures, periods, x-axis configuration, and joins.

You can also read more about metric building best practices here.

Start building your metric today and unlock the power of data-driven insights. 🚀