Time features answer "what did this entity do recently, compared with before?", the strongest signal in most
churn, fraud, credit and demand models. They are also the easiest place to leak the future. Every feature is
computed for a row's anchor (its prediction time minus data latency) from events strictly before
that anchor. scripts/pit_features.py computes window features from a CSV event log for a snapshot spine or
for every event; scripts/gen_window_sql.py emits the same features as SQL for PostgreSQL, DuckDB, BigQuery
or Snowflake. On the fixtures the two agree row for row (verified in DuckDB) and match pandas
rolling(..., closed="left").
Trigger Conditions
Activate this skill when the user:
- Needs rolling-window, as-of or "last N days" features from transactions, logins, clicks, sensor readings or orders
- Builds RFM (recency, frequency, monetary), velocity (fraud), engagement trend (churn) or lag features (forecasting)
- Asks how to compute features "as of" a scoring date, or per event without using that event or later ones
- Has a model that looks great offline and suspects look-ahead bias in time features
- Needs the SQL for windowed features in a warehouse
Choosing which features to build: feature-engineering-idea-generator; checking the result for leakage: target-leakage-detector; serving the same features online: feature-store-schema-designer; forecasting models themselves: time-series-forecasting-model-builder.
Workflow
1. SPINE -> rows to score: (entity, cutoff) snapshots, or each event (event-level)
2. LATENCY -> how stale is the data at scoring time? anchor = cutoff - latency
3. WINDOWS -> pick 3-4 windows on a log scale matching the decision cadence (1h/24h/7d, 7d/30d/90d)
4. COMPUTE -> python scripts/pit_features.py --events ev.csv --entity id --time ts --value amount \
--spine spine.csv --cutoff-col score_date --windows 7d 30d 90d --latency 6h -o f.csv
5. SQL -> python scripts/gen_window_sql.py --dialect bigquery ... (same semantics in the warehouse)
6. DERIVE -> ratios (short/long), deltas, per-unit rates, days since milestones
7. VERIFY -> no feature uses data at/after the anchor; replay one entity by hand; leakage scanFeature Families
| Family | Examples | Typical use |
|---|---|---|
| Recency | seconds/days since last event, since last purchase, since last support ticket | churn, reactivation |
| Frequency | events in 1h / 24h / 7d / 30d, active days in 30d | engagement, fraud velocity |
| Monetary | sum / mean / max amount per window | CLV, fraud, credit |
| Velocity / burst | count in 1h vs mean hourly count over 7d; ratio of short to long mean | fraud, bot detection |
| Trend | spend 30d / spend previous 30d; slope of weekly counts | churn (declining engagement) |
| Diversity | distinct merchants / devices / countries / products per window | fraud, cross-sell |
| Expanding | all-time count, mean and max before the anchor; tenure | normalisation for new vs old entities |
| Relative to self | amount / entity mean over 30d; z-score vs own history | anomaly, fraud |
| Lags (series) | y[t-7], y[t-14], y[t-28], y[t-364]; rolling means shifted by the horizon | forecasting |
| Smoothed | exponentially weighted mean (half-life 7d) | stable recency-weighted levels |
| Milestones | days since signup, since plan change, until contract end | churn, renewals |
| Calendar | day of week, hour, month, holiday, payday, days to holiday | demand, traffic |
Definitions, formulas and code (pandas, polars, SQL) for each: window-feature-catalog.md.
The Point-in-Time Rule
[ window 7d ............................. )
---------|-----------------------------------------|----------|------> time
anchor - 7d anchor cutoff
^ latency ^
events counted: anchor - 7d <= t_event < anchor prediction made here- Strictly before:
t_event < anchor, never<=. An event stamped at the cutoff is usually part of the outcome (the transaction being scored, the cancellation itself). - Event-level scoring excludes the current event (
--event-level): the transaction being scored cannot appear in its own velocity count. - Latency: if events land in the warehouse with a delay, training must use the same delay
(
--latency 6h), or the model learns from data production will not have yet. - Windows anchored at the anchor, recency measured to the anchor: both scripts use the same convention.
Spine Design
| Scoring pattern | Spine | Notes |
|---|---|---|
| Weekly batch churn | one row per active customer per Monday | eligibility filter at each date; group CV by customer |
| Real-time fraud | one row per transaction (--event-level) | features from the card's earlier transactions only |
| Monthly credit review | one row per account per month-end | label window starts after the snapshot |
| Daily demand | one row per store-SKU per day | lags must respect the forecast horizon |
Spine construction, sampling of negatives and snapshot frequency: spine-and-snapshot-design.md.
Choosing Windows
- Match the decision cadence: real-time fraud needs minutes/hours; monthly churn needs weeks/months.
- Use 3-4 windows spaced roughly x4-x8 apart (1h, 6h, 24h, 7d or 7d, 30d, 90d, 365d).
- Always pair a short and a long window so the model can see change (
ratio_<value>_<short>_<long>). - Longer windows need longer history before the first usable snapshot (warm-up period): drop or flag snapshots whose history is shorter than the longest window.
- Count-type features should be 0, not missing, when no events fall in the window; means/max are missing (the model or an indicator handles it).
Forecasting Lags
For a forecast made at day t for day t + h, a lag k is only available if k >= h (direct strategy):
- Horizon 7 days: y[t-7], y[t-14], y[t-28] are valid; y[t-1] is not (unknown at forecast time for t+7).
- Rolling statistics must be shifted by h before rolling.
h = 7
g = df.groupby("series_id")["y"]
df["lag_7"] = g.shift(h)
df["rmean_28"] = g.transform(lambda s: s.shift(h).rolling(28, min_periods=7).mean())Output Format
When building time features, deliver:
- Spine definition - rows, cutoff semantics, eligibility, latency.
- Feature list - name, definition (window, aggregation, filter), anchor convention, missing/zero behaviour.
- Code -
pit_features.pycommand or pandas/polars code, and the warehouse SQL fromgen_window_sql.py. - Verification - a hand-checked entity, the leakage scan, and the warm-up rule.
- Serving note - how the same features are computed online (stream, feature store) with the same windows.
Scripts
python scripts/pit_features.py --events txns.csv --entity card_id --time timestamp --value amount \
--event-level --windows 1h 24h 7d --distinct merchant_category country -o feats.csv
python scripts/pit_features.py --events events.csv --entity customer_id --time ts --value amount \
--spine snapshots.csv --cutoff-col score_date --windows 7d 30d 90d --latency 6hPure Python (bisect per entity). Per window: n, sum, mean, max of --value, distinct counts of --distinct
columns; plus n_prior, secs_since_last and the short/long mean ratio. Event-level mode excludes the current
event; spine mode keeps the spine's columns. Times: ISO dates/datetimes (with Z or offsets) or unix seconds/ms.
Exit codes: 0 written, 2 usage or read error.
python scripts/gen_window_sql.py --dialect postgres --events payments.txns --entity card_id --time txn_ts \
--value amount --windows 1h 24h 7d --distinct merchant_id --event-level --id txn_id
python scripts/gen_window_sql.py --dialect bigquery --events '`p.d.events`' --entity customer_id \
--time event_ts --spine '`p.d.snapshots`' --cutoff score_ts --value revenue --windows 7d 30d 90d --latency 6hDialects: postgres, duckdb, bigquery, snowflake. One LEFT JOIN of spine to events bounded by the longest
window, conditional aggregates per window, strict < on the anchor, current event excluded by id in
event-level mode, days since the last event. Exit codes: 0 printed, 2 usage error.
Additional Resources
| File | Content |
|---|---|
| window-feature-catalog.md | Every feature family with formulas and pandas / polars / SQL code: RFM, velocity, trend, diversity, EWMA, self-relative, lags, calendar |
| spine-and-snapshot-design.md | Building spines, eligibility, snapshot frequency, warm-up, negative sampling, group-aware CV |
| latency-backfill-and-serving.md | Event vs processing time, late-arriving data, backfills, online/offline parity, streaming windows |
| performance-at-scale.md | Making window features fast: polars, DuckDB, Spark, pre-aggregated daily tables, incremental computation |
anomaly-detection-model-builder
Build and evaluate anomaly detection for tabular, sensor and transaction data with few or no labels: define the anomaly type (point, contextual, collective), choose detectors (robust z-score/MAD and IQR rules, Mahalanobis with robust covariance, k-NN distance, LOF, Isolation Forest, one-class SVM, PCA reconstruction / autoencoders, forecasting residuals for time series), combine them by rank, explain each alert by its deviating features, set thresholds from an alert budget or the score distribution, and evaluate with precision@k, PR-AUC and recall on known incidents, or detector agreement when no labels exist. Includes pure-Python detectors (robust z, Mahalanobis, kNN, Isolation Forest, ensemble) and an evaluation script.
catboost-model-builder
Build CatBoost models that use its strengths correctly: raw categorical columns as cat_features (ordered target statistics, no manual encoding), text_features, Pool construction with NaN-safe categories, ordered boosting and has_time for temporal data, symmetric (oblivious) trees, overfitting detector with eval_set and use_best_model, losses for classification, regression (Quantile, Poisson, Tweedie) and ranking (YetiRank), class weights, monotone constraints, feature importance (PredictionValuesChange, LossFunctionChange) and SHAP values, GPU training and model export. Plans cat/text/numeric columns from a CSV and generates a runnable training script, and checks parameter sets for the conflicts CatBoost rejects (iterations vs n_estimators, Bayesian bootstrap with subsample, multiple class-weight options, depth > 16, use_best_model without eval_set).
categorical-encoding-selector
Choose and implement the right encoding for every categorical column given the model family and the data: one-hot (with infrequent-level grouping and handle_unknown), ordinal with an explicit order, cross-fitted target encoding (scikit-learn TargetEncoder, CatBoost ordered statistics, category_encoders inside CV), frequency/count encoding, feature hashing, native categorical support in LightGBM, HistGradientBoosting, XGBoost and CatBoost, prefix levels for codes (zip3, NAICS-2), and embeddings. Measures cardinality, rare-level share, unseen-level rate on a time-ordered holdout and target signal per column, emits the ColumnTransformer, and quantifies target-encoding leakage (in-sample vs out-of-fold vs holdout AUC).
classification-model-selector
Choose the right classification algorithm for tabular, text or mixed data and prove the choice with a fair bake-off: logistic regression (L2, L1, elastic net), linear SVM, naive Bayes, k-NN, RBF SVM, decision trees, random forest, extra trees, HistGradientBoosting, LightGBM, XGBoost, CatBoost, explainable boosting machines (EBM/GAM), points scorecards and MLPs, matched to data size, width, categorical and high-cardinality share, missingness, class imbalance, text, and constraints such as interpretability, monotonic constraints, calibrated probabilities, latency budgets and tiny deployable models. Ranks candidates with starting hyperparameters and cautions, then generates a bake-off script running every candidate on identical CV folds with per-fold metrics, fit time and prediction latency.
clustering-algorithm-selector
Choose the right clustering algorithm and check that clustering makes sense at all: cluster tendency (Hopkins statistic with a correlated-features caveat, nearest-neighbour isolation), then k-means / MiniBatchKMeans, Gaussian mixtures with BIC, agglomerative (Ward, average, complete, single) with dendrograms, DBSCAN, HDBSCAN, OPTICS, spectral clustering, BIRCH, mean shift, k-prototypes and k-modes for mixed or categorical data, and Gower distance, matched to data size, expected shapes, noise, varying density, known k, soft memberships, hierarchy and streaming. Covers preprocessing (scaling, encoding, dimensionality, distance metrics), validation (internal indices, bootstrap stability, external labels, business profiling) and assigning new points.
collaborative-filtering-implementer
Implement collaborative filtering recommenders that beat popularity: item-based kNN with cosine, Jaccard or asymmetric similarity, shrinkage, neighbourhood size, positive-only or mean-centred rating signals and 'because you liked X' explanations; implicit ALS with confidence weighting, BPR pairwise ranking and the closed-form EASE model in plain NumPy, tuned on a temporal validation split by recall@k and NDCG@k and refit on all data; plus user-kNN, explicit matrix factorisation, the implicit/LightFM libraries, hyperparameter ranges, cold-start fallbacks, scaling with sparse matrices and approximate nearest neighbours. Produces top-k lists in the format of the recommendation evaluator.