GA4/GTM Cleanup Workbook and Container Data Extraction

The Problem

Following a completed GA4/GTM audit, the client agreed to make structural fixes but the decisions (which UTMs to fix, which cross-domain domains to keep, which events to keep/remove/merge) required input from non-technical marketing stakeholders. Web-based conversion numbers had been unusable for optimization for the whole window.

Approach & Architecture

Programmatically extracted the real Conversion Linker domains and new source/medium combinations from the GTM container and GA4 exports, and the GA4 event tags from the container. Combined those with the audit's event inventory into a plain-language events list. Built a four-tab branded xlsx (Google Sheets-ready) with data-validation dropdowns, colour-coded input cells, example rows, frozen headers and a 'Start here' instructions tab. Validated with LibreOffice recalc and a visual render.

Three Python extraction passes feed three review tabs: (1) GA4 source/medium comparison CSV parsed in Feb-vs-March triplets to isolate new/malformed combos; (2) GTM container JSON parsed for the gclidw (Conversion Linker) tag's linkerDomains and for gaawe (GA4 event) tags; (3) audit event inventory merged with container events into a plain-English list. openpyxl builds the branded workbook with DataValidation dropdowns; recalc.py verifies integrity.

Key Decisions & Trade-offs

Hardest Part

Parsing the GA4 source/medium comparison CSV, whose rows come in per-combo triplets (a % change label row followed by a March row and a February row with blank source/medium), then correctly attributing March vs February session counts to each combo to isolate the new/malformed ones.

Technical Detail

Code

for t in cont.get('tag',[]): if t.get('type')=='gclidw': for p in t.get('parameter',[]): if p.get('key')=='linkerDomains': linker=[x.strip() for x in p['value'].split(',') if x.strip()]
owned={'client.org','client.org'}
other=[x for x in linker if x not in owned] # 54 to confirm/remove

if dc=='% change': cur=sm
elif dc.startswith('Mar') and cur is not None: combos.setdefault(cur,{})['mar']=int(r[3].replace(',',''))
elif dc.startswith('Feb') and cur is not None: combos.setdefault(cur,{})['feb']=int(r[3].replace(',',''))
new=[(k,v.get('feb',0),v.get('mar',0)) for k,v in combos.items() if v.get('feb',0)<=5 and v.get('mar',0)>=20]

dv_krm=DataValidation(type="list", formula1='"Keep,Remove,Merge into another,Not sure"', allow_blank=True)
s3.add_data_validation(dv_krm)
dv_krm.add(s3.cell(row=rr,column=8))

Measured Results

Turned raw GTM/GA4 exports into a branded, four-tab, dropdown-driven decision workbook that let non-technical stakeholders confirm which UTMs, cross-domain domains and events to keep, remove or merge without touching the live setup. Also produced two supporting CSVs (56 linker domains; 41 new source/medium combos). Outcome was an enablement/decision artifact; no performance metric was measured in-chat.

Every figure above was recorded during the work itself. Where no number was measured, none is claimed.

Full Stack