version: 2 models: - name: SHOPIFY_EMAIL_PRODUCTS_DBT description: > Aggregated Shopify product sales attributed to email campaigns and automated emails. Grain: one row per (EMAIL_ID, PRODUCT_TITLE, PRODUCT_TYPE, CURRENCY). Net sales = PRICE * QUANTITY - TOTAL_DISCOUNT; refunds are not accounted for. columns: - name: EMAIL_ID description: UUID of the email (campaign or automated). tests: - not_null - name: PRODUCT_TITLE description: Product title from the order line. - name: PRODUCT_TYPE description: Product type from the product table. NULL when not set. - name: CURRENCY description: ISO currency code for the attributed orders. tests: - not_null - name: TOTAL_QUANTITY_SOLD description: Total quantity of this product sold across all attributed orders. tests: - not_null - name: TOTAL_NET_SALES description: > Sum of NET_LINE_AMOUNT in the original order currency. All rows in the group share the same currency. tests: - not_null - name: TOTAL_NET_SALES_USD description: > Sum of net sales converted to USD. Uses DIM_CURRENCY conversion rate; falls back to 1.0 when rate is missing. tests: - not_null - name: UPDATED_AT description: Timestamp of the most recent Fivetran sync contributing to this row (UTC). tests: - not_null tests: - dbt_utils.unique_combination_of_columns: arguments: combination_of_columns: - EMAIL_ID - PRODUCT_TITLE - PRODUCT_TYPE - CURRENCY - name: SHOPIFY_EMAIL_ORDERS_DBT description: > Aggregated Shopify order revenue attributed to email campaigns and automated emails. Grain: one row per (EMAIL_ID, SHOP_ID, CURRENCY, COUNTRY_CODE). One email can produce multiple rows when attributed orders span different shops or countries. Only emails with at least one matching paid Shopify order are included. Amounts are stored in both the original currency and USD. columns: - name: EMAIL_ID description: UUID of the email (campaign or automated). tests: - not_null - name: SHOP_ID description: Shopify shop ID from STG_SHOPIFY__SHOP_DBT. tests: - not_null - name: SHOP_NAME description: Display name of the Shopify store. tests: - not_null - name: CURRENCY description: ISO currency code of the attributed orders. tests: - not_null - name: COUNTRY_CODE description: Shipping address country code of the attributed orders. - name: TOTAL_ORDERS description: Number of distinct Shopify orders attributed to this email, shop, currency, and country. tests: - not_null - name: ITEMS_SOLD description: Total number of sold items attributed to this email, shop, currency, and country. tests: - not_null - dbt_utils.expression_is_true: arguments: expression: '>= 0' - name: TOTAL_NET_SALES description: > Sum of net sales after refunds in the original currency. tests: - not_null - name: TOTAL_NET_SALES_USD description: > TOTAL_NET_SALES converted to USD using DIM_CURRENCY.CONVERSION_RATE. Falls back to 1.0 when no rate is found. tests: - not_null - name: TOTAL_REFUNDS description: Sum of refunded amounts in the original currency. tests: - not_null - name: TOTAL_REFUNDS_USD description: > TOTAL_REFUNDS converted to USD using DIM_CURRENCY.CONVERSION_RATE. Falls back to 1.0 when no rate is found. tests: - not_null - name: TOTAL_DISCOUNTS description: Sum of discounts applied across all attributed orders in the original currency. tests: - not_null - name: TOTAL_DISCOUNTS_USD description: > TOTAL_DISCOUNTS converted to USD using DIM_CURRENCY.CONVERSION_RATE. Falls back to 1.0 when no rate is found. tests: - not_null - name: TOTAL_TAXES description: Sum of taxes across all attributed orders in the original currency. tests: - not_null - name: TOTAL_TAXES_USD description: > TOTAL_TAXES converted to USD using DIM_CURRENCY.CONVERSION_RATE. Falls back to 1.0 when no rate is found. tests: - not_null - name: TOTAL_DELIVERY description: Sum of shipping / delivery costs across all attributed orders in the original currency. tests: - not_null - name: TOTAL_DELIVERY_USD description: > TOTAL_DELIVERY converted to USD using DIM_CURRENCY.CONVERSION_RATE. Falls back to 1.0 when no rate is found. tests: - not_null - name: TOTAL_SALES description: Sum of TOTAL_PRICE (order grand total) in the original currency. tests: - not_null - name: TOTAL_SALES_USD description: > Sum of order totals converted to USD. Uses DIM_CURRENCY conversion rate; falls back to 1.0 when rate is missing. tests: - not_null - name: FIRST_TIME_BUYER_RATE description: > Share of orders placed by a customer email for the first time (globally across all shops). Calculated as first-time orders / total distinct orders. NULL when there are no orders. - name: UPDATED_AT description: Timestamp of the most recent Fivetran sync contributing to this row (UTC). tests: - not_null tests: - dbt_utils.unique_combination_of_columns: arguments: combination_of_columns: - EMAIL_ID - SHOP_ID - CURRENCY - COUNTRY_CODE - name: SHOPIFY_PRODUCT_TYPES_DBT description: > Incremental lookup table of distinct Shopify product types with a chart-eligibility classification generated by Snowflake Cortex (mistral-large2). A product type is chart eligible if it represents a physical or digital music release (Vinyl, CD, Cassette, Digital Album, Download). Only product types not yet present in the table are classified on each run. columns: - name: PRODUCT_TYPE description: Distinct product type string from shopify_product. tests: - not_null - unique - name: CHART_ELIGIBLE description: > True if Cortex classifies the product type as a chart-eligible music release. False for merchandise, clothing, tickets, books, accessories, and collectibles. Falls back to false when Cortex returns an unparseable response. tests: - not_null