Overview: Copper Query Builder

In this article, we will explain the options and functionality of Query Builder for Copper through an example. For this example, we'll build a Custom Metric using a Filter the number of Open Opportunities by Google Advertising Source, where the amount is greater than 50.

You can find Query Builder under Data manager ( link to Query Builder) or open it directly from our Designer tool.

add_user_popup@2x

IN THIS ARTICLE

1. Basic Options

A
 Custom Metric Name: Write a name for your Custom Metric in this field. You will find this name/title as a Metric option in the Designer after saving the query.
We chose "Opportunities by Google Advertising" for our query name.
B
 Metric (required): Select a Metric from your connected Copper Data Source, such as Count of Opportunities or Value (Amount). A metric can be used in combination with other dimensions or metrics, but only where valid combinations apply for that metric. 
C
 Dimension (optional): The dimension parameter breaks down metrics by common criteria, such as by Assigne, Stage or Source (like in our example). When dimensions are requested, values are segmented by dimension value. If a query has no dimensions parameter, the returned metrics will provide aggregate values for the requested date range, such as overall number of opportunities or total amount.
D
 Date Range (required): Select up to 8 Date Ranges to use when visualizing this MetricSelected intervals will be enabled for a new Metric.
E
 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: None and Previous period.

2. Advanced Options

Filters (optional): You can filter results by default or custom dimension or by metric. Filters can be combined using OR and AND 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, and if it doesn't match, the row is dropped.

Filter Operators

Dimension Filters:

  • Contains: Aggregate metrics where the selected custom or default dimension contains selected value. In our case Google Advertising
  • Does not contain: Aggregate metrics where the country does not contain selected value.

Metric Filters:

  • Equals: Return results where the amount is exactly the selected value.
  • Greater or equal than: Return results where the amount is greater or equal than selected value.
  • Less or equal than: Return results where the amount is less or equal than the selected value.
  • Greater than: Return results where the amount is strictly greater than the  selected  value. 
  • Less than: Return results where the amount is strictly less than the selected value. 
In our example, we set a metric filter to see only number of opportunities where opportunitie amount is higher than 50.

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

3. 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 for switching date range.

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." Now a new metric has been created, with the metric name the same as the custom query name.

By default, the metric will be added in the datasource where data is pulled. It is possible to change the target datasource by clicking on "Change target data source" on the top right.

Transformations

Rename Dimension:

It is possible to rename dimensions (best practice is to rename them before saving the query) 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  if you want to show data for all Countries except one). Click on  "..." next to "Dimension" and select "Skip dimension".

Round values:

Click on  "..." 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