rpt_cohort_ltv_by_first_valid_purchase_attribute_no_product_filters - SourceMedium

Cohort LTV analysis by first valid purchase attributes (channel, campaign, source) with no product filters.

Grain: One row per cohort_filter_name_filter_value_month_id.
Date field: cohort_month.
Filters to start with: acquisition_order_filter_dimension for cohort grouping; cohort_month for time selection.
Joins: none; drill down to obt_orders using (cohort_month, attribute filters).

Query examples

LTV & Retention

Columns

Column	Type	Description
`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.
`acquisition_order_filter_dimension`	STRING	Dimension name used to group cohorts by first valid purchase attribute (e.g., sm_channel).
`acquisition_order_filter_dimension_value`	STRING	Dimension value for the cohort grouping.
`cohort_filter_name_filter_value_id`	STRING	Unique identifier for the cohort grouping without time dimension (name/value composite).
`cohort_filter_name_filter_value_month_id`	STRING	Unique identifier for the cohort row (name/value/month composite).
`cohort_month`	DATE	Cohort acquisition month.
`cohort_month_gross_profit`	NUMERIC	Total gross profit for the acquisition month only (baseline for cohort profitability calculations).
`cohort_month_order_count`	INT64	Total order count for the acquisition month only (baseline for cohort retention calculations).
`cohort_month_order_gross_revenue`	NUMERIC	Total gross revenue for the acquisition month only (baseline for cohort retention calculations).
`cohort_month_order_net_revenue`	NUMERIC	Total net revenue for the acquisition month only (baseline for cohort retention calculations).
`cohort_month_ordered_quantity`	NUMERIC	Total quantity ordered for the acquisition month only (baseline for cohort retention calculations).
`cohort_month_product_gross_profit`	NUMERIC	Total product gross profit for the acquisition month only (baseline for cohort profitability calculations).
`cohort_size`	INT64	Number of customers in the acquisition cohort.
`cost_per_acquisition`	NUMERIC	Customer acquisition cost for the cohort based on marketing spend.
`cumulative_order_cost_of_returns`	NUMERIC	Running total of return costs from acquisition month through current month offset.
`cumulative_order_count`	INT64	Running total of orders from acquisition month through current month offset.
`cumulative_order_fulfillment_cost`	NUMERIC	Running total of SourceMedium configuration sheet fulfillment costs from acquisition month through current month offset.
`cumulative_order_gross_profit`	NUMERIC	Running total of gross profit from acquisition month through current month offset.
`cumulative_order_gross_revenue`	NUMERIC	Running total of gross revenue from acquisition month through current month offset.
`cumulative_order_merchant_processing_fees`	NUMERIC	Running total of payment processing fees from acquisition month through current month offset.
`cumulative_order_net_revenue`	NUMERIC	Running total of net revenue from acquisition month through current month offset for LTV calculation.
`cumulative_order_net_shipping`	NUMERIC	Running total of net shipping revenue from acquisition month through current month offset.
`cumulative_order_product_cost`	NUMERIC	Running total of SourceMedium configuration sheet or Shopify product costs from acquisition month through current month offset.
`cumulative_order_shipping_cost`	NUMERIC	Running total of SourceMedium configuration sheet shipping costs from acquisition month through current month offset.
`cumulative_ordered_quantiy`	NUMERIC	Running total of items ordered from acquisition month through current month offset.
`cumulative_product_gross_profit`	NUMERIC	Running total of product gross profit from acquisition month through current month offset.
`cumulative_second_order_count`	INT64	Running count of customers who have placed a second order through current month offset.
`cumulative_third_order_count`	INT64	Running count of customers who have placed a third order through current month offset.
`customer_count`	INT64	Number of distinct customers with orders in the month offset period.
`months_since_first_order`	INT64	Numeric offset from acquisition month (0 = acquisition month, 1 = month +1, etc.).
`months_since_first_order_name`	STRING	Label for months since first valid purchase (e.g., ‘Acquisition Month’, ‘+1’).
`order_cost_of_returns`	NUMERIC	Cost of returns processed in the month offset period.
`order_count`	INT64	Number of orders placed in the month offset period.
`order_fulfillment_cost`	NUMERIC	Fulfillment and handling costs for orders in the month offset period, sourced from the SourceMedium financial cost configuration sheet.
`order_gross_profit`	NUMERIC	Total gross profit including all costs for orders in the month offset period.
`order_gross_revenue`	NUMERIC	Gross revenue before discounts and refunds for orders in the month offset period.
`order_merchant_processing_fees`	NUMERIC	Payment processing fees for orders in the month offset period.
`order_net_revenue`	NUMERIC	Net revenue after discounts and refunds for orders in the month offset period.
`order_product_cost`	NUMERIC	Product cost (COGS) for orders in the month offset period, sourced from the SourceMedium financial cost configuration sheet or Shopify product costs.
`order_product_gross_profit`	NUMERIC	Product gross profit (revenue minus product cost) for orders in the month offset period.
`order_shipping`	NUMERIC	Net shipping revenue (charged to customers) for orders in the month offset period.
`order_shipping_cost`	NUMERIC	Shipping cost incurred for orders in the month offset period, sourced from the SourceMedium financial cost configuration sheet.
`ordered_quantity`	NUMERIC	Total quantity of items ordered in the month offset period.
`sm_channel`	STRING	Acquisition channel for cohort segmentation (mapped from channel field in parent model).
`sm_order_line_type`	STRING	Order line type segmentation (mapped from slice field in parent model).