Skip to main content

​
Step 1: Map CRM deal owners to AEs

To align commissions to individual AEs, you’ll need to connect CRM data to HRIS data.
  1. Create a new database called Account Owner Mapping. Select your Sales Team database as the source and segment by Name. Commissions Data Config Pn
This will create a line for each Name value in the Sales Team database.
  1. Add a new dimension column and search for Deal Owner. Naming may vary depending on your HRIS integration. You can always check your Deals database for the exact dimension name. Setdealowner Pn
  2. For each relevant row, select the matching Deal Owner. Skip rows for manually added new hires—they won’t exist in your CRM data. Account Owner Map Pn
  3. In your Deals database, add a Lookup for the **Name **column. Name Column Lookup Pn
This auto-matches the records for each line. You can think about this as a human-readable VLOOKUP() from Excel. For tidiness, you can hide one of the columns by right-clicking the column header and selecting Hide in block. Commisions Lookup Pairing Pn
Note: As new employees are hired, you’ll want to revisit the Account Owner Mapping to connect their HRIS record to their CRM record.

​
Step 2: Add commission rate logic

Create a new number driver column in the Deals database called Commission Rate.

​
Option 1: Flat rate commission

To set a flat commission rate %, you can select Add default formula from the column header sub-menu and hardcode a percentage. Flatcommission Pn

​
Option 2: Variable commission rates

This example demonstrates how you can set assumptions based on dimension values. In this example, New Business deals earn an 8% commission, whereas Renewals earn a 3% commission.
  • The formula references the dimension column DEAL_TYPE which only contains the two items: New Business or Existing Business .
  • The logic of the formula reads: _if DEAL_TYPE = New Business apply an 8% Commission Rate, otherwise apply 3% Commission Rate. _ Newbusinessvsrenewals Pn
Since commissions data is part of compensation, many model owners chose to anonymize commission rates and payouts. To do so, right-click on the driver column header and toggle **Anonymize data. **You can read more about Anonymizing data here

​
Step 3: Add payout timing logic

In many organizations, commission payouts are deferred until a contract has been active for a set period. Create a new number driver in your Deals database called Payout Date. Use dateAdd() to add your delay to the deal’s close date. In this example, we assume a 90-day delay. Delaypayment Pn Check the values by clicking the Commissions Paid driver column header and selecting Show as time series. This view allows you to check whether the amounts paid land in the correct months. Commisions Paid Time Series Pn

​
Step 5: Aggregate commissions by AE

The last step is to aggregate commissions paid by AE. In your Sales Team database, add a number driver for **Commissions. **Set the formula to sum the Commissions Paid driver in your Deals database where Name is This Name. Formulathissegmentcommissions Pn By showing the driver column COmmissions as a time series, we can verify the commissions roll up appropriately. Commissionssalesteam Pn