ℹ️Settlement data: V2 Flat File format. COGS: Active batch from COGS Manager (by classification + item overrides).
📦
Loading P&L data...
P&L — Top Level v2
Select a month or settlement and click Load.
Select a transaction type and click "Load Samples" to see how individual transactions map to P&L columns.
Select a classification to see its full P&L breakdown.
Select a settlement to see its P&L breakdown. Compare this against the same settlement downloaded from Seller Central.
FIFO Upload Preview
Click Refresh to load FIFO costs
to
Set date range and click Check Coverage to see which classifications have FIFO cost entries for the period.
Click Refresh to load FIFO batch history
Upload Preview
Click Refresh to load costs
Classifications on settlements without a cost in the active batch
Click Refresh to detect missing class costs
SKUs without a class cost or class assignment — enter per-item costs here
Click Refresh to detect missing item costs
Item-level cost overrides (takes priority over class cost)
Click Refresh to load item overrides
Click Refresh to load batch history
Settlement Reconciliation
Verify each settlement's net proceeds match Amazon's records. Mismatches are flagged automatically.
Loading reconciliation data...
Unmatched Line Identifiers
Settlement line items with no mapping in the distribution map. These need to be classified.
Loading unmatched identifiers...
Satellite Data Gaps
Months where a settlement has reversed amounts (Advertising/Storage) but no satellite data has replaced them. Also shows COGS coverage.
Loading satellite gaps...
Monthly Satellite Summary
One row per P&L month with the Ads, Storage, and COGS satellite totals. COGS uses the same FIFO fallback chain as the per-month view.
Loading satellite summary...
Pull New Settlements
Lists settlement reports available from Amazon SP-API and shows what's already loaded into BigQuery. Click Load on any unloaded row to pull it in.
Click Refresh to fetch the list from Amazon.
Settlement Workbench
Assign L1/L2/L3 mappings per settlement. Match system numbers to Amazon's Net Proceeds breakdown.
🎉
ALL MATCHED!
System numbers match Amazon's breakdown. Ready to lock in.
Net Proceeds Breakdown
Amazon
System
Variance
Beginning Balance
—
—
Sales
—
—
Refunds
—
—
Expenses
—
—
Account Level Reserve
—
—
Net Proceeds
—
—
—
Unmatched Amount
$0
0 identifiers not yet assigned
🔍 Investigating:
System Breakdown by L3
🧪 PLAY MODE0 tentative plays — Save Amazon & Confirm are disabled until you officialize or discard
Unmatched Identifiers (0)
Data Validation
Validate each data source per settlement period. Sign off when confirmed correct.
New Month / New Settlement Checklist
Steps to complete each time a new settlement period closes, a new month begins, or a new storage invoice is available. Listed in order — do not skip steps.
S
SETTLEMENT DATA
Bring in the new settlement
If the settlement didn't come in automatically, trigger the pull manually. Settlements are biweekly and typically close around the 1st and 15th of each month.
Tips: USD settlements are 400K-660K rows. MXN/CAD are 1-2K rows. Look for the 20:17:12 timestamp pattern to identify USD settlements. SP-API only goes back 90 days. Automation status: Currently manual. No Cloud Scheduler configured for automatic settlement pulls.
Historical Settlements (beyond 90 days)
For settlements older than 90 days (SP-API limit), use the Pipes_Data archive table. Contains 221 settlements back to Dec 2023 (~74M rows).
Schema differences: Same V2 flat file data, slightly different types. Requires CAST when inserting:
• deposit_date, posted_date_time, settlement_start_date, settlement_end_date: DATETIME → CAST AS STRING
• reportid: INT64 → CAST AS STRING (maps to report_id)
• pull_timestamp: not in source — use CURRENT_TIMESTAMP()
⚠ Watch for duplicates: Some settlements in Pipes_Data have 2 different reportid values (bulk re-pull). The system sum will be 2x the Amazon deposit. Fix: keep the row with the smaller (older) reportid, delete the other. Always check ratio after loading.
Filter: Always filter WHERE currency = 'USD' and HAVING COUNT(*) > 10000 to skip MXN/CAD settlements.
Reconcile top-level numbers against Amazon
Go to the Reconciliation tab in this app. For each new settlement, compare the System Net Proceeds to the Net Proceeds shown in Amazon Seller Central under:
Seller Central → Reports → Payments → Statement View → Select the settlement → "Net Proceeds" at the top
Enter Amazon's number in the Reconciliation tab and click Verify. If the numbers match ($0.00 variance), the settlement is confirmed clean. If there's a mismatch, investigate before proceeding — it means data is missing or duplicated.
Check for unmapped unique identifiers that need L3 assignment
Go to the Unmatched tab. Any identifiers listed there are settlement line items that don't have a category in the distribution map (tbl_line_distribution_map). They need to be assigned an L3 (Level 3) category so they appear in the correct section of the P&L.
How to correctly assign L3 categories to new identifiers:
1. Determine the correct side: Is this a Sale, Refund, or Expense? This is the most critical decision. Look at the transaction_type and amount_description to determine this.
2. Match to an existing L3 pattern: Go to the Distribution Map tab to see all existing L3 categories and their unique identifiers. Find the most similar existing identifier and use the same L3.
4. Positive vs Negative amounts: Sales/income items are typically positive. Expenses/fees are typically negative. Refunds are negative (money going back). But some refund-related items are positive (e.g., commission refunded back to you). The sign determines which side of the P&L the amount lands on.
5. "Satellite" prefix: If the line item is for Advertising or Storage, prefix with Satellite - Expenses - because these are reversed and replaced by satellite data sources.
6. Insert into the map: Add a row to tbl_line_distribution_map with the unique_identifier and the chosen internal_pnl_category (L3).
SAT
SATELLITE DATA
Make sure FIFO costs are updated for the month
The FIFO Manager needs to have cost data materialized for the new month. If the month isn't showing in the FIFO Manager, trigger a materialization run.
Check the COGS Manager v2 tab — the month badges show coverage counts per tier (🟢 Official / 🟡 Fallback / 🔴 Unofficial / ⛔ Missing). If the month badge isn't showing at all, the FIFO data hasn't been materialized yet.
Check for classifications missing COGS
Load the month on the P&L Top Level v2 tab. Scroll down to the COGS section — if there's a red "⚠ X Classifications Missing FIFO Cost" banner, those classifications need costs assigned. Click the ⬇ Export Missing to Excel button to get a CSV with Classification, Units, and Gross Sales columns (plus a blank Unofficial Cost column to fill in). Send this list out to get the costs.
You can also use the COGS Manager v2 tab to view all coverage by month and add unofficial costs one by one.
Pull in the next available storage bill
Storage fees use a request/collect pattern with two Cloud Run functions in project amazon-api-pipes. SP-API report type: GET_FBA_STORAGE_FEE_CHARGES_DATA.
POST with empty body {} → auto-detects the next missing month and submits a report request
POST with {"month": "2026-03"} → requests a specific month
POST with {"backfill": true} → submits all missing months at once Logs to eddie_v2_storage_fees.tbl_storage_fees_v2_requests with status REQUESTED
POST with empty body {} → collects the oldest pending request (one at a time)
POST with {"collect_all": true} → collects all pending requests
Downloads the report, parses with FLOAT64 typing, loads to eddie_v2_storage_fees.tbl_storage_fees_v2 Updates request status to COLLECTED
⏱ Timing
After submitting a request, wait at least 15-20 minutes for Amazon to generate the report before collecting. Reports for the current month may not be available until a few days after month-end.
Automation (Cloud Scheduler, us-east1)
storage-fees-v2-request — runs at 2 AM ET on the 1st, 6th, 11th, 16th, 21st, 26th storage-fees-v2-collect — runs at 8 AM ET on the same days
Data lands in:amazon-api-pipes.eddie_v2_storage_fees.tbl_storage_fees_v2
Verify ad spend is correct and distributing properly
Quick check: Open the Ad Spend Checker webapp. Set the date range to the full month (1st to last day). On the By Portfolio tab, confirm the total spend amount looks correct for the month — compare against what you see in Amazon Ads console or your ad spend reports. This is the fastest way to spot if data is missing or duplicated.
Distribution check: Switch to the By Classification tab. Check the Spend Distribution Waterfall — the Authoritative Total and Distributed Total should match with $0.00 variance. If there's a variance, it means spend isn't distributing to classifications correctly/completely. The waterfall shows which allocation methods are being used (Direct, Sales Ratio, Portfolio Sales Ratio, Cross-Portfolio, Even Split).
Automation: Ad spend distribution runs via Cloud Scheduler at 8:30 AM LA time daily. If a day is missing, use the backfill URL above with the specific date range.
Source:eddie_v2_webapp_pnl.tbl_spend_by_classification Webapp: Ad Spend Checker Function: re-ad-spend-web-module-700899291768.us-east1.run.app
Storage
Source:eddie_v2_storage_fees.vw_storage_fees_by_classification Webapp: Storage Hub Request: re-pull-fba-storage-fees-v2-request-700899291768.us-east1.run.app Collect: re-pull-fba-storage-fees-v2-collect-700899291768.us-east1.run.app Scheduler: 2 AM (request) / 8 AM (collect) on 1st, 6th, 11th, 16th, 21st, 26th
!
OTHER
Officialize COGS for each classification
The unofficial cost entries are OK for an estimate but not for permanent P&L reporting. Each classification's COGS needs to be officialized through the FIFO Manager once real invoice costs are available. Unofficial entries show as 🔴 in the COGS Manager v2 coverage view — they should eventually become 🟢 (official) or 🟡 (fallback).
🔮 Reminder: Current month storage projection
Not yet built. The current month needs to project storage based on current month average inventory (inventory data hasn't been brought into the tool yet). The projection would use the most recent storage bill at ASIN level to estimate what the current month's bill will be.
Design considerations:
Problem: In January you don't want to use December's storage fees for projection (peak vs non-peak). In November you don't want to use October's fees (you'd underproject due to peak).
Proposed solution: Allow 3 options for current month projection:
1. Most recent month — use last month's actual ASIN-level rates
2. Rate card — Non-peak — use Amazon's published non-peak rates (Jan-Sep)
3. Rate card — Peak — use Amazon's published peak rates (Oct-Dec)
Prerequisites: Need to bring inventory data into the tool, and store the rate cards in the webapp.
Line Distribution by Settlement
Select a settlement and click Load Distribution to see how each line item is classified.
Line Distribution — Visual
Select a settlement to see the visual distribution matching Amazon's chart layout.
L1 should be the field in the net proceeds section (the left side) of Amazon's payment summary screen that the amount connects to
L2 should be the field on the right side of the Amazon payment summary screen that the amount belongs to
L3 has elements of L1 and L2 but really just needs to be done by looking at how we did other L3 items and follow same logic
The best way to trace back what to do with the ones you want to map is to go to the "Unmatched" section on our webapp and it will show you where the unique identifier was last seen, that will tell you which payment to go back to in order to trace which L1, L2 etc assignments should be made.
You'll want to go to the line distribution visual (under Distributions) and compare the top level numbers to the Amazon payment summary graph in Seller Central, and that will help you figure out where the unique identifiers should be tied to.
Click Refresh to load the distribution map.
Transaction View — Order Story
Shows every settlement row tied to this order — sales, fees, refunds, shipping — across all settlements.
🔍
Enter an Order ID above to see its complete financial story.
Every line item, fee, refund, and adjustment tied to the order — pulled from the anchor view.
Example Orders
Classification P&L — Horizontal View
Select a month or settlement and click Load.
Each row = one classification. Columns = P&L metrics. Click any amount to drill down.
COGS Manager v2
Manage COGS coverage for your P&L. Enter unofficial costs for missing classifications to keep your P&L accurate until official FIFO data arrives.
Status:🟢 Official (exact FIFO)🟡 Fallback (prior month FIFO)🔴 Unofficial (manual estimate)⛔ Missing (no cost — urgent)
