rpt_executive_summary_daily

version: 2

models:
  - name: rpt_executive_summary_daily
    description: >
      Daily KPI aggregation for executive reporting and dashboards (revenue, ROAS, profit, targets). Grain: One row per (smcid, sm_channel, sm_sub_channel, date). Date field: date. Critical filters: sm_channel for channel-specific KPIs; date for time windows. Key joins: none; drill down to obt_orders using (date, sm_channel) filters.
    columns:
      - name: active_subscriber_count
        description: >
          The number of customers who have at least one active subscription.

      - name: active_subscription_count
        description: >
          The number of active subscriptions with a store.

      - name: ad_clicks
        description: >
          The number of times ads were clicked.

      - name: ad_impressions
        description: >
          The number of times ads were shown.

      - name: ad_platform_reported_conversions
        description: >
          The number of conversions attributable to ads, as reported by the advertising platform.

      - name: ad_platform_reported_revenue
        description: >
          The amount of revenue attributable to the ads, as reported by the advertising platform.

      - name: ad_spend
        description: >
          The amount of money spent on ads.

      - name: cancelled_passive_subscription_count
        description: >
          The number of subscriptions that were terminated without active customer initiation, often due to reasons like the natural expiration of the subscription period.

      - name: cancelled_subscriber_count
        description: >
          The number of customers who cancelled their final subscription (they do not have any active subscriptions).

      - name: cancelled_subscription_count
        description: >
          The number of subscriptions voluntarily cancelled by customers.

      - name: churned_subscriber_count
        description: >
          The cumulative number of customers who have cancelled their final subscription.

      - name: churned_subscription_count
        description: >
          The cumulative number of subscriptions that have been cancelled.

      - name: contribution_profit
        description: >
          Gross profit minus ad spend.

      - name: customer_count
        description: >
          The number of unique customers who made a purchase.

      - name: date
        description: >
          The date in YYYY-MM-DD format.

      - name: gross_profit
        description: >
          Net order revenue and shipping revenue minus order product cost, order shipping cost, order fulfillment cost, and merchant processing fees.

      - name: net_profit
        description: >
          Gross profit minus ad spend and operating expenses.

      - name: new_customer_count
        description: >
          The number of unique customers who made their first purchase. Special Considerations: Platform coverage varies; tests allow for low coverage thresholds in some cases.

      - name: new_customer_order_count
        description: >
          The number of orders from new customers.

      - name: new_customer_order_gross_revenue
        description: >
          The gross order revenue from new customers.

      - name: new_customer_order_net_revenue
        description: >
          The net order revenue from new customers.

      - name: new_subscriber_count
        description: >
          The number of customers who converted on their first subscription program.

      - name: new_subscription_count
        description: >
          The number of new subscriptions started by customers.

      - name: operating_expenses
        description: >
          The cost the business incurs while performing its normal operational activities. This data is entered into the Financial Cost - Operating Expenses tab of the SourceMedium financial cost configuration sheet.

      - name: order_count
        description: >
          The number of orders placed by customers.

      - name: order_discounts
        description: >
          The total amount of discounts applied to orders.

      - name: order_fulfillment_cost
        description: >
          The blended cost to fulfill (pick and pack) orders for customers, considering that fulfillment cost is a variable order cost included in cost of goods sold (COGS). This data is recorded in the Financial Cost - Fulfillment tab of the financial cost configuration sheet.

      - name: order_gross_revenue
        description: >
          The gross revenue for orders, based on order lines. Gross revenue is calculated by multiplying the price of an order's lines by the quantity purchased. Gross revenue excludes revenue from gift card purchases.

      - name: order_gross_revenue_after_discounts
        description: >
          The gross order revenue minus order discounts.

      - name: order_merchant_processing_fees
        description: >
          The payment processing fees for orders. This data is entered in the Financial Cost - Merchant Processing Fees tab of the SourceMedium financial cost configuration sheet.

      - name: order_net_revenue
        description: >
          The gross order revenue minus order discounts and refunds.

      - name: order_net_shipping_revenue
        description: >
          The gross shipping revenue for orders minus shipping discounts and shipping refunds.

      - name: order_product_cost
        description: >
          The landed cost of orders as defined by the SourceMedium financial cost configuration sheet (or input into Shopify) multiplied by the quantity purchased. The SourceMedium financial cost configuration overrides any costs input into Shopify.

      - name: order_refunds
        description: >
          The total amount of refunds applied to orders.

      - name: order_return_cost
        description: >
          The blended cost to handle returned products from a customer, considering that the cost of returns is a variable order cost included in cost of goods sold (COGS). This data is entered in the Financial Cost - Shipping tab of the SourceMedium financial cost configuration sheet.

      - name: order_shipping_cost
        description: >
          The blended cost to ship products to customers, considering that shipping costs are variable order costs included in cost of goods sold (COGS). This data is set in the Financial Cost - Shipping tab of the SourceMedium financial cost configuration sheet.

      - name: order_total_revenue
        description: >
          Total order revenue after factoring in shipping revenue, taxes collected, discounts, and refunds.

      - name: order_total_taxes
        description: >
          The amount of order and shipping taxes minus order and shipping tax refunds.

      - name: product_gross_profit
        description: >
          Net order line revenue minus product cost.

      - name: repeat_customer_count
        description: >
          The number of unique customers who made at least their second purchase.

      - name: repeat_customer_order_count
        description: >
          The number of orders from repeat customers.

      - name: repeat_customer_order_gross_revenue
        description: >
          The gross order revenue from repeat customers. Special Considerations: Platform aggregation rules can differ; see tests for permitted thresholds.

      - name: repeat_customer_order_net_revenue
        description: >
          The net order revenue from repeat customers.

      - name: sm_channel
        description: >
          Sales channel via hierarchy: (1) exclusion tag 'sm-exclude-order' -> excluded; (2) config sheet overrides; (3) default logic (amazon/tiktok_shop/walmart.com, pos/leap -> retail, wholesale tags -> wholesale, otherwise online_dtc). Note: excluded channel is omitted from Executive Summary and LTV.

      - 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: sm_sub_channel
        description: >
          Sub-channel from source/tags with config overrides (e.g., Facebook & Instagram, Google, Amazon FBA/Fulfilled by Merchant).

      - name: target_ad_spend
        description: >
          The ad spend target for the store. This data is entered in the Targets tab of the SourceMedium configuration sheet.

      - name: target_average_order_value
        description: >
          The average order value target for the store. This data is entered in the Targets tab of the SourceMedium configuration sheet.

      - name: target_cost_per_acquisition
        description: >
          The cost per acquisition target for the store. This data is entered in the Targets tab of the SourceMedium configuration sheet.

      - name: target_order_conversion_rate
        description: >
          The order conversion rate target for the store. This data is entered in the Targets tab of the SourceMedium configuration sheet.

      - name: target_order_count
        description: >
          The order count target for the store. This data is entered in the Targets tab of the SourceMedium configuration sheet.

      - name: target_order_gross_revenue
        description: >
          The gross order revenue target for the store. This data is entered in the Targets tab of the SourceMedium configuration sheet.

      - name: target_order_net_revenue
        description: >
          The net order revenue target for the store. This data is entered in the Targets tab of the SourceMedium configuration sheet.

      - name: target_return_on_ad_spend
        description: >
          The return on ad spend target for the store. This data is entered in the Targets tab of the SourceMedium configuration sheet.

      - name: target_total_order_revenue
        description: >
          The total order revenue target for the store. This data is entered in the Targets tab of the SourceMedium configuration sheet.

      - name: target_website_sessions
        description: >
          The website sessions target for the store. This data is entered in the Targets tab of the SourceMedium configuration sheet.

      - name: website_sessions
        description: >
          The number of sessions on the website.