Skip to content

Instantly share code, notes, and snippets.

@mattm
Created October 1, 2018 19:45
Show Gist options
  • Save mattm/96d25dc8e45d4cfc948824d4d558fdf7 to your computer and use it in GitHub Desktop.
Save mattm/96d25dc8e45d4cfc948824d4d558fdf7 to your computer and use it in GitHub Desktop.
Facebook Ad Insights 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
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment