Email statistics (SQL script for Data Explorer)

data-explorer

(Anton) #1

I run into 100k / mo emails limit of a free SparkPost account.

To better understand what type of email exactly I have to tackle, I had to write an SQL query which calculates email statistics by email type. Sharing it with the community.

Example result:

SQL query

-- [params]
-- string :date_interval = 1 month

WITH stats AS (
  SELECT
    email_type,
    COUNT(id) as num
  FROM email_logs
  WHERE created_at >= CURRENT_TIMESTAMP - interval :date_interval
  GROUP BY email_type
)

SELECT
  email_type as "Email Type",
  num as "Total Emails",
  ROUND(num::numeric * 100 / (SUM(num) OVER ()), 1) || '%' as "Per Cent",
  (  num / (CURRENT_TIMESTAMP::date
     - (CURRENT_TIMESTAMP - interval :date_interval)::date)
  ) as "Emails per Day"
FROM stats
ORDER BY num DESC

Why so many outgoing emails?
#2

Wow, really good @meglio, thank you very much! :thumbsup: