Clean Royalty Statements from Platforms into a Canonical Schema

Every platform thinks it invented royalty statement formatting. Netflix uses quarters and ISO territory codes with a “Revenue USD” column. Amazon uses monthly buckets, territory names in English, and a “Net Receipts” column that already subtracts fees. Apple uses a fiscal calendar that’s five weeks off everyone else’s and a “Units” column that matters for compilation titles.

Step 1: Prep the Raw Paste

Create a sheet called Raw Statement Paste. Paste the platform CSV in, column A onward. Don’t rename columns — the script reads them by position within a platform-specific mapping. In cell B1, set the Platform dropdown: Netflix | Amazon | Apple | Other.

Step 2: Paste the Normalizer Script

Open Extensions > Apps Script. Paste in openrights-royalty-normalizer.gs. Structure:

function normalizeStatement() {
  const ss = SpreadsheetApp.getActive();
  const raw = ss.getSheetByName("Raw Statement Paste");
  const platform = raw.getRange("B1").getValue();
  const mapping = PLATFORM_MAPPINGS[platform];
  if (!mapping) throw new Error(`Unknown platform: ${platform}`);

  const data = raw.getRange(3, 1, raw.getLastRow()-2, mapping.sourceCols).getValues();
  const output = data.map(row => ({
    title: titleCase(row[mapping.titleCol]),
    territory: normalizeTerritory(row[mapping.territoryCol]),
    period: normalizePeriod(row[mapping.periodCol], platform),
    grossUSD: row[mapping.grossCol] * mapping.fxTo(row),
    netUSD: row[mapping.netCol] * mapping.fxTo(row),
    fxRate: mapping.fxTo(row),
    platform,
    sourceRow: row[0],
  }));
  writeCanonical(output);
}

The PLATFORM_MAPPINGS object encodes each platform’s column layout. Add a new platform by adding one entry; the core loop stays the same.

Step 3: Territory Normalization

Platforms disagree on territory representation. Amazon says “United Kingdom”, Netflix says “GB”, Apple says “GBR”. The helper function normalizes all three to ISO α-2:

function normalizeTerritory(input) {
  const map = {
    "UK": "GB", "United Kingdom": "GB", "GBR": "GB",
    "USA": "US", "United States": "US",
    "Germany": "DE", "DEU": "DE",
    // extend as needed
  };
  return map[input] ?? input;
}

If an input isn’t in the map and isn’t already a valid α-2, it passes through unchanged — you’ll see it in the canonical output and know to add it to the map.

Step 4: Period Normalization

“Q1 2026”, “2026-Q1”, “Jan-Mar 2026”, “202601” — same period, four encodings. The normalizer converts all to YYYY-Qn:

function normalizePeriod(input, platform) {
  if (/^\d{4}-Q\d$/.test(input)) return input;
  if (/^Q\d \d{4}$/.test(input)) {
    const [q, y] = input.split(" ");
    return `${y}-${q}`;
  }
  if (/^\d{6}$/.test(input)) {
    const y = input.slice(0,4), m = parseInt(input.slice(4,6));
    return `${y}-Q${Math.ceil(m/3)}`;
  }
  return `INVALID: ${input}`;
}

Apple’s fiscal calendar is an exception — Q1 Apple ≠ Q1 calendar. If Platform=Apple, the script applies a month offset before computing the quarter. Document the offset in a note so the reviewer understands the shift.

Step 5: FX Conversion

Platforms that pay in local currency need conversion to USD before reconciliation. The script uses a static FX_RATES map seeded monthly:

const FX_RATES = {
  "2026-Q1": { EUR: 1.08, GBP: 1.26, JPY: 0.0067 },
  "2026-Q2": { EUR: 1.09, GBP: 1.27, JPY: 0.0068 },
  // update quarterly from a trusted source
};

For audit-grade work, replace with the FX methodology specified in the contract (month-end ECB rate, monthly average, etc.). The static map is fine for reconciliation spot-checks.

Step 6: Write the Canonical Sheet

The script writes output to a Canonical Output sheet. Each row is one normalized statement line. Sort by Title | Territory | Period to match the ordering the reconciliation workbook expects.

Common Issues

Compilation titles. One “title” may be a bundle (album, boxed set). Platforms flatten to the bundle name; for reconciliation you may need to allocate by unit share. The script doesn’t handle this — flag compilation rows and allocate manually.
Negative adjustments. Quarters sometimes include reversals for prior-quarter errors. These show as negative amounts and are easy to miss. Flag negative grossUSD rows in the canonical output with a Notes = ADJUSTMENT entry.
Unallocated / pooled territories. “Rest of World” is a common bucket. The normalizer emits it as-is; reconciliation must decide whether to accept the pool or require allocation.

Download the Template + Script

The Royalty Statement Cleanup Workbook ships with the paste tab, mapping reference, and canonical output pre-wired. The openrights-royalty-normalizer.gs Apps Script handles the per-platform logic. Feed the canonical output into the Royalty Reconciliation workflow next.