Mail statistics

The mail statistics data is stored in InfluxDB with the following configuration of Buckets and Measurements. Each measurement contains Tags and Fields.

ℹ️ The details of these topics can be read in the official documentation.

Configuration

  • Bucket: mail_stats

  • Measurements:

    • mail_sent
    • mail_recipients
    • mail_read

There are three types of measurements under the mail_stats bucket. Each measurement has its own tags and fields, as described below.

mail_sent measurement

Tags

  • sender_domain: Refers to the email domain of the sender's email address. For example, the value of sender_domain in the case of 'abc@domain.com' is 'domain.com'.
  • source: There are two possible sources - API and SMTP. The API source indicates the mail originating from an API client, such as Secure Mail web client. The SMTP source indicates the mail originating from an SMTP client, such as Microsoft Outlook.
  • is_reply: A flag that indicates if the mail is a reply to a received mail.
  • is_forward: A flag that indicates if the mail is a forwarded mail.
  • has_attachments: Indicates if the mail contain any file attachments.

Fields

  • mail_id: Refers to the unique id of a mail.
  • parent_mail_id: Refers to the unique id of the parent mail. A received mail becomes the parent of a replied or a forward mail.
  • sender_email: Refers to the email address of the sender.
  • mail_size_bytes: The total size of the mail in bytes. The size is the total of the sizes of body and all the attachments.

mail_recipients measurement

Tags

  • receiver_domain: Refers to the email domain of the receiver's email address. For example, the value of receiver_domain in the case of 'abc@domain.com' is 'domain.com'.

Fields

  • mail_id: Refers to the unique id of a mail.
  • receiver_email: Refers to the email address of the receiver.

mail_read measurement

Tags

There are no tags under the mail_read measurement.

Fields

  • mail_id: Refers to the unique id of a mail.
  • sender_email: Refers to the email address of the sender.
  • receiver_email: Refers to the email address of the receiver.

Sample queries

Number of new mails sent in the last 30 days

Here, a forwarded mail is considered a new mail.

from(bucket: "mail_stats")
    |> range(start: -30d)
    |> filter(fn: (r) => r["_measurement"] == "mail_sent" and r["is_reply"] == "false")
    |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> group()
    |> count(column: "mail_id")

New mails within a specific time-interval

from(bucket: "mail_stats")
    |> range(start: 2024-10-30T08:00:00Z, stop: 2025-01-02T20:00:01Z)
    |> filter(fn: (r) => r["_measurement"] == "mail_sent" and r["is_reply"] == "false")
    |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> group()

All the users who have received mails in the last 30 days

from(bucket: "mail_stats")
    |> range(start: -30d)
    |> filter(fn: (r) => r["_measurement"] == "mail_recipients")
    |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> group()

All the read statuses in the last 30 days

Note that a mail can be read multiple times, and we log all the reads of a mail.

from(bucket: "mail_stats")
    |> range(start: -30d)
    |> filter(fn: (r) => r["_measurement"] == "mail_read")
    |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> group()

Read statuses of a mail sent by a specific sender on a specific date

Note that a mail can be read multiple times, and we log all the reads of a mail.

from(bucket: "mail_stats")
    |> range(start: 2024-12-30T08:00:00Z, stop: 2024-12-30T20:00:01Z)
    |> filter(fn: (r) => r["_measurement"] == "mail_read")
    |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> filter(fn: (r) => r["sender_email"] == "sender@email.com")
    |> group()

All the recipients of mail sent by a specific sender

import "join"

senderEmail = "abc@domain.com"

sent = from(bucket: "mail_stats")
            |> range(start: -30d)
            |> filter(fn: (r) => r["_measurement"] == "mail_sent")
            |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
            |> filter(fn: (r) => r["sender_email"] == senderEmail)
            |> group()

recipients = from(bucket: "mail_stats")
                |> range(start: -30d)
                |> filter(fn: (r) => r["_measurement"] == "mail_recipients")
                |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
                |> group()

result = join.left(
            left: sent,
            right: recipients,
            on: (l, r) => l["mail_id"] == r["mail_id"],
            as: (l, r) => {
            return {r with sender_email: l["sender_email"]}
            },
        )

result

Key mail events for a specific mail (examples: sent status, recipients, read status, replies, forwards)

The mail_id of a specific mail can be found from the result of a mail_sent measurement query, with the help of the sender's email address. A sample below:

from(bucket: "mail_stats")
            |> range(start: -1d)
            |> filter(fn: (r) => r["_measurement"] == "mail_sent" )
            |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
            |> filter(fn: (r) => r["sender_email"] == "sender@domain.com")
            |> group()

With the appropriate mail_id, the following script can be used to see the key events on the mail.

import "join"

mailId = "0927f665-0b2f-43be-ade4-594085e656cd"

sent = from(bucket: "mail_stats")
            |> range(start: -1d)
            |> filter(fn: (r) => r["_measurement"] == "mail_sent" )
            |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
            |> filter(fn: (r) => r["mail_id"] == mailId)
            |> group()

recipients = from(bucket: "mail_stats")
                |> range(start: -1d)
                |> filter(fn: (r) => r["_measurement"] == "mail_recipients")
                |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
                |> filter(fn: (r) => r["mail_id"] == mailId)
                |> group()

readStatus = from(bucket: "mail_stats")
                |> range(start: -1d)
                |> filter(fn: (r) => r["_measurement"] == "mail_read")
                |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
                |> filter(fn: (r) => r["mail_id"] == mailId)
                |> group()

repliesOrForwards = from(bucket: "mail_stats")
            |> range(start: -1d)
            |> filter(fn: (r) => r["_measurement"] == "mail_sent")
            |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
            |> filter(fn: (r) => r["parent_mail_id"] == mailId)
            |> group()

result = union(tables: [sent, recipients, readStatus, repliesOrForwards])

result

The _measurement column in the resulting table of the above query indicates the event type (mail_sent, mail_recipients, and mail_read). Note that fresh new mail, replies and forwards belong to mail_sent measurement. In order to differentiate them, please check the is_reply or is_forward flag.

Was this page helpful?