obt_customers

version: 2

models:
  - name: obt_customers
    description: >
      Customer analytics table for acquisition, subscription status, and cohort analysis. Grain: One row per sm_customer_key. Date field: customer_created_at. Critical filters: source_system for platform-specific analysis. Key joins: obt_orders via sm_customer_key (1:many); dim_customers via sm_customer_key (1:1).
    columns:
      - name: _synced_at
        description: >
          UTC timestamp when SourceMedium last synced the row. Freshness indicator only; not for time-series analysis.

      - name: customer_address_city
        description: >
          City from customer's primary address (free-form). Coverage varies by source_system; Amazon often has NULL/obfuscated data.

      - name: customer_address_country
        description: >
          Country from customer's primary address (platform-reported names). Coverage varies by source_system; Amazon often has NULL/obfuscated data.

      - name: customer_address_province
        description: >
          The province, state, or district code of the customer's location.

      - name: customer_address_zip_code
        description: >
          The postal code of the customer's location.

      - name: customer_created_at
        description: >
          UTC timestamp when the customer was created.

      - name: customer_email
        description: >
          Customer email address (PII); see customer_email_hashed for privacy‑safe matching.

      - name: customer_email_hashed
        description: >
          SHA-256 hash of customer email address. Used for privacy-safe customer matching and analytics across systems without exposing PII.

      - name: customer_first_name
        description: >
          Customer's first name as provided during account creation or checkout. May be null for guest checkouts or incomplete registrations.

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

      - name: customer_last_name
        description: >
          Customer's last name as provided during account creation or checkout. May be null for guest checkouts or incomplete registrations.

      - name: customer_phone_number
        description: >
          Customer's phone number in platform-provided format (varies by source_system). May be NULL if not collected; formatting and country codes not standardized.

      - name: customer_street_address
        description: >
          The customer's street address.

      - name: customer_tags_array
        description: >
          Array of all tags associated with a customer. Preferred for robust filtering (use UNNEST) and programmatic tag operations.

      - name: customer_tags_csv
        description: >
          Comma-separated list of all tags associated with a customer. Use for simple filtering; beware that individual tag values may contain commas.

      - name: customer_updated_at
        description: >
          UTC timestamp when the customer was last modified.

      - name: first_order_id
        description: >
          The ID of the customer's first order.

      - name: has_customer_consented_to_marketing
        description: >
          Whether the customer has consented to receive marketing communications.

      - name: is_customer_email_verified
        description: >
          Whether the customer's email has been verified.

      - name: last_order_id
        description: >
          The ID of the customer's last order.

      - name: sm_customer_key
        description: >
          Stable SourceMedium customer key. Unique per customer. Key joins: `dim_customers` (1:1); `dim_orders`/`obt_orders` (1:many). Platform caveat: TikTok Shop coverage may be limited.

      - name: sm_store_id
        description: >
          SourceMedium's unique store identifier. For Shopify stores, derived from the myshopify.com domain; for other platforms (Amazon, TikTok Shop, Walmart.com), uses platform-specific identifiers.

      - name: source_system
        description: >
          Originating platform for the record (e.g., Shopify, Amazon, TikTok Shop, Chargebee). Used for platform‑specific behavior and coverage.

      - name: subscriber_created_at
        description: >
          The autogenerated date and time when the subscriber was created.

      - name: subscriber_id
        description: >
          Unique subscription platform identifier when subscription integration configured (e.g., ReCharge, Skio, Loop, Retextion). NULL for non-subscribers; reflects current/most relevant subscription linkage at snapshot time.

      - name: subscriber_status
        description: >
          The status of the subscriber.

      - name: subscription_source_system
        description: >
          The e-commerce source_system used to facilitate a customer's subscription.