Email Analytics dbt Project

This dbt project transforms Gmail data from Airbyte into analytics-ready tables for email insights.

Setup Instructions

1. Create Python Virtual Environment

# Create virtual environment
python3 -m venv venv

# Activate virtual environment (macOS/Linux)
source venv/bin/activate

# Activate virtual environment (Windows)
# venv\Scripts\activate

# Install dbt
pip install -r requirements.txt

2. Configure dbt Profile

The profiles.yml file is already configured for BigQuery with:

• Project: nao-production
• Development dataset: dev
• Location: EU
• Authentication: OAuth

Make sure you're authenticated with Google Cloud:

gcloud auth application-default login

3. Test Connection

dbt debug

4. Run the Project

# Install dependencies (if any)
dbt deps

# Run all models
dbt run

# Run tests
dbt test

# Generate documentation
dbt docs generate
dbt docs serve

Project Structure

models/
├── staging/
│   ├── _sources.yml          # Source definitions
│   └── stg_messages.sql      # Staging model for messages
└── marts/
    ├── schema.yml            # Model documentation and tests
    └── fct_messages.sql      # Fact table for email analytics

Models

Staging Layer

• stg_messages: Cleaned and standardized message data from Gmail sources

Marts Layer

• fct_messages: Analytics-ready fact table with:
  • Message metadata (ID, thread, timestamp)
  • Sender/recipient information
  • Time-based dimensions (hour, day type, time category)
  • Message characteristics (size, direction, snippet length)

Analytics Use Cases

The fct_messages table enables analysis of:

• Email volume trends over time
• Communication patterns by time of day/week
• Sender/recipient analysis
• Message size and content analysis
• Work-life balance insights (business hours vs off-hours)

Development Workflow

1. Make changes to models
2. Run dbt run --select model_name to test specific models
3. Run dbt test to validate data quality
4. Commit changes to version control