Using Aggregate Functions in Maps

The Aggregate functions are now available in the ESM Maps. You can use these functions directly while moving the data from one ESM to another ESM through maps. The Aggregate Function column has been added to the Maps.

The following Aggregate functions have been included in the maps:

• COUNT

• SUM

• MIN

• MAX

• AVG (average)

The following table provides a quick overview of the functions.

The Aggregate functions were not available in the Maps.

You can use the Aggregate functions, when you have a model with a lot of data and want to compile and organize the raw data into a more consumable and summarized format.

Business Example

If you have a model containing data about Sales opportunities. The model can contain raw data about leads, different sales owners, sales segments, annual recurring revenue (ARR), and so on.

Suppose you want to aggregate data and find specific data related to a sales segment or sales owners. In that case, you can use the aggregate functions available in the map and pull only relevant data to another ESM. The functions provide the flexibility to organize data and identify different key values such as maximum value, minimum value, total count, average, etc.You can create a different model with only relevant fields that are required. The following image shows the model created with only relevant fields.

You can create a map with your actual ESM as the Source Model and the new model as the Target model. The following image shows the map containing different fields such as Sales Segment, Opportunity Owner, Lead Source, ARR.

You can group the data as per your requirement and configure the fields accordingly in the Maps. You will have the Aggregate Function column available in the map to select the required function for getting the output. In the example above, the data is grouped by Sales Segment, Opportunity Owner, Lead Source, while the ARR fields are used to find the minimum, maximum, count, average, etc. So, when you run the calculation, you get summarized data that only provides information about the relevant fields.

If you want to filter and segregate data only for a particular field, you can easily do it. For example, you can easily find and track the count, sum, average of opportunities generated by a specific Opportunity Owner.

You can directly find the key point values such as sum, count, maximum, minimum, and average while moving the data from one ESM to another. The functions significantly reduce the effort and time required to organize the data and find key values. You have the flexibility to customize the fields in the map based on your requirement and see different outputs with just a click of a button. You can map a single source field to multiple target fields as per your requirement. For example, ARR was mapped to multiple target fields like sum, count, etc. It is not mandatory to map all the source fields in the map. For example, if you do not want to track data for Lead Source in the example above, then you can just update the Default value for that field to NA and update the Maps to Column value to DimensionFilter. The calculation will aggregate the data based on the updated values and you will see data only based on the Segment and Sales Rep.

In Practice: To use the Aggregate functions in Maps

1. In SpotlightXL, select the Model task and the Map subtask.

2. Select the name of the Source Model defined on the Source Model page.

3. Enter a name for this map.

4. Add the required description.

5. Select the type Data for loading external source data.

6. Select or specify the name of the ESM you want to map the Source ESM to.

7. For Append Missing Dimension Members , select No when mapping to another external source model.

8. Optional, specify the name(s) of one or more fields in the Source ESM to use for filtering the data you want to load. Under the Value column, you can specify a leaf-level field.

9. In the Fields column, specify fields from the Source Model that will be mapped to fields in another External Source Model.

10. Select any of the following in the Maps To column:

    • DimensionFilter : indicates that there is no field in the Source ESM to map to the field in the Target ESM. If you select DimensionFilter, you must specify the Default Value column.

    • Value : indicates that this field in the external source is a value to be copied as is to the specified field in the target ESM.

11. In the Target Field column, select the name of the field in the target ESM to correspond with this field in the source ESM

12. In the Default Value column, specify the text, date, or number that should be placed into this field for all rows in the target ESM if Maps To is DimensionFilter. Or, leave this column blank if Maps To is Value.

13. In the Aggregate Function column, select the required function such as COUNT, MIN, MAX, etc.

Business Use Cases

Let’s understand using the Aggregate functions with some examples.If you have a model containing raw data about leads, different sales owners, sales segments, annual recurring revenue (ARR).

If you want to track your Sales opportunities based on different parameters, for example, you can know the maximum ARR based on Sales Segments, you can know what the opportunities are based on a specific lead source, and many more.

You must perform the following:

• Create a target model with the required fields.

• Create a Map to move data for relevant fields from your Source model to the Target model.

• Select the required Aggregate function.

• Run the calculation to view the output.

Creating a Target Model with Relevant Fields

You must create a Target Model to view and track the data for relevant fields based on your requirement. In this case, as you want to track Sales Opportunities based on different parameters, you can create your Target Model to have fields like Segment, Sales Rep, Lead Source, Minimum ARR, Maximum ARR, and so on.

To do that, navigate to SpotlightXL and create an External Source Model. For example “Opportunity Analytics”

Add the required fields to the Field column of your “Opportunity Analytics” model. If you want to segregate data based on specific fields, then include only those fields to your model. For example, Segment, Sales Rep, Lead Source, Minimum ARR, Maximum ARR, etc.

Once you add fields, specify the type in the Type column. For example, Text, Numeric, Date, and so on. Based on the data in your Source ESM, set the type as Text or Numeric. For example, Segment, Sales Rep, Lead Source are of Type - Text, and the different ARR fields are of Type - Numeric.

In the Include in Data Load column, specify Yes as the value is coming directly from the data source.

Once you have created the Target model and set all the required fields, navigate to creating a Map.

Creating and Configuring a Map

You will be creating a Map to pull the data from your source model to your target model. You must define the Source Fields and then map them to the respective Target Fields. Once you define the source and target fields based on your requirement, you can select the Aggregate functions for the fields. By selecting the Aggregate functions in the Map, you can Aggregate the data as per your requirement while simultaneously moving the data from Source Fields to Target fields. You have the flexibility to configure only the fields that you want to see in the target output.