obt_customers

Customer analytics table for acquisition, subscription status, and cohort analysis.

Grain: One row per sm_customer_key.
Date field: customer_created_at.
Filters to start with: source_system for platform-specific analysis.
Joins: obt_orders via sm_customer_key (1:many); dim_customers via sm_customer_key (1:1).

Columns

Column	Type	Description
`sm_customer_key`	STRING	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.
`sm_store_id`	STRING	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.
`customer_address_city`	STRING	City from customer’s primary address (free-form). Coverage varies by source_system. Amazon coverage is high in practice (see the per-workspace floor on the test below), but individual rows can be NULL or obfuscated.
`customer_address_country`	STRING	Country from customer’s primary address (platform-reported names). Coverage varies by source_system. Amazon coverage is high in practice (see the per-workspace floor on the test below), but individual rows can be NULL or obfuscated.
`customer_address_province`	STRING	The province, state, or district code of the customer’s location.
`customer_address_zip_code`	STRING	The postal code of the customer’s location.
`customer_created_at`	TIMESTAMP	UTC timestamp when the customer was created.
`customer_email`	STRING	Customer email address (PII); see customer_email_hashed for privacy‑safe matching.
`customer_email_hashed`	STRING	SHA-256 hash of customer email address. Used for privacy-safe customer matching and analytics across systems without exposing PII.
`customer_first_name`	STRING	Customer’s first name as provided during account creation or checkout. May be null for guest checkouts or incomplete registrations.
`customer_first_order_id`	STRING	The ID of the customer’s first order.
`customer_id`	STRING	The ID of the customer who placed the order.
`customer_last_name`	STRING	Customer’s last name as provided during account creation or checkout. May be null for guest checkouts or incomplete registrations.
`customer_last_order_id`	STRING	The ID of the customer’s last order.
`customer_phone_number`	STRING	Customer’s phone number in platform-provided format (varies by source_system). May be NULL if not collected; formatting and country codes not standardized.
`customer_street_address`	STRING	The customer’s street address.
`customer_tags_csv`	STRING	Comma-separated list of all tags associated with a customer. Use for simple filtering; beware that individual tag values may contain commas.
`customer_updated_at`	TIMESTAMP	UTC timestamp when the customer was last modified.
`has_customer_consented_to_marketing`	BOOL	Whether the customer has consented to receive marketing communications.
`is_customer_email_verified`	BOOL	Whether the customer’s email has been verified.
`source_system`	STRING	Originating platform for the record (e.g., Shopify, Amazon, TikTok Shop, Chargebee). Used for platform‑specific behavior and coverage.
`subscriber_created_at`	TIMESTAMP	The autogenerated date and time when the subscriber was created.
`subscriber_id`	STRING	Unique subscription platform identifier when subscription integration configured (e.g., ReCharge, Skio, Loop, Stay AI). NULL for non-subscribers; reflects current/most relevant subscription linkage at snapshot time.
`subscriber_status`	STRING	Subscription standing from the `subscribers` union. Recharge maps Recharge’s own `has_payment_method_in_dunning`, gated on having an active subscription. Chargebee adds Trial (any in-trial subscription, and only when none are active). Stay AI is Active/Inactive only: its subscription-level dunning flag is not rolled up here. NULL means the customer is not a subscriber, not that status is unknown (most rows). `subscriber_status != 'Inactive'` drops every non-subscriber. Filter for the value you want. Legacy `subscription_master` and the executive summary use this same Recharge flag. The v2 subscription reports still infer dunning from recent charge failures and therefore count more people. Use this column for current standing; use the v2 reports for the recency time series.
`subscription_source_system`	STRING	The e-commerce source_system used to facilitate a customer’s subscription.

Get into BigQuery

Activate

SQL Query Library

Common analyses (SQL)

Tables

Schema Standards

Columns

​Columns

Columns