Generic Variant Stats

view: generic_variant_stats {
  sql_table_name: v2.generic_variant_stats ;;

  dimension: id {
    type: int
    primary_key: yes
    sql: ${TABLE}.id ;;
  }

  dimension: adjusted_rewards {
    type: number
    sql: ${TABLE}.adjusted_rewards ;;
  }

  dimension: adjusted_suggestions {
    type: number
    sql: ${TABLE}.adjusted_suggestions ;;
  }

  dimension: code {
    sql: ${TABLE}.code ;;
  }

  dimension: reward_value_cnt {
    type: int
    sql: ${TABLE}.reward_value_cnt ;;
  }

  dimension: reward_value_sum {
    type: number
    sql: ${TABLE}.reward_value_sum ;;
  }

  dimension: rewards {
    type: number
    sql: ${TABLE}.rewards ;;
  }

  dimension: suggestions {
    type: int
    sql: ${TABLE}.suggestions ;;
  }

  dimension_group: created {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.created_at ;;
  }

  dimension_group: date {
    type: time
    timeframes: [date, week, month]
    convert_tz: no
    sql: ${TABLE}.date ;;
  }

  measure: count {
    type: count
    drill_fields: [id]
  }

  measure: adjusted_rewards_sum {
    type: sum
    sql: ${TABLE}.adjusted_rewards ;;
  }

  measure: adjusted_suggestions_sum {
    type: sum
    sql: ${TABLE}.adjusted_suggestions ;;
  }

  measure: rewards_sum {
    label: "Goals"
    type: sum
    sql: ${TABLE}.rewards ;;
  }

  measure: suggestions_sum {
    type: sum
    sql: ${TABLE}.suggestions ;;
  }

  measure: goal_rate {
    type: number
    sql: 100.0 * ${rewards_sum} / nullif(${suggestions_sum},0) ;;
  }

  measure: share_of_suggestions {
    label: "Percentage"
    type: percent_of_total
    sql: ${suggestions_sum} ;;
  }

  measure: variant_reward_value_cnt {
    type: sum
    sql: ${TABLE}.reward_value_cnt ;;
  }

  measure: variant_mean_reward {
    label: "Mean Goal Value"
    type: number
    sql: ${variant_reward_value_sum}/ nullif(${variant_reward_value_cnt},0) ;;
  }

  measure: goal_value_per_visitor {
    label: "Goal Value per Visitor"
    type: number
    sql: ${variant_reward_value_sum} / nullif(${suggestions_sum},0) ;;
  }
}

- view: generic_variant_stats
  sql_table_name: v2.generic_variant_stats
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: adjusted_rewards
    type: number
    sql: ${TABLE}.adjusted_rewards
  - dimension: adjusted_suggestions
    type: number
    sql: ${TABLE}.adjusted_suggestions
  - dimension: code
    sql: ${TABLE}.code
  - dimension: reward_value_cnt
    type: int
    sql: ${TABLE}.reward_value_cnt
  - dimension: reward_value_sum
    type: number
    sql: ${TABLE}.reward_value_sum
  - dimension: rewards
    type: number
    sql: ${TABLE}.rewards
  - dimension: suggestions
    type: int
    sql: ${TABLE}.suggestions
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.created_at
  - dimension_group: date
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.date
  - measure: count
    type: count
    drill_fields:
    - id
  - measure: adjusted_rewards_sum
    type: sum
    sql: ${TABLE}.adjusted_rewards
  - measure: adjusted_suggestions_sum
    type: sum
    sql: ${TABLE}.adjusted_suggestions
  - measure: rewards_sum
    type: sum
    label: Goals
    sql: ${TABLE}.rewards
  - measure: suggestions_sum
    type: sum
    sql: ${TABLE}.suggestions
  - measure: goal_rate
    type: number
    sql: 100.0 * ${rewards_sum} / nullif(${suggestions_sum},0)
  - measure: share_of_suggestions
    type: percent_of_total
    label: Percentage
    sql: ${suggestions_sum}
  - measure: variant_reward_value_cnt
    type: sum
    sql: ${TABLE}.reward_value_cnt
  - measure: variant_mean_reward
    type: number
    label: Mean Goal Value
    sql: ${variant_reward_value_sum}/ nullif(${variant_reward_value_cnt},0)
  - measure: goal_value_per_visitor
    type: number
    label: Goal Value per Visitor
    sql: ${variant_reward_value_sum} / nullif(${suggestions_sum},0)