Tips for Organizing Your Historical Donor Data

Importing your historical data into your DMS is crucial for getting the full picture of your fundraising. But preparing your donor data for import doesn't have to be a ton of work!

Below are some useful tips for organizing your donor data before submitting it for import. Along with our Template, following these tips will help us map your historical data correctly in your DMS, and help speed up the data migration process.

General Tips

• If you can, please submit your data file  in .xlsx format (so it can be opened with a spreadsheet program like Excel or Google Sheets)
  • Tip: do you have foreign accents in your data? Save your file in .CSV UTF-8 format instead!
• Treat each row as a single Contact entry if possible (i.e., include all of a Contact's information in the same row, separated by columns)
• You can submit your information as either:
  • One file with Contacts and Contributions on the same sheet (see Option1: Contacts and Contributions sheet in the Template),  OR
  • One file with separate Contacts and Contributions sheets (see Option 2a) Contacts Only and Option 2b)  Contributions Only sheets in the Template):

Options for how to organize your data by sheets

Tips for Contacts

1. First Name and Last Names

• First and Last Names are mandatory. If any records are missing either of these fields, we'll input the missing value as “Anonymous”

2. External IDs

• An External ID is a unique record identifier that is assigned to every one of your Contacts
• If your previous system has already assigned IDs to your Contacts, feel free to provide them. They'll help us identify duplicate Contacts and link any associated Contributions to them
• If no external IDs are provided, our Data Team will create one for each Contact

3. Contact Type

• Contacts in the DMS are separated into 2 different types: Individual, Organization
• Please include a separate column for Contact Type to help us avoid importing contacts under the wrong type (e.g., if an organization has a name that resembles an individual)

4. Personal Contact Details 

• Including identifying information (i.e., mailing address, email, phone number) helps our Data Team better spot duplicate Contacts
• Feel free to include the following information for each Contact Type:
  • Individuals: Middle Name, Formal Title, Preferred Language (set as English unless specified)
  • Organizations: Preferred Language, Address, Email, Phone Phone Extension, Website
• For mailing addresses, please ensure separate columns for each element:
  • Street Address
  • Secondary Address
  • City
  • Province
  • Postal Code
  • Country

How to best record mailing addresses

5. Tags

• Tags are customizable, visual labels for your Contacts; the DMS comes with the below preset Tags:

Preset Tags

• You can add as many other Tags as needed and list them all on the same line for each Contact:

How to list multiple Tags

6. Privacy Preferences

• You can also provide information on privacy specifications (e.g., Do not email, Do not phone, Do not mail).
• Contacts with no specified Privacy requests will be input without any privacy preferences (don't worry, you can update these details in your DMS at anytime):

Example: A 'Yes' under 'Do not phone' means no telephone communication

7. Relationship Information

• You can include relevant relationships for any Contact under the Relationship Type column (e.g., “Employer”, “Employee of”, “Spouse of”, "Sibling of", "Partner of") 
• You can then list the full name (or External ID, if applicable) of the related individual under the adjoining Related Contact column
• You can also add new columns with relevant details about a Related Contact (e.g., job title, address, notes)
• If one Contact has multiple relationships, you can add additional columns as needed:

How to list multiple Relationships

8. Contact Notes 

• Use the Contact Notes column to include any notes relevant to a Contact that don't otherwise have a dedicated place in the Template (based on the columns available)
  • If our Data Team can suggest a better location for the information in the DMS, we'll let you know!

Tips for Contributions

1. Dates Received 

• The Date Received (including month, date, and year) is a mandatory field for all Contributions
• If you don't know the date associated with a Contribution, you can either:
  • Set the date as the day of a specific event/campaign (for Contributions associated with said event/campaign)
  • Add a temporary date (e.g. set all non-dated contributions in 2022 as of 01/01/2022), and adjust them later
  • Remove the Contributions with no dates and import them at a later time when you know this information
• Make sure all of your dates follow a consistent format to avoid confusion (e.g., 01/02/2020 can be interpreted as January 2nd, 2020 or February 1st, 2020).

Make sure to include a 'Date Received' for all Contributions (following a consistent format)

2. Transaction IDs

• Similar to a Contact's External ID, the Transaction ID is a unique record identifier that is assigned to each transaction
• Feel free to provide transaction IDs if these have been assigned by your previous system
• If you've been recording your data manually and haven't assigned Transaction IDs, don't worry! Our Data Migration Team will create one for each Contribution.

3. Total Contribution Amounts

• Keep in mind that the Contribution Amount column refers to the total amount for a single donation, rather than the all-time amount gifted by a specific donor

4. Campaigns and Funds

• Campaigns are your online or offline fundraising appeals. They indicate where a donation has come through (e.g., a gala, a walk-a-thon, a direct mail campaign)
• Funds allow you to record where each donation will be allocated once received by your organization. They indicate where a donation will be directed to (e.g. Operating Fund, After School Program Fund, Emergency Supplies Fund)
• By default, your DMS comes with a preset General Fund and an In-Kind Fund, but you can create an unlimited number of DMS Funds
  • Contributions from the same Campaign may be directed to different Funds (example below)
• If you have Contributions with no set fund/campaign, feel free to label them as "General Fund" or "General Campaign" and update them later

How to record Campaigns and Funds

5. Receipt Information

• For those contributions which have already been issued a tax receipt, wherever possible, please including any relevant receipt information, such as receipted date and receipt number (which should be unique to each Contact)
• If your data includes any Contributions that haven't been receipted yet (but for which you'd like to issue a tax receipt through the DMS), make sure to add a 'No' in the 'Is Tax Receipted?' column:

6. Multiple Contributions Per Contact

If you're including your Contact and Contribution data in the same sheet, and a single Contact has made more than one Contribution, simply list the different Contributions as separate rows for that Contact. In this case, you can either:

• Duplicate the entire row for that Contact and update the information for each Contribution they've made; 

Or

• If you're using External Identifiers, duplicate the row but only include the External Identifier for that Contact (example below). You can then update the information for each Contribution they've made:

Listing multiple Contributions made by a single Contact (using External IDs)

7. GL Account Information

• GL Accounts in the DMS are records used to arrange how the money stored in Funds are being transmitted to your General Ledger and Balance Sheet
• If you'd like, you can include historical GL account information with your data by adding a GL Account column, as well as a corresponding GL Accounts sheet with more details:

Importing GL information

• In your GL Accounts sheet, feel free to provide our team with the below details:
  • Name (mandatory): e.g. General
  • GL Code (mandatory): e.g. 4200
  • Account Type: only if you are including non-Revenue Accounts.
  • Description
  • Funds Associated (mandatory): which Fund is associated with each GL Account
  • Contributions Associated (mandatory): which of your Contributions are associated with the included GL Accounts

What Information is Excluded in the Migration?

1. CanadaHelps Contributions

• Don't worry, any donations received through CanadaHelps (within the past 5 years) will be migrated shortly after your Kick-Off call.

2. Negative Contribution Amounts (contributions less than $0)

3. Information directly related to a previous system (e.g., Last Modified Date, Last Updated By, Added By)

4. Contribution Statistics

• Your DMS will automatically calculate Contribution statistics based on the data provided (i.e., last Contribution amount and date, second last Contribution amount and date, all-time total Contribution amounts) and display it under a Contact's Profile.

Most Common Setbacks

1. Unclear Voided Contributions

• Please label any Contributions that have been cancelled, refunded, or voided. We'll mark these as Cancelled so that you can still track them in your DMS.

2. Missing Received Dates

• Remember, Contributions can't be imported without a received date (see Tips for Contributions above).

3. Lump-Sum Contribution Amounts vs. Individual Contribution Amounts

• As a reminder, the DMS will calculate Year-To-Date and Total Lifetime amounts on each Contact's Summary page, so there's no need to include lump-sum Contribution amounts. Just make sure you're providing clear individual amounts and dates for all Contributions.

4. Unidentified/Ambiguous Relationships Between Funds and GL Accounts

• .In the DMS, every Fund automatically creates a corresponding GL account (e.g., Gala Fund > Gala Fund GL Account)
  • But, you can instead map a Fund to another existing GL account (e.g., Gala Fund > General Fund GL Account)
• Note: a Fund can only be mapped against a single GL Account, but a single GL Account may have multiple Funds within it.

Final Step: Submitting Your Data

So, you've finished preparing your historical data based on the Data Migration Template and the tips above? Hooray! 

You're now ready to submit your data file to our team using our secure form.

You'll see a confirmation screen letting you know that everything was submitted successfully, and then our Data Team will get to work! If our Data Experts have any questions about your data, your Onboarding Specialist will reach out to you directly.

What's Next?