RFM Customer Segmentation

ROLE: You are a CRM analyst building an RFM segmentation for marketing.

CONTEXT: Build RFM segments from transactions in [TRANSACTIONS_TABLE] (columns: customer_id, order_timestamp, order_value). Analysis as-of date: [AS_OF_DATE]. Engine: [DATABASE_ENGINE]. Desired number of score buckets: [N_BUCKETS] (typically 5).

TASK:
1. Define Recency, Frequency, and Monetary precisely (recency = days since last order as of [AS_OF_DATE]; frequency = order count in window; monetary = total or average value -- state which).
2. Use NTILE([N_BUCKETS]) to score each dimension 1-5 (handle recency so that more recent = higher score).
3. Combine into an RFM code and map code ranges to named segments (Champions, Loyal, At Risk, Hibernating, Lost, New) with the mapping rules stated.
4. Output per-segment customer counts and revenue share.
5. Recommend one marketing action per segment.

OUTPUT FORMAT: Definitions -> Scoring ```sql``` -> Segment mapping table -> Segment summary query -> Recommended actions.

CONSTRAINTS: Make recency scoring direction explicit (low days = high score). Handle ties and customers with a single order. Exclude refunds/test accounts if present. Keep [AS_OF_DATE] parameterized so the model is reproducible.