Dashboard Report - Emails Sent

data & reporting
, ,
1

This is an SQL version of the Dashboard Report for Emails Sent.

This report provides a daily breakdown of the total number of emails sent from the Discourse platform within a specified date range. It is useful for monitoring email activity, identifying trends, and ensuring that the email system is functioning correctly.

-- [params]
-- date :start_date
-- date :end_date

SELECT
  DATE(created_at) AS date_sent,
  COUNT(*) AS total_emails_sent
FROM email_logs
WHERE created_at BETWEEN :start_date AND :end_date
GROUP BY date_sent
ORDER BY date_sent ASC

SQL Query Explanation

The SQL query is designed to retrieve the count of emails sent each day within a given period. Here’s a step-by-step explanation of the query:

Parameters

The query accepts two parameters, :start_date and :end_date, allowing the user to specify the date range for the report. Both date parameters accept the date format of YYYY-MM-DD .

Query Breakdown

  • DATE(sent_at) AS date_sent: Extracts the date part from the sent_at timestamp and labels the resulting column as date_sent.
  • COUNT(*) AS total_emails_sent: Counts the total number of rows (emails) for each group, labeling the result as total_emails_sent.
  • FROM email_logs: This clause indicates that the data will be retrieved from the email_logs table, which records each instance of an email being sent.
  • WHERE sent_at BETWEEN :start_date AND :end_date: This clause filters the data to include only the records where the sent_at timestamp falls within the specified date range.
  • GROUP BY date_sent: This clause groups the results by the date on which the emails were sent. This is necessary for the COUNT function to calculate the total per day.
  • ORDER BY date_sent ASC: This clause orders the results in ascending order by the date_sent column, ensuring the report shows the data starting from the earliest date to the latest within the range.

Example Results

date_sent total_emails_sent
2023-11-19 264
2023-11-20 932
2023-11-21 678
2023-11-22 637
2023-11-23 369
… …
2

It’s also worth noting that the default period for retaining email logs is 90 days, but can be adjusted using the delete email logs after days admin setting (with the option to set it to 0 to keep them indefinitely).

Just in case anyone wondered why looking at last years wasn’t working. :slight_smile:

1 Like