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){}
}
2
  • The trigger, it works? 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

2

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .