Fromm Family Foods linked its SQL system to HubSpot and replaced a manual promotions tool
The Customer
Fromm Family Foods makes pet food and sells it through independent retailers and distributors. The company ran its operations from a custom SQL admin system. That system was fed by monthly sales files from ReSight, a nightly product-link feed from eTailPet, and a Footprints ticketing server that handled sales promotions.
Fromm wanted HubSpot to become the working CRM for its stores, distributors, reps and consumers, with the SQL system staying the source of truth.
The Challenge
Each month a data analyst ran ten SQL scripts by hand against the new ReSight sales file. The scripts had no documentation, and the person who wrote them had left the company.
Sales promotions ran through a separate Footprints server. A rep who wanted to offer a store a deal had to work through that ticketing system, and the record of the promotion lived outside the CRM.
Distributor-level sales reporting was limited, and the company’s large base of consumer subscribers had no connection to the CRM.
The Solution
We began by reverse-engineering and documenting the ten monthly SQL scripts, so the process could be automated and would no longer depend on one person’s memory.
We then built an integration database with a table and eight SQL views as the source for a HubSpot sync, running through Tray middleware. Stores sync to Company records with parent and child chain associations. Distributors, groups, products, reps, store status and store types come across as properties. Reps are linked to their companies and distributors, retailer contacts to their stores. Sales rollups from year to date out to fifteen months are mapped onto each store’s record.
Two webhooks serve Fromm’s consumer-facing pages. The store locator webhook searches Company records by coordinates and radius, by city and state, or by store ID, and returns each store’s address, image, coordinates and up to 17 service features. The buy-now webhook reads the daily eTailPet feed into a product URL custom object, so a shopper looking for a product sees online retailers first and in-store retailers after them.
A CMS admin tool, built on HubSpot CMS pages and backed by seven webhooks, lets Fromm staff reconcile ReSight and eTailPet locations: link one to an existing retailer with fuzzy matching, create a store, hide a record or add notes, and trigger the monthly ReSight sync with a button.
The custom promotions sync replaced Footprints. A promotion created in Fromm Admin syncs to a deal pipeline in their CRM. The integration assigns the reference number and deal owner, matches Fromm reps to platform users daily, associates the distributor, store and recipient contacts, generates a PDF purchase order and emails it through workflows. Revisions write back to SQL. Roughly 36,000 historical promotion records were mapped to the new statuses before go-live. The promotions tool went live in August 2022 and took over from Footprints.
Fromm added last-modified timestamps to 16 SQL tables, which let us sync stores, distributors and people live. We also synced more than 400,000 subscriber IDs into consumer contact records, new subscribers included, and routed website form submissions into the platform for opt-in and coupon emails.
What They Got
Fromm’s sales reps now create a promotion in the system they already use, and the purchase order, the deal record and the emails to the distributor and store follow on their own. The monthly sales file loads with one button. Stores, reps, distributors and consumers are in one CRM, kept current from the SQL system Fromm owns, and each store’s sales history sits on its record where a rep can see it.
Wrapping Up
Our standard on an integration like this is to leave the client’s operational database as the source of truth and document everything we touch, so the work survives a change of staff on either side. Lynton now builds AI-native websites on open infrastructure; see our current work at /case-studies.