10 steps to create a data collection system with Office365

An end-to-end system with quality assurance and analytics

Photo by Lukas Blazek on Unsplash

Why spend thousands of dollars paying programmers to build you a simple data collection system? Unless you’re building something on a large scale for a large audience and performance needs to be guaranteed, this isn’t necessary.

Fortunately, it’s easier than ever to create your own data collection system if you have the time. In this tutorial, let’s build a complete data collection system based on agile methodology and Office365.

1. Gather business requirements

It is always better to know your business needs than to start your data collection system from a spontaneous idea. For this example, let’s say you’re in the office and want to create a system that regularly sends out surveys to other staff members.

  • The first business requirement is to determine what questions you need to ask. You can ask your boss or co-workers, or even think of them yourself.
  • The second requirement is to think about the logical flow of data. Let’s say that for us, we want to send out a staff wellbeing survey on an ad hoc basis. A staff member should be able to fill out a simple form anonymously. The responses then go into some sort of database, and you should be able to parse and view those responses.
  • The last requirement is to understand how each actor will interact with your data collection system. For example, how are your colleagues supposed to open the form and how is your management supposed to see the results?

Additionally, you can create a wireframe by literally drawing on a piece of paper what the form should look like.

You can easily jot down requirements on a piece of paper and transfer them to a SharePoint website or OneNote book visible to all relevant stakeholders.

Collect business requirements in OneNote

2. Create a Kanban board

Kanban boards are great tools for visualizing the process of a project and simultaneously acting as a task list. This is also where you turn your business requirements into to-dos or functional requirements.

If you have access to a Kanban board, such as Microsoft Planner, create the following columns:

  • Create a To Do column for each task required to create the form.
  • Create a current column. Each time you work on a specific requirement, move it to the current column.
  • Create a Completed column. When a task is completed, it goes into this column.

If you don’t have access to Microsoft Planner, you can use a Trello board and invite others, or just use blank cards and a board to do Kanban physically.

A Kanban board in Trello

3. Create the form

Since we work in Office365, I recommend using Microsoft Form.

Simply add questions to your form as needed.

For this tutorial, keep it simple. You just need some yes or no questions and a free text field for open questions.

Microsoft Form also allows us to do conditional questions if that is something you desire. For example, if someone answers yes to something, a certain follow-up question comes up, or the next standard question continues.

Appearance of the form in Microsoft Forms in edit mode

4. Create the database

Microsoft Excel is spreadsheet software and not really a database. It can only hold so much information before it starts to run slowly. However, since we’re not collecting that much information at all, Excel does a good job.

If you really want to store everything in a database, you can connect to SharePoint online or Access, but for us we’ll keep it simple and use Excel. Again, it all depends on your business needs.

For Excel to capture data from Microsoft Flow, you must first create a table in your spreadsheet.

Also, if you’re thinking ahead and want to run multiple surveys and then analyze them all in one PowerBI report, you can put all your Excel spreadsheets in one folder, which then becomes your data lake.

In Excel, highlight the rows and columns from which you want to create a table, then click “Table” in the toolbar

5. Use Power Automate

The next step is to connect the form responses to your Excel spreadsheet. You can manually run an Excel export from the form itself, but that’s too cumbersome. Use Power Automate to do this automatically for you.

Power Automate is a bit tricky to use because it requires some understanding of how computers talk to each other. Below is how to do this between Forms and Excel.

From the “Create” page, choose the “automated cloud feed”
The above shows the workflow needed to get responses from the form to Excel

6. Run Quality Assurance

Before sharing your hard work with anyone, first determine if it actually works.

On an Excel spreadsheet, think of some tests that would lead to a happy result and other tests that you don’t want the system to do.

For example, a happy path would allow a user to submit once all required fields have been completed. An undesirable path would be for a user’s response not to be saved to the spreadsheet after filling out the form.

If you have a failed test, put it on your Kanban board to resolve after the test, unless some test depends on fixing that form feature first.

Sample Quality Assurance Test

7. Create your analytics dashboard

With some test results from your form, you can now link PowerBI to the Excel spreadsheet that collects the form data. Your dashboard can be designed according to your business needs.

PowerBI makes this quite easy, navigate to the spreadsheet location, change data types in PowerQuery and load the data model. In PowerBI, you may need to create measures and columns or manipulate existing objects so that visualizations can be created from the data.

You may need to do other data manipulation through PowerQuery
A simple dashboard example

8. Perform quality assurance on your dashboard

Similar to QA on your form, you also need to do this in your dashboard.

You might notice that the data is not correct, so you need to model the data in the PowerBI query editor, or maybe you need to change something on the form itself. For example, rather than yes or no appearing as a drop-down list, you might prefer it to be a checkbox so that the correct data type flows into your data model.

Required patches should be added to your Kanban board.

9. Prepare for communications

You simply can’t forward a survey to everyone and ask them to complete it without explaining why. You will need to carefully craft an email explaining why the survey is needed and how the survey will benefit them. This task can be delegated if you do not want to do it yourself.

10. Clear test data and send survey

Once you have everything prepared, make a copy of the test data spreadsheet from your form test, then clear the survey form from your test answers.

With a clean form, you can now send a copy to everyone with a reason why they need to fill it out.

You can share your form in different ways

Comments are closed.