Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Facebook Conversions View
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
sql: concat(cast(${date_date} as string), ${campaign_id}, ${country}) ;;
hidden: yes
primary_key: yes
}
dimension: _1_d_view {
type: number
sql: ${TABLE}._1_d_view ;;
hidden: yes
}
dimension: _28_d_click {
type: number
sql: ${TABLE}._28_d_click ;;
hidden: yes
}
dimension: _fivetran_synced {
type: number
sql: ${TABLE}._fivetran_synced ;;
hidden: yes
}
dimension: country {
type: string
sql: ${TABLE}.country ;;
hidden: yes
}
dimension: campaign_id {
type: string
sql: ${TABLE}.campaign_id ;;
hidden: yes
}
dimension_group: date {
label: "Conversion"
type: time
sql: ${TABLE}.`date` ;;
timeframes: [raw, date, week, month, quarter, year]
hidden: yes
}
dimension: index {
type: number
sql: ${TABLE}.index ;;
hidden: yes
}
dimension: value {
description: "The total value of all conversions attributed to your ads"
type: number
sql: ${TABLE}.value ;;
hidden: yes
}
measure: total_conversion_value {
description: "The total value of all conversions attributed to your ads"
type: sum
sql: ${value} ;;
value_format_name: usd
}
measure: roas {
label: "ROAS"
description: "Conversion Value / Spend"
type: number
sql: ${total_conversion_value} / nullif(${fb_ad_insights.total_spend}, 0) ;;
value_format_name: percent_0
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment