Microsoft PowerPoint is used by all kinds of organizations to share information about plans, projects, strategies, and status updates, among many kinds of content. Organizations often create standard PowerPoint templates for their people to use in creating the content. PowerPoint is convenient in that someone can input data and it's immediately available for presentation purposes.
As a result, vast amounts of corporate knowledge lie in PowerPoint documents. While in some cases the documents can be searched easily, comparing data across documents in meaningful ways is a very tedious and time-intensive task. If a company has 50 project plans in PowerPoint documents created from the same template, it is not easy to determine, say, the average duration of a project or how many projects are over budget.
As chief technology officer of SAIC, I wanted to analyze data in PowerPoint documents that laid out business growth plans over multiple time horizons. The documents came from a common PowerPoint template and included text descriptions as well as numerical information, such as market size, growth rates, expected revenues and margins, and so forth. The numerical information was conveyed in text, tables, and bar charts.
Enter robotic process automation
Seeking to avoid spending a weekend tediously copying and pasting data across the documents, I wanted to find out whether robotic process automation (RPA) could be used to effectively automate this process. If so, perhaps RPA could be used to help unlock the knowledge contained in other kinds of PowerPoint documents. The proof-of-concept was to use RPA to extract the data to a Microsoft Excel spreadsheet and use that to perform data analytics.
An engineer at SAIC created an RPA bot in UiPath, an RPA software platform. We discovered that people in our company were pretty consistent in populating our template-based documents correctly and did not modify the templates or add unexpected charts. So, the RPA bot worked well.
The RPA engineer then sent the database to one of our data scientists. Looking through the data, the data scientist immediately ran into challenges with style inconsistencies in the data between the documents. For example, people wrote “$3,000,000,” “$3M,” or “$3,000 (in thousands).” This was not unexpected since PowerPoint doesn’t have data validation, so without guidelines any group of people was likely to be inconsistent with inputs when using a standard form.
The style inconsistencies between documents had to be rectified in the data wrangling process. And as is the case in many data analytics projects, data wrangling was the most time-consuming activity, taking longer than the bot development and execution. Once the data scientist standardized all the data and used Jupyter Notebook for the analysis, he was able to generate different views of the data, including Monte Carlo analysis to predict ranges of expected financial outcomes for the business projects. This proved very useful to me in my decision-making.
The data scientist ran into even larger problems with the text data. Given that PowerPoint is a presentation medium, people inputted data in ways that made the most sense to them for presentation. This led to variances in the text data, and the data scientist would have had to interview the people who made the inputs in order to develop a taxonomy or an ontology that standardized the data. This was not done, since it would have made the proof-of-concept a more expensive and time-consuming activity.
Nevertheless, the data scientist was able to perform basic word frequency analysis on the narrative inputs and construct world clouds to identify themes. Moreover, he did a linear discriminant analysis (LDA) to segment the data into topics with associated words. I found this helpful in identifying general trends and patterns.
The proof-of-concept team got together for a hot wash and determined that RPA could be used to effectively mine knowledge from PowerPoint documents created from the same template so long as the document creators were consistent in how they populated them. Any organization that wants to attempt this activity should expect to do a significant data wrangling effort in order to maximize the value of the extracted data.