I decided to create this post to help people manage their bulk files. Hopefully you will learn and apply some of these little tips and tricks.
Renaming Bulk Files at Scale:
* Example is for Mac users, though there is likely a PC version of this.
Scenario: You have dozens of bulk files sitting in your downloads folder on your computer. Well this trick should help you rename all your bulk files quickly. When dealing with multiple markets or clients this will allow you to quickly rename these files to neatly organize them afterwords.
When you download a bulk file the name will begin with a country code for the market followed by your unique ID.
Ex: us-abcd123abd34-bulksheet-123456789
This country code and ID is what we will use to quickly change our titles.
Step 1: Do a search in your downloads folder for this unique ID.
Step 2: Highlight all the files you want to rename.
Step 3: Right click the files and select "Rename all files"
Step 4: In the find box enter the country code and unique ID portion of the title. In the "Replace with" box enter what you want to rename the file as. For example you can use the Account or Brand name and the country for the market.
Step 5: Hit Rename and see all your files instantly update!
Using Greater Than / Less Than Filters in Bulk Files:
Scenario: You have downloaded a bulk file and you want to filter campaigns, ad groups or keywords to see ones performing at an ACoS greater than 35%. However when you go to find a greater than filter, the option is not available. What to do now?
Step 1: Highlight all the data for number related fields you want to filter. Make sure to highlight below the headers.
Step 2: Once its highlight you will see a yellow warning sign. Select the drop down and select "Convert To Number".
Step 3: Now go back to the filter and you should now see the greater than or less than filter options available.
Find Max Bids for Keywords With a Blank Under the Max Bid Field:
*Warning: This will only work if all your ad groups for manual campaigns have unique names.*
Scenario: You want to update bids on a keyword but you don't know what the current bid is because the Max bid field is empty.
Step 1: Isolate your ad groups by filtering the "Record Type" column for only ad groups. Copy and paste the results into a new tab in the excel workbook. I'll refer to this as the "Ad Group" tab.
Step 2: Find the keywords you want to figure out the current Max Bid for. These would be keywords with a blank in the Max Bids field. Once you have located these keywords copy and paste the data into it's own tab in the excel workbook. I'll refer to this as the "Keywords Tab".
Step 3: Run a vlookup to pull the max bids. Starting on the max bid field under your "Keywords Tab" start by entering the following formula:
=vlookup(I2,
Step 4: Now once you have typed the above portion click on your "ad group" tab and select all the data in the Ad group column and Max Bid column. You can do this by simply clicking at the top of the column and dragging your cursor over to cover columns "I" and "J".
Step 5: Once selected enter a comma followed by the number 2 followed by another comma and followed by "false". The result of your formula should look something like this:
Step 6: After hitting enter your Max Bid should now show up in this field. Copy and paste the formula down the rest of the empty fields to pull the max bids for the remaining keywords on your list.
Step 7: Pat yourself on the back, you now have all your current max bids for these keywords!
I hope these little tips and tricks help you stay organized and help you to better analyze your data.
Enjoy!
댓글