In your journey to learn CRM Analytics, have you ever felt like there was a gap between an idea and the solution? You wanted to implement a really cool feature, but couldn’t find the right guide to get you there. Something beyond Trailheads and help articles…
Enter AnalyticsKata.
What is a Kata?
A kata is a martial arts practice where individuals repeat a set of movements until mastery is achieved. These exercises are often practiced individually, then reviewed in a larger group setting. This concept of practice over perfection is the core philosophy behind Atrium’s AnalyticsKatas.
What is an AnalyticsKata?
AnalyticsKata was inspired by Dave Thomas’s CodeKata concept. The purpose of CodeKatas, and by extension AnalyticsKatas, is not to come up with a perfect solution, but to learn from the journey of problem solving. AnalyticsKatas have helped skill up our team in CRM Analytics by providing a safe environment in which one can practice new skills and learn from mistakes.
I have led over 50 AnalyticsKata sessions with the Atrium Analytics team, and together we have learned how to handle multiple languages and currencies, forecast diamond prices, calculate your bowling score on the fly, and so much more. All in CRM Analytics!
Today, I am bringing the AnalyticsKata challenges to you! This blog will introduce the first challenge; each blog post going forward will provide a step-by-step solution for the previous kata while providing the challenge for the next one.
Interested? Read on for AnalyticsKata #1!
AnalyticsKata #1: Business Days
Scenario
DataCorp. is a multinational company that is responsible for the creation of all data that has ever existed. They will be the client for our Kata scenarios. They would like to create a dashboard to understand their sales process better, but first they need to transform their data.
Challenge
Data Corp needs to calculate the duration (in Days) of their Opportunities (in all stages).
If the Opportunity is closed, calculate the duration between the created date to Closed Date; if open, calculate the duration of the Opportunity from the created date to today.
This calculation should exclude the weekends (Saturdays and Sundays) — only include business days. (Example: If the Opportunity has been open for two weeks, the total business days (duration) will be only 10 days).
The final value should be stored in a column called Opportunity Duration (Business Days) in a dataset called Opportunity. The entire solution must be completed using a recipe (no dataflow, SAQL, or SFDC formulas allowed).
Data
Create an analytics-enabled dev edition and use the Opportunity data in that org.
Bonus challenge
Complete the recipe challenge in a lens using SAQL.
And now… the solution!
- Create a new recipe, and create an input node to pull in Opportunity data from SFDC.
- From the Opportunity input node, create a transform node. This will hold the days in week calculations.
- First we need to handle both closed and open Opportunity duration. Add a formula step to the transform node. Using the daysinweek function, we will assign a numerical value to the created date column and call the new column “Created Day of Week”:
- Create another formula step and repeat the same process for closed date, called “Closed Day of Week”.
- Add another formula step to the transform node. Using a case statement, we can get the close date (for Opportunities that have a closed stage) or today (for open Opportunities) in the same column. Let’s call that new column “Close/Current Date”.
- We are also going to need the day in week function applied to this new column, so once again we will repeat step 3 to create a “Close/Current Day of Week”:
- Now that we have the day of week value assigned to all the dates needed for our calculation, let’s find the difference between the created date and the calculated close/current date using the datediff function called “Calendar Days Difference”:
- Finally we put it all together in one case statement to get the actual business days:
- First let’s handle the simplest scenario, full 5 day business weeks. We take the difference in days between the created date and the Close/Current date field (CalendarDaysDifference). We divide by 7 to get us to the week and multiply by 5 to identify the business days in those weeks. (Line 1.)
- Next, we determine if the dates occurred during the same week, or adjacent weeks and subtract 5 to handle that. We identify if the created day of week value is less than or equal to the Close/Current day of week value; if an Opportunity is created on a Tuesday and closed on a Thursday, it shouldn’t count more than one week. (Line 2.)
- Finally, we account for weekends and partial weeks. If the Opportunity is created on a Sunday (CreatedDayOfWeek = 1), then we add 5 days. Otherwise we subtract the created day value from 7. We repeat this process for Opportunities closed on Saturday.
Then…
- Call the new field “Opportunity Duration (Business Days)”.
- From the transform node, create an output node and register your new dataset called “Opportunity”.
- Run the recipe, and voila, you have business days!
Stay tuned for more challenges! In the meantime, learn more about our CRM Analytics consulting services.
