​

Data Schema & Codebook: NGO SRM ROI Calculator

​

Table of Contents

1. Introduction
2. Entity Overview
3. Entity Schemas
4. Taxonomies and Codebooks
5. Validation Rules
6. CSV/XLSX Import Templates
7. Data Provenance Requirements
8. Version Control and Updates
9. Error Handling and Recovery
10. References

​

1. Introduction

​

1.1 Purpose

​

1.2 Scope

​

1.3 Document Conventions

​

2. Entity Overview

​

2.1 Core Entities

​

2.2 Entity Relationships

Scenario (1)
  ├── Baseline Mode Selection (1)
  │   ├── HistoricalCostBaseline (0..1) [if historical]
  │   ├── ScenarioPreset (0..1) [if scenario]
  │   └── Incidents (0..N) [if advanced]
  ├── ReductionRateSelection (1)
  ├── Costs (1..N)
  ├── Assumptions (1)
  └── QualitativeModel (0..1)

• Each Scenario selects one baseline mode (historical, scenario, or advanced)
• HistoricalCostBaseline provides simplified input for organizations with cost data
• ScenarioPreset provides evidence-based estimates when historical data unavailable
• Incidents provide granular modeling for advanced users
• ReductionRateSelection defines expected risk reduction from investments
• Multiple Scenarios can be created for comparison (baseline vs. intervention scenarios)

​

2.3 Data Flow

​

3. Entity Schemas

​

3.1 Incident Entity

​

Schema

​

Field Descriptions

• Description: Name or category of the security incident
• Accepted Values: See Section 4.1 for taxonomy
• Examples: “Data Breach (Phishing)”, “Vehicle Theft”, “Staff Kidnapping”, “Office Burglary”
• Guidance: Use specific, descriptive names to differentiate incident types

• Description: Annualized Rate of Occurrence – expected number of times the incident occurs per year (0 or greater; values above 1 indicate multiple occurrences within the same year)
• Interpretation:
  • 0.00 = Incident will not occur (risk eliminated)
  • 0.25 = Expected once every 4 years
  • 0.50 = Expected once every 2 years
  • 1.00 = Expected once per year
  • 12.00 = Expected monthly (12 times per year)
• Data Source: Historical incident count ÷ Number of years observed
• Example: 3 vehicle thefts over 10 years → ARO = 0.30

• Description: Single Loss Expectancy - expected cost in USD if the incident occurs once
• Components:
  • Direct costs: Property damage, theft, medical expenses, ransom
  • Indirect costs: Downtime, productivity loss, legal fees, reputation damage
• Data Source: Historical average cost per incident, or industry benchmarks for rare events
• Example: Average data breach cost = $45,000 (IT recovery + regulatory fines + reputation damage)

• Description: Free-text field for additional context, assumptions, or data sources
• Example: “Loss of beneficiary data, regulatory fines, reputation damage”

• Description: Origin of ARO/SLE estimates (e.g., “Historical incident reports”, “Industry benchmarks”, “Expert judgment”)
• Purpose: Data provenance for audit trail

{
  "incidentType": "Data Breach (Phishing)",
  "aro": 0.30,
  "sle": 45000,
  "notes": "Loss of beneficiary data, regulatory fines, reputation damage",
  "source": "Historical incident reports"
}

{
  "incidentType": "Vehicle Theft",
  "aro": 0.15,
  "sle": 35000,
  "notes": "Loss of field vehicle and equipment",
  "source": "Regional security data"
}

{
  "incidentType": "Staff Kidnapping",
  "aro": 0.05,
  "sle": 250000,
  "notes": "Ransom, evacuation, medical, legal costs",
  "source": "Industry benchmarks"
}

​

3.2 Historical Cost Baseline Entity

​

Schema

​

Field Descriptions

• Description: Total incident costs for the most recent year
• Components: Direct costs (evacuation, medical, equipment replacement) + Indirect costs (program delays, reputational response)
• Data Source: Financial records, incident reports, insurance claims
• Example: 50000 (represents $50,000 in incident costs for 2024)

• Description: Total incident costs for the previous year
• Purpose: Improves baseline accuracy through multi-year averaging
• Guidance: Include if available; single-year data is acceptable
• Example: 45000 (represents $45,000 in incident costs for 2023)
• Validation: Must be ≥0 when provided; leave blank (null) if data unavailable

• Description: Total incident costs for two years ago
• Purpose: Further improves baseline accuracy and handles anomalous years
• Guidance: Include if available; 2-year average is acceptable
• Example: 55000 (represents $55,000 in incident costs for 2022)
• Validation: Must be ≥0 when provided; leave blank (null) if data unavailable

• Prefer three complete years; two is acceptable when only two exist; single-year baselines should be flagged for follow-up.
• If only partial-year data exist, annualise (e.g., quarterly cost × 4) and document the assumption in scenario notes.
• These constraints align with the helper calculateHistoricalBaselineEAL and the methodological guidance in Methods Note §3.1.1.

​

Calculation Logic

EAL_baseline = (year1Cost + year2Cost + year3Cost) / N

{
  "year1Cost": 50000,
  "year2Cost": 45000,
  "year3Cost": 55000
}

{
  "year1Cost": 75000,
  "year2Cost": null,
  "year3Cost": null
}

{
  "year1Cost": 120000,
  "year2Cost": 95000,
  "year3Cost": null
}

​

3.3 Scenario Preset Entity

​

Schema

​

Field Descriptions

• Description: Identifier for the operational context preset
• Accepted Values:
  • low-risk: Low-Risk Stable Environment ($15,000 baseline EAL)
  • medium-risk: Medium-Risk Conflict-Affected Context ($75,000 baseline EAL)
  • high-risk: High-Risk Active Conflict Environment ($200,000 baseline EAL)
• Data Source: GISF member survey results, Humanitarian Outcomes datasets, expert elicitation
• Guidance: Select preset that best matches operational context

• Description: Override the preset baseline EAL with organization-specific estimate
• Purpose: Allows fine-tuning when preset doesn’t match local conditions
• Guidance: Use when organization has partial cost data or context differs significantly
• Example: 90000 (override medium-risk preset $75,000 with$90,000)
• Validation: Must be ≥0 when provided. Leave blank when adopting preset defaults.

{
  "presetId": "medium-risk",
  "customBaseline": null
}

{
  "presetId": "high-risk",
  "customBaseline": 250000
}

{
  "presetId": "low-risk",
  "customBaseline": null
}

​

3.4 Reduction Rate Selection Entity

​

Schema

​

Field Descriptions

• Description: Category of security investment being evaluated
• Accepted Values:
  • physical: Physical Security (guarding, barriers, safe rooms)
  • training: Training & Capacity Building (HEAT, protocols)
  • technology: Technology & Monitoring (GPS, communications, monitoring)
  • comprehensive: Comprehensive SRM Program (layered approach)
• Data Source: GISF research, Humanitarian Outcomes datasets, practitioner interviews

• Description: Confidence level for the reduction estimate
• Accepted Values:
  • conservative: Lower-bound estimate, minimal assumptions
  • moderate: Mid-range estimate based on typical outcomes (recommended)
  • optimistic: Upper-bound estimate, ideal conditions
• Guidance: Choose based on implementation confidence and contextual fit

• Description: Override evidence-based reduction rate with custom percentage
• Range: 0.00 (0%) to 0.95 (95%)
• Purpose: Allows fine-tuning when evidence ranges don’t match specific context
• Guidance: Document rationale for custom rates in assumption log
• Example: 0.35 (35% reduction)

​

Evidence-Based Reduction Rates

{
  "interventionType": "physical",
  "estimateType": "moderate",
  "customRate": null
}

{
  "interventionType": "comprehensive",
  "estimateType": "optimistic",
  "customRate": null
}

{
  "interventionType": "training",
  "estimateType": "conservative",
  "customRate": 0.18
}

​

3.5 Cost Entity

​

Schema

​

Field Descriptions

• Description: Name or category of the cost item
• Accepted Values: See Section 4.2 for taxonomy
• Examples: “Security Training (Staff)”, “Physical Security Upgrades”, “Cybersecurity Tools”, “Security Personnel”
• Guidance: Use descriptive names that reflect the nature of the investment

• Description: Cost amount in USD
• Precision: Whole dollars (no cents required, but decimals accepted)
• Example: 8000 (represents $8,000)

• Description: Time period when the cost is incurred
• Values: 1 = Year 1, 2 = Year 2, 3 = Year 3, etc.
• Constraint: Must be ≥1 and ≤ Time Horizon Years (from Assumptions entity)
• Example: 1 (cost incurred in first year)

• Description: Classification of cost as Capital Expenditure (CAPEX) or Operating Expenditure (OPEX)
• Accepted Values:
  • capex: Capital expenditure (one-time asset purchases: vehicles, equipment, facility upgrades)
  • opex: Operating expenditure (recurring operational costs: salaries, training, maintenance)
• Purpose: Financial reporting and budgeting (does not affect ROI calculation)
• Default: If not provided, cost is assumed to be OPEX

{
  "category": "Security Training (Staff)",
  "amount": 8000,
  "period": 1,
  "capexOpex": "opex"
}

{
  "category": "Physical Security Upgrades",
  "amount": 25000,
  "period": 1,
  "capexOpex": "capex"
}

{
  "category": "Annual Security Personnel",
  "amount": 32000,
  "period": 2,
  "capexOpex": "opex"
}

​

3.6 Assumptions Entity

​

Schema

​

Field Descriptions

• Description: Number of years over which costs and benefits are evaluated
• Recommended Range: 3 to 5 years for most NGO security investments
• Guidance: Match to asset lifespan (e.g., 5 years for vehicles, 10 years for facilities)
• Example: 3 (three-year analysis period)

​

Example Record

{
  "timeHorizonYears": 3
}

​

3.3.1 Organization Context Fields (Optional)

​

3.7 QualitativeModel Entity

​

Schema Overview

​

3.4.1 Dimensions Object

Import note: Use the identifiers below when populating the improvements array. Provide an empty array ([]) to represent “no statements ticked” when regression is false and no improvements were recorded.

• AE – Access to Environment: Ability to operate in insecure areas
• OC – Operational Continuity: Reduced disruptions and faster recovery
• CA – Community Acceptance: Strength and reliability of community relationships
• SW – Staff Wellbeing: Wellbeing, stress, and retention of staff

​

3.8 Scenario Entity

​

Schema

​

Field Descriptions

• Description: Descriptive name for the scenario
• Examples:
  • “Historical Cost Baseline” (simplified approach)
  • “Medium-Risk Scenario Preset” (fallback approach)
  • “Advanced ARO × SLE Model” (granular approach)
• Guidance: Use names that clearly identify the baseline methodology

• Description: Method for calculating baseline Expected Annual Loss
• Accepted Values:
  • historical: Use historical incident costs (recommended)
  • scenario: Use evidence-based preset estimates
  • advanced: Use incident-level ARO × SLE modeling
• Guidance: Choose based on data availability and analysis requirements

• Description: HistoricalCostInput object (see Section 3.2)
• Required: When baselineMode is “historical”
• Purpose: Provides actual incident cost data for baseline EAL calculation
• Guidance: Include 1-3 years of data; more years improve accuracy

• Description: ScenarioSelection object (see Section 3.3)
• Required: When baselineMode is “scenario”
• Purpose: Provides evidence-based baseline EAL when historical data unavailable
• Guidance: Select preset that best matches operational context

• Description: ReductionRateSelection object (see Section 3.4)
• Purpose: Defines expected risk reduction from security investment
• Guidance: Choose intervention type and confidence level based on planned measures

• Description: Array of Incident objects (see Section 3.1)
• Required: When baselineMode is “advanced”
• Purpose: Provides incident-level modeling for granular analysis
• Guidance: Include all material incident types with reliable ARO/SLE estimates

• Description: Array of Cost objects (see Section 3.5)
• Minimum: 0 cost items (baseline scenarios have no SRM spend)
• Purpose: Captures security investment costs for NPV calculation
• Guidance: Include initial investment plus ongoing operational costs

• Description: Single Assumptions object (see Section 3.6)
• Required fields: timeHorizonYears
• Purpose: Provides temporal parameters for financial calculations

• Description: Single QualitativeModel object (see Section 3.7)
• Purpose: Captures non-financial benefits and outcomes
• Guidance: Include for comprehensive ROI analysis including qualitative impacts

​

4. Taxonomies and Codebooks

​

4.1 Incident Type Taxonomy

​

Accepted Values

​

Taxonomy Crosswalk

• Use specific, descriptive names (e.g., “Data Breach (Phishing)” rather than just “Breach”)
• Combine incident mechanism and impact for clarity (e.g., “Ransomware Attack” not “Cyberattack”)
• Document rare or organization-specific incidents in notes field

​

4.2 Cost Category Taxonomy

​

Accepted Values

​

CAPEX vs. OPEX Guidance

• One-time asset purchases with multi-year lifespans
• Examples: Vehicles, facility upgrades, equipment, software licenses (multi-year)

• Recurring operational costs, typically annual
• Examples: Salaries, training, maintenance, annual licenses, insurance

​

4.3 Qualitative Criteria Taxonomy

​

Criteria Definitions

• Organizations may customize weights to reflect strategic priorities
• Weights must sum to 1.0 (e.g., AE=0.40, OC=0.30, CA=0.30 when SW is omitted, or AE=0.40, OC=0.30, CA=0.20, SW=0.10 if access is top priority)
• Scores should reflect incremental improvement over baseline, not absolute performance

​

4.4 Scenario Preset Catalogue

​

4.5 Reduction Rate Catalogue

​

5. Validation Rules

​

6. CSV/XLSX Import Templates

​

6.1 Incidents Template

incidentType,aro,sle,notes,source
Data Breach (Phishing),0.3,45000,Loss of beneficiary data,Historical incidents
Vehicle Theft,0.15,35000,Loss of field vehicle,Regional security data
Office Burglary,0.25,12000,Theft of equipment,Historical incidents

1. incidentType (string, required)
2. aro (decimal, required)
3. sle (number, required)
4. notes (string, optional - can be empty)
5. source (string, optional - can be empty)

• Header row is required
• Decimal separator: period (.) not comma
• No thousands separators in numbers (use 45000 not 45,000)
• Text fields with commas must be quoted (e.g., "Loss of vehicle, equipment")
• Empty optional fields: leave blank (e.g., ...,,,)

​

6.2 Costs Template

category,amount,period,capexOpex
Security Training (Staff),8000,1,opex
Physical Security Upgrades,25000,1,capex
Cybersecurity Tools,12000,1,capex
Annual Security Personnel,30000,1,opex

1. category (string, required)
2. amount (number, required)
3. period (integer, required)
4. capexOpex (enum [capex, opex], optional)

• Same formatting rules as incidents template
• period must be integer (1, 2, 3, etc.)
• capexOpex must be lowercase: capex or opex

​

6.3 Template-Schema Alignment

• CSV column headers must match schema field names exactly (case-sensitive)
• Column order can vary (parser uses header names, not position)
• Extra columns are ignored (allows for organization-specific metadata)
• Missing required columns trigger validation error

1. Upload CSV/XLSX file
2. Parser validates header row (required columns present)
3. Parser validates each data row against schema (Section 5.1)
4. Parser displays validation errors with row numbers
5. User corrects errors and re-uploads
6. Valid data imported into calculator

​

7. Data Provenance Requirements

​

7.1 Minimum Data Quality Standards

• Recommended Historical Window: ≥12 months (preferably 24-36 months)
• Data Source: Historical incident reports, security logs, insurance claims
• ARO Calculation: Number of incidents / Number of years observed
• SLE Calculation: Total cost of incidents / Number of incidents (average)

• Recommended Coverage: Initial investment + at least 2 years of ongoing costs
• Data Source: Budget documents, procurement records, HR salary data
• Precision: Whole dollars acceptable (no cents required)

• Data Source: Organizational consensus (security committee, staff surveys, expert judgment)
• Documentation: Document rationale for each score in pilot notes or scenario descriptions

​

7.2 Source Documentation

• Populate source field for each Incident (e.g., “Historical incident reports 2020-2022”)
• Document cost data sources in scenario notes (e.g., “2023 Budget, Line Item 4.2.1”)
• Maintain audit trail linking calculator inputs to source documents

• Calculator exports include all input data with source fields
• Version control tracks changes to scenarios over time
• Pilot Pack provides templates for data source documentation

​

7.3 Data Update Frequency

• Quarterly: Update incident data if new incidents occur
• Annually: Refresh ARO/SLE estimates based on rolling 3-year historical window
• Ad-hoc: Update immediately if major context shifts (e.g., conflict escalation, pandemic)

​

8. Version Control and Updates

​

8.1 Schema Versioning

• Major version (X.0): Breaking changes (field removed, validation rule tightened)
• Minor version (X.Y): Additive changes (new optional field, new taxonomy value)
• Patch version (X.Y.Z): Clarifications, typo fixes, no schema impact

​

8.2 Backward Compatibility

• Minor and patch updates maintain backward compatibility with previous versions
• Major updates provide migration scripts and grace period for data updates
• Calculator validates schema version on import and alerts users to version mismatches

​

8.3 Taxonomy Evolution

• New incident types, cost categories, or qualitative criteria can be added without schema changes
• Organizations may use custom values not in standard taxonomy (flexibility by design)

• Deprecated taxonomy values remain valid for existing data but discouraged for new scenarios
• Deprecation notices appear in validation warnings (e.g., “This category is deprecated; use alternative category”)

​

9. Error Handling and Recovery

​

9.1 Common Import Errors

​

9.2 Data Validation Workflow

1. Import Attempt: User uploads CSV/XLSX file
2. Validation: Calculator checks all validation rules (Section 5)
3. Error Report: Display errors with row/column numbers and error messages
4. User Correction: User edits CSV/XLSX file to fix errors
5. Re-import: User uploads corrected file
6. Success: Data imported and ready for calculation

Validation Errors Found (3 errors):

Row 2, Field 'aro': ARO must be at least 0 (found: -0.2)
Row 4, Field 'category': Category is required (empty field)
Row 7, Field 'period': Cost period 5 exceeds time horizon 3 years

Please correct these errors and re-upload the file.

​

9.3 Troubleshooting Guide

• Check: File encoding (UTF-8 required)
• Check: Delimiter (comma , required, not semicolon ;)
• Check: Header row matches schema field names (case-sensitive)

• Explanation: Annual benefits (EAL) don’t recover costs within analysis period
• Solution: Extend time horizon, or recognize that qualitative benefits justify investment

• Explanation: Total costs exceed total benefits under current assumptions
• Solution: Review incident assumptions, time horizon, social value proxies, and qualitative scores; consider non-financial justifications

​

10. References

​

10.1 Related Documentation

​

10.2 Implementation References

​

10.3 Standards

​

Document Control

• ✅ Technical Lead - Schema-implementation alignment verified
• ⏳ Product Owner - RFQ Stage 2 requirements fulfilled (pending review)
• ⏳ GISF Stakeholder - Data preparation guidance confirmed (pending review)