)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
Sales Ops analysts can build a unified Salesforce opportunity report showing deals with specific products alongside deals with no products at all by importing Opportunity and OpportunityLineItem data into Google Sheets or Excel using Coefficient’s Salesforce connector and applying OR logic in the spreadsheet. Salesforce native reporting cannot do this in a single report. Cross filters do not support OR logic between a product-name filter and a no-product condition simultaneously. The result is that sales teams end up managing two separate reports and manually reconciling them, or miss the no-product deals entirely.
A common challenge for revenue teams auditing deal quality or tracking product attach rates: you need to see specific product deals and product-less deals in the same view to understand the full shape of your pipeline, but Salesforce forces you to choose one or the other.
How to build a unified product-filtered opportunity report
Step 1. Import Opportunity data with all relevant fields
Open Coefficient in Google Sheets or Excel and select Import from Salesforce. Use From Objects and Fields and select the Opportunity object. Pull Name, Amount, StageName, CloseDate, OwnerId and AccountId. Apply any stage or date filters you need to scope the import to active pipeline. This is your base opportunity dataset.
Step 2. Import OpportunityLineItem data to identify product relationships
Create a second import for the OpportunityLineItem object. Pull OpportunityId and the Product2.Name field. This gives you a lookup table of which opportunities have line items and what products they contain. Import to a separate sheet tab.
Step 3. Join and apply OR filter logic in the spreadsheet
In your main opportunity sheet, add a formula column that uses VLOOKUP or COUNTIF against your OpportunityLineItem import to classify each deal: deals where the target product name appears in OpportunityLineItem, deals with no OpportunityLineItem rows at all and all other deals. Filter the table to show only the first two categories. This is the OR logic Salesforce cross filters cannot process.
Step 4. Set a daily refresh and add product categorisation column
Set a daily refresh in Coefficient so both imports stay current as new deals are created and products are added or removed. Add a visible categorisation column labelling each row as “Target Product” or “No Product” so your sales team can see at a glance which type each deal is. Use this column for pivot analysis to track the ratio of product-attached to product-less deals over time.
What you get
Your sales team works from one report instead of two. Product attach rate analysis covers both the deals that have the right products and the deals that have none, which is where the attach rate opportunity actually lives. Deal quality audits stop missing the product-less deals that fall outside Salesforce’s cross filter logic.
Start building your unified product opportunity report today at coefficient.io/get-started.
