2022: Week 23 - PD x WOW Salesforce Opportunities

Challenge by: Lorna Brown

It's that time of year again when we team up with the Workout Wednesday crew! This year we wanted to work with Salesforce data as sometimes it needs a little bit of prep before you can create the charts that you want. For example, if we want to create a gantt chart to see how long each opportunity spent in each stage of the pipeline, we may want to reshape our data and bring 2 tables together.

Remember, although we design these challenges in Tableau Prep, you're more than welcome to tackle them in whatever data prep tool you have at your disposal! Be that PowerBI, CRM analytics, Alteryx, Python, R - we see solutions from each of these tools and encourage you to give it a try and give us a shout on Twitter if you get stuck at all.

Inputs

  1. Opportunity Table (45 fields) 

  2. Opportunity History Table 

Requirements

  • Input the data
  • For the Opportunity table:
    • Pivot the CreatedDate & CloseDate fields so that we have a row for when each opportunity Opened and a row for the ExpectedCloseDate of each opportunity (hint)
      • Rename the Pivot 1 Values field to Date
      • Rename the Pivot 1 Names field to Stage and update the values
    • Update the Stage field so that if the opportunity has closed (see the StageName field) the ExpectedCloseDate is updated with the StageName (hint)
    • Remove unnecessary fields
      • Hint: look at the fields in common with the Opportunity History table
  • Bring in the additional information from the Opportunity History table about when each opportunity moved between each stage (hint)
  • Ensure each row has a SortOrder associated with it (hint)
    • Opened rows should have a SortOrder of 0
    • ExpectedCloseDate rows should have a SortOrder of 11
  • Remove unnecessary fields
  • Remove duplicate rows that may have occurred when brining together the two tables (hint)
  • Output the data 

Output

  • 4 fields
    • OppID
    • Date
    • Stage
    • SortOrder
  • 876 rows (877 including headers)

The full output can be downloaded here

Once you have completed the challenge head over to the Workout Wednesday challenges (Tableau or PowerBI) so that you can use this data set to create & visualise your Salesforce Opportunities Dashboard!  

After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and #WOW2022 and tagging @Datajedininja@JennyMartinDS14@TomProwse1@_Lorna_Brown@LukeStanke@HipsterVizNinja@_hughej, and @YetterDataViz

You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help! 

Comments

Popular posts from this blog

How to use... Custom SQL in Prep Builder

2021: Week 22 - Answer Smash

2021: Week 1