Management Measures Monthly

view: management_measures_monthly {
  sql_table_name: "<% parameter schema_marketing %>"."ManagementMeasuresMonthly" ;;

  dimension: business_type {
    sql: ${TABLE}."BusinessType" ;;
  }

  dimension: checksum_id {
    sql: ${TABLE}."ChecksumId" ;;
  }

  dimension: country_code {
    sql: ${TABLE}."CountryCode" ;;
  }

  dimension: destination_currency {
    sql: ${TABLE}."DestinationCurrency" ;;
  }

  dimension: exchange_rate_type {
    sql: ${TABLE}."ExchangeRateType" ;;
  }

  dimension: flg_current_month {
    sql: ${TABLE}."FlgCurrentMonth" ;;
  }

  dimension: flg_current_year {
    sql: ${TABLE}."FlgCurrentYear" ;;
  }

  dimension: flg_last12_months {
    sql: ${TABLE}."FlgLast12Months" ;;
  }

  dimension: flg_month_in_future {
    sql: ${TABLE}."FlgMonthInFuture" ;;
  }

  dimension: load_id {
    type: int
    sql: ${TABLE}."LoadId" ;;
  }

  dimension: source_currency {
    sql: ${TABLE}."SourceCurrency" ;;
  }

  dimension: timezone {
    sql: ${TABLE}."Timezone" ;;
  }

  dimension: version_id {
    sql: ${TABLE}."VersionId" ;;
  }

  dimension: year_month {
    type: int
    sql: ${TABLE}."YearMonth" ;;
  }

  dimension: first_day_of_month {
    type: time
    sql: Cast(to_char(Cast(Cast(${TABLE}."YearMonth" As text) || '01' As date), 'YYYY-MM-DD') As date) ;;
  }

  dimension_group: last_updated {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}."LastUpdatedOn" ;;
  }

  measure: contingent_customer_volume {
    type: sum
    sql: ${TABLE}."ContingentCustomerVolume" ;;
  }

  measure: contingent_customer_volume_last_month {
    type: sum
    sql: ${TABLE}."ContingentCustomerVolume_LastMonth" ;;
  }

  measure: contingent_customer_volume_last_year {
    type: sum
    sql: ${TABLE}."ContingentCustomerVolume_LastYear" ;;
  }

  measure: contingent_order_count_activated {
    type: sum
    sql: ${TABLE}."ContingentOrderCountActivated" ;;
  }

  measure: contingent_order_count_cancelled {
    type: sum
    sql: ${TABLE}."ContingentOrderCountCancelled" ;;
  }

  measure: contingent_order_count_created {
    type: sum
    sql: ${TABLE}."ContingentOrderCountCreated" ;;
  }

  measure: contingent_order_count_expired {
    type: sum
    sql: ${TABLE}."ContingentOrderCountExpired" ;;
  }

  measure: contingent_order_count_gross {
    type: sum
    sql: ${TABLE}."ContingentOrderCountGross" ;;
  }

  measure: contingent_order_count_gross_last_month {
    type: sum
    sql: ${TABLE}."ContingentOrderCountGross_LastMonth" ;;
  }

  measure: contingent_order_count_gross_last_year {
    type: sum
    sql: ${TABLE}."ContingentOrderCountGross_LastYear" ;;
  }

  measure: contingent_package_certificates {
    type: sum
    sql: ${TABLE}."ContingentPackageCertificates" ;;
  }

  measure: contingent_package_certificates_last_month {
    type: sum
    sql: ${TABLE}."ContingentPackageCertificates_LastMonth" ;;
  }

  measure: contingent_package_certificates_last_year {
    type: sum
    sql: ${TABLE}."ContingentPackageCertificates_LastYear" ;;
  }

  measure: contingent_package_volume {
    type: sum
    sql: ${TABLE}."ContingentPackageVolume" ;;
  }

  measure: contingent_package_volume_last_month {
    type: sum
    sql: ${TABLE}."ContingentPackageVolume_LastMonth" ;;
  }

  measure: contingent_package_volume_last_year {
    type: sum
    sql: ${TABLE}."ContingentPackageVolume_LastYear" ;;
  }

  measure: contingent_partner_volume {
    type: sum
    sql: ${TABLE}."ContingentPartnerVolume" ;;
  }

  measure: contingent_volume_activated {
    type: sum
    sql: ${TABLE}."ContingentVolumeActivated" ;;
  }

  measure: contingent_volume_cancelled {
    type: sum
    sql: ${TABLE}."ContingentVolumeCancelled" ;;
  }

  measure: contingent_volume_created {
    type: sum
    sql: ${TABLE}."ContingentVolumeCreated" ;;
  }

  measure: contingent_volume_expired {
    type: sum
    sql: ${TABLE}."ContingentVolumeExpired" ;;
  }

  measure: contingent_volume_gross {
    type: sum
    sql: ${TABLE}."ContingentVolumeGross" ;;
  }

  measure: customer_volume {
    type: sum
    sql: ${TABLE}."CustomerVolume" ;;
  }

  measure: customer_volume_forecast {
    type: sum
    sql: ${TABLE}."CustomerVolumeForecast" ;;
  }

  measure: customer_volume_last_month {
    type: sum
    sql: ${TABLE}."CustomerVolume_LastMonth" ;;
  }

  measure: customer_volume_last_year {
    type: sum
    sql: ${TABLE}."CustomerVolume_LastYear" ;;
  }

  measure: customer_volume_projected {
    type: sum
    sql: ${TABLE}."CustomerVolumeProjected" ;;
  }

  measure: insurance_risk_volume {
    type: sum
    sql: ${TABLE}."InsuranceRiskVolume" ;;
  }

  measure: insurance_risk_volume_cancelled {
    type: sum
    sql: ${TABLE}."InsuranceRiskVolumeCancelled" ;;
  }

  measure: insurance_tax_volume {
    type: sum
    sql: ${TABLE}."InsuranceTaxVolume" ;;
  }

  measure: insurance_tax_volume_cancelled {
    type: sum
    sql: ${TABLE}."InsuranceTaxVolumeCancelled" ;;
  }

  measure: margin_volume_cancelled {
    type: sum
    sql: ${TABLE}."MarginVolumeCancelled" ;;
  }

  measure: margin_volume_gross {
    type: sum
    sql: ${TABLE}."MarginVolumeGross" ;;
  }

  measure: margin_volume_gross_last_month {
    type: sum
    sql: ${TABLE}."MarginVolumeGross_LastMonth" ;;
  }

  measure: margin_volume_gross_last_year {
    type: sum
    sql: ${TABLE}."MarginVolumeGross_LastYear" ;;
  }

  measure: marketing_acquisition_costs {
    type: sum
    sql: ${TABLE}."MarketingAcquisitionCosts" ;;
  }

  measure: marketing_promotion_costs {
    type: sum
    sql: ${TABLE}."MarketingPromotionCosts" ;;
  }

  measure: marketing_total_costs {
    type: sum
    sql: ${TABLE}."MarketingTotalCosts" ;;
  }

  measure: order_count_activated {
    type: sum
    sql: ${TABLE}."OrderCountActivated" ;;
  }

  measure: order_count_cancelled {
    type: sum
    sql: ${TABLE}."OrderCountCancelled" ;;
  }

  measure: order_count_created {
    type: sum
    sql: ${TABLE}."OrderCountCreated" ;;
  }

  measure: order_count_expired {
    type: sum
    sql: ${TABLE}."OrderCountExpired" ;;
  }

  measure: order_count_gross {
    type: sum
    sql: ${TABLE}."OrderCountGross" ;;
  }

  measure: order_count_gross_budget {
    type: sum
    sql: ${TABLE}."OrderCountGrossBudget" ;;
  }

  measure: order_count_gross_forecast {
    type: sum
    sql: ${TABLE}."OrderCountGrossForecast" ;;
  }

  measure: order_count_gross_last_month {
    type: sum
    sql: ${TABLE}."OrderCountGross_LastMonth" ;;
  }

  measure: order_count_gross_last_year {
    type: sum
    sql: ${TABLE}."OrderCountGross_LastYear" ;;
  }

  measure: order_count_gross_projected {
    type: sum
    sql: ${TABLE}."OrderCountGrossProjected" ;;
  }

  measure: order_count_upgraded {
    type: sum
    sql: ${TABLE}."OrderCountUpgraded" ;;
  }

  measure: order_volume_activated {
    type: sum
    sql: ${TABLE}."OrderVolumeActivated" ;;
  }

  measure: order_volume_cancelled {
    type: sum
    sql: ${TABLE}."OrderVolumeCancelled" ;;
  }

  measure: order_volume_created {
    type: sum
    sql: ${TABLE}."OrderVolumeCreated" ;;
  }

  measure: order_volume_expired {
    type: sum
    sql: ${TABLE}."OrderVolumeExpired" ;;
  }

  measure: order_volume_gross {
    type: sum
    sql: ${TABLE}."OrderVolumeGross" ;;
  }

  measure: order_volume_gross_budget {
    type: sum
    sql: ${TABLE}."OrderVolumeGrossBudget" ;;
  }

  measure: order_volume_gross_last_month {
    type: sum
    sql: ${TABLE}."OrderVolumeGross_LastMonth" ;;
  }

  measure: order_volume_gross_last_year {
    type: sum
    sql: ${TABLE}."OrderVolumeGross_LastYear" ;;
  }

  measure: order_volume_upgraded {
    type: sum
    sql: ${TABLE}."OrderVolumeUpgraded" ;;
  }

  measure: partner_volume {
    type: sum
    sql: ${TABLE}."PartnerVolume" ;;
  }

  measure: partner_volume_cancelled {
    type: sum
    sql: ${TABLE}."PartnerVolumeCancelled" ;;
  }

  measure: payment_costs {
    type: sum
    sql: ${TABLE}."PaymentCosts" ;;
  }

  measure: personell_costs {
    type: sum
    sql: ${TABLE}."PersonellCosts" ;;
  }

  measure: voucher_count {
    type: sum
    sql: ${TABLE}."VoucherCount" ;;
  }

  measure: voucher_volume {
    type: sum
    sql: ${TABLE}."VoucherVolume" ;;
  }

  measure: gross_revenue_before_cancel {
    label: "Gross Revenue (before Can.)"
    type: sum
    sql: ${TABLE}."CustomerVolume" - ${TABLE}."ContingentCustomerVolume" + ${TABLE}."ContingentPackageVolume" ;;
  }

  measure: gross_revenue_before_cancel_mom {
    label: "Gross Revenue (before Can.) MoM"
    type: int
    value_format: "0.00"
    sql: Coalesce( NullIf(SUM(${TABLE}."CustomerVolume" - ${TABLE}."ContingentCustomerVolume" + ${TABLE}."ContingentPackageVolume"),0) / NullIf(SUM(${TABLE}."CustomerVolume_LastMonth" - ${TABLE}."ContingentCustomerVolume_LastMonth" + ${TABLE}."ContingentPackageVolume_LastMonth"),0) - 1 ,0) * 100 ;;
  }

  measure: gross_revenue_before_cancel_projected {
    label: "Gross Revenue (before Can.) Projected"
    type: sum
    sql: ${TABLE}."CustomerVolumeProjected" ;;
  }

  measure: count {
    type: count
  }
}

- view: management_measures_monthly
  sql_table_name: '"<% parameter schema_marketing %>"."ManagementMeasuresMonthly"'
  fields:
  - dimension: business_type
    sql: ${TABLE}."BusinessType"
  - dimension: checksum_id
    sql: ${TABLE}."ChecksumId"
  - dimension: country_code
    sql: ${TABLE}."CountryCode"
  - dimension: destination_currency
    sql: ${TABLE}."DestinationCurrency"
  - dimension: exchange_rate_type
    sql: ${TABLE}."ExchangeRateType"
  - dimension: flg_current_month
    sql: ${TABLE}."FlgCurrentMonth"
  - dimension: flg_current_year
    sql: ${TABLE}."FlgCurrentYear"
  - dimension: flg_last12_months
    sql: ${TABLE}."FlgLast12Months"
  - dimension: flg_month_in_future
    sql: ${TABLE}."FlgMonthInFuture"
  - dimension: load_id
    type: int
    sql: ${TABLE}."LoadId"
  - dimension: source_currency
    sql: ${TABLE}."SourceCurrency"
  - dimension: timezone
    sql: ${TABLE}."Timezone"
  - dimension: version_id
    sql: ${TABLE}."VersionId"
  - dimension: year_month
    type: int
    sql: ${TABLE}."YearMonth"
  - dimension: first_day_of_month
    type: time
    sql: Cast(to_char(Cast(Cast(${TABLE}."YearMonth" As text) || '01' As date), 'YYYY-MM-DD') As date)
  - dimension_group: last_updated
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}."LastUpdatedOn"
  - measure: contingent_customer_volume
    type: sum
    sql: ${TABLE}."ContingentCustomerVolume"
  - measure: contingent_customer_volume_last_month
    type: sum
    sql: ${TABLE}."ContingentCustomerVolume_LastMonth"
  - measure: contingent_customer_volume_last_year
    type: sum
    sql: ${TABLE}."ContingentCustomerVolume_LastYear"
  - measure: contingent_order_count_activated
    type: sum
    sql: ${TABLE}."ContingentOrderCountActivated"
  - measure: contingent_order_count_cancelled
    type: sum
    sql: ${TABLE}."ContingentOrderCountCancelled"
  - measure: contingent_order_count_created
    type: sum
    sql: ${TABLE}."ContingentOrderCountCreated"
  - measure: contingent_order_count_expired
    type: sum
    sql: ${TABLE}."ContingentOrderCountExpired"
  - measure: contingent_order_count_gross
    type: sum
    sql: ${TABLE}."ContingentOrderCountGross"
  - measure: contingent_order_count_gross_last_month
    type: sum
    sql: ${TABLE}."ContingentOrderCountGross_LastMonth"
  - measure: contingent_order_count_gross_last_year
    type: sum
    sql: ${TABLE}."ContingentOrderCountGross_LastYear"
  - measure: contingent_package_certificates
    type: sum
    sql: ${TABLE}."ContingentPackageCertificates"
  - measure: contingent_package_certificates_last_month
    type: sum
    sql: ${TABLE}."ContingentPackageCertificates_LastMonth"
  - measure: contingent_package_certificates_last_year
    type: sum
    sql: ${TABLE}."ContingentPackageCertificates_LastYear"
  - measure: contingent_package_volume
    type: sum
    sql: ${TABLE}."ContingentPackageVolume"
  - measure: contingent_package_volume_last_month
    type: sum
    sql: ${TABLE}."ContingentPackageVolume_LastMonth"
  - measure: contingent_package_volume_last_year
    type: sum
    sql: ${TABLE}."ContingentPackageVolume_LastYear"
  - measure: contingent_partner_volume
    type: sum
    sql: ${TABLE}."ContingentPartnerVolume"
  - measure: contingent_volume_activated
    type: sum
    sql: ${TABLE}."ContingentVolumeActivated"
  - measure: contingent_volume_cancelled
    type: sum
    sql: ${TABLE}."ContingentVolumeCancelled"
  - measure: contingent_volume_created
    type: sum
    sql: ${TABLE}."ContingentVolumeCreated"
  - measure: contingent_volume_expired
    type: sum
    sql: ${TABLE}."ContingentVolumeExpired"
  - measure: contingent_volume_gross
    type: sum
    sql: ${TABLE}."ContingentVolumeGross"
  - measure: customer_volume
    type: sum
    sql: ${TABLE}."CustomerVolume"
  - measure: customer_volume_forecast
    type: sum
    sql: ${TABLE}."CustomerVolumeForecast"
  - measure: customer_volume_last_month
    type: sum
    sql: ${TABLE}."CustomerVolume_LastMonth"
  - measure: customer_volume_last_year
    type: sum
    sql: ${TABLE}."CustomerVolume_LastYear"
  - measure: customer_volume_projected
    type: sum
    sql: ${TABLE}."CustomerVolumeProjected"
  - measure: insurance_risk_volume
    type: sum
    sql: ${TABLE}."InsuranceRiskVolume"
  - measure: insurance_risk_volume_cancelled
    type: sum
    sql: ${TABLE}."InsuranceRiskVolumeCancelled"
  - measure: insurance_tax_volume
    type: sum
    sql: ${TABLE}."InsuranceTaxVolume"
  - measure: insurance_tax_volume_cancelled
    type: sum
    sql: ${TABLE}."InsuranceTaxVolumeCancelled"
  - measure: margin_volume_cancelled
    type: sum
    sql: ${TABLE}."MarginVolumeCancelled"
  - measure: margin_volume_gross
    type: sum
    sql: ${TABLE}."MarginVolumeGross"
  - measure: margin_volume_gross_last_month
    type: sum
    sql: ${TABLE}."MarginVolumeGross_LastMonth"
  - measure: margin_volume_gross_last_year
    type: sum
    sql: ${TABLE}."MarginVolumeGross_LastYear"
  - measure: marketing_acquisition_costs
    type: sum
    sql: ${TABLE}."MarketingAcquisitionCosts"
  - measure: marketing_promotion_costs
    type: sum
    sql: ${TABLE}."MarketingPromotionCosts"
  - measure: marketing_total_costs
    type: sum
    sql: ${TABLE}."MarketingTotalCosts"
  - measure: order_count_activated
    type: sum
    sql: ${TABLE}."OrderCountActivated"
  - measure: order_count_cancelled
    type: sum
    sql: ${TABLE}."OrderCountCancelled"
  - measure: order_count_created
    type: sum
    sql: ${TABLE}."OrderCountCreated"
  - measure: order_count_expired
    type: sum
    sql: ${TABLE}."OrderCountExpired"
  - measure: order_count_gross
    type: sum
    sql: ${TABLE}."OrderCountGross"
  - measure: order_count_gross_budget
    type: sum
    sql: ${TABLE}."OrderCountGrossBudget"
  - measure: order_count_gross_forecast
    type: sum
    sql: ${TABLE}."OrderCountGrossForecast"
  - measure: order_count_gross_last_month
    type: sum
    sql: ${TABLE}."OrderCountGross_LastMonth"
  - measure: order_count_gross_last_year
    type: sum
    sql: ${TABLE}."OrderCountGross_LastYear"
  - measure: order_count_gross_projected
    type: sum
    sql: ${TABLE}."OrderCountGrossProjected"
  - measure: order_count_upgraded
    type: sum
    sql: ${TABLE}."OrderCountUpgraded"
  - measure: order_volume_activated
    type: sum
    sql: ${TABLE}."OrderVolumeActivated"
  - measure: order_volume_cancelled
    type: sum
    sql: ${TABLE}."OrderVolumeCancelled"
  - measure: order_volume_created
    type: sum
    sql: ${TABLE}."OrderVolumeCreated"
  - measure: order_volume_expired
    type: sum
    sql: ${TABLE}."OrderVolumeExpired"
  - measure: order_volume_gross
    type: sum
    sql: ${TABLE}."OrderVolumeGross"
  - measure: order_volume_gross_budget
    type: sum
    sql: ${TABLE}."OrderVolumeGrossBudget"
  - measure: order_volume_gross_last_month
    type: sum
    sql: ${TABLE}."OrderVolumeGross_LastMonth"
  - measure: order_volume_gross_last_year
    type: sum
    sql: ${TABLE}."OrderVolumeGross_LastYear"
  - measure: order_volume_upgraded
    type: sum
    sql: ${TABLE}."OrderVolumeUpgraded"
  - measure: partner_volume
    type: sum
    sql: ${TABLE}."PartnerVolume"
  - measure: partner_volume_cancelled
    type: sum
    sql: ${TABLE}."PartnerVolumeCancelled"
  - measure: payment_costs
    type: sum
    sql: ${TABLE}."PaymentCosts"
  - measure: personell_costs
    type: sum
    sql: ${TABLE}."PersonellCosts"
  - measure: voucher_count
    type: sum
    sql: ${TABLE}."VoucherCount"
  - measure: voucher_volume
    type: sum
    sql: ${TABLE}."VoucherVolume"
  - measure: gross_revenue_before_cancel
    type: sum
    label: Gross Revenue (before Can.)
    sql: ${TABLE}."CustomerVolume" - ${TABLE}."ContingentCustomerVolume" + ${TABLE}."ContingentPackageVolume"
  - measure: gross_revenue_before_cancel_mom
    type: int
    label: Gross Revenue (before Can.) MoM
    value_format: 0.00
    sql: Coalesce( NullIf(SUM(${TABLE}."CustomerVolume" - ${TABLE}."ContingentCustomerVolume" + ${TABLE}."ContingentPackageVolume"),0) / NullIf(SUM(${TABLE}."CustomerVolume_LastMonth" - ${TABLE}."ContingentCustomerVolume_LastMonth" + ${TABLE}."ContingentPackageVolume_LastMonth"),0) - 1 ,0) * 100
  - measure: gross_revenue_before_cancel_projected
    type: sum
    label: Gross Revenue (before Can.) Projected
    sql: ${TABLE}."CustomerVolumeProjected"
  - measure: count
    type: count