0

I need to update CampaignMember, the value that I need to insert is in Opportunities, but there are more than 4 Million records, I was using Jitterbit and Excel to do this, but is very difficult because the amount of records to process and the manual labor is just unpractical.

So I came with the idea of building an Apex Batch that does this.

Here's a break down on which fields I'm using on each object.

Opportunity (Opt):

  • npsp__Primary_Contact__c
  • CampaignId
  • Campaign_Type__c
  • Campaign_Member_Ext_ID

CampaignMember (CM):

  • ContactId
  • CampaignId
  • Campaign_Type__c

I'm using a custom key string to match the Opts with CM, and then get the Campaign_Member_Ext_ID from Opt and Update the CM.

Opt custom key string: ContactId;CampaingID

CM custom key: ContactId;CampaingID

My code so far (first attempt, not tested yet because is a new sandbox and there's no data in it | I'm leaving the comments for clarification purposes):

global class CampaignMemberBatch implements Database.Batchable<SObject>, Database.Stateful {

    global Integer recordsProcessed = 0;

    global Database.QueryLocator start(Database.BatchableContext bc) {
        return Database.getQueryLocator([
            SELECT Id, npsp__Primary_Contact__c, CampaignId, Campaign_Member_Ext_ID_data_upload__c, Campaign_Type__c, CreatedDate
            FROM Opportunity 
            WHERE Campaign_Type__c <> NULL
            AND Donor_Type__c = 'Individual'
            AND CampaignId <> NULL
            AND npsp__Primary_Contact__c <> NULL
        ]);
    }

    global void execute(Database.BatchableContext bc, List<Opportunity> scope) {

        // Map which holds the opts key (ContactId;CampaignId) and value (CM_Ext_Id).
        Map<String, String> extIdsByKeys = new Map<String, String>();
        // Map which holds opts CreatedYear and CampaignType, this will be used to filter.
        Map<Integer, String> optCTypeByYear = new Map<Integer, String>();

        // Map which holds the CampaignMember key (ContactId;CampaignId) and CampaignMemberId (CampaignMemberId).
        Map<String, String> cmIdByKey = new Map<String, String>();
        // Map which holds the CampaignMember CreatedYear and CampaignType, this will be used to filter.
        Map<Integer, String> cmCTypeByYear = new Map<Integer, String>();

        // Loop through the scope and build maps that will work to filter CampaignMembers and others
        for (Opportunity opts : scope) {
            extIdsByKeys.put(opts.npsp__Primary_Contact__c + ';' + opts.CampaignId, opts.Campaign_Member_Ext_ID_data_upload__c);
            optCTypeByYear.put(opts.CreatedDate.year(), opts.Campaign_Type__c);
        }

        // Extract ContactIds from extIdsByKeys to filter CampaignMembers
        List<String> contactIds = new List<String>();
        String conId = '';
        for (String conIds : extIdsByKeys.keySet()) {
            conId = conIds.substring(0, conIds.indexOf(';'));
            contactIds.add(conId);
        }

        // Extract CampaignId from extIdsByKeys to filter CampaignMembers
        List<String> campaignIds = new List<String>();
        String cId = '';
        for (String cIds : extIdsByKeys.keySet()) {
            cId = cIds.substring(cIds.indexOf(';') + 1, cIds.length());
            campaignIds.add(cId);
        }

        // Loop through CampaignMembers that have a match with Opts and store those values inside maps that will be used to
        // Match Opts with CampaignMembers by custom Key string (cmIdByKey) and CampaignType and Year (cmCTypeByYear)
        for (CampaignMember campaignMember : [
                SELECT Id, ContactId, CampaignId, Campaign_Type__c, CreatedDate
                FROM CampaignMember
                WHERE ContactId IN:contactIds
                AND CampaignId IN:campaignIds
                AND Campaign_Type__c IN:optCTypeByYear.values()
                AND CALENDAR_YEAR(CreatedDate) IN:optCTypeByYear.keySet()
        ]) {
            cmIdByKey.put(campaignMember.ContactId + ';' + campaignMember.CampaignId, campaignMember.Id);
            cmCTypeByYear.put(campaignMember.CreatedDate.year(), campaignMember.Campaign_Type__c);
        }

        // CampaignMember to Update
        CampaignMember campaignMember;
        // List that will hold the CampaignMembers to update
        List<CampaignMember> campaignMembers = new List<CampaignMember>();

        // Loop through the scope
        for (Opportunity opts : scope) {
            // Loop through the set of years of CampaignMembers to match the Opts created year with CM created year
            for (Integer year : cmCTypeByYear.keySet()) {
                // Check Match Opts created year with CM created year
                if (opts.CreatedDate.year() == year) {
                    // Check Match by CampaignType
                    if (opts.Campaign_Type__c == cmCTypeByYear.get(year)) {
                        for (String cmKeys : cmIdByKey.keySet()) {
                            // Match Opts with CampaignMember using our custom Key String
                            if (opts.npsp__Primary_Contact__c + ';' + opts.CampaignId == cmKeys) {
                                // Check that CampaignMemberId is not null
                                if (cmIdByKey.get(cmKeys) != null) {
                                    campaignMember = new CampaignMember(Id = cmIdByKey.get(cmKeys), External_ID__c = opts.External_ID__c);
                                    campaignMembers.add(campaignMember);
                                }
                            }
                        }
                    }
                }
            }
            if (!campaignMembers.isEmpty()) {
                try {
                    update campaignMembers;
                } catch (DmlException e) {
                    System.debug(' --- DMLException --- :' + e.getMessage());
                }
            }
        }
    }

    global void finish(Database.BatchableContext bc) {
    }

}

Concerns:

  1. Is this approach better than previous?
  2. How should I avoid hit the limits?
  3. How to take full advantage of this method (Processing time performance)?

More than looking for a concrete answer or solution, I need to confirm/check what is good and what's wrong with logic, for example am I falling in bad practices with many loops or is it okay?

Thank you all.

  • 1
    Remember to include spaces between the query text parts when you build it in the start method... also, you could use static SOQL in the query locator to avoid string concatenation and having to escape quotes etc. – Phil W Sep 12 '19 at 20:27
  • Thank you @PhilW I just did as you said. 👍 – AjFmO Sep 12 '19 at 21:13
  • 2
    you don't need global, public suffices – cropredy Sep 12 '19 at 22:11
1

1)You have not released the memory of unused variables, which will lead to a heap size error if the number of records are more after the CampaignMember query. e.g - extIdsByKeys,optCTypeByYear

2)You have done update campaignMembers; without checking the list size, so if the number of records crosses 10k it will throw an exception. You have to takeout the DML operation out that batch job if the list size is more than 10k.

| improve this answer | |
0

The key to having a batchable that always works is to avoid having queries inside the execute method that return an indeterminate (and so potentially very large number that exceeds the governor limit) of records. In this case, that means looking to see if the query in the start method can be restructured to return CampaignMember records rather than Opportunity records - SOQL does offer a lot of options.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.