BBGI–Marketron Integration

1. Project Overview

This project is a backend data integration system built for BBGI (Beasley Media Group), a radio broadcasting company. The system pulls advertising and financial data from Marketron (their traffic/order management platform), processes and unifies it, then pushes it into NetSuite (their accounting/ERP system).

Alongside the data pipeline, there is a Flask-based internal web application that gives the AR (Accounts Receivable) and operations team tools for reconciliation, manual contact mapping, and related tasks. The project also handles automated aged-debt email notifications and invoice PDF
delivery to Tesorio (an AR automation platform) via SFTP.

The codebase went through a refactor mid-project to move from a flat core/ directory structure to a more modular src/integrations/ layout.

2. Background / Requirement

BBGI sells radio advertising across multiple markets. Their sales and order management runs in Marketron, a broadcasting-specific platform that manages clients, orders, invoices, account executives, and aging data. However, their financial reporting and accounting runs in NetSuite. The two systems did not talk to each other automatically. Marketron data needed to be extracted, cleaned up, and loaded into NetSuite as proper financial records — specifically customers, employees (sales reps), non-inventory items (sale types), and invoices.

The two systems did not talk to each other automatically. Marketron data needed to be extracted, cleaned up, and loaded into NetSuite as proper financial records — specifically customers, employees (sales reps), non-inventory items (sale types), and invoices.

On top of that, the AR team needed:

• Automated aging-based email reminders sent to Account Executives, the AR team, and clients
• A way to reconcile invoices between the two systems to catch discrepancies
• Invoice PDFs forwarded to Tesorio (their AR automation vendor) via SFTP
• A simple internal tool to manage contact mappings manually when automated matching fails

3. Problem Statement

• No sync between systems:
  Marketron and NetSuite were completely disconnected. Financial records in NetSuite had to be created or updated manually. This was slow and error-prone.
• Duplicate IDs across markets:
  Marketron creates invoice numbers per company/market, so the same invoice number can appear in multiple markets. NetSuite has no native way to handle these duplicates, which required a composite key (transactionNumber + market_id) and a derived netsuite_transactionID field to manage uniqueness.
• Data mismatches and unification:
  The same client or sale type could appear under different IDs across markets. Records needed to be “unified” — matched and given a single canonical ID — before being sent to NetSuite.
• No visibility on outstanding invoices:
  The AR team had no automated way to notify Account Executives or clients about overdue balances.
• No reconciliation tooling:
  There was no easy way to compare what existed in Marketron vs. what had been synced to NetSuite, making it hard to catch missing or incorrect invoices.

4. Objectives

• Build a reliable daily pipeline that pulls data from Marketron and syncs it to NetSuite
• Store all pulled data in MongoDB as a datalake/intermediary
• Handle data unification — resolve duplicate IDs across markets before syncing
• Sync customers, employees (AEs), sale types, and invoices to NetSuite via a custom RESTlet
• Augment invoice data with additional fields (AE contact info, discount calculations, PDF
  attachments)
• Send automated aging email reports to Account Executives, the AR team, and clients
• Build a web UI for reconciliation and manual contact mapping
• Push invoice PDFs to Tesorio via SFTP
• Support both sandbox and production NetSuite environments

5. Solution Overview

The solution is a Python application that acts as a bridge between Marketron, MongoDB, and NetSuite. The core of it is a data pipeline that runs on a schedule (via Render’s cron job or manual trigger) and does the following:

• 1. Calls the Marketron REST API to fetch updated records (clients, orders, invoices, AEs, etc.)
• 2. Stores raw results in MongoDB Atlas, organized into named collections per data type
  
• 3. Runs augmentation scripts that enrich the raw data — attaches AE email info, computes
  discounts, determines correct NetSuite transaction IDs, and downloads invoice PDFs
  
• 4. Calls the NetSuite RESTlet (a custom SuiteScript 2.x script deployed in NetSuite) via OAuth1 to
  create or update records in NetSuite
  
• 5. Syncs invoice PDFs to Tesorio’s SFTP server

The Flask web app provides an internal UI for the team to:

• • Manually manage client-to-contact email mappings
  • Upload CSV exports from Marketron and NetSuite and run a reconciliation comparison
  • Download reconciliation results as a CSV

Email notifications were built to send personalized aging reports to AEs, the AR team, and clients using Mandrill (Mailchimp Transactional). Emails are stored as previews in MongoDB with a batch UUID before sending, so they can be reviewed first.

6. Technology Stack

7. System Architecture

The system has a simple but multi-part flow. The pipeline side runs as a background job; the web UI runs as a Flask web service.

Data Pipeline Flow

Marketron API → Python (utils_simple.py) — paginated REST calls, incremental date filtering
↓
MongoDB Atlas — raw collections (mktr__invoices, mktr__clients, mktr__orders, etc.)
↓
Augmentation scripts — attach AE emails, compute discounts, determine netsuite_transactionID, download
PDFs
↓
MongoDB Atlas — augmented collections (mktr__invoicesAugmented, etc.)
↓
netsuite_restlet_all.py — transforms and batches data, calls NetSuite via OAuth1
↓
NetSuite RESTlet (mrktrestlet.js) — creates or updates customers, employees, items, invoices
↓
Tesorio SFTP — invoice PDFs uploaded via pysftp

Web Application Flow

Browser → Flask app (app.py) — session-based login, routes
↓
MongoDB Atlas — reads/writes manual_contact_mapping, email_previews, etc.
↓
Reconciliation engine (reconcile_systems_new_format.py) — compares uploaded Marketron CSV vs
NetSuite CSV

Component Communication

• All external API calls go outbound from the Python server — there is no inbound webhook setup
• NetSuite is not called via its standard REST API; instead, a custom SuiteScript RESTlet is deployed inside NetSuite and called via OAuth1 POST requests
  
• MongoDB is used both as a raw datalake and as application storage (for email previews, contact mappings, do-not-contact lists)
  
• Environment variables ( .localenv ) control which NetSuite environment (sandbox vs. production) to use
  

8. Main Modules / Features

Data Map ( core/data_map.py )

Purpose: Central configuration for all data types in the system.
Defines a DataMapping dataclass for each Marketron data type (markets, clients, invoices, orders, AEs, stations, spots, payments, etc.). Each mapping holds the Marketron API URL, the MongoDB collection name, whether the table has daily changes, pagination settings, and which NetSuite record type it maps to. The TableName enum gives stable names to all collections. This file is imported almost everywhere and acts as the single source of truth for the data model.

Marketron Data Pull ( core/utils_simple.py )

Purpose: Pulls data from the Marketron API with pagination and incremental filtering.
The paginated_get() function handles all API calls. It supports multiple pull types ( DataPullType : full, last 7 days, last 30 days, etc.) and uses date-based parameters ( minimumModifiedUtc , minimumDate , etc.) to only fetch records changed since the last run. API calls are made per market (Marketron data is organized by company/market ID), so the system iterates over all markets and merges results. A timed_lru_cache decorator avoids re-fetching data within the same hour.

MongoDB Layer ( core/mongo_new.py )

Purpose: All read and write operations to MongoDB Atlas.
Provides functions to upload, download, and transform data in MongoDB collections. Key functions: upload_data_to_mongo_helper() for batch upsert, download_mongo_table_to_dict() / download_mongo_table_to_list() for reads, and transform_and_reupload_data() for in-place data transformations. Uses replace_id_with_hash() to generate stable document IDs from data content. All batch writes go through a retry decorator to handle transient MongoDB reconnect errors. The database name is bbgi_marketron .

Invoice Data Augmentation ( core/augment_invoice_data.py )

Purpose: Enriches raw invoice data before it gets sent to NetSuite.
Fetches raw invoices from MongoDB, then for each invoice: attaches the Account Executive’s email address, joins order line data to compute discount percentages per line, determines the correct netsuite_transactionID (handles duplicate transaction numbers across markets by appending the market number when needed), and downloads the invoice PDF from Marketron and uploads it to Tesorio SFTP. Results are stored back into MongoDB in the mktr__invoicesAugmented collection. Processing is done in batches using PyMongo’s bulk_write .

NetSuite RESTlet Client ( core/netsuite_restlet.py )

Purpose: Python side of the NetSuite integration. Handles OAuth1 auth and HTTP calls to the RESTlet.
Creates an OAuth1Session using consumer key/secret and token key/secret loaded from environment variables. Exposes typed functions: create_record() , update_record() , get_record() , search_record() , delete_record() , and update_invoice_lines() . Supports both sandbox ( 9639742-sb1 ) and production ( 9639742 ) NetSuite account URLs. All calls go to a single RESTlet endpoint (script 769, deploy 2).

NetSuite RESTlet / SuiteScript ( core/mrktrestlet.js )

Purpose: The SuiteScript 2.x code deployed inside NetSuite that receives requests and creates/updates records.
This JavaScript file is manually deployed inside NetSuite via the Script editor UI (script ID 874 in sandbox). It handles multiple method values in the incoming JSON: POST (create), update , search , GET , delete , updatelines , and debug . The updatelines method allows updating individual line items on an existing invoice by lineuniquekey without replacing the whole record.

NetSuite Sync Orchestrator ( core/netsuite_restlet_all.py )

Purpose: Reads augmented data from MongoDB, transforms it to NetSuite field format, and calls the RESTlet in batches.
Contains transform functions for each record type: transform_customer_data() , transform_employee_data() , transform_item_data() , transform_invoice_data() . The invoice transformer is the most complex — it resolves cross-references (customer ID, AE/sales rep ID, sale type item ID, revenue source classification), maps Marketron fields to NetSuite fields, handles trade invoices, and applies discount logic. Existing records are looked up by externalid to decide whether to create or update. Failures are logged to Sentry and to per-run log files in core/sync_logs/ .

Data Pipeline Refresh ( core/refresh_data.py )

Purpose: Entry point for the full daily sync.
Calls the steps in order: pull updated Marketron data for each table → upload to MongoDB → run invoice augmentation → check Tesorio SFTP file list → sync to NetSuite. The tables updated on each run are: clients, account executives, revenue sources, sale types, sale type categories, invoices, orders, and stations. This script is what the Render cron job calls.

Email Notification System ( core/create_emails.py , send_ae_email_simple.py , etc.)

Purpose: Generates aging report emails for AEs, the AR team, and clients.
Three email types are supported: AE aging (per Account Executive), AR aging (full view for the AR team), and AE contact request (asks AEs to update AP contact info in the CRM). Each email is generated from aging data in MongoDB using Jinja2 HTML templates. Generated emails are stored in MongoDB ( mktr__email_previews ) with a UUID batch ID, so they can be reviewed before sending. Sending is done via Mandrill (Mailchimp Transactional). The system also maintains a “do not contact” list in MongoDB per client and per AE.

Tesorio SFTP Sync ( core/sync_invoice_pdfs_to_tesorio_sftp.py )

Purpose: Uploads invoice PDFs (stored as base64 in MongoDB) to Tesorio’s SFTP server.
Iterates over all invoices in the mktr__invoices_pdf2 collection, skips any already uploaded (tracked in tesorio_pdf_invoice_metadata ), decodes base64, and uploads to /invoice_pdfs/<transactionNumber>.pdf on the SFTP. After uploading, generates a metadata CSV and uploads that to a separate SFTP path ( /invoice_pdf_importer/pending/ ) so Tesorio can import the files.

Reconciliation Tool ( reconcile_systems_new_format.py + Flask route )

Purpose: Compares invoice records between Marketron and NetSuite to find discrepancies.
Accepts two CSV exports (one from Marketron’s aging/transaction report, one from NetSuite’s invoice report). Matches on invoice transaction number, sums amounts for duplicates, then categorizes each record as: Matched, MK Only (exists in Marketron but not in NetSuite), NS Only (in NetSuite but not Marketron), or Non-Invoice (journal entries, payments, etc. that should be ignored). Produces a detailed summary with dollar amounts and a downloadable result CSV.
Exposed via the Flask web app at /reconciliation .

Manual Contact Mapping UI ( app.py + manual_contact_mapping.html )

Purpose: Lets the AR team manually map client Unified IDs to contact email addresses.
Reads and writes to the manual_contact_mapping MongoDB collection. Users can view, edit, download as CSV, and upload a replacement CSV. The endpoint at /api/manual_contact_mapping serves the data as JSON for the JS frontend. This was needed because automated Freshworks matching doesn’t always find a match for every client.

Freshworks Integration ( core/freshworks_utils.py , augment_clients_with_freshworks_plus.py )

Purpose: Pulls AP contact email data from Freshworks CRM to map to Marketron client IDs.
Authenticates using a Freshworks API token. Fetches all sales accounts using the “All Accounts” filter view. Results are cached to disk (JSON with expiry timestamp) to avoid repeated API calls.
The mapping is used during email generation to find the correct AP/billing contact email for each client.

9. My Role and Responsibilities

Backend / Python Development

• Built the core data pipeline in Python — Marketron API pull, MongoDB storage, and NetSuite sync
• Wrote the DataMapping / TableName data model used throughout the system
• Implemented paginated API fetching with incremental date filtering for all Marketron data types
• Built MongoDB helper layer (batch upserts, downloads, transform-and-reupload pattern)
• Wrote invoice augmentation logic (AE email join, discount calculation, transaction ID derivation)

NetSuite Integration

• Built the Python NetSuite RESTlet client with OAuth1 authentication
• Wrote the SuiteScript 2.x RESTlet ( mrktrestlet.js ) deployed inside NetSuite for create/update/delete/search/updatelines operations
• Wrote the sync orchestrator that transforms Marketron data into NetSuite field format and pushes it in batches
• Handled both sandbox and production NetSuite environments with environment-based credential switching
• Designed and implemented the netsuite_transactionID composite key solution for duplicate invoice numbers across markets

Web Application

• Built the Flask web application with session-based authentication
• Implemented the reconciliation UI — file upload, comparison logic, downloadable results
• Built the manual contact mapping UI with CSV import/export and MongoDB sync

Integrations

• Integrated Tesorio SFTP upload for invoice PDFs using pysftp
• Integrated Freshworks CRM API for AP contact email lookup
• Set up Sentry error tracking for pipeline monitoring

Email System

• Built the aging email generation system for AE, AR, and client email types
• Implemented batch UUID tracking and MongoDB-based email preview storage
• Set up do-not-contact list handling at both client and AE levels

Refactoring

• Planned and executed a codebase restructure from flat core/ layout to modular src/integrations/ packages
• Updated import paths across all affected files and added proper Python package structure with __init__.py files

10. Technical Implementation

Data Pull and Storage

The Marketron API is paginated. The system iterates pages until no more results are returned, using a per_page setting per data type (e.g., 500 for most types, 15 for orders which have large nested structures). For incremental runs, a date filter parameter (e.g., minimumModifiedUtc ) is added to only fetch records changed since a cutoff. Data is saved to JSON files locally, then uploaded to MongoDB. For tables with has_daily_changes=True , the upload is an upsert (not a full replace).

Data Unification

Marketron has multiple markets (radio stations / companies), and the same client or sale type may exist under different IDs in different markets. The system generates a unified_id by normalizing the name (lowercased, stripped, de-duplicated). A cluster mapping stores the many-to-one relationship of original IDs to unified IDs. When building records for NetSuite, the system uses unified IDs so that one NetSuite customer record represents one advertiser regardless of which market they appear in.

NetSuite RESTlet Flow

The RESTlet receives a JSON payload with a record_type , a method , and a data_list array. On create/update, it checks whether a record with the given externalid already exists in NetSuite. If it does, it updates it; if not, it creates it. The RESTlet returns a JSON response with success/failure status for each record, which the Python side logs. For invoice line updates, a separate updatelines method updates specific lines on an existing invoice by matching on lineuniquekey .

Duplicate Invoice ID Handling

Since Marketron scopes invoice numbers to markets, the same transaction number (e.g., INV-1234 ) can exist in multiple markets. When augmenting invoices, the system:

• 1. Checks if any other MongoDB record has the same transactionNumber
• 2. If no conflict — sets netsuite_transactionID = transactionNumber
• 3. If conflict — sets netsuite_transactionID = transactionNumber_
  

When syncing to NetSuite, netsuite_transactionID is used as the tranid lookup field.

Reconciliation Logic

The reconciliation tool does a two-pass comparison. First it loads the NetSuite CSV into a dict keyed by document number (summing amounts for duplicates). Then it iterates the Marketron CSV, looks up each transaction number in the NetSuite dict, and marks each as Matched / MK Only / NS Only. Non-invoice prefixes ( PA , JE , BA , TJ , RC ) are tagged separately. An EPSILON = 0.05 tolerance
is used for amount matching.

Authentication

• NetSuite: OAuth1 via requests_oauthlib , HMAC-SHA256 signature
• Marketron: API key header, plus username/password in some calls
• Freshworks: Token-based auth header ( Token token=… )
• Tesorio SFTP: Username/password via pysftp
• Web app: Simple session-based login (username/password from env vars)

Environment Configuration

All secrets (API keys, DB credentials, NetSuite tokens) are in a .localenv file (not committed to git). The NETSUITE_ENV variable switches between sandbox and production NetSuite. The ENVIRONMENT variable switches file paths for things like the aging CSV storage location.

Logging and Error Tracking

The sync scripts write timestamped log files to core/sync_logs/ on each run. Sentry SDK is initialized in data_map.py and used in the sync orchestrator to report failure cases with record-level context. The Flask app uses Python’s standard logging module at DEBUG level.

11. Challenges Faced

Duplicate invoice numbers across markets
Marketron numbers invoices per company (market), so two different markets can both have an invoice numbered MC-00123 . NetSuite uses transaction numbers as a unique key. This required designing a composite netsuite_transactionID field and updating all lookup logic to use it. Existing records in MongoDB and NetSuite had to be backfilled with the new field using a one-off script.

NetSuite RESTlet deployment workflow
NetSuite doesn’t allow pushing JavaScript via API — the SuiteScript file ( mrktrestlet.js ) has to be manually pasted into the Script editor in the NetSuite UI. Any change to the RESTlet required a manual deploy step, which slowed down iteration. Sandbox and production scripts have different script IDs and the file needed to be kept in sync between both.

Data unification complexity
The same client, sale type, or revenue source could appear under 30+ different IDs across markets. Building a reliable clustering/unification step that worked consistently across all data types required careful normalization (case-insensitive name matching, handling empty/null names) and manual overrides for edge cases.

Large invoice payloads causing timeout issues
Invoice records in Marketron include nested order line arrays, which can be large. The initial pagination for orders used 500 per page, which caused HTTP timeouts. This was tuned down to 15 per page for orders to keep requests within acceptable time limits.

Freshworks contact matching gaps
Marketron client names don’t always match the company name in Freshworks CRM exactly, so automated mapping often left clients without an AP email address. This is why the manual contact mapping UI was built — to let the AR team fill in gaps that the automated Freshworks lookup couldn’t handle.

pysftp host key issues
The Tesorio SFTP server’s host key caused connection failures in some environments. This was handled by disabling host key checking ( cnopts.hostkeys = None ), which was an acceptable trade-off for an internal transfer to a known server.

Incremental vs. full data pulls
For some Marketron endpoints, the date filter parameters didn’t always return recently modified records reliably. The workaround was to run wider date windows (7-day or 30-day lookback) rather than strict incremental pulls.

12. Testing and Validation

• NetSuite dry run mode: DRY_RUN = True flag in the sync script prints what would be sent without actually calling the RESTlet. Used to verify field mappings before writing to production.
• Sandbox environment: All development and testing done against the NetSuite sandbox account ( 9639742-sb1 ). Separate credentials and script IDs for sandbox vs. production.
• Single record test mode: SINGLE_TEST_CASE = True flag in the upload function processes only one record at a time for debugging specific cases
• Reconciliation self-test: The reconciliation tool was tested against real Marketron and NetSuite CSV exports to verify matching rates and identify known-missing invoices.
• Email preview-only mode: All emails are generated and stored in MongoDB with sent: false by default, allowing full review before any actual sending occurs.
• Transaction ID verification: A dedicated script ( verify_netsuite_invoices.py ) queries NetSuite for each invoice by transaction ID and confirms the record exists with the correct data
• Test data scripts: run_invoice_test.py and process_test_data_with_api.py allow processing a controlled set of invoice IDs from CSV files to validate the augmentation and sync pipeline end-to-end without running everything.

13. Deployment / Environment

14. Conclusion

This project built a working integration between Marketron (traffic/order management) and NetSuite (ERP/accounting) for BBGI. The system pulls advertising data across multiple markets daily, stores it in MongoDB, processes it, and syncs it to NetSuite as proper financial records.

The main complexity was around data unification (same entity appearing under many IDs across markets), duplicate transaction number handling, and the manual RESTlet deployment workflow inside NetSuite. These were all solved with a combination of derived fields, composite keys, and environment-based configuration.

On top of the pipeline, an internal Flask tool was delivered that gives the AR team a reconciliation view, contact mapping management, and automated aging emails via Mandrill. Invoice PDFs are also synced to Tesorio automatically via SFTP as part of the daily run.

The codebase was also refactored from a flat script layout into a structured src/integrations/package organization to improve maintainability and make each integration boundary explicit.