Daily import service
Picks up each store’s sales file from an FTP inbox and loads it into the reporting database.
Use case · Data consolidation and reportingEvery store’sdaily sales inone reportingdatabase
We built a service that collects each store’s till exports every day, matches their stock codes to one product list and loads the sales into one reporting database. Head office reports on the whole network from a single source, whichever till system a store runs.
3point-of-sale systems read and consolidatedDailysales import from every store
The problem
Sales arrive in a dozen formats, and someone has to merge them
When the stores in a network run different till systems, each one exports its sales in its own format with its own stock codes. Someone at head office collects those files, lines them up and pastes them into a report before one store can be compared with another, and by the time the numbers agree they’re out of date.
Chasing files every week
Someone emails and phones stores for their sales exports, and the network report waits for the last one to arrive.
Every till exports differently
One file uses semicolons, another commas, and a third counts in cents. Each needs cleaning by hand before it can sit next to the others.
One product, three codes
The same item is 4410-A in one store and SKU0412 in the next, so product totals come out wrong unless someone matches the codes by hand.
The missing store
A store that didn’t send its file drops out of the regional total, and it only comes to light when the numbers don’t add up.
Numbers that arrive late
By the time the month is merged, the next month’s stock orders and promotions have been decided on older figures.
One person’s spreadsheet
The merge lives in one person’s workbook and macros. When they’re on leave, the network report waits for them.
Every till already holds the numbers head office needs, but nothing collects them, matches the codes and puts them in one place each day.
Sales files chased from every store, cleaned by hand and pasted into a spreadsheet that’s out of date by the time it adds up.
Every store’s sales in one reporting database, matched to one product list every day.
Before and after
From files in every format to one reporting database
Flip the switch to see the week before and with the system.
BeforeWith the system
- Sales files sent in by each store, in different formatsFiles picked up from an FTP drop and imported automatically
- The same product under a different code in every storeStore stock codes mapped to one central product list
- New stock codes matched by hand, one at a timeA classifier suggests the product type and manufacturer for new codes
- Network reports built by hand in spreadsheetsReports run straight off one reporting database
- A missing store noticed when the report doesn’t add upManual file drop for stores that miss the automatic run
- Comparing regions means another round of mergingProduct mix per region and per store from the same report pack
How it works
Four steps, every day
Click through each step to see what the service does with a store’s file.
01Collect
02Read
03Match
04Report
Step 01
Collect
Each store’s till export lands in an FTP inbox and the daily import picks it up. When a store misses the automatic run, head office loads its file through the manual drop route, so a late store can still join the day’s figures.
Step 02
Read
A reader for each of the three till systems understands that package’s export layout and stock codes, so every file comes out in the same columns whichever till produced it.
Step 03
Match
Every store’s own codes are linked to a central product master. For a code nobody has mapped yet, a machine-learning classifier reads the code and its description and suggests the product type and manufacturer, which leaves fewer codes to map by hand.
Step 04
Report
The sales load into one reporting database and the report pack runs off it: sell-out, product mix per region and per store, turnover analysis, store transactions and stock-code allocation, on SQL Server Reporting Services.
What we built
Six parts, one daily service
Readers for three till systems
Understands the export and stock-code format of each point-of-sale package the stores use.
Stock-code mapping
Links every store’s own codes to a central product master, so a product counts as the same product across the network.
Machine-learning classifier
Reads a stock code and its description and suggests the product type and manufacturer, which cuts down the codes someone has to map by hand.
Manual drop route
Lets head office load a store’s file by hand when it misses the automatic run.
Report pack
Sell-out, product mix per region and per store, turnover analysis, store transactions and stock-code allocation reports on SQL Server Reporting Services.
Hover over a card for more.
What changes
What’s different for the people doing the job
Whoever merged the files
The weekly chase and the copy and paste stop. What’s left are the exceptions: a store that missed the run, or a new code to check.
Head office
Reports open on numbers that are already in one place, with each product counted as the same product in every store.
Regions
Product mix per region and per store comes from the same report pack, so two stores can be compared without cleaning the data first.
The stores
They keep the till they have. The service reads the export it already produces.
Built with
.NETSQL ServerSQL Server Reporting ServicesDockerAzure Kubernetes ServiceFTPPython (machine learning)Exports from three point-of-sale systems
Before you ask
Questions about your stores and tills
Tap a question to open it.
Do our stores have to change their till system?
No. The service has a reader for each till system the stores use and works from the export each one already produces, so head office gets the same columns whichever till a store runs.
What happens when a store’s file doesn’t arrive?
Head office loads it through the manual drop route once it turns up, and it goes into the same reporting database as the rest.
Who matches the new stock codes?
The classifier reads each new code and its description and suggests the product type and manufacturer. That cuts down the codes someone has to map by hand.
We’re adding a store on a different till. Then what?
A new till system needs a reader for its export format. The import, the product list and the reports stay as they are.
How we’d start
How we’d start with your network
01
Look at what you have
Which till systems your stores run, what each one exports, how files reach head office today and which reports people wait for.
02
Get the first import running
One till format and a few stores loading into a reporting database every day, with the product list and code mapping in place.
03
Build it out
The other formats, the rest of the stores, the manual drop route, the classifier and the reports your team needs.
Quick check
Sound like your business?
Tick what sounds familiar. It applies to franchise groups, retail chains and any business whose branches run their own tills.
Your stores or branches don’t all run the same till or accounting system.
Someone at head office spends days each month collecting and merging sales files.
The same product shows up under different codes depending on the store.
You can’t compare two stores’ sales without cleaning the data first.
Your reports are out of date by the time they’re finished.
of 5 ticked
0 to 1 ticked
Your sales data is probably in decent shape. If you’re adding stores or a new till system, it’s worth planning how the data will come together before it gets messy.
2 to 3 ticked
You’re spending time on work a scheduled import could do. A short conversation would show how much of it can come off your team’s plate.
4 to 5 ticked
This is the problem we built this service for. Your network’s sales could land in one reporting database every day without anyone touching a spreadsheet.
More case studies
Other businesses we’ve built for
Let’s talk
Are your stores’ sales still arriving in a dozen formats?
Tell us which till and accounting systems your stores run, and we’ll show you how the numbers could come together in one place.
Use case · Data consolidation and reportingEvery store’sdaily sales inone reportingdatabase
We built a service that collects each store’s till exports every day, matches their stock codes to one product list and loads the sales into one reporting database. Head office reports on the whole network from a single source, whichever till system a store runs.
3point-of-sale systems read and consolidatedDailysales import from every store
The problem
Sales arrive in a dozen formats, and someone has to merge them
When the stores in a network run different till systems, each one exports its sales in its own format with its own stock codes. Someone at head office collects those files, lines them up and pastes them into a report before one store can be compared with another, and by the time the numbers agree they’re out of date.
Chasing files every week
Someone emails and phones stores for their sales exports, and the network report waits for the last one to arrive.
Every till exports differently
One file uses semicolons, another commas, and a third counts in cents. Each needs cleaning by hand before it can sit next to the others.
One product, three codes
The same item is 4410-A in one store and SKU0412 in the next, so product totals come out wrong unless someone matches the codes by hand.
The missing store
A store that didn’t send its file drops out of the regional total, and it only comes to light when the numbers don’t add up.
Numbers that arrive late
By the time the month is merged, the next month’s stock orders and promotions have been decided on older figures.
One person’s spreadsheet
The merge lives in one person’s workbook and macros. When they’re on leave, the network report waits for them.
Every till already holds the numbers head office needs, but nothing collects them, matches the codes and puts them in one place each day.
Sales files chased from every store, cleaned by hand and pasted into a spreadsheet that’s out of date by the time it adds up.
Every store’s sales in one reporting database, matched to one product list every day.
Before and after
From files in every format to one reporting database
Flip the switch to see the week before and with the system.
BeforeWith the system
- Sales files sent in by each store, in different formatsFiles picked up from an FTP drop and imported automatically
- The same product under a different code in every storeStore stock codes mapped to one central product list
- New stock codes matched by hand, one at a timeA classifier suggests the product type and manufacturer for new codes
- Network reports built by hand in spreadsheetsReports run straight off one reporting database
- A missing store noticed when the report doesn’t add upManual file drop for stores that miss the automatic run
- Comparing regions means another round of mergingProduct mix per region and per store from the same report pack
How it works
Four steps, every day
Click through each step to see what the service does with a store’s file.
01Collect
02Read
03Match
04Report
Step 01
Collect
Each store’s till export lands in an FTP inbox and the daily import picks it up. When a store misses the automatic run, head office loads its file through the manual drop route, so a late store can still join the day’s figures.
Step 02
Read
A reader for each of the three till systems understands that package’s export layout and stock codes, so every file comes out in the same columns whichever till produced it.
Step 03
Match
Every store’s own codes are linked to a central product master. For a code nobody has mapped yet, a machine-learning classifier reads the code and its description and suggests the product type and manufacturer, which leaves fewer codes to map by hand.
Step 04
Report
The sales load into one reporting database and the report pack runs off it: sell-out, product mix per region and per store, turnover analysis, store transactions and stock-code allocation, on SQL Server Reporting Services.
What we built
Six parts, one daily service
Daily import service
Picks up each store’s sales file from an FTP inbox and loads it into the reporting database.
Readers for three till systems
Understands the export and stock-code format of each point-of-sale package the stores use.
Stock-code mapping
Links every store’s own codes to a central product master, so a product counts as the same product across the network.
Machine-learning classifier
Reads a stock code and its description and suggests the product type and manufacturer, which cuts down the codes someone has to map by hand.
Manual drop route
Lets head office load a store’s file by hand when it misses the automatic run.
Report pack
Sell-out, product mix per region and per store, turnover analysis, store transactions and stock-code allocation reports on SQL Server Reporting Services.
Hover over a card for more.
What changes
What’s different for the people doing the job
Whoever merged the files
The weekly chase and the copy and paste stop. What’s left are the exceptions: a store that missed the run, or a new code to check.
Head office
Reports open on numbers that are already in one place, with each product counted as the same product in every store.
Regions
Product mix per region and per store comes from the same report pack, so two stores can be compared without cleaning the data first.
The stores
They keep the till they have. The service reads the export it already produces.
Built with
.NETSQL ServerSQL Server Reporting ServicesDockerAzure Kubernetes ServiceFTPPython (machine learning)Exports from three point-of-sale systems
Before you ask
Questions about your stores and tills
Tap a question to open it.
Do our stores have to change their till system?
No. The service has a reader for each till system the stores use and works from the export each one already produces, so head office gets the same columns whichever till a store runs.
What happens when a store’s file doesn’t arrive?
Head office loads it through the manual drop route once it turns up, and it goes into the same reporting database as the rest.
Who matches the new stock codes?
The classifier reads each new code and its description and suggests the product type and manufacturer. That cuts down the codes someone has to map by hand.
We’re adding a store on a different till. Then what?
A new till system needs a reader for its export format. The import, the product list and the reports stay as they are.
How we’d start
How we’d start with your network
01
Look at what you have
Which till systems your stores run, what each one exports, how files reach head office today and which reports people wait for.
02
Get the first import running
One till format and a few stores loading into a reporting database every day, with the product list and code mapping in place.
03
Build it out
The other formats, the rest of the stores, the manual drop route, the classifier and the reports your team needs.
Quick check
Sound like your business?
Tick what sounds familiar. It applies to franchise groups, retail chains and any business whose branches run their own tills.
Your stores or branches don’t all run the same till or accounting system.
Someone at head office spends days each month collecting and merging sales files.
The same product shows up under different codes depending on the store.
You can’t compare two stores’ sales without cleaning the data first.
Your reports are out of date by the time they’re finished.
of 5 ticked
0 to 1 ticked
Your sales data is probably in decent shape. If you’re adding stores or a new till system, it’s worth planning how the data will come together before it gets messy.
2 to 3 ticked
You’re spending time on work a scheduled import could do. A short conversation would show how much of it can come off your team’s plate.
4 to 5 ticked
This is the problem we built this service for. Your network’s sales could land in one reporting database every day without anyone touching a spreadsheet.
More case studies
Other businesses we’ve built for
Let’s talk
Are your stores’ sales still arriving in a dozen formats?
Tell us which till and accounting systems your stores run, and we’ll show you how the numbers could come together in one place.
Let's Connect
Interested in building a smarter, more connected operation? Contact us to discuss how AI can support your business goals.