In this article, we'll walk you through the steps you need to take to ensure your Google Sheet is properly connected. Below we will outline the format your Google Sheet needs to follow and how to connect it to QuotaPath. Please refer back to these steps and tips as needed.
In order sync your Google Sheet to QuotaPath, the following fields/columns will be required:
id (Column name must be lowercase)
Deal Name
Deal Amount (Must be formatted 0000.00)
Close Date (Must be formatted YYYY-MM-DD)
Member Email (Must be email address)
Stage
In addition to the above, you may also need to add additional fields/columns depending on the structure of your compensation plan. For example:
Contract Length
Deal Type / Pipeline
For an example of how your Google Sheet should be formatted, please view this sheet. Feel free to make a copy of this sheet if you'd like to utilize this template.
For additional information on the above, please continue reading below.
Before connecting, these are the steps you will need to take to ensure your Google Sheet is properly formatted prior to connecting in QuotaPath:
Sheet & Tab Names: Please make sure there are no slashes ('/') in either the Google Sheet name or the tab names.
Unique ID: You will need a column for a unique ID associated with each deal in your Google Sheet. The IDs do not have to be in order (i.e. 1-100), however, they all do need to be completely unique to the sheet. The same ID cannot be used twice within the tab you using (i.e. ID 1 cannot exist twice on a sample ‘Deals’ tab). The same ID can be used across multiple sheets (i.e. ID 1 exists on both the ‘Deals’ and the ‘Invoicing’ tab)
Within the sheet itself, the header for the id column must read “id” (lowercase). An uppercase header for this column will lead to a source form error.
Make sure you don’t have any data in rows that there is not a corresponding ID associated with that Data. It is perfectly fine to have no data in columns that DO have an ID associated with the corresponding row. Rows 13-15 in the example below will cause an error as there is no ID field, but there is data in the rest of the row. Rows 5-7 are perfectly ok, even with a missing close date, as there is an ID field associated with that row.
Date: All Date fields must be in the YYYY-MM-DD format. To adjust your date format:
Click on the header of the date column you want to change (i.e. click on the letter E across the top of the sheet)
Click on “Format”
Scroll to Number
Continue down to Custom Date and Time
Find the YYYY-MM-DD format and click “Apply”
Amount: All amount totals should be in a 0000.00 format. Do not use a currency format or a format that contains commas (i.e. do not use $ or 1,000). To update formats take similar steps to the above:
Select the column header of the column you would like to edit
Click on “Format”
Scroll to “Number”
Continue down to custom number formats
Select 0.00 option
Now that your sheet is compatible with our system, you are ready to connect it to QuotaPath. You can view the following video, or follow the steps below:
Navigate to the integrations page. Click on ‘Settings’ within the left hand navigation bar and then ‘Integrations’.
Find the Google Sheets integration card and click ‘Connect’.
Choose the Google Account you would like to use and allow QuotaPath access to the Google account you selected.
Once you are taken back to QuotaPath, you will be prompted to fill out an integration source form. Within this source form you will need to enter the link to your spreadsheet:
This can be either the shareable link, or the URL at the top of the page.
Once you submit you will be fully authenticated with Google Sheets.
Click on the ‘Next’ button in the lower right hand corner of the modal to continue to the next steps
Here you will select your sync schedule and the objects/streams you would like to sync
All objects are based on the tab names you have in your Google Sheet
Once you select ‘Sync Now’ your data will start flowing into QuotaPath.
This could take a few minutes so feel free to close the modal and navigate to Home to complete any outstanding onboarding tasks. We will send you an email once your integration is ready to use.
You will also be able to see your sync status across the top of the integration card under the “Connected Integrations section”:
At any point, you can click “Edit Settings” or “Resync Now”
“Edit Settings” will allow you to update your sync schedule or any of the streams/objects you have connected
“Resync now” will allow you to kick off a manual sync, ahead of your next scheduled sync time
Once the Google Sheet is connected, navigate to Home or Plans to sync data to a compensation plan in QuotaPath.
After you've formatted your Google Sheet, connected it to your QuotaPath workspace, and created your plan, follow the below steps to sync your plan to the Google Sheet.
Go to the Plans page in your QuotaPath workspace, navigate to the plan you'd like to sync, and click the path to expand it. From there, click "Sync to CRM" to sync the first path to your Google Sheet.
In the pop-up modal, select to connect to 'Google Sheets'. On the following screen, choose which tab in the Google Sheet to sync to. If you are not seeing a tab, go back to Settings then Integration and edit the integration settings for Google Sheets to sync to another 'stream', or tab in your google Sheet.
Next, start aligning the QuotaPath fields required to calculate commissions to the fields in your Google Sheet.
From there, use the dropdown to select the field in the Google Sheet to define each of the 3 deal stages: closed won, pipeline and closed lost. It is only required to define closed won.
On the last step, two filters will automatically appear. The first filter will appear as 'Member Email field name' = Assignee. This filter will automatically match align deal data based on the plan assignee. The second will appear as 'Deal Date field name' >= one year ago today. This filter automatically filters out data from more than a year ago. You can remove this filter and add it back with the plan effective date, but this filter is required. Last, add any additional filters that are necessary to filter for the relevant deals for this path. For example, a 'Contract Term' field.
When finished, click 'Sync' to finish setting up the data sync for this path. If there are additional paths in the plan, repeat the above steps to sync every path in the plan. After every path is synced to your Google Sheet and you've assigned QuotaPath workspace members to the plan, head to the Earnings page to start managing commissions!
If you are having any troubles, please feel free to reach out to our support team, support@quotapath.com, or start a chat from the icon on the lower right hand corner of your page.