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:

• Period-over-period comparisons
• Period-to-date aggregates
• Rolling calculations such as averages and maxima
• Priors, i.e. records from a specified interval preceding a record

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!