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:

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

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

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

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

Calculate SKU repurchase rates:

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

PreviousPlan to Actual CodeNextSubscription Code

Last updated