Turning AI into an Analyst: OpenClaw Skill System + DuckDB for E‑Commerce Forecasting

Skill System Overview

OpenClaw extends large language models with a Skill system . Each Skill is a SKILL.md markdown file that describes a workflow in plain language. When a user query matches a Skill description, OpenClaw injects a concise XML snapshot of the Skill into the LLM prompt and, upon match, executes the full Skill body.

Skill Processing Pipeline

Discovery : Scan configured directories, merge built‑in and custom SKILL.md files.

Parse : Read YAML front‑matter to extract name, description, dependencies, etc.

Filter : Discard disabled Skills, missing dependencies, or incompatible environments.

Inject : Pack remaining Skills into a ~100‑word XML snippet and add it to the system prompt.

Runtime : When a query matches a Skill, load the full SKILL.md and execute the defined steps.

OpenClaw ships with >50 built‑in Skills (weather lookup, GitHub operations, smart‑home control, AI‑code generation) and allows users to create arbitrary new Skills.

E‑Commerce Forecasting Use‑Case

Traditional row‑store MySQL queries on billions of rows take hours, preventing daily model retraining. DuckDB, a column‑store analytical engine, provides sub‑second query performance on the same data, enabling rapid feature extraction and model iteration.

Setup Summary

Create an RDS DuckDB analytical instance (spec myduck.n2.large.1) in the same VPC and zone as compute resources.

Import the public 7‑month e‑commerce behavior dataset into MySQL (or let OpenClaw generate the import script).

Define a single‑line Skill that references the open‑source predictor repository: https://github.com/huanjizhou/ecommerce-predictor.

Skill Definition (Front‑Matter)

name: ecommerce-predictor
description: "电商用户行为时间序列预测。GradientBoosting/Lasso 预测 PV、UV、购买量。"
use when: 预测、时间序列、趋势分析、销量预测、电商预测.
not for: 实时风控、中国电商双 11/618、非时序分类问题.
metadata:
  OpenClaw:
    emoji: "📈"
requires:
  bins: ["python3"]

Core Pipeline Inside the Skill

Rolling Training : Train on the full historical window, moving the end date forward each month.

train_start = '2019-10-01'  # fixed start
train_end   = '2019-11-01'  # moves forward each month

Auto Validation : Validate on the next month and compute metrics such as MAPE and R².

val_start = train_end
val_end   = '2019-12-01'
mape = mean_absolute_percentage_error(y_true, y_pred) * 100

History Tracking : Store each run’s metadata and metrics in validation_history.json (version, training days, best model, PV MAPE, purchase MAPE, etc.).

Auto Model Selection : Train multiple models (Ridge, Lasso, RandomForest, GradientBoosting, XGBoost, …) and automatically select the one with the lowest PV MAPE.

models = {
    'Ridge': Ridge(alpha=1.0),
    'Lasso': Lasso(alpha=0.01),
    'RandomForest': RandomForestRegressor(...),
    'GradientBoosting': GradientBoostingRegressor(...),
    'XGBoost': xgb.XGBRegressor(...)
}
best_model = min(val_results, key=lambda x: val_results[x]['pv_mape'])

First Prediction Results

PV MAPE: 26.92%

Purchase MAPE: 11.39%

Black‑Friday error: 45% (holiday not encoded)

Iterative Improvements (v1.0 → v6.0)

v2.0 (61 days, RandomForest) reduced PV MAPE to 8.15%.

v3.0 (92 days, GradientBoosting) achieved PV MAPE 2.73% and purchase MAPE 42.28%.

v4.0 (123 days, GradientBoosting) kept PV MAPE at 2.73% and solved the Black‑Friday error.

v5.0 (152 days, Ridge) regressed PV MAPE to 29.99% due to over‑fitting on limited features.

v6.0 (183 days, GradientBoosting) reached PV MAPE 10.03% , purchase MAPE 12.65% , and eliminated the Black‑Friday error.

Overall gains:

PV prediction error dropped 62.7% (26.92% → 10.03%).

Purchase prediction stayed below the 15% threshold.

Holiday effects fully captured.

Training data grew 5.9× (31 days → 183 days).

Feature set expanded from 5 to ~20 core features.

Model stability verified with leave‑one‑out cross‑validation (MAPE ≈ 5.38%).

Key Takeaways

Skill System : Providing AI with a concise, machine‑readable manual turns a knowledge‑only model into an executor capable of running arbitrary commands, including full ML pipelines.

DuckDB : Column‑store analytics deliver >1,000× speedup over MySQL for large‑scale time‑series queries, enabling daily model retraining without impacting production workloads.

Closed‑Loop Automation : The combination creates a self‑optimizing loop – data ingestion → feature extraction (DuckDB) → model training/validation (Skill) → automatic model selection → next‑cycle prediction.