MGSLG Data Source Mapping and Analysis

Overview

This document maps the likely existing data sources at MGSLG and defines the data structure for the analytics prototype. The analysis is based on typical educational NGO data collection patterns and training program management requirements.

Existing Data Sources (Assumed)

1. Participant Registration Database

Likely System: Excel spreadsheets, Access database, or simple CRM Data Volume: 1,000-5,000 participants over multiple years Update Frequency: Per program enrollment

Core Data Fields

Personal Information
- Participant ID (unique identifier)
- Full name
- Email address
- Phone number
- Date of birth
- Gender
- Home address (city, province, postal code)
Professional Information
- Current position/job title
- Institution/school name
- Institution type (primary, secondary, tertiary, government)
- Years of experience
- Sector (public, private, NGO)
- Previous positions (if collected)
Program Participation
- Programs enrolled in
- Enrollment dates
- Completion status
- Completion dates
- Certificates issued

2. Training Program Records

Likely System: Spreadsheets, learning management system, or file-based records Data Volume: 50-200 programs over multiple years Update Frequency: Per program cycle

Program Data Fields

Program Details
- Program ID
- Program name/title
- Program category (Leadership, Governance, Skills Development)
- Duration (days/weeks)
- Target audience (Educators, School Leaders, Governing Bodies)
- Prerequisites
- Learning outcomes
Delivery Information
- Delivery mode (in-person, online, hybrid)
- Location (city, province, venue)
- Trainer/facilitator names
- Program dates (start/end)
- Capacity limits
- Actual attendance
Program Performance
- Enrollment numbers
- Completion rates
- Participant satisfaction scores
- Trainer evaluations
- Repeat delivery frequency

3. Feedback and Evaluation Data

Likely System: Survey tools (SurveyMonkey, Google Forms), Excel analysis Data Volume: Varies by program participation Update Frequency: Per program completion

Evaluation Data Fields

Program Feedback
- Participant ID
- Program ID
- Overall satisfaction rating (1-5 or 1-10 scale)
- Content quality rating
- Trainer effectiveness rating
- Venue/logistics rating
- Likelihood to recommend
Learning Assessment
- Pre-program knowledge assessment
- Post-program knowledge assessment
- Skill improvement self-rating
- Learning objective achievement
- Application intention ratings
Follow-up Surveys
- 3-month post-program application survey
- 6-month career impact survey
- 1-year outcome assessment
- Ongoing professional development tracking

4. Career Progression Tracking

Likely System: Manual follow-up, LinkedIn tracking, informal networks Data Volume: Limited, inconsistent Update Frequency: Annual or ad-hoc

Career Data Fields

Position Changes
- Participant ID
- Previous position
- New position
- Promotion date
- Institution change (if applicable)
- Salary change (if disclosed)
Professional Development
- Additional qualifications earned
- Professional memberships
- Leadership roles assumed
- Awards or recognition received
- Further training completed

5. Institutional Relationships

Likely System: Contact management, spreadsheets Data Volume: 100-500 institutions Update Frequency: Ongoing relationship management

Institution Data Fields

Institution Profile
- Institution ID
- Institution name
- Institution type
- Location (province, district)
- Size (number of students/staff)
- Contact person details
Engagement History
- Programs utilized
- Number of participants sent
- Frequency of engagement
- Partnership agreements
- Payment/funding arrangements

Data Quality Assessment

Expected Data Completeness

Participant Demographics: 90-95% complete
Program Participation: 100% complete (core business data)
Feedback Data: 60-80% complete (typical survey response rates)
Career Progression: 20-40% complete (challenging to track)
Institution Data: 80-90% complete

Data Quality Challenges

Inconsistent Formats: Different data entry conventions over time
Missing Historical Data: Earlier programs may lack comprehensive records
Follow-up Limitations: Career progression difficult to track systematically
Manual Entry Errors: Typographical errors, duplicate entries
System Changes: Data may be scattered across different systems/formats

Proposed Unified Data Model

Core Entities

Participant Entity

Participant {
  participant_id: UNIQUE_ID
  personal_info: {
    first_name: VARCHAR(50)
    last_name: VARCHAR(50)
    email: VARCHAR(100)
    phone: VARCHAR(20)
    date_of_birth: DATE
    gender: ENUM('Male', 'Female', 'Other', 'Prefer not to say')
  }
  address: {
    street_address: VARCHAR(200)
    city: VARCHAR(50)
    province: VARCHAR(30)
    postal_code: VARCHAR(10)
  }
  professional_info: {
    current_position: VARCHAR(100)
    institution_id: FOREIGN_KEY
    years_experience: INTEGER
    sector: ENUM('Public', 'Private', 'NGO', 'Other')
  }
  created_date: TIMESTAMP
  last_updated: TIMESTAMP
}

Program Entity

Program {
  program_id: UNIQUE_ID
  program_name: VARCHAR(200)
  category: ENUM('Leadership', 'Governance', 'Skills Development', 'Other')
  target_audience: ENUM('Educators', 'School Leaders', 'Governing Bodies', 'Mixed')
  duration_days: INTEGER
  delivery_mode: ENUM('In-Person', 'Online', 'Hybrid')
  description: TEXT
  learning_outcomes: TEXT
  created_date: TIMESTAMP
}

Enrollment Entity

Enrollment {
  enrollment_id: UNIQUE_ID
  participant_id: FOREIGN_KEY
  program_id: FOREIGN_KEY
  program_instance_id: FOREIGN_KEY
  enrollment_date: DATE
  completion_status: ENUM('Enrolled', 'Completed', 'Withdrawn', 'No-Show')
  completion_date: DATE
  certificate_issued: BOOLEAN
}

Program Instance Entity

ProgramInstance {
  instance_id: UNIQUE_ID
  program_id: FOREIGN_KEY
  start_date: DATE
  end_date: DATE
  location: VARCHAR(100)
  trainer_id: FOREIGN_KEY
  capacity: INTEGER
  actual_attendance: INTEGER
}

Feedback Entity

Feedback {
  feedback_id: UNIQUE_ID
  participant_id: FOREIGN_KEY
  program_instance_id: FOREIGN_KEY
  overall_satisfaction: INTEGER
  content_quality: INTEGER
  trainer_effectiveness: INTEGER
  venue_logistics: INTEGER
  likelihood_recommend: INTEGER
  comments: TEXT
  feedback_date: DATE
}

Career Update Entity

CareerUpdate {
  update_id: UNIQUE_ID
  participant_id: FOREIGN_KEY
  position_title: VARCHAR(100)
  institution_id: FOREIGN_KEY
  change_type: ENUM('Promotion', 'Job Change', 'Additional Responsibility')
  change_date: DATE
  salary_change: DECIMAL(10,2) # Optional, sensitive data
  update_source: ENUM('Self-Reported', 'LinkedIn', 'Institution Feedback')
  verified: BOOLEAN
}

Data Integration Strategy

Phase 1: Assessment and Cleanup

Data Discovery: Catalog all existing data sources
Quality Assessment: Identify data quality issues and gaps
Standardization: Create data cleaning and normalization rules
Deduplication: Identify and merge duplicate participant records

Phase 2: Integration and Migration

Data Mapping: Map existing fields to unified data model
ETL Development: Create extraction, transformation, and loading processes
Migration Testing: Validate data integrity during transfer
Backup and Recovery: Ensure original data is preserved

Phase 3: Ongoing Data Management

Data Entry Standards: Establish consistent data entry procedures
Regular Updates: Create processes for maintaining current data
Quality Monitoring: Implement ongoing data quality checks
Privacy Compliance: Ensure POPIA compliance throughout

Privacy and Compliance Considerations

POPIA Compliance Requirements

Lawful Processing: Consent and legitimate interest documentation
Data Minimization: Only collect and process necessary data
Purpose Limitation: Use data only for stated purposes
Accuracy: Maintain accurate and up-to-date records
Storage Limitation: Define retention periods for different data types
Security: Implement appropriate technical and organizational measures

Sensitive Data Handling

Personal Information: Name, contact details, demographic data
Professional Information: Position, institution, performance data
Career Progression: Salary information, performance evaluations
Feedback Data: Opinions and evaluations

Data Retention Policies

Active Participants: Retain during participation plus 2 years
Completed Participants: Retain for 7 years for impact assessment
Withdrawn Participants: Retain basic records for 2 years
Feedback Data: Retain for 5 years for program improvement

Document Version: 1.0 Last Updated: September 2025 Next Review: Post-data discovery phase