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!

Kostenlos starten

Schließen auch Sie sich den Tausenden von Unternehmen an, die ihre Daten mithilfe von Fivetran zentralisieren und transformieren.

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

Fivetran is now a dbt Metrics Ready Partner

Fivetran is now a dbt Metrics Ready Partner

October 18, 2022
October 18, 2022
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.

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!
Topics
No items found.
Share

Verwandte Beiträge

No items found.
No items found.
Announcing Fivetran Managed Data Lake Service
Blog

Announcing Fivetran Managed Data Lake Service

Beitrag lesen
Introducing ChatGPT for SQL queries on Fivetran's HubSpot connector
Blog

Introducing ChatGPT for SQL queries on Fivetran's HubSpot connector

Beitrag lesen
How we use machine learning to improve our product
Blog

How we use machine learning to improve our product

Beitrag lesen

Kostenlos starten

Schließen auch Sie sich den Tausenden von Unternehmen an, die ihre Daten mithilfe von Fivetran zentralisieren und transformieren.

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