A Multi-Layered SQL Analytics Pipeline built to clean messy marketing data and output unified dashboard-ready KPIs.

Executive Summary

This project demonstrates how I architect data engineering pipelines to transform messy, real-world marketing event logs into a structured data warehouse. The pipeline simulates raw exports from Meta Ads, Google Ads, GA4, and an e-commerce backend database, organizing them using a modular, multi-layered analytics engineering framework.

The goal is to turn raw, inconsistent data inputs into an enterprise-grade reporting layer ready for executive BI dashboards. This page provides a clear overview of the data architecture, transformation rules, and pipeline integration tests.

Skills Demonstrated

• Multi-Layer Data Modeling: Staging, Intermediate, and Mart warehousing design patterns.

• Data Cleansing & Normalization: Advanced type casting (CAST), text standardization (LOWER, TRIM), and fault-tolerant string handling (COALESCE, NULLIF).

• Advanced Deduplication: Isolating data pings and transaction double-reporting using window functions (ROW_NUMBER() OVER (PARTITION BY...)).

• Resilient Relational Joins: Unifying disjointed data silos via multi-key FULL OUTER JOIN tracking matrices to safeguard against data drops.

• Data Quality Frameworks: Architecting diagnostic query layers to automatically check data pipeline integrity and parameter synchronization.

• Product Analytics Logic: Implementing cohort retention and conversion funnel analysis logic.

Project Overview

This project unifies disjointed marketing spend, front-end web traffic, and bottom-of-funnel transaction logs into a single, cohesive analytics asset.

The source datasets contain classic real-world anomalies—such as text-stored financial numbers, missing UTM parameters, duplicate records, and casing mismatches. The pipeline handles these issues programmatically through a modular SQL structure inside the codebase.

• GitHub: [Full SQL Architecture & Code Scripts]

• Dashboard (Optional): [Link to BI View]

Problem ➔ Approach ➔ Results

The Problem

Marketing performance data arrives highly fragmented across ad networks and analytics trackers. tracking URLs often contain missing campaign parameters, transactions suffer from network double-pings, and data types are often misaligned. This structural friction makes high-stakes metrics like Cost Per Acquisition (CPA) and Return on Ad Spend (ROAS) deeply unreliable for business stakeholders.

The Approach

Instead of relying on single, long scripts, I implemented a modular, dbt-inspired three-layer warehouse architecture inside Sql_2/:

1. Staging Layer (/staging): Standardizes data types, fixes text formatting, and uses defensive window functions to cleanly deduplicate transaction records.

2. Intermediate Layer (/intermediate): Integrates data streams using robust FULL OUTER JOIN logic and multi-key wrappers to maintain full reporting transparency during tracking drops.

3. Marts Layer (/marts): Computes core executive KPIs (Net Profit, Conversion Rates, CPA, and ROAS) with built-in zero-division protection to ensure dashboard stability.

4. Analysis & Testing Layer (/analysis): Builds deep-dive funnel/cohort metrics and runs an automated pipeline bug checker to audit join integrity and variable linkage.

The Results

• 100% Data Integrity: Duplicate transaction logs are automatically intercepted and eliminated.

• Zero-Crash Visualizations: Divide-by-zero errors are mathematically handled, preventing dashboard crashes on zero-activity days.

• Resilient Tracking: Inconsistent marketing campaign names are synchronized using robust fallback logic.

• Production-Ready Deliverables: A final, pre-aggregated KPI table is generated, optimized for lightning-fast BI dashboard query speeds.

Deliverables

• Modular Data Warehouse Pipeline: Production SQL models organized across /staging, /intermediate, and /marts layers.

• Deep-Dive Analytics Modules: Behavioral cohort retention tables and conversion funnel scripts.

• Pipeline Bug Checker & Trust Layer: Automated integration query script to audit system sync health.

• Data Model Documentation: Enterprise-style README mapping data lineage and structural workflows.

Full SQL Scripts (Github)