How to prepare your spreadsheet for opportunities import

Owned by Nikita Vasilev
Last updated: Mar 18, 2021 by Markus Ståhlberg
4 min read

We recommend creating a saved report on your CRM system including the necessary fields to enable importing the opportunity data into N.Rich. NOTE: If you are running ABM programs related to specific products, we recommend consulting N.Rich first regarding the setup to ensure that the engagement data is associated with products properly.

There are three stages in the opportunity import into N.Rich;

  1. Export accounts with open opportunities from CRM and import the accounts to N.Rich

  2. Export the open opportunity data from CRM and import the opportunities to N.Rich

  3. Export opportunity stage history data from CRM and import the data to N.Rich

NOTE: it is essential to define the report time range in a way that it only covers opportunities that have been open during the campaign in order to exclude redundant historical data. This can be achieved most easily by filtering the opportunities by the close date starting from the start date of the ABM campaign on N.Rich and ending some time far away in the future, e.g. the year 2050. This way you will ensure that only opportunities that are relevant will be included in the report regardless of whether you run the report before or after the start of the campaign.

When using Salesforce CRM (Lightning Experience), here are the guidelines:

Example screenshot of the open opportunity data report when the ABM campaigns have started on the 15th of September 2020;

Video tutorial for setting the filter. When using SFDC CRM please watch this first and apply to the scope of all three exports - the other tutorial videos are not covering this crucial guideline:

Step 1: Export accounts with open opportunities from CRM

Create a saved report on CRM including the standard required fields for importing accounts:

Report type: Accounts

  • Account CRM ID (Native CRM ID, for example on SFDC format looks like this: 0060c00001Sv8FLAAZ, or custom account ID)

  • Account name

  • Website

  • Account billing country

Please remember to save the report so that you can easily access it in the future.

If you are using Salesforce CRM, here are the specific guidelines (Lightning experience)

Screenshot of a completed report:

Video tutorial for creating the report:

After exporting the accounts with open opportunities, you can import the excel file into N.Rich (see: https://n-rich.atlassian.net/wiki/spaces/NK/pages/1706655745)

NOTE: If you are using Salesforce CRM, there is a possibility that the exported file is corrupted. In such a case, you need to open the .xls file on Excel and resave it as .xlsx. If this doesn’t work, you may need to copy the contents of the original file into a new, blank excel sheet and save that.

Step 2: Export open opportunity data from CRM

The mandatory fields for importing the opportunity data are. It is recommended to create a saved report on CRM with the given fields so that the report can be re-run easily.

Report type: Opportunities

  • Account name

  • Account CRM ID (Native CRM ID, for example on SFDC format looks like this: 0060c00001Sv8FLAAZ, or custom account ID)

  • Website

  • Account billing country

  • Opportunity ID (Native CRM ID, for example on SFDC format looks like this: 0060c00001Sv8FLAAZ)

  • Opportunity name

  • Created date

  • Stage

  • Last stage change date

  • Amount

  • Opportunity currency (needed, only if your organization uses multiple currencies, see also: https://n-rich.atlassian.net/wiki/spaces/NK/pages/1333362689)

  • Close date

  • Closed (true / false)

  • Won (true / false)

Additionally, if applicable, we recommend adding the product fields:

Please remember to save the report so that you can easily access it in the future.

If you are using Salesforce CRM, here are the specific guidelines (Lightning experience):

Screenshot of a completed report:

Video tutorial for creating the opportunity export report:

Step 3: Exporting opportunity stage history fields

As the final step of the export, you need to export the opportunity stage history data. The mandatory fields for importing the opportunity stage history fields are the following.

Report type: Opportunity history

  • Account name

  • Opportunity ID (Native CRM ID, for example on SFDC format looks like this: 0060c00001Sv8FLAAZ)

  • Opportunity name

  • From Stage

  • To Stage

  • Amount

  • Opportunity currency (needed, only if your organization uses multiple currencies, see also: https://n-rich.atlassian.net/wiki/spaces/NK/pages/1333362689)

  • Close Date

  • Last Modified

Please remember to save the report so that you can easily access it in the future.

If you are using Salesforce CRM, here are the specific guidelines (Lightning experience).

Screenshot of a completed report:

 

Video tutorial for creating the opportunity stage history report:

Here are the steps about how to create an opportunity stage history report on SFDC Classic:

  1. Go to the Reports tab and click on New Report.

  2. Select the Report Type as Opportunities. Expand it and select Opportunity History from the drop-down list.

  3. Click on Create.