Promotional Margin Leakage SQL Diagnostic

Role: Staff Commercial Analytics Specialist specializing in omnichannel pricing algorithms and gross margin leakage analysis.

Context

• Merchandise division: {{merchant_division}}
• Active loyalty program tier: {{loyalty_tier_program}}
• Campaign identifier: {{promotional_campaign_id}}
• SQL dialect environment: {{database_dialect}}
• Observed margin erosion level: {{margin_erosion_percentage}}
• Lead merchandiser contact: {{lead_merchandiser_name}}

Task

Draft an urgent diagnostic email to {{lead_merchandiser_name}} detailing margin erosion detected by the promotional audit SQL model, highlighting root causes in the query logic and recommending promotional parameter updates.

Method

1. Review the SQL transaction model tracking net realized price against item unit cost across {{merchant_division}}.
2. Detail the WHERE clause conditions and join structures that uncovered overlapping discounts during {{promotional_campaign_id}}.
3. Quantify the variance between planned promotional margins and the actual {{margin_erosion_percentage}} reduction.
4. Pinpoint the specific discount combination causing leakage (e.g., loyalty point redemption combined with auto-applied cart coupons in {{loyalty_tier_program}}).
5. Identify the top five SKUs or subcategories driving the highest aggregate dollar loss.
6. Provide a revised SQL CASE statement or validation constraint to simulate fixed promotional rules.
7. Detail short-term promotional kill-switch criteria and long-term pricing rule adjustments.
8. Establish the next review milestone to re-run the {{database_dialect}} audit script.

Constraints

• MUST cite precise margin figures and specific SKU category references.
• MUST NOT recommend deactivating entire promotion campaigns without offering granular exclusion criteria.
• MUST include a concise summary of the analytical SQL logic used to identify the leakage.
• Content must be formatted strictly for an executive retail merchandising audience.

Output format

• Subject Line: Urgent diagnostic tag, campaign ID, and margin impact indicator
• Incident Summary: Immediate bottom-line financial exposure and margin erosion rate
• SQL Detection Logic: Clear explanation of how the pricing model identified the leak
• Top Affected Product Categories: Structured breakdown of vulnerable merchandise
• Corrective Action Plan: Prioritized table of immediate discount rule fixes and timeline

Self-review

• Verify that the margin erosion percentage matches {{margin_erosion_percentage}}.
• Confirm that the proposed SQL validation logic correctly handles {{loyalty_tier_program}} rules.
• Ensure tone is objective, urgent, and focused on commercial risk remediation.