2000_SHP_BAS_shopify_sales_report Transformation

Target Database Table

drp.shopify_sales_report.

Script Description and Logic

Pulls fields from shopify.orders, shopify.order_shipping_lines, shopify.locations, shopify.order_discount_codes and shopify.order_line_items at the order level to replicate the reports available in Shopify.

Order lines from Shopify are included in the primary source tables and are reported by unique order id, ,transaction type, shop id and integration id. The code looks back to pull orders created or modified in the last 7 days.

A lookup of shopify.order_line_items is performed to generate a list of quantity in orders where a gift card was used, this table is stored as giftcards_v_total_items. Next shopify.refunds is joined to shopify_refund_line_items, shopify_orders, shopify_order_line_items, currency_conversion derived table, shopify.locations, giftcards_v_total_items where the last date updated at was less than 7 days ago and where the transaction type is a refund at the item level.

This table of item refunds is stored as drp_staging.shopify_sales_report. The logic for transaction item refunds is replicated with updated filters to generate a table for order refunds, order purchases and item purchases. These tables are then recorded into drp_staging.shopify_sales_report. The records in staging are compared to those in the final table.

Any transactions that match are removed from the final table and updated by way of insertion from staging. The records that appear in staging, but not in the final table are inserted into drp.shopify_sales_report.

Source Tables Used In Script

Coming soon.

SQL Flow

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

2000_SHP_BAS_shopify_sales_report TransformationCalculated and Derived Fields

Coming soon.

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