# JASANZ Certification Data Analysis Process — December 2024

## Overview
This document outlines the step-by-step process used to consolidate and analyse JASANZ certification data for the **December 2024** snapshot, covering four ISO management system standards: ISO 9001 (Quality), ISO 14001 (Environmental), ISO 45001 (OH&S), and ISO 27001 (Information Security).

---

## Step 1 — Source Data Ingestion

### Source Files
Six data files were obtained from the JASANZ register. Note: the December 2024 source files have a different format to later snapshots — they contain additional geolocation columns and are padded with blank rows. Only rows with valid `CertifiedBy` values were retained.

### QES Standards (CSV files, mixed encoding)

| File | Standard | Total Rows | Valid Records |
|---|---|---|---|
| `14001_Updated_Edit_This.csv` | ISO 14001:2015 (Environmental) | 18,545 | 7,564 |
| `45001_Updated_Edit_This.csv` | ISO 45001:2018 (OH&S) | 18,545 | 8,562 |
| `9001_Top4_Updated_Edit_This.csv` | ISO 9001:2015 (Quality — Top 4 CBs) | 18,545 | 4,441 |
| `9001_TheRest_Updated_Edit_This.csv` | ISO 9001:2015 (Quality — remaining CBs) | 18,545 | 9,261 |

### ISO 27001 (Excel files)

| File | Standard | Total Rows | Valid Records |
|---|---|---|---|
| `ISO27001_2013_Finished.xlsx` | ISO/IEC 27001:2013 (legacy version) | 18,545 | 572 |
| `ISO27001_2022_Finished.xlsx` | ISO/IEC 27001:2022 (current version) | 18,505 | 876 |

### Schema Normalisation
The source files contained additional columns (`Suburb`, `New_Postcode`, `Accurate_Suburb`, `Postcode`, `LEFTCOUNT`, etc.) beyond the standard schema. Only the following columns were retained:
- **CertifiedBy** — Certification Body that issued the certificate
- **OrgName** — Name of the certified organisation
- **Scope** — ISO standard and management system type
- **City** — Location of the certified organisation (renamed from `Suburb`)

Note: `ExpiryDate` was not available in the December 2024 source data.

---

## Step 2 — QES Merge into Single Workbook
The four QES source files were concatenated into a single Excel workbook after normalisation.

- **Output file**: `QES_Merged.xlsx`
- **Sheet name**: `Merged_Full`
- **Total valid records**: 29,828
- **Method**: Pandas `concat()` with `ignore_index=True`, blank/padded rows excluded via NaN filtering, exported via `openpyxl`
- **Encoding note**: `14001_Updated_Edit_This.csv` required `latin-1` encoding; all others used `utf-8`

---

## Step 3 — QES Split Data by Certification Body
The merged QES dataset contains **48 unique Certification Bodies**. Each CB was given its own sheet tab within `QES_Merged.xlsx`. The `Merged_Full` sheet was preserved unchanged as the master dataset.

- **Sheet naming**: Trading names and abbreviated forms were used to fit Excel's 31-character sheet name limit
- **Sort order**: Sheets ordered by certificate count (descending)
- **Data**: Each sheet contains only the rows for that specific CB, with all original columns preserved

---

## Step 4 — QES Unique Client Count per Certification Body

### Objective
Determine the number of **unique clients** each Certification Body services. A "client" is defined as a unique value in Column B (`OrgName`). Organisations holding multiple certifications (e.g. ISO 9001 + ISO 14001 + ISO 45001) are counted only **once** per Certification Body.

### Method
- Iterated through each of the 48 CB sheets
- Extracted all `OrgName` values (Column B), stripped whitespace
- Applied deduplication using a set (case-sensitive, exact match after trim)
- Wrote a summary table on each sheet in **Column M** (Certification Body name) and **Column N** (Unique Client Count)

### Results — QES Unique Client Count by Certification Body

| # | Certification Body | Total Records | Unique Clients |
|---|---|---|---|
| 1 | SAI Global / Intertek | 4,029 | 2,483 |
| 2 | Citation Certification | 3,222 | 1,434 |
| 3 | Compass Assurance Services | 2,705 | 1,397 |
| 4 | Global-Mark | 2,238 | 987 |
| 5 | Equal Assurance | 2,205 | 915 |
| 6 | Sustainable Certification | 1,482 | 569 |
| 7 | Global Compliance Certification | 1,258 | 563 |
| 8 | TQCSI-Yaran | 1,036 | 538 |
| 9 | Bureau Veritas Australia | 936 | 522 |
| 10 | DNV Business Assurance Australia | 881 | 489 |
| 11 | Southpac Certifications | 757 | 346 |
| 12 | Business Systems Certification | 1,000 | 340 |
| 13 | Compliance Australia Certification Services | 732 | 328 |
| 14 | LRQA | 495 | 321 |
| 15 | BSI Group ANZ | 504 | 311 |
| 16 | DLCS | 710 | 292 |
| 17 | SGS Australia | 534 | 287 |
| 18 | Sci Qual International | 494 | 278 |
| 19 | ECAAS Pty Ltd | 442 | 229 |
| 20 | ECAAS Certification International | 365 | 179 |
| 21 | ATLAS Certification | 437 | 175 |
| 22 | Global Registrar of Systems | 427 | 173 |
| 23 | Integrated Quality Certification | 380 | 163 |
| 24 | QSERC | 409 | 155 |
| 25 | Certifi International | 278 | 141 |
| 26 | Quality Control Services (Environmental) | 281 | 133 |
| 27 | Management Systems Certification Global | 261 | 120 |
| 28 | Adaptive Certifications | 273 | 117 |
| 29 | Certification Partner Global | 179 | 90 |
| 30 | Obsequentia | 148 | 64 |
| 31 | Certex International | 75 | 62 |
| 32 | Guardian Independent Certification | 125 | 61 |
| 33 | DQS Certification AUSNZ | 87 | 54 |
| 34 | Institute for Healthy Communities Australia | 48 | 48 |
| 35 | HDAA Australia | 38 | 38 |
| 36 | Global Management Certification | 86 | 33 |
| 37 | BSCIC Certifications | 77 | 32 |
| 38 | Ashburton Assurance Australasia | 50 | 28 |
| 39 | ApprovalMark International | 25 | 23 |
| 40 | ICG Compliance | 19 | 19 |
| 41 | Telarc | 23 | 14 |
| 42 | Certification Oceania | 19 | 12 |
| 43 | Quantum Certification Services | 12 | 12 |
| 44 | RSHQ / Simtars | 10 | 10 |
| 45 | ComplyNet | 18 | 6 |
| 46 | AVA Certification | 11 | 5 |
| 47 | Australasian Certification Authority | 4 | 3 |
| 48 | SCS Global Services | 3 | 1 |
| | **TOTAL** | **29,828** | **14,600** |

### Output Location in Workbook
On each CB sheet within `QES_Merged.xlsx`:
- **Column M, Row 1**: Header — "Certification Body"
- **Column M, Row 2**: The full Certification Body name
- **Column N, Row 1**: Header — "Unique Client Count"
- **Column N, Row 2**: The unique client count (integer)

---

## Step 5 — QES Master Summary Sheet (CB_Summary)

### Method
- Extracted the CB name and unique client count from each CB sheet (Column M/N, Row 2)
- Sorted all 48 entries by unique client count in **descending order** (most clients first)
- Created a new sheet called `CB_Summary`, positioned as the **second sheet** in the workbook (after `Merged_Full`)

### Output
- **Sheet**: `CB_Summary` in `QES_Merged.xlsx`
- **Column A**: Certification Body (full name)
- **Column B**: Unique Client Count
- **Rows**: 48 (one per CB), sorted largest to smallest

---

## Step 6 — ISO 27001 Data Merge

### Source Data
| File | Standard | Valid Records |
|---|---|---|
| `ISO27001_2013_Finished.xlsx` | ISO/IEC 27001:2013 (legacy version) | 572 |
| `ISO27001_2022_Finished.xlsx` | ISO/IEC 27001:2022 (current version) | 876 |

### Method
- Concatenated both 27001 source files into a single dataset (after column normalisation)
- Created sheet `27001_Merged` in `QES_Merged.xlsx` (positioned after `CB_Summary`)
- **Total valid records**: 1,448

---

## Step 7 — ISO 27001 Split by Certification Body

### Method
- Identified **24 unique Certification Bodies** in the 27001 dataset
- Created individual sheets for each CB, prefixed with `27k_` to distinguish from QES sheets
- Each sheet contains the CB's filtered data with all original columns preserved

---

## Step 8 — ISO 27001 Unique Client Count per CB

### Method
- Applied the same deduplication logic as Step 4
- Counted unique `OrgName` values per CB sheet
- Wrote summary to **Column M** and **Column N** on each `27k_` sheet

### Results — ISO 27001 Unique Client Count by Certification Body

| # | Certification Body | Total Records | Unique Clients |
|---|---|---|---|
| 1 | Compass Assurance Services | 445 | 438 |
| 2 | SAI Global / Intertek | 252 | 246 |
| 3 | Global Compliance Certification | 196 | 190 |
| 4 | Citation Certification | 114 | 114 |
| 5 | Sustainable Certification | 94 | 93 |
| 6 | Equal Assurance | 60 | 60 |
| 7 | TQCSI-Yaran | 48 | 48 |
| 8 | LRQA | 48 | 46 |
| 9 | Global-Mark | 46 | 41 |
| 10 | DNV Business Assurance Australia | 36 | 36 |
| 11 | Bureau Veritas Australia | 21 | 19 |
| 12 | Business Systems Certification | 13 | 13 |
| 13 | BSI Group ANZ | 13 | 13 |
| 14 | Certification Partner Global | 12 | 12 |
| 15 | QSERC | 12 | 12 |
| 16 | Southpac Certifications | 11 | 11 |
| 17 | DLCS | 11 | 11 |
| 18 | Integrated Quality Certification | 6 | 6 |
| 19 | BSCIC Certifications | 2 | 2 |
| 20 | ECAAS Pty Ltd | 2 | 2 |
| 21 | ECAAS Certification International | 2 | 2 |
| 22 | Guardian Independent Certification | 2 | 2 |
| 23 | Obsequentia | 1 | 1 |
| 24 | Certifi International | 1 | 1 |
| | **TOTAL** | **1,448** | **1,419** |

### Key Observations
- The ISO 27001 market in December 2024 had 1,448 records / 1,419 unique clients
- **Compass Assurance Services** leads with 438 unique clients (30.9% of market)
- 24 of the 48 QES certification bodies also operate in the 27001 space

---

## Step 9 — ISO 27001 Master Summary Sheet (27001_CB_Summary)

### Method
- Extracted CB name and unique client count from all 24 `27k_` sheets
- Sorted by unique client count descending
- Created `27001_CB_Summary` sheet (positioned after `27001_Merged`)

### Output
- **Sheet**: `27001_CB_Summary` in `QES_Merged.xlsx`
- **Column A**: Certification Body (full name)
- **Column B**: Unique Client Count
- **Rows**: 24 (one per CB), sorted largest to smallest

---

## Workbook Structure
`QES_Merged.xlsx` contains **76 sheets**:
1. `Merged_Full` — QES complete dataset (29,828 valid records)
2. `CB_Summary` — QES master summary (48 CBs)
3. `27001_Merged` — ISO 27001 complete dataset (1,448 valid records)
4. `27001_CB_Summary` — ISO 27001 master summary (24 CBs)
5. 48 QES individual CB sheets
6. 24 ISO 27001 individual CB sheets (prefixed `27k_`)

---

## Step 10 — (Next) Further Analysis
*Pending — awaiting direction on analysis requirements.*

---

*Document generated: 2026-02-06*