3510_PFT_BAS_performance_trends Transformation

Target Database Table

drp.performance_trends.

Script Description and Logic

Combines traffic data from Google Analytics with order and order channel data using three different attribution models: first click, last click, and the customer's own custom attribution model. Results are then aggregated to create metrics on a daily store level for each of the three models. Metrics include traffic, revenue, orders, units, new customers, and total customers.

Source Tables Used In Script

Schema Table (or Derived Table) Name Table Type Purpose
drp order_business_unit database Pulls in store name and type
drp order_channel database Allows for splitting out orders by first click, last click, and attribution models
drp order_status database Allows for filtering for only valid orders
drp_staging ga_traffic_to_update database Lookup
drp_staging performance_trends database Pre-insert
drp_staging uos_orders_to_update database Lookup
ga base_traffic database Primary
N/A attribution_channel derived Pulls metrics using the customer's own attribution model
N/A fc_channel derived Pulling metrics using a first click attribution model
N/A lc_channel derived Pulling metrics using a last click attribution model
platform account_integrations database Primary
platform account_linked_integrations database Primary

SQL Flow

To View Larger Image, Right Click and Choose Open Image In New Tab.

3510_PFT_BAS_performance_trends Transformation

Calculated and Derived Fields

Target Column Target Column Data Type Source schema.table Source Column Transformation/Logic
ga_fc_channel VARCHAR ga.base_traffic channel CASE WHEN NVL(t.channel, '(unavailable)') = '(unavailable)' THEN 'Missing from GA' ELSE t.channel END
ga_lc_channel VARCHAR ga.base_traffic channel CASE WHEN NVL(t.channel, '(unavailable)') = '(unavailable)' THEN 'Missing from GA' ELSE t.channel END
attribution_channel VARCHAR ga.base_traffic channel CASE WHEN NVL(t.channel, '(unavailable)') = '(unavailable)' THEN 'Missing from GA' ELSE t.channel END
fc_sessions BIGINT ga.base_traffic sessions SUM(sessions)
lc_sessions BIGINT ga.base_traffic sessions SUM(sessions)
attribution_sessions BIGINT ga.base_traffic sessions SUM(sessions)
fc_sales DECIMAL(20,4) drp_staging.uos_orders_to_update total SUM(total)
lc_sales DECIMAL(20,4) drp_staging.uos_orders_to_update total SUM(total)
attribution_sales DECIMAL(20,4) drp_staging.uos_orders_to_update total SUM(total)
fc_orders BIGINT drp_staging.uos_orders_to_update order_id COUNT(DISTINCT os.order_id)
lc_orders BIGINT drp_staging.uos_orders_to_update order_id COUNT(DISTINCT os.order_id)
fc_orders BIGINT drp_staging.uos_orders_to_update order_id COUNT(DISTINCT os.order_id)
fc_non_subscription_orders BIGINT drp_staging.uos_orders_to_update order_id COUNT(DISTINCT CASE WHEN os.subscription_order_flag = FALSE THEN os.order_id ELSE NULL END)
lc_non_subscription_orders BIGINT drp_staging.uos_orders_to_update order_id COUNT(DISTINCT CASE WHEN os.subscription_order_flag = FALSE THEN os.order_id ELSE NULL END)
fc_non_subscription_orders BIGINT drp_staging.uos_orders_to_update order_id COUNT(DISTINCT CASE WHEN os.subscription_order_flag = FALSE THEN os.order_id ELSE NULL END)
fc_units BIGINT drp_staging.uos_orders_to_update total_feature_items SUM(total_feature_items)
lc_units BIGINT drp_staging.uos_orders_to_update total_feature_items SUM(total_feature_items)
fc_units BIGINT drp_staging.uos_orders_to_update total_feature_items SUM(total_feature_items)
fc_customers BIGINT drp.order_status unique_customer_id COUNT(DISTINCT os.unique_customer_id)
lc_customers BIGINT drp.order_status unique_customer_id COUNT(DISTINCT os.unique_customer_id)
fc_customers BIGINT drp.order_status unique_customer_id COUNT(DISTINCT os.unique_customer_id)
fc_new_customers BIGINT drp.order_status unique_customer_id COUNT(DISTINCT CASE WHEN os.first_order_flag = 1 THEN os.unique_customer_id ELSE NULL END)
lc_new_customers BIGINT drp.order_status unique_customer_id COUNT(DISTINCT CASE WHEN os.first_order_flag = 1 THEN os.unique_customer_id ELSE NULL END)
fc_new_customers BIGINT drp.order_status unique_customer_id COUNT(DISTINCT CASE WHEN os.first_order_flag = 1 THEN os.unique_customer_id ELSE NULL END)


All content © Daasity 2021. Do not copy, share or distribute.