Cheap Domain Hosting for business & blog

logo
Menu

How to Build a Smart Data Hub with Python and Google Workspace Tools

Creating a centralized data hub is a powerful way to unify business data, automate reporting, and unlock deeper insights. For companies that rely on legacy ERPs and various marketing tools, combining data in one place like Google BigQuery makes daily operations smoother and smarter. In this guide, we explore how to set up a modern ETL pipeline using Python and Google Workspace tools, integrating sources like Mon Ami ERP, ActiveCampaign, and marketing ad platforms into a fully functional analytics ecosystem.

This setup is perfect for teams looking to automate data workflows, visualize sales and inventory, and even experiment with AI-driven reporting.

Connecting ERP Data to BigQuery

The starting point of any ETL process is to extract data. In this setup, we connect Mon Ami ERP, which stores sales, stock, and product data.

Two ways to pull ERP data:

  • ODBC connector using Python’s pyodbc if you have live access
  • Nightly CSV dump if the database cannot be accessed directly

Once the data is fetched, Python scripts using pandas can clean and format the data before uploading it to Google BigQuery.

Tables to upload:

  • sales_data
  • stock_levels
  • product_master

BigQuery becomes the main destination for all structured business data.

Cleaning and Structuring the Data

After loading the raw tables, the next step is data modeling. Use SQL views or staging tables in BigQuery to simplify the structure for analysis.

Goals of data cleaning:

  • Normalize column formats
  • Remove duplicates or blanks
  • Map foreign keys (e.g., product ID to product name)

These cleaned views are used as the base for reports and AI queries later.

Building a BI Dashboard in Looker Studio

With the data ready in BigQuery, you can now create your first dashboard using Looker Studio (formerly Google Data Studio). This gives your team an interactive visual report without writing code.

Key elements in version 0.1 dashboard:

  • Total sales over time
  • Stock levels by category
  • Filters for date, product, and customer
  • Conditional formatting for low inventory

This dashboard provides instant value for both sales and warehouse teams.

Integrating Marketing Data via API

Marketing performance is critical to measure alongside sales. Use Python’s requests library to pull data from:

  • ActiveCampaign (contacts, deals)
  • Google Ads and Meta Ads cost endpoints

Transform and push this data into a table called marketing_costs in BigQuery. This lets you calculate metrics like ROAS and ad spend trends.

Automating the Entire Flow with Google Apps Script

To avoid manual effort, all processes should run automatically. You can use Google Apps Script or Google Cloud Functions to schedule tasks and monitor failures.

Key automation features:

  • Daily refresh of all ETL steps
  • Error logging and retry mechanism
  • Alerts via email or Slack in case of failure

Apps Script is simple to maintain and integrates well with Sheets, Forms, and Gmail.

Barcode Scanning Workflow with Zebra TC56

Physical inventory operations can also be connected to your digital hub. Zebra TC56 with DataWedge can be used to scan barcodes into a Google Form.

Steps to connect barcode data:

  • Google Form accepts scan input (timestamp, product ID)
  • Responses land in a connected Google Sheet
  • Apps Script pushes the Sheet to BigQuery as movimenti_barcode

Now your stock movement is fully trackable and reportable in real time.

Agentic AI Assistant for Business Questions

To take things to the next level, you can connect OpenAI’s Assistants API to your data. Build a bot that:

  • Accepts business questions (e.g., “What were sales last week?”)
  • Translates the question into SQL queries
  • Runs them on BigQuery
  • Replies with results in Google Chat

This AI assistant can answer predefined queries, saving time for your team and showcasing the power of natural language insights.

Handing Off and Documenting Everything

Good documentation ensures long-term maintainability. As a final step, include:

  • A tech readme explaining each script and function
  • Schema diagram showing table relationships
  • Instructions for how to restart, update, or troubleshoot
  • One hand-over call to walk through the system

This makes sure your team can confidently manage the setup on their own.

Unlocking Value from Data the Smart Way

Setting up a data hub using Python and Google Workspace creates a powerful engine for insights. By pulling data from ERPs, APIs, and barcode systems into BigQuery, your business gets a full picture of operations, performance, and growth. Automations reduce manual tasks, dashboards reveal patterns, and AI opens the door to smart decisions.

With the right mix of scripting, cloud tools, and clean architecture, you can turn scattered data into a strategic asset.