Project 1: SQL on Marketing Data

============================================
-- 2. Rolling 7-day revenue
-- Demonstrates window functions for time-series smoothing.
-- ============================================

SELECT
    date(transaction_time) AS date,
    SUM(revenue) AS daily_revenue,
    SUM(SUM(revenue)) OVER (
        ORDER BY date(transaction_time)
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7d_revenue
FROM cleaned_transactions
GROUP BY date
ORDER BY date;

Each dataset required targeted cleaning to fix inconsistent formats, duplicated rows, and text‑stored numeric values. The cleaning pipeline uses CTEs to keep the logic readable and modular.

Ad Spend

• Normalized campaign names (TRIM, LOWER, REPLACE)

• Converted cost, impressions, and clicks from text to numeric

• Standardized dates using DATE()

• Removed rows with missing or invalid spend values

Web Sessions

• Cleaned UTM parameters (TRIM, COALESCE)

• Fixed inconsistent casing in campaign names

• Converted session timestamps to dates

• Removed duplicate sessions using ROW_NUMBER()

Transactions

• Deduplicated transactions using ROW_NUMBER() on transaction_id

• Converted revenue from text to numeric

• Normalized date formats

• Linked transactions to sessions via session_id

Outcome:  

A clean, consistent set of tables ready for modeling and joining.

Full cleaning SQL is available in the GitHub repository.

The data model unifies spend, sessions, and revenue into a single structure for daily campaign performance reporting.

Keys & Relationships

• Spend keyed by date + campaign_name

• Sessions keyed by date + utm_campaign

• Transactions keyed by transaction_id, linked to sessions via session_id

Join Logic

• Spend → Sessions: matched on normalized campaign name + date

• Sessions → Transactions: matched on session_id

• Final model aggregates cost, sessions, clicks, impressions, and revenue at the daily campaign level

Purpose

This model creates a reliable single source of truth for CPC, CPM, ROAS, CVR, and daily trend analysis.

Full SQL implementation is available in the GitHub repository.