Widget Builder: Applying Filters

Filter Setup for Custom Widgets

Dynamic Filter Logic

Dynamic filters are designed to coexist with filters that are hardcoded in the SQL. Note that if you hard code a filter and then apply it as an optional filter, both the optional filter (based on selection made when viewing the dashboard) and the hard coded filter will apply simultaneously, this is not a good idea and will lead to confusion.

There are two types of dynamic filters: optional and required. Required filters are filters that must be set to some value at all times (dates, attribution/scoring model etc.). Optional filters are not required to be set, and if they aren’t set, will not appear in the SQL

In order for the widget to produce valid SQL the corresponding table must be joined anywhere you apply filters. Custom widgets do not auto detect filters and automatically add the necessary joins. 

NOTE about limiting joins: In BigQuery joins are always executed. Thus if you join cm.cm_insights_attribution to cm_channel, but fail to use any columns from cm_channel within the query outside of the join, Big Query will still scan one column in cm_channel and apply the join. Removing unnecessary joins is a way to increase query performance.

Required Filter Logic

Required filters are designated as such because they do not allow null filter values to be submitted. Once added to SQL they will automatically apply the default org value for the Organization Default Filter Set.

Optional Filter Logic

Filter availability for optional filters is determined at the CM table level. These include:

• cm_campaign
• cm_companystats
• cm_eventstats
  • The cm_eventstats table also contains all data from cm_channel including Channel and Sub-Channel
• cm_item
• cm_opportunity
• cm_performance
• cm_person

Applying Dynamic Filters

To insert a dynamic filters into a SQL query use the following format:

-- This goes in the widget editor

NOTE:

• The dynamic filter block assumes it is the first line in the where clause. This is important for proper SQL generation. For example, the above code block might generate this SQL:

Inserting in a hardcoded filter should be done AFTER the dynamic filter code block. For example:

Might generate the following SQL:

Flipping the dynamic filter code block and the hard coded SQL will result in this SQL being generated which is not valid and will cause an error:

Applying Date Filters

The date filter is a unique filter in that it is usable across many report types, but needs to be able to reference different date columns. In order to do this you must alias the desired date column you want to filter on to “filter_date.” When date filters are applied the SQL generated will look like this: 

The recommended way to do this is by first creating a cte, and aliasing the column within the cte:

The benefits to using this technique are largely around simplicity without increasing query costs. Normally select * is frowned upon however using a select * in a cte like this BigQuery will automatically only select the columns needed to run the query.

NOTE the SQL assumes the filter_date is either already a date type or a type that can be cast into a date. To ensure stability and reliability of widgets, it is highly recommended to cast the date filter into a date type instead of relying on the casting done in the filter itself.

Alternative Method For Applying Date Filters (Attribution ONLY)

Most system (non-custom) widgets focused on attribution support a dynamic “date type” filter that allows users to switch between opp create date, opp close date, and opp touch date. This method explains how to incorporate both a date filter and the date type into customer attribution widgets.

Example:

With df as (
SELECT
*,
date_trunc(opp_cm_created_date, MONTH) as filter_date, — For using create date as filter
— date_trunc(opp_cm_close_date, MONTH) as filter_date, — For using close date as filter
FROM cm.cm_insights_attribution
)
Select
cast(sum(touch_value) as STRING FORMAT’$999,999,999,999.99′)
FROM df
WHERE
{{filters:datePeriod|attributionModel}}

The following example contains magic sql slugs:

• {{filters:dateType}}
  • This required magic SQL slug is used to control the data fitlers in the where clause of a sql query. It is designed to be used as an alternative to the {{filters:datePeriod}} slug
  • When the Opportunity Date Category filter is set to Created Date, the Sql inserted is:
    • CAST(cm_insights_attribution.opp_cm_created_date AS DATE) BETWEEN '2025-10-27' AND '2026-02-24'
  • When the Opportunity Date Category filter is set to Closed Date, the Sql inserted is:
    • CAST(cm_insights_attribution.opp_cm_close_date AS DATE) BETWEEN '2025-10-27' AND '2026-02-24'
  • When the Opportunity Date Category filter is set to Touch Date, the Sql inserted is:
    • CAST(cm_insights_attribution.response_datetime AS DATE) BETWEEN '2025-10-27' AND '2026-02-24'
  • NOTE
    • The strings outlined above are hardcoded; meaning to use these there must be a table aliased as cm_insights_attribution. If there is no table with that alias, the widget will throw an error
    • The cast() function is hard coded in as a safety measure to minimize the chances of invalid sql being written that cannot be easily controlled via sql
• {{dateTypeColumn}}
  • This OPTIONAL magic SQL slug is used for when you want to use the date as a dimension on a table OR as an axis for a chart.
  • When the Opportunity Date Category filter is set to Created Date, the Sql inserted is:
    • cm_insights_attribution.opp_cm_created_date
    • In the example above line 2 becomes: date_trunc({{cm_insights_attribution.opp_cm_created_date}}, MONTH) as Date,
  • When the Opportunity Date Category filter is set to Closed Date, the Sql inserted is:
    • cm_insights_attribution.cm_insights_attribution.opp_cm_close_date
    • In the example above line 2 becomes: date_trunc({{cm_insights_attribution.cm_insights_attribution.opp_cm_close_date}}, MONTH) as Date,
  • When the Opportunity Date Category filter is set to Touch Date, the Sql inserted is:
    • cm_insights_attribution.cm_insights_attribution.cm_insights_attribution.response_datetime
    • In the example above line 2 becomes: date_trunc({{cm_insights_attribution.cm_insights_attribution.cm_insights_attribution.response_datetime}}, MONTH) as Date,
  • NOTE
    • When using this method there is no need to create a cte to alias a date column to filter_date
    • The strings outlined above are hardcoded; meaning to use these there must be a table aliased as cm_insights_attribution. If there is no table with that alias, the widget will throw an error
    • The table.column path was intentionally not auto-wrapped in a cast function to preserve flexibility.

Required Filter Details

Attribution model

--Widget builder SQL looks like this:

--Generates SQL that looks like this:

Engagement model

--Widget builder SQL looks like this:

--Generates SQL that looks like this:

Funnel model

--Widget builder SQL looks like this:

--Generates SQL that looks like this:

NOTE: The Filter Drawer will display a list of Funnel Model Names and not IDs.