Skip to content

Email engagement dashboard powered by BigQuery & Looker Studio. Visualizes campaign performance and conversion funnel.

Notifications You must be signed in to change notification settings

dizerfree/email-campaign-analytics-sql

Folders and files

NameName
Last commit message
Last commit date

Latest commit

Β 

History

7 Commits
Β 
Β 
Β 
Β 
Β 
Β 

Repository files navigation

Email Engagement Dashboard powered by BigQuery & Looker Studio

This repository contains a BigQuery SQL query powering a Looker Studio dashboard that visualizes email campaign performance across countries and over time.

Dashboard Preview

πŸ”— View dashboard in Looker Studio

πŸ“Š Dashboard Highlights

The dashboard includes:

πŸ“ Country-level breakdown

  • users β€” number of unique email subscribers per country
  • email sent β€” total emails sent per country
  • users rank β€” country rank by subscriber count
  • send rank β€” country rank by sent emails

πŸ“ˆ Time-based dynamics

  • Daily trend of sent, opens, and clicks in line chart
  • Weekly engagement bar chart segmented by sent, opens, and clicks

πŸ” Conversion funnel

  • Emails Sent β†’ Opens β†’ Clicks
  • Calculated rates:
    • open rate = COUNT_DISTINCT(open_id_message) / COUNT_DISTINCT(sent_id_message)
    • click rate = COUNT_DISTINCT(visit_id_message) / COUNT_DISTINCT(sent_id_message)

πŸ‘₯ User segmentation fields

  • email verified = CASE is_verified WHEN '1' THEN 'Yes' WHEN '0' THEN 'No'
  • unsubscribed = CASE is_unsubscribed WHEN '1' THEN 'Yes' WHEN '0' THEN 'No'

πŸ“… Date breakdown fields

  • day_of_week_name = CASE WEEKDAY(date) β†’ Mon–Sun
  • day_of_week_num = WEEKDAY(date) β€” for sorting in bar chart
  • month = CASE EXTRACT(MONTH FROM date) β†’ Jan–Dec
  • year = EXTRACT(YEAR FROM date)

🧩 Filters available

  • country
  • email verified
  • unsubscribed
  • year
  • month

🎨 UX-focused design

  • Dashboard is optimized for dark backgrounds with high-contrast, harmonious colors
  • Color palette selected for accessibility, clarity, and emotional tone
  • Bilingual labels and intuitive sorting enhance user experience

Color palette used:

Element / Metric Hex Code Usage Description
Sent (daily dynamics) #5A8FB3 Blue-gray β€” used in daily sent_msg line chart and weekly bars
Funnel accent #1D74BB Bright blue β€” main highlight in conversion funnel
Open (daily dynamics) #6AA785 Green β€” opens in daily line chart and weekly bars
Click (daily dynamics) #B98C7A Brown β€” clicks in daily line chart and weekly bars
Open/Click Rate #5C7A91 Gray β€” used for percentage indicators in table

Dark background layers:

Layer / Depth Hex Code Usage
Panel background #3F5668 Main chart containers
Section background #374856 Table and filter zones
Canvas base #2C2F38 Overall dashboard background
Deep background #1C1D1D Outer frame / page base

πŸ› οΈ Technologies Used

  • Google BigQuery β€” for scalable SQL querying and data transformation
  • Looker Studio β€” for interactive dashboard design and visualization

πŸ“ Files

  • email_engagement_dashboard.sql β€” main query powering the dashboard

✍️ Author

Created by Nataliia
Supporting clear decisions through data from HR systems to SQL dashboards.
Driven by analytics, data quality, and collaborative growth.

About

Email engagement dashboard powered by BigQuery & Looker Studio. Visualizes campaign performance and conversion funnel.

Topics

Resources

Stars

Watchers

Forks