Ecommerce Blueprint#
The Factory's Analytics > Blueprints > Ecommerce orders semantic layer builds a complete semantic layer for an online shop's sales, from generated data to a chat app. It is the canonical example of a semantic layer on Hopsworks: metrics defined once over feature groups and sliced by any time range, region, customer group, product or channel.
The build creates:
- four feature groups of generated orders, written by a backfill of two years and a daily job;
- the semantic project named after
<name>with hyphens turned into underscores, such asecommerce_semanticforecommerce-semantic, the blueprint's dbt project with four semantic models, 26 metrics and three saved queries; - the app
<name>-chat, where questions in plain language are translated by an LLM into metric queries.
Build it#
In the project's Factory, open New > Analytics > Blueprints and pick Ecommerce orders semantic layer. The form asks for the name (ecommerce-semantic by default), the repository, and the LLM of the chat app. Create starts the build in the Terminal, where Claude Code runs /hops-semantic-layer <name>, and the Factory shows its phases.
The build runs six phases, recorded in the system's system.yaml:
| Phase | Does |
|---|---|
| Test the generator and app | Runs the tests of the generator and of the app's translator. |
| Backfill history | Writes two years of history up to the last midnight (UTC) with the job <name>-backfill. |
| Register and compile metrics | Writes the time spine, registers the dbt project as the semantic project and compiles it; hops semantic test runs every metric once. |
| Reconcile metrics | Checks that the totals of nine metrics equal SQL over the feature groups, and runs the saved queries. |
| Deploy the chat app | Deploys <name>-chat and smoke-tests it. |
| Schedule the daily job | Schedules <name>-daily, which writes each new day. |
The data#
The generator writes a star schema around order lines, each table an offline Delta feature group tagged analytics_table {"layer": "gold"}.
| Feature group | One row per | Columns |
|---|---|---|
ecommerce_order_items | order line | order_id, customer_id, product_id, ordered_at, status, quantity, unit_price, gross_amount, discount_amount, net_amount, cost_amount, profit_amount |
ecommerce_orders | order | customer_id, ordered_at, channel, payment_method, promotion, status, customer_type, item_count, units, the order's amounts and shipping_fee |
ecommerce_customers | customer | region, country, city, segment, loyalty_tier, acquisition_channel, signup_ts, signup_month |
ecommerce_products | product | product_name, category, subcategory, brand, price_band, list_price, unit_cost, launched_at |
The two years hold about 160,000 orders. Volume grows about 30% a year, faster in Asia Pacific and Latin America; weekends are busier, January is quiet, November and December peak around Black Friday and Cyber Monday, and site-wide sales discount most orders on their days. Every row is a function of its day, so a replayed day rewrites the same rows.
The metrics#
All money is in USD, without shipping, and relates as gross_sales - discounts - returns = revenue and revenue - cost_of_goods_sold = gross_profit. Cancelled orders count in no money metric; a returned order is refunded in full.
| Metric | Type | Meaning |
|---|---|---|
revenue | simple | Net revenue: what customers paid for the items they kept. |
gross_sales, discounts, returns | simple | List value before discounts, discounts given, and refunds. |
cost_of_goods_sold, gross_profit | simple | Cost of the units kept, and revenue minus that cost. |
gross_margin | ratio | Gross profit over net revenue. |
orders, completed_orders, returned_orders, cancelled_orders, all_orders | simple | Orders by status; orders excludes cancelled ones. |
average_order_value, units_per_order | ratio | Net revenue and units per completed order. |
units_sold, buyers, revenue_per_buyer | simple, ratio | Units kept, distinct customers who ordered, and revenue per buyer. |
discount_rate, return_rate, cancellation_rate | ratio | Shares from 0 to 1. |
shipping_revenue, new_customers | simple | Shipping charged on completed orders, and signups by signup date. |
revenue_month_to_date, revenue_trailing_28d | cumulative | Month to date and trailing 28 days; grouped by metric_time. |
revenue_last_year, revenue_growth_yoy | derived, offset | The same period a year earlier, and the growth against it; grouped by metric_time. |
The dimensions are metric_time at any grain, the customer's customer__region, customer__country, customer__city, customer__segment, customer__loyalty_tier, customer__acquisition_channel and customer__signup_month, the order's order__channel, order__payment_method, order__promotion, order__status and order__customer_type, and the product's product__category, product__subcategory, product__brand and product__price_band. The order metrics cannot be grouped by product, since an order has several.
hops semantic query ecommerce_semantic --metrics revenue,average_order_value \
--group-by metric_time__quarter,customer__segment --start-time 2026-01-01
hops semantic query ecommerce_semantic --metrics revenue_growth_yoy --group-by metric_time__month
hops semantic query ecommerce_semantic --saved-query category_performance_quarterly
The chat app#
<name>-chat answers questions such as "net revenue by region per quarter this year" or "which customer segment had the highest average order value last quarter?". Each answer shows one sentence on what the query shows, the query itself, a chart, the rows and the SQL; a follow-up such as "only Europe" refines the previous query. Edit query changes an answer's metrics, group-bys, filters and time range and runs it again, and Build a query writes one from scratch, both without the LLM.
The LLM is any OpenAI-compatible chat endpoint, set as the account environment variables of the member who starts the app:
| Variable | Value |
|---|---|
LLM_URL | The endpoint's base URL, such as https://api.openai.com/v1, or the OpenAI URL of an LLM deployment in the project. |
LLM_MODEL | The model name. |
LLM_API_KEY | The key the endpoint takes. |
The blueprint's form saves them; without them the app answers no questions but its query builder works. Change them in Account settings > Environment variables and restart the app.
The LLM writes the query, never the answer:
- it sees the semantic project's metrics and dimensions, with the values of the small dimensions, and the question, never a data row;
- it answers with a JSON query of metrics, group-bys, filters, a time range, an order and a limit;
- the app refuses a name the catalog lacks, a filter value a dimension does not take and a group-by a metric cannot reach, and sends the reason back to the LLM once;
- the query runs through the semantic layer as the member who started the app, with the filter values bound as literals, never as SQL.