Claude Code CLI can connect to a GA4 BigQuery export and a CRM export or API in the same session, join session-level acquisition data to opportunity-level pipeline data on a shared key, and run the handful of queries that actually explain why pipeline is breaking, instead of the four or five separate reports a RevOps team normally pulls from GA4, the CRM, and a spreadsheet to guess at the same answer.
Most B2B teams already have both halves of this data. What they don’t have is a routine way to join them. GA4 knows the session. The CRM knows the outcome. Neither one, on its own, can tell you that a specific channel’s leads are filling the top of the funnel and then stalling at SQL for eleven extra days after a lead-routing rule changed three weeks ago. That answer only shows up once the two datasets sit in the same query.
Where the RevOps dashboard hits a wall
Three limits show up constantly for teams trying to diagnose a pipeline problem instead of just monitoring one:
- Stage counts hide the cohort. A dashboard showing “SQL-to-Opportunity conversion: 22%” is a lifetime average. It smooths over a channel that converts at 35% and one that converts at 9%, and it smooths over a bad three-week stretch sitting inside an otherwise fine quarter.
- The CRM source field undercounts the real journey. Forrester’s State Of Business Buying, 2024 report puts the average B2B purchase at 13 internal stakeholders, with 89% of deals crossing more than one department. A CRM field that logs a single “lead source” per deal was never built to represent that, which is exactly why a dashboard built on that field alone tends to be confidently wrong about which channel actually earns credit.
- GA4’s own retention caps the lookback. Google’s documentation on GA4 data retention confirms standard event-level data expires from the GA4 UI at a maximum of 14 months. For a B2B sales cycle that regularly runs longer than that, the GA4 interface has already lost the first-touch session by the time a deal closes. A BigQuery export table does not expire on that schedule once it has landed, which is the difference between a dashboard that can’t answer the question and a queryable table that still can.
None of this means the CRM dashboard is broken. It is built to be glanced at by a rep or a manager without a query language, and it does that job well. It was never built to run a cross-dimensional investigation, and it shows the moment the question gets past “what is our conversion rate” into “why did it change.”
Two data sources, one join key
There are two legitimate data sources here, and a CLI session is what makes joining them practical instead of a one-off engineering project:
- The GA4 BigQuery export schema lands raw, event-level session data in a queryable table: every landing page, channel, device, and campaign parameter attached to a client ID, with none of the row limits an exploration report imposes.
- A CRM export or API is authoritative on the outcome: stage, owner, amount, and close date, but it usually knows the deal’s origin only as whatever got typed or auto-tagged into a single source field at form submission.
The join between them is a shared key, almost always the GA4 client ID or a lowercased, hashed email captured at the same form fill that creates the CRM lead. Where that key does not exist cleanly today, that is the actual first step of this workflow, before any stage math is trustworthy: get the form submission writing the same identifier to both systems. A query joined on a broken key produces a confident, wrong answer just as easily as no join at all.
What each query actually answers
Run in a Claude Code CLI session against the joined table, each of the five queries answers a specific question a dashboard does not ask on its own:
- Session-to-lead stitching is the prerequisite, not an optional first step. It joins the GA4 client ID from the export to the CRM’s lead-create event on the matching key, and immediately surfaces the leads with no matching session at all, phone calls, referrals, and dark-social shares a dashboard built only on UTM parameters cannot see in the first place.
- Stage-transition drop-off computes the actual conversion rate between every adjacent CRM stage, cohorted by the week a lead entered that stage, rather than a single lifetime percentage. A quarter that looks flat on average frequently contains one bad three-week stretch sitting next to two good ones, and the lifetime number never shows which weeks did the damage.
- Channel by stage cross-tab pivots that same drop-off by first-touch channel. This is where a channel that fills the top of the funnel with volume but never survives past SQL becomes visible next to a smaller channel converting three times as well, one that is usually underfunded precisely because the dashboard only reports raw lead counts by channel, not survival rate.
- Time-to-stage velocity calculates the median and 90th-percentile days between each stage transition. This catches the deals a pipeline review misses entirely: not the ones that closed lost, which already have a reason attached, but the ones sitting silently three times longer than normal in a stage nobody has flagged yet.
- Anomaly detection compares each stage’s conversion rate week over week and flags a break the moment it happens, a renamed CRM field, a form validation that shipped quietly, a lead-routing rule that changed who a lead goes to. Caught this way, it is a two-week problem. Caught at quarterly pipeline review, it is a quarter of lost revenue with an unclear cause.
A representative investigation
The pattern that works is hypothesis-driven, the same discipline that makes any CLI-assisted analysis useful rather than misleading: state a specific, falsifiable question, let the session write and run the joined query, look at the actual numbers, then let the answer drive the next question. A representative shape this takes, illustrative only and not a real client’s figures:
That second answer required joining two systems and crossing channel, cohort week, and stage-transition time at once, which is exactly the kind of investigation covered under our Analytics MCP and API integration work. It also sits directly downstream of the workflow we walked through in Claude Code CLI and the GA4 Data API: that piece covers pulling and cross-tabbing GA4 session data on its own, while this workflow joins that same session data to CRM outcomes to answer questions GA4 alone cannot.
| Channel | Leads | SQL → Opp | Median Days in SQL |
|---|---|---|---|
| Organic Search | 142 | 29% | 4 |
| Paid Search | 98 | 26% | 5 |
| Outbound / ABM | 67 | 11% | 15 |
Where this does not replace your CRM or BI stack
A joined BigQuery and CRM workflow is not a wholesale replacement for a RevOps dashboard, and treating it as one is a real failure mode:
- Day-to-day monitoring stays in the CRM or BI tool. Checking whether pipeline is roughly on trend does not need a scripted investigation; it needs a glance at a dashboard, which the CRM already does well.
- The join is only as good as the key. A client ID or hashed email that isn’t consistently captured at form submission produces a partial join and a confidently incomplete answer, which is worse than an obviously missing one.
- A rep’s judgment on a specific stalled deal isn’t something a query replaces. The velocity and anomaly queries tell you where to look. They don’t tell you why a specific champion went quiet; that still takes a person picking up the phone.
Frequently Asked Questions
Does this replace our marketing attribution model?
No. A marketing attribution model assigns credit across touchpoints for a closed deal, and this workflow doesn’t build one on its own. What it does is supply the joined, session-to-outcome dataset an attribution model needs to be accurate in the first place, since a model built only on a CRM’s single source field is working from an incomplete picture of the journey.
How do you run a sales funnel analysis with BigQuery instead of CRM reports?
Land the GA4 BigQuery export and a CRM export or API pull in the same project, join them on a shared identifier such as a client ID or hashed email captured at form submission, then run the stage-transition and channel cross-tab queries described above. The CRM report shows the funnel’s current shape; the joined BigQuery tables show which sessions and channels actually produced each stage of it.
What is pipeline velocity and how is it calculated here?
Pipeline velocity is the speed deals move through stages, typically measured as the median number of days between one stage and the next. The time-to-stage velocity query in this workflow calculates both the median and the 90th-percentile days per transition, which surfaces stalled deals sitting well outside the normal range, not just the average.
Want your GA4 and CRM data joined for this kind of investigation instead of five separate reports? See how MV3 sets up Analytics MCP and API access, or book a call to talk through where your pipeline reporting has the same blind spot.
Share this article
Ryan Brooks leads technical SEO at MV3 Marketing, specializing in schema architecture, entity graphs, crawlability, and the structural signals that determine whether AI answer engines cite a page.
Ready to audit your organic growth opportunity?
$2,500 flat. 5 business days. Six deliverables tied to pipeline , not rankings. No retainer required.
Get the Organic Growth Audit →