The spreadsheet—where the magic happens
Last week we talked about why you should perform an audit before migrating your content. All of that data you record during your migration audit doesn’t mean very much in its raw form. To turn it into meaningful, actionable information, you’ll need to perform a bit of spreadsheet magic.
See the migration audit template to see an example of the final result.
“Use a spreadsheet to turn data into information” Click To TweetRemember that quality of information usually beats quantity of information. Take the time to figure out what you really need to know about your content. Consider what will help you during the migration, and what will be useful when you’re developing and maintaining the content afterward. Ask stakeholders what they consider important.
Before you start waving your wand, you need to decide what results you’re looking for. Here are few examples of what your spreadsheet can do for you:
During your migration audit
- Highlight the entries that still need to be audited.
- Calculate an average of how many entries you audit each day.
- Estimate the date your audit work will be complete.
- Show a progress bar for your overall migration effort.
- Display entries assigned to a particular auditor.
- List content that has been marked for deletion.
After your migration audit
- Count the number of images and links found during the migration audit.
- Compare the number of articles audited to the number of articles that will exist on the new platform.
- See how many articles were marked to be kept, rewritten, deleted, split, or merged.
- See how many articles were set to each post-migration content type.
That’s some useful information! Now, think about how well that would integrate into your progress updates and final report. Maybe spreadsheets aren’t so dull after all?
Our content migration audit template
How do we accomplish all that nifty data-wrangling? It’s easier to show than tell.
If you haven’t already, open our migration audit template and explore for yourself.
The Entries sheet holds the data about your content. That’s where you’ll do your migration audit work. With the help of Named Ranges, you can use drop-down menus to fill in the data.
From there, the two Calculations sheets collect the data and start turning it into useful information.
The Dashboard displays that information in easy-to-read charts.
When you’re ready to starting working on your own copy, click File, and then click Make a copy.
Learn from the best
When you starting building or customizing your own migration audit spreadsheet, you’ll probably need to boost your spreadsheet know-how. The learning curve is friendly enough, but the formulas can get a bit complex.
Working with spreadsheet formulas is a bit like programming. Start simple, and build from there. Logical thinking and attention to detail will guide you through most trouble you might run into.
Keep these resources within arm’s reach, and don’t be shy about using them. Remember: even wizards need reference books.
Here are some resources you can use to build or customize your own auditing spreadsheet:
- Google Docs support – The official support pages are the best place to learnout how to use formulas in Google Sheets. Their interactive example worksheets are excellent. Use the search bar to find what you need.
- Exceljet – This cheatsheet will help you find the formula you need by a plain-English description. Example worksheets included.
- Automate Excel – This site offers free interactive tutorials on keyboard shortcuts, formulas, and functions.
Choose your spreadsheet technology
You’ll notice that some of those resource links are for Google Sheets, and some are for Microsoft Excel. Until you get into really advanced work, the two platforms have very similar capabilities. They even use a lot of the same formulas and approaches. How do you choose which to use?
Google Sheets
Google Sheets is unsurpassed in collaborative abilities. You can see who is working on the sheet, and any changes they make appear in real time as they type. If you’ve never worked like this before, believe me, it’s incredible.
This makes Google the obvious choice if you plan to have a team of people auditing your content. No need to divide content into different files and re-sync later, and all of your progress information will be up-to-the-second accurate.
Google Sheets also offers a more modern interface. For example, you can drag-and-drop rows and columns. Your formulas will still work when you move the cells they reference.
The catch? You need to have a Google account. That might sound like a small thing, but in some organizations this can be a hurdle.
Microsoft Excel
Microsoft Excel has been the ubiquitous spreadsheet program for decades. You can email an Excel sheet to anyone and have confidence that they will be able to open it.
The fact that it’s a desktop application means it’s a bit more full-featured than Google Sheets. It has the ability to crunch bigger numbers and produce highly customized charts. Google Sheets have cell limits and formula limits that Excel doesn’t have, so if you want to collect vast amounts of data in a single column or cell, consider using Excel.
Can you switch platforms?
The short answer is yes. However, be prepared to do a bit of manual cleanup afterward.
Google Sheets, being the relative newcomer, has worked hard to make it easy for you to import an Excel sheet to the Google platform. Just upload your Excel file to your Google Drive, and then open it in Google Sheets. Your original file will stay untouched, and a Google Sheets clone will open for editing.
If you started in Google Sheets and want an Excel spreadsheet, you’re all set. You can download your Google Sheet as an Excel file with just a couple of clicks.
Time to pick up that wand
A thorough migration audit will pay off in a big way. It’s an opportunity for you to get rid of content you no longer need, and collect a lot of insights about the stuff you’re going to keep.
Your smart spreadsheet will let you turn your raw data into all kinds of great information that will guide you through the migration. Then, when it comes time to present your results to the project stakeholders, those charts, graphs, and concrete numbers will make you the hero of the day!
View the migration audit template.
Further reading
How to audit content for migration part I: The what, how and why
