Salesforce: Query Builder

In this article, we will create a custom query using the Salesforce Query Builder in order to highlight the features and functionality of the tool.

For this example, we will be creating a new metric that will give us all opportunities from Month to date, where a stage is not equal to Closed Won and amount is greater than 10000.

IN THIS ARTICLE

1. Basic Options

A
 Custom Query Name: Create a name for your custom query in this field. You will find this name/title as a metric option in the designer after saving the query.
We chose "Opportunities" for our query name.
For basic setup we choose Builder option for creating custom query. SOQL query will be used in Importing data by SOQL topic.
B
 Select Object (required): Select a object from your connected Salesforce data source such as account, lead, opportunity, partner, etc.
You can find more information about Objects here.
C
 Date Range (required): To display your data the way you like, you will need to choose time intervals. Just click on the "Date range" field. Selected intervals will be enabled for a new metric. 
D
 Compare With (optional): Select a date range to compare your previously selected interval to. This date range will be used for "previous line" on line and bar charts, and for calculating change %.
Options are:  NonePrevious period and  Same period last year.
E
 Date Field (required): Select a date by which results will be sorted such as close date, created date, last modified date, etc. 
There are different Date field options for every Object.
F
 Data Type (required): Select a data granularity.
Options are:  Daily values and   Total values.
G
 Metric (required): Select a metric from your chosen object. A metric can be used in conjunction with dimension. We used amount from Opportunities.
H
 Metric Operation (required): Select an operation which will be delivered to results from selected object.
Options are:  SUM,   COUNT,   AVG,   MIN and   MAX.
I
 Dimensions (optional): The  dimensions parameter breaks down metrics by common criteria, such as by  name or  stage (like in our example). When dimensions are requested, values are segmented by the dimension value. If a query has no  dimensions parameter, the returned metrics will provide aggregate values for the requested date range, such as overall reach.

2. Advanced Options

In our example, we set filters in order to not retrieve data for Closed Won stage and retrieve data for opportunities with amount greater than 10000.
Filter by (optional): You can filter results by dimensions or by specific metrics. Filters can be combined using the  AND and  OR boolean logic. Filtered queries restrict the rows that do (or do not) get included in the result. Each row in the result is tested against the filter. If the filter matches, the row is retained. If it doesn't match, the row is dropped.

Filter Operators

Metric Filters:

  • Equals: Return results for opportunities where amount is exactly 10000
  • Does not equal: Return results for opportunities where amount is not 10000
  • Greater than:  Return results for opportunities where amount is strictly greater than 10000
  • Less than:  Return results for opportunities where amount is strictly less than 10000
  • Greater than or equal to:  Return results for opportunities where amount is 10000 or more
  • Less than or equal to:  Return results for opportunities where amount is 10000 or less

3. Importing data by SOQL

Sometimes data from Builder won't be enough. For this case, we add an option to pull data with SOQL Query which uses SOQL (Salesforce Query Language). It is very similar to SQL or MySQL, so if you know basics of one of this two it won't be hard for you, to pull some data. To write code just click SOQL Query button under Custom Query Name.

A
 Custom Query Name: Create a name for your custom query in this field. You will find this name/title as a metric option in the designer after saving the query.
We chose "Opportunities - Query" for our query name.
B
 Text Box for code (required): Write your code in here.
You can find more information about SOQL here.
C
 Data type : We need to know what data are you querying, so we give you option to define data.

Options are:   Unprocessed data (event value) and Processed current data (current value).

When everything mentioned above is set, click "Run query" to show Data preview.

4. Data preview

In Data Preview you will see all values for selected metrics and date ranges. At the bottom you can check total values. Just click on tabs to switch between date ranges.

If you want to sort values by date/time just click on the arrow next to "Date" 

When your query is ready, click "Save query." This creates a metric in Databox, with the metric name the same as the custom query name (Opportunities).

By default, a metric will be added within the data source that the data is pulled from. It is possible to change the target data source by clicking on "Change target data source" in the top right of the screen.

Transformations

Rename Dimension:

It is possible to rename dimensions. Best practice is to rename them before saving the query. You can do this by clicking  "..." next to "Dimension" and then selecting "Rename dimension"

Skip Dimension:

This function is useful when you want to exclude one or more dimensions. For example, this would be useful if you want to show data for all stages except one. Click  "..." next to "Dimension" and select "Skip dimension"

ROUND VALUES:

Click  "..." next to "Value" and select "Round values." Enter the number of decimal places to be rounded to into field in popup, and values will be rounded accordingly.

Still need help? Contact Us Contact Us