Client overview
Industry:
Construction
Location:
Pontefract, UK
Role:
Development & UX Design
TimeLine:
5 weeks
Tools:
PHP, MySQL, JavaScript, Leaflet JS, TCPDF and LocationIQ API.
Planning applications are publicly available through council websites, but the data is spread across multiple systems and difficult to search efficiently.
The client had been pitched a solutio costing £234/month plus £0.72 per letter.
Instead, he asked me to build a custom internal tool that would allow them to:
- Search planning applications with a 20-mile radius
- Filter results by relevant keywords
- Generate personalised outreach letters for each result
Product Creation Journey
Problem
His 20 mile radius covers 4 different councils and this was time consuming and required logging into each different council website and filter them manually.
4 different councils to manually search through
That’s 4 different websites to manually search through, and copy and paste each address.
New Applications published
New applications are published every day of the week, and most of the time they get published towards the end of the week.
Tools out there cost £3000/year +
A company approached him to sell their software, + 0.72£ per letter. But if he gets busy he is still paying this monthly fee.
Being busy on construction sites, he only had a limited of time per week to generate new leads, and these current steps where time consuming. And required a bespoke letter, or cold call each time.
My Work Process
- I had to do research: How was I going to get this data? Is there a reliable API that can provide this data?
- How am I going to display it easily to Scott to be able to make everything easy to do, I start building a basic wireframe for the dashboard layout.
- I decided to install Xampp and install
I set out to create a fully custom built system, that would work locally on his PC, he wanted his partner to do these searches at home. So something that works from just his desktop is fine.
Having never done this before, I set out to find how the .
The client’s workflow involved:
• searching several council portals
• manually reviewing applications
• copying addresses to send letters
This made identifying potential leads slow and repetitive.
Challenges
Apart from the learning curve there was a few added challenges
The API didn’t work for one of the councils
I reported to the developers but didn’t get a response, it appeared that the 3 councils that it was providing information for where all using the same system, and Barnsley (the missing one) was a different build.
Couldn’t get all the content on the PDF doc
Styling the PDF was challenging at first, but with the help of TCPDF and some custom styling I was able to have everything fit, including a GDPR Disclaimer in the footer.
Avoiding Duplicate Results
The API provides 1 result per credit, so if the client did the search for “Extension” and “Loft” but 1 result had both, then we want him to avoid wasting credits and also printing out the letter twice for the same lead.
The Barnsley Council doesn’t provide Lat/Lon
The leaflet JS app only works with Lat and Longitude which is provided by the API with the other councils.
Solutions
To provide the best results for my client, I addressed the challenges and tried going further on others.
Custom Scraper built for Barnsley Council
2 custom .php pages where created: scrape_barnsey.php and get_barnsley_results.php
PDF Letter for Barnsley
The creation of a new php doc to make sure the results from Barnsley would display the same on all letters. (generate_letter_barnsley.php)
Communication with a Database
Setting up phpMyAdmin was essential, it provided the ability to save all results so we can consult retroactively and also avoid duplicates.
Dynamic Geocoding
I created a free account with LocationIQ so the software could get geocoding to get the long/lat from the postcode it scraped to then feed it to leaflet and display the correct map.
With the addition of the Database, and several queries setting it up I was able to create added functions on the dashboard, showing the total PDF’S Generated, the System Activity, and a list of the last letters created.
Finished Dashboard
Delivered a fully functional Software that the user uses on a weekly basis. Clear UX and provides real results.
Results
Custom-Built Success
Successfully replaced a £3,000/year SaaS with a one-time build that costs £0.10 per lead.
£250,000
The software identified leads that resulted in a quarter-million pounds of billed work within 4 months.
Automated Workflow
Reduced a 5-hour manual process to a 15-minute automated “review and print” session.
Zero Maintenance
100% stable performance post-launch with no downtime or data loss.
Client feedback
“I have had two websites and a personal desktop application created by Pierre and i cant thank him enough.
He goes above and beyond every time i need something, hes always there on the other end of the phone and he keeps all my sites up to date as and when i need them updated”
Scott Savage, Director of Enhanced Building Management
