Digital Marketing Analytics & Visualizations Portfolio

PROJECT DETAILS

THE BUSINESS CHALLENGE

A for-profit higher education company with multiple brands acquired a new brand that had no analytics infrastructure and incomplete SQL opportunity data in their Microsoft SQL database.

Initial Challenges:

• No existing analytics or reporting for the newly acquired brand
• Incomplete and inconsistent opportunity data in SQL database requiring correction
• Marketing team had no visibility into which traffic sources generated applications vs. just traffic
• No standardized KPIs or measurement framework established

Stakeholder Need:

• Collaborated with stakeholders to define and align on key performance indicators (KPIs) and dashboard components
• Weekly visibility into which traffic sources, content sections, and devices generate qualified leads and applications
• Unified view combining web analytics with opportunity/application data for enrollment funnel analysis

TECHNICAL APPROACH

Data Integration

• Google Analytics (Universal Analytics → GA4): Traffic metrics (pageviews, sessions, users, traffic source, device category, landing pages)
  • Initially connected to Universal Analytics data
  • Migrated to GA4 via BigQuery export several years into project lifecycle
• Microsoft SQL Data Warehouse: Opportunity and application data
  • Corrected incomplete and inconsistent opportunity data in database
  • Standardized lead/application tracking across multiple brand properties
• Blending Method: Tableau relationships on common keys (page URL, date, device type) using LEFT JOIN to preserve all traffic data

Technical Solutions

• Data Correction: Cleaned and standardized incomplete opportunity data in Microsoft SQL database for accurate reporting
• Calculated Fields for Standardization:
  • Device: Normalized device categorization between Google Analytics and SQL data sources
  • Content Groupings: Created consistent content taxonomy across multiple brand websites
  • Traffic Source: Standardized traffic source classifications for unified reporting
• Attribution Model: Built custom "last non-direct click" attribution through Tableau calculated fields for accurate channel credit
• Platform Migration: Successfully transitioned from Universal Analytics to GA4 via BigQuery without reporting disruption
• Performance Optimization: Implemented incremental refreshes and data extracts, reducing refresh time from 45+ minutes to under 5 minutes
• Data Validation: Automated checks flag discrepancies between Tableau and SQL warehouse within 2% tolerance
• BigQuery Cost Control: Partitioned tables by date, used query previews, optimized queries for cost efficiency

Automation

• Automated daily refresh via Tableau Server
• Email alerts for failed refreshes or validation issues
• Rolling data retention window for historical analysis
• Weekly review and analysis by marketing stakeholders

INTERACTIVE FEATURES

Filtering Capabilities

• Date Range: WoW, MoM, or custom periods for campaign analysis
• Traffic Source: Isolate by Default Channel Grouping (Organic, Paid Search, Social, Email, Referral, Display, Direct)
• Device Type: Desktop, Mobile, Tablet independently or combined
• Content Section: Focus on specific website areas (Blog, Product Pages, Resources)

Drill-Down & Navigation

• Hierarchical Structure: Click website sections to expand to individual page performance
• Page-Level Detail: Identify top performers and underperformers by specific URL
• Time-Series Trends: View performance trends over time for any filtered selection

Metrics & Visualization

• Toggle Views: Switch between absolute metrics (leads, pageviews) and conversion rates (LCR)
• Comparison Mode: Current period vs. previous period with variance calculations
• Goal Tracking: Visual indicators show performance against monthly targets
• Color-Coded Performance: Green (above average), yellow (average), red (below average) LCR indicators

ADVANCED TABLEAU TECHNIQUES

Level of Detail (LOD) Expressions

• Solved granularity mismatch between data sources
• Properly aggregated leads while enabling filtering by traffic source and device, with drill-down capability from content grouping to individual page level

Parameter-Driven Flexibility

• Switch between attribution windows (1-day, 7-day, 30-day lookback) without dashboard rebuild
• Custom attribution logic overrides GA4 defaults for stakeholder-specific models

Performance Optimization

• Created indexed views on commonly queried fields (date, page_url, lead_id)
• Reduced SQL query time from 12 seconds to 2 seconds
• Dashboard load time: < 5 seconds on Tableau Server

DASHBOARD COMPONENTS

Primary Visualization: Website section performance table with pageviews, leads, LCR; expandable rows for page-level detail

Supporting Visuals:

• Traffic source breakdown (horizontal bar chart)
• Device performance comparison (traffic vs. leads)
• Time-series trend line with 7-day moving average
• KPI cards: total pageviews, leads, LCR, WoW/MoM changes

SKILLS ENHANCED

• Multi-source data blending in Tableau
• Data cleaning and database correction (Microsoft SQL)
• Google Analytics platform migration (Universal Analytics → GA4)
• BigQuery optimization for cost and performance
• Marketing attribution methodology
• Stakeholder consultation and KPI definition
• Calculated field creation for data standardization
• Automated data validation and monitoring

Last Updated: November 2025 | Dashboard Status: Active - Weekly Usage for Ongoing Analytics

PROJECT DETAILS

THE BUSINESS CHALLENGE

The for-profit higher education company's newly acquired brand had no analytics infrastructure and incomplete SQL opportunity data requiring correction.

Key Questions:

• How are lead and application submissions trending across content areas and traffic sources?
• What is the duplicate lead rate, and where do duplicates originate?
• Which combinations of traffic source and content produce the best LCR and App CVR?
• Where are patterns and anomalies in the conversion funnel?

Stakeholder Need:

• Self-service analytics for exploring multiple KPIs without custom report requests
• Ability to slice data by page, content grouping, and traffic source
• Daily updated dashboard for real-time monitoring
• Rapid pattern and anomaly identification

TECHNICAL APPROACH

Data Source & Correction

• Microsoft SQL Data Warehouse: Lead and application data for newly acquired brand
• Data Cleaning: Corrected incomplete and inconsistent opportunity data in SQL database before building dashboard
• Data Elements: Leads, duplicate tracking, applications, timestamps, pages, content sections, traffic sources

SQL Query Optimization

• Built optimized queries to minimize database load
• Implemented incremental data extraction for new/changed records only
• Created indexed views on commonly queried fields
• Reduced query execution time through optimized joins

Calculated Fields (Heavily Used)

• Traffic Source: Standardized channel classifications across data
• Content Groupings: Created consistent content taxonomy for website
• Metric Calculations:
  • LCR (Lead Conversion Rate)
  • App CVR (Application Conversion Rate)
  • Duplicate rates and identification logic
  • Period-over-period comparisons and variance calculations

Parameters for Dynamic Flexibility

• Metric Selector Parameter: Switch entire dashboard between Leads, Duplicates, Applications, LCR, App CVR
• Dimension Selector Parameter: Toggle analysis axis between Page, Content Grouping, Traffic Source
• Date Range Parameter: Flexible time period selection
• Combined Power: Any metric by any dimension in seconds without rebuilding dashboard

Dashboard Architecture

• Multi-layer design with summary and detailed views
• Cascading filters for progressive analysis focus
• Parameter-driven dynamic content updates
• Optimized for both daily monitoring and deep-dive analysis

Automation

• Automated daily refresh via Tableau Server during off-peak hours
• Email alerts for refresh failures or data quality issues
• Rolling data retention for historical trend analysis

INTERACTIVE FEATURES

Parameter-Driven Selections

Metric Selector (Switch Between):

• Leads (total submissions)
• Duplicates (duplicate lead count/rate)
• Applications (enrollment applications)
• LCR (Lead Conversion Rate)
• App CVR (Application Conversion Rate)

Dimension Selector (Analyze By):

• Page (individual URL performance)
• Content Grouping (website section analysis)
• Traffic Source (channel performance)

Flexibility Examples:

• "Show Applications by Traffic Source"
• "Show Duplicates by Content Grouping"
• "Show LCR by Page"

Additional Filtering

• Date Range: Custom periods, WoW, MoM comparisons
• Device Type: Desktop, Mobile, Tablet filter
• Drill-Down: Content Grouping → Individual Page navigation

Analysis Capabilities

• Trend Analysis: View selected metric over time for any dimension
• Cross-Dimensional: Filter by date range while viewing any metric/dimension combination
• Top Performers: Automatic display of top 5-10 items in selected dimension
• Period Comparison: Current vs. previous period with variance indicators

DASHBOARD COMPONENTS

Summary View

• KPI Cards: Large displays showing selected metric with period comparison
• Top Performers List: Automatically updates based on dimension selection
• Trend Indicators: Visual flags showing performance direction (up/down/flat)

Detailed Analysis View

• Primary Visualization: Bar chart or table showing selected metric by selected dimension
  • Dynamically updates based on parameter selections
  • Sortable by metric value
  • Color-coded performance indicators
• Time-Series Chart: Trend line of selected metric over time
• Distribution Analysis: Performance comparison across dimension categories

ADVANCED TABLEAU TECHNIQUES

Extensive Calculated Field Usage

• Standardization: Traffic Source and Content Grouping fields normalize data across sources
• Metrics: LCR, App CVR, duplicate rates, period-over-period variance
• Conditional Logic: IF/THEN statements for data consistency and dynamic categorization
• Date Functions: Custom calculations for WoW, MoM comparisons

Parameter-Driven Dynamic Dashboard

• Metric Selector: Single control changes entire dashboard to show selected metric
• Dimension Selector: Changes analysis axis between Page, Content Grouping, or Traffic Source
• Single Dashboard, Multiple Uses: Eliminates need for separate dashboards per metric/dimension combination

Level of Detail (LOD) Expressions

• Solved complex aggregation challenges when switching between dimensions
• Enabled proper conversion rate calculations regardless of dimension selection
• Allowed performance comparison against overall averages

Performance Optimization

• Data extracts for faster load times
• Aggregated calculations to reduce on-demand computation
• Efficient filter design to minimize query overhead
• Dashboard load time: < 5 seconds; filter changes: < 2 seconds

PATTERN & ANOMALY IDENTIFICATION

Content Performance

• Content groupings that drive leads vs. applications
• Individual page performance within groupings
• Pages with high leads but low application conversion (quality issues)
• Content with traffic but no conversions

Traffic Source Analysis

• Sources driving volume vs. quality
• Source-specific LCR and App CVR
• Traffic sources with high duplicate rates
• Channel performance trends

Duplicate Lead Patterns

• Pages generating most duplicate submissions
• Traffic sources with higher duplicate rates
• Content groupings with confusing UX leading to resubmissions

Conversion Funnel Insights

• Lead-to-application conversion by dimension
• Funnel drop-off points
• High-performing traffic source + content combinations

Anomaly Detection

• Sudden application drops from specific content grouping
• Unexpected duplicate spikes from traffic sources
• LCR or App CVR deviations from norms
• Pages with abnormal performance patterns

USE CASES & BUSINESS IMPACT

Daily Monitoring

• Morning dashboard review for overnight performance
• Quick issue identification (duplicate spikes, conversion drops)
• Real-time lead and application volume visibility

Weekly Analysis

• Trend deep-dives using different metric/dimension combinations
• Optimization opportunity identification (low-converting pages, high-duplicate sources)
• Stakeholder reporting on key findings

Ad-Hoc Investigation

• Explore metric changes by switching dimensions via parameters
• Answer business questions without custom report requests
• Test performance driver hypotheses using parameter selections

SKILLS ENHANCED

• Advanced calculated field creation in Tableau (heavily used for metrics and dimensions)
• Parameter-driven dynamic dashboard design for self-service analytics
• Data cleaning and correction in Microsoft SQL database
• Complex metric calculations (LCR, App CVR, duplicate rates)
• Multi-dimensional data modeling for flexible analysis
• LOD (Level of Detail) expressions for complex aggregations
• SQL query optimization for dashboard performance
• Dashboard UX design enabling non-technical user analysis
• Stakeholder training on parameter-driven dashboard usage

Last Updated: November 2025 | Dashboard Status: Active - Daily Updates for On-Demand Analysis