2

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?

  • 2
    can you try out writing queries and post along with your question. Secondly how salesperson is linked to Oppty? – Santanu Boral Dec 24 '16 at 17:23
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
  • 1
    If I am not wrong, this prints only sales rep name and number of distinct products he added but not how many times he added each product. As per OP, he wants the product and the number of times it was added by the sales rep. – javanoob Dec 24 '16 at 20:40
  • LakhanP' s answer provided the results I was expecting. I am too new to comment on the other answers but thank all of you for your knowledge and input ! – Soapmuseum Dec 26 '16 at 15:43
  • There are limits it seems when trying to query 50000 opportunities. Here is what I did based on his comment. Not sure how to get the full results without limiting Select COUNT_DISTINCT(ProductId__c),Createdby.Name From MasterCard_Opportunity_Product__c WHERE CreatedDate > 2016-10-01T00:00:00Z Group by CreatedBy.Name LIMIT 200 – Soapmuseum Dec 26 '16 at 15:51
  • Hi @Soapmuseum, Salesforce is a multi tenant platform therefore it has some governor limits for good reasons. In your case, LIMIT may not help to overcome this issue salesforce.stackexchange.com/questions/7439/… You will have to restrict your where clause e.g. CreatedBy.userRoleId In :salesPersonRole – LakhanP Dec 26 '16 at 19:27
1

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.