SOQL query unique products per user

Question

I'm new to SOQL queries. I am trying to write a query that will calculate the total number of UNIQUE products a user has applied to their history of opportunities. The goal being to assess their product diversity usage.

Example: A salesperson has 5 opportunities

• Oppty 1 - Product A
• Oppty 2 - Products B, C
• Oppty 3 - Products A, C
• Oppty 4 - Products A
• Oppty 5 - Product C

Their total product count would be A(3), B(1), C(3). Their UNIQUE products are A,B,C = 3 Products

What would be the best way to calculate this across 50,000 opportunity records considering any governor limits?

Also, can a query like this be written as a SOQL query that can be run in a tool like dataloader or workbench?

Answer 1

You can use following SOQL -

Select COUNT_DISTINCT(Product2Id) From OpportunityLineItem

If sales person is determined by CreatedBy then you can group by above query on CreatedBy field to get the unique product count by each Salesperson. e.g. -

Select COUNT_DISTINCT(Product2Id), Createdby.Name From OpportunityLineItem Group by CreatedBy.Name

Answer 2

Typically when I've been asked to calculate this sort of KPI it was measured by the Opportunity owner. If you want to count the number of times each product has been ordered for each rep, you need to use a GROUP BY clause on PricebookEntry.Product2Id and Opportunity.OwnerId. If you don't care about the Id values and just want names, you would change it to PricebookEntry.Product2.Name and Opportunity.Owner.Name.

Ids

List<AggregateResult> products = [
    SELECT PricebookEntry.Product2Id, Opportunity.OwnerId
    FROM OpportunityLineItem
    GROUP BY PricebookEntry.Product2Id, Opportunity.OwnerId
];

Names

List<AggregateResult> products = [
    SELECT PricebookEntry.Product2.Name product, Opportunity.Owner.Name owner
    FROM OpportunityLineItem
    GROUP BY PricebookEntry.Product2.Name, Opportunity.Owner.Name
];

If you're looking to export this data, I suggest you explore using Reports to aggregate this data. They're already purpose built for export, and you can get the results as a .csv or .xls.

Answer 3

The following is one shot SOQL that provides the Product Group Count across all the Opportunity Line Items for all Opportunities for a given Opportunity Owner. Please provide a valid Opportunity.Owner.Id in the where clause of the query.

select Product2.Name,COUNT(Product2.Id) from OpportunityLineItem
where Opportunity.Owner.Id='<>' group by Product2.Name