rpt_customers_first_and_last_order_summary_v1

SELECT *
FROM `your_project.sm_views.rpt_customers_first_and_last_order_summary_v1`
LIMIT 100;

version: 2

models:
  - name: rpt_customers_first_and_last_order_summary_v1
    description: >
      Customer-level first and last order summary generated for tenant reporting. Combines customer profile fields, lifetime order totals, and first/last order attribution and order attributes from transformed v2 models.
    columns:
      - name: sm_store_id
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          SourceMedium store identifier for the customer record.

      - name: customer_id
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Source customer identifier from the transformed customer model.

      - name: customer_email
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Customer email address when available in the transformed customer model.

      - name: customer_email_hashed
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Hashed customer email value used for privacy-preserving joins and matching.

      - name: customer_first_order_id
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Order identifier for the customer first order.

      - name: customer_last_order_id
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Order identifier for the customer most recent order.

      - name: customer_tags_csv
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Comma-separated customer tags from the transformed customer profile.

      - name: subscriber_status
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Current subscription or marketing subscriber status associated with the customer.

      - name: customer_created_at
        data_type: TIMESTAMP
        meta:
          is_nullable: true
        description: >
          Timestamp when the customer profile was created in the source system.

      - name: customer_updated_at
        data_type: TIMESTAMP
        meta:
          is_nullable: true
        description: >
          Timestamp when the customer profile was last updated in the source system.

      - name: subscriber_created_at
        data_type: TIMESTAMP
        meta:
          is_nullable: true
        description: >
          Timestamp when the associated subscriber record was created, when available.

      - name: customer_address_country
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Country from the customer default or most relevant address.

      - name: customer_address_province
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Province, state, or region from the customer address.

      - name: customer_address_city
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          City from the customer address.

      - name: customer_address_zip_code
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Postal or ZIP code from the customer address.

      - name: customer_total_orders
        data_type: INT64
        meta:
          is_nullable: true
        description: >
          Total distinct orders associated with the customer.

      - name: customer_total_gross_revenue
        data_type: NUMERIC
        meta:
          is_nullable: true
        description: >
          Sum of gross order revenue across all orders for the customer.

      - name: customer_total_net_revenue
        data_type: NUMERIC
        meta:
          is_nullable: true
        description: >
          Sum of net order revenue across all orders for the customer.

      - name: customer_total_revenue
        data_type: NUMERIC
        meta:
          is_nullable: true
        description: >
          Sum of total order revenue across all orders for the customer.

      - name: customer_total_discounts
        data_type: NUMERIC
        meta:
          is_nullable: true
        description: >
          Sum of order discount amounts across all orders for the customer.

      - name: customer_total_refunds
        data_type: NUMERIC
        meta:
          is_nullable: true
        description: >
          Sum of order refund amounts across all orders for the customer.

      - name: first_order_sm_channel
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          SourceMedium channel assigned to the customer first order.

      - name: first_order_date
        data_type: TIMESTAMP
        meta:
          is_nullable: true
        description: >
          Processed timestamp for the customer first order.

      - name: first_order_sm_utm_source_medium
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          SourceMedium UTM source and medium attribution for the customer first order.

      - name: first_order_sm_utm_campaign
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          SourceMedium UTM campaign attribution for the customer first order.

      - name: first_order_product_titles
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Comma-separated product titles included in the customer first order.

      - name: first_order_sm_zero_party_attribution
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Zero-party attribution source captured for the customer first order.

      - name: first_order_sm_sub_channel
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          SourceMedium sub-channel assigned to the customer first order.

      - name: first_order_skus_csv
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Comma-separated SKUs included in the customer first order.

      - name: first_order_discount_codes_csv
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Comma-separated discount codes applied to the customer first order.

      - name: first_order_payment_status
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Payment status of the customer first order.

      - name: first_order_gross_revenue
        data_type: NUMERIC
        meta:
          is_nullable: true
        description: >
          Gross revenue amount for the customer first order.

      - name: first_order_net_revenue
        data_type: NUMERIC
        meta:
          is_nullable: true
        description: >
          Net revenue amount for the customer first order.

      - name: first_order_total_revenue
        data_type: NUMERIC
        meta:
          is_nullable: true
        description: >
          Total revenue amount for the customer first order.

      - name: first_order_sales_channel_name
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Sales channel name associated with the customer first order.

      - name: first_order_type
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          SourceMedium order type assigned to the customer first order.

      - name: last_order_sm_channel
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          SourceMedium channel assigned to the customer most recent order.

      - name: last_order_date
        data_type: TIMESTAMP
        meta:
          is_nullable: true
        description: >
          Processed timestamp for the customer most recent order.

      - name: last_order_sm_utm_source_medium
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          SourceMedium UTM source and medium attribution for the customer most recent order.

      - name: last_order_sm_utm_campaign
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          SourceMedium UTM campaign attribution for the customer most recent order.

      - name: last_order_product_titles
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Comma-separated product titles included in the customer most recent order.

      - name: last_order_sm_zero_party_attribution
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Zero-party attribution source captured for the customer most recent order.

      - name: last_order_sm_sub_channel
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          SourceMedium sub-channel assigned to the customer most recent order.

      - name: last_order_skus_csv
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Comma-separated SKUs included in the customer most recent order.

      - name: last_order_discount_codes_csv
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Comma-separated discount codes applied to the customer most recent order.

      - name: last_order_payment_status
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Payment status of the customer most recent order.

      - name: last_order_gross_revenue
        data_type: NUMERIC
        meta:
          is_nullable: true
        description: >
          Gross revenue amount for the customer most recent order.

      - name: last_order_net_revenue
        data_type: NUMERIC
        meta:
          is_nullable: true
        description: >
          Net revenue amount for the customer most recent order.

      - name: last_order_total_revenue
        data_type: NUMERIC
        meta:
          is_nullable: true
        description: >
          Total revenue amount for the customer most recent order.

      - name: last_order_sales_channel_name
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          Sales channel name associated with the customer most recent order.

      - name: last_order_type
        data_type: STRING
        meta:
          is_nullable: true
        description: >
          SourceMedium order type assigned to the customer most recent order.