Campaign Status "Finished" Before All Emails Were Sent

[subhash-ngowda]

Version:

• listmonk: [v3.0.0]
• OS: [Ubuntu LTS]

Description of the bug: We are encountering an issue where the campaign status is marked as "finished" before all emails have been sent. Campaign Details

• Campaign Type: Email
• Subscriber List: Single opt-in list with 200,000 subscribers
• Blocklisted Subscribers: Approximately 20,000
• Batch Size: 200
• Issue Observed: Campaign status changed to "finished" after sending only 37 emails

Analysis and Findings Upon further investigation, we identified a potential bug in the query/code that might be causing this issue. Below are the details (Code Snippet from Pipe.go):

// NextSubscribers processes the next batch of subscribers in a given campaign.
// It returns a bool indicating whether any subscribers were processed
// in the current batch or not. A false indicates that all subscribers
// have been processed, or that a campaign has been paused or cancelled.

Campaign Scheduling Mechanism The campaign scheduling mechanism uses last_subscriber_id and max_subscriber_id to process subscribers in batches. The last_subscriber_id acts as a pointer until max_subscriber_id is reached. The subIDs query fetches distinct subscriber IDs and their subscription status from subscriber_lists where the list ID is among the lists from campLists. Additionally, it filters out subscribers with the status unsubscribed. If all subscribers that meet the criteria in subIDs are blocklisted, the subs CTE will return zero records due to the condition that excludes blocklisted subscribers. Observed Behaviour in Our Case In our scenario, during the second iteration, all 200 records were blocklisted, resulting in zero records being returned. Consequently, the NextSubscribers process returned false, causing the campaign status to be changed to "finished" without sending emails to the entire list.

Relevant Code in Pipe.go

// Line 85
if len(subs) == 0 {
return false, nil
}

Conclusion The issue stems from the query filtering out blocklisted subscribers and returning zero records if all remaining subscribers in a batch are blocklisted. This causes the NextSubscribers process to return false prematurely, changing the campaign status to "finished".

Recommendations To address this issue, we recommend modifying the query and/or logic to ensure that the campaign does not prematurely finish when encountering batches of blocklisted subscribers. This will ensure that emails are sent to all eligible subscribers in the list.

[MaximilianKohler]

This might be the main issue for #1762, and #1802.

[nayanthulkar28]

Hi @subhash-ngowda , I tried to replicate the above bug with batch_size=2 but didn't notice results as you mention in the solution above. In the CTE subIDs we are filtering out with status != unsubscribe which also indicated that subIDs won't include blocklist subscribers because blocklist unsubscribe lists while creating or updating. The subIDs proceed with all valid subscribers with batch size.

[subhash-ngowda]

This might be the main issue for #1762, and #1802. Yes

[subhash-ngowda]

@nayanthulkar28 We were only able to reproduce this if the users are in multiple lists and they are blocklisted from any of the list. Take a look at this below attached image.

[nayanthulkar28]

Understood!

[nayanthulkar28]

I couldn't find the case where a subscriber is blocklisted and it has status!='unsubscribed' (Please let me know the when this case happens).

Considering this usecase, here we can fix this query accordingly:

Remove this limit in subIDs

subIDs AS (
    SELECT DISTINCT ON (subscriber_lists.subscriber_id) subscriber_id, list_id, status FROM subscriber_lists
    WHERE
        -- ARRAY_AGG is 20x faster instead of a simple SELECT because the query planner
        -- understands the CTE's cardinality after the scalar array conversion. Huh.
        list_id = ANY((SELECT ARRAY_AGG(list_id) FROM campLists)::INT[]) AND
        status != 'unsubscribed' AND
        subscriber_id > (SELECT last_subscriber_id FROM camps) AND
        subscriber_id <= (SELECT max_subscriber_id FROM camps)
    // ORDER BY subscriber_id LIMIT $2
),

Add it in subs

subs AS (
    SELECT subscribers.* FROM subIDs
    LEFT JOIN campLists ON (campLists.list_id = subIDs.list_id)
    INNER JOIN subscribers ON (
        subscribers.status != 'blocklisted' AND
        subscribers.id = subIDs.subscriber_id AND

        (CASE
            -- For optin campaigns, only e-mail 'unconfirmed' subscribers.
            WHEN (SELECT type FROM camps) = 'optin' THEN subIDs.status = 'unconfirmed' AND campLists.optin = 'double'

            -- For regular campaigns with double optin lists, only e-mail 'confirmed' subscribers.
            WHEN campLists.optin = 'double' THEN subIDs.status = 'confirmed'

            -- For regular campaigns with non-double optin lists, e-mail everyone
            -- except unsubscribed subscribers.
            ELSE subIDs.status != 'unsubscribed'
        END)
    )
    ORDER BY subscribers.id LIMIT $2
),

[MaximilianKohler]

I couldn't find the case where a subscriber is blocklisted and it has status!='unsubscribed' (Please let me know the when this case happens).

I think it happens when a subscriber clicks the "unsubscribe" link in an email and chooses "permanent block list", or it may happen when you manually blocklist someone. I think it used to happen when the system would automatically blocklist them for bounce/complaint, but I think at some point that changed to only blocklisting them and not unsubscribing them.

[rlford]

I'd like to add to this and state that I received the same problem. 166 of 238 e-mails were sent out. The problem with the "ubsubscribe" or "block list" theory is that this is a brand new installation with the very first campaign I've ever created. Our employees haven't even had the chance to unsubscribe yet because they've never received an e-mail like this from us in the past.

[lucasferreira]

Hi folks, just for aggregate in this issue, we was facing the same problem with our campaigns and after reading this issue we delete all our blocked users from our lists and now our campaigns do not stop in midway anymore.

Today we could sent 1.5mi e-mails again without problems, so this thing about "import a lot of blocked users from csv" could be really a important fact in this investigation.