This repository contains a BigQuery SQL query powering a Looker Studio dashboard that visualizes email campaign performance across countries and over time.
π View dashboard in Looker Studio
The dashboard includes:
usersβ number of unique email subscribers per countryemail sentβ total emails sent per countryusers rankβ country rank by subscriber countsend rankβ country rank by sent emails
- Daily trend of
sent,opens, andclicksin line chart - Weekly engagement bar chart segmented by
sent,opens, andclicks
- 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)
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'
day_of_week_name= CASE WEEKDAY(date) β MonβSunday_of_week_num= WEEKDAY(date) β for sorting in bar chartmonth= CASE EXTRACT(MONTH FROM date) β JanβDecyear= EXTRACT(YEAR FROM date)
- country
- email verified
- unsubscribed
- year
- month
- 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 |
- Google BigQuery β for scalable SQL querying and data transformation
- Looker Studio β for interactive dashboard design and visualization
email_engagement_dashboard.sqlβ main query powering the dashboard
Created by Nataliia
Supporting clear decisions through data from HR systems to SQL dashboards.
Driven by analytics, data quality, and collaborative growth.
