"The team’s project results greatly exceeded the client’s expectations … We were also able to leverage technology tools to enable the team … and take action to better perform the mission."

Project Portfolio:
  Database Reconciliation Project

Challenge

The purpose of this project was to cleanup a consultant/volunteer database, from a large and prestigious, Washington D.C. based, internationally focused nonprofit.  The project required that the team reach out to the individuals registered in the Nonprofit’s database:

  • To confirm interest and availability for China based projects
  • To verify current personal and work data
  • To update the database with the information resulting from these activities

By applying my business analysis experience and my technology knowledge experience, I aided the team with many issues.   I was involved in creating a tracking spreadsheet in Google Sheets to track all the work on the database records.  I also developed a data entry conventions document to ensure data was correctly entered in the system.  I was in charge of entering thousands of updates to the database accurately and efficiently.  Lastly, I helped analyze the data so we could take strategic actions and so we could have a better idea of what the data contained.

Solution Phase One

The first phase of the project consisted of contacting the consultants/volunteers in the database and requesting that they send us their most up to date contact and work information. Additionally, we requested that they answer several questions regarding their work and interest in mainland China.


We selected to send an email blast, with the request for information, to all valid emails in the database.  For those in the database without a valid or working email, a physical postcard was sent to them.  Both the email and the postcard had a website link to an online form that requested all the data we needed from them.  The email blast product we used was Constant Contact and was very effective.  It was able to identify which emails responded and which emails did not.  We were able to use this information to send two or three emails blast to the contacts who did not respond initially.  Response rates of over 7% for emails and over 22% for postcards where achieved; a very high rate for unsolicited correspondence.


I was able to help the team in many ways.  I leveraged my expertise in training senior citizens in computer use and my expertise in requirements gathering to contribute to team success.


The majority of persons contacted where senior citizens with many high and valuable skills.  However, for many of them computer use was not one of them.  The way the online information form was initially set up was not very user friendly.  This problem would reduce the response rates and the effectiveness of the campaign.  I used my experience training seniors to propose key changes on the form.  The changes ensured the form was easy to use for everyone, including people not too comfortable using computers.  The changes helped ensure we got back as many responses as possible.


I also created a requirements document to clearly communicate our needs to the development team. With the help of images and of descriptions, I articulated each requirement in a reasonable size and in very specific detail.  Because the developers clearly understood what was needed from the first time they got the requirements, the project move forward to the implementation stage quickly.

Solution Phase Two

The second phase of the project involved updating the database with all the new and updated information that was received from the information request campaign.  Because there were over 12,000 people in the database, with tens of data fields per person, we had to ensure that the data was entered correctly and efficiently, and that we were able to track project progress.


To meet the needs mentioned above, I created a customized collaborative Google sheets spreadsheets so we could track our work.  We also developed a data entry convention document, specifying how to enter data and how to update the tracking spreadsheet, to ensure consistency and quality in the data entered.


The customized Google sheets spreadsheets was used to record which records in the Lotus Notes/Domino database where updated.  In addition, special sections where created to add notes and other details.  We leveraged the extensive use of the spreadsheets formulas to automatically count and summarize all the work that was done.  All this summarized data was organized and presented in the Google sheets as an “information dashboard” that displayed the most up to date information.  For example, a pie chart was created comparing records that where completed versus ones that were not completed.  The pie chart automatically updated as the data was entered in the worksheet.


The large number of data points that needed to be updated meant that there were many different circumstances that could happen in the data entry process.  A data entry convention document was created that clearly outlined what to do with the data depending on the circumstances presented.


I helped the team with the data entry, but also by suggesting and developing the tracking spreadsheet and the data entry convention documentation.  I also was able to leverage my knowledge of programing logic rules acquired while learning the programming language Python.  I used these logic rule constructs (i.e. boolean expressions, conditional statements, etc.) to effectively manage and analyze the large data sets we were using in the project.  For example, by running a function in the email field that returned FALSE if the email format was not correct (i.e. correct email format ---jane.doe@gmail.com), I was able to identify all the incorrectly formatted emails in the database.

Results

The project was a tremendous success.  The team’s project results greatly exceeded the client’s expectations.  The client was very happy with the high quality of our work.  They were impressed with the size of the data collected and the high level of accuracy of the inputted data.


We had a high response rate to the request for information because we created an effective and compelling process to collect the information.  We were able to update the database in a very accurate and efficient manner because we used strict conventions and used customized spreadsheets to manage project progress.  We were also able to leverage technology tools to enable the team to manage the data and take action to better perform the mission.  The tremendous amounts of records meant this data analysis for action could not have been done manually at all.  By using the function capabilities of spreadsheets and also by using Python’s programing abilities, we had the ability to work efficiently and effectively with the data.