About the Client
BAC Partners is an Australian digital transformation and customer experience consultancy that helps organizations improve sales, marketing, customer service, and business operations through strategy, process optimization, CRM platforms, and automation solutions. The company specializes in designing and implementing customer-centric technology solutions that enable businesses to scale, enhance customer engagement, and drive operational efficiency across multiple industries. Their expertise spans customer experience transformation, go-to-market strategy, CRM implementation, marketing automation, and managed services.
https://www.bac.co/
Project Background
BAC Partners required a CRM visibility layer for a dental practice group client whose operational data resided in SAP Business One. The sales and marketing teams needed that data surfaced in HubSpot in a structured, clean format that was automatically kept up to date without manual exports or re-keying.
The challenge was architectural, as SAP and HubSpot use different data models. The solution required an intermediate MySQL staging database (DB-2) to normalize SAP data, apply custom business rules including RFM scoring, and push the processed information into HubSpot’s three core CRM objects. BAC Partners also required the application to be Python-based and configuration-driven, enabling it to be replicated across future client engagements by simply updating a configuration file.
Scope & Requirements
In Scope
- Create and populate MySQL DB-2 from SAP DB-1 (companies, contacts, and deals) using defined business rules.
- CRON-scheduled batch jobs to continuously sync DB-1 to DB-2 (companies every 8 hours, deals every 8 hours, and RFM daily).
- HubSpot API integration for Companies, proxy Contacts, and Deals objects.
- Calculated fields including Days Since Last Order, Lifetime Order Count, Lifetime Revenue, and RFM Score.
- Parent-child company relationship mapping using FatherCard.
- Change-pointer logic to update modified records in HubSpot (Phase D).
- Full dataset migration: 5,754 companies, 5,346 contacts, and 147,398 deals.
- One week of post-go-live hypercare and full documentation handover.
Out of Scope
- UI or portal development.
- Performance and functional testing beyond the agreed unit test cases.
- Custom HubSpot API modifications.
- Changes to DB-1 (SAP source system).
Project Timelines
|
Phase |
Key Deliverables |
Status |
|
Phase A |
Requirement gathering, SSH & DB access, data mapping analysis, HubSpot field configuration, scope sign-off |
Done |
|
Phase B |
DB1→DB2 Python pipeline, CRON batch jobs, RFM calculation logic, unit testing |
Done |
|
Phase C |
HubSpot API integration (companies, contacts, deals), relationship linking, bug fixes, 1k-record test push |
Done |
|
Phase D |
Change-pointer sync, full dataset migration (5,754 companies · 5,346 contacts · 147k+ deals), daily RFM update, hypercare |
Done |
Results & Impact
Observability & Visibility
Assessment Report
Phase A delivered a comprehensive assessment before any code was written, including:
- Risk & Gap Analysis — identified data quality issues such as missing emails, blank company names, malformed domain names, and the HubSpot item code limit of 5,000 versus 5,577 SAP entries. All issues were resolved through agreed mitigation strategies.
- Customized Data Mapping Report — full field-level mapping was signed off across Companies, Contacts, and Deals, with business rules confirmed by BAC Partners.
- API rate limit validation — confirmed that HubSpot’s limits of 100 requests per second and 250,000 daily requests were sufficient for the dataset before pipeline development commenced.
Cloud Formation
Integration Architecture
Below is the high-level architecture diagram.
Outcome Metrics
|
✔ Full Visibility Assessment & data-mapping report with risk/gap analysis signed off before a line of code was written. |
☁ Cloud-Native Pipeline Python-based sync on Ubuntu cloud — config-driven for multi-instance reuse across BAC Partners clients. |
⚡ 6-Week Delivery All four phases from requirement gathering to full dataset go-live completed in under six weeks. |
Data Mapping
Deal Field Mapping (SAP → HubSpot)
|
HubSpot Field |
SAP Column |
Business Rule |
|
Deal Name |
DocNum |
Prefix “DocNum: ” prepended to value |
|
Deal Stage |
— |
“Customer Invoiced” (fixed value) |
|
Close Date |
DocDate |
Direct mapping |
|
Company |
CardCode |
Linked to initialised company record |
|
Amount |
— |
Sum of (Price × Qty) across all line items per DocNum |
|
Product Brand |
U_Brand |
All unique U_Brand values per DocNum |
|
Product Category 1 |
U_Group5 |
All unique U_Group5 values per DocNum |
|
Product Category 2 |
U_Group2 |
All unique U_Group2 values per DocNum |
|
Product Category 3 |
U_Group3 |
All unique U_Group3 values per DocNum |
Contact Field Mapping (SAP → HubSpot)
|
HubSpot Field |
SAP Column |
Business Rule |
|
First Name |
— |
“Proxy” — fixed value for all integration-generated contacts |
|
Last Name |
CardName |
Direct mapping |
|
|
E_Mail |
From OCRD table; omitted if missing or malformed |
|
Company |
CardName |
Link to existing HubSpot company — all companies initialised first |
Company Field Mapping (SAP → HubSpot)
|
HubSpot Field |
SAP Column |
Business Rule |
|
Card Code |
CardCode |
First unique CardCode used as primary key |
|
Company Name |
CardName |
Direct mapping; falls back to CardCode if blank |
|
City / State / Country |
City/State/Country |
Direct mapping |
|
Phone |
Phone1 |
Direct mapping |
|
Website URL |
IntrntSite |
Direct mapping |
|
Parent Company |
FatherCard |
Child-to-parent association — all companies initialised first |
|
Days Since Last Order |
— |
Calculated: days between most recent invoice date and run date |
|
Lifetime Order Count |
— |
Total non-cancelled invoice count per company |
|
Lifetime Revenue |
— |
Sum of (Price × Qty) across all non-cancelled invoices |
|
RFM Score |
— |
(1/R) × F × √M where R=days since last order, F=order count, M=lifetime revenue |
Implementation
Phase A — Assessment & Data Mapping
Established SSH and SQL Server access to the SAP environment. Mapped all HubSpot fields against SAP CMS columns, configured custom HubSpot properties (Card Code, RFM Score, Days Since Last Order, and Lifetime Revenue), confirmed API key access, and validated sample RFM calculations against client-provided reference values.
Phase B — DB-1 → DB-2 Pipeline
Built Python extraction scripts to pull company and deal data from SAP SQL Server into MySQL DB-2. Implemented RFM calculations with corrected Price × Quantity multiplication (9-decimal precision). Configured CRON jobs for automated synchronization and validated data counts of 6,086 company records and 485,820 total deal records.
Phase C — HubSpot API Integration
Developed createCompany.py and createDeals.py to push staged data from DB-2 into HubSpot through REST APIs. Key implementation details included:
- Proxy contact creation — one contact per company email address, with “Proxy” used as the first name as per BAC Partners’ specification.
- Child-to-parent company association using FatherCard, ensuring all companies were initialized before relationships were established.
- Deal-to-company and deal-to-contact linking.
- Duplicate email handling, where HubSpot API errors were gracefully managed and contacts linked to the first-created record.
- CardName fallback, where blank company names used CardCode as the contact identifier.
A 1,000-record subset was migrated initially, resulting in 762 deals, 307 companies, and 263 contacts being created. All field mappings were verified in the HubSpot interface before proceeding with the full migration.
Phase D — Change Sync & Full Migration
Implemented change-pointer logic using last-modified columns from DB-1. Added a daily RFM refresh script (updateCompanyRFMvalues.py) scheduled to run at 5:00 AM to keep Days Since Last Order and RFM scores current. The full dataset migration was executed on 21 October 2019, successfully migrating 5,754 companies, 5,346 contacts, and 147,398 deals, with all relationship links and parent-child hierarchies verified in HubSpot.
Technology & Architecture
|
Source (DB-1) |
SAP Business One (SAP CMS) | SQL Server | OINV · INV1 · OCRD · OITM tables |
|
Staging (DB-2) |
MySQL on Ubuntu 18.04 cloud server | 3 tables: Company · Deal · Calculated Columns |
|
Language |
Python 3 | Config-driven via config.ini | replicable across multiple client instances |
|
CRM Target |
HubSpot CRM | Companies · Contacts · Deals | HubSpot REST API v1 |
|
Scripts |
getDataFromSAP.py | getDataForDealsMapping.py | pushCompaniesInMySQLCron.py | pushDealsMappingInMySQLCron.py | updateCompanyRFMvalues.py |
|
CRON Schedule |
Companies: 1 AM daily | Deals: 3 AM daily | RFM update: 5 AM daily |
|
Source Control |
GitHub — BACPARTNERS/CDT-SAP-HS-INTEGRATION |
|
Testing |
Postman (HubSpot API validation) | SQL queries | Manual HubSpot UI verification |
Challenges
HubSpot Item Code Property Limit
HubSpot multi-select properties support a maximum of 5,000 options. The SAP dataset contained 5,577 item codes, causing API failures. Resolved by removing item code field mapping from scope by agreement — no impact on core integration value.
Data Quality — Emails, Blank Names, Domain Validation
39 contacts had missing or malformed email addresses; several companies had no CardName; SAP’s IntrntSite field contained email addresses that HubSpot rejected as invalid domains. Resolved with three targeted code rules: skip email field if invalid, fall back to CardCode for blank names, and exclude Company Domain Name from mapping by client agreement.
Duplicate Contact Handling
Multiple companies shared the same email address (e.g. two dental practices using the same reception inbox). HubSpot’s API returns an error on duplicate email creation. Resolved by catching the error in code and proceeding — the contact is associated with the first-created record, and the duplicate is not re-created.
RFM Calculation Accuracy
Initial RFM values were incorrect due to missing Price × Qty multiplication in the SQL query. Resolved by correcting the query and increasing decimal precision to 9 places, aligning output exactly with BAC Partners’ validation figures.
Compressed Six-Week Timeline
All four phases were delivered within six weeks with 2–3 day feedback cycles, iterative code commits to GitHub, and proactive scope-change estimates before any out-of-scope work began.
Key Benefits
- Automated CRM accuracy — HubSpot reflects SAP data continuously with no manual exports, eliminating data lag and re-keying errors.
- Config-driven reusability — the same codebase serves any client by updating
config.ini; BAC Partners can deploy this for future engagements without re-architecting. - Revenue intelligence in HubSpot — RFM score, lifetime revenue, and days since last order provide GTM teams with instant customer tiering inside their existing CRM.
- Full company hierarchy — parent-child dental group structures are preserved and navigable in HubSpot.
- Zero middleware cost — pure Python on Ubuntu with no Informatica, MuleSoft, or iPaaS licensing fees.
- Fully auditable — all scripts are maintained in GitHub with inline comments, and CRON execution is logged to
/usr/logs/.
Post-Go-Live Support
Hypercare Period
A dedicated one-week hypercare period was provided following the full-dataset go-live. All Phase D test scenarios (15 test cases) were completed successfully with a pass status. The CRON schedule was fine-tuned, all identified issues were resolved, and complete installation and configuration documentation was handed over to BAC Partners.
