# JASANZ Certification Data Analysis Process

## Overview
This document outlines the step-by-step process used to consolidate and analyse JASANZ certification data across three ISO management system standards: ISO 9001 (Quality), ISO 14001 (Environmental), and ISO 45001 (Occupational Health & Safety).

---

## Step 1 — Source Data Ingestion
Four CSV extracts were obtained from the JASANZ register:

| File | Standard | Records |
|---|---|---|
| `ISO_14001.csv` | ISO 14001:2015 (Environmental) | 8,449 |
| `ISO_45001.csv` | ISO 45001:2018 (OH&S) | 9,368 |
| `ISO_9001_Big6_Request.csv` | ISO 9001:2015 (Quality — major CBs) | 5,919 |
| `ISO_9001_TheRest_December_2025.csv` | ISO 9001:2015 (Quality — remaining CBs) | 8,660 |

All files share the same schema:
- **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
- **ExpiryDate** — Certificate expiry date

---

## Step 2 — Merge into Single Workbook
All four source files were concatenated into a single Excel workbook.

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

No deduplication or filtering was applied at this stage — the sheet is a direct union of all source data.

---

## Step 3 — Split Data by Certification Body
The merged dataset contains **50 unique Certification Bodies**. The data will be split by the `CertifiedBy` field to enable per-CB analysis.

### Certification Bodies Identified (sorted by certificate count)

| # | Certification Body | Certificates |
|---|---|---|
| 1 | SAI Global Certification Services Pty Ltd operating as Intertek & Intertek SAI Global | 3,924 |
| 2 | Citation Certification Pty Ltd | 3,375 |
| 3 | Compass Assurance Services Pty Ltd | 3,071 |
| 4 | Equal Assurance Pty Ltd | 2,643 |
| 5 | Global-Mark Pty Ltd | 2,394 |
| 6 | Global Compliance Certification Pty Ltd | 1,703 |
| 7 | Sustainable Certification Pty Ltd | 1,477 |
| 8 | Business Systems Certification Pty. Ltd | 1,243 |
| 9 | TQCS International Pty Ltd, also operating as TQCSI-Yaran Pty Ltd | 1,095 |
| 10 | Bureau Veritas Australia Pty. Ltd. | 1,064 |
| 11 | DNV Business Assurance Australia P/L | 980 |
| 12 | Southpac Certifications Pty Ltd | 870 |
| 13 | DLCS International Pty Ltd Trading As DLCS | 784 |
| 14 | Compliance Australia Certification Services | 688 |
| 15 | ECAAS Pty Ltd t/as ECAAS Certification International | 624 |
| 16 | SGS Australia Pty Ltd | 555 |
| 17 | ATLAS Certification Pty Ltd | 516 |
| 18 | BSI Group ANZ Pty Limited | 500 |
| 19 | Sci Qual International Pty Ltd | 493 |
| 20 | Global Registrar of Systems Pty Ltd | 490 |
| 21 | LRQA Limited | 451 |
| 22 | QSERC Pty Ltd | 429 |
| 23 | Adaptive Services Consulting Pty Limited T/A Adaptive Certifications | 396 |
| 24 | Certifii Pty Ltd trading as Certifi International | 372 |
| 25 | Integrated Quality Certification Private Limited | 356 |
| 26 | Ashburton Register Pty Ltd trading as Ashburton Assurance Australasia | 287 |
| 27 | Management Systems Certification Global Pty Ltd | 271 |
| 28 | ECAAS Pty Ltd | 226 |
| 29 | Certification Partner Global FZ LLC, trading as Certification Partner Global (Aust) Pty Ltd in Australia | 170 |
| 30 | Guardian Independent Certification Limited | 153 |
| 31 | Obsequentia Pty Ltd | 144 |
| 32 | DQS Certification AUSNZ Pty Ltd | 94 |
| 33 | Certex International Pty Ltd | 81 |
| 34 | Global Management Certification Pty. Ltd. | 81 |
| 35 | BSCIC Certifications Private Limited | 80 |
| 36 | Certification Oceania Pty Ltd | 60 |
| 37 | Institute for Healthy Communities Australia Certification Pty Ltd | 49 |
| 38 | HDAA Australia Pty Ltd | 29 |
| 39 | ICG Compliance Pty Ltd | 29 |
| 40 | ApprovalMark International Pty Ltd | 25 |
| 41 | Quality Control Services (Environmental) Pty Ltd | 24 |
| 42 | Telarc Limited | 21 |
| 43 | ComplyNet Pty Ltd | 18 |
| 44 | AVA Certification Pty Ltd | 16 |
| 45 | MS Certification Pty Ltd | 15 |
| 46 | Quantum Certification Services Pty Ltd | 12 |
| 47 | Resources, Safety and Health Queensland (RSHQ) trading as Safety in Mines Testing and Research Station (Simtars) | 10 |
| 48 | Scientific Certification Systems, Inc., DBA SCS Global Services | 4 |
| 49 | Australasian Certification Authority for Reinforcing and Structural Steels Ltd | 3 |
| 50 | QMS International Certifications Pty Ltd | 1 |

### Split Method: Separate Sheets per Certification Body
Each of the 50 certification bodies 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 are ordered by certificate count (descending)
- **Data**: Each sheet contains only the rows for that specific certification body, with all original columns preserved

### Workbook Structure Summary
`QES_Merged.xlsx` now contains **51 sheets**:
1. `Merged_Full` — Complete dataset (32,396 records)
2. 50 individual CB sheets — One per certification body

---

## Step 4 — 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 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)

### Example
Compass Assurance Services has 3,071 certificate records, but many organisations hold multiple certifications. For instance, *Ampol Australia Petroleum Pty Ltd* appears 3 times (rows 46–48) across different ISO standards — this counts as **1 unique client**. After deduplication, Compass has **1,538 unique clients**.

### Results — Unique Client Count by Certification Body

| # | Certification Body | Total Records | Unique Clients |
|---|---|---|---|
| 1 | SAI Global / Intertek | 3,924 | 2,379 |
| 2 | Compass Assurance Services | 3,071 | 1,538 |
| 3 | Citation Certification | 3,375 | 1,479 |
| 4 | Equal Assurance | 2,643 | 1,087 |
| 5 | Global-Mark | 2,394 | 1,043 |
| 6 | Global Compliance Certification | 1,703 | 684 |
| 7 | Bureau Veritas Australia | 1,064 | 578 |
| 8 | Sustainable Certification | 1,477 | 567 |
| 9 | TQCSI-Yaran | 1,095 | 560 |
| 10 | DNV Business Assurance Australia | 980 | 513 |
| 11 | Business Systems Certification | 1,243 | 442 |
| 12 | Southpac Certifications | 870 | 392 |
| 13 | DLCS | 784 | 322 |
| 14 | BSI Group ANZ | 500 | 308 |
| 15 | Compliance Australia Certification Services | 688 | 306 |
| 16 | ECAAS Certification International | 624 | 302 |
| 17 | SGS Australia | 555 | 295 |
| 18 | LRQA | 451 | 294 |
| 19 | Sci Qual International | 493 | 271 |
| 20 | ATLAS Certification | 516 | 209 |
| 21 | Global Registrar of Systems | 490 | 197 |
| 22 | Certifi International | 372 | 174 |
| 23 | QSERC | 429 | 164 |
| 24 | Adaptive Certifications | 396 | 164 |
| 25 | Integrated Quality Certification | 356 | 152 |
| 26 | Management Systems Certification Global | 271 | 120 |
| 27 | ECAAS Pty Ltd | 226 | 117 |
| 28 | Ashburton Assurance Australasia | 287 | 109 |
| 29 | Certification Partner Global | 170 | 81 |
| 30 | Guardian Independent Certification | 153 | 73 |
| 31 | Certex International | 81 | 68 |
| 32 | Obsequentia | 144 | 61 |
| 33 | DQS Certification AUSNZ | 94 | 60 |
| 34 | Institute for Healthy Communities Australia | 49 | 49 |
| 35 | BSCIC Certifications | 80 | 33 |
| 36 | Global Management Certification | 81 | 32 |
| 37 | Certification Oceania | 60 | 29 |
| 38 | HDAA Australia | 29 | 29 |
| 39 | ICG Compliance | 29 | 29 |
| 40 | ApprovalMark International | 25 | 23 |
| 41 | Telarc | 21 | 13 |
| 42 | Quality Control Services (Environmental) | 24 | 12 |
| 43 | Quantum Certification Services | 12 | 12 |
| 44 | RSHQ / Simtars | 10 | 10 |
| 45 | AVA Certification | 16 | 8 |
| 46 | ComplyNet | 18 | 6 |
| 47 | MS Certification | 15 | 5 |
| 48 | Australasian Certification Authority | 3 | 3 |
| 49 | SCS Global Services | 4 | 2 |
| 50 | QMS International Certifications | 1 | 1 |
| | **TOTAL** | **32,396** | **15,405** |

### Key Observations
- The total of 32,396 certificate records maps to **15,405 unique client-CB relationships** — meaning on average each client holds ~2.1 certificates with their CB.
- The top 5 CBs by unique client count (SAI Global, Compass, Citation, Equal Assurance, Global-Mark) collectively account for **7,526 unique clients** (48.9% of all client-CB relationships).
- Some CBs have a high records-to-clients ratio (e.g. Citation: 3,375 records / 1,479 clients = 2.28x), indicating many multi-standard clients.

### 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 — Master Summary Sheet (CB_Summary)

### Objective
Consolidate the Column M (Certification Body) and Column N (Unique Client Count) values from all 50 CB sheets into a single master summary sheet for at-a-glance comparison.

### 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** (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**: 50 (one per CB), sorted largest to smallest

### Workbook Structure (Updated)
`QES_Merged.xlsx` now contains **52 sheets**:
1. `Merged_Full` — Complete dataset (32,396 records)
2. `CB_Summary` — Master summary of all CBs ranked by unique client count
3. 50 individual CB sheets — One per certification body

---

## Step 6 — ISO 27001 Data Ingestion & Merge

### Objective
Extend the analysis to include ISO 27001 (Information Security Management System) certifications using the same methodology established in Steps 1–5.

### Source Data
Two CSV extracts were obtained:

| File | Standard | Records |
|---|---|---|
| `ISO_27001_2013.csv` | ISO/IEC 27001:2013 (legacy version) | 18 |
| `ISO_27001_2022.csv` | ISO/IEC 27001:2022 (current version) | 1,698 |

Both files share the same 5-column schema as the QES data (`CertifiedBy`, `OrgName`, `Scope`, `City`, `ExpiryDate`).

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

---

## Step 7 — ISO 27001 Split by Certification Body

### Method
Applied the same process as Step 3:
- Identified **27 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 (each organisation counted once regardless of how many certificates they hold)
- Wrote summary to **Column M** (Certification Body name) and **Column N** (Unique Client Count) on each `27k_` sheet

---

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

### Method
Applied the same consolidation as Step 5:
- Extracted CB name and unique client count from all 27 CB sheets
- Sorted by unique client count descending
- Created `27001_CB_Summary` sheet (positioned after `27001_Merged`)

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

| # | Certification Body | Total Records | Unique Clients |
|---|---|---|---|
| 1 | Compass Assurance Services | 519 | 512 |
| 2 | Global Compliance Certification | 259 | 245 |
| 3 | SAI Global / Intertek | 238 | 238 |
| 4 | Citation Certification | 139 | 139 |
| 5 | Sustainable Certification | 113 | 112 |
| 6 | Equal Assurance | 86 | 86 |
| 7 | DNV Business Assurance Australia | 58 | 58 |
| 8 | Global-Mark | 50 | 47 |
| 9 | TQCSI-Yaran | 42 | 42 |
| 10 | LRQA | 35 | 32 |
| 11 | Business Systems Certification | 30 | 30 |
| 12 | Southpac Certifications | 26 | 26 |
| 13 | Bureau Veritas Australia | 23 | 23 |
| 14 | Certification Partner Global | 15 | 15 |
| 15 | QSERC | 13 | 12 |
| 16 | DLCS | 12 | 12 |
| 17 | ECAAS Certification International | 9 | 9 |
| 18 | Certifi International | 7 | 7 |
| 19 | BSI Group ANZ | 7 | 7 |
| 20 | Guardian Independent Certification | 6 | 6 |
| 21 | BSCIC Certifications | 6 | 6 |
| 22 | Certification Oceania | 6 | 6 |
| 23 | Obsequentia | 5 | 5 |
| 24 | ATLAS Certification | 4 | 4 |
| 25 | Integrated Quality Certification | 3 | 3 |
| 26 | SGS Australia | 3 | 3 |
| 27 | ECAAS Pty Ltd | 2 | 2 |
| | **TOTAL** | **1,716** | **1,687** |

### Key Observations
- The ISO 27001 market is smaller (1,716 records / 1,687 unique clients) compared to the QES dataset (32,396 records / 15,405 unique clients)
- The records-to-clients ratio is much closer to 1:1 (1.02x) since most organisations hold only one 27001 certificate
- **Compass Assurance Services** leads in 27001 certifications with 512 unique clients — notably ahead of SAI Global/Intertek (238) who lead the QES space
- 27 of the 50 QES certification bodies also operate in the 27001 space; 23 CBs do not hold any 27001 certifications

### Workbook Structure (Updated)
`QES_Merged.xlsx` now contains **81 sheets**:
1. `Merged_Full` — QES complete dataset (32,396 records)
2. `CB_Summary` — QES master summary (50 CBs)
3. `27001_Merged` — ISO 27001 complete dataset (1,716 records)
4. `27001_CB_Summary` — ISO 27001 master summary (27 CBs)
5. 50 QES individual CB sheets
6. 27 ISO 27001 individual CB sheets (prefixed `27k_`)

---

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

---

*Document generated: 2026-02-05*