2018 Excellence Awards Entry: Slide Compiler - From Alteryx to Powerpoint
Name: Helle Ravn
Company: Danica Pension
Overview of Use Case:
In Alteryx we have built the “Slide Compiler” that creates customer specific slides collected from many different data systems. Alteryx updates text, numbers, as well as graphs in PowerPoint and sends it to our advisors before a meeting with the customer.
Describe the business challenge or problem you needed to solve:
The main problem we wanted to solve was: “How to get more time for our pension advisors to meet with their customers?”
A year ago, our advisers spent 80% of their time on preparation and administration and only 20% time with the customer. We wanted to help them to free more time to spend with the customer and thereby raise customer’s satisfaction. At the same time, we wanted all our advisers to go through the same story line and show the same professional presentation in every meeting with customers. Most importantly, we wanted to make the presentations customer-specific. Every time an adviser has a meeting, they will receive and email, a week in advance, with a pre-meeting .PDF file and a PowerPoint presentation with customer-specific data.
It was important for us still to have the flexibility in the presentation, so the adviser can hide, add, and delete slides before a meeting. At the same time, we know they always have the newest slides with the newest data on all important customer information. We also wanted it to be able to update text, numbers, and graphs automatically in the PowerPoint through Alteryx.
Describe your working solution:
The solution for the “Slide Compiler” and the pre-meeting .PDF file.
1) Workflow 1: combine, summarize, filter data from many different SQL tables and csv files. A robot generates the csv files. The robot go into different systems where we do not have a data table. We use both in-DB and not in-DB in the workflow.The workflow ends up with a dataset with data for one company per line.
2) Workflow 2: determines which companies to make a PowerPoint presentation on (companies with meetings in the next seven days),
3) Workflow 3: combine data from workflow one and two and end with the “Slide-compiler” macro, that make and send the PowerPoint presentation to the adviser.
4) Macro: The “slide-compiler”
1) The PowerPoint presentation is saved in xml format. In the PowerPoint, we have put a unique ref-point all the places we want to put in numbers, text or update graphs.
2) In Alteryx we import the PowerPoint presentation as csv file, with delimiters “|” and code page Unicode UTF-8. We then use a summarize tool to concatenate data, so we get the hole PowerPoint in one string.
3) We make a manual list in Alteryx that tells us which ref points in the PowerPoint to be change with which variables in the data set from workflow 3. We both have ref points in text and in graphs(pie chart and bar chart).
4) We join data from the workflow 3 with the data from the manual list and then we use "the trick."
5) The trick: We use a “find and replace” tool to change the ref points with the data points in the PowerPoint string.
6) We save the PowerPoint presentation as an xml file on a shared drive, because the PowerPoint presentation is too big to send. In Alteryx the file format is csv, with delimiters “\0” and Code Page Unicode UTF-8.
7) In the same macro we also built a pdf file in Alteryx with the layout tool/visualization tool that consist of a lot of different customer specific data, so the adviser do not have to go into many different systems before a meeting. This is the pre-meeting document.
😎 In the end of the flow Alteryx sends an email to the advisor with a link to the customer specific presentation and the pre-meeting .PDF.
9) The flow is scheduled and runs every day.
Furthermore, we have built two apps where the adviser can choose one of their customers in a drop-down list and get the customer specific PowerPoint or the pre-meeting .PDF file.
Describe the benefits you have achieved:
The “Slide-compiler” has been running for a year now. It has generated over 3000 presentations with 80+ slides in each, saving the adviser at least half an hour per meeting and given them more time with the customers (a total of 1500 hours saved). The advisers always have up to date presentations and data on the customers in the meeting. We plan to role this out to more departments next month, so there will be even more customer specific presentations generated going forward.
The “Slide-compiler” has given the advisors the opportunity to spend more time with the customer. Hopefully, this results in more satisfied customers and positive, long-term relations with customers.
Testimonials from three advisers:
“For me, it has given me time to come closer to the customer. By that I mean: I now have time to go to the customer's website, see financial accounts etc. I have time to immerse myself in the preparation. It provides better meetings, greater customer benefit and time for broader knowledge of each other = closer cooperation and hopefully greater customer loyalty” – Anette Pfeiffer
“After we have gotten the slide compiler as a tool in our everyday life, it has both released some administration / preparation time and we have gotten some slides that are relevant to the individual company customer.
Before I spent a lot of time researching customer information, where I now get the information "served on a dish".
Earlier it could also be a challenge which topics the status meeting should address, because all the slides are in the same set, I can "pick" the relevant one” – Tina Hansen
“With the Slide compiler, it is possible for us to have relevant and updated facts in our meeting preparation. Now it is possible to improve a customer meeting in less time while having updated slides in our presentation” – Michael Sørensen