The Problem
The marketing team at Malomatia was pulling data manually from Facebook, Instagram, GA4, LinkedIn, X (Twitter) and Simpplr Hub every week. Lots of copy-paste, reports were always a week behind, and there was no single source of truth for cross-channel performance.
The Solution
I designed and built the entire pipeline so that all 6 sources flow into Oracle automatically every day, and Power BI picks it up for self-service analytics. Every Python script follows a modular pattern with shared configuration, supports multiple run modes, and logs every execution to a central monitoring table. All jobs are scheduled via Windows Task Scheduler using .bat wrapper files with staggered timings on an internal VM.
Tech Stack
Architecture
The pipeline follows a 5-stage architecture: Data Sources → Extract (Python REST API scripts) → Transform (Oracle staging tables + stored procedures) → Load (Power BI Desktop for report creation) → Service (Power BI Service publishes reports to business users). Each stage has its own monitoring and error handling built in.
Data Pipeline Architecture - Marketing BI Platform at Malomatia
Meta Integration (Facebook + Instagram)
Facebook and Instagram data flows through the Meta Graph API. I built separate Python scripts for page-level metrics (reach, impressions, engagement) and post-level performance. Each script connects to Oracle using the oracledb library, loads data into staging tables first, then Oracle stored procedures transform and push to final reporting tables. Scripts support full and incremental modes with DELETE+INSERT logic for daily data and MERGE for post-level metrics. Long-lived access tokens are configured so the pipeline runs unattended without breaking every 60 days. All scripts are scheduled via .bat wrapper files on the internal VM with job execution logging to MKT_JOB_EXECUTION_LOG.
How it works:
LinkedIn Page Analytics Integration
LinkedIn was one of the hardest integrations to get off the ground. Getting Development Tier API access itself took weeks of coordination with the LinkedIn API team because their Community Management API has a strict vetting process before they even let you make your first API call. Once access was granted, I built modular Python scripts using the requests library with OAuth 2.0 authentication. Each script supports three run modes: full load (truncate + reload), incremental (Oracle MERGE upsert), and date-specific backfill. The codebase follows a shared config pattern with a common config.py and loader_helpers.py so that any future developer can add a new endpoint without touching existing scripts. All jobs are scheduled via Windows Task Scheduler using .bat wrapper files with staggered timings to avoid database contention.
How it works:
Google Analytics 4 Integration
GA4 integration uses the official google-analytics-data Python library to pull data from the GA4 Data API. The tricky part was handling non-SUM-safe metrics - unique-count metrics like Active Users, Sessions, and Bounce Rate get inflated when you sum them across dates because the same user gets counted multiple times. I had to work around this carefully because the marketing manager was matching every single KPI against the GA4 UI. Each script follows the same modular pattern with DELETE+INSERT logic, date dimension handling, and centralized job logging. Scripts are scheduled via .bat files with staggered timings on the internal VM.
The gotcha:
Views, Event Count, New Users are SUM-safe. But Active Users, Sessions, Bounce Rate are NOT - summing them across dates gives inflated numbers. Built separate summary tables without a date dimension to get exact GA4 UI match.
How it works:
Simpplr Hub Analytics Pipeline
Simpplr is the internal employee intranet platform at Malomatia. Unlike the other sources which use REST APIs, Simpplr pushes daily gzipped CSV exports to an AWS SFTP folder. I built a modular pipeline using Python with the paramiko library for SFTP connectivity and pandas for data transformation. Each entity (users, content, sites, interactions, login events, newsletters) has its own dedicated script that downloads the latest CSV, decompresses it, transforms the data, and loads it into Oracle.
How it works:
Custom Deneb Visuals
After the base platform was live, leadership wanted richer storytelling visuals. Standard Power BI charts were not giving the look the marketing head wanted, so I built custom visuals using Deneb / Vega-Lite directly inside Power BI. These visuals are powered by Oracle views (MKT_VW_CHANNEL_DAILY_METRICS and MKT_VW_CHANNEL_MONTHLY_SUMMARY) that aggregate data across all channels.
Stacked ridgeline chart showing each channel engagement waveform over time. Built with Deneb/Vega-Lite, reveals engagement patterns that standard bar charts completely miss.
Day-of-week and hour-of-day engagement matrix per channel. Tells the team exactly when to post for maximum reach. Uses Malomatia Sunday-Thursday work week.
What This Brings to the Table
This is not just a reporting tool - it changes how the marketing team operates on a daily basis. Here is the real business value this platform delivers:
No more pulling data from 6 different platforms. One dashboard, one version of the truth. The marketing head walks into a Monday meeting with numbers everyone trusts.
The team used to react to last week data. Now they see yesterday performance first thing in the morning. A bad campaign gets caught on Day 2, not Day 8.
No more copy-pasting from Meta Business Suite, GA4 dashboard, or LinkedIn Analytics. The entire data collection pipeline is fully automated and monitored.
Every KPI is verified 1:1 against the source platform UI. The team does not second-guess the dashboard - they act on it. This took careful handling of GA4 non-SUM-safe metrics and Meta lifetime-only limitations.
For the first time, the team can compare LinkedIn engagement against Instagram reach against GA4 website traffic in one view. They spot which channel is driving real results.
Adding a new data source means writing one new Python script and one Oracle table - the rest of the framework (logging, monitoring, email alerts, Power BI model) already handles it. Shared config pattern means any developer can extend the codebase.
Outcome
The marketing team now opens one Power BI report and gets a unified view of all their channels. No more manual data pulls, no more week-old numbers. All KPIs are verified one-to-one against the source UIs so they have full trust in the numbers.
Impact Summary
