Joins

In Sightfull, the joins property in metric schemas opens the door to creating advanced metrics by leveraging the relationships between different entities. This capability allows for more nuanced and comprehensive data analysis by connecting related data points across various entities.

What are joins?

In standard SQL, a join is a method for combining rows from two or more tables based on a related column between them. In Sightfull, joins serve a similar purpose but are tailored to the platform's data modeling and analysis needs. They allow users to construct metrics that incorporate data from objects that are related to the main metric entity, enriching the insights derived from the analysis.

Joins schema

The joins property in a metric schema includes several keys, each serving a specific function:

Schema property	Description	Example
from	The relationship used to roll/look-up other entities for the metric calculation.	opportunities
select	The dimension(s) required from the related entity.	$id
alias	The label for the selection we are making.	opportunity_ids
filters	Optional SQL filters to select the subset of related entities we need.	- sql: $created_date <= $period.start
join_type	Determines the type of join (e.g., inner_join, left_join).	inner_join

For instance, the following metric schema uses the joins property to count the number of open opportunities each account had at the start of the period:

 - name: account_starting_open_opportunity
    entity: account
    operation: sum
    joins:
      - from: opportunities
        select: $id
        alias: opportunity_ids
        filters:
          - sql: $created_date <= $period.start
          - sql: $close_date > $period.start
        join_type: inner_join
    measure: count(distinct $opportunities)

Using joins

The joins property is a powerful tool for creating metrics that reflect complex relationships and aggregations across different entities. There are two ways joins can be used in the metric:

• One-to-one: access a single object the main entity is related to. For example the account an opportunity belongs to.
• One-to-many: aggregate multiple objects that relate to the main entity. For example all the line items in an opportunity.

One-to-one

One-to-one joins are used when a metric is based on a relationship to a single related entity. This allows for the direct selection of any dimensions on that entity for use in metric calculation inside the measure property.

For example, this metric counts the number of opportunities that have an active account:

 - name: opportunities_with_active_account
    entity: opportunity
    operation: count
    joins:
      - from: account
        select: $id
        alias: active_accounts
        filters:
          - sql: $is_active = true
        join_type: inner_join
    x_axis:
      period:
        - sql: $macros.PERIOD_IN($created_date)

One-to-many

In one-to-many joins, you can select multiple values from related entities and perform various SQL operations on them within the metric's measure (e.g., min(), max(), count(), sum()). Using the optional filters key within joins, you can further refine these selections to include only those entities that meet specific criteria.

For example, this metric sums the amount of the recurring line items of each oppotunity:

 - name: opportunity_line_items_amount
    entity: opportunity
    operation: sum
    joins:
      - from: line_items
        select: $amount_in_usd
        alias: line_item_amount
        filters:
          - sql: $is_recurring = true
        join_type: inner_join
    x_axis:
      period:
        - sql: $macros.PERIOD_IN($created_date)
    measure: sum($line_item_amount)

info

Note: When joining to multiple objects, an aggregation operation is required to calculate the metric value for the main entity; in the above example the main entity is the opportunity and the sum() operation is used to aggregate the amount of all line_items related to that opportunity.

Join type

The join_type can be either left_join or inner_join, mirroring standard SQL behavior. left_join includes all records from the entity table regardless of their matches in the "joined" table, while inner_join returns only entities where there is at least one relevant match in the "joined" table. The choice of join type affects the resulting dataset and should be selected based on the specific analytical requirements.

Wrapping up

Understanding and effectively utilizing the joins property in Sightfull metric schemas can significantly enhance the depth and relevance of your data analysis, providing a more interconnected view of various business entities.