Transform Code Introduction

This page provides an overview of the different blocks of SQL Transformation Code that exist in the Daasity repository and links to pages that outline how to setup the configuration

Overview

You can use Daasity to run transform code on data in your data warehouse. You can run custom transform code created by you, or you can run a variety of pre-built SQL Code blocks maintained by Daasity. These SQL code blocks enable you to accelerate the development of your analytics data stack by including this out-of-box code as part of your workflow.

These SQL Code blocks are built for one of two purposes:

1. Transform your raw source data into one of the Daasity Unified Models that are outlined in our Unified Data Schemas.

2. Transform data from one of the Unified schemas into one of our data marts that are outlined in our Data Mart Models

You will be unable to implement the Daasity SQL Code blocks if you do not have access to the Code Repository feature

How to Run SQL Code Blocks

Our template SQL code blocks are made up of YML code that should be added to a script manifest file in your Custom Code Repository. The associated transform code will run when the script manifest file is used in a workflow.

SQL Code Blocks

SQL Code blocks exist for the following Data Marts:

Unified Schemas [UxS]

• Unified Order Schema: this block has code that must be run regardless of the Commerce integration and links to all code blocks for each of the Commerce integrations

• Unified Marketing Schema: this block has code that must be run to initialize and finalize the marketing tables as well as links to all code blocks for each of the Marketing integrations

• Unified Notification Schema: this block has code that must be run to initialize and finalize the email/SMS tables as well as links to all code blocks for each of the Email/SMS integrations

• Unified Traffic Schema: this block has code that must be run to initialize and finalize the traffic, product page, shopping stage and transaction tables that unify data across GA3 (Universal Analytics), GA4 and Amazon

Data Reporting Platform [drp]

• Initialization: this block creates all the initial tables that are needed to execute the standard Daasity transformation

• Householding: this block looks across the Unified Order Schema in an attempt to create a unique customer identifier by running householding logic across the different commerce platforms that connected to Daasity

• Order Status: this block of code determines the statuses of orders which drives revenue calculations and assigns orders to business channels

• Order Channel and Marketing: this block of code combines order and traffic data to determine the attribution of an order. This code is for GA3 only and is being replaced by the Attribution Data-Mart

• Daily Plan: this block of code creates tables for Revenue and Marketing plans based on data manually entered into the Daasity App via our out-of-box Google Sheets

• Order and Order Line Revenue: this block of code creates tables to analyze profitability at the order-item level allocating COGS, marketing, shipping and fulfillment to each item in an order

• Plan to Actual: this block of code combines data from the Daily Plan code block along with actual performance to provide you with an easy way to report on how well you performed to plan

• Customer: this block of code creates profiles and segments for customers as well as track changes in customer segments over time and the relation of those segments to retention performance

• Subscription: this block of code creates tables for subscribers, subscriptions and churn across time to help analyze the performance of your subscription businesses

• Reviews: this block of code aggregates review data to better understand the impact of reviews on product performance

• Life-Time Value: this block of code creates tables for life-time value analysis and LTV to CAC analysis at the customer level

• Returns: this block of code creates tables to help understand the relationship between returns, exchanges and the original order to better understand the drivers for returns and exchanges

• Product and Inventory: this block of code builds SKU attributes to enable better product analysis and creates a historical snapshot of inventory to ensure that Merchandising and Planning analytics can be performed

• Email & SMS: this block of code builds tables to better track the performance of email and SMS strategies by building customer and campaign level performance metrics and email / SMS list size changes over time

Data-Marts [dm]

• Channel Attribution Data Mart: this block of code uses the Attribution Settings UI to create dim and fct tables to attribute orders to a channel for the different attribution methods

• Marketing Data Mart: this block of code generates the dim and fct tables to enable comparison of marketing performance based on the different attribution models selected in the Attribution Settings UI