Delivery servers stats overview

[nohands]

I'd like to build a dashboard (not necessarily inside Mailwizz) that shows delivery server performance over time, primarily focusing on the last 30 days. The idea is to see which mail servers are performing well (so they can be prioritized for sendouts) and which are not (so they can be parked/retired).

What do I mean by performance? It's mostly centered around volume (number of emails sent within a given timeframe), deliveries, soft / hard bounces, opens, clicks, unsubs. To get this, I'd have to build an SQL query to extract this info from the DB. While I don't have an issue with SQL part, I'm still learning the ropes of Mailwizz, so I'm not familiar with all the DB tables.

Could you pinpoint which tables contain the info I mentioned above? Can this information be accessed on an individual recipient level as well, so I could also check the recipient mailbox provider (based on the recipient domain and some extra lookup table) and essentially add another column to this dataset showing the performance of a mail server in relationship to a mailbox provider?

PS: I run my own postfix servers with integrated bounce/fbl, so we can assume that this part is already working.

Thanks a lot.

 

[twisted1919]

Most likely you'll want to look into campaign_delivery_log, this one stores all emails that go out for a campaign and also contains a reference to the delivery server and subscriber. Then there's the delivery_server_usage_log table, where we log each email that goes out from a server. With these two tables you should be able to construct the dashboard you're after and connect to other tables in mailwizz, I think you'll find the database tables and schema very straight forward.

My advice is to not pull this info directly, instead, decide how you want to display this data and buid your own summary tables for it, and query mailwizz's tables in background and populate these tables and query these tables directly, which must be optimized for your use case.