July 2017 - October 2018

Excel to Kaplan PDF

Don't ask a designer for PDF update

Kaplan has lots of print materials for the marketing. For example, every year Kaplan publishes price list, school and course fact files, student departure guides and brochures in 15+ languages. Designers open a graphic tool to update new data and to make light design changes, which is a very simple task, but a very heavy burden because they have to manage hundreds pages.

I had interviews with stakeholders to know their inconvenience and requirements. They want to manage PDF content by themselves at any time and publish the updates immediately. Firstly, we thought about developing a web content management system. However, it requires many processes and many human resources and most of all it is difficult to respond quickly to frequent design requests. It could save one designer, but it might need one developer.

We needed more light-weight solution. There was an interesting question, which is whether there is any way to implement it with MS Excel or Google Spreadsheet and I got an idea there. My idea was to take advantage of Excel's data importing function to import data into pre-developed HTML. The idea was able to create a content management system with only Excel.

👨🏻‍💻My role

User research / Visualisation / Frontend dev / Tutorial

🗓️Project period

July 2017 - October 2018

🧑🏼‍💼Client

Aspect International Language

A picture is worth a thousand words

Let me show you how it works.
This is an example of Kaplan Price List. I will change few words in a Google Spreadsheet to update Pricelist cover page texts. This is how Kaplan content editors really do. By changing data in Spreadsheet/Excel, you can manage PDF content and publish it immediately. It's a piece of cake indeed.

How it works

As you can see in the video above, content editors can easily generate a PDF file quickly after content modification and don't really need to ask designers for this tedious task. It could be more powerful with other software that can integrate data such as CPQ. Take a look at the process below.

1. Input Texts

Content Editor needs to enter the required texts within the cells of the sheet that has already been built. Text data will be feeded automatically to pre-templated HTML cells.

* Sheet (Content tab)

2. Data Mapping

Marketing data such as price number will be mapped automatically by CPQ software. (e.g. Apttus) Price data will be feeded automatically to pre-templated HTML cells.

* Sheet (Content tab)

3. HTML & Translation

All data has been imported to pre-formulated cells of HTML tab. At the same time data has automatically transferred to the sheets of translation, where content editors localise content of their sheets.

* Sheet (HTML tab)

4. Create a HTML page

Content editor needs to copy/paste all cells in HTML and save it as .html locally. Then they open the HTML file in their browser to review.

* Text Editor

5. Print & Publish

If there is no problem, Content editor needs to save it as PDF using browser print function. Then upload the PDF file to the server to publish it online.

* Browser/Print

6 print designs

I created these 6 prints using this solution. At first, the solution was introduced to create a small PDF file on pages 5-6, but since then the pricelist has been a task for over 40 pages of pdf files and has been translated into more than 15 languages. Users can directly download them from Kaplan website.

Actual products

These are the actual products that Kaplan has published. You can open the backend spreadsheet and see how they actually work. Also you can download html and pdf files that I created with those sheets. Since the introduction of this solution, the design team no longer need to work on modifying the letters or data in the PDF.

Pricelist (49 pages)
School Factfiles (6 pages)
Departure Guide (8 pages)
Accommodation Factfiles (6 pages)
Course Factfiles (2 pages)
Enrolment Form (1 page)

Retrospective

This is what I felt and what I learned

Made colleagues happy in a simple way

To emphasise again it is very easy for user. However, backstage work was not easy. I had to study further not only Excel/Spreadsheet functions but also javascript to make it easier for my coworkers to take advantage of the solution. When I had a tutorial they were very happy because they noticed it is really simple to use. I believe it is another good example of UX.

Unique = Fun

It was really fun to implement new and creative ideas into products. The solution started with a small hint, but the results had a great effect. Content editors no longer have to worry about fast PDF updates, and designers are released from boring tasks.