10 Steps to Building a Data Collection System with Office365 – DataDrivenInvestor

An End-to-End System with Quality Assurance and Analytics

Photo by Lukas Blazek on Unsplash

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

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

1. Gather Business Requirements

It’s always better to know what your business requirements are rather than starting your data collection system from a spontaneous idea. For this example, let’s say you’re in the office and you want to build a system that regularly sends surveys to other staff.

  • The first business requirement is to figure out what questions you need to ask. You can ask your boss, or colleagues, or even think of them yourself.
  • The second requirement is to think about the logical flow of data. Let’s say for us, we want to send out a staff wellness survey on an ad-hoc basis. A staff member should be able to fill out a simple form anonymously. Responses then go to some sort of database, and you should be able to analyze and visualize these responses.
  • The last requirement is to figure out 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 view the results?

Furthermore, you can create a wireframe by literally drawing out on a piece of paper how the form should look.

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

Gathering Business Requirements in OneNote

2. Create a Kanban Board

Kanban boards are great tools to visulizing the process of a project and acting as a to-do list simulatenous. This is also where you turn your business requirements into actionable 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 build the form.
  • Create an in-progress column. Each time you work on a specific requirement, move it to the in-progress column.
  • Create a done column. When a task is finished, it goes to this column.

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

A Kanban Board in Trello

3. Create the form

Since we’re working in office365, I recommend using Microsoft Form.

Simply, add questions to your form based on your requirements.

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

Microsoft Form also allows us to make conditional questions if that’s something you desire. For instance, if someone answers yes to something then a certain follow-up question presents itself, or else the next standard question proceeds.

Form Appearance in Microsoft Forms in Editing Mode

4. Create the database

Microsoft Excel is a spreadsheet software and not really a database. It can only hold so much information before it begins to perform slowly. However, considering that we are not gathering so much information at all, then Excel does a fine job.

If you actually want to store everything on a database, then you can connect to SharePoint online or Access, but for us, we’ll keep it simple and use Excel. Again, this is all dependent on your business requirements.

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

Furthermore, if you’re thinking ahead and want to make several surveys and then analyze them all in a single PowerBI report, you can put all of your Excel spreadsheets into a single folder, which then becomes your data lake.

In Excel, highlight the rows and columns you wish to creat a table from, then click ‘Table’ on the toolbar

5. Use Power Automate

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

Power Automate is a bit tricky to use as it requires something understanding of how computers talk to each other. Below is how you would do it between Forms and Excel.

From the ‘Creat’ Page, choose the ‘Automated Cloud Flow’
The above displays the workflow needed to get response from Form to Excel

6. Run Quality Assurance

Before you share your hard work with anyone, first figure out 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 be allowing a user to submit once all required fields have been answered. An undesirable path would be a user’s response not being recorded on the spreadsheet after completing the form.

If you have a failed test, then put it on your Kanban board to resolve after testing unless certain tests are reliant on that form feature being fixed first.

Excample of Quality Assurance Testing

7. Create Your Analytics Dashboard

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

PowerBI makes this quite easy, navigate to where the spreadsheet is being held, edit the data types in PowerQuery and load the data model. Within PowerBI, you may need to create measures and columns or manipulate existing objects so that visualizations can be created out of the data.

You may need to do further data manipulation via PowerQuery
A simple dashboard example

8. Do Quality Assurance on Your Dashboard

Like doing quality assurance on your form, you also need to do it in your dashboard.

You may notice that the data is coming in not right, so you need to model the data in the PowerBI Query Editor, or perhaps, you need to change something on the form itself. For example, rather than yes or no appearing as a dropdown list, you may prefer it as a check box so that the correct data type flows into your data model.

Required fixes should be added to your Kanban board.

9. Prepare for Communications

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

10. Wipe the Test Data and Send Out the Survey

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

With a clean form, you can now send a copy to everyone along with a reason explaining why he/she should complete it.

There are various ways you can share your form
Total
0
Shares
Leave a Reply

Your email address will not be published. Required fields are marked *

Previous Post

Impact Rankings 2022: remote working post-Covid – Times Higher Education

Next Post

Bad management in remote working taking its toll | theHRD – The HR Director Magazine

Related Posts