Facebook Ad Insights LookML

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
  }

  dimension: account_id {
    type: string
    sql: ${TABLE}.account_id ;;
  }

  dimension: account_name {
    type: string
    sql: ${TABLE}.account_name ;;
  }

  dimension: country {
    type: string
    sql: ${TABLE}.country ;;
  }

  dimension: ad_id {
    type: string
    sql: ${TABLE}.ad_id ;;
  }

  dimension: adset_id {
    type: string
    sql: ${TABLE}.adset_id ;;
  }

  dimension: campaign_id {
    type: string
    sql: ${TABLE}.campaign_id ;;
  }

  dimension: campaign_name {
    type: string
    sql: ${TABLE}.campaign_name ;;
  }

  dimension_group: date {
    label: "Report"
    type: time
    timeframes: [raw, date, week, month, quarter, year]
    sql: ${TABLE}.`date` ;;
  }

  dimension: impressions {
    type: number
    sql: ${TABLE}.impressions ;;
    hidden: yes
  }

  dimension: inline_link_clicks {
    type: number
    sql: ${TABLE}.inline_link_clicks ;;
    hidden: yes
  }

  dimension: spend {
    type: number
    sql: ${TABLE}.spend ;;
    hidden: yes
  }

  measure: total_spend {
    label: "Total Spend"
    description: "The estimated amount of money we've spent on these ads"
    type: sum
    sql: ${spend} ;;
    value_format_name: usd
  }

  measure: total_impressions {
    label: "Total Impressions"
    description: "The number of times our ads were on screen."
    type: sum
    sql: ${impressions} ;;
    value_format_name: decimal_0
  }

  measure: total_link_clicks {
    label: "Total Link Clicks"
    description: "The number of clicks on links within the ad that led to destinations or experiences, on or off Facebook"
    type: sum
    sql: ${inline_link_clicks} ;;
    value_format_name: decimal_0
  }

  measure: cpm_ {
    label: "CPM"
    description: "The average cost for 1,000 impressions"
    type: number
    sql: ${total_spend} / nullif(${total_impressions}, 0) * 1000 ;;
    value_format_name: usd
  }

  measure: ctr_amount {
    label: "CTR"
    description: "The percentage of times people saw your ad and performed a link click"
    type: number
    sql: ${total_link_clicks} / nullif(${total_impressions}, 0) ;;
    value_format_name: percent_2
  }
}