Configuring Maps using Aggregate Functions - Use Cases

Let’s understand, configuring a map for different use cases.

Scenario 1

For your Sales Opportunity data, if you want to aggregate data based on Segment, Sales Rep, and Lead Source, and track the Minimum ARR, Maximum ARR, Average ARR, Total ARR, and Count of Opportunities for a Sales Rep in a specific Segment or if you want to know the Lead Source for a Sales Rep’s generated Opportunities.

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

2. Select the “Opportunity Data” as the Source Model.

3. Enter a name for this map. For example, “Summarize Opportunity”.

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

5. Specify the Target Model name that you created. For example, “Opportunity Analytics”

6. In the Field column, specify the fields that you want to move from your Source Model - Opportunity Data. In this case add Sales Segment, Opportunity Owner, Lead Source, and ARR.

7. Select Value in the Maps To column for the fields that you want to be copied. In this case, specify Value for Sales Segment, Opportunity Owner, Lead Source, and ARR.

8. In the Target Field column, select the name of the fields defined in the target model. In this case, specify Segment, Sales Rep, Lead Source, Min ARR, Max ARR, Avg ARR, Count of Opportunities, Total ARR.

9. Leave the Default Value column blank for all fields.

10. In the Aggregate Function column, select the following for the respective ARR target fields:

    • MIN for the Min ARR field

    • MAX for the Max ARR field

    • AVG for the Avg ARR field

    • COUNT for Count of Opportunities field

    • SUM for Total ARR

The following image shows the Map configuration for the above use case.

Run Calculation with a Map

You must create a calculation for the above Map. The calculation will run the map.

In the Calculation, select the target model name. In this case, select “Opportunity Analytics”.

Select ExternalSourceMap as the type in the Type column.

Specify the name of the map created. In this case, use “Summarize Opportunity”.

When you run the calculation, you will see that the complete Opportunity data is segregated as per the Map configuration and displayed in the Target Model.

Reviewing Output in the Target Model

Once you run the calculation, you will see that the complete Opportunity data is segregated based on the Sales Segments, Opportunity Owners, Lead Source. Enterprise and SMB. So, based on the fields configured in the Map you would see the data segregated in the output. In this case, as you have mapped the Sales Segment, Opportunity Owner, Lead Source, and ARR source fields with their respective Target fields, you see the values grouped accordingly. You can see the Minimum, Maximum, Average ARR, Total ARR, and Count of Opportunities for all Sales Reps for their Respective Segments. You can also see the Count of Opportunities generated by individual Sales Rep in a specific segment. You can see the lead sources used by a Sale Rep to generate the Opportunity.

Once the data is displayed in columns, you can easily filter data for a specific Sales Rep and track all the details as shown in the above image. For example, if you want to know data for the Sales Rep - Mary, you can just select Mary from the Sales Rep column. Once you select the Sales Rep, you can see the Segments where the Sales Rep has generated the opportunities. In this case, Mary has generated the opportunities in the Enterprise segment, with the total Count of Opportunities generated by her is 8. Out of the total, 7 were generated from the Lead Source “Partner” and 1 was generated from the Lead Source “Partner”. You can also check the Total ARR for Mary along with the other values as Min ARR, MAX ARR, and Average ARR. You can similarly view and track details for any Sales Rep.

Scenario 2

For your Sales Opportunity data, if you want to aggregate data based on Segment, and track the Minimum ARR, Maximum ARR, Average ARR, Total ARR, and Count of Opportunities.

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

2. Select the “Opportunity Data” as the Source Model.

3. Enter a name for this map. For example, “Summarize Opportunity”.

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

5. Specify the Target Model name that you created. For example, “Opportunity Analytics”

6. In the Field column, specify the fields that you want to move from your Source Model - Opportunity Data. In this case add Sales Segment and ARR. As you previously had Opportunity Owner and Lead Source also there in your Map, now update these fields in the Map and specify “None” in the Field column.

7. Select Value in the Maps To column for the fields that you want to be copied. In this case, specify Value for Sales Segment and ARR, while specify Dimension Filter in the Maps To column for the fields with “None”.

8. In the Target Field column, select the name of the fields defined in the target model. In this case, specify Segment, Min ARR, Max ARR, Avg ARR, Count of Opportunities, Total ARR.

9. In the Default Value column, specify “NA” for the Fields with “None”, and leave this column blank for Sales Segment and ARR fields.

10. In the Aggregate Function column, select the following for the respective ARR target fields:

    • MIN for the Min ARR field

    • MAX for the Max ARR field

    • AVG for the Avg ARR field

    • COUNT for Count of Opportunities field

    • SUM for Total ARR

The following image shows the Map configuration for the above use case.

Run Calculation with a Map

You must create and run the calculation for the above Map configuration. For information on how to create and run a calculation, see Run Calculation with a Map.

Reviewing Output in the Target Model

When you run the calculation for the above configuration, you will see that the complete data is segregated based on the segments Enterprise and SMB.

The Segment column only shows values whereas the Sales Rep and Lead Source columns don’t show any value as they were set to “NA”.

So, based on the fields configured in the Map you would see the data segregated in the output. In this case, as you have only mapped the Sales Segment source field with the Segment target field, you see the values grouped by Segment.

You can see the Minimum, Maximum, Average ARR, Total ARR, and Count of Opportunities for the Enterprise and SMB segment.

The aggregate functions help to compile and summarize huge data from a source system into a consumable output that can easily be used for accurate analysis and tracking.