A project by Demo on Experizon.
Executive Summary Build an end-to-end data pipeline that ingests multi-channel marketing data (web sessions, email, social, ads), segments customers using RFM analysis (Recency, Frequency, Monetary value), and generates attribution reports showing which marketing channel drives the most conversions. The output will be a live dashboard and SQL models ready for the marketing team to action insights within 3 weeks. Business Problem Our marketing team is currently spending 20+ hours per week manually pulling data from Google Analytics, Mailchimp, Facebook Ads, and our internal CRM, then Excel-ifying it into segmentation spreadsheets. This causes: Decision lag: Insights are 1 week old by the time they are actionable Data silos: Email team uses different KPIs than the ads team Wasted budget: We cannot quickly identify which campaigns convert best or which customer segments are most valuable Business Goal: Enable the marketing team to make daily, data-driven decisions on budget allocation, audience targeting, and message personalization within 90 days of launch. Requirements Deliverables dbt Project (GitHub repo) Staging models (sessions, email, ads, crm) Intermediate models (customer_rfm, attribution_summary) dbt tests and documentation SQL Models and Views customer_rfm_segments (RFM tiers) attribution_summary (channel to conversions) daily_segment_metrics (trend tracking) BI Dashboard 4–5 tabs with interactive drill-downs Scheduled refresh every night at 6 AM Documentation dbt docs site Runbook: Operating the Pipeline Data dictionary (metric definitions) One-page Getting Started for the marketing team Sample Data Synthetic enriched data (200K transactions with channel attribution) Load script for local testing
Marketing plan document including: - Target audience analysis - Channel strategy (social, content, paid) - 3-month campaign roadmap - Budget recommendations - Success metrics