Amazon Attribution: Building a Creative Code Key

This 43 minute video walks through how to combine the Amazon Attribution data with the ad platform data (in this case, BookBub) in a single report, starting from a blank spreadsheet. It also goes over a coded naming convention, which allows you to analyze your ad performance in a ton of different ways. Even if you don’t do some of the more complex analysis featured in the video, the code key provides a central storage location for all your creative elements which is invaluable when creating ads. The codes also make it easy to search for specific ads and elements on your dashboard by, say, typing in “G115” and instantly pulling up every ad that’s ever run with that image.

Starting the count from 100 allows you to use the platform search without getting extraneous results until you hit 1000 (e.g., if you start from “G1”, when you have a creative with G10 in the name that will show up as well when you search “G1” on BookBub or Facebook). Count up from 100 across all the books; if you make G100 and G101 for Book A, then switch to Book B, the next image will be coded G102 (e.g., don’t start over from G100 for the different book).

Maintain a separate key for Facebook and BookBub. That’s easier than trying to combine them into a single document.

If you’re building a key for Facebook, you can use a service like Supermetrics or DataSlayer to automatically pull the raw data into Google Sheets daily so you don’t have to export the ad data manually.

A few notes on Wistia:

  • You can watch the video at 1.5x – 2x speed by clicking the gear at the bottom right.
  • You can access the chapter timestamps and jump to a specific section by clicking the three horizontal lines at the bottom right.
  • You can make the video full screen by clicking the rectangle at the bottom right.

Key formulas

  • SUMIFS: formula syntax here is (metric you’re trying to pull, column of code you’re trying to match, cell of code)
  • SPLIT: formula syntax here is (cell with code text you’re trying to split, character you’re trying to split it at)

If you want to get to an intermediate level with Sheets, this course is one of the best courses I’ve ever taken. It’s $12 – $20, so if it’s displaying a price of $199, just wait until it’s “on sale” (which is the actual regular price).

BookBub naming convention:

  • G100 = image
  • H100 = headline
  • S100 = sub-headline
  • A100 = audience [in this example I’m using AG100 for aggregate audiences; I wouldn’t do this and would just code these using the regular A100 system]
  • Full BookBub Creative Code = G100 H100 S100 A100 (must be in this exact order)

Facebook naming convention:

  • G100 = image
  • C100 = copy
  • H100 = headline
  • A100 = audience
  • Full Facebook Creative Code = G100 C100 H100 A100 (must be in this exact order)

Note that I don’t generally recommend creating individual tracking links for each audience; this massively multiplies the number of links you have to create. Only do this when you’re testing specific audiences, otherwise making a specific link for each ad creative is sufficient.