Customer Creditcard Features

Customer Credit-Card Feature Engineering

Purpose: This skill prescribes a production-ready, leakage-safe feature engineering workflow for customer-level credit-card and general banking models (marketing response, propensity-to-apply, credit-risk, churn, cross-sell). The canonical row is (customer_id, cutoff_date) and every feature must be computable from data with timestamps <= cutoff_date.

Start by pinning the problem: target definition, label window/horizon, prediction cadence, and allowed data sources (e.g., can we use bureau data, credit-pull timing, or third-party enrichments?). If any item is ambiguous, stop and clarify before coding.

Step 0 — Problem scoping (mandatory)

1. Define the label precisely (e.g., applied_for_card within 30 days, default within 365 days, responded_to_offer within 30 days).
2. Choose cutoff_date semantics and sampling cadence (daily, weekly, monthly snapshots).
3. Choose outcome horizon(s) (15/30/90/365 days) and tie rolling-window lengths to the horizon.
4. Clarify permitted external/data-enrichment sources (credit bureau, alternative data, 3rd-party spending enrichments).

Core tables and canonical schema

Create a mapping from your source tables to the canonical shapes. Example canonical tables (dtypes and columns described in references/schema.md):

• customers — customer_id, signup_date, dob, gender, address, acquisition_channel, kyc_level, customer_status
• cards — card_id, customer_id, card_type, issue_date, active_flag, product_tier, credit_limit, currency
• transactions — txn_id, card_id, customer_id, txn_ts, amount, currency, merchant_category, merchant_id, pos_entry_mode, auth_result, fraud_flag, is_refund, settlement_ts, geo_country, geo_region
• balances — customer_id, date, statement_balance, min_payment_due, current_balance, available_credit, credit_limit
• payments — payment_id, customer_id, card_id, payment_ts, amount, method, status
• authorizations — auth_id, txn_id, auth_ts, auth_result (approved/declined), decline_reason
• chargebacks / disputes — dispute_id, txn_id, customer_id, open_ts, close_ts, dispute_amount, dispute_result
• applications — application_id, customer_id, product, app_ts, app_status (start, submit, approved, rejected), decision_ts
• marketing_events — event_id, customer_id (nullable), channel (email, mail, portal, app), campaign_id, placement, presentment_id, click_flag, event_ts, session_id
• bureaus — customer_id, pull_ts, score, delinquency_counts, open_accounts, total_credit_limit, total_balance
• crm_interactions — interaction_id, customer_id, channel, subject, created_ts, resolved_ts, outcome, sentiment

Confirm the exact column names and types with your data engineering team; do not assume homogeneity across systems.

Step 1 — Leakage hygiene (non-negotiable)

All features must be computed from rows with timestamp <= cutoff_date. Additional rules:

• Reconstruct mutable snapshots (e.g., credit_limit or customer_status) as-of cutoff_date rather than reading the current table.
• For settled/settling events (refunds, disputes, chargebacks), use settlement_ts / close_ts with a configurable settlement_buffer_days to avoid late-settling leakage.
• For bureau pulls, record bureau_pull_ts and do not use bureau data pulled after the cutoff_date (including pulls triggered by the model itself).
• For marketing presentments visible only after application or approval, treat as leakage and exclude unless timing proves they occur before the label window.

Read references/leakage.md for domain-specific traps and mitigations.

Step 2 — Rolling-window and window selection

Tie windowed aggregations to the label horizon. Defaults:

• Digital marketing / presentments: 15d, 30d short windows
• Spend & using signals: 7d, 30d, 90d, 180d, 365d
• Historic exposure (mail/email): up to 1 year

Pick ~6 windows per the customer-inactivity guidance: three short, three long. Record the chosen set in feature_spec.yaml.

Step 3 — Feature families (exhaustive)

Below are feature groups with representative feature names and implementation notes. Use as a checklist — implement the ones relevant to your use-case.

This section is intentionally modeled after the inactivity skill, but adapted for banking and card behavior. The same principle applies: build a robust set of features that covers customer lifecycle, recent behavior, trend changes, product affinity, channel preference, and digital engagement without leaking post-cutoff information.

3.1 Identity, lifecycle, and customer context

• age_years (from dob), age_bucket
• tenure_days = cutoff_date - signup_date
• is_new_customer_7d, is_new_customer_30d, is_new_customer_90d
• customer_status_asof_cutoff (reconstructed from account lifecycle events)
• days_since_primary_card_issue, days_since_first_txn, days_since_first_application
• has_credit_card, has_debit_card, has_savings, has_personal_loan, has_mortgage
• account_age_bucket (new / active / mature / dormant)

3.2 Product holdings and cross-product features

• n_active_cards, n_inactive_cards
• sum_credit_limit_total, avg_card_limit, max_card_limit
• credit_product_depth = number of active financial products owned
• cross_sell_coverage_score = share of products held relative to customer profile
• days_since_last_product_added, new_product_flag_last_90d
• primary_product_type (e.g., premium cash-back card, low-limit secured card)
• product_holdings_balance_share per product family

3.3 Transaction-level spend features

• Rolling-window totals: txn_count_{7,30,90,180,365}d, txn_amount_sum_{...}, txn_amount_mean_{...}
• Card-specific: txn_count_primary_card_{30d}, txn_amount_sum_primary_card_{90d}
• Category/merchant: txn_amount_grocery_{30d}, txn_count_online_{30d}, top_merchant_share_{90d}
• Channel: instore_txn_share_{90d}, online_txn_share_{90d}
• Authorization and decline signals: decline_rate_{30d}, decline_by_reason_{code}_count
• Refunds and adjustments: refund_count_{90d}, refund_amount_share_{90d}
• avg_daily_spend_30d, median_daily_spend_30d, std_daily_spend_30d

3.4 Balance, utilization, and credit health

• statement_balance_total, current_balance_total, available_credit_total
• credit_utilization_{card,aggregate}_{30d,90d} = current_balance / credit_limit
• max_utilization_last_90d, utilization_trend_30d_vs_90d
• payment_to_balance_ratio_{30d}, min_payment_coverage_ratio_{30d}
• days_since_last_statement, statement_balance_change_{30d}
• credit_limit_growth_{90d}
• utilization_bucket (low / moderate / high / maxed out)

3.5 Payments and repayment behavior

• last_payment_days_ago, avg_payment_amount_last_90d, median_payment_amount_last_90d
• on_time_payment_rate_{30d,90d,365d}
• missed_payment_count_{30d,90d,365d}
• payment_lag_mean_days, payment_lag_p90_days
• minimum_payment_coverage_ratio_{30d}
• payment_method_share (ACH / branch / cheque / transfer)
• autopay_enrolled_flag, autopay_usage_rate_{90d}

3.6 Delinquency, collections, and bureau signals

• past_due_bucket_30d, past_due_bucket_60d, past_due_bucket_90d
• max_past_due_days_last_365d
• collections_contact_count_{90d,365d}
• collections_amount_sum_{365d}
• bureau_score_asof, bureau_active_derogatory_count, bureau_open_accounts
• bureau_total_balance, bureau_total_credit_limit
• bureau_trend_30d for recent changes in score or utilization

3.7 Fraud, disputes, and chargebacks

• chargeback_count_{90d,365d}, chargeback_rate_{90d}
• fraud_flag_recent = any fraud flagged txn in 90d
• fraud_score_proxy, suspicious_merchant_share_{90d}
• dispute_count_{90d}, dispute_amount_share_{90d}
• high_risk_merchant_share — fraction of txn amount at high-risk MCCs
• geo_risk_shift_count — unusual changes in spending location or merchant mix

3.8 Behavioral trends and time-series features

These are essential and often missing from “plain count” feature sets.

• spend_slope_30d, spend_slope_90d from daily spend series
• txn_count_slope_30d, txn_count_slope_90d
• transactions_last_30d / transactions_prev_30d
• spend_last_30d / spend_prev_30d
• spend_last_90d / spend_prev_90d
• two_month_trend_direction (up / flat / down)
• weekly_spend_trend_90d
• volatility_spend_30d, volatility_txn_90d
• spend_decline_pct_30d, txn_decline_pct_30d
• days_since_last_decline_event, recent_utilization_drop

This is exactly where the inactivity skill contributes: trend and decay features, not just counts.

3.9 Digital engagement and marketing response features

For bank marketing or card cross-sell campaigns.

• presentments_{15d,30d,90d}_count, presentment_clicks_{15d,30d,90d}
• card_presentments_{15d,30d}, card_clicks_{15d,30d}
• portal_session_count_{30d}, avg_session_duration_{30d}
• email_sent_{30d,90d,365d}, email_open_rate_{30d,90d}, email_click_rate_{30d,90d}
• time_since_last_open_days, time_since_last_click_days
• campaign_exposure_count_{30d} by channel
• marketing_exposure_share_card = card_presentments / total_presentments
• channel_response_rate_email, channel_response_rate_portal, channel_response_rate_physical
• last_campaign_type_seen, last_channel_seen

3.10 Period-to-period comparison features (the “first 6 months vs last 6 months” family)

These are critical for product and engagement behavior monitoring and should be a formal feature family.

• txns_first_6m, txns_last_6m, txns_last6m_vs_first6m_ratio
• spend_first_6m, spend_last_6m, spend_last6m_vs_first6m_ratio
• payments_first_6m, payments_last_6m, payments_change_pct_6m
• presentments_first_15d, presentments_last_15d, presentments_last15d_vs_first15d_ratio
• email_opens_first_6m, email_opens_last_6m, email_open_6m_trend
• card_usage_trend_6m_slope, utilization_change_6m
• first_3m_vs_last_3m_txn_rate

These features answer exactly the questions: is behavior accelerating, decelerating, or flat? Which customers are transforming their spend patterns?

3.11 Product affinity and merchant affinity

• most_frequent_merchant_top1, most_frequent_merchant_top1_share_{90d}
• top_product_category_share_{90d}
• merchant_category_entropy_{90d}
• spend_share_grocery_{90d}, spend_share_travel_{90d}, spend_share_dining_{90d}
• product_affinity_score_card = share of transactions in the product bucket the customer uses most
• merchant_repeat_rate_{90d} = repeat share of merchants used in the last 90d
• most_frequent_responded_product for campaign response models
• top_historical_product_response_rate, top_product_click_share

3.12 Channel and product preference features

• preferred_channel_email, preferred_channel_portal, preferred_channel_branch
• channel_preference_score (based on spend or engagement share by channel)
• channel_balance_score (digital vs in-branch usage mix)
• preferred_product_family = category the customer spends in most
• channel_product_affinity = channel × product interactions

3.13 Sequence and behavioral flow features

• open_then_click_then_apply sequence flag
• click_without_apply flag
• multiple_exposures_before_response count
• first_exposure_to_response_days
• repeat_exposure_effect = conversion lift after exposure count > 1
• first_2_touch_response_rate, first_5_touch_response_rate
• campaign_fatigue_flag = repeated exposures with declining engagement

3.14 Session and deduplication features

• presentments_per_session_median, sessions_with_card_presentment_share
• unique_presentments_per_session, de_duped_exposure_count
• placement_click_rate_carousel, placement_click_rate_banner, placement_click_rate_splash
• session_to_apply_conversion_rate

3.15 Merchant, geo, and location features

• geo_region_diversity, top_state_share
• n_distinct_countries_last_90d, n_distinct_cities_last_90d
• travel_spend_share for customers with unusual geo variation
• domestic_vs_international_txn_share
• merchant_entropy_{90d}

3.16 Device, channel, and security features

• device_change_count_last_365d, suspicious_device_share
• two_factor_enrolled_flag, recent_password_change_days_ago
• login_frequency_{30d}, auth_failure_rate_{30d}
• new_device_flag_30d, device_trust_score

3.17 Customer support and servicing signals

• crm_contacts_{30,90}d, support_ticket_count_{90d}
• avg_crm_sentiment, customer_complaint_rate_{180d}
• support_resolution_time_days, open_support_issue_flag
• call_center_contact_share, chat_contact_share

3.18 Derived ratios and share features

• card_spend_share = card_spend / total_spend
• card_txn_share = card_txn_count / total_txn_count
• cash_advance_share_{90d}
• revolving_balance_share_{90d}
• top_category_share_{90d}
• decline_rate_30d, refund_rate_90d, late_payment_rate_90d

3.19 Decay-weighted and seasonality features

• recency_weighted_spend_30d = sum(amount * exp(-days_ago / decay_k))
• recency_weighted_declines_90d for high-risk behavior decays
• holiday_spend_index, month_of_year_signal, payday_proximity_flag
• weekday_vs_weekend_spend_share, seasonality_index

3.20 Interaction and model-driving features

• utilization × bureau_score_bucket
• recent_declines × last_payment_days_ago
• presentment_clicks_30d × card_presentment_share
• recent_spend_change × product_affinity_score
• channel_preference × product_category_share
• recency_weighted_spend × default_risk_flag

These interactions are useful only after building the base features and pruning redundant ones.

Step 4 — Window policy and denominator discipline

• Use short windows for digital and campaign features: 7/15/30 days.
• Use longer windows for spend, product, and behavior stability: 90/180/365 days.
• Always keep the denominator alongside a ratio feature: emails_sent, sessions_count, total_txn_count, total_spend.
• For days_since_last_* features, use a sentinel such as 9999 plus a boolean has_* flag.
• Record the chosen windows in feature_spec.yaml for each model family.

Step 5 — Leakage and banking-specific traps

• Do not use post-application approval information as input to application or response models.
• Exclude card presentments or digital events occurring after the outcome label start.
• Use settlement timestamps for refunds/disputes and apply a settlement_buffer_days where appropriate.
• For bureau data, only use pulls with pull_ts <= cutoff_date.
• For account or balance snapshots, reconstruct as-of cutoff_date rather than reading the current table.

Step 6 — Validation and selection

• Check missingness, cardinality, target correlation, and time stability across monthly snapshots.
• Validate trend features with time-split backtests; a feature that only works in one quarter may be a seasonal artifact.
• Run correlation pruning, mutual information screening, and SHAP or permutation importance.
• Keep a small set of interpretable business features rather than a huge raw feature dump.

Step 7 — Testing and reproducibility

• Unit test each feature function for off-by-one logic, zero denominators, and missing customer edge cases.
• Run an end-to-end synthetic integration test with a small fixed dataset.
• Export a feature registry with feature_name, description, source, window_days, type, and owner.

Step 8 — Monitoring and deployment

• Monitor PSI or drift for all core features.
• Monitor feature freshness and null rates by data source.
• Shadow-run new features and compare with the prior production model before rollout.

Step 9 — Reusable patterns for other domains

The same pattern generalizes beyond banking:

• customer lifecycle features
• recency-frequency-monetary features
• trend features and first-vs-last-period comparisons
• product and channel affinity features
• session behavior and engagement sequences
• interaction features with stable denominators
• leakage-safe time windows and as-of dates

This is the core blueprint the inactivity skill gave us, adapted to banking data and customer response modeling.

Step 4 — Windows, denominators, and missingness

• Digital windows: 15/30 days preferred for presentments; longer for campaign history (90/365) as needed.
• Mail and email historic exposures: up to 1 year.
• Always carry denominators (e.g., emails_sent, sessions_count, total_txn_count) when computing rates; replace NaN rates with 0 and preserve denom as separate feature.
• For days_since_last_* features, use a sentinel (e.g., 9999) combined with a boolean has_ever_* flag.

Step 5 — Encoding and cardinality

• Low-cardinality categoricals: one-hot or ordinal where appropriate.
• Medium cardinality: frequency encoding (value_counts / N) + smoothing.
• High-cardinality: target encoding with time-aware OOF, or hashing to fixed buckets.
• For merchant_id, consider hierarchical encodings (merchant sector, merchant cluster) instead of raw id where possible.

Step 6 — Leakage checklist and domain traps

• Do not use application outcome or post-approval placements as input.
• Settling windows: refunds/chargebacks settle late — either filter by settlement_ts <= cutoff_date or apply a buffer.
• Bureau pulls invoked by model or marketing must be excluded if pulled after cutoff_date.
• Card replacement or reissue events dated after cutoff must not be used.

See references/leakage.md for an exhaustive catalog of traps and suggested mitigations.

Step 7 — Validation and filtering

• Assert no feature uses events with timestamps > cutoff.
• Check label prevalence sanity and stability across cutoffs.
• Feature quality checks: missingness rates, cardinality, drift across time, high correlation with label (investigate leaks).
• Reduce to a manageable set: use mutual information, correlation pruning, SHAP influence, and business interpretability to pick ~50–200 features per model.

Step 8 — Testing & reproducibility

• Unit tests: per-function tests for aggregations and windowing to catch leakage and off-by-one errors.
• Integration test: end-to-end customer_id, cutoff_date → feature row comparisons against a golden CSV.
• Serialization: export feature_spec.yaml documenting windows, encodings, and derivations; store canonical SQL or transformation code as part of the feature repo.

Step 9 — Monitoring & production concerns

• Feature drift monitoring (mean/std) with alerting.
• Data freshness checks and SLA for upstream tables.
• Shadow-run monthly re-computation and compare model scores vs training expectations.
• Explainability: register features and short descriptions in a feature registry.

Step 10 — Compliance & privacy

• Mask or hash PII before model training and logging.
• For bureau data, document allowed use cases and retention policies; do not cache sensitive fields beyond permitted retention.
• For marketing features involving external prospect lists, ensure consent/opt-out compliance.

Example SQL snippets (conceptual)

-- 1) Rolling 30-day transaction sum per customer -- SELECT customer_id, SUM(amount) AS txn_amount_30d -- FROM transactions -- WHERE txn_ts > DATEADD(day, -30, :cutoff_date) AND txn_ts <= :cutoff_date -- GROUP BY customer_id

Deliverables and next steps

• SKILL.md (this file) — feature guidance and checklist
• references/schema.md — canonical schemas (below)
• references/leakage.md — domain leakage traps
• Example implementations: integrate into scripts/ (feature builders), unit tests, and the exploratory notebook already included for marketing response.

If you'd like, I will:

• implement rolling-window trends, decay-weighted features, exposure-share features, and session-deduped presentment metrics in customer-marketing-response/scripts/marketing_response_features.py;
• add unit tests and example SQL for each family;
• or create a feature registry feature_spec.yaml capturing names, descriptions, windows, and encodings.