Topics in this article include:
- Creating the Opportunities CSV file
- Preparing your Data
- Import Settings
- Mapping CSV Fields
- Importing Opportunities in detail
- Saving and Sharing an Import Template
- Importing your Data
- Valid/Invalid Records
You’re able to import Open (active), Won and Lost Opportunities into Pipeliner. You can either match the Opportunities to existing Account and Contact records or create the Accounts and/or Contacts at the same time as importing your Opportunities. You can also add Product Line Item information (Product name, Quantity and Price) to your Opportunities ⤵
Creating the Opportunities CSV file
You need to prepare a CSV file for each pipeline that you’re importing Opportunities into.
To begin, you can download a sample file to use as a basic, default template or you can start from an existing spreadsheet and use the downloaded sample file as a guide.
Click on the “Import” button and then "Download Sample File" button ⤵
Another useful tip is to Export from the Opportunities menu. This will create a file with all of your Opportunity fields in and you can use this as your “custom” template.
Click on the “Export” button and then choose “Comma Separated Values (CSV)”. You can leave the default enclosure and separator as is ⤵
Depending on which browser you’re using, you’ll then see the downloaded file. For example, in Chrome it will show in the download bar at the bottom of the screen.
Click to open it in Excel. You’ll also be able to access from the Downloads folder on your machine.
NOTE: You might want to click on "File" and "Save As" and rename this file so that you can use it for your import file.
NOTE: The downloaded sample file only contains the default (out-of-the-box) Pipeliner fields. If you have added custom fields to your Pipeliner records, make sure to add those to the file as new columns, and, if you're not using any of the default fields, simply delete those columns. Just because a column is in the sample file doesn't mean you have to use it (unless it's a mandatory field) ⤵
NOTE: If you export from the Opportunities menu, the file will contain all your Opportunity fields - both default and custom fields.
Whichever option you choose, you’ll need to prepare your data before you can import it.
Preparing your Data
Remember that importing is fundamentally the same as manually creating new records so everything that you need to enter if you click on “Create New” and then “Opportunity” is also required in order to import Opportunities. However, when importing, you also have to supply values that might auto-populate when creating manually such as Owner, Sales Unit and Status ⤵
Key points to keep in mind are:
- Every row (Opportunity) in your data must have data for all mandatory/required fields.
- Data formats must be valid — check your dates, make sure that all values are valid if you’re mapping to dropdown fields such as Lost Reason.
- Make sure that your data does not contain duplicate records before you import
- All Opportunities must be assigned to an Owner and Sales Unit.
As you build your data for import — whether or not you use the sample file as a starting point — keep checking your data quality. Ensure there are no spaces before and after data in any column, that email addresses are all valid (no "mailto:" in an email address, for example), that data is all in the correct column and that your data meets all the mandatory field requirements.
Which fields are mandatory/required for Opportunities?
Each type of record in Pipeliner (each entity) has a number of system fields that are required in order to create records ⤵
For Opportunities, these required fields are:
- Opportunity Name
- Opportunity Value (can be "0")
- Closing Date (ideally "yyyy-mm-dd")
- Pipeline Step (must exactly match one of the stages of your pipeline)
- Opportunity Status (must be one of “Open”, “Won” or “Lost”)
- Owner (note that you can just map the current, importing user if your data doesn’t contain Sales Unit or Owner data)
- Sales Unit (must exactly match an existing Sales Unit)
- By default, you also have to provide an Account name to match your Opportunity to an Account record (although it is possible to change this behaviour in the Admin Module in Fields & Forms, for example, if you sell B2C)
Formatting your data correctly
Opportunity name is a text field so you can choose any name that works for you — Opportunity name is used in the Global Search in Pipeliner so it’s best not to use generic, identical names. Lots of customers use Company or Contact name as the Opportunity name if they don’t use specific project names to identify Opportunities.
Opportunity value is a currency field. Currencies (and any other numbers in your data) should not contain a thousand delimiters (e.g. “10000.00” not “10,000.00”) and no currency symbols (eg “10000” not “$10000”). If you use multiple currencies in Pipeliner, you need to add a separate column for currency symbol which must contain the three-letter currency code for each different currency in your data (e.g “USD” not “$” and “GBP” not “£” if you use British pounds and US dollars and are importing Opportunities in both currencies).
NOTE: If you only use a single, base currency in Pipeliner, you don’t need to include the currency symbol column.
Closing Date is a date field. Dates should ideally be formatted as "yyyy-mm-dd" though there are other standard date formats that you can choose from when importing.
Pipeline Step maps to the stages of the pipeline you’re importing into. Pipeline step must exactly match one of the stages of your sales — make sure it’s just the text and doesn’t contain a number e.g. “Initial Contact” not “1. Initial Contact”.
Opportunity Status is a system field and the only allowed values are “Open” – an active, open Opportunity in one of the blue columns in your pipeline – or “Won” – a closed, successful Opportunity living in the green column of your pipeline – or “Lost” – an unsuccessful Opportunity which you will see from the Archive (for Business and Enterprise customers) or from an Account or Contact record’s Opportunities tab.
NOTE: If you’re importing Lost Opportunities, Lost Reason is a required field. You need to add a Lost Reason column to your data containing one of your Lost Reasons (from the dropdown field in Pipeliner) for every Lost Opportunity.
Owner is a user field. Every record in Pipeliner MUST be assigned to an Owner (and Sales Unit). The Owner must be a current, active Pipeliner user. When preparing your data to map to Owner, add a column named Owner and use the user's login email address rather than their name to guarantee a match when importing. If a matching user login cannot be found, or you don’t map to the Owner field, imported Opportunities will be owned by the importing user.
Sales unit is a dropdown of the sales units that the Owner is assigned to. When mapping a Sales Unit, you need to be sure that the Owner you’re assigning to each Opportunity is also assigned to the Sales Unit you're mapping to.
For example, if your data contains a record where you're mapping to an Owner "email@example.com" and a Sales Unit of "Northeast", Bill must be assigned to the Northeast Sales Unit or you'll get an error when importing.
Account name is a lookup on existing Account records (though you can also create new Accounts when importing). If you’re trying to match your imported Opportunities to existing Account (company) records, the Account name in your data has to be an exact AND unique match to an existing Account.
NOTE: Your Pipeliner Admins may have removed the mandatory requirement for Account in which case you don’t need to worry about it!
Most data is fairly simple to work with if you stick to the requirements. If your data is a bit more complicated and contains many different types of fields, there are lots of tips in this article to help with preparing data for import.
NOTE: You don’t have to name the columns in your header row to match the field names in Pipeliner but it’s good practice to do this and it makes mapping much easier!
NOTE: If, as part of your Pipeliner setup, you have made any additional default or custom fields mandatory, every record in your CSV file will need to contain a value for those as well.
Save your file in CSV format when it's ready to import.
Selecting your CSV file
Importing is an easy step-by-step process using the Import Wizard. First, you select your CSV file and then you specify its properties and decide on deduplication settings, next you map the fields in your data to those in Pipeliner (and save it as a “Template” so you can re-use it easily), then you preview the records to be imported (which allows you to check that your mappings are correct and identifies any invalid records) and, finally, you accept the records which complete the import.
NOTE: Once you've selected Accept All, your records will be imported and, if you've made a mistake, you'll need to find and delete the imported records — there is no Undo option.
Click on the "Import" button ⤵
Next click on the “Click here to upload” link ⤵
Click on the “Upload a document” button or drag and drop the CSV file here ⤵
Locate and select the data file you've prepared ready for import. Click on "Open" ⤵
CSV File Settings
These settings define the properties of your CSV file. You can usually leave these on the default Auto and Auto Detect settings ⤵
Change this from “Auto” only if you selected a specific encoding — such as Unicode (UTF-8) — when saving your file to CSV format.
NOTE: If your data includes accented characters, it’s best to select “Western European (ISO)”.
You’ll only need to change this from “Auto detect” if you specifically chose a non-standard enclosure.
Multiple checkbox separator
If you’re mapping column(s) to multi-select checkbox fields in Pipeliner, choose the separator you used in your file. We advise you use the pipe | symbol as it’s extremely unlikely to have been used elsewhere in your data!
NOTE: When preparing your data, separate values by your chosen multiple checkbox separator and no spaces — e.g. “blue|green|red” were blue, green and red all options in your multi-select field in Pipeliner.
You should only need to change this from “Auto detect” if you specifically chose a non-standard separator.
If your dates are in the format "dd-mm-yyyy", leave this on Auto. If not, select an option from the list which matches your date format.
NOTE: If the actual date format in your CSV file is, for example, "dd.mm.yyyy" or "dd/mm/yyyy", choosing "dd-mm-yyyy" will also work (the same principle applies to the other formats).
Column contains headers
Make sure this box is ticked if the first row of your CSV file contains header names for your columns.
Click on “Save” to confirm your settings.
Choose your options for auto-creating linked records and, very importantly, your deduplication field ⤵
- Accounts — leave this enabled if you want new Accounts to be created if there is no match found to an existing Account record.
- Contacts — leave this enabled if you want new Contacts to be created if there is no match found to an existing Contact record.
Choose from one of the options to select which field should be used to de-duplicate the data that you are importing ⤵
Auto — if you leave the default option “Auto” selected, your imported Opportunities will be deduplicated only based on Opportunity Name. What this means is that if you have more than one row in your file with the same Opportunity name, the end result will be one Opportunity record being created in Pipeliner. Data from the first instance of the Opportunity name will take priority over subsequent rows but, if a subsequent row contains data that the first row did not, those values will be added to the Opportunity.
None — if you choose “None”, your data will not be deduplicated and every row in your CSV file will be imported as a separate Opportunity “as is”.
Select from the field list — if you select a specific field from the list of available fields, your imported data will be deduplicated based on a combination of the field you selected and Opportunity Name.
The available deduplication fields include the following system and default fields:
- Sales Unit
- Closing Date
- Lost Date
- Won Date
- Creation Date
- Opportunity Status
- Opportunity Description
- Lost Reason
- Lost Reason Description
And also, custom (user-defined) Opportunity fields of the following field types:
- Single-Line Text
- Date and Time
- Long text
- Integer number
- Float number
- Base Currency
Why is my choice of deduplication field so important?
Let’s say you’re importing a file of new opportunities (without Product information) — i.e. one row per Opportunity. If you leave the deduplication as “Auto”, unless you are completely sure that your Opportunity Names are unique, you’ll end up with fewer Opportunities imported than you have in your file as the duplicate names will be treated as a single Opportunity. In this situation, you’d want to select “None” so that all rows are imported as separate Opportunities.
If, however, you’re importing Opportunities with associated Product information, you have to prepare your file with one row per Product. In this case, you would copy the Opportunity information so that it is repeated on every row in your file and only the Product information would be different on each row. If you selected “None” in this scenario, you’d end up with many duplicated Opportunities — each with a single Product associated with them. So the right option for this situation would be “Auto” if your only deduplication value is Opportunity Name or choose a specific field — e.g. Closing Date - to deduplicate by Closing Date and Opportunity Name. The end result would then be a single Opportunity per instance of Opportunity name (and Closing Date if you selected it) with multiple Products associated with each Opportunity.
Choose a saved import template to re-use the same settings, deduplication and field mappings from a previous import. Your file will need to be the same structure and format each time. This is an invaluable option when you’re regularly importing data from the same source — e.g. monthly orders as Won opportunities from your ERP system ⤵
If you haven’t saved any Templates, you’ll be able to select from “Empty” or “Default Opportunity Mapping Template”. You can save a new template on the Field Mappings screen.
When you’ve chosen the Settings and Template you want to use, click “Next” to progress to the Field Mappings ⤵
Mapping CSV Fields
If you selected a template, your fields will already be mapped on the Map CSV fields screen ⤵
If you selected “Empty” then you’ll need to start by mapping each field.
Drag each column name from the right-hand panel to its corresponding field on the left. The left-hand side of the screen shows all the fields that are on the Opportunity Form for the pipeline that you’re importing into ⤵
NOTE: If you don’t see a field that you need to map to, you’ll need to cancel your import and adjust your Form from the Admin Module and then begin your import again. If this happens, map all the fields you can and then save as a template before cancelling so you don’t have to repeat all your steps.
NOTE: This is where it’s a timesaver if you matched your column names in your data to your field names in Pipeliner but it’s not essential to have done that, as long as you know which column should map to which field.
NOTE: You do not have to map every field from your data file to a field in Pipeliner — just leave behind those you don't want to import.
There are a number of system fields on the import form. If you don’t map a column from your file to these fields, they will be populated with default values.
These system fields include those in the Opportunity Status section of the import form as well as fields like Opportunity Created Date, Value, Closing Date etc in the General Information section ⤵
Unless you map values to each of these fields, they will be auto-populated with default values — Status will be “Open”, all Date fields will default to Today’s date, Step will default to the first stage of your pipeline etc.
Also, if your data does not contain a valid user email address to map to the Owner field or the name of a Sales Unit to map to Sales Unit, you'll need to leave the option to "Use current user" as the Owner and "Use default value" in Sales Unit (the current user’s default Sales Unit) checked and all imported records will be owned by the importing user (and will be in the importing user's default Sales Unit).
Importing Opportunities in detail
Let’s look at each section of the import form in turn.
NOTE: Remember that what you see will depend on the set up of your own Pipeliner system so may differ from the fields shown here.
Opportunity Status Section
This section handles the fields that are required when specifying the Status of each Opportunity. Opportunities in Pipeliner have one of 3 system status values depending on where they are in your process:
- Open Opportunities are active and moving through the stages of your sales.
- Won Opportunities have been successfully concluded and live in the final (green) column of your process
- Lost Opportunities are unsuccessful deals and have a status of Lost. Depending on your tier, you’ll see Lost Opportunities in the Archive or from an Account record or report. Lost Opportunities still live in one of the stages of your sales (so you can see where they got to before they dropped out of the pipeline) and all Lost Opportunities require a Lost Reason. You won’t be able to import Lost Opportunities without a Sales Stage and Lost Reason.
As well as the required fields, especially if you are migrating data into Pipeliner from a previous system, you might want to map the dates that Deals were Won or Lost prior to them being imported and you can map those dates in this section.
The Won Date and Lost Date are really important when using the Insights in Pipeliner so if you’re importing previously Won or previously Lost Opportunities, you’ll definitely want to include the right date for these fields to make sure your Insights show the correct metrics.
NOTE: Remember to format dates in the same format in your file ideally "yyyy-mm-dd".
Here an example of fields mapped from a file that contains Opportunities with different status values with Status, Won Date and Lost Date mapped ⤵
The fields in this section are the fields you see on your Opportunity Form when adding or editing an Opportunity. The core mandatory fields like Name, Value, Closing Date, Owner, Sales Unit live in this section as well as other default and user-defined fields that you are using.
You can also map an Opportunity Created Date from your data if you need to preserve when the Opportunity was originally created. If you don’t map to this field, the Created Date will be the date the record was imported.
Here are some sample mappings ⤵
Accounts and Contacts
Here’s where you link your imported Opportunities to existing Account and/or Contact records in Pipeliner or create new Account and/or Contact records at the same time as importing your Opportunities ⤵
NOTE: Account may be mandatory if using the default system settings.
NOTE: You can create Accounts and Contacts at the point of import if you have selected the options to Auto-create Accounts and Auto-create Contacts in your Import Settings.
You can map name, primary phone and primary email address straight onto the import form ⤵
If you have more field information — for example, website, address, contact mobile number etc — click on the More Fields option to map the additional fields ⤵
Fields that have already been mapped on the main import form are highlighted in dark blue whereas unmapped fields show in light blue in the right-hand panel. You can re-use any field you have already mapped and mapped it again to the new Account (or Contact) record that will be created by your import.
Products & Services Section
If you use Products in Pipeliner and then associate them with Opportunities, you can import those related Products when importing Opportunities.
Each Product must be on a separate row in your CSV file. Below is an example of a prepared file colour-coded so you can see that, when there are multiple products for an Opportunity, the Opportunity information is copied on each row and the Product information (Product Name, Quantity and Price plus Price List and Currency).
NOTE: All the purple rows are a single Opportunity with 4 Products associated with it, the green rows are a single Opportunity with 2 Products and the other rows are an Opportunity with a single Product each ⤵
Map your Product fields. You can map Product Name (this has to exactly match an existing Product in Pipeliner), Quantity, Price and, optionally, the Price List to be used and the Product Currency ⤵
Your Pipeliner Admin may have enabled certain fields as Sales Action fields. If so, you will see them in this section and can map to them in the normal way.
NOTE: If a sales action field is required as part of your process in order to move from one sales stage to another, you MUST supply that data in your CSV in order to successfully import Opportunities that are past the stage at which the field is required ⤵
Saving and Sharing an Import Template
You can save your settings and mappings as an Import Template and share with your colleagues. This article has details.
Importing your Data
Click on the "Import" button ⤵
You’ll then see a preview of the records to be imported. This is the result of my file which included Products — my file had 9 rows in it but, as we know, we opted to deduplicate the data based on Opportunity Name to account for multiple Products per Opportunity. The end result is that 5 Opportunities will be imported ⤵
You can click on any record in the preview to see the actual mappings in the right-hand panel. Scroll down to see that the multiple Products have been correctly added to the single Opportunity ⤵
The preview will show you if any records are invalid for importing. The row will be highlighted in red in the preview ⤵
If you click on an invalid row you’ll be able to see where the problem is and resolve it ‘on the fly’ by correcting the data or adding in missing information ⤵
NOTE: If you have lots of invalid records, check them over to see if there’s a pattern to the problem and then close the import (make sure you have saved your import template) and sort out the issue in your data or by modifying the set up of Pipeliner and then re-do the import.
You can also click on the "Filter" icon and just choose to see just Invalid records to make them easier to review and fix ⤵
Once you've corrected an individual record, you can click on "Accept" to import just that record immediately ⤵
If you are looking at a record and do not want to import it you can reject the record using the red "Reject" button at the bottom of the record details. This will remove that particular record from the import list ⤵
If you've made a mistake in your mappings, you can use the "Change Mapping" button at the top to change the field mappings and this will return you to the “Map CSV fields” screen ⤵
If you have mapped more fields than the default display includes, then the import page may not show all fields at once.
To view more fields you can select the following button to show all field options ⤵
Accepting Records and Finishing the Import
You now need to select the records to be imported by clicking the checkboxes on the left (or select all by clicking the first checkbox in the upper left). Click on the "Accept Selected" button to finish the import of those records ⤵
Import Options when duplicates are found
Pipeliner will then begin to import your records. As part of the import process, it will check the data that already exists in Pipeliner.
NOTE: This is not the same as the Deduplication settings that you chose which work on your imported data. These options refer to records that already exist in Pipeliner before you start your import.
If any of the opportunities that you’re importing has the same Name as a record that already exists, you’ll be able to choose what to do:
- Skip All — duplicate records will not be imported and will remain in the import preview once the non-duplicates have been imported.
- Create Duplicates — ignore the warning and go ahead and create duplicate records
- Update — data on the duplicate records will be overwritten by the incoming data (be aware that when you select this option, unless your incoming data contains all fields, data might be lost from your existing records if the field is not mapped as part of this import)
- Cancel — cancel the import so you can check the data and decide what to do
If you click on "Close" to abort your import while there are still records in the preview, you’ll be prompted by a message indicating that the import is complete and check that you really want to leave. Click on "Continue" to close the import ⤵
Once you’ve chosen your option, the import will complete and you can then check your Pipeliner system to view the newly imported records ⤵
What if there’s a problem after the import is complete?
Rarely, but sometimes, despite all your data preparation and care, the imported data can be wrong. As long as you’re based on a user role that allows you to delete, you can remove records by clicking on the Opportunities menu, switch to the "List View" and then select the records to be removed. You might need to set up a filter for this. You’ll then need to Archive each Opportunity. Finally, delete the Archived Opportunities from the Archive (or from the Account or Contact records if you’re a Starter Tier customer).
NOTE: If you don’t see the "Delete" button, it’s your user role that needs amending.
NOTE: You must be the record Owner in order to delete them. If you’re not, select your records and then click on the Ownership button to make yourself the Owner and, once that’s complete, you will then be able to delete them.
NOTE: If you can’t change the Owner of a record, you need to be assigned Manager rights in the Admin Module.