↓Skip to main content
  1. Projects/

Consulting Gig: Small Business Analysis

·3 mins
Table of Contents

🏡🌳🌴🌲🌴🌳🏡

A data analysis consulting project for a tree trimming service

Project Start: Jan 03, 2023

Hours billed: 17

Inbound Vector

My good friend (and former ally in cinema) Winson is connected to one of the Barnes Brothers. The Barnes run a busy tree trimming and removal service near Fayetteville, NC.

Barnes expressed interest in mining their invoicing and calendar data for business insights. My name came up, because I do this sort of thing 👨‍💻. Winson made the connection, helped scope out the project parameters, budget, and deliverables, then left us to ourselves.

The Ask

  • “Use Barnes Brothers’s historical estimates 💵 and calendar events 🗓️ to determine the most lucrative strategy and help focus their resources.”

  • “Generate a report featuring a series of maps 🗺️📍showing acceptance rates of job estimates, average/median/mean earned amount by zip code, and provide any insights supported by the data.”

  • “In a 10-15 minute meeting, help go over the results 🎤 and highlight anything of value that supports one or more courses of action.”

The client does invoicing and estimates in the cloud. Their job scheduling happens over shared google calendars. With at least high hundreds of jobs under their belts, they’re set up to spot some actionable patterns in their billing and appointment data.

At the very least, I imagine I’ll be able to aggregate data by zip code and client uid. I bet I could even use the address field to get accurate customer lat/long coordinates, perhaps to create a heatmap of fruitful micro-climates.

The client could then target their promotional programs at those areas for maximal return. They’ll know where to send the holiday fruit baskets, and where to cut back on low-growth areas.

Available Data

  • “From the extract the estimate’s date, total amount, and customer address fields.”

  • “From Barnes Brothers’ Google calendar, extract the address field of calendar events with Green labels.”

Challenges

  • In the billing software, not all job estimates materialize into paid/invoiced jobs
  • The job scheduling calendar and billing software are two separate systems
  • The “address” fields are the main join keys between billing & calendar

Approach

1. Divide Work Into Stages

Communicate plan and billing estimate to client for:

  1. Data Collection (10hr)
  2. Data Analysis (6hr)
  3. Reports / Visualization (4hr)

2. Data Collection

  • Client provides credentials for google cloud and “invoice simple”
  • Invoice software lacks data export feature. Submit feature request
  • Write Selenium scripts to log in and scrape invoice data to csv
  • Make GCP API calls to fetch green calendar event data
  • Clean up data types and other housekeeping in pandas / jupyter

3. Data Analysis

  • Fuzzy string match to find matching “address” fields between billing estimates and calendar events
  • Datetimes further confirm or deny that certain estimates became invoiced, completed jobs
  • Derive aggregate metrics e.g. confirmation rates and cumulative totals by zip, client
  • Run addresses through Google Geocoding API to get lat/long for each address

3. Reports/Viz

  • Plotly to generate ordinary bar charts outlining top clients, high-conversion areas
  • kepler.gl to visualize high-spend geographies. The maps look sort of like this:

Deliverables

  • .html files with interactive kepler maps

  • Clean source and aggregated data as .xlsx files

  • 20-minute presentation of insights / slides

Client Feedback

Positive!