Amazon Advertising Campaign - Python and Bulk Operations

This article shows you how to leverage the programming language Python and the Pandas package to create the necessary spreadsheets you need to work with Amazon's bulk operations.

What are Amazon advertising bulk operations?
What is the perfect campaign structure?
How to create a new bulk sheet with Python?
Summary

What are Amazon advertising bulk operations?

Amazon advertising bulk operations (we call them bulk sheets) are Excel-Sheets that contain your complete campaign structure, including the relevant performance metrics.

You can either create new campaigns or update existing ones with bulk sheets.

You can find the page where you can download and upload bulk sheets here:

Amazon bulk sheets in Amazon's advertising console

Bulk sheets are especially handy if you want to, e.g., mass-create or mass-update many campaigns without doing the same operation one by one for each campaign.

Let's assume you want to increase the budget for each of your 400 Sponsored Products campaigns with an ACoS < 20 % by 10%. While you can do this easily in Amazon's advertising console, using Amazon's bulk sheets would be smarter.

It would work like this:

You download the latest bulk sheet from the advertising console
You open the file in, e.g., Microsoft Excel, and go to the "Sponsored Products" worksheet
You filter the sheet for the campaigns you are interested in
You change the budget column, e.g., using a temporary column
You save the file and re-upload it to Amazon

However, today we don't want to look at updating existing campaigns but creating new campaigns for your products.

What is the perfect campaign structure?

Our agency REVOIC typically creates very specific campaigns for single products or product families.

We, e.g., typically create the following manual Sponsored Product campaigns:

1 campaign with generic keyword targets (generic means that the keywords do not contain any brand name)
1 campaign with offensive keyword targets (offensive means that we target competitors' brands)
1 campaign with offensive product targets (i.e., targeting competitors' products)
1 campaign with defensive keyword targets (defensive means we target keywords containing our brand or product names)
1 campaign with defensive product targets (i.e., targeting our products)

We do this because offensive, generic, and defensive campaigns typically show very different performance metrics, e.g., it is much "cheaper" to bid on your brand than bidding on competitors' brands. Generic campaigns are typically less "expensive" than "offensive" campaigns but, on the other hand, more expensive than "defensive" campaigns. This means you need to have different performance targets for each type. If ACoS is your main goal, you will need higher ACoS targets for offensive campaigns compared to your defensive campaigns.

If you put all targets into one campaign, you would have a mixed bag. Sure, you can manage this campaign on, e.g., target level, but how would you allocate budget then? How would you allow, e.g., higher budgets for your defensive campaigns than your offensive campaigns? This would not be possible. This is another reason why we split campaigns.

However, creating those five campaigns for a single product/product family requires some work. And this is where we automate things using Python.

How to create a new bulk sheet with Python?

To create a bulk sheet from scratch which creates meaningful campaigns for a given product, we need some information first:

Which ASINs or SKUs should be advertised?
What are your own and the competitors' brands?
What are the keywords you want to target?
What are the products you want to target?

You need to organize this information, e.g., in a different Excel-Sheet or Google Docs. We are organizing this information in separate text files. We have other scripts that create these text files more or less automagically, e.g., from existing (but unorganized) campaigns, but we'll need to discuss this in another post.

Product and keyword data are stored in different text files

We create these files for each ASIN, SKU, or group of ASINs/SKUs.

Our script will then iterate through each folder and create those five campaigns for each product (group). So the only thing you need to do is populate these files, run the script, upload the bulk sheet, and you are good to go.

Here are the main components of the script. It would take too much place to show every code line, so we'll focus on the most important things.

Setting up some variables

As we'll create a new bulk sheet, we need to understand the structure of the bulk sheet with its different entities. You have to define different values for a campaign compared to an ad group or keyword target.

E.g., to create a new campaign, you need to fill the following fields (you find those if you download, e.g., a populated bulk sheet):

emptySpBulksheet = {
    "Product": "Sponsored Products",
    "Entity": "",
    "Operation": "Create",
    "Campaign Id": "",
    "Ad Group Id": "",
    "Portfolio Id": "",
    "Ad Id (Read only)": "",
    "Keyword Id (Read only)": "",
    "Product Targeting Id (Read only)": "",
    "Campaign Name": "",
    "Ad Group Name": "",
    "Start Date": "",
    "End Date": "",
    "Targeting Type": "",
    "State": "",
    "Daily Budget": "",
    "SKU": "",
    "ASIN": "",
    "Ad Group Default Bid": "",
    "Bid": "",
    "Keyword Text": "",
    "Match Type": "",
    "Bidding Strategy": "",
    "Placement": "",
    "Percentage": "",
    "Product Targeting Expression": ""
}

These are the columns that need to be filled. Some columns belong to special entities, e.g., the Daily Budget column refers to the campaign entity only.

We do the same, e.g., for Sponsored Brands and Sponsored Display campaigns.

We then define some variables which define the behavior of our script. Here are some examples:

# Campaign
createSPCampaigns = True
campaignStatus = "Enabled" # Enter “Enabled”, “Paused”, or “Archived”
campaignDailyBudget = 20 # EUR or USD
campaignBiddingStrategy = 'Dynamic bids - down only' # Enter "Dynamic bids - down only", "Dynamic bids - up and down", or "Fixed bid".
campaignStartDate = time.strftime("%Y%m%d") # Today as default, e.g. "20220529"
campaignEndDate = "" # “yyyymmdd”, can be empty

# Adgroup (https://advertising.amazon.com/API/docs/en-us/bulk sheets/sp/sp-entities/sp-entity-ad-group)
adgroupMaxBix = 1.50 
adgroupStatus = "Enabled" # Enter “Enabled”, “Paused”, or “Archived”

# Keyword
keywordDefaultMatchType = 'broad'
negativeKeywordDefaultMatchType = 'negativeExact' # negativePhrase or negativeExact

# Toggles: Here you can define which type of targets to create (true) or not (false)
addKeywordsGeneric   = True
addKeywordsDefensive = True
addKeywordsOffensive = False
addProductsOffensive = False
addProductsDefensive = False

We do the same for the other ad types (SB, SD).

Additional information for external sheets

Sometimes we also use an Excel-Sheet with some additional information. We do this for our employees. It is sometimes easier to work with Excel compared to text files.

Let's assume we have a separate sheet where the filename is stored in the variable mainInputFile with two sheets:

Our brands and product names
Competitors' brands and product names

To get these brands into our python script we would do the following:

# Read additional data from main input file (Google Doc -> Excel Sheet)
xls = pd.ExcelFile(mainInputFile)
dfB = pd.read_excel(xls, engine="openpyxl", sheet_name="Our brands")
dfC = pd.read_excel(xls, engine="openpyxl", sheet_name="Competitor brands")

ownBrands        = dfB['Brand'].unique().tolist()
competitorBrands = dfC['Brand'].unique().tolist()

# Make sure brands are lowercase
ownBrands = list(map(str.lower, ownBrands))
competitorBrands = list(map(str.lower, competitorBrands))

We also do some data massage and convert everything to lowercase which makes the comparison later easier.

You could also hard-code this into the script, but this approach makes it more flexible, which is handy if you work with multiple clients as we do it.

Methods to create campaigns, ad groups, etc.

In the next step, we need to define some functions/helper methods that allow us to build up the bulk sheet.

Here is an example of creating structured campaign names which also reflect the goal of the campaign:

def getCampaignName(targeting = "Manual", campaignType = "SP", type1="keyword", type2 = "generic", groupName = "default" ):
    
    prefix = "RE-" + campaignType + "-"
    
    # targeting
    if targeting == "Manual":
        targetingPrefix = "MANU-"
    else:
        targetingPrefix = "AUTO-"
        
    # type1
    if type1 == "keyword":
        type1Prefix = "KW-"
    else:
        type1Prefix = "PT-"
    
    # type 2
    if type2 == "generic":
        type2Prefix = "GEN-"
    elif type2 == "offensive":
        type2Prefix = "OFF-"
    else:
        type2Prefix = "DEF-"
    
    groupName = groupName.strip()
    groupName = groupName[:30].upper()
    
    campaignName = prefix + targetingPrefix + type1Prefix + type2Prefix + groupName + '-' + randomString
    
    return campaignName

As you can see, our campaign names start with a prefix so we can easily identify our campaigns. We then add the following strings:

"MANU" or "AUTO" if it is a manual or auto campaign (only for Sponsored Product campaigns)
"KW" or "PT" if it is about keyword or product targeting
"GEN", "OFF", or "DEF" for the type of targets are used (generic, offensive, or defensive)
The name of the product (group)
A random string that changes each time we run the script

The random string makes it easy to, e.g., filter for those campaigns created in a single run. If you made a mistake, you can easily find those campaigns and, e.g., archive them and start over. Don't leave this away. You will thank me later.

Then we create the different methods to create an entity, e.g., here is a method to fill the relevant fields needed to create a campaign:

def createSpCampaign(targeting = "Manual", type1="none", type2 = "none", groupName = "none", customerPath = "none", counter = 1, fileName = "" ):

    global bulkSheetSp
    
    campaignName = getCampaignName(targeting = targeting, campaignType = "SP", type1=type1, type2 = type2, groupName = groupName)
    
    campaign = copy.deepcopy(emptySpBulksheet) # Create a deep copy
    
    campaign['Entity']           = 'Campaign'
    campaign['Campaign Id']      = campaignName
    campaign['Campaign Name']    = campaignName
    campaign['Start Date']       = campaignStartDate
    campaign['End Date']         = campaignEndDate
    campaign['Targeting Type']   = targeting.upper()
    campaign['State']            = campaignStatus
    campaign['Daily Budget']     = campaignDailyBudget
    campaign['Bidding Strategy'] = campaignBiddingStrategy
    
    if ((bulkSheetSp['Entity'] == 'Campaign') & (bulkSheetSp['Campaign Id'] == campaignName)).any():
        pass # Do nothing, we already have this campaign
    else:
        bulkSheetSp = bulkSheetSp.append(campaign, ignore_index=True)
    
    # Create adgroup          
    createSpAdgroup(targeting = targeting, type1=type1, type2 = type2, groupName = groupName, campaignName = campaignName, customerPath = customerPath, counter = counter, fileName = fileName)

Here we first copy our "empty bulk sheet" and fill it with all the necessary data. Later you will see where this comes from. This method then calls the next method createSpAdgroup, which does a similar thing as shown above, just for an ad group. All the information we passed to our campaign is also getting passed into the ad group.

This method looks like this:

def createSpAdgroup(targeting = "Manual", type1="none", type2 = "none", groupName = "none", campaignName = 'none', customerPath = "none", counter = 1, fileName = ""):
    
    counterString = f"{counter:02}"
    
    global bulkSheetSp
    
    adgroupName  = getCampaignName(targeting = targeting, campaignType = "SP", type1=type1, type2 = type2, groupName = groupName) + '-AG' + '-' + counterString
    
    adgroup = copy.deepcopy(emptySpBulksheet) # Create a deep copy to not alter emptySpBulksheet
    
    adgroup['Entity']        = 'Ad Group'
    adgroup['Campaign Id']   = campaignName
    adgroup['Ad Group Name'] = adgroupName
    adgroup['Ad Group Id']   = adgroupName
    adgroup['State']         = adgroupStatus
    adgroup['Ad Group Default Bid'] = adgroupMaxBix
        
    bulkSheetSp = bulkSheetSp.append(adgroup, ignore_index=True)

    # Create ad
    
    createSpAd(targeting = targeting, type1=type1, type2=type2, groupName=groupName, campaignName=campaignName, adgroupName=adgroupName, customerPath = customerPath)
    
    if type1 == "keyword":
        createSpKeywordTarget(targeting = targeting, type1=type1, type2=type2, groupName=groupName, campaignName=campaignName, adgroupName=adgroupName, customerPath = customerPath, fileName = fileName)

    if type1 == "product":
        createSpProductTarget(targeting = targeting, type1=type1, type2=type2, groupName=groupName, campaignName=campaignName, adgroupName=adgroupName, customerPath = customerPath)

We define all other methods needed to create ads, keyword targets, product targets, negative keywords, etc., in the same way.

The main method which kicks off everything

And then we need our main script to kick it all off. This looks like this:

if createSPCampaigns:
    
    # Create empty bulksheet
    bulkSheetSp = pd.DataFrame(data=emptySpBulksheet, index=[0])

    # Get all products or product groups stored in different folders
    productGroups = list_paths(customerPath)
    productGroupsLength = len(productGroups)

    j = 0

    randomString = randStr(N=5) # Create a random string

    for productGroup in productGroups:
        
        j = j + 1
        
        # Check which files are available
        all_files = sorted(glob.glob(customerPath  + '/' + productGroup + '/*.txt'))
        
        counter = 0

        for file in all_files:

            if (addKeywordsOffensive) & ('keywords-offensive.txt' in file):
                createSpCampaign(targeting = "Manual", type1="keyword", type2 = "offensive", groupName = productGroup, customerPath = customerPath, fileName = file)
            
            if (addKeywordsGeneric == True) & ('keywords-generic.txt' in file):
                counter = counter + 1
                createSpCampaign(targeting = "Manual", type1="keyword", type2 = "generic", groupName = productGroup, customerPath = customerPath, counter = counter, fileName = file)
            
            if (addKeywordsDefensive) & ('keywords-defensive.txt' in file):
                createSpCampaign(targeting = "Manual", type1="keyword", type2 = "defensive", groupName = productGroup, customerPath = customerPath, fileName = file)
            
            if (addProductsDefensive) & ('products-defensive.txt' in file):
                createSpCampaign(targeting = "Manual", type1="product", type2 = "defensive", groupName = productGroup, customerPath = customerPath, fileName = file)
            
            if (addProductsOffensive) & ('products-offensive.txt' in file):
                createSpCampaign(targeting = "Manual", type1="product", type2 = "offensive", groupName = productGroup, customerPath = customerPath, fileName = file)

For didactic reasons, I removed some lines, e.g., cleaning the files before we parse them (remove blank lines, remove duplicate keywords, etc.). Our script also handles the case if a file contains more than 1.000 keywords. This can quickly happen if you work with permutations because you want to use phrase match in case of broad match.

Create your Amazon bulk sheet

Last but not least, we need to export our data to an Excel sheet we can then upload to Amazon.

This works like this:

# Remove empty rows
if createSPCampaigns:
    bulkSheetSp['Entity'].replace('', np.nan, inplace=True)
    bulkSheetSp.dropna(subset=['Entity'], inplace=True)

# Filename
today = time.strftime("%Y-%m-%d-%H-%M") # Today as default
outputFile = customerPath + '/' + today + '-' + slugify(customer) + '-campaign-create-bulksheet.xlsx'
outputFile = outputFile.replace("input-", "")

# Export to Excel
with pd.ExcelWriter(outputFile) as writer:  
    if createSPCampaigns:
        bulkSheetSp.to_excel(writer, sheet_name='Sponsored Products Campaigns', index=False)

Summary

And there you have it. Once you have everything in place, you can use your creativity on how to quickly populate those txt files or your main input sheet.

Here are some ideas to get you started:

Quickly generate keyword and product target ideas based on the search term report in Brand Analytics
Use the keywords from your Amazon SEO research here as well

If your client gives us the task to re-organize the existing campaigns and leverage the good performing keywords, we, e.g., parse existing bulk sheets and extract all the products incl. the well-performing targets. We then put each target in the correct bucket and run the script within minutes.