Counting Tasks with DLRS

      22 Comments on Counting Tasks with DLRS

ObHow many tasks have been done in X time frame is a common ask from marketing or sales.  Before it would require a report to answer but now with DLRS we can have that answer live on the record itself.  In this example I’ll go over Task Counting for Leads.

The theoretical ask this DLRS can solve for is this.  Someone would love to know how many tasks in the last week have been done on a Lead.   Even better they would love to get a quick count of how many tasks are on that Lead that are not from some sort of automated system.  Well with DLRS you can accomplish that pretty easily.  The goal is so an SDR can create a List View that only pulls in leads that have not been contacted by a person more than 2 times in the last week.

First step is on the Lead lets create a numeric field and name it Completed Tasks In The Last Week.  Lets make the API name of the field DLRS_Completed_Tasks_In_The_Last_Week.  Next lets go and create our DLRS.  I’ll use the Custom Metadata layout as that seems to be the new hotness and most likely the future of DLRS.

  1.  This is the Parent Object as we want to roll the count up to the Lead Record
  2. Activities/Task live on the Task Object
  3. WhoID is the field that relates Tasks/Activities to the Lead Object.  It is a strange field here is a data map of it
  4.   This is where all of our business logic will live.  We will go through these step by step for this use case.
    1. For this use case we only want to select Tasks that have been completed.  So we say that IsClosed must be TRUE.
    2. We also don’t want to count ones that our Marketing Automation system creates automatically but WE DO want to count those that are created when a rep sends an email through in this case Marketo Sales Insight.  So we know that when a Rep creates a task via MSI Marketo creates it but ASSIGNS it to the Rep.  When Marketo creates a Task from a marketing email or activity it assigns the task to itself.  In this case the Assigned To field is OwnerID so we say that the OwnerID is NOT EQUAL to the ID of our Marketo user.
    3. We don’t want to count things that were set as complete further in the past then last week so we say ActivityDate >= LAST_WEEK.  We also don’t want to count things in the future where someone might have entered in the wrong value so we use ActivityDate <= TODAY.  We also don’t want to count things where there was no Date filled in so you can’t see it in the screenshot but there is ActivityDate != Null also.  (If you wanted to get fancy you could set up a workflow rule to stamp the date when an activity is completed and use that as your criteria so you could have people leave the due date in the future to show they completed something early).
    4. When we put it all together the field ends up reading as so
      IsClosed = TRUE AND OwnerId != ‘a145000000CDmlQ’ AND ActivityDate >= LAST_WEEK AND ActivityDate <= TODAY AND ActivityDate != Null
  5. This is where we put in all of the fields we use in #4 on separate lines, in this case IsClosed, ActivityDate and OwnerID
  6. We just want a count of the records so we just can put ID here and it will count how many ID’s are returned.
  7. We want to Count how many records are found
  8. This is where we want to store the results on our Lead Record.  In this case we will use the field we just created DLRS_Completed_Tasks_In_The_Last_Week__c
  9. I want this to run in realtime
  10. I want this to bypass any sort of task privacy I might have set up in my org so I have it set to run as System

Once you have this record saved, you can deploy the child trigger via the manage child trigger button then go back and activate the rollup.  Now you will also want to click Calculate to backfill all the records, this might best be done at night as it will take a while to run through all your lead records.  Then you can build a Lead List view for the SDR’s where the DLRS_Completed_Tasks_In_The_Last_Week__c <= 2.  As this is a realtime Rollup that has a date field as a criteria you might want to set up a schedule Calculate to re-run all leads where that value is above 2 every Sunday night so come Monday it is a fresh value for the SDRs to work.

 

Account Based Marketing (ABM) Lite Inside Salesforce

Account Based Marketing (ABM) Lite inside Salesforce

Account Based Marketing (ABM) is the newest and hottest up and coming marketing plans. For a quick read up on what ABM is Salesforce wrote a pretty good blog on ABM. https://www.salesforce.com/blog/2016/12/account-based-marketing-state-of-mind.html

So, the biggest problem that lots of admins will start to face as ABM becomes the next big thing is how the hell do I map leads to accounts?!  This was my solution to the ABM problem inside salesforce, this may not be the most elegant but it works for my needs without having to pay for yet another tool.  It uses a combination of a custom object, in our case Informatica (Could be SFDC Apex/Flow if you wanted), Apex and a handful of custom fields.

First off, the backbone of this is the best way to match Leads to Accounts is NOT on account name, as account name is normally user provided and might not match what we have as the account name in our Account Object.  The answer we came up with is domain matching, you could build some cool fuzzy logic inside Apex but we treat this more as a manual process as some companies have MANY mail domains that don’t match to their web-domain.

The Domain Matching Object is a pretty basic object.  3 custom fields and that’s it.  Simple and clean.  One thing we did do is set up the field that holds the domain to be a unique case insensitive field as for this to work there can only be one domain record.  This is a n:1 type relationship.  We can have many domain records to a single account but we can’t have many accounts to a single domain.

Next the way I populate this object auto-magically is with a scheduled apex class.  But first we must make a few changes to the Lead Object, Contact Object and Account Object.  Let’s get to those and then we will go over my apex code (Disclaimer: I am not a trained developer! The code you will see here is not written using Apex best practices).

Lead Object –

  • Domain Lookup Timestam – Date/Time
  • Email Domain – Formula – Text – SUBSTITUTE(Email, LEFT(Email, FIND(“@”, Email)), NULL)
  • Related Account – Lookup – Account
  • Related Account Type – Formula – Text – Account.Type

Contact Object –

  • Email Domain – Formula – Text – SUBSTITUTE(Email, LEFT(Email, FIND(“@”, Email)), NULL)

Account –

  • New Related List on Page Layout – Domain-Matched Leads

Now back to auto-populating this new object for people to approve or deny matching.  I am not the best at coding so there are most likely ways you can make this better, faster, stronger.  This was the best I had and it does the trick.

If you’ve never built any apex you’ll want to go to Setup > Develop > Apex Classes > New Apex Class.  Then you can copy this as a starting point but you will need to change the fields to the names of the fields in your org.  You can only do this in a sandbox, you can’t write new apex in production.

global class NewAccountDomains implements Schedulable
{
    global void execute(SchedulableContext SC)
    {
        newDomains();
    }
    
    public void newDomains()
    {
        //Create our List of New Account Domain Records to generate at the end.
        List newMatches = new List();
        Set newAddedDomains = new Set();
        
        //First find all of our Customer Account ID's
        Set customerAccountID = new Map<Id, Account>([SELECT Id FROM Account WHERE Type = 'Customer']).keySet();
        
        //Collect a Set of our Domains from the Account Domain Object.  We will use this to exclude in our contact scan
        Set matchedDomains = new Set(); 
        for (Account_Domain_Matching__c domainScan : [Select Id, Domain__c from Account_Domain_Matching__c])
        {
            matchedDomains.add(domainScan.Domain__c);
        }
        
        //Now Collect a List of Contacts that have new Domains that are from Customer Accounts and are not already Matched Domains
        for (Contact newDomains : [SELECT Id, Email_Domain__c, AccountId FROM Contact WHERE Email_Domain__c != NULL AND Email_Domain__c NOT IN :matchedDomains AND AccountId IN :customerAccountID])
        {   
            if(newDomains != null)
            {
                if(!newAddedDomains.contains(newDomains.Email_Domain__c))
                {
                    Account_Domain_Matching__c ADM = new Account_Domain_Matching__c(
                        Domain__c = newDomains.Email_Domain__c,
                        Related_Account__c = newDomains.AccountID,
                        Approved__c = 'Awaiting Approval');
                
                    newMatches.add(ADM);
                    newAddedDomains.add(ADM.Domain__c);
                }
            }
        }
        
        if(newMatches != null)
        {
            insert newMatches;
        }
     }
}

Going over what this class does.  It will grab a list of Accounts that in this case are set as a Customer (you can customize this as you need maybe you only want to grab target accounts, as ABM is a focused way of marketing and selling you will want to limit your account selection else you will end up with a pretty large and mostly impossible domain mapping task.)

It then finds all the current Domain Matching Records we have and the domains that are in those records.  Then it looks at all the contacts attached to those accounts we care about that DO NOT have a domain that is already in our Domain Matching object.  For those that it finds it then creates a new Domain Matching record and sets it to “Awaiting Approval”.

Now you will most likely have a bunch of bad domains in that list.  My buddy Kevin Purdy wrote a small python script that you feed a CSV and it will check to see if the domain responds to a gethostbyname request. If it does not, it writes the domain out to another CSV file.  You can then use this as a primary key in Data Loader and  change the status of those records to rejected.

To use this script you’ll need to install python then copy this code into a new text document and save it as emailscan.py .  Then you’ll want a csv with all of your domains in a single list in the same folder as your emailscan.py.  Then run emailscan.py and it should grab that csv and check if the domains resolve or not, then it will write the ones that don’t resolve to a new folder called results.csv.

 

#!/usr/bin/env python

import socket
import csv
import sys

def hostname_resolves(hostname):
  try:
    socket.gethostbyname(hostname)
    return False
  except socket.error:
    return True

writer = csv.writer(open("results.csv", 'w', newline=""))
with open("emails.csv", "rt") as csvFile:
  reader = csv.reader(csvFile, delimiter=",")
  for line in reader:
    if hostname_resolves(line[0]):
          writer.writerow(line)
          print (line)

I also have this small trigger on the Account Domain Matching object that when a record is denied it clears out the related account so if anything, ever goes wrong and any other processes picks up denied records they at least the record wont map to anything.  Call me paranoid ;) 

To create a trigger go to Setup > Create > Objects > Account_Domain_Matching__c object we created earlier.  Then in this object near the bottom there should be a button to create a new trigger on the object.  You can only do this in a sandbox environment you can’t write apex in production.

trigger NullRelatedAccountOnDeny on Account_Domain_Matching__c (before update, before insert) 
{
    for(Account_Domain_Matching__c adm : Trigger.New)
    {
        if(adm.Approved__c == 'Denied' && adm.Related_Account__c != Null)
        {
            adm.Related_Account__c = Null;
        }
    }

}

(The test below test class covers both the scheduled class and the Null Values trigger.)

Below is the bare min for a test class that will get this covered but by no means follows all best practices.  . Again I’m not a developer by trade, but this will work.  You can use it as a starting point for getting a test class to work in your org at your own level of best practices.

@isTest
public class NewAccountDomainsTest
{
    public static testmethod void testschedule()
    {
        Test.startTest();
        
        //Generate Fakes Data
        
        Account a = new Account(
        type = 'Customer',
        name = 'Testy McTesterson',
        );
        insert a;
        
        Contact c = new Contact(
        firstname = 'Testy', 
        lastname ='McTesty', 
        title ='Magic Maker', 
        email ='Testy@McTesterson.com',
        accountid = a.id
        );
        insert c;
        
        Account_Domain_Matching__c adm = new Account_Domain_Matching__c(
        domain__c = 'Dan.com', 
        approved__c = 'Approved', 
        related_account__c = a.id
        );
        insert adm;

        Account_Domain_Matching__c adm2 = new Account_Domain_Matching__c(
        domain__c = 'Dan2.com', 
        approved__c = 'Denied', 
        related_account__c = a.id
        );
        insert adm2;
                     
        //Setup the Schedule Run
        
        NewAccountDomains nad = new NewAccountDomains();
        String nad1 = '0 00 1 3 * ?';
        system.schedule('Test', nad1, nad);
             
        Test.stopTest();
    }
}

Now we have an object that is full of domain mapping but we don’t have anything matching the leads to those records.  In my case I use a mix of Apex and Informatica.   That Date/Time field we created on the lead object I populate that with a time stamp when leads are created or updated in ways that we would want to re-scan.

Again, I know logic-less triggers are best and a Trigger should just call a class that holds all the logic but, this works for my needs.  You can easily convert this to a logic-less trigger if you are so inclined.

trigger AccountMatchDate on Lead (Before Insert, Before Update) 
{
    if(Trigger.isInsert)
    {
       for(Lead ld : Trigger.New)
       {
          ld.Domain_Lookup_Timestamp__c = datetime.now();
       }
    } 
    else if(Trigger.isUpdate)
    { 
      for(Lead ld : Trigger.New)
      {
        Lead oldld = Trigger.oldMap.get(ld.id);
        if(oldld.Email != ld.Email && oldld.Email_Domain__c != ld.Email_Domain__c)
        {
           ld.Domain_Lookup_Timestamp__c = datetime.now();
        }
      }
    }
} 

Now that we have that date stamp we can use Informatica to do the lookup for us.  If you don’t have informatica you can 100% use Apex or Visual Flow to have this all done in SFDC. In place of a timestamp you would just have Apex or Visual Flow do the lookup on Insert when Email != NULL or when Email Changes.  I don’t have the visual workflow that does that but you COULD.

(Here is a lead trigger that would do this 100% inside sfdc however, I cannot guarantee the efficiency of this code!)

trigger AccountMatch on Lead (Before Insert, Before Update) 
{
    if(Trigger.isInsert)
    {
       List DomainList = new List([SELECT Id, Domain__c, Related_Account__c FROM Account_Domain_Matching__c WHERE Approved__c = 'Approved']);
       Map<String, Account_Domain_Matching__c> DomainMap = new Map<String, Account_Domain_Matching__c>();
       For(Account_Domain_Matching__c adm : DomainList)
       {
           DomainMap.put(adm.Domain__c, adm);
       }    
       
       for(Lead ld : Trigger.New)
       {
          if(ld.Email != Null)
          {
             Account_Domain_Matching__c related = DomainMap.get(ld.Email_Domain__c);
             if(related != Null)
             {
                ld.Related_Account__c = related.Related_Account__c;
             }
          }
       }
    } 
    else if(Trigger.isUpdate)
    { 
       List DomainList = new List([SELECT Id, Domain__c, Related_Account__c FROM Account_Domain_Matching__c WHERE Approved__c = 'Approved']);
       Map<String, Account_Domain_Matching__c> DomainMap = new Map<String, Account_Domain_Matching__c>();
       For(Account_Domain_Matching__c adm : DomainList)
       {
           DomainMap.put(adm.Domain__c, adm);
       }    
      for(Lead ld : Trigger.New)
      {
        Lead oldld = Trigger.oldMap.get(ld.id);
        if(oldld.Email != ld.Email && oldld.Email_Domain__c != ld.Email_Domain__c && ld.Email != Null)
        {
           Account_Domain_Matching__c related = DomainMap.get(ld.Email_Domain__c);
           if(related != Null)
           {
              ld.Related_Account__c = related.Related_Account__c;
           }
        }
      }
    }
}

The way we do it is we have a nightly Informatica task that builds out a csv for all Account Domain Matching records that are set to Approved.  It pulls down the Domain__c and Related_Account__c into a large CSV.  Then we have another task that runs as part of a Task Flow where it grabs the domain off the lead, looks it up off this csv then if a match is found returns the Related_Account__c (this is the ID of the account) to the Lead.Related_Account__c field that we created on the lead object.  To make this a speedier task it will only grab the lead records where that date/time stamp we have is a greater value then the last time the task ran.

Let’s go see what this looks like in action.

Here we have our Account

And our Related Leads

Currently we don’t have any.

This is our Account Domain Matching Record.

Now let’s create a Lead.

Here would be an example where the website wouldn’t be a great option to auto map as an approved mail domain as everyone from Dan Theman Industries has a mail domain of dandonin.com not danthemandonin.com

Same goes for the Account name, we have it as Dan The man Donin Industries but the field we got either from the form or from whatever enrichment database you might use have the account as Dan The Man Industries so without a very fancy and complex fuzzy logic these wouldn’t have been matched correctly.

Now if we go back to our Account we can see this lead is now related.

Now someone who is working on an ABM account they can go to the account and see all Contact and Leads attached and you can make the related list display whatever information you would like.  So, if you have marketing automation fields you can have that display.  This means you can also build workflows and process builder between the objects now.  When a lead comes in and gets a set lead score it could email the ACCOUNT owner aka the person who owns the ABM functions.

This is ABM Lite, hopefully it can help you start to tackle the challenge of ABM inside salesforce!