Instantly share code, notes, and snippets.

View fb_conversions.lookml
view: fb_conversions {
derived_table: {
sql:
select *
from fivetran.fb_ad_insights_action_values
where action_type = "offsite_conversion.fb_pixel_purchase" ;;
}
dimension: primary_key {
type: string
View facebook-ad-insights-view.lookml
view: fb_ad_insights {
sql_table_name: fivetran.fb_ad_insights ;;
dimension: primary_key {
type: string
sql: concat(cast(${date_date} as string), ${campaign_id}, ${country}) ;;
hidden: yes
primary_key: yes
}
View facebook-ad-insights-joining.lookml
explore: fb_ad_insights {
label: "Facebook"
view_label: "Facebook"
join: fb_conversions {
view_label: "Facebook"
type: left_outer
relationship: one_to_one
sql_on: ${fb_ad_insights.primary_key} = ${fb_conversions.primary_key} ;;
}
View ad-insights-primary-key.lookml
dimension: primary_key {
type: string
sql: concat(cast(${date_date} as string), ${campaign_id}, ${country}) ;;
hidden: yes
primary_key: yes
}
View facebook-conversion-value.sql
select
campaign_id,
sum(value) as total_conversion_value
from facebook_ad_insights.ad_insights_action_values
where
action_type = 'offsite_conversion.fb_pixel_purchase' and
date = '2018-10-01'
group by 1
order by 1
View facebook-ad-insights-spend.sql
select
campaign_id,
sum(spend) as total_spend,
sum(impressions) as total_impressions,
sum(spend) / sum(impressions) * 1000 as cpm,
sum(inline_link_clicks) as link_clicks,
sum(inline_link_clicks) / sum(impressions) as ctr
from facebook_ad_insights.ad_insights
where date = '2018-10-01'
group by 1
View base-model-macro-variable.sql
select
id,
email
from {{ source(var('base.users')) }}
View dbt-source.sql
{% macro source(table_name) %}
{% if target.name == 'prod' %}
{{ return("preceden_heroku." ~ table_name) }}
{% else %}
{{ return(table_name) }}
{% endif %}
{% endmacro %}
View base-model.sql
select
id,
email
from {{ var('base.users') }}
View base-table.yml
vars:
"base.users" : "users"