Bulk File Basics: How to Optimize Sponsored Product Ads Using Bulk Files
First thing’s first… what are bulk files?
Think of them as your entire ad account in spreadsheet form, because that’s exactly what they are! But did you know you can use bulk files to make changes to your PPC campaigns? Virtually anything you do in the campaign manager can be done through bulk files.
So how is this useful? Well, in the campaign manager if you want to see what keywords you’re using in a campaign you’d have to click on the Campaign, then the Ad group, then go to the Targeting level. Multiply that across 10’s or 100’s of campaigns and things can quickly grow tedious. With a bulk file, you can quickly look at all keywords and ASIN’s you’re bidding on across your entire advertising account.
Bulk files have virtually endless applications. Today, I’m going to be breaking down one of their uses, step-by-step. After going through these steps you’ll be able to adjust bids on ALL your Sponsored Product ads with a single spreadsheet upload!
Step 1 – Requesting a Bulk File
Where to find them:
Within your Amazon Advertising Campaign Manager portal (in Seller Central) you will notice, in the left-hand menu, a section called “Bulk File.” This is where you can request, download, and upload your bulk files.
Downloading a Bulk File:
1. First, select the date range you would like to view. You can either pick a custom date or quickly choose a preset of 30 days, 14 days, etc.
2. Uncheck the box beside “campaign items with zero impressions”
3. Click the button “Create Spreadsheet for Download”
4. You should then see a file under the downloads section that shows your bulk file request is in progress. When it’s available, a blue download link will appear. Click this link and the bulk file will download to your computer.
Opening the Bulk File
Open your file using spreadsheet software such as Microsoft Excel or Google Sheets.
Once you open the file you will see four tabs. You’ll want to click on the tab labeled “Sponsored Products Campaigns”
After you open the Sponsored Product Campaigns tab, take a look at the columns listed below. These are the columns we will need to filter and analyze.
- Column B: Record Type (tells you what level of data you’re looking at)
- Column D: Campaign (or campaign name)
- Column J: Ad Group (or ad group name)
- Column K: Max Bid (this is your keyword bid and the number we will be changing)
- Column L: Keyword or Product Targeting
- Column N: Match Type
- Columns P through R are our status columns
- Columns S through Y are the performance numbers – similar to what you would find inside campaign manager
Note: There are two tricky formatting issues that you’ll need to tackle before we can start filtering. We’ll tackle the first one now. We’ll cover the other issue later.
Formatting Issue #1
The numbers in the bulk file are stored as text. You can tell if a spreadsheet value is stored as text if it aligns to the left.
The numbers being stored as text will create some problems later when you sort the columns, so you’ll need to fix this. There are two methods depending on which spreadsheet program you’re using. I’ll cover how to change the format in both Google Sheets and Excel.
1. Highlight Columns S through Y
2. Click on the toolbar, then Format > Number > Number
3. Now Highlight Column K and do the same thing!
1. You’ll need to click into a cell in column S and drag your mouse (or use hotkeys) to select all of the cells in columns S through Y (Don’t select the header in Row 1. Just select the numbers.)
2. You should see the little yellow caution sign appear. Click the small dropdown arrow next to it and select “Convert to Number”
3. You’ll need to use the same method to highlight all of the numbers in column K and convert them to numbers as well.
Filter the Bulk File
Now that you know the important columns and you fixed the formatting issues, it’s time to apply filters to isolate the keywords.
1. Filter Column B (record type) to only show “Keyword” and “Product Targeting”
2. Filter Columns P, Q, and R to only show “enabled”
3. Filter Column N (match type) to NOT show anything with “negative” in the cell. (This is because we don’t bid on negative keywords and so we don’t want to include them in our bid adjustments)
Congratulations! You now have a list of all of your active sponsored product targets and their performance numbers.
Make Changes to Bulk File
Now that we’ve filtered everything, you can analyze columns Q through Y to gauge performance. Each row in your filtered sheet represents a target you’re bidding on. You can look at the cells on the row to see how they are performing and then make changes to the cells in Column K, which is our bid.
In the below example, you can see the ACoS in Column Y. See that row with an 82.41% ACoS? You might want to lower that bid. The current bid is 0.97. You can change the number to whatever you want the new bid to be.
You can sort the file by ACoS or any other metric depending on what kind of optimization you are doing. More on that below.
After you make all the bid changes needed in column K you can upload the file and those bids will be changed in the campaign manager.
Formatting Issue #2 – Blank Cells in Column K
You might come across some blank cells in column K. This happens when the bid hasn’t been changed and the bid is the same as the ad group default bid.
You’ll need to change the filter in Column B to show Ad Group, Keyword, and Product Targeting.
Then check for a row in your spreadsheet with the same campaign’s name and ad group name as the rows with the blank cells and “Ad Group” in Column B. The ad group row will contain the value you need to enter in the blank bid cell.
In the below example, notice the row that shows “Ad Group” in column B says 1.00. That is the bid for the two blank keyword rows. So we can enter $1.00 as the bid for the second and fourth row.
After you fix the missing bid issue you’ll want to make sure you filter Column B to just show Keyword and Product Targeting again.
Now you know how to filter your bulk file to view all the keywords/targets and the bids!
But we’re not done.
Filtering Your Bulk File
First, you can sort ACoS, or Column Y, from highest to lowest. This will bring to the top all of your high ACoS keywords.
Another helpful thing to look at is clicks with no sale keywords. To find the top clicks with no sale keywords, filter Column X (Sales) to only show $0 and then sort by column T (Clicks) highest to lowest.
You can also filter Columns D and J to only show keywords in a particular campaign or ad group.
After you’ve adjusted the bids make sure you save the changes!
The very last step is to upload the file. To upload the build file, follow these four steps.
1. Go back to the bulk file section in the campaign manager
2. Click Choose File under “Step 3”
3. Select your saved file that has the changes you want, then click “Open”
4. Click “Upload to Process Changes”
Just like when we downloaded the file, your upload will appear below and show as “processing.” Once it’s finished you should see something like this…
Congrats! You just adjusted all of your chosen bids using a bulk file! Wasn’t that a lot quicker than changing bids one at a time?
One final thing. A best practice is to go and double-check a few bids to make sure they’ve been changed afterward. It can take a moment for the bids to update, so give it a few minutes before checking.
Happy bulk optimizing!