Retention MTD Performance vs Forecast - 2024.04.24

What is it?

A new viz added to the Retention dashboard and set of metrics added to the Customer Retention Performance MTD explore that show how each customer segment is performing month-to-date vs forecast.

In this case, all customer segments are overperforming vs forecast, so nothing to do here.

Why does this matter?

Your team can use this data in combination with our Audiences tool to target customers in under-performing segments in the hopes you can get them to purchase & help meet your brand’s financial goals for the month.

How are we calculating the forecast?

More or less, we are looking at what each customer spent on average during the same month of the previous year and then multiplying that by the current size of the segment. In more detail ...

1. We calculate average customer metrics for each segment in the same month of the previous year (e.g.: how much gross sales did each High-Value customer have for the month of April 2023?)

2. We then divide those averages by the number of days in the month so we get a daily average (e.g.: if the average high-value customer spent $300 in April 2023, that’s $100 per day)

3. We then multiply the daily average by the number of days elapsed in the current month and the number of customer currently in that segment.

This gives us the the amount of revenue, orders, etc we would expect to see for this segment in the current month, which can be compared against actuals.

How can you get this feature?

If you are a Growth merchant or an Enterprise merchant who onboarded after September 2023, you should already have the new visualization in your Retention dashboard, and the new metrics will already be in your Customer Retention Performance MTD explore.

If you are a legacy Enterprise merchant, you need to make the following changes to get the new metrics in the Customer Retention Performance MTD explore:

1.Create the /views/drp/retention_performance_forecast_append.view file

Under the /views/drp/ folder, create the retention_performance_forecast_append.view file with the following contents:

view: retention_performance_forecast_append {
  sql_table_name: DRP.RETENTION_PERFORMANCE_FORECAST_APPEND ;;

#
# MEASURES
#

  measure: forecast_average_order_gross_sales {
    group_label: "Forecast"
    group_item_label: "Average Order Gross Value (AOV)"
    label: "Forecast Average Order Gross Value (AOV)"
    type: number
    sql: 1.00 * ${forecast_total_gross_sales} / NULLIF(${forecast_total_orders},0) ;;
    value_format_name: decimal_2
    html: {{ retention_performance.currency_symbol_converted._value }}{{ rendered_value }} ;;
  }

  measure: forecast_average_order_net_sales {
    group_label: "Forecast"
    group_item_label: "Average Order Net Value (AOV)"
    label: "Forecast Average Order Net Value (AOV)"
    type: number
    sql: 1.00 * ${forecast_total_net_sales} / NULLIF(${forecast_total_orders},0) ;;
    value_format_name: decimal_2
    html: {{ retention_performance.currency_symbol_converted._value }}{{ rendered_value }} ;;
  }

  measure: forecast_average_units_per_transaction {
    group_label: "Forecast"
    group_item_label: "Average Units per Transaction (UPT)"
    label: "Forecast Average Units per Transaction (UPT)"
    type: number
    sql: 1.00 * ${forecast_total_units} / NULLIF(${forecast_total_orders},0) ;;
    value_format_name: decimal_1
  }

  measure: forecast_average_gross_revenue_per_unit {
    group_label: "Forecast"
    group_item_label: "Average Unit Gross Revenue (AUR)"
    label: "Forecast Average Unit Gross Revenue (AUR)"
    type: number
    sql: 1.00 * ${forecast_total_gross_sales} / NULLIF(${forecast_total_units},0) ;;
    value_format_name: decimal_2
    html: {{ retention_performance.currency_symbol_converted._value }}{{ rendered_value }} ;;
  }

  measure: forecast_average_net_revenue_per_unit {
    group_label: "Forecast"
    group_item_label: "Average Unit Net Revenue (AUR)"
    label: "Forecast Average Unit Net Revenue (AUR)"
    type: number
    sql: 1.00 * ${forecast_total_net_sales} / NULLIF(${forecast_total_units},0) ;;
    value_format_name: decimal_2
    html: {{ retention_performance.currency_symbol_converted._value }}{{ rendered_value }} ;;
  }

  measure: forecast_average_customer_gross_revenue {
    group_label: "Forecast"
    group_item_label: "Average Customer Gross Revenue"
    label: "Forecast Average Customer Gross Revenue"
    type: number
    sql: 1.00 * ${forecast_total_gross_sales} / NULLIF(${retention_performance.count_ordering_customers},0) ;;
    value_format_name: decimal_2
    html: {{ retention_performance.currency_symbol_converted._value }}{{ rendered_value }} ;;
  }

  measure: forecast_average_customer_net_revenue {
    group_label: "Forecast"
    group_item_label: "Average Customer Net Revenue"
    label: "Forecast Average Customer Net Revenue"
    type: number
    sql: 1.00 * ${forecast_total_net_sales} / NULLIF(${retention_performance.count_ordering_customers},0) ;;
    value_format_name: decimal_2
    html: {{ retention_performance.currency_symbol_converted._value }}{{ rendered_value }} ;;
  }

  measure: forecast_total_gross_sales {
    group_label: "Forecast"
    group_item_label: "Total Gross Sales"
    label: "Forecast Total Gross Sales"
    type: sum
    sql: ${TABLE}.forecast_gross_sales ;;
    value_format_name: decimal_0
    html: {{ retention_performance.currency_symbol_converted._value }}{{ rendered_value }} ;;
  }

  measure: forecast_total_net_sales {
    group_label: "Forecast"
    group_item_label: "Total Net Sales"
    label: "Forecast Total Net Sales"
    type: sum
    sql: ${TABLE}.forecast_net_sales ;;
    value_format_name: decimal_0
    html: {{ retention_performance.currency_symbol_converted._value }}{{ rendered_value }} ;;
  }

  measure: forecast_total_orders {
    group_label: "Forecast"
    group_item_label: "Count Orders"
    label: "Forecast Count Orders"
    type: sum
    sql: ${TABLE}.forecast_orders ;;
    value_format_name: decimal_0
  }

  measure: forecast_total_units {
    group_label: "Forecast"
    group_item_label: "Count Units"
    label: "Forecast Count Units"
    type: sum
    sql: ${TABLE}.forecast_units ;;
    value_format_name: decimal_0
  }


#
# HIDDEN DIMENSIONS
#

  dimension_group: __loaded {
    hidden: yes
    type: time
    timeframes: [raw, time, date, week, month, quarter, year]
    sql: ${TABLE}."__LOADED_AT" ;;
  }

  dimension: customer_segment {
    hidden: yes
    type: string
    sql: ${TABLE}."CUSTOMER_SEGMENT" ;;
  }

  dimension: forecast_gross_sales {
    hidden: yes
    type: number
    sql: ${TABLE}."FORECAST_GROSS_SALES" ;;
  }

  dimension: forecast_net_sales {
    hidden: yes
    type: number
    sql: ${TABLE}."FORECAST_NET_SALES" ;;
  }

  dimension: forecast_orders {
    hidden: yes
    type: number
    sql: ${TABLE}."FORECAST_ORDERS" ;;
  }

  dimension: forecast_units {
    hidden: yes
    type: number
    sql: ${TABLE}."FORECAST_UNITS" ;;
  }

  dimension: performance_period {
    hidden: yes
    type: string
    sql: ${TABLE}."PERFORMANCE_PERIOD" ;;
  }

  dimension: unique_customer_id {
    hidden: yes
    type: string
    sql: ${TABLE}."UNIQUE_CUSTOMER_ID" ;;
  }

  dimension: unique_id {
    hidden: yes
    primary_key: yes
    type: string
    sql: ${TABLE}."UNIQUE_ID" ;;
  }

}

2.Add the unique_id dimension to the /views/drp/retention_performance.view file

This field is needed for a proper join between the existing retention_performance view and the retention_performance_forecast_append view.

This is the field you need to add:

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

3.Add a join to the retention_performance explore

The retention_performance explore is typically included in the customers model.

This explore should be updated to look like this (the join is what is being added):

explore: retention_performance {
  label: "Customer Retention Performance MTD"
  view_label: "Customer Retention Performance MTD"

  join: retention_performance_forecast_append {
    view_label: "Customer Retention Performance MTD"
    type: left_outer
    relationship: one_to_one
    sql_on: ${retention_performance.unique_id} = ${retention_performance_forecast_append.unique_id} ;;
  }

}