version: 2

models:
  - name: obt_orders
    description: >
      The obt_orders table is SourceMedium's out-of-the-box, "BI-ready" table that contains fully joined order facts and order dimensions
      for a shop. This "One Big Table" (OBT) is a denormalized table that is designed to be developer friendly.

    columns:
      - name: smcid
        description: >
          The SourceMedium ID of a store, which is derived from the store's myshopify.com domain.
        tests:
          - not_null

      - name: sm_order_key
        description: >
          The unique order key created by SourceMedium that can be used to join order dimensions to related tables.
        tests:
          - not_null
          - unique

      - name: sm_customer_key
        description: >
          The unique customer key created by SourceMedium that can be used to join customer dimensions to related tables.
        tests:
          - not_null

      - name: source_system
        description: >
          The e-commerce source system used to facilitate a customer's order.

      - name: order_id
        description: >
          The ID of the order.

      - name: customer_id
        description: >
          The ID of the customer.

      - name: order_name
        description: >
          A separate unique identifier for the order generated by the source system.

      - name: order_number
        description: >
          The order's position in the shop's count of orders.

      - name: order_checkout_id
        description: >
          The ID of the checkout that the order is associated with.

      - name: order_currency
        description: >
          The three-letter code (ISO 4217 format) for the currency used for the tender transaction.

      - name: order_created_at
        description: >
          The autogenerated date and time when the order was created.

      - name: order_created_at_local_datetime
        description: >
          The autogenerated date and time when the order was created, converted to the reporting timezone configured in SourceMedium.

      - name: order_updated_at
        description: >
          The autogenerated date and time when the order was last updated.

      - name: order_updated_at_local_datetime
        description: >
          The autogenerated date and time when the order was last updated, converted to the reporting timezone configured in SourceMedium.

      - name: order_processed_at
        description: >
          The autogenerated date and time when the order was processed.

      - name: order_processed_at_local_datetime
        description: >
          The autogenerated date and time when the order was processed, converted to the reporting timezone configured in SourceMedium.

      - name: order_cancelled_at
        description: >
          The autogenerated date and time when the order was cancelled.

      - name: order_cancelled_at_local_datetime
        description: >
          The autogenerated date and time when the order was cancelled, converted to the reporting timezone configured in SourceMedium.

      - name: order_cancellation_reason
        description: >
          The customer's reason for the order's cancellation.

      - name: sm_order_sales_channel
        description: >
          The name of the sales channel used to place an order, which is derived from the order source name, payment gateway name, and order tags.

      - name: order_source_name
        description: >
          Where the order originated as reported by the source system.

      - name: order_referring_site
        description: >
          The URL of the site that referred the customer to the shop.

      - name: sm_order_referrer_source
        description: >
          A cleaned version of the website where the customer clicked a link to the shop.

      - name: sm_order_landing_page
        description: >
          The URL for the page where the buyer landed when they entered the shop.

      - name: sm_order_referrer_domain
        description: >
          The domain of the website where the customer clicked a link to the shop.

      - name: order_index
        description: >
          An ordered index that can be used to identify the sequential position of an order relative to a customer's order history.

      - name: order_index_reversed
        description: >
          An ordered index that can be used to identify the sequential position of an order relative to a customer's order history, in reverse order.

      - name: order_tags_csv
        description: >
          A list of tags that the shop owner has attached to the order.

      - name: order_tags_array
        description: >
          A list of tags that the shop owner has attached to the order in an array format.

      - name: order_sequence
        description: >
          Whether an order is the first order or a repeat order.

      - name: order_session_user_agent
        description: >
          The user agent of the device used by the customer to place the order.

      - name: customer_device_type
        description: >
          The type of device used by the customer to place the order, which is derived from the user agent.

      - name: order_session_browser_type
        description: >
          The type of browser used by the customer to place the order, which is derived from the user agent.

      - name: order_payment_status
        description: >
          The financial status of an order, which indicates whether the order has been paid.

      - name: primary_order_payment_gateway
        description: >
          The technology or service that securely transmitted payment information between the customer, the business, and the payment processor.

      - name: order_shipping_country_code
        description: >
          The two-letter code (ISO 3166-1 format) for the country of the shipping address.

      - name: order_shipping_country
        description: >
          The country of the shipping address.

      - name: order_shipping_city
        description: >
          The city, town, or village of the shipping address.

      - name: order_shipping_zip
        description: >
          The postal code of the shipping address.

      - name: order_shipping_state
        description: >
          The state of the shipping address.

      - name: is_price_tax_inclusive
        description: >
          Whether taxes are included in the order subtotal.

      - name: sm_default_channel
        description: >
          The sales channel associated with an order, which is derived from the order source name and order tags.

      - name: sm_channel
        description: >
          The sales channel associated with an order, which is derived from the order source name and order tags and factors in manual overrides as defined by the SourceMedium channel mapping configuration sheet.

      - name: sm_sub_channel
        description: >
          The sales sub-channel associated with an order, which is derived from the order source name and order tags and factors in manual overrides as defined by the SourceMedium channel mapping configuration sheet.

      - name: sm_utm_source
        description: >
          The last touch utm_source value associated with an order, which is derived from Shopify notes data, Shopify customer visits data, and GA or GA4 events.

      - name: sm_utm_medium
        description: >
          The last touch utm_medium value associated with an order, which is derived from Shopify notes data, Shopify customer visits data, and GA or GA4 events.

      - name: sm_utm_campaign
        description: >
          The last touch utm_campaign value associated with an order, which is derived from Shopify notes data, Shopify customer visits data, and GA or GA4 events.

      - name: sm_utm_content
        description: >
          The last touch utm_content value associated with an order, which is derived from Shopify notes data, Shopify customer visits data, and GA or GA4 events.

      - name: sm_utm_term
        description: >
          The last touch utm_term value associated with an order, which is derived from Shopify notes data, Shopify customer visits data, and GA or GA4 events.

      - name: sm_utm_source_medium
        description: >
          A concatenation of source and medium.

      - name: sm_order_type
        description: >
          The order classification, such as a subscription or one-time order, which is derived from order attributes, order tags, or subscription platform data, if a subscription platform has been integrated.

      - name: subscription_order_sequence
        description: >
          Whether a subscription order is the first subscription order or a repeat subscription order based on the subscription order index or order tag indicators when an index is not available.

      - name: customer_email_hashed
        description: >
          A hashed version of the customer's email address.

      - name: gross_order_revenue
        description: >
          The gross revenue for an order, based on an order's lines. Gross revenue is calculated by multiplying the price of an order's lines by the quantity purchased. Gross revenue excludes revenue from gift card purchases.

      - name: order_discounts
        description: >
          The total amount of discounts applied to an order.

      - name: order_refunds
        description: >
          The total amount of refunds applied to an order.

      - name: order_net_revenue
        description: >
          The gross order revenue minus order discounts and refunds.

      - name: order_quantity
        description: >
          The quantity of order lines that were originally purchased in an order.

      - name: order_refund_quantity
        description: >
          The quantity of order lines originally purchased in an order that were refunded.

      - name: order_net_quantity
        description: >
          The quantity of items that were originally purchased in an order minus the quantity of items refunded.

      - name: gross_order_taxes
        description: >
          The amount of taxes associated with an order's lines, after discounts and before returns. This does not include shipping taxes.

      - name: order_tax_refunds
        description: >
          The amount of tax refunds applied to an order. This does not include shipping tax refunds.

      - name: order_net_taxes
        description: >
          The order tax minus order tax refunds. This does not include shipping taxes.

      - name: gross_order_shipping
        description: >
          The amount the customer paid in shipping for an order. This does not include shipping tax.

      - name: order_shipping_discounts
        description: >
          The amount of discounts applied to shipping.

      - name: order_shipping_refunds
        description: >
          The amount of shipping refunds applied to an order.

      - name: order_net_shipping
        description: >
          The gross shipping revenue for an order minus shipping discounts and shipping refunds.

      - name: gross_order_shipping_taxes
        description: >
          The amount of taxes associated with shipping, after discounts and before returns.

      - name: order_shipping_tax_refunds
        description: >
          The amount of shipping tax refunds applied to an order.

      - name: order_net_shipping_taxes
        description: >
          The order shipping tax minus shipping tax refunds.

      - name: order_total_taxes
        description: >
          The amount of order and shipping taxes minus order and shipping tax refunds.

      - name: order_total_discounts
        description: >
          The amount of order and shipping discounts for an order.

      - name: order_total_refunds
        description: >
          The amount of order and shipping refunds.

      - name: order_net_revenue_before_refunds
        description: >
          The gross order revenue minus order discounts.

      - name: order_total_revenue
        description: >
          Total order revenue after factoring in shipping revenue, taxes collected, discounts, and refunds.

      - name: gross_order_duties
        description: >
          The amount the customer paid in duties for an order.

      - name: order_duty_refunds
        description: >
          The amount of duty refunds applied to an order.

      - name: order_net_duties
        description: >
          The gross order duties minus duty refunds.

      - name: shipping_cost
        description: >
          The blended cost per order to ship products to customers, considering that shipping costs are variable order costs included in cost of goods sold (COGS). This data is set in the Financial Cost - Shipping tab of the SourceMedium financial cost configuration sheet.

      - name: cost_of_returns
        description: >
          The blended cost per order to handle returned products from a customer, considering that the cost of returns is a variable order cost included in cost of goods sold (COGS). This data is entered in the Financial Cost - Shipping tab of the SourceMedium financial cost configuration sheet.

      - name: fulfillment_cost
        description: >
          The blended cost to fulfill (pick and pack) orders for customers, considering that fulfillment cost is a variable order cost included in cost of goods sold (COGS). This data is recorded in the Financial Cost - Fulfillment tab of the financial cost configuration sheet.

      - name: merchant_processing_fees
        description: >
          The payment processing fees for an order. This data is entered in the Financial Cost - Merchant Processing Fees tab of the SourceMedium financial cost configuration sheet.

      - name: sm_zero_party_attribution_source
        description: >
          The attributable source of an order based on order tags associated with a post-purchase survey service provider.

      - name: discount_codes_csv
        description: >
          A list of discount codes applied to an order.

      - name: is_order_sm_valid
        description: >
          Whether an order is not voided, cancelled, uncollectible, draft, or refunded.

      - name: valid_order_index
        description: >
          An ordered index that can be used to identify the sequential position of a valid order relative to a customer's valid order history.

      - name: valid_order_index_reversed
        description: >
          An ordered index that can be used to identify the sequential position of a valid order relative to a customer's valid order history, in reverse order.

      - name: product_cost
        description: >
          The landed cost of an order as defined by the SourceMedium financial cost configuration sheet (or by costs input directly into Shopify) multiplied by the quantity purchased.

      - name: gross_profit
        description: >
          Net order revenue and shipping revenue minus order product cost, order shipping cost, order fulfillment cost, and merchant processing fees.

      - name: product_gross_profit
        description: >
          Net order revenue minus product cost.

      - name: subscription_order_index
        description: >
          An ordered index that can be used to identify the sequential position of a subscription order relative to a customer's subscription order history.

      - name: earliest_refund_date
        description: >
          The date of the first refund associated with an order.

      - name: latest_refund_date
        description: >
          The earliest date when a refund was processed for an order.

      - name: skus_csv
        description: >
          A list of SKUs included in an order.

      - name: product_titles_csv
        description: >
          A list of product titles included in an order.

      - name: product_variant_titles_csv
        description: >
          A list of product variant titles included in an order.

      - name: order_cart_quantity
        description: >
          The quantity of items that were originally purchased in an order.

      - name: sm_utm_source_grouped
        description: >
          The last touch order source mapped to a cleaned version of the source platform (e.g. Meta, TikTok, Klaviyo).

      - name: sm_zero_party_attribution_source_grouped
        description: >
          The last touch order source based on zero party attribution mapped a cleaned version of the source platform (e.g., Meta, TikTok, Klaviyo).

      - name: sm_utm_medium_grouped
        description: >
          The last touch order medium mapped to a cleaned version of the medium platform (e.g., Social, Email, Search).

      - name: sm_zero_party_attribution_medium_grouped
        description: >
          The last touch order medium based on zero party attribution mapped to a cleaned version of the medium platform (e.g., Social, Email, Search).

      - name: subscription_order_gross_revenue
        description: >
          The gross revenue for a subscription order, based on an order's lines. Gross revenue is calculated by multiplying the price of an order's lines by the quantity purchased. Gross revenue excludes revenue from gift card purchases.

      - name: sub_order_net_revenue
        description: >
          The gross order revenue minus discounts and refunds for subscription orders.

      - name: earliest_subscription_order_net_revenue
        description: >
          The gross order revenue minus discounts and refunds for the first subscription order.

      - name: recurring_sub_order_net_revenue
        description: >
          The gross order revenue minus discounts and refunds for recurring subscription orders.

      - name: is_subscription_order
        description: >
          Whether the order is a subscription order.

      - name: is_first_sub_order
        description: >
          Whether a subscription order is the first subscription order based on the subscription order index or order tag indicators when an index is not available.

      - name: is_order_recurring_subscription
        description: >
          Whether a subscription order is a repeat subscription order based on the subscription order index or order tag indicators when an index is not available.

      - name: is_latest_order
        description: >
          Whether an order has an order_index_reversed that equals 1.

      - name: earliest_refund_date
        description: >
          The earliest date when a refund was processed for an order.

      - name: latest_refund_date
        description: >
          The most recent date when a refund was processed for an order.

      - name: order_to_refund_days_earliest
        description: >
          The number of days between the process date and the first refund date for an order.

      - name: order_to_refund_days_latest
        description: >
          The number of days between the process date and the most recent refund date for an order.

      - name: order_to_refund_weeks_earliest
        description: >
          The number of weeks between the process date and the first refund date for an order.

      - name: order_to_refund_weeks_latest
        description: >
          The number of weeks between the process date and the most recent refund date for an order.

      - name: order_to_refund_months_earliest
        description: >
          The number of months between the process date and the first refund date for an order.

      - name: order_to_refund_months_latest
        description: >
          The number of months between the process date and the most recent refund date for an order.

      - name: order_latency_days
        description: >
          The number of days between the order_created_at and the order_processed_at for an order.

      - name: order_latency_days_by_type
        description: >
          The number of days between the order_created_at and the order_processed_at for an order, grouped by order type.

      - name: valid_order_latency_days
        description: >
          The number of days between the process date of a valid order and the process date of the most recent preceding valid order for a customer.

      - name: valid_order_latency_days_by_type
        description: >
          The number of days between the process date of a valid order and the process date of the most recent preceding valid order of the same type for a customer.

      - name: valid_order_sequence
        description: >
          Whether a valid order is the first valid order or a repeat valid order.