Amazon Advertising Efficiency

The idea behind Advertising Efficiency:

Creating an Advertising Efficiency sheet

Preparing the new sheet:

Copy & Paste the necessary data

Creating the Headers and Tables:

Get the data from Amazon Seller Central

Create the first empty table

Format the table

Duplicating the first empty table:

The idea behind Advertising Efficiency:

The purpose of the Advertising Efficiency sheet is to provide us with a general overview of the performance of our advertising campaigns on Amazon.

The initial creation only needs to be done ONCE per Amazon retail website (US, DE, and FR).

With each new search term report, we update the sheet to include the data from the latest report. We will also be using charts so the data is presented more readily.

Update the Advertising Efficiency sheet as soon as the Amazon Advertisement Optimization task for the latest month is done.

Creating an Advertising Efficiency sheet

If you don't have the link yet to the search term report, request it from Sir Antti or Kleia. Then request permission to create new sheets.

Below are our current existing search term reports:

Search Terms UK 2016-2017
Search Terms DE 2016-2017
Search Terms FR 2016-2017

Follow the checklist and when in doubt, check the images provided to make sure you're still on track. The image will be captioned and numbered based on the checkbox they relate to.

1

Open the Search Term report for the country you are working on (US, DE, FR, etc.)
2

Search for a tiny plus symbol on the lower-left corner of the spreadsheet. It should come before the list of sheets.
3

When the new sheet has been added, right-click on the sheet and select the option 'Rename..."
4

Rename the new sheet to "Advertising Efficiency"

Preparing the new sheet:

This is the part where we set up everything in preparation for data you'll be placing.

Be careful to follow the steps as instructed. From experience, Google Sheets can tend to be picky about how certain things are done. If you have experience using Microsoft Office Excel, you'll find Google Sheets to be convenient for a lot of things, but also very picky in how they are done.

Let's get started!

Copy & Paste the necessary data

Use data from older search term reports

There should be a finished Advertising Efficiency sheet from older Amazon search term reports.

Request for the links to these old search term reports. This is the quickest way for you to place the data without having to manually format the values.

1

From the old search term report, select the first 5 rows. Do this by clicking and dragging your mouse from '1' to '6' on the left part of the sheet where the rows are indicated.

1

Go back to the new Advertising Sheet you are working on.
2

Click the first cell on the corner of the upper-right side, the one under column A and row 1 (or cell A1)
3

Paste the data you just copied.

After completing steps 1, 2, and 3 above.

Now comes the tricky part. The next few steps will seem weird but trust me, this is the only way I found to accomplish the desired effect on Google Sheets.

1

Select the rows '2' to '5' again and copy the selection
2

Once you are sure you've copied them, right-click on the selection and delete these rows.

Now you only have rows '1' and '2' left.

1

Select rows '1' and '2'
2

Click "View" on the upper part, under the name of the spreadsheet.
3

Place your mouse over "Freeze" to expand a new set of options on the side
4

In the new selection, choose "2 rows"

You have now frozen these rows, which means you should be able to view them even if you scroll down through the sheet.

We're now going to insert back the rows you just copied and deleted.

1

Select row '2'
2

On the upper part of the sheet, click on "Insert"
3

Select "Row Above" FOUR times
4

Select the cell A2
5

Press Ctrl+V to paste the data you copied awhile ago.
6

If the cell widths and heights appear unnatural, edit this to look uniform and natural.

This is how it will look after you've pasted back the data you just copied and deleted.

1

Next, delete all the values in the rows 2 to 5 EXCEPT "Campaign:" and "Ad Group:"

After completing the step above, it should look like this. Now everything is prepared and we can now place the necessary values to create the Advertising Efficiency sheet for this Amazon site.

And that's it! You're done with this part. Great job!

Let's continue to the next steps for preparing the new sheet.

Creating the Headers and Tables:

The following steps will guide you through how to create the headers and tables. Please be patient with some of the steps. They may not really make sense but they are crucial to achieving the desired effect, especially on Google Sheets.

Get the data from Amazon Seller Central

Let's now move on to creating tables. For this, we need to know what headers to put in. We can do this by logging in to Seller Central and find out what the 'Campaigns' and 'Ad Groups' are.

Getting The Data From Amazon Seller Central

1

Request access to Amazon's Seller Central. If you've already been given access, proceed to the next step.
2

Log in to your Seller Central account.
3

Make sure you are logged in to the right country. See the image below to find out how to change the country.
4

Click on 'Advertising' on the upper-left side of the page.
5

Select 'Campaign Manager'

You'll need a notepad to work more efficiently for the next part.

1

Open a notepad tool/software.
2

On Seller Central, since you've already selected Campaign Manager, you should see the list of campaigns. Copy all of these and paste them into your notepad.
3

Click on the first campaign, starting from the top.
4

The results that appear on the next page are the 'Ad Groups', which you also need. Copy everything in here, too.
5

In your notepad, make sure to paste the details you took from 'Ad Groups' under the right campaign.
6

Click 'Back' in your browser and do the same for the next Ad Group, paying attention to paste the details under the right campaign in your notepad.

Create the first empty table

Now that you have the name of campaigns and ad groups from Seller Central on your notepad, it's time to use them for the Advertising Efficiency sheet.

1

Copy the first Campaign on your notepad.
2

Click on the first cell to the left that's below the frozen rows.
3

Paste the copied campaign name.
4

In the same row, but under the column H, paste the first Ad Group from your notepad.
5

Format the two values you just added to Font Size 18 and Bolded.

Do the following all under the first campaign and in the same row (row 8):

1

In cell A8, write 'Month'
2

In cell C8 (skip B8 for now), write 'Total Average ACoS'
3

In cell D8, write 'Sum of Sales'
4

In cell E8, write 'Sum of Money Spent'
5

In cell F8, write 'Average Conversion Rate'

Now that the sub-headers have been prepared, it's time to prepare the rest of the table.

1

Select the cells A8-B21. It should look like the image below:

1

Click on Format, then hover your mouse over 'Merge cells'. In the selection that appears, click on 'Merge cells horizontally'. It should look like this:

1

Under the sub-header 'Month', write down 12/2016 until 12/2017 with each date on separate lines. You should end at row 21 after doing this.

Format the table

We'll need to format the tables so they look appealing. Of course, designing it is optional but it makes the work more interesting to see everything organized and designed well.

Here are the steps to formatting the first table just like the rest of the Advertising Efficiency sheets. If you already have prior knowledge and expertise with Google sheets or Excel, this should be very easy. If not, then this is a good place to start learning.