When building reports, you may find it useful to organize attribute values into buckets in which multiple attribute values are combined into a single number. For example, you want to organize attribute values for U.S. States (California, Oregon, Washington) into regional buckets (Western U.S. States). The goal is to reduce the complexity of a report and to provide easier ways to digest or organize information.
This intermediate tutorial assumes some knowledge of MAQL. For more information, see MAQL - Analytical Query Language.
In the following example, you can see a sum of Won Opportunities split out by Region:
Many of the Accounts are located in different regions of Europe, the Middle East and Africa (or EMEA), as well as areas of North and South America. These breakouts may be easier to display in a report by organizing the attribute values into buckets for each general region.
Buckets are defined via MAQL as part of individual metric definitions.
This solution is useful if you are creating buckets for use in a few metrics and reports. If you need to create buckets to apply across a large number of metrics and reports, you should consider modifying your source data, data model, and ETL processes to support this usage. Contact your project administrator for more information.
Accounts in the EMEA region may be grouped into a single bucket. Create the following metric (# Won Opps - EMEA) using the # of Won Opps metric, sliced by the attribute values in the region:
SELECT # of Won Opps. WHERE Account Region IN (EMEA - Balkan,EMEA - Benelux, EMEA - East Europe, EMEA - France, EMEA - Iberia, EMEA - Middle East, EMEA - Nordics, EMEA - Others, EMEA - UK&Ireland)
- Create metrics for similar buckets for the other regions. These metrics use the same metric syntax as in the above metric, except that you are including only the regions specific to that metric:
- # of Won Opps. - North America
- # of Won Opps. - South and Central America
- # of Won Opps. - Asia-Pacific
- # of Won Opps - Other
- Verify that each attribute value is included in one (and only one) of the metrics.
After all metrics for regional buckets are defined, you can define the report.
Since the new metrics essentially create attribute slices in the report, there is no need to include the original attribute, Account Region, in the report. Each region is now defined by its own metric.
- The end result is a report providing a high-level view of the accounts by region:
If you need to re-apply a set of attribute values across other metrics, you can use copy and paste in the Custom Metric Editor to ensure consistency across your metrics. Note that any changes to these attribute values must be propagated across all metrics specified in this manner.