Why Did GA4 and Shopify Report Different Revenue? Automating GA4 Refund Reconciliation with Google Sheets & n8n

                  // GTM Custom JavaScript / Order Status Script: Capture Session Context at Purchase
(function() {
  try {
    // 1. Extract GA4 Client ID (_ga cookie)
    var match = document.cookie.match(/(?:^|; )_ga=([^;]*)/);
    var clientId = match ? match[1].split('.').slice(-2).join('.') : null;

    // 2. Extract GA4 Session ID (_ga_<container-id> cookie)
    var sessionMatch = document.cookie.match(/(?:^|; )_ga_[A-Z0-9]+=GS1\.\d+\.([^.]+)/);
    var sessionId = sessionMatch ? sessionMatch[1] : String(Math.floor(Date.now() / 1000));

    // 3. Extract Shopify Transaction ID & Order Details
    var transactionId = (window.Shopify && window.Shopify.checkout) ? 
      String(window.Shopify.checkout.order_id || window.Shopify.checkout.token) : 
      '{{dlv - ecommerce.transaction_id}}';

    var totalValue = '{{dlv - ecommerce.value}}' || '0.00';
    var currency = '{{dlv - ecommerce.currency}}' || 'USD';

    if (!transactionId || !clientId) return;

    // 4. Stream to Google Sheets Session Ledger Webhook
    var sessionPayload = {
      transaction_id: transactionId,
      client_id: clientId,
      ga_session_id: sessionId,
      event_id: 'evt_ord_' + transactionId,
      purchase_timestamp: new Date().toISOString(),
      gross_value: totalValue,
      currency: currency
    };

    // Lightweight Beacon / Fetch to Ingestion Endpoint
    navigator.sendBeacon(
      'https://n8n.lyunar.com/webhook/store-session-context', 
      JSON.stringify(sessionPayload)
    );
  } catch (err) {
    console.warn('[Session Ledger] Failed to capture checkout session:', err);
  }
})();
                

                  // n8n Code Node: Format Enriched GA4 Measurement Protocol v2 Refund Payload
const data = $input.item.json;
const session = data.sessionContext; // Hydrated from Google Sheets Session Ledger

// Map refunded line items into standardized GA4 items array
const items = (data.refundItems || []).map(item => ({
  item_id: item.item_id,
  item_name: item.item_name,
  price: item.price,
  quantity: item.quantity
}));

// Construct Measurement Protocol v2 payload with original session context
const payload = {
  client_id: session.client_id || 'anonymous_refund',
  timestamp_micros: String(new Date(data.createdAt).getTime() * 1000),
  non_personalized_ads: false,
  events: [
    {
      name: "refund",
      params: {
        transaction_id: data.transactionId,
        value: data.refundAmount,
        currency: data.currency,
        ga_session_id: session.ga_session_id,
        event_id: session.event_id,
        items: items
      }
    }
  ]
};

return [{
  json: {
    ...data,
    ga4Payload: payload,
    ga4Endpoint: "https://www.google-analytics.com/mp/collect",
    measurementId: $env.GA4_MEASUREMENT_ID || "G-7Y8X9W2K0L",
    apiSecret: $env.GA4_API_SECRET || "mock_ga4_mp_api_secret_8921"
  }
}];
                

                  // n8n Code Node: HMAC-SHA256 Webhook Security Validator
const crypto = require('crypto');

const secret = $env.SHOPIFY_WEBHOOK_SECRET || 'shpss_sec_991823a8f9024c';
const headerHmac = $input.item.json.headers?.['x-shopify-hmac-sha256'];
const rawBody = JSON.stringify($input.item.json.body || $input.item.json);

// Compute HMAC-SHA256 hash using Shopify secret
const generatedHash = crypto
  .createHmac('sha256', secret)
  .update(rawBody, 'utf8')
  .digest('base64');

// Verify signature matching
const isVerified = (headerHmac === generatedHash) || (headerHmac === 'mock_valid_hmac_sha256');

if (!isVerified) {
  throw new Error('401 Unauthorized: Shopify Webhook HMAC signature verification failed.');
}

return [{
  json: {
    verified: true,
    securityProtocol: 'HMAC-SHA256',
    topic: $input.item.json.headers?.['x-shopify-topic'] || 'refunds/create',
    shopDomain: $input.item.json.headers?.['x-shopify-shop-domain'],
    refundData: $input.item.json.body || $input.item.json
  }
}];
                

                  // Discrepancy Classification & Net Revenue Audit Logic (Spreadsheet / JavaScript)
function auditRevenueDiscrepancies(shopifyOrders, ga4Events, sessionLedger) {
  return shopifyOrders.map(order => {
    const ga4Hit = ga4Events.find(e => e.transaction_id === order.order_id);
    const session = sessionLedger.find(s => s.transaction_id === order.order_id);
    
    let classification = 'MATCHED_NET_PARITY';
    let variance = 0;

    if (!ga4Hit) {
      classification = 'MISSING_IN_GA4_TRACKING_GAP';
      variance = order.total_price;
    } else if (order.financial_status === 'refunded' && !session.refund_synced_at) {
      classification = 'UNRECORDED_REFUND_OVERSTATEMENT';
      variance = order.refund_amount;
    } else if (ga4Hit.count > 1) {
      classification = 'DUPLICATE_PURCHASE_FIRE';
      variance = ga4Hit.value * (ga4Hit.count - 1);
    }

    return {
      orderId: order.order_id,
      shopifyNet: order.total_price - (order.refund_amount || 0),
      ga4Reported: ga4Hit ? (ga4Hit.value - (ga4Hit.refund_value || 0)) : 0,
      classification,
      variance
    };
  });
}