New Blog Content LookML

new-blog-content.lookml

view: mp_new_blog_content {
  derived_table: {
    sql:
      SELECT
        current_url,
        MIN(TIMESTAMP_TRUNC(time, DAY)) AS published_at
      FROM mp.event
      WHERE
        current_url like "https://www.helpscout.net/blog/%"
      GROUP BY 1
      HAVING
        COUNT(*) > 200 AND
        MIN(time) >= "2018-04-04"
      ORDER BY 2 DESC ;;
    sql_trigger_value: FORMAT_DATETIME("%Y-%m-%d-%H", CURRENT_DATETIME()) ;;
  }

  dimension: current_url {
    type: string
    sql: ${TABLE}.current_url ;;
    hidden: yes
  }

  dimension: is_new_blog_content {
    label: "Is New Blog Content?"
    type: yesno
    sql: ${blog_post_publish_date} IS NOT NULL ;;
  }

  dimension_group: blog_post_publish {
    type: time
    timeframes: [
      date,
      week,
      month,
      quarter,
      year
    ]
    sql: ${TABLE}.published_at ;;
  }
}