Franchise reporting without rebuilding every file

With recurring exports, much of this work returns every month. Someone moves a column into place again, adds the location name, and checks the reporting period. If last month's explanation is in a colleague's email, they also have to ask how that file was handled.

Two columns with similar names

Suppose headquarters collects the number of completed orders for each location over an agreed month. One location sends an export with a column called completed_orders. Another has a column called orders, without explaining whether canceled orders are included.

The headquarters employee cannot resolve that by changing the heading. They need an answer from the second location: what does this number cover, and can it provide a count of completed orders only? Automation can accept the recognized file from the first location and show the second as needing clarification. The employee sees the original heading and the contact responsible for that location's data.

Once the column's meaning is confirmed, headquarters records which field in the combined dataset it should map to. If it includes canceled orders, the location needs to provide an appropriate export or additional data. When the location supplies the agreed measure in a consistent format, subsequent files can be prepared the same way. The question needs revisiting when the format or meaning changes.

The prepared data retains a link to the original file, its location, and its reporting month. Someone reviewing the consolidated dataset can then open the source of a particular value. Correctly arranged columns will not fix an export covering the wrong period.

Headquarters can see what is still outstanding

File intake should be linked to the list of locations expected to report for the month. The employee can then distinguish a missing export from a file that has arrived but needs an answer. They can contact the right location without comparing a folder's contents against a separate recipient list. A working report should identify locations whose data has not yet been included, including files awaiting clarification.

Sharing results with franchisees and handling corrections after they review them is a later stage. If messages get lost there, see our article on custom franchise reporting software.

When Power Query is enough

If locations can submit consistent exports, start with the tools headquarters already uses. Microsoft describes combining files from a folder with Power Query and refreshing the resulting table. Files should have consistent column names, types, and counts; column order can differ. This is a useful option for recurring files with the same structure.

A shared template and agreed definitions may be sufficient if locations can use them. Headquarters still needs to track missing files, but does not have to rebuild each spreadsheet manually. Several stable layouts can also be transformed into one shared table using agreed mappings.

An additional connection makes sense when files arrive in different places and explanations remain in email. It should keep the data and open questions with the relevant location and period. AI may help read a changing layout or draft a description of an ambiguity when the work requires interpreting text. Agreed rules are enough for known columns. Headquarters and the location confirm what each measure means.

What to commission when file preparation holds up reporting

In a proposed Syntalith project, we connect the places where exports arrive with preparation of the combined dataset. Recognized formats are transformed according to headquarters' agreed mappings, while the employee sees missing files and questions requiring a location's response.

The initial scope can cover one monthly dataset. Headquarters identifies the expected measures and locations, and the responsible people at each location explain their exports. Syntalith builds the connection and the process for applying those agreed column mappings.

You can start a conversation about automating data intake by describing a file someone has to rework every month. Explain what the employee changes and who they ask when a column is unclear. See Syntalith's pricing page for pricing information.