2023 Week 01 | Simple Recipe – Line Item Sales

Introduction

Hello and welcome in 2023! If you are here, hopefully one of your new years resolutions is to practise your CRMA skills. The good thing is that it takes less effort than going to the gym and is arguably more fun as well!

From now on you can come back Wednesday each week to find a new problem with varying difficulty to solve.

We kick things off with 4 challenges that build up on each other to work on the basics of CRMA. If you don’t have access to the tool or would like to start from scratch, you can sign up for a new developer org here: https://trailhead.salesforce.com/promo/orgs/analytics-de

This week we focus on a simple recipe to get things going.

Requirements

• Create a dev org or use an existing org you have access to
• Connect to the Opportunity, OpportunityLineItem, Product2 and Account objects
• Join all objects together
• Include only “Closed Won” opportunities for “Customer” accounts
• Calculate
  • the total list price per line item
  • the absolute discount for each line item
  • a string field with the year of the close date of the opportunity
• Aggregate
  • Quantity
  • List Price
  • Total List Price
  • Sale Price
  • Total Price
  • Absolute Discount
• Group By
  • Billing Country
  • Close Date Year
  • Product Family
  • Product Name
  • Opportunity Type
• Output the data into a dataset (we’ll need it next week!)

The resulting dataset should look something like this:

I got 11 columns with 377 rows, but I don’t know if Salesforce changes the default data in their orgs, so use this as a guide.