2023 Week 01 | Simple Recipe – Line Item Sales

20 Comments / CRM Analytics / By

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.

Sign up for a dev org:

https://trailhead.salesforce.com/content/learn/projects/quick-start-einstein-analytics/sign-up-for-an-analytics-org

Engineer data in CRMA:

https://trailhead.salesforce.com/content/learn/projects/integrate-local-and-remote-data-in-einstein-analytics?trail_id=wave_analytics_enable_and_produce&trailmix_creator_id=nankam&trailmix_slug=crm-analytics

Dataset

This week uses the data from the standard objects that come with the CRMA Dev-Org. If this is your first challenge, you can sign up for a dev org here: https://trailhead.salesforce.com/promo/orgs/analytics-de

Share

After you finish your workout, share a screenshot of your solutions or interesting insights on Twitter using the hashtags #WOW2023 and #CRMA and tag @genetis, @PreenzJ, @LaGMills and @JaackParry. (Or you can use this handy link to do that)

Also make sure to fill out the Submission Tracker to track your progress and help us judge the difficulty of our challenges.

Solution

Below is a solution by Tim Dries from Biztory (thanks for that!)

The “Total List Price” was intended to be Quantity * List Price. And the data should be filtered by “Closed Won” and not only “Closed”. Opportunities could be closed and lost and we don’t want to include those.

20 thoughts on “2023 Week 01 | Simple Recipe – Line Item Sales”

  6. Alex Waleczek

    Thanks, Tim! Looks good and I actually learned about the “Extract” button from this video.

    For anybody else watching, the “Total List Price” was intended to be Quantity * List Price. And the data should be filtered by “Closed Won” and not only “Closed”. Opportunities could be closed and lost and we don’t want to include those.

    Alex

  8. Klaus

    Hey Alex, I want to give CRMA a try but I’m failing at the very first step 🫣. I set up the dev org following the link, but Analytics Studio is not appearing in my apps. Any recommendations to fix that issue?

  10. Pingback: 2023 Week 03 | Interactive Discount Dashboard – Workout Wednesday

  11. Pingback: 2023 Week 02 | Simple Discount Dashboard – Workout Wednesday

  12. zubair

    Hi All,

    I am a new in CRMA , can you please help when we issues this dataset?, can we use this data set with tableau ?pleae help

  14. Pingback: 2023 Week 04 | Interactions & Custom Queries – Workout Wednesday

  17. Pingback: 2023 Week 07 | Hidden Pages – Workout Wednesday

  18. Pingback: 2023 Week 08 | Hidden Pages Extended – Workout Wednesday

  20. Pingback: 2023 Week 12 | Recipe Scheduling – Workout Wednesday

Comments are closed.

Scroll to Top