ETL Pipelines for ERP Data: Extracting Insights from Odoo & Shopify

Your business data lives in silos. Odoo has your accounting, inventory, and HR data. Shopify has your eCommerce transactions. GoHighLevel has your marketing and CRM data. Google Analytics has your web traffic. Each platform has its own reporting, but none of them can answer cross-system questions: What is the true customer acquisition cost including fulfillment and support? Which marketing channels bring customers with the highest lifetime value across both online and offline sales?

ETL (Extract, Transform, Load) pipelines bridge these silos by pulling data from every source, cleaning and standardizing it, and loading it into a unified data warehouse where your BI tools can query across all systems.

Key Takeaways

• ETL pipelines connect data silos (Odoo, Shopify, GoHighLevel) into a single warehouse, enabling cross-system analytics that no individual platform can provide
• Three extraction strategies (API, database replication, webhooks) suit different data sources and freshness requirements
• Transform patterns (deduplication, normalization, enrichment) ensure data quality before it reaches the warehouse
• Incremental loading with idempotent operations keeps pipelines reliable and efficient as data volume grows

---

Extraction Strategies

The extraction phase pulls raw data from source systems. Each data source has different capabilities and constraints, requiring different extraction approaches.

API Extraction

Most modern platforms expose REST or GraphQL APIs for data access. API extraction is the safest approach because it uses the platform's official interface and does not depend on internal database structures.

Odoo XML-RPC / JSON-RPC API:

Odoo exposes its data through XML-RPC and JSON-RPC endpoints. You can read any model (customers, sales orders, invoices, inventory moves) with field-level granularity and domain filters.

• Endpoint: https://your-odoo.com/jsonrpc
• Authentication: Database name, username, password (or API key)
• Pagination: Use offset and limit parameters
• Incremental: Filter by write_date > last_sync_timestamp
• Rate limits: Self-hosted Odoo has no rate limits. Odoo SaaS applies per-second limits.

Shopify REST / GraphQL API:

Shopify's API provides access to orders, products, customers, inventory, and more.

• Endpoint: https://your-store.myshopify.com/admin/api/2024-10/
• Authentication: Private app credentials or OAuth access token
• Pagination: Cursor-based (follow next link header)
• Incremental: updated_at_min parameter on most resources
• Rate limits: 2 requests/second (REST) or 1,000 cost points/second (GraphQL)

GoHighLevel API:

• Endpoint: https://rest.gohighlevel.com/v1/
• Authentication: API key or OAuth
• Resources: Contacts, opportunities, pipelines, campaigns, conversations
• Incremental: Filter by date range where supported

Data Source Extraction Methods

| Data Source | Best Method | Refresh Frequency | Incremental Field | Rate Limit | |-----------|------------|-------------------|-------------------|-----------| | Odoo ERP | JSON-RPC API | Every 15-60 min | write_date | None (self-hosted) | | Shopify | GraphQL API | Every 15-60 min | updated_at | 1,000 pts/sec | | GoHighLevel | REST API | Every 1-4 hours | Date range filter | Varies | | Google Analytics | GA4 Data API | Daily | Date dimension | 10 req/sec | | Stripe | REST API | Every 15 min | created cursor | 100 req/sec | | PostgreSQL (direct) | Logical replication | Real-time | WAL stream | N/A | | Flat files (CSV) | SFTP/S3 polling | Varies | File timestamp | N/A |

Database Replication

For Odoo specifically, direct database access is sometimes faster and more complete than the API. Since Odoo runs on PostgreSQL, you can use logical replication to stream changes from the Odoo database to your analytics database in near-real-time.

Advantages: No API rate limits, captures all fields (including those not exposed via API), near-zero latency.

Disadvantages: Tightly coupled to Odoo's internal schema (breaks on upgrades), requires database access (not available for Odoo SaaS), bypasses Odoo's access control layer.

Recommendation: Use API extraction for most sources. Reserve database replication for high-volume, latency-sensitive Odoo deployments where you control the database.

Webhook-Based Extraction

Webhooks push data to your pipeline in real-time when events occur. Shopify supports webhooks for orders, products, customers, and inventory changes. Odoo supports webhooks via custom modules.

Advantages: Real-time data with no polling overhead.

Disadvantages: Can miss events if your endpoint is down (need retry logic), out-of-order delivery, no backfill capability.

Recommendation: Use webhooks for real-time dashboards and alerts. Use scheduled API extraction for the warehouse to ensure completeness.

---

Transform Patterns

Raw data from source systems is messy: duplicate records, inconsistent formats, missing values, conflicting naming conventions. The transform phase cleans and standardizes data before it reaches the warehouse.

Deduplication

Customers exist in multiple systems with different IDs. The same person might be "John Smith" in Odoo (ID: 42), "[email protected]" in Shopify (ID: 8891), and "John S." in GoHighLevel (ID: contact_xyz).

Deduplication strategies:

• Email match: The simplest approach. Match records across systems by email address.
• Fuzzy name matching: Use Levenshtein distance or phonetic matching for names that are similar but not identical.
• Phone number normalization: Strip formatting and match on digits.
• Composite key: Match on a combination of email + phone + name for higher confidence.

Create a master customer record in the warehouse that links to IDs in all source systems. This enables the RFM analysis and cohort analysis that cross system boundaries.

Normalization

Standardize data formats across systems:

• Currency: Convert all monetary amounts to a base currency using historical exchange rates (date of transaction, not current rate).
• Dates: Convert all timestamps to UTC. Odoo stores in UTC, Shopify in the shop's timezone.
• Status fields: Map system-specific statuses to a universal set. Odoo's sale status maps to "Confirmed," Shopify's paid maps to "Confirmed."
• Units: Standardize units of measure. Odoo might track in kilograms, Shopify in pounds.
• Address format: Standardize country codes (ISO 3166), state/province codes, postal code formats.

Enrichment

Add derived fields that do not exist in any source system:

• Customer lifetime value: Calculated from transaction history across all channels.
• RFM scores: Computed from recency, frequency, and monetary values.
• Acquisition channel attribution: Mapped from first-touch UTM parameters.
• Geographic enrichment: Derive region, timezone, and market tier from address data.
• Business day calculation: Flag weekends and holidays for accurate SLA measurement.

Data Quality Checks

Run automated checks during the transform phase:

| Check | Rule | Action on Failure | |-------|------|------------------| | Null check | Required fields cannot be null | Log warning, fill default, or reject | | Range check | Amounts > 0, quantities >= 0 | Log warning, investigate | | Referential integrity | Every order has a valid customer | Create placeholder dimension record | | Freshness check | Data arrived within expected window | Alert on-call team | | Duplicate check | No duplicate primary keys | Deduplicate, keep most recent | | Reconciliation | Sum of order amounts matches source total | Investigate discrepancy |

---

Load Strategies

The load phase writes transformed data into the data warehouse.

Full Load vs. Incremental Load

Full load: Truncate the target table and reload all data from scratch. Simple and guarantees consistency but impractical for large tables (millions of rows) because it takes too long and wastes compute.

Incremental load: Only process records that are new or changed since the last load. Faster and more efficient. Requires tracking the last successful load timestamp or using change data capture.

Recommendation: Use incremental loading for fact tables (sales, inventory) and full load for small dimension tables (products, employees) that change infrequently.

Upsert (Merge) Pattern

The most robust incremental load pattern is the upsert: INSERT new records and UPDATE existing records that have changed.

For each record in the transformed batch:
  IF record exists in target (match on business key):
    IF record has changed (compare hash of all fields):
      UPDATE the target record
    ELSE:
      SKIP (no change)
  ELSE:
    INSERT the new record

This pattern is idempotent --- running it twice with the same data produces the same result. This matters because ETL failures require re-running, and idempotent loads prevent duplicate data.

Load Scheduling

| Pipeline | Schedule | Duration | Dependencies | |----------|----------|----------|-------------| | Odoo sales extraction | Every 30 min | 2-5 min | None | | Shopify orders extraction | Every 30 min | 1-3 min | None | | Customer deduplication | Every 30 min (after extraction) | 3-8 min | Odoo + Shopify loads | | Dimension refresh | Daily at 2 AM | 10-20 min | None | | RFM scoring | Daily at 3 AM | 5-15 min | Dimension refresh | | Data quality checks | After every load | 1-2 min | Load completion | | Materialized view refresh | After every load | 2-10 min | Load completion |

---

Pipeline Architecture

Components

A production ETL pipeline needs these components:

1. Scheduler: Triggers pipeline runs on schedule (cron, Airflow, Dagster, or Prefect).
2. Extractors: Source-specific connectors that pull data via API, database, or webhook.
3. Transformers: Business logic that cleans, standardizes, and enriches data.
4. Loaders: Write transformed data to the warehouse.
5. Orchestrator: Manages dependencies between pipeline steps (extraction before transformation, transformation before loading).
6. Monitoring: Tracks pipeline health, data freshness, and quality metrics.
7. Alerting: Notifies the team when pipelines fail or data quality drops.

Tool Options

Lightweight (mid-market starting point):

• Custom scripts (Python + SQLAlchemy or Node.js) scheduled via cron
• dbt for SQL-based transformations
• Simple monitoring via log files and email alerts

Mid-weight (scaling up):

• Apache Airflow for orchestration
• Singer/Meltano for pre-built source connectors
• Great Expectations for data quality testing

Enterprise:

• Fivetran or Airbyte for managed extraction
• Snowflake or BigQuery as the warehouse
• Monte Carlo or Bigeye for data observability

For most mid-market companies running Odoo and Shopify, custom Python scripts with dbt transformations and cron scheduling are sufficient until data volume exceeds 10 million rows per day or the number of data sources exceeds 10.

---

Error Handling and Recovery

ETL pipelines fail. APIs return errors, source systems go down for maintenance, data formats change without notice, network connections drop. Robust error handling separates production-grade pipelines from fragile scripts.

Retry Logic

Implement exponential backoff for transient errors (rate limits, timeouts, server errors):

• Attempt 1: Immediate
• Attempt 2: Wait 5 seconds
• Attempt 3: Wait 30 seconds
• Attempt 4: Wait 2 minutes
• Attempt 5: Wait 10 minutes
• After 5 failures: Alert the team and pause the pipeline

Dead Letter Queue

Records that fail transformation (invalid data, unexpected format) go to a dead letter queue for manual review. Do not let one bad record stop the entire pipeline.

Checkpoint and Resume

For long-running extractions, save progress checkpoints. If the pipeline fails after extracting 80 percent of records, it should resume from the last checkpoint, not start over.

Monitoring Dashboard

Track pipeline health in your BI dashboards:

• Last successful run timestamp per pipeline
• Records processed per run (trend over time)
• Error rate per pipeline
• Data freshness (time since last warehouse update)
• Dead letter queue depth

---

Frequently Asked Questions

Should we build ETL pipelines in-house or use a managed service?

For mid-market companies with one to three data sources and a developer on staff, in-house pipelines (Python scripts + cron) are cost-effective and fully customizable. Managed services like Fivetran or Airbyte make sense when you have five or more data sources, no developer bandwidth for ETL maintenance, or need pre-built connectors for platforms that have complex APIs. The managed services cost $500 to $2,000 per month for mid-market volumes, which is less than the developer time required to build and maintain equivalent custom connectors.

How do we handle schema changes in Odoo or Shopify?

Monitor source system release notes for breaking changes. Build your extractors to validate the response schema before processing --- if a field is missing or a new field appears, log a warning rather than crashing. Use version pinning for Shopify's API (specify the API version in the URL). For Odoo, major version upgrades (e.g., 17 to 18) often change field names and model structures --- plan a pipeline update as part of your ERP upgrade project.

What about real-time ETL instead of batch?

Real-time ETL (sometimes called ELT or streaming ETL) processes events as they arrive rather than in scheduled batches. This is appropriate for real-time dashboards and operational alerts but adds complexity. Most mid-market companies get 95 percent of the value from 15 to 30 minute batch cycles. Start with batch, add real-time for specific high-value use cases.

How do we ensure data consistency between the warehouse and source systems?

Run daily reconciliation checks: compare aggregated totals in the warehouse (e.g., total orders, total revenue) against the source system's own reports. Flag discrepancies above a threshold (typically 0.1 percent for financial data). Common causes of discrepancy include timezone differences, deleted records, currency conversion rounding, and records created during the extraction window.

---

What Is Next

ETL pipelines are the plumbing that enables your entire analytics stack. They feed the data warehouse that powers self-service dashboards, predictive models, and customer segmentation. Building reliable pipelines is one of the highest-ROI investments in your BI strategy.

ECOSIRE builds ETL pipelines that connect Odoo, Shopify, GoHighLevel, and other platforms into a unified data warehouse. Our Odoo integration services handle the extraction layer, our OpenClaw AI platform manages transformation and quality checks, and our team designs the warehouse schema tailored to your analytics needs.

Contact us to unify your business data and unlock cross-system analytics.

---

Published by ECOSIRE --- helping businesses scale with AI-powered solutions across Odoo ERP, Shopify eCommerce, and OpenClaw AI.