Custom End-to-End Analytics for E-commerce
Data Processing
Background Niche: E-commerce (International & Local Sales) Platforms: Horoshop, Shopify, Marketplaces (Rozetka, Prom), Social Media (Instagram, Facebook)
Problem: As the business scaled, the true effectiveness of marketing campaigns became hidden in a chaos of data. Ad platforms (Meta, Google) were claiming the same conversions (data duplication). Canceled orders, refunds, and agency fees were not properly tracked. As a result, ROAS looked great, but the actual net profit (MER) didn’t match the cash flow.
Objective Develop and implement an End-to-End Analytics System (Single Source of Truth) that would:
Aggregate data from all sales channels (website, social media, marketplaces) and ad accounts.
Account for the actual order status (fulfilled / canceled).
Calculate not just the ad spend, but also the agency fees (Team Costs).
Provide objective ROAS and MER (Marketing Efficiency Ratio) metrics.
Solution: Custom Analytics Module Instead of using heavy, out-of-the-box solutions that are difficult to adapt to hybrid business models, we implemented a custom end-to-end analytics module.
Data Collection & Cleansing (Data Pipeline) Using n8n and APIs, we set up a seamless data collection process:
Orders & Statuses: Pulled from the CRM (e.g., KeyCRM) to instantly show the total actual revenue, deducting cancellations and refunds.
Expenses (Spend): Automatically parsed from Google Ads, Meta Ads, and TikTok Ads accounts.
Deduplication: Implemented an attribution model that assigns orders to their primary sources and isolates "Disputed/Unattributed" orders into a separate, transparent block.
Tracking Team Costs (Agencies & Fees) A core feature of the module is the Team Registry. We assigned every traffic source to a specific team or contractor (Target, SEO, SMM, Marketplace) and logged their monthly retainers (Fees). Now the system calculates: MER (Marketing Efficiency Ratio) = Total Revenue / (Ad Spend + Agency Fees) This allows the business owner to see whether not only the ads but also the contractors are actually paying off.
Geography & Multi-Currency Support Because the business operates across multiple markets, we divided the dashboard into clusters:
UA Region (UAH, Horoshop, Rozetka, Prom)
EU Region (EUR/USD, Shopify, Etsy) Within each cluster, data is automatically converted and standardized to a single denominator.
Dynamics & Data Layers The interface allows users to toggle data layers with a single click, cutting out unnecessary noise:
Finances: Gross Revenue vs Refund Amounts.
Orders: Successful Orders vs Cancellations.
Costs: Ad Spend vs Agency Fees.
Efficiency: Net ROAS vs MER.
Results
100% Financial Transparency: The owner now sees the real picture for every traffic source, factoring in refunds. "Blind spots" have been completely eliminated.
Budget Optimization by 20-30%: We identified Marketing Plays that showed a beautiful ROAS in the Meta dashboard but generated the highest cancellation rates in the CRM. The budget was reallocated accordingly.
Objective Team Evaluation: Implementing the MER metric with agency fees included allowed the business to transition to performance-based pay for contractors, backed by hard data.
Single Window for Decision Making: Eliminated the need to compile manual Excel reports. Data updates in real time, and the interface runs fast without visual clutter, focusing solely on the numbers.
Tech Stack: PHP, n8n (ETL/Pipelines), Docker, REST API, Smarty (Frontend), MariaDB.
Problem: As the business scaled, the true effectiveness of marketing campaigns became hidden in a chaos of data. Ad platforms (Meta, Google) were claiming the same conversions (data duplication). Canceled orders, refunds, and agency fees were not properly tracked. As a result, ROAS looked great, but the actual net profit (MER) didn’t match the cash flow.
Objective Develop and implement an End-to-End Analytics System (Single Source of Truth) that would:
Aggregate data from all sales channels (website, social media, marketplaces) and ad accounts.
Account for the actual order status (fulfilled / canceled).
Calculate not just the ad spend, but also the agency fees (Team Costs).
Provide objective ROAS and MER (Marketing Efficiency Ratio) metrics.
Solution: Custom Analytics Module Instead of using heavy, out-of-the-box solutions that are difficult to adapt to hybrid business models, we implemented a custom end-to-end analytics module.
Data Collection & Cleansing (Data Pipeline) Using n8n and APIs, we set up a seamless data collection process:
Orders & Statuses: Pulled from the CRM (e.g., KeyCRM) to instantly show the total actual revenue, deducting cancellations and refunds.
Expenses (Spend): Automatically parsed from Google Ads, Meta Ads, and TikTok Ads accounts.
Deduplication: Implemented an attribution model that assigns orders to their primary sources and isolates "Disputed/Unattributed" orders into a separate, transparent block.
Tracking Team Costs (Agencies & Fees) A core feature of the module is the Team Registry. We assigned every traffic source to a specific team or contractor (Target, SEO, SMM, Marketplace) and logged their monthly retainers (Fees). Now the system calculates: MER (Marketing Efficiency Ratio) = Total Revenue / (Ad Spend + Agency Fees) This allows the business owner to see whether not only the ads but also the contractors are actually paying off.
Geography & Multi-Currency Support Because the business operates across multiple markets, we divided the dashboard into clusters:
UA Region (UAH, Horoshop, Rozetka, Prom)
EU Region (EUR/USD, Shopify, Etsy) Within each cluster, data is automatically converted and standardized to a single denominator.
Dynamics & Data Layers The interface allows users to toggle data layers with a single click, cutting out unnecessary noise:
Finances: Gross Revenue vs Refund Amounts.
Orders: Successful Orders vs Cancellations.
Costs: Ad Spend vs Agency Fees.
Efficiency: Net ROAS vs MER.
Results
100% Financial Transparency: The owner now sees the real picture for every traffic source, factoring in refunds. "Blind spots" have been completely eliminated.
Budget Optimization by 20-30%: We identified Marketing Plays that showed a beautiful ROAS in the Meta dashboard but generated the highest cancellation rates in the CRM. The budget was reallocated accordingly.
Objective Team Evaluation: Implementing the MER metric with agency fees included allowed the business to transition to performance-based pay for contractors, backed by hard data.
Single Window for Decision Making: Eliminated the need to compile manual Excel reports. Data updates in real time, and the interface runs fast without visual clutter, focusing solely on the numbers.
Tech Stack: PHP, n8n (ETL/Pipelines), Docker, REST API, Smarty (Frontend), MariaDB.