Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Create Transaction Tables for CRM Data

Only available with Adaptive Insights for Sales

For Sales Planning domains, you can have up to five transaction tables. Use transaction tables to store data from your customer relationship management system (CRM).

Each transaction table can represent the transaction type you import. Name the tables whatever makes sense and then import data from your CRM. For territory planning, the tables flow data into the cube sheet that powers territory planning charts. 

Before You Begin

Understand where you are in the overall Territory Planning setup: 

  1. (You are here) Create a transaction table or a modeled sheet and import your CRM data into it. 
  2. Create custom dimensions for sales reps and geography. 
  3. Create a modeled sheet to assign territories to sales reps. 
  4. Create a cube sheet for opportunities. The sheet display sales opportunities per sales rep based on your CRM data and your territory assignments.
  5. Complete Object Modeling to bring the CRM data and the territory assignments together in the cube sheet. 
  6. (Optional) Create your territory map charts and other charts.

How You Get There

From the nav menu, select Modeling. In the Others menu, select Transactions.

Transactions Tables

Transaction Tables Overview

From the Transaction Tables Overview page you can:

1 Edit or Rename a transaction table. The DEFAULT transaction table cannot be renamed.

2 Click a table's name to edit its transaction definition fields in the transaction builder.

Transaction table names cannot contain special characters ~!@#$%^&()_. They prevent transaction data import.

Basic Steps

The transaction tables you create for territory planning require certain columns:

  • Dimension columns for the sales rep and geography dimension you created. 
  • Columns that map to the CRM fields you want to import and also the cube sheet accounts you will create in the opportunities cube sheet.
  • A number column that identifies the source data.

To create the transaction table:

  1. Prepare a mapping key for the CRM fields, the transaction column names, and the cube account codes you are going to create for your opportunity cube. 
  2. Add territory planning dimensions to the table. 
  3. Add columns for the CRM data. 
  4. Add an ID column for the source data. 
  5. Save the table and import the data.

Prepare a Mapping Key

To map the transaction data to your opportunity cube sheet, the name of the columns must exactly match the cube account codes. You likely have not yet created your cube sheet accounts, but you can prepare ahead of time for what your account codes will be. 

How Account Codes are Created

Cube account codes have two identifiers: the sheet code and the account code, separated by a period. For example, OC.FARR is a complete account code. The prefix identifies the sheet and the rest identifies the account. 

When you create cube sheets, you must enter an account prefix code. This code becomes the prefix of all accounts you create for the cube. In general choose initials or abbreviations of the cube sheet name for prefix code. For example, you might name your opportunity cube Opportunities. You might choose OC for sheet's prefix. 

When you create the cube accounts, you must enter a code and name for each account. The account names for your opportunity cube sheet must align to the field names of your CRM data. You may have a field for forecasted ARR. For the cube account then, use a name like Forecasted ARR. For the code, use FARR

The full cube account code will be OC.FARR

Map the Transaction Fields to the Codes

  1. Choose the cube sheet code. Keep it short. 
  2. List the field names of the CRM data you want to import and create abbreviations or initials for each field. You will enter these codes for the opportunity cube sheet accounts.
  3. Combine the sheet code and the account codes, divided by a period. These will be the names of your transaction table columns. They should look like OC.FARR, for example.
  4. Save your list in an excel file. Keep it handy so you can reference it when you create the transaction table, the opportunity cube sheet, and object modeling.

Sample mapping key:

CRM Field Code for Cube Account Name for Transaction Column
Forecasted ARR FARR OC.FARR
Customer Ccount CustCount OC.CustCount
ARR ARR OC.ARR
Opportunity Count OppCount OC.OppCount

Add Dimensions to the Table

  1. From the Elements pane, select Dimensions
  2. From the list of dimensions, drag and drop the Geography and Sales Rep dimension into a row.  
  3. From the left pane, select Back to Elements.

Add Columns for CRM Data

  1. From the left pane, select Data Entry Columns
  2. For each of the data fields you want to import from your CRM, drag and drop a Number column into a row. 
  3. Select a Number column from the canvas. In the right panel for Name, enter the account code you created for that field using your mapping key.
  4. Repeat for each Number column until you have columns for each of the CRM fields you want to import.

Add an ID Column

  1. Drag and drop one more Number column into the canvas.
  2. For name enter the ID associated with the data source. You'll need this for the object modeling step. 
  3. Save the table. 

The example below shows the transaction columns for a CRM transaction table:

Sales Transaction Definition Example

1 A Geography column as a dimension.  

2 A SalesRep column as a dimension. 

3 An OC.FARR number column. The name matches the future cube account of an Opportunities cube sheet.

4 An OC.OpportunityCount number column. The name matches the future cube account of an Opportunities cube sheet.

In the following example, the cube sheet builder shows the cube account codes:

Opportunities Transactions Example - Cube Accounts

  • Was this article helpful?