Email & SMS

This page provides detailed instructions on the Email & SMS 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

• UNS

• Order Status

Transformation Code Requirements

The following code should be added to the main workflow in the appropriate position, after Product and Inventory per the Transform Code Sequence - DRP page

 #--------------------------------------------------------------------------------------------------------------------
  # SECTION: 7000_NOTIFICATIONS
  # DEPENDENCIES:
  #   - Section: 0500_UOS
  #   - Section: 1000_UNS
  #   - Section: 2700_ORDER_STATUS
  # REQUIRED: No
  # PURPOSE: This code the DRP tables for Notifications (Email/SMS performance) and should be enabled once the
  # upstream dependencies are enabled
  #--------------------------------------------------------------------------------------------------------------------
  notification_performance:
    integrations:
      - klaviyo
      - attentive_mobile
    scripts:
      - "github://platform-sql-shared/scripts/base/1000_uns/1499_UNS_BAS_unique_contacts.sql"
      - "github://platform-sql-shared/scripts/base/7000_notification_performance/7000_NTF_BAS_notification_contact_history_one_time.sql"
      - "github://platform-sql-shared/scripts/base/7000_notification_performance/7020_NTF_BAS_notification_contact_status_history.sql"
      - "github://platform-sql-shared/scripts/base/7000_notification_performance/7010_NTF_BAS_notification_file_size_history_monthly_one_time.sql"
      - "github://platform-sql-shared/scripts/base/7000_notification_performance/7015_NTF_BAS_notification_engaged_file_size.sql"
      - "github://platform-sql-shared/scripts/base/7000_notification_performance/7030_NTF_BAS_notification_campaign_detail_performance.sql"
      - "github://platform-sql-shared/scripts/pro/7000_notification_performance/7040_NTF_BAS_notification_campaign_purchase_view_through.sql"
      - "github://platform-sql-shared/scripts/base/7000_notification_performance/7050_NTF_BAS_notification_file_size_monthly.sql"
      - "github://platform-sql-shared/scripts/base/7000_notification_performance/7060_NTF_BAS_customer_notification_summary.sql"
      - "github://platform-sql-shared/scripts/base/7000_notification_performance/7070_NTF_BAS_notifications_customer_profile.sql"

Email & SMS

Add this code to create the tables that enable performance of your Email & SMS channels

• [drp.notification_engaged_file_size]

• [drp.notication_campaign_detail]

• [drp.notification_product_purchase_view_through]

• [drp.notification_file_size_monthly]

• [drp.notifications_customer_profile]

Create the [uns.unique_contacts_lkp] table to link contacts across the different Email, SMS providers if multiple are used

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/1000_uns/1499_UNS_BAS_unique_contacts.sql

Build the history tables to track status and file size over time

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/7000_notification_performance/7000_NTF_BAS_notification_contact_history_one_time.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/7000_notification_performance/7020_NTF_BAS_notification_contact_status_history.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/7000_notification_performance/7010_NTF_BAS_notification_file_size_history_monthly_one_time.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/7000_notification_performance/7015_NTF_BAS_notification_engaged_file_size.sql

Build the [drp.notication_campaign_detail] table to be able to analyze campaign performance down to the individual customer level

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/7000_notification_performance/7030_NTF_BAS_notification_campaign_detail_performance.sql

Build the [drp.notification_product_purchase_view_through] table to understand the performance of campaigns based on a view through perspective - i.e. the impact of email / SMS performance if the customer interacted with the campaign but did not purchase directly from the campaign

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/pro/7000_notification_performance/7040_NTF_BAS_notification_campaign_purchase_view_through.sql

Build the [drp.notification_file_size_monthly] table to track the monthly file size

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/7000_notification_performance/7050_NTF_BAS_notification_file_size_monthly.sql

Build the [drp.notifications_customer_profile] table to understand customer level interactions with Email / SMS

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/7000_notification_performance/7060_NTF_BAS_customer_notification_summary.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/7000_notification_performance/7070_NTF_BAS_notifications_customer_profile.sql