Shopify & GA4 Daily Sales & Performance Reporting Automation: n8n & Activepieces

                // n8n Code Node: Dual-Source Aggregator & Daily Sales Metrics
const dateContext = $("Compute Reporting Date Window").first().json;
const shopifyRaw = $("Shopify: Get Many Orders").first().json;
const ga4Raw = $("Google Analytics 4: Get Daily Report").first().json;

// 1. Process Shopify Orders (Financial Source of Truth)
const orders = Array.isArray(shopifyRaw) ? shopifyRaw : (shopifyRaw.orders || []);
let grossSales = 0, totalDiscounts = 0, totalTax = 0, totalRefunds = 0;
const skuVolume = {};

orders.forEach(ord => {
  grossSales += parseFloat(ord.subtotal_price || ord.total_price || 0);
  totalDiscounts += parseFloat(ord.total_discounts || 0);
  totalTax += parseFloat(ord.total_tax || 0);

  (ord.refunds || []).forEach(ref => {
    (ref.transactions || []).forEach(t => totalRefunds += parseFloat(t.amount || 0));
  });

  (ord.line_items || []).forEach(item => {
    const sku = item.sku || item.title || "Product";
    skuVolume[sku] = (skuVolume[sku] || 0) + (item.quantity || 1);
  });
});

const orderCount = orders.length;
const netSales = grossSales - totalDiscounts - totalRefunds;
const aov = orderCount > 0 ? (netSales / orderCount) : 0;

// Sort top SKUs
const topSkus = Object.entries(skuVolume)
  .sort((a, b) => b[1] - a[1])
  .slice(0, 2)
  .map(([name, qty]) => `${name} (${qty} sold)`);

// 2. Process GA4 Telemetry & Attribution
let ga4Revenue = 0, ga4Sessions = 0, ga4Transactions = 0;
if (ga4Raw.rows && ga4Raw.rows.length > 0) {
  ga4Revenue = ga4Raw.rows.reduce((sum, r) => sum + parseFloat(r.metricValues?.[0]?.value || 0), 0);
  ga4Sessions = ga4Raw.rows.reduce((sum, r) => sum + parseInt(r.metricValues?.[1]?.value || 0), 0);
  ga4Transactions = ga4Raw.rows.reduce((sum, r) => sum + parseInt(r.metricValues?.[2]?.value || 0), 0);
}
const conversionRate = ga4Sessions > 0 ? ((orderCount / ga4Sessions) * 100).toFixed(2) + "%" : "0.00%";

return [{
  json: {
    report_date: dateContext.report_date || new Date().toISOString().split('T')[0],
    shopify_orders: orderCount,
    shopify_gross: "$" + grossSales.toFixed(2),
    shopify_discounts: "$" + totalDiscounts.toFixed(2),
    shopify_refunds: "$" + totalRefunds.toFixed(2),
    shopify_net: "$" + netSales.toFixed(2),
    shopify_aov: "$" + aov.toFixed(2),
    ga4_revenue: "$" + ga4Revenue.toFixed(2),
    ga4_sessions: ga4Sessions,
    ecommerce_cr: conversionRate,
    top_sku_1: topSkus[0] || "N/A",
    top_sku_2: topSkus[1] || "N/A",
    logged_at: new Date().toISOString()
  }
}];