Export of Bounced / Unsubscribed

[skeelkat]

Thanks for a really awesome piece of software. I love everything about it, and I mean everything

Is it possible to export bounced / unsubscribed emails? I would like to export the bounced emails to a CSV or similar and give it to my accountant to rectify / remove the dead items from my ERP.

[knadh]

Thanks! There isn't a direct CSV export for bounces currently, but there's an API that returns JSON. You could use it to dump data to a JSON file or format it to a CSV or directly integrate with your ERP.

eg:

curl -u username:passsword "http://localhost:9000/api/bounces"

You could also query the bounces table directly.

SELECT bounces.created_at,
    bounces.subscriber_id,
    subscribers.uuid AS subscriber_uuid,
    subscribers.email AS email
FROM bounces
LEFT JOIN subscribers ON (subscribers.id = bounces.subscriber_id)
ORDER BY bounces.created_at DESC LIMIT 1000;

[skeelkat]

Thanks! There isn't a direct CSV export for bounces currently, but there's an API that returns JSON. You could use it to dump data to a JSON file or format it to a CSV or directly integrate with your ERP.

eg:

curl -u username:passsword "http://localhost:9000/api/bounces"

You could also query the bounces table directly.

SELECT bounces.created_at,
    bounces.subscriber_id,
    subscribers.uuid AS subscriber_uuid,
    subscribers.email AS email
FROM bounces
LEFT JOIN subscribers ON (subscribers.id = bounces.subscriber_id)
ORDER BY bounces.created_at DESC LIMIT 1000;

Thanks man... I started the docker container 4 hours ago. Did not get around to the API section.

[MaximilianKohler]

Closed as completed? The directions above only give a bounced list, not unsubscribed no?

I looked at the APIs page https://listmonk.app/docs/apis/subscribers/ and it says you can pass SQL queries, but I tested via the search at /admin/subscribers, and subscribers.status='unsubscribed' and subscribers.subscription_status='unsubscribed' aren't valid queries.

I guess you would have to export the whole list, convert to CSV and then sort by subscription_status (if that's even a column)?