Ads Maintenance Log

This article captures the weekly notes that I keep from managing the advertising campaigns on Amazon. The ultimate goal is to streamline a proper workflow for managing ads, particularly adjusting biddings for the base bids and the premiums at three different placements.

Objective

ACOS = 广告花费 / 广告销售额

     = 平均 CPC x 广告点击数 /（商品单价 x 广告销售数量)

     = 平均 CPC x 广告点击数 /（商品单价 x 广告点击数 x 广告转化率）

     = 平均 CPC /（商品单价 x 广告转化率）

Maximum affordable CPC for Charge Rise II - category

CPC	U/P	ACOS	CVR
0.50	39.99	33.16%	3.77%
0.60	39.99	39.80%	3.77%
0.70	39.99	46.43%	3.77%
0.80	39.99	53.06%	3.77%

Theoretically, given these three constraints, (Unit Price=39.99, CVR=3.77%, and ACOS<=40%), the average CPC of this ads group should never exceed $0.60.

In short, the day to day task is to decide what bids we should offer for each keyword/asin, so that the target CPC<=0.60 (hence the target ACOS<=40%) of each ads campaign is met.

Other Goals

There are other important goals, but they are out of the scope of managing ads and require extra resources:

1. increase click through rate (industry average=?)
2. increase clicks (on average 9K click per month)
3. increase conversion rate (on average between 3%-7%)

Existing Ads

1. Where to look for valid and effective keywords, on a regular basis? a. sif: ?? b. menu → growth → product opportunity explorer: past 360 days c. menu → brand analytics: 2025 q1
   • output: 20 keywords every other week
2. What are the criteria to pick a keyword?: a. relevancy 相关性 b. search volume 搜索量 c. 竞价接近
3. Where to add the new identified keywords for test run? To maintain a track record versus easy to manage?
   • add to the existing ads campaigns?
   • create new ads campaigns?
4. How to structure the ads portfolio properly?
5. How to set different bids for the same keywords? a. clock radio vs. alarm clock radio b. ihome vs. ihome alarm clock
6. What are the upper limits when setting target CPC for each keyword? a. Maximum bidding for sfq-16 b. Maximum bidding for sfq-18 c. Maximum bidding for sfq-20
7. How to manage bids with a properly structured process, using bulksheets? a. download and analyze historical data in Excel b. propose bids to be implemented for the coming week in Excel c. review, cross-check, approve d. update approved bids online (through manual input or bulksheets upload) e. weekly?
8. How to avoid/reduce wasted spends on irrelevant keywords/asins? a. download and analyze search term reports b. spot and propose negative keywords c. bi-weekly? monthly? bi-monthly?
9. sfq-20 ads portfolio
   • charge rise ii - keywords--------------|
   • charge rise ii - keywords 02-----------|> keywords 001/002/003
     • three groups created, 2025-04-27, placement=0/10/20, target cpc=.9/.99/1.08, fixed bidding, phrase match, black and white
     • grouping criteria - similar level of suggested bids
     • grouping criteria - similar CVR
   • charge rise ii - competing brands------|not working, paused 周一 2025-05-12 21:06:30 +0800
   • charge rise ii - category Tue 2025-04-29 10:54:55 +0800, do you still receive impressions & clicks after reducing bids to market price .26/.31/.42?
   • charge rise ii - asin 3: bulksheets upload, test run, Tue 2025-04-29 14:29:29 failed; Wed 2025-04-30 14:06:39 succeeded:
     • Is it proper to use these two ratios (ros/pp and tos/pp) as placement premiums?
     • Does not work, paused 周一 2025-05-12 21:07:22 +0800
     • Goes back to original asin, which not much spends, bad recent acos, but with historical legacy
     • pending bulksheets upload
     • asin bidding should be cheap, ours are too high >0.73
   • will keywords 001/002/003 perform better than original keywords? 周一 2025-05-12 21:11:54 +0800
10. sfq-16 ads portfolio: a. asin - acos=74.35%, so this does not work, paused Wed 2025-04-30 b. do we have any asin campaigns that ever worked? lessons learned? c. is is worthwhile to run any asin targeting, at all? d. if so, what should we do differently this time, as compared to previous ones? e. 出单词；鸡肋词（零出单，低点击，消耗未超客单价），定标准 停止投放，大词活动内亦精准否定。手动建立黑名单 (keywords and asins)。
11. sfq-18 ads portfolio Thu 2025-05-15 12:10:09 +0800 a. soundfreaq b. competing brands c. category 2503
12. questions to ask a. how to come back on "bluetooth speaker" again? (16 orders, acos=88.50%) how to reduce waste this time? b. exact match did not work: 1. bluetooth speaker 2. portable bluetooth speaker c. competing brands appears to be working; others? ue/jawbone/beats bluetooth speaker d. check again on attributes: xxx bluetooth speaker, bluetooth speaker with xxx, ... e. what are the differences between category and category 2503?
13. sfq-16 category 2503: a. added wood taupe 周五 2025-05-23 14:40:00 +0800 b. because there is no significant proof on the ad group of new keywords, between black and wood taupe
14. sfq-18 asin targeting: a. B0D6WD2QSQ B0748N1BZD bose b. B0CXL4FQBK B086DBR6T7 B0B445JCZ3 B0DYBNC4SG sony c. B0DGRBWLJZ B01DKGP5QO B01IOD7KB6 marley d. B0CY6S748H B0CY6TTR3C sonos
15. uk sfq-17 dab keywords, more keywords (new campaigns? add on existing one?) 周二 2025-05-27 11:10:41 +0800
16. uk sfq-20 charge rise ii, take from the US, keywords 001/002/003
17. download search terms report every first week for the previous month, screen and output the negative keywords repository
18. sfq-16 sound rise ii, start a new category campaign, compact radio & stereo
19. 周三 2025-05-28 08:07:09 +0800
20. sfq-18 主图优化的两个设计思路（以显示产品相对尺寸）
    1. 产品旁边加一台 iphone，或 充电线配件 (6ft)
    2. 产品放在手掌中
21. sfq-16 类目试投： a. home audio speakers → bookshelf speakers (probably not suitable?) b. electronics → home audio & theater → compact radios & stereos → stereo shelf systems (similar price level, form factor) c. alarm clocks (mostly cheap clocks, need similar price, similar features) d. sound machines
22. 周四 2025-05-29 07:51:12 +0800
23. review the newly created campaigns for uk sfq-17 and sfq-20
24. find new & update existing negative keywords for sfq-18 camapaigns
25. dig keywords from customer reviews
26. 周五 2025-05-30 07:47:23 +0800
27. Review the category 20250325 Search Terms Report and define the criteria for negating ineffective keywords and ASINs. Keep track of all negative keywords and ASINs, comprehensively and with supporting data.
28. There are 34 negative ASINs on the list. How can we verify that these negations are valid dec bbisions?
29. 周二 2025-06-03 10:34:22 +0800
30. Shall we deactivate the white from keywords 001/002/003?
31. 周五 2025-06-13 15:11:53 +0800 Sound Rise II
32. category 11, remove 34 negative asins, take records for category 07 and category 2503 (bid adjustments)
33. clock radio, wood restart again; competing brands, wood keep running ($30+); category 11,

Final Naming Convention Summary

Format: [ProductCode].[CampaignType].[Target].[OptionalSuffix]

Element	Description	Examples
ProductCode	Product identifier	sfq-16, sfq-18, sfq-20, sfqold
CampaignType	Type of campaign	Keyword, Category, ASIN
Target	What the campaign targets	alarm-clock, clock-radios, 2510
OptionalSuffix	Date code or version (YYMM format)	2411 (Nov 2024), 2602 (Feb 2026)

Your Complete Mapping Table (Finalized)

Current Name	Final Name	Notes
Sound Rise II - alarm clock	sfq-16.Keyword.alarm-clock
Sound Rise II - soundfreaq	sfq-16.Keyword.soundfreaq-brand	"-brand" suffix clarifies it targets brand terms
Sound Rise II - clock radio 241128	sfq-16.Keyword.clock-radio	Date code removed
Sound Rise II - Category 11	sfq-16.Category.clock-radios.2411	Nov 2024 version
Soundfreaq - Portable speakers	sfqold.Keyword.portable-speakers	Legacy product
Sound Rise II - competing brands 2	sfq-16.Keyword.competitor-brands.2509
SFQ-18 Soundfreaq	sfq-18.Keyword.soundfreaq-brand
Sound Rise II - ASIN 2510	sfq-16.ASIN.2510
Sound Rise II - Category 2602	sfq-16.Category.clock-radios.2602	Feb 2026 version
Charge Rise II - Core keywords	sfq-20.Keyword.core

Useful Excel Formulas for Parsing These Names

Once your Raw_Campaign_Data table has these standardized names, you can use these formulas to extract components:

To Extract	Formula (assuming name in cell A2)
Product Code	=TEXTBEFORE(A2, ".")
Campaign Type	=INDEX(TEXTSPLIT(A2, "."), 2)
Target	=INDEX(TEXTSPLIT(A2, "."), 3)
Suffix (if exists)	=INDEX(TEXTSPLIT(A2, "."), 4)

Example: For sfq-16.Category.clock-radios.2602:

• Product Code = sfq-16
• Campaign Type = Category
• Target = clock-radios
• Suffix = 2602

Next Step

Now that the naming convention is finalized, shall I proceed to design the Raw_Campaign_Data table structure and the parsing formulas for your analysis sheet?

The next deliverable would include:

1. Exact column headers for Raw_Campaign_Data
2. How to handle the two files you downloaded (one filtered, one unfiltered)
3. Formulas to automatically populate Product Code, Campaign Type, Target, and Suffix from the campaign name
4. Template for period-over-period calculations

Now, let's proceed to the next step: collect data from the downloaded report at ~/Downloads folder, e.g. Campaign_Apr_21_2026.csv, which is the source file. While the master report (destination file) is stored inside ~/OneDrive/Documents/AdsReport_202604211403.xlsx. Since we are dealing with Excel files, I'd like to manipulate the files with a Python script. Here are desired actions of what the Python script should do:

1. look for the source file, read the file name to understand the report type (campaign, bids replacements, targeting, and so on...), read file content for copy & paste
2. open the destination file, look for the correct sheet (Raw_Campaign_Data if a campaign report, Raw_Targeting_Daat if a targeting report, and so on) to append copied data
3. append data into the selected worksheet, and do some custom editing, e.g. insert the Snapshot Date in front of the pasted data.
4. print success/fail message after the operation
5. if pasted success, then delete the source file(s) from the ~/Downloads folder Advise if you have any other suggestions for a more robust Python script designed for this purpose.

Excellent idea to automate this with Python! This will save you significant manual effort and eliminate copy-paste errors. Let me design a robust solution for you.

Complete Python Script for Automated Data Ingestion

Additional Recommendations for a More Robust Script

1. Add Logging Instead of Print Statements

2. Add Data Validation Checks

3. Add Backup Before Writing

4. Handle Duplicate Snapshots

5. Configuration File (YAML or JSON)

Create a config.yaml file to avoid hardcoding paths:

# config.yaml
paths:
  downloads: "~/Downloads"
  destination: "~/OneDrive/Documents/AdsReport_202604211403.xlsx"
  backup_folder: "~/OneDrive/Documents/Backups"

report_types:
  - pattern: "campaign"
    sheet: "Raw_Campaign_Data"
  - pattern: "targeting"
    sheet: "Raw_Targeting_Data"
  - pattern: "bid"
    sheet: "Raw_Bid_Adjustments"

delete_after_ingest: true
create_backup: true

6. Schedule the Script (Windows Task Scheduler / cron)

To run automatically every day:

Windows (Task Scheduler):

C:\Python39\python.exe C:\scripts\ingest_ads_reports.py

macOS/Linux (crontab):

# Run at 9 AM every day
0 9 * * * /usr/bin/python3 /home/user/scripts/ingest_ads_reports.py

Folder Structure After Implementation

~/Downloads/
├── Campaign_Apr_21_2026.csv          # Will be deleted after processing
├── Targeting_Apr_21_2026.csv         # Will be deleted after processing
└── (other downloaded reports)

~/OneDrive/Documents/
├── AdsReport_202604211403.xlsx       # Master file (updated)
├── AdsReport_202604211403.xlsx.bak   # Automatic backup
└── ads_ingestor.log                  # Processing log

Usage Instructions

1. Save the script as ingest_ads_reports.py

2. Install dependencies:

   pip install pandas openpyxl

3. Run the script:

   python ingest_ads_reports.py

4. Optional: Specify custom paths

   python ingest_ads_reports.py "C:\MyReports\master.xlsx" "C:\Users\Name\Downloads"

What the Script Does (Step by Step)

Step	Action
1	Scans ~/Downloads for Campaign_*.csv files
2	Detects report type from filename (campaign, targeting, etc.)
3	Reads CSV and cleans data (removes $, ,, % symbols)
4	Standardizes campaign names to dot notation (e.g., sfq-16.Keyword.alarm-clock)
5	Adds Snapshot Date column with today's date
6	Opens master Excel file and appends to the correct sheet
7	Creates a backup before making changes
8	Deletes source CSV file after successful import
9	Logs all actions and prints a summary

Would you like me to add any specific features, such as:

• Email notifications after successful import?
• Automatic detection of new files via file watcher?
• Handling of additional report types (search terms, negative keywords, etc.)?
• Data quality dashboards (e.g., flagged anomalies in spend or ACOS)?

This is an excellent idea. A structured log file is essential for maintaining accountability, tracking performance changes, and enabling data-driven decisions. Here is a comprehensive template you can implement directly in Excel.

---

Campaign Operations Log – Template

Worksheet Structure

I recommend creating a dedicated worksheet named Ops Log with the following columns.

Column	Header	Description	Example
A	Date	Date of the operation (YYYY-MM-DD)	2026-04-23
B	Time	Time of the operation (HH:MM)	14:30
C	Campaign	Campaign name	Sound Rise II - alarm clock
D	Ad Group	Ad group name (if applicable)	Keyword targeting
E	Operation Type	Category of change	Bid Adjustment
F	Target Type	Keyword / Placement / Product / Campaign Setting	Keyword
G	Target Name	Specific item being modified	radio alarm clock
H	Match Type	Phrase / Exact / Broad / N/A	Phrase
I	From (Value)	Previous value	0.80
J	To (Value)	New value	0.70
K	Unit	Currency / Percentage / Status / Text	USD
L	Reason	Why this change was made	ACOS 41.5%, reduce bid
M	Expected Impact	What you anticipate will happen	Lower ACOS to ~38%
N	Initiated By	Name or initials of person making change	JY
O	Approved By	Name of approver (if applicable)	MK
P	Status	Pending / Completed / Reverted	Completed
Q	Review Date	When to check results	2026-04-30
R	Actual Impact	Post-change results (filled later)	ACOS improved to 37.2%
S	Notes	Any additional context	Reduced by $0.10 after 2 weeks of high ACOS

---

Operation Type Dropdown Menu

To ensure consistency, create a dropdown list in column E with these options:

Category	Operation Types
Bid Adjustments	Bid Increase, Bid Decrease, Bid Reset
Placement Adjustments	Top of Search Change, Rest of Search Change, Product Page Change
Targeting	Keyword Added, Keyword Paused, Keyword Resumed, Keyword Removed, Match Type Changed
Negative Targeting	Negative Keyword Added, Negative Keyword Removed, Negative Product Added
Product Targeting	ASIN Target Added, ASIN Target Removed, ASIN Target Bid Changed
Campaign Settings	Budget Changed, Status Changed, Bidding Strategy Changed, Start/End Date Changed
Ad Group Settings	Ad Group Added, Ad Group Paused, Default Bid Changed
Bulk Operations	Bulk Import, Bulk Export, Bulk Update

---

Sample Log Entries

Here are 10 realistic examples to populate your template:

Date	Time	Campaign	Ad Group	Operation Type	Target Type	Target Name	Match Type	From	To	Unit	Reason	Expected Impact	Initiated By	Status
2026-04-23	09:15	Sound Rise II - alarm clock	Keyword targeting	Bid Decrease	Keyword	radio alarm clock	Phrase	0.80	0.70	USD	ACOS 41.5% for 30 days	Lower ACOS to 38%	JY	Completed
2026-04-23	09:20	Sound Rise II - alarm clock	Keyword targeting	Bid Decrease	Keyword	bluetooth alarm clock	Phrase	0.70	0.50	USD	ACOS 52.6%, inefficient	Reduce waste, target 45% ACOS	JY	Completed
2026-04-22	14:00	Sound Rise II - clock radio 241128	Keyword targeting	Bid Increase	Keyword	clock radio	Phrase	0.50	0.60	USD	Test higher Top of Search capture	Increase orders from 153 to 180	JY	Pending
2026-04-22	14:05	Sound Rise II - alarm clock	Placement	Top of Search Change	Placement	Top of Search	N/A	70%	60%	Percentage	Reduce spend after base bid increases	Maintain effective bid ~$1.20	JY	Completed
2026-04-21	11:30	Sound Rise II - alarm clock	Negative keywords	Negative Keyword Added	Keyword	projection	Phrase	N/A	N/A	N/A	Search term report shows irrelevant traffic	Reduce wasted spend	MK	Completed
2026-04-20	16:00	Sound Rise II - category 11	Product targeting	ASIN Target Added	Product	B0787W458R	N/A	N/A	0.40	USD	Search term report shows 6 orders	Capture competitor traffic	JY	Completed
2026-04-19	10:00	Sound Rise II - alarm clock	Keyword targeting	Keyword Paused	Keyword	alarm clock speaker	Phrase	Active	Paused	Status	ACOS 61.6%, only 6 orders	Reallocate budget to winners	MK	Completed
2026-04-18	09:00	Sound Rise II - competing brands 2	N/A	Status Changed	Campaign	Sound Rise II - competing brands 2	N/A	Enabled	Paused	Status	Zero orders for 14 days, ACOS rising	Stop losses, investigate later	JY	Completed
2026-04-17	15:30	Sound Rise II - alarm clock	Keyword targeting	Match Type Changed	Keyword	clock radio	Broad	Phrase	Match Type	Search term report shows irrelevant matches	Improve relevance, lower ACOS	MK	Completed
2026-04-16	13:00	Sound Rise II - clock radio 241128	N/A	Budget Changed	Campaign	Sound Rise II - clock radio 241128	N/A	30	25	USD	Under-spending, reallocate to alarm clock	Optimize budget allocation	JY	Completed

---

Additional Recommended Columns (Optional)

Depending on your team's needs, you may want to add:

Column	Header	Description
T	Ticket/Reference #	Internal tracking number (e.g., JIRA, Asana)
U	Related Change #	Link to related log entry (e.g., reverting a change)
V	Test Group	A/B test identifier (e.g., "Test A - Bid Increase")
W	Automated?	Yes/No – was this change made by a script/rule?
X	Reverted On	Date when change was reversed (if applicable)

---

Pivot Table for Analysis

Once you have 20-30 entries, create a pivot table to analyze:

Row Label	Values
Operation Type	Count of changes
Campaign	Sum of bid changes
Initiated By	Count by person
Status	% Completed vs Pending

Pivot Table Setup:

• Rows: Operation Type, Campaign
• Values: Count of Date
• Filters: Status (Completed only), Date range

This will show you:

• Which campaigns receive the most changes
• What types of changes are most common
• Who is making the most adjustments
• How many changes are still pending review

---

Conditional Formatting Suggestions

Apply these rules to make the log more readable:

Column	Condition	Format
Operation Type = "Bid Increase"	Cell	Green fill
Operation Type = "Bid Decrease"	Cell	Red fill
Status = "Pending"	Row	Yellow fill
Status = "Reverted"	Row	Gray fill
To > From (for bids)	Cell	Light green
To < From (for bids)	Cell	Light red

---

How to Use This Template

1. Create a new worksheet named Ops Log in your Excel file
2. Copy the column headers from the template above
3. Set up data validation for columns E, F, H, P using dropdown lists
4. Log every change as you make it (not at the end of the week)
5. Schedule a weekly review to update "Actual Impact" and close out completed items
6. Share with your team so everyone logs changes consistently

---

Sample Excel Formula for Automated Columns

Column	Formula	Purpose
B (Time)	=IF(A2<>"", NOW(), "") with copy-paste values after entry	Auto-fill current time
Q (Review Date)	=A2+7	Auto-calculate 7-day review window
R (Actual Impact)	Manual entry after review	Track results

---

This template will give you full visibility into campaign management activities and enable root cause analysis when performance changes unexpectedly. Would you like me to provide this as an actual Excel file structure or add any other columns?