Why Do the CAMPAIGN_RECIPIENT and CAMPAIGN_RECIPIENT_ACTIVITY Tables Contain More Rows Than the Number of Emails Sent?

Question

Why do the CAMPAIGN_RECIPIENT and CAMPAIGN_RECIPIENT_ACTIVITY tables contain more rows than the number of emails sent from Mailchimp?

Environment

Connector: Mailchimp

Answer

The tables contain more rows due to the following reasons:

• The connector fetched data that is older than the previous day.
• If you observe this after the historical sync, the connector may still be syncing the historical email activity.

In each sync, the Mailchimp connector processes email activities once all other endpoints are done. It uses the email activities to fetch the data for the CAMPAIGN_RECIPIENT and CAMPAIGN_RECIPIENT_ACTIVITY tables. As the email activities endpoint is extremely slow, we process email activities for up to 8 hours per sync. In subsequent syncs, we fetch the remaining data in the same way. We stop processing the email activities when it exceeds 8 hours as we do not want the sync to run indefinitely until all email activities are synced.

We load data only once for the CAMPAIGN_RECIPIENT table. However, our sync strategy for the CAMPAIGN_RECIPIENT_ACTIVITY table is mostly non-incremental. We check if a campaign has any new activity, such as new clicks or opens. If it contains any new activity, we fetch and upsert all data including previously upserted data.

For more information, see our Mailchimp documentation.

Cause

The Mailchimp email activity endpoint is slow in its responses.