Fivetran is now a dbt Metrics Ready Partner

Integration between Fivetran and dbt Semantic Layer lets you enhance data models with rich metric definitions and derive value from data quicker and easier.
October 18, 2022

At Fivetran, we have long provided dbt packages to enable teams to build off-the-shelf data models, helping analysts quickly bootstrap reports and dashboards. We have now taken these packages a step further and extended them to create pre-defined dbt metrics on top of the data models. This will make it even easier to quantitatively answer important business questions.

What’s included

Our first dbt metrics package extends our Ad Reporting data models with metrics for spend, engagements and clicks. These metrics include:

  • Average non-zero spend
  • Average spend
  • Bounce rate
  • Clicks
  • Clickthrough rate
  • Cost per click
  • Count of active ads
  • Impressions
  • Spend

These metrics can be filtered or aggregated by any of the following dimensions:

  • Account
  • Ad group
  • Ad platform
  • Campaign

How this provides value

By offering a central source of truth for key business metrics, the dbt Semantic Layer – with metric creation facilitated by Fivetran’s packages –  enables non-technical business stakeholders to self-serve slice-and-dice data in downstream tools. Ad metrics are essential to understanding how your business acquires prospects and customers. Combined with other metrics, such as sales, you can assemble a complete picture of your marketing and sales funnel.

Even better, you can use secondary calculations to help make longitudinal comparisons. Normally, this would require hand writing SQL window functions, which can be inaccessible to casual users and tedious even for experts. Using our dbt metrics packages, your analysts access window functions through a layer of abstraction provided by a Jinja constructor macro.

These secondary calculations include:

You can, of course, create custom metrics as well.

Like data models, these predefined business metrics are hosted on a Git project and benefit from version control and collaboration. This enables you to build a useful, granular semantic layer over your data models, giving your business users an easy way to interact with and understand your data.

How to get started

Getting started with Fivetran’s dbt metrics packages consists of the following steps:

  1. Install both the ad_reporting and metrics dbt packages in your project
  2. Run the ad reporting package models
  3. Use the metrics calculate macro
select * 
from {{ metrics.calculate(
	metric('new_customers'),    
  grain='week',    
  dimensions=['plan', 'country'],    
  secondary_calculations=[        
  	metrics.period_over_period(comparison_strategy="ratio", interval=1, alias="pop_1wk"),        
    metrics.period_over_period(comparison_strategy="difference", interval=1),‍        
    metrics.period_to_date(aggregate="average", period="month", alias="this_month_average"),        
    metrics.period_to_date(aggregate="sum", period="year"),‍        
    metrics.rolling(aggregate="average", interval=4, alias="avg_past_4wks"),        
    metrics.rolling(aggregate="min", interval=4)    ],    
start_date='2022-01-01',    
end_date='2022-12-31',    
where="some_column='filter_value'") }}

Stay tuned for future improvements. Fivetran and dbt Labs are always striving to make analytics accessible with fewer steps and less hassle.

Need a hands-on guide to using Fivetran with dbt Core? Attend this lab

Sign up!

Start for free

Join the thousands of companies using Fivetran to centralize and transform their data.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Blog

Fivetran is now a dbt Metrics Ready Partner

October 18, 2022
Fivetran is now a dbt Metrics Ready Partner
Topics
No items found.
Share
Integration between Fivetran and dbt Semantic Layer lets you enhance data models with rich metric definitions and derive value from data quicker and easier.

At Fivetran, we have long provided dbt packages to enable teams to build off-the-shelf data models, helping analysts quickly bootstrap reports and dashboards. We have now taken these packages a step further and extended them to create pre-defined dbt metrics on top of the data models. This will make it even easier to quantitatively answer important business questions.

What’s included

Our first dbt metrics package extends our Ad Reporting data models with metrics for spend, engagements and clicks. These metrics include:

  • Average non-zero spend
  • Average spend
  • Bounce rate
  • Clicks
  • Clickthrough rate
  • Cost per click
  • Count of active ads
  • Impressions
  • Spend

These metrics can be filtered or aggregated by any of the following dimensions:

  • Account
  • Ad group
  • Ad platform
  • Campaign

How this provides value

By offering a central source of truth for key business metrics, the dbt Semantic Layer – with metric creation facilitated by Fivetran’s packages –  enables non-technical business stakeholders to self-serve slice-and-dice data in downstream tools. Ad metrics are essential to understanding how your business acquires prospects and customers. Combined with other metrics, such as sales, you can assemble a complete picture of your marketing and sales funnel.

Even better, you can use secondary calculations to help make longitudinal comparisons. Normally, this would require hand writing SQL window functions, which can be inaccessible to casual users and tedious even for experts. Using our dbt metrics packages, your analysts access window functions through a layer of abstraction provided by a Jinja constructor macro.

These secondary calculations include:

You can, of course, create custom metrics as well.

Like data models, these predefined business metrics are hosted on a Git project and benefit from version control and collaboration. This enables you to build a useful, granular semantic layer over your data models, giving your business users an easy way to interact with and understand your data.

How to get started

Getting started with Fivetran’s dbt metrics packages consists of the following steps:

  1. Install both the ad_reporting and metrics dbt packages in your project
  2. Run the ad reporting package models
  3. Use the metrics calculate macro
select * 
from {{ metrics.calculate(
	metric('new_customers'),    
  grain='week',    
  dimensions=['plan', 'country'],    
  secondary_calculations=[        
  	metrics.period_over_period(comparison_strategy="ratio", interval=1, alias="pop_1wk"),        
    metrics.period_over_period(comparison_strategy="difference", interval=1),‍        
    metrics.period_to_date(aggregate="average", period="month", alias="this_month_average"),        
    metrics.period_to_date(aggregate="sum", period="year"),‍        
    metrics.rolling(aggregate="average", interval=4, alias="avg_past_4wks"),        
    metrics.rolling(aggregate="min", interval=4)    ],    
start_date='2022-01-01',    
end_date='2022-12-31',    
where="some_column='filter_value'") }}

Stay tuned for future improvements. Fivetran and dbt Labs are always striving to make analytics accessible with fewer steps and less hassle.

Need a hands-on guide to using Fivetran with dbt Core? Attend this lab

Sign up!
No items found.

Related blog posts

No items found.
No items found.
No items found.

Start for free

Join the thousands of companies using Fivetran to centralize and transform their data.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.