Analytics

view: analytics {
  sql_table_name: analytics_2013_12_09 ;;

  dimension: module {
  }

  dimension: hour {
    type: time
  }

  dimension: app {
  }

  dimension: app_version {
  }

  dimension: publisher_id {
    hidden: yes
  }

  dimension: pict_url_md5 {
    hidden: yes
  }

  dimension: pict_info {
    sql: ${pict_url_md5} ;;
  }

  dimension: image {
    sql: ${pict_url_md5} ;;
  }

  dimension: visitor {
  }

  dimension: visitor_mod_xx {
    sql: SUBSTRING(${visitor}, 10, 2) ;;
  }

  dimension: advertiser_id {
    hidden: yes
  }

  dimension: ad_campaign_id {
    hidden: yes
  }

  dimension: ad_feature {
  }

  dimension: country {
  }

  dimension: us_international {
    label: "US/International"
    sql: CASE WHEN ${country} = 'US' THEN 'US' ELSE 'International' END ;;
  }

  dimension: dma {
    type: int
  }

  dimension: pict_id {
  }

  dimension: pict_mod_10000 {
    type: int
    sql: MOD(${pict_id},10000) ;;
  }

  dimension: app_tag {
  }

  dimension: device {
  }

  dimension: browser {
  }

  dimension: ver {
  }

  dimension: display_style {
  }

  dimension: relevance_engine {
  }

  dimension: auction_config {
  }

  measure: picture_count {
    type: count_distinct
    sql: ${pict_id} ;;
  }

  measure: m_auction_clicks {
    type: sum
    sql: COALESCE(m_auction_clicks, 0) ;;
  }

  measure: m_auction_impressions {
    type: sum
    sql: COALESCE(m_auction_impressions, 0) ;;
  }

  measure: m_auction_invalid_clicks {
    type: sum
    sql: COALESCE(m_auction_invalid_clicks, 0) ;;
  }

  measure: m_auction_opens {
    type: sum
    sql: COALESCE(m_auction_opens, 0) ;;
  }

  measure: m_available {
    type: sum
    sql: COALESCE(m_available, 0) ;;
  }

  measure: m_banner_impressions {
    type: sum
    sql: COALESCE(m_banner_impressions, 0) ;;
  }

  measure: m_canshares {
    type: sum
    sql: COALESCE(m_canshares, 0) ;;
  }

  measure: m_cardcloses {
    type: sum
    sql: COALESCE(m_cardcloses, 0) ;;
  }

  measure: m_cardshows {
    type: sum
    sql: COALESCE(m_cardshows, 0) ;;
  }

  measure: m_carousel_impressions {
    type: sum
    sql: COALESCE(m_carousel_impressions, 0) ;;
  }

  measure: m_clearing_price {
    type: sum
    sql: COALESCE(m_clearing_price, 0) ;;
  }

  measure: m_click_revenue {
    type: sum
    sql: COALESCE(m_click_revenue, 0) ;;
  }

  measure: m_clicks {
    type: sum
    sql: COALESCE(m_clicks, 0) ;;
  }

  measure: m_closes {
    type: sum
    sql: COALESCE(m_closes, 0) ;;
  }

  measure: m_compshares {
    type: sum
    sql: COALESCE(m_compshares, 0) ;;
  }

  measure: m_dehighlight_clicks {
    type: sum
    sql: COALESCE(m_dehighlight_clicks, 0) ;;
  }

  measure: m_email_returns {
    type: sum
    sql: COALESCE(m_email_returns, 0) ;;
  }

  measure: m_email_started {
    type: sum
    sql: COALESCE(m_email_started, 0) ;;
  }

  measure: m_facebook_completed {
    type: sum
    sql: COALESCE(m_facebook_completed, 0) ;;
  }

  measure: m_facebook_returns {
    type: sum
    sql: COALESCE(m_facebook_returns, 0) ;;
  }

  measure: m_facebook_started {
    type: sum
    sql: COALESCE(m_facebook_started, 0) ;;
  }

  measure: m_failed_requests {
    type: sum
    sql: COALESCE(m_failed_requests, 0) ;;
  }

  measure: m_failures_hbase_timeout {
    type: sum
    sql: COALESCE(m_failures_hbase_timeout, 0) ;;
  }

  measure: m_failures_missing_image {
    type: sum
    sql: COALESCE(m_failures_missing_image, 0) ;;
  }

  measure: m_failures_no_ad {
    type: sum
    sql: COALESCE(m_failures_no_ad, 0) ;;
  }

  measure: m_failures_no_keywords {
    type: sum
    sql: COALESCE(m_failures_no_keywords, 0) ;;
  }

  measure: m_failures_offensive_content {
    type: sum
    sql: COALESCE(m_failures_offensive_content, 0) ;;
  }

  measure: m_failures_offensive_context {
    type: sum
    sql: COALESCE(m_failures_offensive_context, 0) ;;
  }

  measure: m_failures_offensive_image {
    type: sum
    sql: COALESCE(m_failures_offensive_image, 0) ;;
  }

  measure: m_first_mouseovers {
    type: sum
    sql: COALESCE(m_first_mouseovers, 0) ;;
  }

  measure: m_gplus_returns {
    type: sum
    sql: COALESCE(m_gplus_returns, 0) ;;
  }

  measure: m_gplus_started {
    type: sum
    sql: COALESCE(m_gplus_started, 0) ;;
  }

  measure: m_highlight_clicks {
    type: sum
    sql: COALESCE(m_highlight_clicks, 0) ;;
  }

  measure: m_highlight_mouseovers {
    type: sum
    sql: COALESCE(m_highlight_mouseovers, 0) ;;
  }

  measure: m_image_exposed {
    type: sum
    sql: COALESCE(m_image_exposed, 0) ;;
  }

  measure: m_image_views {
    type: sum
    sql: COALESCE(m_image_views, 0) ;;
  }

  measure: m_imp_cancels {
    type: sum
    sql: COALESCE(m_imp_cancels, 0) ;;
  }

  measure: m_imp_shares {
    type: sum
    sql: COALESCE(m_imp_shares, 0) ;;
  }

  measure: m_impression_revenue {
    type: sum
    sql: COALESCE(m_impression_revenue, 0) ;;
  }

  measure: m_impression_time {
    type: sum
    sql: COALESCE(m_impression_time, 0) ;;
  }

  measure: m_impressions {
    type: sum
    sql: COALESCE(m_impressions, 0) ;;
  }

  measure: m_interactions {
    type: sum
    sql: COALESCE(m_interactions, 0) ;;
  }

  measure: m_interg5 {
    type: sum
    sql: COALESCE(m_interg5, 0) ;;
  }

  measure: m_interrupted {
    type: sum
    sql: COALESCE(m_interrupted, 0) ;;
  }

  measure: m_invalid_clicks {
    type: sum
    sql: COALESCE(m_invalid_clicks, 0) ;;
  }

  measure: m_mo_cancels {
    type: sum
    sql: COALESCE(m_mo_cancels, 0) ;;
  }

  measure: m_mo_shares {
    type: sum
    sql: COALESCE(m_mo_shares, 0) ;;
  }

  measure: m_moclick_cancels {
    type: sum
    sql: COALESCE(m_moclick_cancels, 0) ;;
  }

  measure: m_moclick_shares {
    type: sum
    sql: COALESCE(m_moclick_shares, 0) ;;
  }

  measure: m_mouseover_time {
    type: sum
    sql: COALESCE(m_mouseover_time, 0) ;;
  }

  measure: m_mouseovers {
    type: sum
    sql: COALESCE(m_mouseovers, 0) ;;
  }

  measure: m_no_datas {
    type: sum
    sql: COALESCE(m_no_datas, 0) ;;
  }

  measure: m_note_changes {
    type: sum
    sql: COALESCE(m_note_changes, 0) ;;
  }

  measure: m_opens {
    type: sum
    sql: COALESCE(m_opens, 0) ;;
  }

  measure: m_page_views {
    type: sum
    sql: COALESCE(m_page_views, 0) ;;
  }

  measure: m_performance_score {
    type: sum
    sql: COALESCE(m_performance_score, 0) ;;
  }

  measure: m_pins {
    type: sum
    sql: COALESCE(m_pins, 0) ;;
  }

  measure: m_pinterest_returns {
    type: sum
    sql: COALESCE(m_pinterest_returns, 0) ;;
  }

  measure: m_pinterest_started {
    type: sum
    sql: COALESCE(m_pinterest_started, 0) ;;
  }

  measure: m_playerclicks {
    type: sum
    sql: COALESCE(m_playerclicks, 0) ;;
  }

  measure: m_posshares {
    type: sum
    sql: COALESCE(m_posshares, 0) ;;
  }

  measure: m_postroll_ads {
    type: sum
    sql: COALESCE(m_postroll_ads, 0) ;;
  }

  measure: m_prompt_impression_time {
    type: sum
    sql: COALESCE(m_prompt_impression_time, 0) ;;
  }

  measure: m_relevance {
    type: sum
    sql: COALESCE(m_relevance, 0) ;;
  }

  measure: m_requests {
    type: sum
    sql: COALESCE(m_requests, 0) ;;
  }

  measure: m_return_visits {
    type: sum
    sql: COALESCE(m_return_visits, 0) ;;
  }

  measure: m_revenue {
    type: sum
    sql: COALESCE(m_revenue, 0) ;;
  }

  measure: m_scrolls {
    type: sum
    sql: COALESCE(m_scrolls, 0) ;;
  }

  measure: m_shares {
    type: sum
    sql: COALESCE(m_shares, 0) ;;
  }

  measure: m_shopped_image_views {
    type: sum
    sql: COALESCE(m_shopped_image_views, 0) ;;
  }

  measure: m_sketchy_page_views {
    type: sum
    sql: COALESCE(m_sketchy_page_views, 0) ;;
  }

  measure: m_tabclicks {
    type: sum
    sql: COALESCE(m_tabclicks, 0) ;;
  }

  measure: m_takeover_advertiser_impressions {
    type: sum
    sql: COALESCE(m_takeover_advertiser_impressions, 0) ;;
  }

  measure: m_takeover_background_clicks {
    type: sum
    sql: COALESCE(m_takeover_background_clicks, 0) ;;
  }

  measure: m_takeover_impression_time {
    type: sum
    sql: COALESCE(m_takeover_impression_time, 0) ;;
  }

  measure: m_takeover_product_clicks {
    type: sum
    sql: COALESCE(m_takeover_product_clicks, 0) ;;
  }

  measure: m_takeover_product_impressions {
    type: sum
    sql: COALESCE(m_takeover_product_impressions, 0) ;;
  }

  measure: m_takeover_revenue {
    type: sum
    sql: COALESCE(m_takeover_revenue, 0) ;;
  }

  measure: m_taskbar_closes {
    type: sum
    sql: COALESCE(m_taskbar_closes, 0) ;;
  }

  measure: m_taskbar_impressions {
    type: sum
    sql: COALESCE(m_taskbar_impressions, 0) ;;
  }

  measure: m_taskbar_time {
    type: sum
    sql: COALESCE(m_taskbar_time, 0) ;;
  }

  measure: m_text_shares {
    type: sum
    sql: COALESCE(m_text_shares, 0) ;;
  }

  measure: m_ticker_time {
    type: sum
    sql: COALESCE(m_ticker_time, 0) ;;
  }

  measure: m_timeouts {
    type: sum
    sql: COALESCE(m_timeouts, 0) ;;
  }

  measure: m_twitter_returns {
    type: sum
    sql: COALESCE(m_twitter_returns, 0) ;;
  }

  measure: m_twitter_started {
    type: sum
    sql: COALESCE(m_twitter_started, 0) ;;
  }

  measure: m_unavailable {
    type: sum
    sql: COALESCE(m_unavailable, 0) ;;
  }

  measure: m_unavailable_offensive_content {
    type: sum
    sql: COALESCE(m_unavailable_offensive_content, 0) ;;
  }

  measure: m_unbranded_advertiser_impressions {
    type: sum
    sql: COALESCE(m_unbranded_advertiser_impressions, 0) ;;
  }

  measure: m_unbranded_product_clicks {
    type: sum
    sql: COALESCE(m_unbranded_product_clicks, 0) ;;
  }

  measure: m_unbranded_product_impressions {
    type: sum
    sql: COALESCE(m_unbranded_product_impressions, 0) ;;
  }

  measure: m_unbranded_revenue {
    type: sum
    sql: COALESCE(m_unbranded_revenue, 0) ;;
  }

  measure: m_unpinned {
    type: sum
    sql: COALESCE(m_unpinned, 0) ;;
  }

  measure: m_widgets {
    type: sum
    sql: COALESCE(m_widgets, 0) ;;
  }

  measure: ctr {
    type: number
    sql: 100.0 * ${m_clicks} / NULLIF(${m_impressions}, 0) ;;
  }

  measure: count {
    type: count
  }
}

- view: analytics
  sql_table_name: analytics_2013_12_09
  fields:
  - dimension: module
  - dimension: hour
    type: time
  - dimension: app
  - dimension: app_version
  - dimension: publisher_id
    hidden: true
  - dimension: pict_url_md5
    hidden: true
  - dimension: pict_info
    sql: ${pict_url_md5}
  - dimension: image
    sql: ${pict_url_md5}
  - dimension: visitor
  - dimension: visitor_mod_xx
    sql: SUBSTRING(${visitor}, 10, 2)
  - dimension: advertiser_id
    hidden: true
  - dimension: ad_campaign_id
    hidden: true
  - dimension: ad_feature
  - dimension: country
  - dimension: us_international
    label: US/International
    sql: CASE WHEN ${country} = 'US' THEN 'US' ELSE 'International' END
  - dimension: dma
    type: int
  - dimension: pict_id
  - dimension: pict_mod_10000
    type: int
    sql: MOD(${pict_id},10000)
  - dimension: app_tag
  - dimension: device
  - dimension: browser
  - dimension: ver
  - dimension: display_style
  - dimension: relevance_engine
  - dimension: auction_config
  - measure: picture_count
    type: count_distinct
    sql: ${pict_id}
  - measure: m_auction_clicks
    type: sum
    sql: COALESCE(m_auction_clicks, 0)
  - measure: m_auction_impressions
    type: sum
    sql: COALESCE(m_auction_impressions, 0)
  - measure: m_auction_invalid_clicks
    type: sum
    sql: COALESCE(m_auction_invalid_clicks, 0)
  - measure: m_auction_opens
    type: sum
    sql: COALESCE(m_auction_opens, 0)
  - measure: m_available
    type: sum
    sql: COALESCE(m_available, 0)
  - measure: m_banner_impressions
    type: sum
    sql: COALESCE(m_banner_impressions, 0)
  - measure: m_canshares
    type: sum
    sql: COALESCE(m_canshares, 0)
  - measure: m_cardcloses
    type: sum
    sql: COALESCE(m_cardcloses, 0)
  - measure: m_cardshows
    type: sum
    sql: COALESCE(m_cardshows, 0)
  - measure: m_carousel_impressions
    type: sum
    sql: COALESCE(m_carousel_impressions, 0)
  - measure: m_clearing_price
    type: sum
    sql: COALESCE(m_clearing_price, 0)
  - measure: m_click_revenue
    type: sum
    sql: COALESCE(m_click_revenue, 0)
  - measure: m_clicks
    type: sum
    sql: COALESCE(m_clicks, 0)
  - measure: m_closes
    type: sum
    sql: COALESCE(m_closes, 0)
  - measure: m_compshares
    type: sum
    sql: COALESCE(m_compshares, 0)
  - measure: m_dehighlight_clicks
    type: sum
    sql: COALESCE(m_dehighlight_clicks, 0)
  - measure: m_email_returns
    type: sum
    sql: COALESCE(m_email_returns, 0)
  - measure: m_email_started
    type: sum
    sql: COALESCE(m_email_started, 0)
  - measure: m_facebook_completed
    type: sum
    sql: COALESCE(m_facebook_completed, 0)
  - measure: m_facebook_returns
    type: sum
    sql: COALESCE(m_facebook_returns, 0)
  - measure: m_facebook_started
    type: sum
    sql: COALESCE(m_facebook_started, 0)
  - measure: m_failed_requests
    type: sum
    sql: COALESCE(m_failed_requests, 0)
  - measure: m_failures_hbase_timeout
    type: sum
    sql: COALESCE(m_failures_hbase_timeout, 0)
  - measure: m_failures_missing_image
    type: sum
    sql: COALESCE(m_failures_missing_image, 0)
  - measure: m_failures_no_ad
    type: sum
    sql: COALESCE(m_failures_no_ad, 0)
  - measure: m_failures_no_keywords
    type: sum
    sql: COALESCE(m_failures_no_keywords, 0)
  - measure: m_failures_offensive_content
    type: sum
    sql: COALESCE(m_failures_offensive_content, 0)
  - measure: m_failures_offensive_context
    type: sum
    sql: COALESCE(m_failures_offensive_context, 0)
  - measure: m_failures_offensive_image
    type: sum
    sql: COALESCE(m_failures_offensive_image, 0)
  - measure: m_first_mouseovers
    type: sum
    sql: COALESCE(m_first_mouseovers, 0)
  - measure: m_gplus_returns
    type: sum
    sql: COALESCE(m_gplus_returns, 0)
  - measure: m_gplus_started
    type: sum
    sql: COALESCE(m_gplus_started, 0)
  - measure: m_highlight_clicks
    type: sum
    sql: COALESCE(m_highlight_clicks, 0)
  - measure: m_highlight_mouseovers
    type: sum
    sql: COALESCE(m_highlight_mouseovers, 0)
  - measure: m_image_exposed
    type: sum
    sql: COALESCE(m_image_exposed, 0)
  - measure: m_image_views
    type: sum
    sql: COALESCE(m_image_views, 0)
  - measure: m_imp_cancels
    type: sum
    sql: COALESCE(m_imp_cancels, 0)
  - measure: m_imp_shares
    type: sum
    sql: COALESCE(m_imp_shares, 0)
  - measure: m_impression_revenue
    type: sum
    sql: COALESCE(m_impression_revenue, 0)
  - measure: m_impression_time
    type: sum
    sql: COALESCE(m_impression_time, 0)
  - measure: m_impressions
    type: sum
    sql: COALESCE(m_impressions, 0)
  - measure: m_interactions
    type: sum
    sql: COALESCE(m_interactions, 0)
  - measure: m_interg5
    type: sum
    sql: COALESCE(m_interg5, 0)
  - measure: m_interrupted
    type: sum
    sql: COALESCE(m_interrupted, 0)
  - measure: m_invalid_clicks
    type: sum
    sql: COALESCE(m_invalid_clicks, 0)
  - measure: m_mo_cancels
    type: sum
    sql: COALESCE(m_mo_cancels, 0)
  - measure: m_mo_shares
    type: sum
    sql: COALESCE(m_mo_shares, 0)
  - measure: m_moclick_cancels
    type: sum
    sql: COALESCE(m_moclick_cancels, 0)
  - measure: m_moclick_shares
    type: sum
    sql: COALESCE(m_moclick_shares, 0)
  - measure: m_mouseover_time
    type: sum
    sql: COALESCE(m_mouseover_time, 0)
  - measure: m_mouseovers
    type: sum
    sql: COALESCE(m_mouseovers, 0)
  - measure: m_no_datas
    type: sum
    sql: COALESCE(m_no_datas, 0)
  - measure: m_note_changes
    type: sum
    sql: COALESCE(m_note_changes, 0)
  - measure: m_opens
    type: sum
    sql: COALESCE(m_opens, 0)
  - measure: m_page_views
    type: sum
    sql: COALESCE(m_page_views, 0)
  - measure: m_performance_score
    type: sum
    sql: COALESCE(m_performance_score, 0)
  - measure: m_pins
    type: sum
    sql: COALESCE(m_pins, 0)
  - measure: m_pinterest_returns
    type: sum
    sql: COALESCE(m_pinterest_returns, 0)
  - measure: m_pinterest_started
    type: sum
    sql: COALESCE(m_pinterest_started, 0)
  - measure: m_playerclicks
    type: sum
    sql: COALESCE(m_playerclicks, 0)
  - measure: m_posshares
    type: sum
    sql: COALESCE(m_posshares, 0)
  - measure: m_postroll_ads
    type: sum
    sql: COALESCE(m_postroll_ads, 0)
  - measure: m_prompt_impression_time
    type: sum
    sql: COALESCE(m_prompt_impression_time, 0)
  - measure: m_relevance
    type: sum
    sql: COALESCE(m_relevance, 0)
  - measure: m_requests
    type: sum
    sql: COALESCE(m_requests, 0)
  - measure: m_return_visits
    type: sum
    sql: COALESCE(m_return_visits, 0)
  - measure: m_revenue
    type: sum
    sql: COALESCE(m_revenue, 0)
  - measure: m_scrolls
    type: sum
    sql: COALESCE(m_scrolls, 0)
  - measure: m_shares
    type: sum
    sql: COALESCE(m_shares, 0)
  - measure: m_shopped_image_views
    type: sum
    sql: COALESCE(m_shopped_image_views, 0)
  - measure: m_sketchy_page_views
    type: sum
    sql: COALESCE(m_sketchy_page_views, 0)
  - measure: m_tabclicks
    type: sum
    sql: COALESCE(m_tabclicks, 0)
  - measure: m_takeover_advertiser_impressions
    type: sum
    sql: COALESCE(m_takeover_advertiser_impressions, 0)
  - measure: m_takeover_background_clicks
    type: sum
    sql: COALESCE(m_takeover_background_clicks, 0)
  - measure: m_takeover_impression_time
    type: sum
    sql: COALESCE(m_takeover_impression_time, 0)
  - measure: m_takeover_product_clicks
    type: sum
    sql: COALESCE(m_takeover_product_clicks, 0)
  - measure: m_takeover_product_impressions
    type: sum
    sql: COALESCE(m_takeover_product_impressions, 0)
  - measure: m_takeover_revenue
    type: sum
    sql: COALESCE(m_takeover_revenue, 0)
  - measure: m_taskbar_closes
    type: sum
    sql: COALESCE(m_taskbar_closes, 0)
  - measure: m_taskbar_impressions
    type: sum
    sql: COALESCE(m_taskbar_impressions, 0)
  - measure: m_taskbar_time
    type: sum
    sql: COALESCE(m_taskbar_time, 0)
  - measure: m_text_shares
    type: sum
    sql: COALESCE(m_text_shares, 0)
  - measure: m_ticker_time
    type: sum
    sql: COALESCE(m_ticker_time, 0)
  - measure: m_timeouts
    type: sum
    sql: COALESCE(m_timeouts, 0)
  - measure: m_twitter_returns
    type: sum
    sql: COALESCE(m_twitter_returns, 0)
  - measure: m_twitter_started
    type: sum
    sql: COALESCE(m_twitter_started, 0)
  - measure: m_unavailable
    type: sum
    sql: COALESCE(m_unavailable, 0)
  - measure: m_unavailable_offensive_content
    type: sum
    sql: COALESCE(m_unavailable_offensive_content, 0)
  - measure: m_unbranded_advertiser_impressions
    type: sum
    sql: COALESCE(m_unbranded_advertiser_impressions, 0)
  - measure: m_unbranded_product_clicks
    type: sum
    sql: COALESCE(m_unbranded_product_clicks, 0)
  - measure: m_unbranded_product_impressions
    type: sum
    sql: COALESCE(m_unbranded_product_impressions, 0)
  - measure: m_unbranded_revenue
    type: sum
    sql: COALESCE(m_unbranded_revenue, 0)
  - measure: m_unpinned
    type: sum
    sql: COALESCE(m_unpinned, 0)
  - measure: m_widgets
    type: sum
    sql: COALESCE(m_widgets, 0)
  - measure: ctr
    type: number
    sql: 100.0 * ${m_clicks} / NULLIF(${m_impressions}, 0)
  - measure: count
    type: count