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
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.
Same period last year.
dimensionsparameter breaks down metrics by common criteria, such as by
stage(like in our example). When dimensions are requested, values are segmented by the dimension value. If a query has no
dimensionsparameter, 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.
ORboolean 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.
- Equals: Return results for opportunities where amount is exactly
- Does not equal: Return results for opportunities where amount is not
- Greater than: Return results for opportunities where amount is strictly greater than
- Less than: Return results for opportunities where amount is strictly less than
- Greater than or equal to: Return results for opportunities where amount is
- Less than or equal to: Return results for opportunities where amount is
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.
We chose "Opportunities - Query" for our query name.
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.
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"
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"
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.