Previously, we showed you how you can analyze your sales commissions using advanced suites such as Tableau or Microsoft Power BI. However, a full-fledged Business Intelligence tool might be overkill if you don’t require advanced analytics. Also, familiarity with Excel probably makes it a compelling choice for most sales compensation analysts. In this blog post, we’ll show you how you can connect Microsoft Excel to your Sales Cookie workspace to retrieve and analyze sales commissions.
Your Sales Cookie environment is where all your commission data lives (incentive plans, rep dashboards, commission calculations, attainment thresholds, payout rates, etc.). Using Excel, you can easily connect to your Sales Cookie cloud environment and retrieve commission data. To analyze payouts, you only need to use a few of the available data tables. For a complete list of tables, see this list.
- Plan – Plans represent commission structures. Each plan has a set of eligibility, crediting, attainment, and payout rules.
- Calculation – Calculations determine commission payouts for one specific plan (ex: Quarterly Bonus) and period (ex: Q4).
- CalculationCommission – Calculation commissions track per-transaction payouts. Calculation commissions do not track payouts which are not transaction specific (ex: cash bonuses).
- CalculationCredit – Calculation credits track which transactions were credited to which users / teams. Calculation credits are also used to determine attainment.
- CalculationResult – Calculation results summarize payouts per payee. Calculation results do not track commissions at the transaction level.
- SystemUser – System users are users within your workspace. They can be admins, payees, etc.
Each table is linked to other tables. For example, each record in the CalculationCommission table points to a beneficiary (aka payee) within the SystemUser table. Don’t worry if those relationships look a bit cryptic. We’ll walk you through everything!
Connecting Excel To The Cloud
Connecting Excel to your Sales Cookie environment is super easy! First, login to your Sales Cookie account, and copy your API URL from your profile.
Next, open Excel and select this option.
Paste your API URL and Excel is connected to your Sales Cookie environment! Excel will show you all available tables. Sales Cookie is a powerful platform with open APIs, so all kinds of tables are available. However, to analyze commissions, you’ll only use a few tables mentioned previously.
As an example, we’ll only select the CalculationCommission table. This table stores per-transaction commissions. Excels shows us sample data within the CalculationCommission table. It also gives us the option to first transform the data. Let’s do this!
Each CalculationCommission record refers to other tables. For example, each CalculationCommission record points to a payee within the SystemUser table (via its BeneficiaryId field). Our goal is to retrieve payee email addresses and first/last names. To retrieve fields from related tables, choose this option.
Retrieving Data From Related Tables
Here is what our CalculationCommission table looks before transformation. As you can see, some of the fields, such as Value, have actual data (here Value means payout). Other fields look more like IDs because they reference other tables.
If we scroll to the right, we can see related records like this:
To pull fields from related records, all we have to do is click on the “split arrow” icon. This allows us to expand the related record, and select fields from it. Below, we expanded the Beneficiary linked record to grab its email address and first/last name.
Awesome – we can now see fields from our CalculationCommission table AND the related table!
We’re done with our data transformation. Let’s close the editor and load the spreadsheet.
Here are the first 3 rows of our loaded spreadsheet (with fields from the CalculationCommission and related table). Now we can clearly analyze who got paid what.
Our Excel spreadsheet has a live connection to the Sales Cookie cloud environment, so we can refresh its content with just one click.
The Sky’s The Limit!
We can now create all kinds of commission reports using a tool we’re very familiar with – Excel. For example, we can run forecasts, review data insights, add pivot tables, and build charts such as this Pareto chart.
If you have hundreds of payees, manually generating commission spreadsheets is a time killer. You won’t be able to create global reports spanning multiple payees or periods. And if a single commission spreadsheet changes, your reports becomes invalid, and need to be re-generated. Using Sales Cookie, you can calculate commissions across hundreds of payees, with then analyze everything live from Excel. Visit us online to learn more!