Child Sponsorship Data Import Outline

This page outlines the data structure and step-by-step process for initial importing regarding child records. Due to the rich automation features in Site Stacker, child records must be imported in a careful manner to ensure those automations do not change your data unexpectedly.  

WARNING: The order of these tasks is highly important and MUST be followed for desired results!

Quick Tip for importing: For large imports, lines of data should not reach over 25,000 records at a time - this is a safe number in case there are any issues with the imported data and you need to run a rollback.

If you are unfamiliar with our import tool, you can reference the following article: Import Tool Overview

Begin Your Import Journey:

On your Admin dashboard, open the “Import” component:

Click the tab labeled “Documentation”. This tab is where all the templates for imports available to your installation. (Screenshot 1)

(Screenshot 1)

You will need follow the hierarchy of imports as follows:

• Import Children (“Person” Import)
• Import Child Photos (“Person Photo” Import)
• Import Record Types (in the following order):
  1. Child (Must be the first Record Type imported!)
  2. Available
  3. Unavailable
  4. Sponsored
  5. Departed Child
• Import Entities (Various Imports)
  • Example: Child Enrollment details
• Import Sponsors (if applicable)
  1. “Person” Import
  2. “Person Photo” Import (if applicable)
  3. Sponsor Record Types (Sponsor, Previous Sponsor, Delinquent Sponsor)
  4. “Sponsor of” Relationship
  5. “Previous Sponsor of” Relationship (if applicable)

Let’s go ahead and take a deeper look into the files that make up these imports!

Children Import Files

The first file we will need for both Children and Sponsors is the “Person” Import. You will want to select the checkbox next to the name and when you click on the “Download Selected” button it will download that CSV in a Zip File (you can download as many Import files as possible you may need for this process!).

Once you have the file(s) downloaded you can then open the ZIP file that will reveal a folder with the file(s) inside. Our example is the “12. Person.csv” file.

Note: Import files must keep the leading number from the template (ex. 12. Person.csv). The files can be renamed for your needs if the leading number is kept. (ex. "12. Child Names A-G.csv").

12. Person.csv – This file is to be used for all Children (and Sponsor) records with the following fields filled out:

• firstname [REQUIRED] – The first name of the Child or Sponsor.
• middlename (Optional) – Can be filled out if records contain this field.
• lastname [REQUIRED] – The last name of the Child or Sponsor. This is a required field for both Children and Sponsors.
• gender (Optional) – The gender of the Child or Sponsor. Current values are ‘m’ or ‘f’ for Male or Female.
• birthday (Optional) – The birth date of the Child or Sponsor in the format of YYYY-MM-DD (ex: 1900-01-11).
• email (Optional) –The email that any communication or updates would be sent to (for Sponsors).
• external_id [RECOMMENDED] – A key field for both Children and Sponsors to link connect any necessary data to this record including Record Types, Relationships and CRM Entities in the process for every unique person. This field will contain the current ID the Child or Sponsor has in your current data system (or a new ID you have created). This is usually only used on initial imports.

16. Person Photo.csv – This file is mainly used for Child Photos (but there are some use cases that Sponsors or regular Donors may also have photos attached to their records). Here are the key fields to fill out:

• path– This is where the photo lives in Site Stacker’s “File Manager” component.
  1. Note: Photos must be uploaded beforehand into File Manager so that you can use the path (ex. "/Files/Child Photos/CHILD123.jpg"). We recommend using the name or IDs of the records for the image name so that connecting these photos to the correct records is easier.
• external_person_id – This field connects to the specific Child or other record that needs their photo attached.

Record Type Imports:

Note: You can find a list of current Record types under the CRM component:

Head to the “Configuration” tab and then select the “Record Type” tab in the right panel and you will see a list of Record Types (Screenshot 2). Whenever you add Record Types here, the related Import Documentation will also be added in the Import component:

(Screenshot 2)

31. CRM Record Type - Child.csv – this will define your record as a Child Record within the CRM and Site Planner components. There is only one field needed for this import (Note: this also applies to the 40. CRM Record Type - Sponsor.csv file):

• external_person_id – This field connects to the specific Child or other record that needs this specific Record Type.

You will follow the same import process with the following Child Record Type imports:

• 30. CRM Record Type - Available.csv (This record type will be applied to all children who should show up for sponsorship on the website. Their content item will be searchable and reside in the Available children folder.)
• 41. CRM Record Type - Sponsored.csv (This record type will be applied to all children who have a Sponsor. Their content item will be marked unsearchable and their content item will reside inside the Sponsored children folder.)
• 42. CRM Record Type - Unavailable.csv (This record type will be applied to all children who are unavailable for sponsorship, for any reason. Their content item is unsearchable and will reside in the Unavailable children folder.)
• 165. CRM Record Type - Departed Child.csv (This record type will be applied to all children who have left the program for certain reasons. Their content item will be unpublished and unsearchable and will reside in the Departed children folder.)

And the same for the following Sponsor Record Type imports:

• 40. CRM Record Type - Sponsor.csv (This record type will be applied to any donor when they sponsor a child and this record type will remain in the CRM, even after they cancel their sponsorship or become delinquent.)
• 37. CRM Record Type - Previous Sponsor.csv (This record type will be applied to all sponsors who cancel their sponsorship.)
• 32. CRM Record Type - Delinquent Sponsor.csv (This record type will be applied to all sponsors who become delinquent in their sponsorship when their monthly payment fails after 3 attempts.)

After you have imported all the necessary Record Types for both Children and Sponsors, we can now import the Entities for Children which are located under the CRM component > Configuration > Entities > “Child Departure” and “Child Enrollment” which includes the following entities (Screenshot 3):

Child Departure Entity Group:

• Departure Info
  1. Departure Date – Date when the child departed the program.
  2. Form Completed By – Text field of person who completed the form. (Field Rep)
  3. Home Visits Made – Number of Home Visits made by the Field Rep.
  4. Departure Reason – Text area for listing any reasons that may have caused the departure to happen.
  5. Explain the Child’s New Situation – Text Area to describe what updates will happen for the child.
• Spiritual Impact
  1. Is the Child a Christian? – A datagroup used for answering this question. (Yes/No/Unkown)
  2. Did the Child become a Christian while in the Sponsorship Program? – A datagroup used for answering this question. (Yes/No/Unkown)
  3. Explain the Child’s Christian Experience: - A text area field to describe or elaborate on a child's spiritual journey or encounters within the Christian faith.
• Physical & Emotional Impact
  1. Is the child healthy? – Text field used to describe the child’s health.
  2. Has the child matured appropriately for their age? – Text area used to describe the child’s maturity related to their age.
  3. Explain the child's level of maturity and how they relate to others: - Text area used to describe their maturity vs. others maturity levels at their age.
  4. Highest class-level completed: - Text field for a child’s last school grade completed.
  5. Skills the child was trained in – Text field to describe any skills the child has learned with life experiences.
• General Comments
  1. Comments of Project Impact on Child – Text area used to summarize the project's overall impact on the child's life along with any other comments.

Child Enrollment Entity Group:

• Location Information
  1. Child Country – Datagroup containing the Child’s Country.
  2. Child Community – Datagroup containing the Child’s Community. (Region in Country)
• Education
  1. Grade – A number (sometimes datagroup) of grades a child is in school.
  2. Distance to School (kms) – Number of kms school is from the child.
  3. School Progress – A datagroup of school updates. (Excellent, Good, Fair, etc.)
  4. Favorite Subject – Datagroup containing Subjects in school.
  5. Other Favorite Subject – A text field for listing extra Favorite Subjects.
• Health Info
  1. General Health – Text area used for any health updates for a child.
  2. Handicap – Text field of any handicaps a child has.
  3. Other Handicap – Text field for any additional handicaps.
• Home Life
  1. Chores – A datagroup of Chores that a child may participate in.
  2. Other Chore – Text field used for listing additional Chores.
  3. Common Meal – Text field for any favorite meals a child has.
  4. Prays for – Text field for a child’s personal prayers.
• Family Information
  1. Child Full Name – Text field for the full name of the child.
  2. Child Lives With – Text field to list anyone the child may live with.
  3. Father’s Name – Text field for listing the Father’s name if applicable.
  4. Mother’s Name – Text field for listing the Mother’s name if applicable.
  5. Siblings Names - Text field for listing any Sibling(s) name(s) if applicable.
• Parent Information
  1. Mother Occupation – Text Field for listing the Mother’s primary occupation
  2. Other Mother Occupation – Text field for listing a secondary Mother occupation.
  3. Father Occupation – Text Field for listing the Father’s primary occupation
  4. Other Father Occupation – Text field for listing a secondary Father occupation.
• Interests
  1. Hobbies – Text area for listing multiple hobbies a child may have.
  2. Interests – Text area for listing any interests a child has.
  3. Favorite Thing to Do – Text field for listing a favorite thing a child does. (Ex: Playing sports, Chores, Playing with friends, etc.)
  4. Other Favorite Thing to Do – Text field for listing extra favorite things to do.
  5. Future Dream – Text field for listing a child’s future dreams.
  6. Other Future Dream – Text field for listing additional dreams the child has.
• Story
  1. Summary – A text area field for a summary of the child used on your website
  2. Body – The Body text for the detail page of the child seen on your website
  3. Cover Photo – An image set used for displaying an image on the detail page of the child.
  4. Child ID – A text field containing the Child’s ID from your current database (or potentially a newly created ID).
• Enrollment Notes / Date
  1. Enrollment Notes – A text area field used to describe the initial notes on a child’s record.
  2. Enrollment Completion Date – A date field to determine when the child finished the program.
• Packet Story
  1. Packet Story – A text area field used for creating a packet story that may be sent to Sponsors.
• Program Dates
  1. Start Date – Date when the child started the program.
  2. End Date – Date when the child finished or exited the program.
• Misc. Information
  1. Notes – A text area field used for internal or additional notes for a child.
  2. Additional Notes – Another text area field for even more notes on a child.

(Screenshot 3)

Note: For entities containing Datagroup imports, you will need to get the IDs of both the Datagroup itself and the values within the datagroup! You will find these within the Data Groups Component:

Once you are inside of the Data Groups component you will find the name of the data group which need the. You will need to hover over the columns on the left panel (and the right panel) and under the Columns sub-menu, select the “ID” column to have it show as a display column (Screenshot 4). So if our import has the Favorite Subject entity and the Child’s favorite subject is History, then the following columns would look like this in the import file:

favorite_subject (Must supply a valid Datagroup FavoriteSubject ID value.) – For the value "History", the ID is 5

favorite_subject-external_id (Must supply a valid Datagroup FavoriteSubject External ID value.) – For the "Favorite Subject" Datagroup, the ID is 25

(Screenshot 4)

Once you have finished importing these Record Types and Entities, now we can import the relationships between the Children and their Sponsors!

Sponsorship Relationship Imports:

You can find the list of your current Relationships by looking under the same CRM component > Configuration > Relationship tab (Screenshot 5):

(Screenshot 5)

Below is a list of the following imports related to Sponsors and Children along with their descriptions. All you will need are the External Ids of both Sponsors and Children to make these connections!

Tip: Some imports are inverse relationships and only one of these relationships is needed for importing! (Ex: “Sponsor of” is one, and “Sponsored by” is the inverse relation)

• 66. CRM Relation Type - Sponsor of - Sponsor is ‘Sponsor of’ Child.
• 67. CRM Relation Type - Sponsored by (Inverse) - Child is ‘Sponsored by’ Sponsor.
• 68. CRM Relation Type - Previous Sponsor of - Sponsor is ‘Previous Sponsor of’ Child.
• 69. CRM Relation Type - Previously Sponsored by (Inverse) - Child is ‘Previously Sponsored by’ Sponsor.
• 214. CRM Relation Type - Affiliated Sponsored by - Applied to a sponsor when they sponsor a child on behalf of an organization. This allows the child record to show ‘Sponsored by’ the Organization and the Sponsor as ‘Affiliated Sponsored by’.
• 215. CRM Relation Type - Affiliated Sponsor of (Inverse) - This allows the Sponsor’s CRM record to show ‘Affiliated Sponsor of’ Child when they sponsor on behalf of an organization.

Once this is complete, you have successfully completed this walkthrough of importing Child Sponsorship Data! You can now go back into your Spark and check the Active boxes of any Spark events that you previously disabled so that they can function correctly as Children get sponsored!