rpt_customers_first_and_last_order_summary_v1 - SourceMedium

One row per customer, with lifetime totals and the attribution and contents of their first and most recent orders. Built from your sm_transformed_v2 tables.

Example query

SELECT
  first_order_sm_channel,
  COUNT(*) AS customers,
  AVG(customer_total_orders) AS avg_orders,
  AVG(customer_total_net_revenue) AS avg_net_revenue
FROM `your_project.sm_views.rpt_customers_first_and_last_order_summary_v1`
WHERE DATE(first_order_date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
GROUP BY 1
ORDER BY customers DESC

Columns

Column	Type	Description
`sm_store_id`	STRING	SourceMedium store identifier for the customer record.
`customer_address_city`	STRING	City from the customer address.
`customer_address_country`	STRING	Country from the customer default or most relevant address.
`customer_address_province`	STRING	Province, state, or region from the customer address.
`customer_address_zip_code`	STRING	Postal or ZIP code from the customer address.
`customer_created_at`	TIMESTAMP	Timestamp when the customer profile was created in the source system.
`customer_email`	STRING	Customer email address when available in the transformed customer model.
`customer_email_hashed`	STRING	Hashed customer email value used for privacy-preserving joins and matching.
`customer_first_order_id`	STRING	Order identifier for the customer first order.
`customer_id`	STRING	Source customer identifier from the transformed customer model.
`customer_last_order_id`	STRING	Order identifier for the customer most recent order.
`customer_tags_csv`	STRING	Comma-separated customer tags from the transformed customer profile.
`customer_total_discounts`	NUMERIC	Sum of order discount amounts across all orders for the customer.
`customer_total_gross_revenue`	NUMERIC	Sum of gross order revenue across all orders for the customer.
`customer_total_net_revenue`	NUMERIC	Sum of net order revenue across all orders for the customer.
`customer_total_orders`	INT64	Total distinct orders associated with the customer.
`customer_total_refunds`	NUMERIC	Sum of order refund amounts across all orders for the customer.
`customer_total_revenue`	NUMERIC	Sum of total order revenue across all orders for the customer.
`customer_updated_at`	TIMESTAMP	Timestamp when the customer profile was last updated in the source system.
`first_order_date`	TIMESTAMP	Processed timestamp for the customer first order.
`first_order_discount_codes_csv`	STRING	Comma-separated discount codes applied to the customer first order.
`first_order_gross_revenue`	NUMERIC	Gross revenue amount for the customer first order.
`first_order_net_revenue`	NUMERIC	Net revenue amount for the customer first order.
`first_order_payment_status`	STRING	Payment status of the customer first order.
`first_order_product_titles`	STRING	Comma-separated product titles included in the customer first order.
`first_order_sales_channel_name`	STRING	Sales channel name associated with the customer first order.
`first_order_skus_csv`	STRING	Comma-separated SKUs included in the customer first order.
`first_order_sm_channel`	STRING	SourceMedium channel assigned to the customer first order.
`first_order_sm_sub_channel`	STRING	SourceMedium sub-channel assigned to the customer first order.
`first_order_sm_utm_campaign`	STRING	SourceMedium UTM campaign attribution for the customer first order.
`first_order_sm_utm_source_medium`	STRING	SourceMedium UTM source and medium attribution for the customer first order.
`first_order_sm_zero_party_attribution`	STRING	Zero-party attribution source captured for the customer first order.
`first_order_total_revenue`	NUMERIC	Total revenue amount for the customer first order.
`first_order_type`	STRING	SourceMedium order type assigned to the customer first order.
`last_order_date`	TIMESTAMP	Processed timestamp for the customer most recent order.
`last_order_discount_codes_csv`	STRING	Comma-separated discount codes applied to the customer most recent order.
`last_order_gross_revenue`	NUMERIC	Gross revenue amount for the customer most recent order.
`last_order_net_revenue`	NUMERIC	Net revenue amount for the customer most recent order.
`last_order_payment_status`	STRING	Payment status of the customer most recent order.
`last_order_product_titles`	STRING	Comma-separated product titles included in the customer most recent order.
`last_order_sales_channel_name`	STRING	Sales channel name associated with the customer most recent order.
`last_order_skus_csv`	STRING	Comma-separated SKUs included in the customer most recent order.
`last_order_sm_channel`	STRING	SourceMedium channel assigned to the customer most recent order.
`last_order_sm_sub_channel`	STRING	SourceMedium sub-channel assigned to the customer most recent order.
`last_order_sm_utm_campaign`	STRING	SourceMedium UTM campaign attribution for the customer most recent order.
`last_order_sm_utm_source_medium`	STRING	SourceMedium UTM source and medium attribution for the customer most recent order.
`last_order_sm_zero_party_attribution`	STRING	Zero-party attribution source captured for the customer most recent order.
`last_order_total_revenue`	NUMERIC	Total revenue amount for the customer most recent order.
`last_order_type`	STRING	SourceMedium order type assigned to the customer most recent order.
`subscriber_created_at`	TIMESTAMP	Timestamp when the associated subscriber record was created, when available.
`subscriber_status`	STRING	Current subscription or marketing subscriber status associated with the customer.