Time consuming & costly

A challenge our team was faced with was to develop commentary around revenue, hours, headcount, and more metrics.  For instance, instead of showing “Net Revenue: 340M” in a report, the business leaders requested an email that would have more description around the metric.  An example for this metric would be: “For this period, net revenue was 340M, which is 10M above what we planned and 5M over prior year.”  
 

Initially, we worked with a 3rd party to utilize their product around natural language processing.  However, we continued to face issues around customization and timely updates.  After I sat with them to learn more about how their process worked, I concluded we could do the same functionality in SQL.  I came back to our office and got to work in designing a solution.  

Design & Develop

I knew from the start we needed a dynamic, customizable solution. One that could work across all functions and all types of reports.  We also did not want to reengineer the whole process and therefore enhanced the Excel portion of the process vs creating a more online portal.  Each row of the final report required a row in Excel.  

Most of the words of a sentence don’t change from week to week and therefore are static.  For example, from our previous example the underlined words would be static. “For this period, net revenue was 340M, which is 10M above what we planned and 5M over prior year.” In addition to the metric values, the words like “above”, “below”, “over” are also dynamic and are dependent on the value being negative, positive, or within a threshold decided in the configuration process.    

So how does this work?  

Good question.  We start with the configuration.  Anything being passed in from the database is surrounded by “<<<” and “>>>”.  Going back to our example:

“For this period, net revenue was <<<NRActualsCYCP(($#Units))>>>, which is <<<NRPlanCYCP(($#Units))>>>  <<<{{CSHoursActualsPYCPGrowthPct_Metric}}>>>  what we planned and <<<NRActualsPYCP(($#Units))>>> <<<{{CSHoursActualsPYCPGrowthPct_Metric}}>>> prior year.” 

Everything between the “<<<” and “>>>” can be pulled out and looked up in the database to get the value.  Let’s look at what would happen the first time the code comes across the first “<<<”. 

1. Strips out everything between “<<<” and “>>>”

   Result: NRActualsCYCP(($#Units))
   
   

2. Get all text to the left of “((“

   Result: NRActualsCYCP

   1. Look up result to get value in Database

      Result: 340,000,000
      
      

3. Get text between “((“ and “))”

   Result: $#Units

   1. Pass metric value and this text into a function that will return the correct formatting for the value.

      Result: 340M

Overall Result: “For this period, net revenue was 340M, which is 10M above what we planned and 5M over prior year.”

Overall Process Flow

Above I discuss how we bring in the values for each sentence but there is more that goes into the process. One additional complication in the requirements is the need to repeat paragraphs by region or service area. In order to accomplish this in the most efficient way, I again use configuration tables that will allow the paragraphs to just be configured once. The configuration tables then will have the options needing to repeat. Below is an overview of the process flow and below that is an example of one of the stored procedures created.

Savings & Efficiency

As a result of the changes I made, leadership could not renew a contract we had with our 3rd party vendor, saving 150k/year.

Further, because everything was in house now, anything needing to be researched and changed could be handled by our team and not by coordinating with a vendor. We could also more easily incorporate any changes the business needed because of the configuration model implemented.

Below is an example of a final report. Please note- all numbers are made up and some text is masked for confidentiality.