Project : School Marketing System
Project Type: Web Development
Application: Database Analysis & Management
Client: Slim Goodbody Corporation
Description
The School Marketing System is an advanced web application designed to analyze and filter a database of over 130,000 public and private schools in the United States. The Slim Goodbody Corporation currently uses it to locate profitable venue locations and focus its marketing campaign for the company's nation wide educational performances. The system is for private use at this time. Feel free to contact me if interested.
The School Database
The 130,000 school database was created by exporting official data from the National Center for Educational Statistics (NCES). The public NCES tools allow the data to be exported as Excel files. I then wrote scripts to reimport, optimize and divide the data into a custom MySQL School database.
Database Content Overview
- General School Information
- Public Schools (100,917) & Private Schools (29,492)
- Name, Address, City, State, Zip, Postal Code
- Longitude, Latitude ( Used for proximity searches and visually mapping )
- Phone, Fax
- Total Students
- Number of Students Per Grade Level Per School
- Teacher / Student Ratio
- Charter, Magnet, Shared Status
- # Students in Free Lunch
- Public Schools (100,917) & Private Schools (29,492)
- Related Tables
- Level ( Primary, Middle, High, Other )
- Locale ( Large City, Town, Rural, etc. )
- Type ( Regular, Montessori, Special Education, etc. )
- Operation Status ( Operational, New, Reopened, etc . )
- Orientation ( Roman Catholic, Church of Christ, Jewish, etc )
- Association ( None, Accelerated Christian Education, Solomon Schechter Day Schools, etc. )
I also gathered and imported other database information including geographic economic statistics and district and county information. The NCES had longitude and latitude points for public schools but did not have them for private, so I used a batch geocoding script to calculate the coordinates from their addresses. Coordinates were required for proximity searches and displaying schools on a Map.
The database maintains the NCES school, district and county unique id's to allow updates to the data in the future.
School Data Filters
The system provides data filters to generate lists of schools with particular properties. The filters can be combined and tweaked to provide very different and specific lists. Some examples of the type of filters you could apply include:
- Primary and Elementary Schools in Maine and New Hampshire with greater than 500 students.
- Schools within X mile radius of Y Address.
- All Schools in the United States considered to be in a Large Central City with the Per Capita above $30,000
- All schools that match the keyword "Max"
Once a list is generated it can be saved to be opened again at a later date or exported into a excel file perhaps to be sent to your mailing service.
Generated lists can be excluded from new lists. So for example, you could save a list of schools within a 5 mile radius of a city in Maine and then run a new list of Maine excluding the schools in the previous list.
Application Screen Shot
