Search syntax: combined queries
Operators (and, or, not)
When searching for leads that match multiple conditions, we can simply specify them one after another, in any order:
city:"New York" calls > 0
calls > 0 city:"New York"
These two queries are equivalent and will find leads with at least one call and where the city field matches New York.
For more advanced queries, use the operators and, or, and not.
The operator and matches if all conditions are true. This operator is always implied (and therefore optional). We can make the search from above more readable by making the implied and more explicit:
city:"New York" and calls > 0
The or operator matches if at least one of the conditions is true:
city:"New York" or city:"San Francisco"
This finds leads that are either in New York or in San Francisco.
The not operator negates the query that follows immediately after it:
not city:"San Francisco" and state:CA
state:CA and not city:"San Francisco”
Both queries match leads where the state is California, but the city is not San Francisco.
Another way to say 'or'
A lot of ors in a search can slow things down:
state:ME or state:NH or state:MA or state:RI or state:CT or state:NY or state:NJ or state:DE or state:MD or state:VA or state:NC or state:SC or state:GA or state:FL
If you want to find leads that match a variety of different values for a single field, you can ditch the ors and format them like this:
state in (ME, NH, MA, RI, CT, NY, NJ, DE, MD, VA, NC, SC, GA, FL)
Now your search will process faster (and it's easier to read too).
Grouping using parentheses
When using different operators, parentheses can be used to group the query:
not city:"San Francisco" and not city:"New York"
not (city:"San Francisco" or city:"New York")
These two queries are equivalent and show all leads that are not from San Francisco and not from New York.
When mixing and and or operators, parentheses should always be used to clarify the meaning of the query:
contact_name:john and (city:"San Francisco" or city:"New York")
(contact_name:john and city:"San Francisco") or city:"New York"
The first search will match leads with a contact named John and are in San Francisco or New York. The second search will match leads from San Francisco with a contact named John…and any lead from New York.
Nested object grouping
You can create searches that match objects that are nested in the lead (emails, calls, opportunities, contacts, etc). Let's say we want to find leads with won opportunities that are worth more than $100:
opportunity_status:won and opportunity_value > $100
This seems like it should work. However, this query is actually looking for leads with any won opportunity and any opportunity where the value is greater than $100. In addition to pulling in leads with won opportunities that are worth more than $100, you’ll get some other results. A lead with a $200 lost opportunity and a $50 won opportunity would match too.
To create more targeted searches, you’ll need to use nested grouping. Nested grouping allows you to limit multiple search conditions to a specific nested object.
Put another way, nested grouping allows you to combine multiple search questions (“Does the lead have a won opportunity? Does the lead have an opportunity worth more than $100?”) into one more specific question (“Does the lead have a won opportunity that’s worth more than $100?”).
Building a nested object search
Provide the name of the object type (contact, task, opportunity, call, note, email, etc.) followed by parentheses:
Inside the parentheses put the object-specific query:
opportunity(opportunity_status:won and opportunity_value > $100)
You can remove the object type prefix (opportunity) inside the parentheses to make the query more compact and readable:
opportunity( status:won and value > $100 )
Now we get what we originally wanted: leads where both conditions match on a specific opportunity.
We want to see all leads that have calls made by John which were longer than 30 seconds and contained the text “happy” in the call note:
call( note:happy by:John duration > 30s )
Since happy is in the parentheses following call, happy will only be searched for in call activities. Leads that only have happy appear in other object types (like a note or an email) won’t match. Since happy won't trigger a match outside of a call activity, we can just use full text search inside the query:
call( happy by:John duration > 30s )
Let's try another one. This query matches leads who have replied to our template "Initial Outreach" sent by John during the first week of April:
email(direction:sent template:"Initial Outreach" user:John sent >= 2014-04-01 sent <= 2015-04-07 has_reply:yes)
Multiple field sorting
We can sort by multiple fields separated by commas:
This will show leads with the lowest priority value first, and then search within each priority group by descending number of activities (most activities first).
Smart view queries
Frequently used search queries can be saved as smart views. Sometimes it's useful to create a search or a new smart view based on an existing smart view. Instead of repeating the query belonging to an existing smart view, you can use the in keyword:
in:"Interesting Leads" and state:CA
This will match all leads that are currently in the Interesting Leads smart view and are located in California.
Limiting number of results
Splitting up / distributing results
The slice keyword splits a list of leads into separate chunks. It’s a great keyword to use when you want to make sure different people aren't working on the same leads. (Dividing up existing Smart Views with slice is a great way to assign leads without any manual work.)
Let’s start off with a smart view called Hot Leads and a group of 3 people who want to work with those leads. Slice can help us separate the Hot Leads results into 3 groups (without overlapping). Person 1 can use this query to get approximately one third of the Hot Leads results:
slice:1/3 in:"Hot Leads"
Same for person 2:
slice:2/3 in:"Hot Leads”
Person 3 takes a different approach and only wants to concentrate on the Hot Leads that have been called by Joe:
Even though the search above has extra criteria, by adding in slice:3/3, you are guaranteeing that none of Person 3’s leads show up in the earlier two searches.
Note: the number of leads in each "slice" may not be exactly equal. Slice is more concerned about making sure the same lead never appears in different slices (even there are additional queries added in after the slice keyword).
Lead/opportunity status range search
We can perform historical searches on lead and opportunity statuses using keywords lead_status , opportunity_status (and some parentheses, of course). The inner query (the bit inside parentheses) accepts the keywords status, from, to, after and before.
This will match leads that were in status Trial any time in January:
lead_status(status:Trial from:"January 1st" before:"February 1st"
Let’s get more specific. Here are leads that were in status Trial on January 1st and ended up being in status Customer on February 1st:
lead_status(status:Trial on:"January 1st") and lead_status(status:Customer on:"February 1st")
Opportunity statuses are also supported and can also be contained in a nested (parenthetical) query. Use this to match leads with opportunities in status Active on January 1st and moved to status Won by February 1st:
opportunity( status(status:Active on:"January 1st") and status(status:Won on:"February 1st") )
Time zone and local time searches
timezone keyword searches for leads that has a phone number/address that matches a specific timezone, the
local_time keyword can show leads that are in a specific time window:
local_time > 9am local_time < 5pm
You can also add in a sort keyword to order your matching leads from east to west:
Timezones can be tricky. A couple of things to remember:
A few notes about time zones:
- A lead can be associated with multiple time zones if it has multiple addresses or phone numbers with different area codes.
- If you know that either phone numbers or addresses will be a better timezone predictor, add phone_ or address_ to your timezone/local_time keyword (e.g. phone_local_time , address_timezone).
- When you search for a particular local time, we find leads where any of their time zones match the time zone / local time you requested.
- Keep in mind that the "Local time" column is not capable of showing multiple local times. If multiple time zones are associated with a lead, one local time is displayed at random.