[{'Text': '# GBS MongoDB Cheatsheet\n\n> A living document. Each time you use `read_gbs`, capture what you learn.\n> Patterns, edge cases, useful pipelines, and data quirks.\n\n## Current State\n\n| What | Value |\n|------|-------|\n| Current stats period | **2026Q1** |\n| Current tariffs period | **2026Q2** |\n| Total broadband stats | ~106,000 across 87 periods (back to 1997Q4) |\n| Stats per recent quarter | ~1,800 records, ~680 operators |\n| 2026Q1 progress | 213 records / 96 operators (just started) |\n| UK operators | 141 total |\n| Global operators | 1,300+ across all countries |\n\n## Key People\n\n| Person | 2026Q1 Activity |\n|--------|----------------|\n| veronica.speiser@point-topic.com | 152 recs, 41 operators (since May: 177 recs) |\n| aliciamenendezbuick@gmail.com | 56 recs, 53 operators |\n| gbs-agent | 4 recs, 2 operators (automated) |\n\n## Useful `read_gbs` Pipelines\n\n### 1. See recent data entry activity\n\n```json\n[\n  {"$match": {"type": "broadband", "createdAt": {"$gte": {"$date": "2026-05-01T00:00:00Z"}}}},\n  {"$group": {"_id": "$createdBy", "count": {"$sum": 1}, "operators": {"$addToSet": "$operator"}}},\n  {"$sort": {"count": -1}}\n]\n```\n\n### 2. Check 2026Q1 progress (all operators with data)\n\n```json\n[\n  {"$match": {"period": "2026Q1", "type": "broadband", "is_archived": {"$ne": true}}},\n  {"$group": {\n    "_id": "$operator",\n    "count": {"$sum": 1},\n    "techs": {"$addToSet": "$tech"},\n    "states": {"$addToSet": "$state"}\n  }},\n  {"$sort": {"count": -1}}\n]\n```\n\n### 3. Full period history for any operator (via ObjectId)\n\n```json\n[\n  {"$match": {"operatorId": {"$oid": "662ff7a3f9d5751ffbf300f6"}}},\n  {"$sort": {"period": -1}},\n  {"$project": {"period": 1, "tech": 1, "channel": 1, "domain": 1, "subscribers": 1, "state": 1}}\n]\n```\n\n### 4. Get any operator\'s ObjectId\n\n```json\n[\n  {"$match": {"name": "BT Group", "country": "United Kingdom"}},\n  {"$project": {"_id": 1, "name": 1, "technologies": 1}}\n]\n```\n\n### 5. Gap analysis: which techs had data in previous period but not current\n\n```json\n[\n  {"$match": {"operatorId": {"$oid": "662ff7a3f9d5751ffbf300f6"}, "period": "2025Q4"}},\n  {"$group": {"_id": null, "techs": {"$addToSet": "$tech"}}}\n]\n```\n\n## Data Model Notes\n\n### Statistics Collection\n\n| Field | Type | Notes |\n|-------|------|-------|\n| `_id` | ObjectId | |\n| `operatorId` | ObjectId | references Operator |\n| `period` | string | `YYYYQN` format, e.g. `"2026Q1"` |\n| `type` | string | `broadband` (main), `mobile`, `iptv` |\n| `tech` | string | Free text. **Not** validated against Operator.technologies! |\n| `channel` | string | `Infrastructure` or `Retail` |\n| `domain` | string | `Residential`, `Business`, or `Total` |\n| `subscribers` | number | Integer subscriber count |\n| `state` | number | **1**=pending, **2**=approved, **3**=published |\n| `is_archived` | boolean | Soft delete flag |\n| `createdBy` | string | Email of who entered it |\n\n**Key insight**: The `(operatorId, period, type, tech, channel, domain)` tuple is unique (enforced at app layer).\n\n### Operator Collection\n\n| Field | Type | Notes |\n|-------|------|-------|\n| `_id` | ObjectId | |\n| `name` | string | Display name (BT Group, BT Wholesale Business, Sky UK...) |\n| `country` | string | ISO country name |\n| `technologies` | array of strings | Full list of techs this operator uses (20+ for majors!) |\n| `isArchived` | boolean | |\n\n**Key insight**: Only ~4-5 techs are actually *active with data* at any time, not the full 20+ technology list. Many are historical (ISDN, ADSL, 3G, etc.). The relevant techs are the ones that *appear in Statistics records*.\n\n### GlobalVariables Collection\n\n| Field | Type | Notes |\n|-------|------|-------|\n| `currentStatsPeriod` | string | e.g. `"2026Q1"` — the quarter being filled |\n| `currentTariffsPeriod` | string | e.g. `"2026Q2"` |\n\n## State Machine\n\n```\n1 (pending) ──→ 2 (approved) ──→ 3 (published)\n```\n\n- **pending** (1): Just entered by associate, needs review\n- **approved** (2): Reviewed, correct\n- **published** (3): Final, visible in outputs\n\n## Data Quality Patterns\n\n### Know Your BT Family\n\nThere are multiple BT-related operators in the system. They are **separate operators**:\n\n| Operator | OID | Notes |\n|----------|-----|-------|\n| **BT Group** | `662ff7a3f9d5751ffbf300f6` | The holding company. Has 4 active techs (DSL, FTTP, FTTx, G.fast cabinet). Only has 1 record in 2026Q1 (FTTP). **Missing 3 techs that existed in 2025Q4.** |\n| **BT Wholesale Business** | (different OID) | Wholesale arm. Fully entered for 2026Q1 (all 4 techs, 4 records). |\n\n### Gap Pattern: Techs Get Dropped Between Quarters\n\nWhen a new period starts, associates carry forward last quarter\'s data. If they miss a tech, it silently disappears. The active techs for major UK operators are typically:\n\n- DSL / ADSL / ADSL2+ (declining)\n- FTTP / FTTH (growing)\n- FTTx / VDSL2 (growing then plateauing)\n- G.fast cabinet (niche, Openreach areas)\n- Cable Modem / DOCSIS3.1 / HFC (Virgin Media)\n- FWA / 5G FWA (emerging)\n\n### 2026Q1 Status\n\n- **Fully entered UK majors** (6-8 recs): KCOM, Virgin Media O2, TalkTalk, Sky UK, Vodafone UK, Zen Internet, BT Wholesale Business\n- **Partially entered UK**: BT Group (1 rec), several altnets (2-4 recs each)\n- **Non-UK**: Alicia has entered 53 non-UK operators with 1 record each (just Total/Retail, the easy first pass)\n- **UK altnets with full data** (4 recs each): Hyperoptic, Gigaclear, Community Fibre, toob, and many more\n\n## Patterns to Avoid\n\n### ❌ Don\'t iterate with N+1 queries\n\nIf you need data about multiple operators, use `$lookup` or `$in` in a single pipeline. Each `read_gbs` call costs one mongosh subprocess spawn (~200ms overhead).\n\n### ❌ Don\'t guess subscriber numbers\n\nNever manufacture subscriber counts from news articles, ratios, or estimates. The tool can only report what\'s actually entered.\n\n### ✅ Do check historical periods\n\nWhen analyzing gaps for a target period, always check the immediately **preceding complete period** (e.g. 2025Q4 for 2026Q1). That\'s your carry-forward baseline.\n\n## Writing EJSON in `read_gbs`\n\nThe tool supports MongoDB EJSON syntax for BSON types:\n\n| BSON Type | JSON Syntax | Use Case |\n|-----------|-------------|----------|\n| ObjectId | `{"$oid": "662ff7a3f9d5751ffbf300f6"}` | Match on `operatorId`, `_id` |\n| Date | `{"$date": "2026-05-01T00:00:00Z"}` | Match on `createdAt`, `updatedAt` |\n\nGet ObjectIds by first querying the Operator collection with a name match.\n'}]