# JASANZ Certification Data Analysis Process — June 2024

## Overview
This document outlines the step-by-step process used to consolidate and analyse JASANZ certification data for the **June 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
Five data files were obtained from the JASANZ register. Note: the June 2024 source files have a simpler schema than later snapshots — only 3 columns (`CertifiedBy`, `OrgName`, `Scope`), with no City or ExpiryDate fields. Two Excel files were missing column headers and required manual assignment.

### QES Standards

| File | Format | Standard | Records |
|---|---|---|---|
| `certifications-14001.csv` | CSV | ISO 14001:2015 (Environmental) | 7,195 |
| `certifications-45001.xlsx` | XLSX (no header) | ISO 45001:2018 (OH&S) | 8,179 |
| `9001complete.xlsx` | XLSX (no header) | ISO 9001:2015 (Quality — all CBs) | 13,288 |

### ISO 27001

| File | Format | Standard | Records |
|---|---|---|---|
| `Certifications-27001.2013.csv` | CSV | ISO/IEC 27001:2013 (legacy version) | 900 |
| `certifications.27001.2022.csv` | CSV | ISO/IEC 27001:2022 (current version) | 420 |

### Schema
All files contained 3 columns (after normalisation):
- **CertifiedBy** — Certification Body that issued the certificate
- **OrgName** — Name of the certified organisation
- **Scope** — ISO standard and management system type

Note: `City` and `ExpiryDate` were not available in the June 2024 source data.

### Data Quality Notes
- `certifications-45001.xlsx` and `9001complete.xlsx` had no column headers — first data row was row 1. Headers were assigned programmatically as `CertifiedBy`, `OrgName`, `Scope`.
- ISO 9001 data was provided as a single complete file rather than split by major/minor CBs.

---

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

- **Output file**: `QES_Merged_June_2024.xlsx`
- **Sheet name**: `Merged_Full`
- **Total records**: 28,662
- **Method**: Pandas `concat()` with `ignore_index=True`, exported via `openpyxl`

---

## Step 3 — QES Split Data by Certification Body
The merged QES dataset contains **50 unique Certification Bodies**. Each CB was given its own sheet tab within the workbook. 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)

---

## 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 are counted only **once** per Certification Body.

### Method
- Iterated through each of the 50 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 | 3,977 | 2,472 |
| 2 | Compass Assurance Services | 2,529 | 1,308 |
| 3 | Global-Mark | 2,148 | 970 |
| 4 | Equal Assurance | 1,994 | 836 |
| 5 | Citation Certification | 1,580 | 708 |
| 6 | Best Practice Certification | 1,550 | 674 |
| 7 | Sustainable Certification | 1,429 | 614 |
| 8 | Bureau Veritas Australia | 952 | 539 |
| 9 | TQCSI-Yaran | 1,031 | 534 |
| 10 | Global Compliance Certification | 1,067 | 490 |
| 11 | DNV Business Assurance Australia | 804 | 435 |
| 12 | ECAAS Pty Ltd | 740 | 376 |
| 13 | LRQA | 554 | 354 |
| 14 | Compliance Australia Certification Services | 699 | 318 |
| 15 | Business Systems Certification | 888 | 309 |
| 16 | SGS Australia | 568 | 306 |
| 17 | BSI Group (Australia and New Zealand) | 477 | 306 |
| 18 | DLCS | 699 | 293 |
| 19 | Southpac Certifications | 623 | 292 |
| 20 | Sci Qual International | 499 | 284 |
| 21 | Quality Control Services (Environmental) | 403 | 197 |
| 22 | Global Registrar of Systems | 412 | 165 |
| 23 | ATLAS Certification | 398 | 159 |
| 24 | Integrated Quality Certification | 354 | 156 |
| 25 | QSERC | 402 | 155 |
| 26 | Certifi International | 243 | 125 |
| 27 | Management Systems Certification Global | 250 | 117 |
| 28 | Adaptive Certifications | 232 | 100 |
| 29 | Certification Partner Global | 151 | 78 |
| 30 | PricewaterhouseCoopers Compliance Services | 125 | 63 |
| 31 | Certex International | 75 | 62 |
| 32 | Obsequentia | 138 | 61 |
| 33 | Guardian Independent Certification | 108 | 54 |
| 34 | DQS Certification AUSNZ | 87 | 54 |
| 35 | Institute for Healthy Communities Australia | 49 | 49 |
| 36 | HDAA Australia | 39 | 39 |
| 37 | BSCIC Certifications | 83 | 35 |
| 38 | Global Management Certification | 86 | 34 |
| 39 | Ashburton Assurance Australasia | 50 | 28 |
| 40 | ICG Compliance | 25 | 25 |
| 41 | ECAAS Certification International | 34 | 22 |
| 42 | ApprovalMark International | 19 | 17 |
| 43 | Certification Oceania | 19 | 12 |
| 44 | RSHQ / Simtars | 10 | 10 |
| 45 | Quantum Certification Services | 10 | 10 |
| 46 | Intertek Certification International Sdn Bhd | 11 | 9 |
| 47 | Telarc | 9 | 6 |
| 48 | ComplyNet | 15 | 5 |
| 49 | AVA Certification | 13 | 5 |
| 50 | Australasian Certification Authority | 4 | 3 |
| | **TOTAL** | **28,662** | **14,273** |

### Output Location in Workbook
On each CB sheet:
- **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 50 entries by unique client count in **descending order**
- Created `CB_Summary` sheet, positioned as the second sheet (after `Merged_Full`)

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

---

## Step 6 — ISO 27001 Data Merge

### Source Data
| File | Standard | Records |
|---|---|---|
| `Certifications-27001.2013.csv` | ISO/IEC 27001:2013 (legacy version) | 900 |
| `certifications.27001.2022.csv` | ISO/IEC 27001:2022 (current version) | 420 |

### Method
- Concatenated both 27001 source files into a single dataset
- Created sheet `27001_Merged` (positioned after `CB_Summary`)
- **Total records**: 1,320

---

## Step 7 — ISO 27001 Split by Certification Body

### Method
- Identified **24 unique Certification Bodies** in the 27001 dataset
- Created individual sheets prefixed with `27k_`

---

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

### Method
- Applied the same deduplication logic as Step 4
- 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 | 397 | 389 |
| 2 | SAI Global / Intertek | 246 | 236 |
| 3 | Global Compliance Certification | 146 | 138 |
| 4 | Best Practice Certification | 94 | 93 |
| 5 | Sustainable Certification | 76 | 74 |
| 6 | TQCSI-Yaran | 58 | 57 |
| 7 | LRQA | 54 | 52 |
| 8 | Equal Assurance | 44 | 44 |
| 9 | Global-Mark | 44 | 38 |
| 10 | DNV Business Assurance Australia | 29 | 28 |
| 11 | BSI Group (Australia and New Zealand) | 21 | 21 |
| 12 | Bureau Veritas Australia | 22 | 20 |
| 13 | Citation Certification | 18 | 18 |
| 14 | DLCS | 13 | 13 |
| 15 | PricewaterhouseCoopers Compliance Services | 12 | 12 |
| 16 | Certification Partner Global | 12 | 11 |
| 17 | QSERC | 11 | 11 |
| 18 | Business Systems Certification | 6 | 6 |
| 19 | Southpac Certifications | 6 | 6 |
| 20 | Integrated Quality Certification | 5 | 5 |
| 21 | BSCIC Certifications | 2 | 2 |
| 22 | ECAAS Pty Ltd | 2 | 2 |
| 23 | Certifi International | 1 | 1 |
| 24 | Guardian Independent Certification | 1 | 1 |
| | **TOTAL** | **1,320** | **1,278** |

### Key Observations
- The ISO 27001 market in June 2024 had 1,320 records / 1,278 unique clients
- **Compass Assurance Services** leads with 389 unique clients (30.4% of market)
- **Best Practice Certification** appears in June 2024 data (93 unique 27001 clients) but is absent from later snapshots — likely rebranded or acquired
- 24 of the 50 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`
- **Column A**: Certification Body (full name)
- **Column B**: Unique Client Count
- **Rows**: 24 (one per CB), sorted largest to smallest

---

## Workbook Structure
`QES_Merged_June_2024.xlsx` contains **78 sheets**:
1. `Merged_Full` — QES complete dataset (28,662 records)
2. `CB_Summary` — QES master summary (50 CBs)
3. `27001_Merged` — ISO 27001 complete dataset (1,320 records)
4. `27001_CB_Summary` — ISO 27001 master summary (24 CBs)
5. 50 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*