Skip to main content
This page contains SQL query examples to help you get started with your Email Meter data in BigQuery. You can use these as a starting point and adapt them to your specific needs.
These examples use your-project.your-dataset.your-view as a placeholder. Replace this with the actual path to your Email Meter view, which your Business Intelligence Consultant will provide.

​
Before you start

There are a few fields you’ll see in most queries:
  • main_timestamp is the primary timestamp for when emails were sent or received. You’ll use this for date filtering and grouping.
  • action_type (Google) or directionality (Microsoft 365) indicates whether an email was Sent or Received.
  • is_excluded_message indicates whether an email is an automated notification, calendar invite, or other non-actionable message. You can filter these out by adding is_excluded_message = FALSE to your queries, but note that this field may not be available in all datasets.

​
Email volume analysis

These queries help you understand how much email your team handles and when activity peaks.

​
Daily email volume

A simple starting point to see sent and received email counts by day. 
SELECT
  DATE(main_timestamp) AS date,
  action_type,
  COUNT(*) AS email_count
FROM `your-project.your-dataset.your-view`
WHERE
  main_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
GROUP BY date, action_type
ORDER BY date DESC
Useful for comparing workload across departments over time. 
SELECT
  FORMAT_TIMESTAMP('%Y-%m', main_timestamp) AS month,
  department,
  action_type,
  COUNT(*) AS email_count
FROM `your-project.your-dataset.your-view`,
  UNNEST(department) AS department
WHERE
  main_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
GROUP BY month, department, action_type
ORDER BY month DESC, department

​
Peak hours analysis

Shows when your team sends the most email. Useful for understanding work patterns or identifying after-hours activity. 
SELECT
  EXTRACT(HOUR FROM main_timestamp) AS hour_of_day,
  EXTRACT(DAYOFWEEK FROM main_timestamp) AS day_of_week,
  COUNT(*) AS email_count
FROM `your-project.your-dataset.your-view`
WHERE
  main_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
  AND action_type = 'Sent'
GROUP BY hour_of_day, day_of_week
ORDER BY day_of_week, hour_of_day

​
Response time analysis

Response time data lives in the reply_data field, which is a repeated record. You’ll need to use UNNEST to access it.

​
Average response time by user

Shows how quickly each team member responds to incoming emails. The reply_time field is in seconds, so we divide by 3600 to get hours. 
SELECT
  gmail_primary_address AS user_email,
  COUNT(*) AS emails_replied,
  ROUND(AVG(reply_data.reply_time) / 3600, 2) AS avg_response_hours,
  ROUND(AVG(reply_data.cwt_time) / 3600, 2) AS avg_customer_wait_hours
FROM `your-project.your-dataset.your-view`,
  UNNEST(reply_data) AS reply_data
WHERE
  main_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
  AND action_type = 'Received'
  AND is_replied = TRUE
GROUP BY user_email
ORDER BY avg_response_hours ASC
reply_time uses your configured business hours, so weekends and off-hours don’t count against response time. If you want the actual calendar time, use cwt_time instead.

​
Response time distribution

Instead of just averages, this shows how response times are distributed across predefined buckets (e.g., under 1 hour, 1-4 hours, etc.). 
SELECT
  reply_data.reply_time_bucket,
  reply_data.reply_time_bucket_order,
  COUNT(*) AS email_count,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) AS percentage
FROM `your-project.your-dataset.your-view`,
  UNNEST(reply_data) AS reply_data
WHERE
  main_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
  AND is_replied = TRUE
GROUP BY reply_data.reply_time_bucket, reply_data.reply_time_bucket_order
ORDER BY reply_data.reply_time_bucket_order

​
Emails pending response

Shows received emails that haven’t been replied to yet, grouped by the date they were received. Useful for identifying backlogs. 
SELECT
  DATE(main_timestamp) AS received_date,
  COUNT(*) AS pending_replies,
  ROUND(AVG(email_age) / 3600, 2) AS avg_age_hours
FROM `your-project.your-dataset.your-view`
WHERE
  action_type = 'Received'
  AND is_replied = FALSE
  AND is_first_message = TRUE
GROUP BY received_date
ORDER BY received_date DESC

​
SLA compliance

If you have SLA targets configured in Email Meter, you can use the reply_sla_bucket field to track compliance.

​
SLA compliance rate

Shows how many emails fall into each SLA bucket (e.g., “Within SLA”, “Breached”) by month. 
SELECT
  FORMAT_TIMESTAMP('%Y-%m', main_timestamp) AS month,
  reply_data.reply_sla_bucket AS sla_bucket,
  COUNT(*) AS email_count
FROM `your-project.your-dataset.your-view`,
  UNNEST(reply_data) AS reply_data
WHERE
  main_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
  AND is_replied = TRUE
GROUP BY month, sla_bucket
ORDER BY month DESC, sla_bucket

​
SLA compliance by team

Compares SLA performance across departments, showing both counts and percentages. 
SELECT
  department,
  reply_data.reply_sla_bucket AS sla_bucket,
  COUNT(*) AS email_count,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(PARTITION BY department), 2) AS percentage
FROM `your-project.your-dataset.your-view`,
  UNNEST(reply_data) AS reply_data,
  UNNEST(department) AS department
WHERE
  main_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
  AND is_replied = TRUE
GROUP BY department, sla_bucket
ORDER BY department, sla_bucket

​
Internal vs external communication

The is_intradomain field indicates whether an email was sent within your organization or to/from external addresses.

​
Communication breakdown

Shows the split between internal and external emails over time. 
SELECT
  DATE(main_timestamp) AS date,
  is_intradomain AS communication_type,
  action_type,
  COUNT(*) AS email_count
FROM `your-project.your-dataset.your-view`
WHERE
  main_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
GROUP BY date, communication_type, action_type
ORDER BY date DESC

​
Top external domains

Shows which external domains your team communicates with the most. Useful for identifying key clients or partners. 
SELECT
  from_domain,
  COUNT(*) AS email_count,
  COUNT(DISTINCT DATE(main_timestamp)) AS active_days
FROM `your-project.your-dataset.your-view`
WHERE
  main_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
  AND action_type = 'Received'
  AND is_intradomain = 'External'
GROUP BY from_domain
ORDER BY email_count DESC
LIMIT 20

​
Client group analysis

If you’ve configured contact groups in Email Meter, you can analyze email activity by client segment.

​
Email volume by client group

Shows how much email flows to and from each client group. 
SELECT
  client_group_array.client_group,
  action_type,
  COUNT(*) AS email_count,
  COUNT(DISTINCT DATE(main_timestamp)) AS active_days
FROM `your-project.your-dataset.your-view`,
  UNNEST(client_group_array) AS client_group_array
WHERE
  main_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
GROUP BY client_group_array.client_group, action_type
ORDER BY email_count DESC

​
Response time by client group

Useful for checking if high-priority clients are getting faster responses. 
SELECT
  client_group_array.client_group,
  COUNT(*) AS emails_replied,
  ROUND(AVG(reply_data.reply_time) / 3600, 2) AS avg_response_hours,
  ROUND(AVG(reply_data.cwt_time) / 3600, 2) AS avg_customer_wait_hours
FROM `your-project.your-dataset.your-view`,
  UNNEST(reply_data) AS reply_data,
  UNNEST(client_group_array) AS client_group_array
WHERE
  main_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
  AND is_replied = TRUE
GROUP BY client_group_array.client_group
ORDER BY avg_response_hours ASC

​
Thread analysis

These queries help you understand conversation patterns and complexity.

​
Average thread length

Shows how many back-and-forth emails occur in typical conversations. Longer threads might indicate complex issues or inefficient communication. 
SELECT
  DATE(main_timestamp) AS date,
  ROUND(AVG(thread_length), 2) AS avg_thread_length,
  MAX(thread_length) AS max_thread_length
FROM `your-project.your-dataset.your-view`
WHERE
  main_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
  AND is_first_message = TRUE
GROUP BY date
ORDER BY date DESC

​
New conversations started

Tracks how many new email threads are initiated each day, split by sent vs received. 
SELECT
  DATE(main_timestamp) AS date,
  action_type,
  COUNT(*) AS new_conversations
FROM `your-project.your-dataset.your-view`
WHERE
  main_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
  AND is_first_message = TRUE
GROUP BY date, action_type
ORDER BY date DESC

​
User productivity

These queries give you a per-user breakdown of email activity.

​
Emails sent and received per user

A basic overview of each user’s email volume. 
SELECT
  gmail_primary_address AS user_email,
  SUM(CASE WHEN action_type = 'Sent' THEN 1 ELSE 0 END) AS emails_sent,
  SUM(CASE WHEN action_type = 'Received' THEN 1 ELSE 0 END) AS emails_received,
  COUNT(*) AS total_emails
FROM `your-project.your-dataset.your-view`
WHERE
  main_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
GROUP BY user_email
ORDER BY total_emails DESC

​
Reply rate by user

Shows what percentage of received emails each user actually replies to. 
SELECT
  gmail_primary_address AS user_email,
  COUNT(*) AS emails_received,
  SUM(CASE WHEN is_replied = TRUE THEN 1 ELSE 0 END) AS emails_replied,
  ROUND(SUM(CASE WHEN is_replied = TRUE THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS reply_rate
FROM `your-project.your-dataset.your-view`
WHERE
  main_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
  AND action_type = 'Received'
GROUP BY user_email
ORDER BY reply_rate DESC

​
Business hours analysis

If you’ve configured business hours in Email Meter, you can use the is_inside_bhs field to compare activity during and outside work hours.

​
Activity during vs outside business hours

SELECT
  DATE(main_timestamp) AS date,
  CASE WHEN is_inside_bhs = TRUE THEN 'During business hours' ELSE 'Outside business hours' END AS time_period,
  action_type,
  COUNT(*) AS email_count
FROM `your-project.your-dataset.your-view`
WHERE
  main_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
GROUP BY date, time_period, action_type
ORDER BY date DESC

​
Advanced examples

These queries combine multiple concepts and show some of BigQuery’s more powerful analytical features.

​
Week-over-week comparison

Compares this week’s email volume to last week, broken down by day of week. Useful for spotting trends or anomalies. 
WITH current_week AS (
  SELECT
    FORMAT_TIMESTAMP('%A', main_timestamp) AS day_name,
    EXTRACT(DAYOFWEEK FROM main_timestamp) AS day_order,
    COUNT(*) AS emails
  FROM `your-project.your-dataset.your-view`
  WHERE
    main_timestamp >= TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), WEEK)
    GROUP BY day_name, day_order
),
previous_week AS (
  SELECT
    FORMAT_TIMESTAMP('%A', main_timestamp) AS day_name,
    EXTRACT(DAYOFWEEK FROM main_timestamp) AS day_order,
    COUNT(*) AS emails
  FROM `your-project.your-dataset.your-view`
  WHERE
    main_timestamp >= TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), WEEK), INTERVAL 7 DAY)
    AND main_timestamp < TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), WEEK)
    GROUP BY day_name, day_order
)
SELECT
  COALESCE(c.day_name, p.day_name) AS day_name,
  COALESCE(c.day_order, p.day_order) AS day_order,
  COALESCE(c.emails, 0) AS this_week,
  COALESCE(p.emails, 0) AS last_week,
  COALESCE(c.emails, 0) - COALESCE(p.emails, 0) AS difference,
  CASE
    WHEN p.emails > 0 THEN ROUND((c.emails - p.emails) * 100.0 / p.emails, 1)
    ELSE NULL
  END AS percent_change
FROM current_week c
FULL OUTER JOIN previous_week p ON c.day_order = p.day_order
ORDER BY day_order

​
Response time percentiles

Averages can be misleading when you have outliers. This query shows P50 (median), P90, and P99 response times, giving you a better picture of the actual distribution. 
SELECT
  department,
  COUNT(*) AS emails_replied,
  ROUND(APPROX_QUANTILES(reply_data.reply_time, 100)[OFFSET(50)] / 3600, 2) AS p50_hours,
  ROUND(APPROX_QUANTILES(reply_data.reply_time, 100)[OFFSET(90)] / 3600, 2) AS p90_hours,
  ROUND(APPROX_QUANTILES(reply_data.reply_time, 100)[OFFSET(99)] / 3600, 2) AS p99_hours,
  ROUND(AVG(reply_data.reply_time) / 3600, 2) AS avg_hours
FROM `your-project.your-dataset.your-view`,
  UNNEST(reply_data) AS reply_data,
  UNNEST(department) AS department
WHERE
  main_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
  AND is_replied = TRUE
GROUP BY department
ORDER BY p50_hours ASC
P50 (median) is often more useful than average for response times, since a few very slow responses won’t skew the number. P90 tells you “90% of emails were replied to within X hours.”

​
Shared mailbox delegate performance

If you use shared or delegated mailboxes, this query shows which team members are handling the most emails and how quickly they respond. The from_delegated field captures who actually sent the email from a shared mailbox.
This query requires the from_delegated field, which may not be available in all datasets. Check with your Business Intelligence Consultant if you need delegation analytics.
SELECT
  from_delegated AS delegate_email,
  gmail_primary_address AS shared_mailbox,
  COUNT(*) AS emails_sent,
  COUNT(CASE WHEN is_reply = TRUE THEN 1 END) AS replies_sent,
  ROUND(AVG(CASE WHEN is_reply = TRUE THEN reply_data.reply_time END) / 3600, 2) AS avg_response_hours
FROM `your-project.your-dataset.your-view`
LEFT JOIN UNNEST(reply_data) AS reply_data
WHERE
  main_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
  AND action_type = 'Sent'
  AND from_delegated IS NOT NULL
GROUP BY delegate_email, shared_mailbox
ORDER BY emails_sent DESC

​
First response vs follow-up response times

Not all replies are equal. This query separates first responses (the initial reply to a customer) from follow-up responses within the same thread. First response time is often the metric that matters most for customer experience. 
SELECT
  CASE
    WHEN reply_data.reply_index = 1 THEN 'First response'
    ELSE 'Follow-up response'
  END AS response_type,
  COUNT(*) AS total_replies,
  ROUND(AVG(reply_data.reply_time) / 3600, 2) AS avg_response_hours,
  ROUND(APPROX_QUANTILES(reply_data.reply_time, 100)[OFFSET(50)] / 3600, 2) AS median_response_hours
FROM `your-project.your-dataset.your-view`,
  UNNEST(reply_data) AS reply_data
WHERE
  main_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
  AND is_replied = TRUE
GROUP BY response_type
ORDER BY response_type

​
Filtering techniques

These are common WHERE clause patterns you can combine with any of the queries above.

​
Date range filtering

-- Last 30 days
WHERE main_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)

-- Specific month
WHERE main_timestamp >= '2025-12-01' AND main_timestamp < '2026-01-01'

-- Year to date
WHERE main_timestamp >= TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), YEAR)

​
User filtering

-- Filter by specific user
WHERE gmail_primary_address = 'user@company.com'

-- Filter by user ID (recommended for accuracy)
WHERE gmail_user_id = '102114834148206491213'

-- Filter by multiple users
WHERE gmail_primary_address IN ('user1@company.com', 'user2@company.com')

​
Domain filtering

-- Filter by sender domain
WHERE from_domain = 'important-client.com'

-- Filter by multiple domains
WHERE from_domain IN ('client1.com', 'client2.com', 'partner.com')

-- Exclude specific domains
WHERE from_domain NOT IN ('noreply.com', 'automated-service.com')

​
Recipient filtering

The recipients field is a repeated record, so you’ll need to use UNNEST to filter by recipient. 
SELECT *
FROM `your-project.your-dataset.your-view`,
  UNNEST(recipients) AS recipient
WHERE
  recipient.address = 'important@client.com'
  AND action_type = 'Sent'

​
Best practices

If your dataset includes the is_excluded_message field, you can add is_excluded_message = FALSE to your queries to filter out automated notifications, calendar invites, and other non-actionable messages. Note that this field may not be available in all datasets.
Email addresses can change over time. For consistent tracking, use gmail_user_id (Google Workspace) or outlook_user_id (Microsoft 365) when filtering or grouping by user.
The reply_data.reply_time field automatically accounts for your configured business hours, giving you a more accurate picture of actual response times during working hours. Use reply_data.cwt_time if you want the total calendar time.
BigQuery charges based on the amount of data scanned. Always include date filters to limit the data range, and select only the columns you need rather than using SELECT *.
Fields like reply_data, recipients, addresses, and client_group_array are repeated records. Use the UNNEST function to flatten these arrays for analysis.

​
Resources

These pages contain the full list of fields available in your Email Meter view.

Google Schema

Fields available for Google Workspace

Microsoft 365 Schema

Fields available for Microsoft 365

​
Need help?

These examples are meant to be a starting point. Your Business Intelligence Consultant can help you build custom queries tailored to your specific reporting needs, or troubleshoot any issues you run into.