Decide and Plan
An automatic pipeline means new form submissions appear in your Excel workbook or Power BI report without anyone downloading and pasting files. This page shows the steps, the checks to build in, and a plan for when it breaks.
If you have not yet chosen a platform, start with KoboToolbox vs ODK vs SurveyCTO.
Should You Automate at All?
Automate when the same data is pulled at least monthly, by more than one person, into the same report. Do not automate when the form is still changing, the survey runs once, or no one on the team can fix a broken connection. A weekly manual export with a checklist is better than a pipeline nobody understands.
The Pipeline in Four Stages
Form. Submissions are stored on the platform server.
Connection. Excel or Power BI reads the data from the server, through an export link (KoboToolbox) or an OData feed (ODK Central).
Cleaning. A set of saved steps fixes names, types and known problems each time the data refreshes.
Report. Tables, charts and indicator calculations that read from the clean table only.
Keep these stages separate. Never type into the raw data or the clean table.
Build It
Step by Step
Step 1: Freeze the form design. Pilot the form first. Changing question names after the pipeline is built breaks the downstream columns. Record the final question names in a data dictionary.
Step 2: Choose the access. Do not use a manager's login if you can avoid it. Where the platform lets you share a project with limited permissions, create a separate account that can only view data, and keep its password in a protected place, not in a shared sheet. KoboToolbox connections use your account username and password. ODK Central connections use a Central account.
Step 3: Get the data link. In KoboToolbox, the link is a synchronous export URL from the project's API; follow the KoboToolbox guide, since link formats have changed in the past. In ODK Central, open the form's Submissions page, select "Analyze via OData" and copy the link. Test the link in a browser and confirm you can see the latest submission.
Step 4: Connect from Excel or Power BI. In Excel with ODK Central, go to Data, Get Data, From Other Sources, From OData Feed and paste the link. In Excel with KoboToolbox, go to Data, Get Data, From Web and paste the export URL; this is the same Web connector Kobo documents for Power BI. In Power BI Desktop with KoboToolbox, choose Get Data, Web, paste the synchronous export URL, and choose Basic sign-in with your Kobo username and password. In Power BI Desktop with ODK Central, choose Home, Get Data, OData feed, paste the link, and choose Basic authentication with the Central email and password. Basic authentication may need a gateway for scheduled refresh in the Power BI service. See the ODK Central OData guide and the Microsoft OData feed connector.
Step 5: Clean in saved steps. Open the Power Query Editor (in Power BI, Transform Data) to rename columns, set data types, remove test submissions, remove duplicates and split multi-select answers. Each action is saved and runs again at each refresh. Use the checks in How to Clean Your Dataset as your list.
Step 6: Build the report from the clean table. Put indicators and charts on the clean table only. For chart choices, see Data Visualization.
Step 7: Set the refresh. In Excel, refresh on request or on opening the file. In Power BI, scheduled refresh is set in the online service under Refresh, then Schedule refresh on the semantic model. Microsoft allows up to 8 scheduled refreshes a day on Power BI Pro and up to 48 on Premium per user or Premium and Fabric capacity. Some data sources need a gateway. Choose a schedule that matches how often you review the data.
Build In Four Checks
- Count check. Show the number of rows on the report page and compare it with the count on the platform each week.
- Latest date check. Show the date of the newest submission so readers see if the feed has stopped.
- Blank and range check. Flag blank required fields and impossible values.
- Test data check. Filter out test submissions by a marker you agree on, such as a test ID.
Protect and Repair
Protect Personal Data
Data feeds often include names, phone numbers and locations. Before connecting, drop or hide those columns in the query, and share the report only with people who need it. See the access and backup parts of the data management protocol.
What Breaks, and the Fix
| Problem | Likely cause | Fix |
|---|---|---|
| Refresh fails | Password changed or expired | Update the saved credentials in the data source settings |
| Scheduled refresh stops | Repeated failures, or no one has viewed the report for two months | Fix the cause, then switch the schedule back on |
| Column missing or blank | Question renamed or form redeployed | Check the data dictionary; update the rename step |
| Row count too low | Filter set wrongly, or only part of the data pulled | Compare with the platform count; review the filters |
| Duplicates appear | Resubmitted or edited records | Keep the latest record per ID |
| Numbers read as text | Data type not set | Set the type in the saved steps |
Pipeline Record
Keep one page with the form name, link location, who owns the access, refresh schedule, saved cleaning steps and who fixes it when it fails.
| Field | Your notes |
|---|---|
| Form and version | |
| Data link kept where | |
| Access owner and backup person | |
| Refresh schedule | |
| Checks on the report page | |
| Who fixes failures |