January Campaign Metrics Quality Status

How do you handle missing or inconsistent data in your reporting?

Using the provided tables, write a SQL query for January 2025 that preserves every campaign, summarizes its available metrics, and assigns a data-quality status based on missing records, null values, invalid negative values, or inconsistent campaign names.

Output

1. One row per campaign, including campaigns with no January metrics
2. Columns: campaign_id, campaign_name, channel, total_spend, total_conversions, and data_quality_status
3. Order by campaign_id ascending

Output details

• Treat January as dates >= DATE '2025-01-01' and < DATE '2025-02-01'.
• Return totals as zero when no metrics or when the aggregate is otherwise NULL, while ignoring NULL values in sums.
• Include negative values in totals, but classify any negative spend or conversions as INVALID_VALUES.
• Apply status precedence: MISSING_METRICS, then MISSING_VALUES, then INVALID_VALUES, then INCONSISTENT_NAME, otherwise CLEAN.
• Classify campaign names as inconsistent when they differ, including when exactly one compared name is NULL.