# Analytics roadmap & build spec for Summit Gear Co _Prepared by Strata BI ยท 2026-07-29 ยท 100% buildable from the selections_ > A reference architecture for the analytics stack this business could be running on: its data sources, the dbt modeling layers that turn them into trusted numbers, the dashboards those numbers power, and the AI layer on top. Generated by Strata BI's roadmap engine. It depicts the build and contains **no SQL**. This is the intro reference; the engagement turns it into a full, build-grade spec. ## 1. Executive summary This spec describes **5 dashboards**, built on **5 data sources** flowing through a layered dbt project, with an AI analyst on top. ## 2. How Strata BI builds this This roadmap is the foundation. We take what's here, keep digging, enriching it with everything specific to your business, and feed it into our purpose-built engine. - **Built fast.** Your full analytics stack comes together in roughly a fifth of the time a traditional team would take, even one using AI. - **Built right.** We bake in efficiency, cost, and security protocols most engineers never get to. - **Grounded in your business.** Every model reflects your context, not a generic template. - **Your team, empowered.** We train your people for full autonomy; nothing in the build is a black box. ## 3. Business context | Attribute | Value | | --- | --- | | Company | Summit Gear Co | | Industry | Retail & e-commerce | ## 4. Data sources Each source lands as staging models: one tidy table per entity. ### commerce - `stg_commerce__orders`: one row per orders. - `stg_commerce__customers`: one row per customers. - `stg_commerce__products`: one row per products. - `stg_commerce__refunds`: one row per refunds. ### accounting - `stg_accounting__invoices`: one row per invoices. - `stg_accounting__payments`: one row per payments. - `stg_accounting__expenses`: one row per expenses. - `stg_accounting__customers`: one row per customers. - `stg_accounting__gl_accounts`: one row per gl accounts. ### ads - `stg_ads__campaigns`: one row per campaigns. - `stg_ads__ad_groups`: one row per ad groups. - `stg_ads__spend`: one row per spend. - `stg_ads__conversions`: one row per conversions. ### email_sms - `stg_email_sms__campaigns`: one row per campaigns. - `stg_email_sms__messages`: one row per messages. - `stg_email_sms__subscribers`: one row per subscribers. - `stg_email_sms__engagement`: one row per engagement. ### inventory - `stg_inventory__items`: one row per items. - `stg_inventory__stock_levels`: one row per stock levels. - `stg_inventory__purchase_orders`: one row per purchase orders. - `stg_inventory__shipments`: one row per shipments. ## 5. dbt project structure ``` analytics/ dbt_project.yml packages.yml claude.md company-profile.md models/ staging/ _sources.yml commerce/ stg_commerce__orders.sql stg_commerce__customers.sql stg_commerce__products.sql stg_commerce__refunds.sql accounting/ stg_accounting__invoices.sql stg_accounting__payments.sql stg_accounting__expenses.sql stg_accounting__customers.sql stg_accounting__gl_accounts.sql ads/ stg_ads__campaigns.sql stg_ads__ad_groups.sql stg_ads__spend.sql stg_ads__conversions.sql email_sms/ stg_email_sms__campaigns.sql stg_email_sms__messages.sql stg_email_sms__subscribers.sql stg_email_sms__engagement.sql inventory/ stg_inventory__items.sql stg_inventory__stock_levels.sql stg_inventory__purchase_orders.sql stg_inventory__shipments.sql intermediate/ int_channel_spend.sql int_cost_allocation.sql int_customer_attribution.sql int_location_sales_labor.sql int_purchase_history.sql int_sku_movement.sql marts/ finance/ mart_location_pnl.sql mart_segment_profitability.sql growth/ mart_channel_roi.sql operations/ mart_inventory_sell_through.sql retention/ mart_repeat_retention.sql kpi/ kpi_channel_roi.sql kpi_location_pnl.sql kpi_profitability.sql kpi_repeat_retention.sql kpi_sell_through.sql exposures.yml _docs/ overview.md metrics.md .github/ workflows/ dbt_ci.yml dbt_deploy.yml seeds/ plan_tiers.csv channel_map.csv snapshots/ subscriptions_snapshot.yml account_status_snapshot.yml macros/ cents_to_dollars.sql generate_schema_name.sql date_spine.sql surrogate_key.sql get_relation.sql tests/ assert_active_subscriptions_have_plan.yml assert_positive_mrr.yml unique_account_id.yml no_future_dated_events.yml skills/ cost-analysis.skill freshness.skill lineage-audit.skill metric-consistency.skill test-coverage.skill efficiency.skill ``` **Modeled tables** | Model | Layer | Description | | --- | --- | --- | | `int_channel_spend` | intermediate | Spend normalized across ad platforms and channels. | | `int_customer_attribution` | intermediate | New customers matched to their acquiring channel. | | `mart_channel_roi` | mart | Cost per customer and revenue by channel. | | `kpi_channel_roi` | kpi | Channel ROI feed. | | `int_cost_allocation` | intermediate | Costs mapped to products, services, and segments. | | `mart_segment_profitability` | mart | Margin by product, service, segment, and customer. | | `kpi_profitability` | kpi | Margin leaders-and-laggards feed. | | `int_sku_movement` | intermediate | Sales and stock movement per SKU per period. | | `mart_inventory_sell_through` | mart | Sell-through, cover, and aging by SKU and category. | | `kpi_sell_through` | kpi | Overstock/stockout watch feed. | | `int_purchase_history` | intermediate | Every customer's purchase timeline, normalized. | | `mart_repeat_retention` | mart | Repeat rate, purchase frequency, and retention cohorts. | | `kpi_repeat_retention` | kpi | Returning-customer feed. | | `int_location_sales_labor` | intermediate | Sales and labor hours aligned by location and day. | | `mart_location_pnl` | mart | Contribution margin, labor %, and prime cost by location. | | `kpi_location_pnl` | kpi | Location comparison feed. | ## 6. Dashboards ### Which Channels Actually Pay **Audience:** Owner / marketing lead Money goes into ads and channels; customers come out. What happens in between is attribution theater. You can't see the true cost of a real customer, channel by channel. **Questions it answers** - What does a new customer really cost, channel by channel? - Which channels bring customers who come back? - Where should the next dollar of spend go? **Lineage** ```mermaid flowchart LR sources --> int_channel_spend sources --> int_customer_attribution int_channel_spend --> mart_channel_roi int_customer_attribution --> mart_channel_roi mart_channel_roi --> kpi_channel_roi kpi_channel_roi --> dashboard["๐Ÿ“Š Which Channels Actually Pay"] ``` **Metrics** - **Spend by channel**: Marketing and ad spend, normalized across platforms. _(grain: weekly; by: channel, campaign)_ - **Cost per new customer**: Spend divided by genuinely new customers, per channel. _(grain: monthly; by: channel)_ - **Revenue per channel**: Revenue attributed to the channel that acquired the customer. _(grain: monthly; by: channel, cohort)_ ### Where the Money Actually Is **Audience:** Owner / CEO / finance lead You can see revenue, but not what any of it costs, so nobody truly knows which products, services, or customers make money and which quietly lose it. **Questions it answers** - Which products or services actually make us money? - Which customers or jobs are we losing money on? - Where would a price change matter most? **Lineage** ```mermaid flowchart LR sources --> int_cost_allocation int_cost_allocation --> mart_segment_profitability mart_segment_profitability --> kpi_profitability kpi_profitability --> dashboard["๐Ÿ“Š Where the Money Actually Is"] ``` **Metrics** - **Gross margin by segment**: Revenue minus direct costs, per product, service, or segment. _(grain: monthly; by: product, service, segment)_ - **Contribution margin**: What each sale contributes after all variable costs. _(grain: monthly; by: product, channel)_ - **Cost breakdown**: Where each dollar of revenue goes: COGS, delivery, overhead. _(grain: monthly; by: category, segment)_ ### Inventory & Sell-through **Audience:** Owner / ops / buying lead Cash sits on shelves in the wrong SKUs while the winners stock out. The buy decisions that caused it were made on gut. **Questions it answers** - Which products are overstocked, and which are about to stock out? - How fast does each SKU actually sell through? - Where is cash tied up in inventory right now? **Lineage** ```mermaid flowchart LR sources --> int_sku_movement int_sku_movement --> mart_inventory_sell_through mart_inventory_sell_through --> kpi_sell_through kpi_sell_through --> dashboard["๐Ÿ“Š Inventory & Sell-through"] ``` **Metrics** - **Sell-through rate**: Units sold as a share of units available, per period. _(grain: weekly; by: sku, category, channel)_ - **Weeks of cover**: How long current stock lasts at the recent sales rate. _(grain: weekly; by: sku, location)_ - **Stockout rate**: Share of items (or days) out of stock when demanded. _(grain: weekly; by: sku, location)_ - **Inventory value by age**: On-hand value bucketed by how long it has sat. _(grain: weekly; by: age_bucket, category)_ ### Customers Who Come Back **Audience:** Owner / growth lead New customers get all the attention, but the business runs on the ones who return. You can't see who comes back, how often, or what changed when they stopped. **Questions it answers** - What share of customers buy again, and how fast? - Which first purchases predict a loyal customer? - Where are repeat rates slipping? **Lineage** ```mermaid flowchart LR sources --> int_purchase_history int_purchase_history --> mart_repeat_retention mart_repeat_retention --> kpi_repeat_retention kpi_repeat_retention --> dashboard["๐Ÿ“Š Customers Who Come Back"] ``` **Metrics** - **Repeat purchase rate**: Share of customers who buy again within the period. _(grain: monthly; by: cohort, segment)_ - **Customer retention curve**: Share of each cohort still buying at N periods after their first purchase. _(grain: monthly; by: cohort)_ - **Revenue from returning customers**: Share of revenue produced by repeat customers vs. first-timers. _(grain: monthly; by: segment)_ ### Location P&L & Labor **Audience:** Owner / director of ops Each location tells its own story at month-end, and labor creep hides in the averages until the P&L lands. **Questions it answers** - How is each location doing today, not last month? - Where is labor out of line with sales? - Which locations are drifting, and on what? **Lineage** ```mermaid flowchart LR sources --> int_location_sales_labor int_location_sales_labor --> mart_location_pnl mart_location_pnl --> kpi_location_pnl kpi_location_pnl --> dashboard["๐Ÿ“Š Location P&L & Labor"] ``` **Metrics** - **Location contribution margin**: Location revenue minus its direct costs. _(grain: weekly; by: location)_ - **Labor % of sales**: Labor cost as a share of sales, by location and daypart. _(grain: daily; by: location, daypart)_ - **Prime cost**: Cost of goods plus labor, the operator's headline cost. _(grain: weekly; by: location)_ - **Sales per labor hour**: Revenue generated per scheduled labor hour. _(grain: daily; by: location, daypart)_ ## 7. Metric catalog | Metric | Definition | Grain | Dimensions | | --- | --- | --- | --- | | Spend by channel | Marketing and ad spend, normalized across platforms. | weekly | channel, campaign | | Cost per new customer | Spend divided by genuinely new customers, per channel. | monthly | channel | | Revenue per channel | Revenue attributed to the channel that acquired the customer. | monthly | channel, cohort | | Gross margin by segment | Revenue minus direct costs, per product, service, or segment. | monthly | product, service, segment | | Contribution margin | What each sale contributes after all variable costs. | monthly | product, channel | | Cost breakdown | Where each dollar of revenue goes: COGS, delivery, overhead. | monthly | category, segment | | Sell-through rate | Units sold as a share of units available, per period. | weekly | sku, category, channel | | Weeks of cover | How long current stock lasts at the recent sales rate. | weekly | sku, location | | Stockout rate | Share of items (or days) out of stock when demanded. | weekly | sku, location | | Inventory value by age | On-hand value bucketed by how long it has sat. | weekly | age_bucket, category | | Repeat purchase rate | Share of customers who buy again within the period. | monthly | cohort, segment | | Customer retention curve | Share of each cohort still buying at N periods after their first purchase. | monthly | cohort | | Revenue from returning customers | Share of revenue produced by repeat customers vs. first-timers. | monthly | segment | | Location contribution margin | Location revenue minus its direct costs. | weekly | location | | Labor % of sales | Labor cost as a share of sales, by location and daypart. | daily | location, daypart | | Prime cost | Cost of goods plus labor, the operator's headline cost. | weekly | location | | Sales per labor hour | Revenue generated per scheduled labor hour. | daily | location, daypart | ## 8. AI & intelligence layer An AI analyst that reads Summit Gear Co's modeled data sits on top of the stack, with two surfaces: - **Ask (pull)**: ad-hoc questions in plain English, answered from the modeled data. - **Alerts (push)**: custom watchdogs you set by describing the incident once. ## 9. What the full engagement adds This is the intro roadmap. The questions stay short on purpose (a few minutes, not a data audit) so it balances ease against value. The depth that makes it build-ready comes when we work together: - **Model-level build contracts**: materialization, grain, keys, and tests for every model, so it's executable from scratch. - **Every relevant source**, including the ones the wizard didn't ask about. - **Metric and dashboard detail**: definitions and dashboard compositions tailored to how your team actually runs. Personalized this way, the spec typically grows 2-3x and becomes the brief our AI-enhanced engine builds your instance from. ## 10. Notes - This is an **idealized reference architecture**, not a copy of any existing repo. - It contains **no SQL**: model names, descriptions, and lineage only. - The working dbt project, dashboards, and AI app are delivered in the engagement.