1

I wrote an APEX script for the campaign object that updates when the campaign updates. Unfortunately I am running into the issue of hitting limits. Also, it would be a lot more useful if the data updated on a regular schedule rather than when the campaign is updated.

Also, trying to wrap my head around writing a test class for a scheduled Batch Update.

here is the code:

trigger UpdateInfluences on Campaign (before update) {
    Set campaignNames = New Set();
    Map dateTimeMap = New Map();

    Map infMQLCounts = New Map();
    Map srcMQLCounts = New Map();

    Map infSQLCounts = New Map();
    Map srcSQLCounts = New Map();

    Map infOpCounts = New Map();
    Map infOpWonCounts = New Map();
    Map infOpValue = New Map();
    Map infOpWonValue = New Map();

    Map srcOpCounts = New Map();
    Map srcOpWonCounts = New Map();
    Map srcOpValue = New Map();
    Map srcOpWonValue = New Map();


    for (Campaign currentCampaign : Trigger.new) {
    //Initiate all the variables
        campaignNames.add(currentCampaign.Name);
        dateTimeMap.put(currentCampaign.Name, currentCampaign.StartDate);
        infMQLCounts.put(currentCampaign.Name, 0);
        srcMQLCounts.put(currentCampaign.Name, 0);
        infSQLCounts.put(currentCampaign.Name, 0);
        srcSQLCounts.put(currentCampaign.Name, 0);

        infOpCounts.put(currentCampaign.Name, 0);
        infOpWonCounts.put(currentCampaign.Name, 0);
        infOpValue.put(currentCampaign.Name, 0);
        infOpWonValue.put(currentCampaign.Name, 0);

        srcOpCounts.put(currentCampaign.Name, 0);
        srcOpWonCounts.put(currentCampaign.Name, 0);
        srcOpValue.put(currentCampaign.Name, 0);
        srcOpWonValue.put(currentCampaign.Name, 0); 
    }

    // For each member of the campaign, pull all relevant data that is later used to determine if they should be counted on the lists or not
    List leadResults = [SELECT CampaignMember.Id, Lead.MQL_Date__c, Contact.MQL_Date__c, Lead.SQL_Date__c, Contact.SQL_Date__c, Lead.isMQL__c, Contact.isMQL__c, Lead.isSQL__c, 
    Contact.isSQL__c, Lead.mkto2__Acquisition_Program__c, Contact.mkto2__Acquisition_Program__c, Campaign.Name, Contact.Id, CampaignMember.HasResponded  FROM CampaignMember WHERE CampaignMember.HasResponded = true AND (Lead.isMQL__c = true OR Contact.isMQL__c = true OR Lead.isSQL__c = true OR Contact.isSQL__c = true) AND Campaign.Name IN :campaignNames];

    // Pull a list of all the primary contacts on opportunities
    List opportunityResults = [SELECT ContactId, Opportunity.StageName, Opportunity.Name, Opportunity.IsWon, Opportunity.CreatedDate, Opportunity.Quote_Accepted_Date__c, Opportunity.Adjusted_Opportunity_Amount__c, Opportunity.Acquisition_Program__c FROM OpportunityContactRole WHERE OpportunityContactRole.IsPrimary=true];

    Map> opportunityMap = new Map>();
    for(OpportunityContactRole o : opportunityResults){
        if (opportunityMap.containsKey(o.ContactId)) {
            opportunityMap.get(o.ContactId).add(o);
        } else {
            opportunityMap.put(o.ContactId, new List{o});
        }
    }


    for (CampaignMember c : leadResults) {
        //Influence Data - If the contact/lead is a member of the campaign and became a member of the campaign before the MQL date or the SQL date
        if (c.Lead.MQL_Date__c>= dateTimeMap.get(c.Campaign.Name) || c.Contact.MQL_Date__c>= dateTimeMap.get(c.Campaign.Name))
            infMQLCounts.put(c.Campaign.Name,infMQLCounts.get(c.Campaign.Name)+1);
        if (c.Lead.SQL_Date__c>= dateTimeMap.get(c.Campaign.Name) || c.Contact.SQL_Date__c>= dateTimeMap.get(c.Campaign.Name))
            infSQLCounts.put(c.Campaign.Name,infSQLCounts.get(c.Campaign.Name)+1);

        //Source Data - If the contact/lead is a member of the campaign and the acqusition program is the current campaign
        if ((c.Lead.isMQL__c == true || c.Contact.isMQL__c == true) && (c.Lead.mkto2__Acquisition_Program__c == c.Campaign.Name || c.Contact.mkto2__Acquisition_Program__c == c.Campaign.Name))
            srcMQLCounts.put(c.Campaign.Name,srcMQLCounts.get(c.Campaign.Name)+1);
        if ((c.Lead.isSQL__c == true || c.Contact.isSQL__c == true) && (c.Lead.mkto2__Acquisition_Program__c == c.Campaign.Name || c.Contact.mkto2__Acquisition_Program__c == c.Campaign.Name))
            srcSQLCounts.put(c.Campaign.Name,srcSQLCounts.get(c.Campaign.Name)+1);

        if (opportunityMap.containsKey(c.Contact.Id) == true) {
            //influence opportunities - If the opportunity was created/won after the contact/lead was added to the campaign
            for (OpportunityContactRole ocr : opportunityMap.get(c.Contact.Id)) {

                if (ocr.Opportunity.CreatedDate >= dateTimeMap.get(c.Campaign.Name)) {
                    infOpCounts.put(c.Campaign.Name, infOpCounts.get(c.Campaign.Name)+1);
                    infOpValue.put(c.Campaign.Name, infOpValue.get(c.Campaign.Name)+ocr.Opportunity.Adjusted_Opportunity_Amount__c);

                    if (ocr.Opportunity.Quote_Accepted_Date__c >= dateTimeMap.get(c.Campaign.Name) && ocr.Opportunity.IsWon == true) {
                        infOpWonCounts.put(c.Campaign.Name, infOpWonCounts.get(c.Campaign.Name)+1);
                        infOpWonValue.put(c.Campaign.Name, infOpWonValue.get(c.Campaign.Name)+ocr.Opportunity.Adjusted_Opportunity_Amount__c);
                    }
                }

            }

        }
    }


    for (OpportunityContactRole ocr : opportunityResults) {
    //source opportunities - If the primary contact of the opportunity acqusition program = this campaign
        if (campaignNames.contains(ocr.Opportunity.Acquisition_Program__c) == true) {
            srcOpCounts.put(ocr.Opportunity.Acquisition_Program__c, srcOpCounts.get(ocr.Opportunity.Acquisition_Program__c)+1);
            srcOpValue.put(ocr.Opportunity.Acquisition_Program__c, srcOpValue.get(ocr.Opportunity.Acquisition_Program__c)+ocr.Opportunity.Adjusted_Opportunity_Amount__c);

            if (ocr.Opportunity.IsWon) {
                srcOpWonCounts.put(ocr.Opportunity.Acquisition_Program__c, srcOpWonCounts.get(ocr.Opportunity.Acquisition_Program__c)+1);
                srcOpWonValue.put(ocr.Opportunity.Acquisition_Program__c, srcOpWonValue.get(ocr.Opportunity.Acquisition_Program__c)+ocr.Opportunity.Adjusted_Opportunity_Amount__c);
            }
        }
    }

    for (Campaign currentCampaign : Trigger.new) {
    // Update everythin on the campaign
        currentCampaign.Influenced_MQLs__c = (Decimal)infMQLCounts.get(currentCampaign.Name);
        currentCampaign.Influenced_SQLs__c = (Decimal)infSQLCounts.get(currentCampaign.Name);
        currentCampaign.Influenced_Opportunity_Count__c = (Decimal)infOpCounts.get(currentCampaign.Name);
        currentCampaign.Influenced_Opportunity_Amount__c = (Decimal)infOpValue.get(currentCampaign.Name);
        currentCampaign.Influenced_Opportunity_Won_Count__c = (Decimal)infOpWonCounts.get(currentCampaign.Name);
        currentCampaign.Influenced_Opportunity_Won_Amount__c = (Decimal)infOpWonValue.get(currentCampaign.Name);

        currentCampaign.Sourced_MQLs__c = (Decimal)srcMQLCounts.get(currentCampaign.Name);
        currentCampaign.Sourced_SQLs__c = (Decimal)srcSQLCounts.get(currentCampaign.Name);
        currentCampaign.Sourced_Opportunity_Count__c = (Decimal)srcOpCounts.get(currentCampaign.Name);
        currentCampaign.Sourced_Opportunity_Amount__c = (Decimal)srcOpValue.get(currentCampaign.Name);
        currentCampaign.Sourced_Opportunity_Won_Count__c = (Decimal)srcOpWonCounts.get(currentCampaign.Name);
        currentCampaign.Sourced_Opportunity_Won_Amount__c = (Decimal)srcOpWonValue.get(currentCampaign.Name);
    }


}

I have looked through the Salesforce docs and many blog posts on how to schedule batch updates, but haven't been successful. This is what I was trying, but I can't seem to wrap my head around this: (long again, main changes are in the first few lines)

global class CampaignInfluence_BatchUpdate implements Database.Batchable {

    //Get all the campaign Ids
    public string query = 'SELECT Id FROM Campaign';

    //, Schedulable
    //global void execute (SchedulableContext SC){
    //   database.executeBatch(this);
    //   string sch = '0 30 2,6,10,14,18,22 * * ?';
    //   system.schedule('Batch', sch, new Scheduler_class());
    //}

    global Database.QueryLocator start(Database.BatchableContext BC) {
        return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, List scope) {
        // List of campaigns to update
        List  campaigns_to_update = (List )scope; 
        for (campaign current_campaign : scope){
            campaigns_to_update.add(current_campaign);
        }

        Set campaignNames = New Set(); // List of campaign names

        // Object Maps for all the fields to be updated
        Map dateTimeMap = New Map();

        Map infMQLCounts = New Map();
        Map srcMQLCounts = New Map();

        Map infSQLCounts = New Map();
        Map srcSQLCounts = New Map();

        Map infOpCounts = New Map();
        Map infOpWonCounts = New Map();
        Map infOpValue = New Map();
        Map infOpWonValue = New Map();

        Map srcOpCounts = New Map();
        Map srcOpWonCounts = New Map();
        Map srcOpValue = New Map();
        Map srcOpWonValue = New Map();

        //Initialize the campaigns name set and all the maps to 0
        for (Campaign currentCampaign : campaigns_to_update) {
            campaignNames.add(currentCampaign.Name);
            dateTimeMap.put(currentCampaign.Name, currentCampaign.StartDate);
            infMQLCounts.put(currentCampaign.Name, 0);
            srcMQLCounts.put(currentCampaign.Name, 0);
            infSQLCounts.put(currentCampaign.Name, 0);
            srcSQLCounts.put(currentCampaign.Name, 0);

            infOpCounts.put(currentCampaign.Name, 0);
            infOpWonCounts.put(currentCampaign.Name, 0);
            infOpValue.put(currentCampaign.Name, 0);
            infOpWonValue.put(currentCampaign.Name, 0);

            srcOpCounts.put(currentCampaign.Name, 0);
            srcOpWonCounts.put(currentCampaign.Name, 0);
            srcOpValue.put(currentCampaign.Name, 0);
            srcOpWonValue.put(currentCampaign.Name, 0); 
        }

        //Get the list of all campaign members
        List leadResults = [SELECT CampaignMember.Id, Lead.MQL_Date__c, Contact.MQL_Date__c, Lead.SQL_Date__c, Contact.SQL_Date__c, Lead.isMQL__c, Contact.isMQL__c, Lead.isSQL__c, 
        Contact.isSQL__c, Lead.mkto2__Acquisition_Program__c, Contact.mkto2__Acquisition_Program__c, Campaign.Name, Contact.Id, CampaignMember.HasResponded  FROM CampaignMember WHERE CampaignMember.HasResponded = true AND (Lead.isMQL__c = true OR Contact.isMQL__c = true OR Lead.isSQL__c = true OR Contact.isSQL__c = true) AND Campaign.Name IN :campaignNames];

        // Get the list of all opportunities with primary contacts
        List opportunityResults = [SELECT ContactId, Opportunity.StageName, Opportunity.Name, Opportunity.IsWon, Opportunity.CreatedDate, Opportunity.Quote_Accepted_Date__c, Opportunity.Adjusted_Opportunity_Amount__c, Opportunity.Acquisition_Program__c FROM OpportunityContactRole WHERE OpportunityContactRole.IsPrimary=true];

        // Map contact ID to opportunity contact role record
        Map> opportunityMap = new Map>();
        for(OpportunityContactRole o : opportunityResults){
            if (opportunityMap.containsKey(o.ContactId)) {
                opportunityMap.get(o.ContactId).add(o);
            } else {
                opportunityMap.put(o.ContactId, new List{o});
            }
        }

        System.debug(opportunityResults);
        System.debug(opportunityMap);

        // Cycle through campaign members and add up the record counts on the campaigns. 
        for (CampaignMember c : leadResults) {
            //Influence Data
            if (c.Lead.MQL_Date__c>= dateTimeMap.get(c.Campaign.Name) || c.Contact.MQL_Date__c>= dateTimeMap.get(c.Campaign.Name))
                infMQLCounts.put(c.Campaign.Name,infMQLCounts.get(c.Campaign.Name)+1);
            if (c.Lead.SQL_Date__c>= dateTimeMap.get(c.Campaign.Name) || c.Contact.SQL_Date__c>= dateTimeMap.get(c.Campaign.Name))
                infSQLCounts.put(c.Campaign.Name,infSQLCounts.get(c.Campaign.Name)+1);

            //Source Data
            if ((c.Lead.isMQL__c == true || c.Contact.isMQL__c == true) && (c.Lead.mkto2__Acquisition_Program__c == c.Campaign.Name || c.Contact.mkto2__Acquisition_Program__c == c.Campaign.Name))
                srcMQLCounts.put(c.Campaign.Name,srcMQLCounts.get(c.Campaign.Name)+1);
            if ((c.Lead.isSQL__c == true || c.Contact.isSQL__c == true) && (c.Lead.mkto2__Acquisition_Program__c == c.Campaign.Name || c.Contact.mkto2__Acquisition_Program__c == c.Campaign.Name))
                srcSQLCounts.put(c.Campaign.Name,srcSQLCounts.get(c.Campaign.Name)+1);

            if (opportunityMap.containsKey(c.Contact.Id) == true) {
                //influence opportunities
                System.debug(opportunityMap.get(c.Contact.Id));
                for (OpportunityContactRole ocr : opportunityMap.get(c.Contact.Id)) {

                    if (ocr.Opportunity.CreatedDate >= dateTimeMap.get(c.Campaign.Name)) {
                        infOpCounts.put(c.Campaign.Name, infOpCounts.get(c.Campaign.Name)+1);
                        infOpValue.put(c.Campaign.Name, infOpValue.get(c.Campaign.Name)+ocr.Opportunity.Adjusted_Opportunity_Amount__c);

                        if (ocr.Opportunity.Quote_Accepted_Date__c >= dateTimeMap.get(c.Campaign.Name) && ocr.Opportunity.IsWon == true) {
                            infOpWonCounts.put(c.Campaign.Name, infOpWonCounts.get(c.Campaign.Name)+1);
                            infOpWonValue.put(c.Campaign.Name, infOpWonValue.get(c.Campaign.Name)+ocr.Opportunity.Adjusted_Opportunity_Amount__c);
                        }
                    }

                }

            }
        }


        for (OpportunityContactRole ocr : opportunityResults) {
            if (campaignNames.contains(ocr.Opportunity.Acquisition_Program__c) == true) {
                srcOpCounts.put(ocr.Opportunity.Acquisition_Program__c, srcOpCounts.get(ocr.Opportunity.Acquisition_Program__c)+1);
                srcOpValue.put(ocr.Opportunity.Acquisition_Program__c, srcOpValue.get(ocr.Opportunity.Acquisition_Program__c)+ocr.Opportunity.Adjusted_Opportunity_Amount__c);

                if (ocr.Opportunity.IsWon) {
                    srcOpWonCounts.put(ocr.Opportunity.Acquisition_Program__c, srcOpWonCounts.get(ocr.Opportunity.Acquisition_Program__c)+1);
                    srcOpWonValue.put(ocr.Opportunity.Acquisition_Program__c, srcOpWonValue.get(ocr.Opportunity.Acquisition_Program__c)+ocr.Opportunity.Adjusted_Opportunity_Amount__c);
                }
            }
        }

        // Update the appropriate fields on the campaign
        for (Campaign currentCampaign : Trigger.new) {
            currentCampaign.Influenced_MQLs__c = (Decimal)infMQLCounts.get(currentCampaign.Name);
            currentCampaign.Influenced_SQLs__c = (Decimal)infSQLCounts.get(currentCampaign.Name);
            currentCampaign.Influenced_Opportunity_Count__c = (Decimal)infOpCounts.get(currentCampaign.Name);
            currentCampaign.Influenced_Opportunity_Amount__c = (Decimal)infOpValue.get(currentCampaign.Name);
            currentCampaign.Influenced_Opportunity_Won_Count__c = (Decimal)infOpWonCounts.get(currentCampaign.Name);
            currentCampaign.Influenced_Opportunity_Won_Amount__c = (Decimal)infOpWonValue.get(currentCampaign.Name);

            currentCampaign.Sourced_MQLs__c = (Decimal)srcMQLCounts.get(currentCampaign.Name);
            currentCampaign.Sourced_SQLs__c = (Decimal)srcSQLCounts.get(currentCampaign.Name);
            currentCampaign.Sourced_Opportunity_Count__c = (Decimal)srcOpCounts.get(currentCampaign.Name);
            currentCampaign.Sourced_Opportunity_Amount__c = (Decimal)srcOpValue.get(currentCampaign.Name);
            currentCampaign.Sourced_Opportunity_Won_Count__c = (Decimal)srcOpWonCounts.get(currentCampaign.Name);
            currentCampaign.Sourced_Opportunity_Won_Amount__c = (Decimal)srcOpWonValue.get(currentCampaign.Name);
        }
    }

    global void finish(Database.BatchableContext BC){}
}
Improve this question
2
  • The trigger, it works?
    – Carlos Naranjo
    Feb 6, 2017 at 20:31
  • The trigger code at the top works for me, well aside from throwing errors on limits.
    – asielen
    Feb 11, 2017 at 6:12

1 Answer 1

Reset to default
1

First of all add this to your Batchable class line 1

global class CampaignInfluence_BatchUpdate 
       implements Database.Batchable, Schedulable {

Then, add a Schedulable execute method that executes the Batch

public void execute(SchedulableContext sc) {
  Database.executeBatch(new CampaignInfluence_BatchUpdate());
}

Finally, go to Developer Console to start the schedulable:

System.schedule('jobnameMustBeUnique',
                '0 30 2,6,10,14,18,22 * * ?', 
                 new CampaignInfluence_BatchUpdate());

The anonymous apex will start a schedulable job with the desired cron expression. When the job starts, its execute(SchedulableContext sc) method is called that then starts the batch job

Finally, unless this is a managed package, you can ditch the global and use public

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.