Facebook Conversions 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
    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
  }
}