E-Commerce Analytics — from order cleansing to RFM segmentation and ontology

Workshop goal

By the time you finish this Workshop, you will have walked through one full cycle of the following flow inside D.Hub.

• Load a single scenario into portal so that two sub-collections (ecommerce_raw and analytics), 4 datasets, 4 code nodes, 3 pipelines, an ontology with 3 entities and 2 relations, a dashboard, and one knowledge are all registered together.
• Inspect the schemas of raw_orders (from an Oracle ERP) and raw_customers (from a PostgreSQL CRM).
• Use the orders_cleaning pipeline to remove null required-column rows and produce cleaned_orders.
• Use the customer_segmentation pipeline to compute RFM (Recency / Frequency / Monetary) scores and assign four segment labels: champion / loyal / at_risk / new.
• Expand the EC_Customer → EC_purchased → EC_Order → EC_contains_product → EC_Product path in the Graph explorer.
• Walk through the sales_overview dashboard to see segment-by-segment revenue share and trends.

It's an integrated exercise that ties the datasets / dashboard of the Analyst Path and the pipelines / code nodes of the Engineer Path into a single e-commerce scenario. Recommended duration: 90 minutes.

Prerequisites

• An analyst or engineer account with access to D.Hub portal (Editor or higher)
• ~50 KB of download room for one scenario zip

No terminal, Python, or dhub2-examples clone needed. Finishing the entry-level tutorial Import a scenario zip in one shot first makes step 1 flow smoothly.

1. Load the scenario (10 min)

Download one zip and feed it to portal's Import dialog — every asset in the scenario gets registered in a single pass.

With the zip in hand, go to Collections in the left sidebar. The more (⋯) menu next to the Explorer header — the page title Collections area — has an Import (가져오기) item. Open it, pick the zip, and two collections appear automatically.

Following manifest.json, the import creates the following in order:

1. Two collections — ecommerce_raw (alias: E-Commerce Raw Data), analytics (alias: E-Commerce Analytics)
2. Four datasets — raw_orders (raw), raw_customers (raw), cleaned_orders (analytics), customer_analytics (analytics)
3. Four codes (Python) — null_cleanup, join_datasets, rfm_segmentation, build_ecommerce_ontology
4. Three pipelines — orders_cleaning, customer_segmentation, ontology_materialization
5. Ontology — three entities (EC_Customer, EC_Product, EC_Order), two relations (EC_purchased, EC_contains_product)
6. One knowledge — product_manual (product catalog and getting-started doc)
7. One dashboard — sales_overview

Step 1 is complete once the collection tree on the left shows ecommerce_raw and analytics side by side. The split between raw landing zone and processed / derived data mirrors the IoT Workshop — raw_orders and raw_customers live in ecommerce_raw, while pipeline outputs land in analytics.

2. Browse the raw data (10 min)

Open the ecommerce_raw collection and start with raw_orders. The Preview tab shows order transactions.

• order_id — Unique order identifier
• customer_id — Foreign key to raw_customers
• product_id / product_name — Product info
• order_date — Order timestamp
• quantity, unit_price, total_amount — Quantity, unit price, total amount
• status — completed / cancelled / pending

raw_customers is the customer profile pulled from the CRM. Columns: customer_id, name, email, signup_date, region, tier (standard / silver / gold / platinum).

In the Schema tab on both datasets, confirm that customer_id is the same type (string) on both sides. The RFM computation in §3 groups by customer_id — type drift halts the pipeline.

3. Order-cleansing pipeline (15 min)

In the analytics collection's Pipelines section, click orders_cleaning to open the Workflow editor. One node — the null_cleanup script removes rows where any required column on raw_orders is null, producing cleaned_orders.

The script body is essentially one line: drop rows where any of order_id, customer_id, total_amount, order_date is null. Cancelled orders are also filtered so the RFM computation in the next step sees only valid transactions.

Press the top Run button. When it finishes, go back to the left tree and open the Preview tab on cleaned_orders. Step 3 is done when the row count is slightly smaller than raw_orders (the null + cancelled rows that got dropped).

4. RFM segmentation (15 min)

This step is the Workshop's core. In the analytics collection's Pipelines section, open customer_segmentation to see two nodes.

• join_datasets — Inner-join cleaned_orders with raw_customers on customer_id. Output: per-customer transaction history.
• rfm_segmentation — Compute R / F / M scores per customer, then assign a final segment label. Output: customer_analytics.

Click the rfm_segmentation node and expand Script preview to see the RFM formula.

Recency  = (today − last_order_date) days   → lower is better
Frequency = number of orders               → higher is better
Monetary  = SUM(total_amount)              → higher is better

Each score is bucketed into 1–5 quantiles, and the combination determines one of four segments:

Press Run. When it finishes, open customer_analytics. The segment column should hold four labels, and (on the seed data) the row counts distribute roughly champion : loyal : at_risk : new ≈ 2 : 4 : 2 : 2.

5. Ontology + graph exploration (15 min)

Now the graph view. In the analytics collection's Pipelines section, click ontology_materialization. One node — the build_ecommerce_ontology script reads cleaned_orders + raw_customers + customer_analytics in batch mode and lands the following five artifacts in upsert mode.

Press Run. In the left sidebar's Ontology area, confirm the three entities are loaded. Open the Graph explorer and run a one-line Cypher to surface the first instances.

MATCH (c:EC_Customer {segment: 'champion'})-[:EC_purchased]->(o:EC_Order)-[:EC_contains_product]->(p:EC_Product)
RETURN c, o, p
LIMIT 25

Twenty-five purchase paths from champion-segment customers appear. Double-click a node to expand its neighbors and see the full product mix for one customer.

6. Browse the dashboard (15 min)

In the analytics collection's Dashboards section, open sales_overview (alias: Sales overview dashboard). Five widgets share one dataset — customer_analytics.

• Total Customers (statistic) — COUNT(DISTINCT customer_id)
• Total Revenue (statistic) — SUM(total_spend)
• Customer Segments (donut) — Customer counts per segment (champion / loyal / at_risk / new distribution)
• Revenue by Region (bar) — SUM(total_spend) per region, descending
• Avg Order Value by Tier (bar) — AVG(avg_order_value) per tier, descending

Open the ⋯ menu on any widget card and select Edit to see the SQL body — all five widgets are written in SQL Query mode. The dashboard is a strong reference book for the query-mode side of the Analyst Path's Simple vs Query mode toggle.

7. Next steps and a retrospective (5 min)

By this point you've walked through five flows on a single e-commerce scenario:

• Scenario load → ecommerce_raw vs analytics sub-collection split
• Cleansing pipeline → null + cancelled rows dropped
• RFM segmentation → four-label assignment
• Ontology materialization → customer-order-product graph
• Dashboard → segment-aware revenue summary

Pick the most appealing direction for one step further.

• Go back to the Analyst Path's First dashboard lesson and rebuild the sales_overview widget SQL by hand.
• Move to the Engineer Path Pipeline scheduling lesson and register the RFM pipeline as a daily 04:00 automated run (the scenario manifest default).
• Add your own domain scenario to dhub2-examples. This Workshop's flow — raw → cleanse → segment → ontology → dashboard — maps almost identically to other transactional domains (SaaS billing, mobility ride logs).

Verification checklist

You can self-check whether this Workshop is complete via these five points.

• Both ecommerce_raw and analytics collections appear in the collection tree, with raw_orders · raw_customers in ecommerce_raw and cleaned_orders · customer_analytics in analytics.
• After running orders_cleaning, the row count on cleaned_orders is smaller than raw_orders (null + cancelled rows removed).
• After running customer_segmentation, the segment column on customer_analytics is distributed across four labels (champion / loyal / at_risk / new).
• In the Graph explorer, starting from one EC_Customer you can expand the 4-hop path EC_purchased → EC_Order → EC_contains_product → EC_Product.
• The sales_overview dashboard's Customer Segments widget shows all four segments as donut slices.