Custom tables are used to perform a breakdown of your data on an attribute other than the account. Examples of custom tables would be: a custom table to track data by sales rep, a custom table to track opportunities by type, a custom table to track each separate form submitted on your website.
Creating a Custom Table
Let's walk through the creation of an example custom table. This custom table will track metrics by Sales Rep.
Step 1: Add Custom Table
To create the custom table, go to Settings --> Tables. Click on "Add Custom Table".
Step 2: Name the table, and choose object
Name your table, and choose which object to create the table from.
Let's name this table "Sales Reps".
The object to create the table from, is the object that has the field which will represent the rows in your table. So in this case, it’s the Salesforce User object, since we want rows in our table to be various Salesforce Users -- these are our Sales Reps.
Step 3: Choose a Primary Key
Next, we choose a Primary Key.
The Primary Key is the field that has the rows you want your table to be made of. We want to use the Name field for our example, so that the rows in the table are all the different names of our Salesforce Users. These are our Sales Reps.
Now, we click Create Table and our table will be created. The table won't have any useful information in it yet though. Right now, the table just contains rows, but no columns. The next step will be to create columns -- these are metrics that are specific to that custom table.
Creating Metrics for a Custom Table
To create metrics on a custom table, we need to use the Advanced tab of the metrics creation screen. Let's walk through an example where we create a metric that tracks the number of Contacts Created by Sales Rep on our "Sales Rep Example" table.
Step 1: Create Metric Advanced
Step 2: Display Name and Description
The Display Name is the name that will show up throughout the UI when you are looking at this metric. In the case of custom tables, you will often be creating metrics that also exist on the Accounts table, like Bookings. To avoid confusion, it is recommended to put your custom table name in brackets in front of the display name you choose. So, here, we can call this metric [Sales Rep Example] Contacts Created.
The Description is a tooltip that will show up to all users in your org when they hover over this metric. Best practice is to describe the calculation and any relevant groupings in your Description. Here, we will create a description that says "Number of contacts created, grouped by Owner, grouped by Created Date".
Step 3: Choose Family
The Family is the object that has the data that will be going into our metric. In this case, since we're looking to calculate the number of contacts created, the family we will use is the SFContact.
Step 4: Choose Reporting Type
The Reporting Type determines if this metric will be created in Time Series, or Summary format. Time Series format will allow you to see different values for this metric, depending on the time period you are looking at, while Summary format will just store one overall value for the metric, for each row. More information on Time Series and Summary Metrics is found here.
For this particular metric, we will choose to create a Time Series metric, so that we can see the number of contacts created by each sales rep over different periods of time, depending on the period we want to look at.
Step 5: Choose Reporting Table
The reporting table is the table that we want this metric to be displayed on. Since we want to see the number of contacts created by sales rep, we will choose the "Sales Rep Example" table. This means that we will be adding this metric to the Custom Table we created for "Sales Rep Example".
Step 6: Aggregator and Formula
The Formula, when combined with the Aggregator, tells Rekener what to store as data for this field. For this particular metric, we want an Aggregator of COUNT, since we just want to count the number of records created by each sales rep. That means that first we need to set the Data Type at the bottom of the screen to Integer, and then we will be able to change the Aggregator to COUNT. Since the Aggregator is set to COUNT, we do not need a formula -- the system will just count all of the contact records.
Step 7: Condition
The Condition would restrict which Contact records go into this calculation. Since we just want to see the total number of contacts created by each rep, we can leave the Condition blank so that we will count all of them.
Step 8: Date Grouping
We will group this metric by Created Date. This means that whichever date range we look at, we will only display the number of contacts that have a Created Date between that date range.
Step 9: Sales Rep Example Grouping
The last step is to tell the system how to map this field to your custom table rows. In essence, you are telling the system how it knows which sales rep to associate a given contact with. What we want to do here is to group it by the Contact Owner's name. So, whatever value is in the Contact Owner's name field, is the row that the contact will be associated with in our Sales Reps Example table. So, for a given contact, if the Owner's name is Bob Jones, then Bob Jones will get credit for that Contact Created.
The way we get access to the name of the Contact Owner, is to look through the Owner ID. So, we open the menu, go to the Owner ID, and then choose the Name of that Salesforce User to group the data to our table.
Now we have a new custom table, and a metric for Contacts Created associated with it.
To see how to view this custom table on your accounts, groups, and for your business as a whole, check out this article.