dim_orders

version: 2

models:
  - name: dim_orders
    description: >
      The dim_orders table is a dimensional table containing information about orders that have been placed by customers.
    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.

      - name: source_system
        description: >
          The e-commerce source system used to facilitate an order.

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

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

      - 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 modified.

      - name: order_updated_at_local_datetime
        description: >
          The autogenerated date and time when the order was last modified, 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.