How it works
- Step 1Export the GA4 / GSC reports you want to combine — **GA4 standard reports**: top-right export menu → Download file → CSV (max 100,000 rows per file, 5-row metadata preamble). **GA4 Explore**: TSV/CSV from the exploration UI (up to 10,000,000 cells, no preamble). **Search Console**: ZIP with a performance CSV + filters CSV — use only the performance CSV (no preamble, capped at 1,000 rows in the UI). Keep date-range granularity consistent (all monthly, all quarterly) so the optional dedup step is simple.
- Step 2Strip the GA4 preamble per file (before forking) — For GA4 standard reports, run the standalone csv-cleaner tool on each file with
skipRows: 5so row 1 of each output is the canonical header (Page path,Active users,Sessions, …). This matters because the orchestrator's in-browser cleaner node ignoresskipRows. For GA4 Explore / GSC / Looker exports there is no preamble, so skip this. Verify a few report types: some Engagement reports use 6 preamble rows, some Acquisition reports use 4. - Step 3Fork the workflow — Click
Fork to my drafts. The from-blueprint route copies the 3-node graph (csv-merger → csv-cleaner → csv-deduplicator) into a new **private** draft owned by you, snapshots it as version 1, and writes aforkedaudit event. Forks start unscheduled (schedule_cronis null) — you opt into a cron schedule later if you want one. - Step 4Drop the cleaned files onto the merger (Step 1) — Step 1 (csv-merger) is the chain lead and takes the multi-file
csv[]input. Drop all your preamble-stripped files at once, in chronological order (Jan first, Dec last). Strict mode rejects the run if any file's headers differ — for same-property same-report-type files they should always match, so a strict-mode rejection means a stray file slipped in. - Step 5Let Step 2 (csv-cleaner) tidy the merged file — The cleaner trims whitespace, removes empty rows, repairs short/long rows, and folds hidden NBSP / zero-width whitespace from accented page titles. It does not strip a preamble here — that was Step 2-in-the-prep, before the merge. The summary shows
inputRows → outputRows. - Step 6Set the dedup key (Step 3) or skip it — If your exports overlap (e.g.
Jan 1–Mar 31andJan 1–Jun 30both contain March), open Step 3 and set the match columns as tags —Page pathplus your time-grain column (Date/Day) — withstrategy: first. Do not rely on the blueprint's literalPage,Datestring default; set the tags explicitly. Skip Step 3 for non-overlapping ranges, then load the merged CSV into Looker Studio / BigQuery / pandas.
The real blueprint node chain
Exactly what forking places on the canvas, from lib/orchestrator/seo-workflow-blueprints.ts. Edges auto-wire first out-port → first in-port between consecutive nodes.
| Order | Node (registry slug) | Blueprint config | Ports (in → out) |
|---|---|---|---|
| 1 (lead) | csv-merger | mode: strict | csv[] → csv — multi-file input, concatenates |
| 2 | csv-cleaner | trimWhitespace, removeEmptyRows, repairRows, deduplicate: false, normalizeHiddenWhitespace (no skipRows in the browser node) | csv → csv |
| 3 | csv-deduplicator | columns: Page,Date, strategy: first (set columns as tags in the editor) | csv → csv |
Per-export-type preamble handling
How to configure the per-file preamble strip (on the standalone csv-cleaner) for each Google analytics export type before the merge.
| Export type | Preamble? | `skipRows` (standalone cleaner) | Notes |
|---|---|---|---|
| **GA4 standard report** | Yes — 5 rows (property, date range, sampling, blank, headers) | **5** | The pandas community recipe; verify by opening the first 6 lines — Engagement reports sometimes use 6, some Acquisition reports 4 |
| **GA4 Explore (TSV/CSV)** | No | **0** | Explore exports start at the column headers. Up to 10M cells per export |
| **Search Console Performance** | No (headers at row 1) | **0** | GSC UI caps at 1,000 rows per export. For more, use the GSC API or BigQuery Bulk Export |
| **Looker Studio chart download** | No (Excel-compatible mode adds a BOM; Standard CSV doesn't) | **0** | 750,000 rows per chart download; multi-chart reports need per-chart exports |
| **GA4 direct-to-Sheets** | No | **0** | Sheets → File → Download → CSV; the preamble is typically lost in the Sheets round-trip |
Fork, schedule, tier & audit matrix
Verified against the orchestrator runtime: from-blueprint route, cron tick, tier-precheck, and the audit writer.
| Mechanic | How it works for this workflow | Grounded in |
|---|---|---|
| Fork | Copies the exact 3-node graph into a new **private** draft, snapshots version 1, writes a forked audit event. Any authenticated user can fork — no source-workflow permission check. | app/api/orchestrator/workflows/from-blueprint/route.ts |
| Schedule (cron) | Ships **unscheduled** (scheduleCron: null). Cron is a real mechanism — set a 5-field POSIX schedule_cron after forking and a Cloudflare Cron Trigger hits /api/orchestrator/cron/tick, which enqueues a run when due. | seo-workflow-blueprints.ts + cron/tick/route.ts + lib/runner/cron.ts |
| Tier gating | **None.** All three CSV tools default to the free tier (no minTier in the registry), so precheckWorkflowTier returns ok: true for any user. Paid tiers only affect the standalone tools' input-size caps. | lib/orchestrator/tier-precheck.ts + tool-registry.ts |
| Credentials | **None required.** These are pure CSV tools, not connectors — they run in-browser via PapaParse and need no API keys. (Connector nodes, by contrast, run on the paired runner and error in the browser.) | lib/orchestrator/tool-executor.ts |
| Run history / trace | Enterprise audit table workflow_audit_events records forked, run_triggered, run_cancelled, etc. Writes are fire-and-forget — they never block the user path and failures are swallowed to console. | lib/orchestrator/audit.ts + enqueue/route.ts |
Cookbook
Real GA4 / GSC merge scenarios with the exact node configuration. Step 1 = csv-merger (lead), Step 2 = csv-cleaner, Step 3 = csv-deduplicator. Strip the GA4 preamble per file on the standalone cleaner before Step 1.
12 monthly GA4 Pages reports → annual file
ExampleStandard report from one GA4 property, exported month-by-month for 2025. Each file has the 5-row preamble; identical column structure across all 12. Strip the preamble per file, then merge strict.
Inputs (12 GA4 standard report CSVs): ga4-pages-2025-01.csv (5-row preamble + ~2,400 data rows) ga4-pages-2025-02.csv (preamble + ~2,100 rows) ... ga4-pages-2025-12.csv Prep (standalone csv-cleaner, per file): skipRows: 5 -> 12 files, canonical headers on row 1 Step 1 (csv-merger, strict): drop all 12 -> ~27,000-row file Step 2 (csv-cleaner): trim / repair / fold whitespace Step 3 (csv-deduplicator): skip (non-overlapping months)
Multi-property roll-up (3 GA4 properties, same report)
ExampleSame report type (e.g. Acquisition by Source) from 3 GA4 properties. Add a source_property column to each file before merging so downstream can split by property.
Pre-merge: add 'source_property' to each file property-a-acquisition.csv -> source_property='example.com' property-b-acquisition.csv -> source_property='shop.example.com' property-c-acquisition.csv -> source_property='blog.example.com' Prep (standalone csv-cleaner): skipRows: 5 per file Step 1 (csv-merger, strict): schemas match across properties Step 2 (csv-cleaner): tidy merged file Step 3 (csv-deduplicator): optional, if source overlap undesirable Result: one cross-property dataset with property tags.
Overlapping date ranges (Q1 + H1)
ExampleUser exported Jan 1-Mar 31 AND Jan 1-Jun 30 for the same property. The Jan-Mar rows appear in both. Step 3 dedupes them — set the columns as tags in the editor.
Inputs: ga4-q1-2025.csv (Jan-Mar rows) ga4-h1-2025.csv (Jan-Jun rows, includes Jan-Mar again!) Prep: skipRows: 5 per file (standalone cleaner) Step 1 (csv-merger, strict) + Step 2 (csv-cleaner) -> merged file with duplicated Jan-Mar rows Step 3 (csv-deduplicator): columns (as TAGS): Page path, Date strategy: first Result: each (Page, Date) pair appears once.
Looker Studio multi-page report
ExampleLooker Studio caps at 750,000 rows per chart download, so a multi-page report needs N exports. No preamble in Looker output, so no skipRows prep is needed.
Inputs (Looker Studio chart downloads): page1-chart1.csv (Excel-compatible CSV, BOM) page1-chart2.csv page2-chart1.csv ... up to 750k rows each Prep: none (no preamble) Step 1 (csv-merger, strict): drop all chart CSVs Step 2 (csv-cleaner): tidy Step 3 (csv-deduplicator): skip Result: unified Looker Studio dataset.
Schedule a recurring SEO merge after forking
ExampleThe blueprint ships unscheduled. To run monthly, set a cron expression on the forked draft so the Cloudflare Cron Trigger enqueues it. Note: cron runs route through the paired runner, where csv-cleaner's skipRows IS honored.
After forking, set schedule_cron on the workflow row, e.g.:
0 8 1 * * (08:00 UTC on the 1st of each month)
Mechanism (verified):
Cloudflare Cron Trigger -> GET /api/orchestrator/cron/tick
-> reads workflows where schedule_cron is not null
-> isDueSince(parseCron(expr), last_fired_at, now)
-> inserts a workflow_run_queue row (source: 'cron')
-> the runner's dispatch poller claims and executes it
Forks start with schedule_cron = null; you opt in explicitly.Edge cases and verbatim errors
Merge run without stripping the GA4 preamble first
Broken merge — misaligned outputGA4 standard reports prepend 5 metadata rows (property name, date range, sampling status, blank, then headers). If you drop raw GA4 standard CSVs straight onto Step 1, csv-merger in strict mode treats each file's first row (the property-name banner) as its header — different banners per file fail strict matching, or worse, succeed and misalign every column. Strip the preamble per file with the standalone csv-cleaner (skipRows: 5) before the merge.
Relying on the browser cleaner node's skipRows
skipRows ignored in browserThe orchestrator's in-browser csv-cleaner handler hardcodes trimWhitespace, removeEmptyRows: true, repairRows: true, deduplicate: false and does not read skipRows from the node config. So setting skipRows: 5 on the Step 2 node has no effect when you run the workflow in the browser. The standalone csv-cleaner tool and the paired runner bundle DO honor skipRows — use those for the per-file preamble strip.
Strict merge rejects files with different headers
400 / strict-mode rejectionStep 1 runs mode: strict, which rejects the merge if any file's columns differ from the first. This is intentional — same-property same-report-type exports always share headers after the preamble is stripped, so a rejection means a stray report type (e.g. an Acquisition file mixed into Pages files), an un-stripped preamble, or a renamed column. Fix the offending file or switch the node to union mode to fill missing columns with empty cells.
csv-merger gets zero file inputs
error: requires at least one fileThe merge handler throws CSV Merge requires at least one file input if neither a multi-file csv[] payload nor a single file reaches Step 1. This happens when an upstream step emits only text (no blob/blobs) or when nothing is dropped at workflow start. Drop your CSV files directly onto the canvas's entry, or ensure the upstream node emits file outputs.
Dedup key default 'Page,Date' silently mis-keys
Wrong dedup — fix configThe blueprint passes columns: "Page,Date" as a string, but the browser deduplicator reads config.columns as a string array and indexes [0]. Indexing the literal string yields its first character, not the Page column, so the composite key collapses to a single garbled key and the dedup does nothing useful. Open Step 3 and re-enter the match columns as proper tags (e.g. Page path, Date).
Mixing GA4 Standard and Explore in one run
Different preamble requirementsStandard reports carry a 5-row preamble; Explore exports carry none. A single per-file strip can't satisfy both: strip 5 from an Explore file and you delete real data; strip 0 from a Standard file and the merge breaks. Process them as two separate batches — strip 5 from the Standard files, 0 from the Explore files — then merge the cleaned outputs together.
GA4 100,000-row per-file cap
Google documented limitStandard report downloads cap at 100,000 rows (verified per Google's docs). If a single date range exceeds this, data is already lost at export time — the workflow can't recover rows that never made it into the file. Mitigations: switch to GA4 Explore (10M-cell cap), narrow the date range, or apply a segment/filter before exporting.
GSC 1,000-row per-export cap
Google documented limitSearch Console UI exports are 'truncated to 1,000 rows of representative examples of your data' (verbatim from Google's docs). Merging 12 monthly 1,000-row GSC files gives a 12,000-row result, but each month is still capped at 1,000. For complete data, use the GSC API (no cap) or BigQuery Bulk Export — neither produces a CSV that needs this merge.
Same Page URL with different casing across exports
GA4-side ambiguityGA4 occasionally emits the same page URL with different casing across report views (example.com/Blog vs example.com/blog). The deduplicator runs case-insensitive in the orchestrator handler, so casing-only duplicates collapse on the dedup key — but if you need the merged data itself force-lowercased, run `csv-find-replace` (regex mode) on the URL column before Step 1.
Expecting a cron schedule out of the box
Ships unscheduledThis blueprint sets scheduleCron: null, and forks start unscheduled (the fork route copies schedule_cron, which is null). The workflow will not run on a timer until you explicitly set a 5-field POSIX cron expression on the forked draft. Once set, /api/orchestrator/cron/tick (driven by a Cloudflare Cron Trigger) enqueues the run when isDueSince matches.
Frequently asked questions
What is the actual node chain this workflow forks?
Three nodes in this order: csv-merger (strict mode, the chain lead that takes the multi-file csv[] input and concatenates), then csv-cleaner (trims, removes empty rows, repairs short/long rows, folds hidden whitespace), then csv-deduplicator (collapses overlapping rows, strategy: first). This is grounded in the blueprint in lib/orchestrator/seo-workflow-blueprints.ts — the merger leads, not the cleaner.
Why isn't the GA4 preamble stripped inside the workflow itself?
The orchestrator's in-browser csv-cleaner node hardcodes its flags (trim, remove-empty, repair, no-dedup) and does not read skipRows. So the 5-row GA4 metadata preamble must be removed per file BEFORE the merge — run the standalone csv-cleaner tool with skipRows: 5 on each GA4 standard report. The standalone tool and the paired @jadapps/runner both honor skipRows; only the browser orchestrator node ignores it. Cron / runner-routed runs of the workflow DO honor it.
Why does the merger lead instead of the cleaner?
Because the merger is the node with the multi-file csv[] input port — it's the only step that can ingest several exports at once. The blueprint comment makes this explicit: csv-merger takes the multi-file input and concatenates, then the cleaner tidies the merged file. Stripping the GA4 preamble is therefore a per-file prep step you do on the standalone cleaner before you drop the files onto the merger.
Do I need a paid tier to fork or run this?
No. All three tools (csv-merger, csv-cleaner, csv-deduplicator) default to the free tier — none declares a minTier in the registry, so precheckWorkflowTier returns ok: true for every user. The only place tiers matter is the standalone tools' own input-size caps (Free ~2 MB total, Pro effectively unlimited up to browser-memory bounds).
Is my analytics data uploaded to JAD Apps?
No. These are pure CSV tools that run in your browser via PapaParse — page paths, user counts, session data, and search queries never reach a server. They are not connectors (connectors run on the paired runner and need credentials); CSV tools need no API keys. The only server-side write is an anonymous merged-files counter for signed-in dashboard stats. This matters because GA4 custom dimensions can carry PII that most jurisdictions treat as personal data under GDPR / CCPA.
What does 'Fork to my drafts' actually do?
It POSTs the blueprint slug to /api/orchestrator/workflows/from-blueprint, which copies the 3-node graph into a new **private** workflow row owned by you, snapshots it as version 1, and writes a forked audit event tagged with this blueprint slug. There's no source workflow to permission-check, so any authenticated user can fork. The fork starts unscheduled (schedule_cron null) — you opt into cron later.
Can I run this on a schedule?
Yes, but you have to opt in. The blueprint ships scheduleCron: null, so it runs manually until you set a 5-field POSIX cron expression on the forked draft (e.g. 0 8 1 * * for 08:00 UTC on the 1st). A Cloudflare Cron Trigger then hits /api/orchestrator/cron/tick, which reads every workflow with a non-null schedule_cron, checks isDueSince, and enqueues a run. The granularity is roughly 1 minute and the tick walks minute-by-minute to cover gaps.
Is there a run history / audit trail?
Yes — the orchestrator writes to the workflow_audit_events table for events like forked, run_triggered, run_cancelled, and run_resumed. Writes are fire-and-forget by design: they never block the user-facing path and failures are swallowed to console rather than surfaced. This audit table is oriented toward enterprise compliance; the per-run step statuses you see in the UI come from the run panel, not this table.
How do I dedupe overlapping date ranges correctly?
Use Step 3 (csv-deduplicator) but DON'T trust the blueprint's literal columns: "Page,Date" default — the browser handler indexes config.columns as an array, so a bare string mis-keys. Open the node and enter the match columns as proper tags: your URL column (Page path) plus your time-grain column (Date / Day), with strategy: first. The handler dedups case-insensitively. Skip Step 3 entirely if your exports are non-overlapping.
Can I merge GA4 with Search Console data?
Not directly in one chain — they have completely different schemas (GA4 has Sessions/Users/Page paths; GSC has Queries/Pages/Clicks/Impressions/Position). Process each source through its own merge, then align them with csv-header-rename on the shared concept (Page / URL) and a separate csv-merger pass. The result is a denormalised dataset with Sessions and Clicks side by side per URL — useful for SEO/CRO analysis.
How does this differ from the GA4 / GSC cleanup spoke?
The cleanup spoke is for **one** export — strip preamble, fix encoding, dedupe within the file. This workflow is for **multiple** exports — concatenate them (Step 1), tidy the combined file (Step 2), and dedupe across files (Step 3). One file → use the spoke. Many files → use the workflow.
What about Universal Analytics exports?
UA was sunset on July 1, 2023 and its data deleted roughly 6 months later. Archived UA CSVs have a different schema from GA4 (same concepts — Sessions, Users, Pages — but different column names). To cross-merge UA + GA4, align them with csv-header-rename using Google's published metric-mapping cheat sheet, then merge. This workflow targets GA4 + GSC; treat UA archives as a separate header-rename + merge step.
Local-first by design
This workflow executes entirely on your jadapps-runner. API keys, database credentials, and OAuth tokens are stored in an AES-GCM-encrypted vault on your device — they are never uploaded to JAD Apps' servers. The server only stores the workflow graph (the recipe), not the secrets.