Customer Code

This page provides detailed instructions on the Customer Code transform code block and what should be added to the Script Manifest file in order to execute the code

Script Manifest File (YML)

Upstream Transformation Dependencies

This code block is dependent on the following upstream code blocks being implemented:

• Initialization

• UOS

• Google Analytics

• Order Status

• Order Channel and Marketing

• Order and Order Line Revenue

Transformation Code Requirements

The following code should be added to the main workflow in the appropriate position, after Plan to Actual and before Subscription per the Transform Code Sequence - DRP page

  #--------------------------------------------------------------------------------------------------------------------
  # SECTION: 4600_CUSTOMER
  # DEPENDENCIES:
  #   - Section: 0500_UOS
  #   - Section: 2700_ORDER_STATUS
  #   - Section: 3000_CHANNEL
  #   - Section: 4300_ORDER_LINE_REVENUE
  # OPTIONAL:
  #   - Integration: Gorgias
  # REQUIRED: No
  # PURPOSE: This code the DRP tables for Customer / Customer Profiles and should be enabled once the upstream
  # blocks are enabled
  #--------------------------------------------------------------------------------------------------------------------
  customer:
    integrations:
    scripts:
      - "github://platform-sql-shared/scripts/base/4600_customer/4610_CUS_BAS_customers.sql"
      - "github://platform-sql-shared/scripts/pro/4600_customer/4615_CUS_BAS_monthly_customer_segments.sql"
      - "github://platform-sql-shared/scripts/pro/4600_customer/4619_CUS_BAS_customer_segment_last_week.sql"
      - "github://platform-sql-shared/scripts/pro/4600_customer/4620_CUS_BAS_customer_profiles.sql"
      - "github://platform-sql-shared/scripts/pro/4600_customer/4630_CUS_BAS_customer_fo_sku_lkp.sql"
      - "github://platform-sql-shared/scripts/pro/4600_customer/4640_CUS_BAS_retention_performance.sql"
      - "github://platform-sql-shared/scripts/base/4300_order_line_revenue/4325_OLR_CTM_sku_level_repurchase_rate.sql"

  customer_gorias:
    integrations:
      - gorgias
    scripts:
      # - "github://platform-sql-shared/scripts/base/4600_customer/4690_CUS_BAS_customer_profile_insert_gorgias.sql"

Customer

Add this code to enable customer and retention analysis by creating the following tables:

• [drp.customers]

• [drp.historical_customer_segmentation]

• [drp.customer_profiles]

• [drp.customers_fo_sku_lkp]

• [drp.retention_performance]

• [drp.sku_level_repurchase_rates]

Create the [drp.customers] table:

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/4600_customer/4610_CUS_BAS_customers.sql

Create the [drp.historical_customer_segmentation], which calculates the historical RFM segments, and determine last week's segment for each customer:

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/pro/4600_customer/4615_CUS_BAS_monthly_customer_segments.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/pro/4600_customer/4619_CUS_BAS_customer_segment_last_week.sql

Create the [drp.customer_profiles] table which summarizes customer acquisition and performance metrics:

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/pro/4600_customer/4620_CUS_BAS_customer_profiles.sql

Create a table that tracks the SKUs that a customer made in their first purchase [drp.customers_fo_sku_lkp]

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/pro/4600_customer/4630_CUS_BAS_customer_fo_sku_lkp.sql

Create the [drp.retention_performance] that tracks sales performance by segment for this month and last month

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/pro/4600_customer/4640_CUS_BAS_retention_performance.sql

Calculate SKU repurchase rates:

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/4300_order_line_revenue/4325_OLR_CTM_sku_level_repurchase_rate.sql

Customer - Gorgias

Add this code if you have the Gorgias extractor implemented and you would like to be able to track the impact of Customer Service at the customer level

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/4600_customer/4690_CUS_BAS_customer_profile_insert_gorgias.sql