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? Commented Feb 6, 2017 at 20:31
  • The trigger code at the top works for me, well aside from throwing errors on limits. Commented Feb 11, 2017 at 6:12

1 Answer 1

Reset to default
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

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.