Source Database Table
drp.order_channel_influencer_discount_code
Script that Populates Database Table
3040_CHN_BAS_order_channel_influencer_discount_code.sql
Script Description and Logic
Pulls order-related fields and discount codes for all orders with influencer derived discount codes. All discount codes and influencers in the primary source table, bsd.influencers, are included and reported by unique order id and discount code combination. From influencers, a lookup is performed to pull a list of influencer discount codes and stored as influencer_codes. Next order_discount_codes is joined to influencer_codes to generate a list of orders that used discount codes derived from social media influencers tracked in the BSD and then sequenced in preparation for removal of duplicates. The resulting table is stored as rows_deduped. Rows_deduped is then inserted into drp_staging.order_channel_influencer_discount_code. All records are removed from the final table, drp.order_channel_influencer_discount_code, and updated by way of insertion from the query (upsert). An additional insert into drp_staging.order_channel_source is then performed for use in later processes.
Source Tables Used In Script
Schema | Table (or Derived Table) Name | Table Type | Purpose |
uos | order_discount_codes | database | Primary |
bsd | influencers | database | Lookup |
uos | orders | database | Lookup |
drp | integration_mapping | database | Ensure unique records |
drp_staging | order_channel_influencer_discount_code | database | Pre-insert |
N/A | influencer_codes | derived | Pulls a list of discount codes and social media influencers from BSD |
N/A | rows_deduped | derived | Generates a list of orders that used discount codes derived from social media influencers, removes duplicate records |
SQL Flow
Calculated and Derived Fields
Target Column | Target Column Data Type | Source schema.table | Source Column | Transformation/Logic |
order_channel_id | CHAR | uos.order_discount_codes | __shop_id, order_id | Combination of __shop_id, order_id and a text label of influencer discount code to identify records |
All content © Daasity 2021. Do not copy, share or distribute.