Avendra keeps HubSpot contacts and lists current from two systems that export CSV files

The Customer

Avendra came to Lynton in January 2023 as they moved their marketing onto HubSpot Marketing Hub. Two of their internal systems held the contacts that marketing depended on. Their ERM exported contacts, and MyAvendra exported lists of contacts with email addresses. Both systems produced CSV spreadsheets and neither had a connector to HubSpot.

The Challenge

Avendra needed those spreadsheets to land in HubSpot without someone uploading them by hand. Contacts from the ERM had to become HubSpot contacts, and the MyAvendra recipient exports had to become HubSpot lists, each refreshed on a schedule as new files appeared.

At the same time, the marketing team was learning a new platform. They had a prospective supplier form, in English and Spanish, with custom properties to carry over, and a Salesforce connection being set up with a third party, HPSI, that had to be calibrated alongside everything else.

The Solution

We built a custom integration that watches FTP folders for new spreadsheets. When an ERM export arrives, the integration syncs its rows to HubSpot contacts. When a MyAvendra export arrives, it syncs the recipients to a HubSpot list. It runs on a schedule, so a file dropped on the server shows up in the CRM without anyone touching it.

The sync logic and field mappings came first. We worked them out with Avendra over four strategy calls and two field-mapping meetings, and development started only after the client approved the mappings. A sandbox instance went to Avendra for user acceptance testing with written test scenarios, their feedback went into mapping fixes, and the integration moved to its live instance on May 26, 2023. We then ran the historical sync of existing data, monitored it, and handed the integration to support.

In parallel we ran the Marketing Hub onboarding: a kickoff, eight training calls and a recap deck covering portal settings, an asset migration audit, permissions, segmentation properties, lifecycle stages, lead scoring, a process map of the customer lifecycle, campaigns, forms, landing pages, CTAs, email templates, workflows, and reports and dashboards. We rebuilt the English prospective supplier form in HubSpot with custom properties under the Avendra business unit, added the Spanish version, built the prospective supplier automation, landing page and email, built a welcome email template and welcome series, and set up food and weather recall emails. An email deliverability session and a data import working session connected the list sync to the team’s day-to-day work.

On the Salesforce side we set up and calibrated the connection, reviewed the sync errors between the two systems, and handled the follow-up on which business unit the app sat under. When the remaining UAT time was no longer needed for the file integration, Avendra asked us to spend it on the Salesforce work instead, and we did.

The Results

Partway through UAT, Avendra told us the file integrations were in a good place and they didn’t need the remaining UAT time on them. That is the outcome the project was built for. Contacts and lists from the ERM and MyAvendra now reach HubSpot on their own, the marketing team runs its forms, emails and workflows on the platform, and the Salesforce connection is in place beside it.

Wrapping Up

The standard on this job was to agree every field mapping with the client before writing code, test in a sandbox with written scenarios, and monitor the historical sync until it was clean. Lynton now builds AI-native websites on open infrastructure; see our current work at /case-studies.