The Two BI Reports

The two BI reports every B2B marketing team needs for per-post attribution are the Content-to-Pipeline Path Report and the Content Influence Report. Together they answer the two questions that matter at budget review: which specific content appeared in the buyer paths of deals you closed, and what percentage of current pipeline has touched each asset. The per-post attribution pillar covers the full framework including UTM schema and CRM field setup. This post goes one layer deeper, giving you the exact data model, SQL logic, and reading pattern for both reports so a marketing ops team can build them in Looker, Tableau, or Power BI in a single working session. Salesforce's 2026 State of Marketing (n=4,450) found only 24% of marketing teams track content performance below the channel level. These two reports are what the other 76% are missing.

What are the two BI reports you need for BI reports content attribution?

Standard content analytics report at the channel level: organic search produced 1,400 sessions, email drove 290 form starts, paid social delivered 110 pipeline assists. That data allocates channel budgets. It cannot tell you whether the specific post about sales qualification frameworks showed up in deals your AEs are closing this quarter.

Two reports close that gap, and both share a single data model.

The first is the Content-to-Pipeline Path Report. It shows which specific pieces of content appeared in the buyer paths of deals that closed or advanced to a target stage. You build it by joining web session data to CRM opportunity records on a shared contact identifier. The output is a ranked list of content slugs attached to deal IDs and contract values: which blog posts, webinars, and case studies show up when a deal closes.

The second is the Content Influence Report. It shows what percentage of deals currently active at each pipeline stage have touched each piece of content at least once. Same joined data, different aggregation. The output is an influence rate: "This post appears in 58% of deals currently in the Proposal stage."

The path report is retrospective and informs content production. The influence report is forward-looking and informs sales enablement. Use the first to decide what to build more of. Use the second to decide what to resurface to accounts that are close to closing.

How are these different from first-touch or U-shaped attribution models?

Attribution models assign fractional credit to channels using weighting rules. These two reports do not assign credit. They answer a binary: was this content present in the deal path, and if so, at what deal value? That binary is a SQL join, not a vendor platform. Any team with BI access to session logs and CRM opportunity data can build both reports without purchasing attribution software.

Why do standard marketing analytics tools miss asset-level attribution?

The gap is architectural. Web analytics tools like GA4 track page behavior in session tables. CRM tools track deal outcomes in opportunity tables. These systems do not share a native row-level join key between a web session and a CRM deal record.

GA4 knows a user visited your pricing page three times and your qualification framework post twice before filling out a form. Salesforce knows a contact at Meridian Corp converted and later became a implementation budgetdeal. What neither system does automatically: join the session history of the Meridian contact to the deal record so you can see which posts were in the path.

Most analytics reporting solves this at the channel level because channel attribution is simpler. UTM parameters pass channel data to CRM via hidden form fields, and CRM reports group by channel. That is attribution, but it collapses every piece of content within a channel into one row. "Organic" gets credit. Not the specific post.

Asset-level attribution requires persisting the post slug via utm_campaign from the session through the form fill into the CRM record, then surfacing it in a BI tool that can join both datasets. The campaign tag migration spoke covers how to restructure CRM fields to hold this data. Once that plumbing is in place, the two reports described here are standard aggregations.

What is the join key between the web session layer and the CRM layer?

The join key is the contact email address or CRM contact ID, passed through the form fill. A hidden field on every lead form captures the utm_campaign value (the content slug) and stores it on the CRM contact record alongside the contact's email. That email then connects to every opportunity associated with the contact, creating the session-to-deal link both reports require. The session storage persistence spoke covers how to wire this correctly when visitors arrive on one page and convert on a different one.

How do you build the Content-to-Pipeline Path Report?

The Content-to-Pipeline Path Report answers one question: which content slugs appear most frequently in the session histories of contacts whose opportunities reached a target stage or closed?

The logic in SQL-style pseudocode:

SELECT
  s.utm_campaign        AS content_slug,
  o.opportunity_id,
  o.close_date,
  o.amount,
  o.stage
FROM   web_sessions s
JOIN   crm_contacts   c  ON s.contact_email = c.email
JOIN   opportunities  o  ON c.contact_id = o.primary_contact_id
WHERE  o.stage IN ('Closed Won', 'Proposal', 'Negotiation')
  AND  s.session_date BETWEEN
         DATEADD(day, -180, o.create_date) AND COALESCE(o.close_date, CURRENT_DATE)
ORDER  BY o.amount DESC

The 180-day lookback covers most B2B buying cycles. Adjust to your average sales cycle length. If your CRM tracks multiple contacts per opportunity via contact roles, join on any contact with a role on the deal rather than only the primary contact, or multi-stakeholder paths will be undercounted.

Sort by total contract value of associated deals to identify highest-value content. Sort by frequency to find the most consistently present content regardless of deal size.

What is the minimum data model for the Content-to-Pipeline Report?

Web session table fields required

Four fields are needed: contact_email (or contact_id if your analytics system identifies users before conversion), utm_campaign (the content slug), session_date, and page_domain (for filtering to your own domain on multi-domain setups). If sessions are identified by cookie before conversion, you need a cookie-to-contact mapping table as an intermediate join.

CRM opportunity table fields required

Five fields are needed: opportunity_id, primary_contact_id or a contact-role junction table, create_date, close_date (nullable for open deals), amount, and stage. If stage is stored as a current value only, the path report runs fine. If you want to filter by what stage the deal was in at each content touch, add a stage history table to your join.

How do you build the Content Influence Report?

The Content Influence Report answers: for each piece of content, what percentage of deals currently active at each pipeline stage have at least one session touch on that content?

Same joined dataset, different aggregation:

SELECT
  s.utm_campaign             AS content_slug,
  o.stage,
  COUNT(DISTINCT o.opportunity_id)                              AS opps_that_touched,
  tot.total_opps_in_stage,
  ROUND(
    100.0 * COUNT(DISTINCT o.opportunity_id) / tot.total_opps_in_stage,
  1)                                                            AS influence_rate
FROM   web_sessions s
JOIN   crm_contacts  c   ON s.contact_email = c.email
JOIN   opportunities o   ON c.contact_id = o.primary_contact_id
JOIN (
  SELECT stage, COUNT(DISTINCT opportunity_id) AS total_opps_in_stage
  FROM   opportunities
  WHERE  is_open = TRUE
  GROUP  BY stage
) tot ON o.stage = tot.stage
WHERE  o.is_open = TRUE
GROUP  BY s.utm_campaign, o.stage, tot.total_opps_in_stage
ORDER  BY influence_rate DESC

Run this weekly. An influence rate above 40% on a specific stage means that asset is a near-constant in active deals there. An influence rate under 10% on deals past Qualification means the content exists but is not in the active flow.

How do you interpret an influence rate that drops between pipeline stages?

If a piece of content shows 52% influence at the Awareness stage and 11% at Proposal, two things could be happening. Either the content is genuinely early-funnel, consumed before later stages define the deal shape, and the drop is expected. Or the content is not being used in sales follow-up when it should be. A high early-stage influence rate with low late-stage presence is the signal that the content exists but no one is sending it to in-progress accounts. Sales enablement teams use this drop to identify under-distributed assets.

What data does each report require, and where does it live?

Both reports share a three-layer data model. Getting that model right before building either report prevents the most common build failure: discovering mid-build that one layer lacks the required join key.

Layer 1: Web session data

Source: your analytics system or CDP export. Required fields: session identifier, contact email or cookie ID, utm_campaign value (the content slug), session timestamp, and page domain. Export this as a flat table via BigQuery or Snowflake export from GA4 or your CDP. The utm_campaign value should be the exact content slug. Conversion System dogfoods this via the withUtm() helper in src/templates/shared.ts, which appends the slug as utm_campaign to every outbound CTA link so each post carries its attribution tag through the full path from click to form to CRM.

Layer 2: Form fill and contact data

Source: CRM. Required fields: contact email, first_utm_campaign (the slug captured at first form fill), and contact_id. The hidden form field capturing utm_campaign at submission is the bridge between the session layer and the CRM. Without it, the session data cannot be joined to the opportunity. This is the plumbing that the per-post attribution framework depends on.

Layer 3: Opportunity data

Source: CRM. Required fields: opportunity_id, associated contact IDs via contact roles or a junction table, create_date, close_date, amount, stage, and is_open. If your CRM does not use contact roles on opportunities, substitute the primary contact. You will miss multi-threaded deals but the reports still run and remain meaningful for SMB deal patterns where single-contact opportunities are the norm.

How do you read and act on these reports each week?

The two reports have different refresh cadences because they serve different audiences.

The Content-to-Pipeline Path Report is a quarterly review tool for marketing leadership. Run it at the end of each quarter on the trailing 90 days of closed-won deals. The output drives content production priorities: posts and webinars that appear consistently in high-value deal paths get extended into new formats, deeper guides, or refreshed with updated data. Posts that appear in zero deals over 90 days get evaluated for consolidation into the cluster pillar or removal.

The Content Influence Report is a weekly sales enablement tool. Run it every Monday. The output goes to sales leadership as a brief: here are the three pieces of content touching the highest percentage of deals in Proposal right now, and here are the three with the highest influence in late-stage Negotiation. Sales reps use this to decide what to re-guide to stalled accounts without guessing.

What does a healthy influence rate look like in B2B SaaS?

In a typical 30-90 day B2B SaaS sales cycle, a healthy path report shows the top five content pieces each appearing in 15-35% of closed-won deal paths. An outlier at 50% or above is worth examining: it may be foundational or it may be a piece AEs are sending as a template, which inflates the attribution. The Influence Report on open pipeline typically shows top-performing assets at 30-60% influence in Qualification and 20-40% in Proposal. Influence above 70% usually signals a short sales cycle where one asset does most of the relational work.

How do these reports connect to defending AI spend at the board level?

The standard AI spend defense problem in B2B marketing is a measurement gap. BCG's 2025 Widening AI Value Gap report (n=1,000) found 60% of companies report little or no AI business impact, while 4% create substantial value. The difference is not tool quality; it is measurement rigor. Bain's Commercial Excellence Agenda 2025 (n=1,263) found organizations with marketing-sales-IT alignment on AI goals report 2.9x higher AI measurable movement, largely because aligned organizations build the attribution layer that lets them prove AI spend is working.

The two BI reports here are that attribution layer. Once both are running, an AI content spend defense becomes concrete: here are the AI-produced assets appearing in the Content-to-Pipeline Path Report for Q2, associated with quantified closed-won deal value. Here is their influence rate in current pipeline. Here is the cost to produce them. That is a slide the CFO can plan.

How do you label AI-produced content in the path report?

Add a production_method field to your content metadata table with values of ai_generated, ai_assisted, and human. Join this field to the content slug column in both reports. The path report filtered to production_method = ai_generated shows which AI-produced assets appeared in closed-won paths and at what deal value. The influence report filtered the same way shows AI-produced vs human-produced influence rates side by side. That comparison is the board-level measurable movement slide. The AI measurable movement measurement pillar covers the three-metric framework that makes this calculation board-defensible. A free plan can identify which parts of your attribution layer are missing before you build.

Methodology

This post describes two BI reports for per-post content attribution, covering their data model, SQL logic, and reading pattern. It does not claim to report client outcomes. The framework is derived from standard BI reporting practice and public marketing measurement research.

Salesforce's 2026 State of Marketing (n=4,450, global) is a vendor-published research report. The 24% channel-level tracking figure appears without a published sampling methodology, but the sample size and Salesforce's longitudinal reporting history from prior years give it reasonable directional validity as a benchmark for enterprise B2B marketing analytics maturity.

BCG's Widening AI Value Gap 2025 (n=1,000, executive survey, September 2025) and Bain's Commercial Excellence Agenda 2025 (n=1,263, B2B commercial leaders, January 2025) are both executive self-report surveys. The 4%/60% impact split from BCG and the 2.9x measurable movement alignment finding from Bain are used here as directional benchmarks, not precise causal estimates.

The SQL shown is illustrative pseudocode. Field names, join keys, and date arithmetic syntax vary by BI platform and CRM. The BI reports content attribution use case requires a data team with access to both web analytics exports and CRM opportunity records in the same warehouse environment. Most mid-market B2B SaaS teams running Snowflake, BigQuery, or Databricks have this infrastructure in place; teams on analytics-only tools without a warehouse layer will need a staging table to materialize the join before either report can run.