Objective: Create a dedicated Google Sheet dashboard per product, centralizing performance and market data in a structured and scalable way. Each product will have its own file with multiple tabs, each focusing on different data aspects.
Data Sources: Amazon (Seller Central + Ads API) and Keepa, with some fields entered manually when needed.
File Structure: One Google Sheet Per Product - Each file represents a single ASIN and includes 5 tabs.
Tab 1: Market_Tracking
Purpose
Compare my product to selected competitors on key market indicators
Table Structure
A
B
C
D
E
F
G
H
I
J
K
ASIN
Product Title
Last Updated
BSR
BSR Trend
Price (30d Avg)
Current Price
Shipping Time
Reviews Count
Rating
Notes
[MANUAL INPUT]
[AUTO FROM API]
27/06/25 10:30
2,450
↑ +15%
$31.50
$29.99
1-2 days
1,247
4.6
MY PRODUCT
[MANUAL INPUT]
[AUTO FROM API]
27/06/25 10:30
1,890
↓ -5%
$33.20
$32.99
2-3 days
2,103
4.4
TOP COMPETITOR
[MANUAL INPUT]
[AUTO FROM API]
27/06/25 10:30
3,200
→ 0%
$28.75
$27.99
3-5 days
856
4.2
PRICE COMPETITOR
Daily Historical Data with Date Separation
Date Separation Method:
Bold header row for each new date with gray background (#f0f0f0)
Alternating row colors within each date: white and light blue (#f8f9ff)
Empty row between different dates for clear visual separation
Column A (ASIN): Manual input field - user enters ASINs to track
First ASIN is always the user's product, following ASINs are competitors
Price comparison feature: Show difference between 30-day average and current price
All data except ASIN input should auto-populate via Keepa API
Daily script adds new data below existing data with proper date separation
Tab 2: Inventory_Tracking
Purpose
Monitor inventory levels and forecast reorder needs
Update Frequency
Automatic: Once per week at fixed time
Manual: On-demand update button available
Table Structure
A
B
C
D
E
F
G
H
I
J
K
ASIN
SKU
FBA Available
Reserved
Inbound
Produced (Manual)
Total Inventory
Sales 30d
Sales 60d
Low Stock Alert
Days Remaining
B08XXX1234
SKU-001-RED
450
35
200
100
785
120
240
✅ Safe
19.6
B08XXX5678
SKU-002-BLUE
89
12
0
0
101
95
180
⚠️ LOW STOCK
31.9
B08XXX3456
SKU-004-BLACK
45
8
0
0
53
78
155
🔴 CRITICAL
20.4
Automatic Calculations:
• Total Inventory = FBA Available + Inbound + Produced (Manual) - Reserved
• Days Remaining = Total Inventory ÷ (Sales 30d ÷ 30)
• Low Stock Alert Logic:
🔴 CRITICAL: Days Remaining < 30
⚠️ LOW STOCK: Days Remaining 30-45
✅ Safe: Days Remaining > 45
Tab 3: Daily_KPI_Dashboard
Purpose
Central dashboard of daily performance metrics at Parent ASIN level
Table Structure
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
Date
Parent ASIN
CVR
Impressions
Clicks
CTR
Spend
Orders
PPC Sales
ACOS
ROAS
Sessions
Unit Session %
Organic Units
Organic Ratio
Total Units
Total Sales
Refund Cost
Net Profit
TACoS
27/06/25
B08XXX1234
12.5%
3,456
89
2.57%
$45.67
15
$189.34
24.12%
4.15
234
6.41%
18
54.5%
33
$457.23
[MANUAL]
[MANUAL]
9.99%
Automatic Calculations:
• CVR = Orders / Clicks × 100 • CTR = Clicks / Impressions × 100
• ACOS = Spend / PPC Sales × 100 • ROAS = PPC Sales / Spend
• Unit Session % = Total Units / Sessions × 100
• Organic Ratio = Organic Units / Total Units × 100
• TACoS = Spend / Total Sales × 100
Manual Input Columns: Refund Cost (R) and Net Profit (S) are empty - require manual entry
Tab 4: Weekly_KPI_Summary
Purpose
Weekly KPI overview for all Parent ASINs - runs every Sunday
Table Structure
Week Ending
Parent ASIN
Total Impressions
Total Clicks
CTR (%)
Total Spend
Total Orders
PPC Sales
ACOS (%)
ROAS
Total Sessions
Unit Session %
Organic Units
Organic Ratio
Total Units
Total Sales
Refund Cost
TACoS (%)
23/06/25
B08XXX1234
24,192
623
2.57%
$319.69
105
$1,325.38
24.12%
4.15
1,638
6.41%
126
54.5%
231
$3,200.61
[MANUAL]
9.99%
Tab 5: SQP_Weekly_Report
Purpose
Search Query Performance (SQP) data from Amazon reports - exact replication of Amazon SQP report with weekly automation
Table Structure
Search Query
Child ASIN
Impressions
Clicks
CTR
Orders
Sales
ASIN Impression Share
ASIN Click Share
ASIN Purchase Share
[Other SQP Columns]
Impression Share Δ
Click Share Δ
Purchase Share Δ
wireless earbuds
B08XXX1234-RED
1,234
67
5.43%
8
$156.78
15.2%
12.8%
18.3%
[AUTO FROM SQP]
↑ +1.5%
↑ +0.8%
↓ -0.5%
bluetooth headphones
B08XXX1234-BLUE
987
45
4.56%
6
$124.50
13.7%
11.2%
16.9%
[AUTO FROM SQP]
↓ -0.3%
→ 0.0%
↑ +1.2%
Report Structure:
Column A: Search Query (each search term gets its own row)
Columns B-M: All existing Amazon SQP report fields - pulled exactly as displayed in Amazon
Last 3 Columns: Growth indicators for market share metrics
Technical Specification for Integration
Data Sources & API Integration
Data Point Category
Source
Update Frequency
Technical Notes
BSR, Price, Reviews
Keepa API
4 times daily
endpoints: /product, /query
Ad Metrics
Amazon Ads API
Daily morning
Sponsored Products reports
Sales & Sessions
Amazon SP-API
Daily afternoon
Business Reports API
Inventory
SP-API or Manual
Daily
FBA Inventory reports
Refund & Profit
Manual Entry
Weekly
Cannot be automated
Google Sheets Functions Required
=IMPORTDATA("https://api-endpoint.com/keepa-data")
=QUERY(Daily_KPI!A:T, "SELECT AVG(T) WHERE A >= date '"&TEXT(TODAY()-30,"yyyy-mm-dd")&"'")
=SPARKLINE(TACoS_Daily!F2:F31, {"charttype","line";"color1","red"})
Conditional Formatting Rules
1. Inventory Days: <30 Red, 30-45 Yellow, >45 Green 2. TACoS Performance: >15% Red, 10-15% Yellow, <10% Green 3. BSR Trend: Improvement Green ↑, Decline Red ↓, No change Yellow →
Implementation Instructions for Developer
Phase 1: Basic Structure
Create template with 5 tabs
Set up headers and structure for each tab
Add basic formulas
Phase 2: API Connections
Google Apps Script integration
Keepa API setup with API key
Amazon SP-API authentication
Amazon Ads API setup
Phase 3: Automation
Time-based triggers for data updates
Error handling and logging
Data validation checks
Phase 4: UI/UX
Conditional formatting
Charts and visualizations
Data validation
Formula protection
Final Deliverables
✅ Working Google Sheets file with all tabs
✅ Google Apps Script for API integration
✅ Documentation for setup and maintenance
✅ Template for replication to additional products
Important Notes
Dashboard should be clear and not overly complex in first version