This article is a tutorial for taking Amazon Associates (Amazon Affiliate program) export data into MySQL workbench.
In MySQL workbench, you can easily view and sort your data in some sophisticated ways that Amazon’s own dashboard doesn’t really support.
I wanted to ask questions like:
- What was my best-selling item for this particular affiliate ID?
- What items make the highest commission?
- Which items get returned the most frequently?
Here’s how I dumped my Amazon Affiliate data into MySQL workbench and some of the queries I used to explore it. I’m pretty new to SQL so I’m sure there are even more sophisticated things I could be doing with this data and different ways to do the same thing!
(You will need to use your own .csv data dump to follow along with this tutorial.)
Step 1: Get a .csv export of your Amazon Affiliate data
Use the Download Reports button from your Amazon Affiliate dashboard to download your account’s data as a csv (comma separated values). Be sure to adjust the date range.
For this analysis, I exported all of 2016’s Amazon Affiliate data. It’ll generate a bunch of files, but to follow this tutorial you’ll want the file that starts with Fee-Earnings-
This might take a while, especially if you’re exporting an entire year’s worth of data like I did. You can do the next several steps while you wait.
Step 2: Download and install MySQL workbench
Step 3: Make a new connection
Step 4: Start your computer’s MySQL service
You may have to manually start your MySQL server.
On a Mac, it’s in Apple menu > System Preferences… > MySQL
On a Windows computer, these steps might help you.
Step 5: Create a new schema in MySQL Workbench
Right click in the light blue area underneath SCHEMAS in MySQL Workbench. Choose Create Schema…
Give it a name and leave the rest as-is:
You should now see the new schema in the SCHEMAS section:
Step 6: Prepare your csv data for import
Your csv file’s first line will cause problems on import, so let’s open the csv and remove it. I used Microsoft Excel for removing the first line but you can probably use anything capable of opening, editing, and saving a csv file (including a basic text editor).
You need to get rid of the first line, the one that starts “Fee-Earnings reports…”
If you’re saving from Excel (and this problem might be limited to just Excel on a Mac), you should save it as a Microsoft Comma Separated. On my Macbook, at least, saving as a normal .csv from Excel will cause it to fail the import in MySQL Workbench.
If you have any problems importing the csv file in MySQL Workbench, try different csv formats after saving your csv file from Excel (or whatever you use… I’m convinced my import problems were just a result of using Excel on a Mac.)
Step 7: Bring your csv data into MySQL Workbench
Right-click your schema and choose Table Data Import Wizard.
Follow the prompts and choose your cleaned up .csv file. If everything works, you should end up with a screen like this:
If you run into any import errors, make sure you’ve 1) removed the first line from the file and 2) saved as a Windows CSV (if you’re saving from Excel, possibly just Excel on a Mac. I don’t have Excel on my Windows computer to test this theory).
Follow the prompts to finish importing the data.
Step 8: SQL queries!
Finally, the fun part!
See the little “circle” made of arrows to the right of the SCHEMAS title? Click that and Tables should refresh and get a roll-out arrow. Click that arrow and you’ll see your data in a table. Right click to select the top 1000 and open the editor.
Here’s what you should have now: an editor pane and a bunch of results below.
Each query starts with a SELECT and ends with a semicolon (“;”). To run just one query, place your editor cursor anywhere inside the query and click the lighting bolt with a cursor icon.
Here are some queries I developed for analyzing my Amazon Affiliate data. Feel free to steal them, modify them, and use them to analyze your own affiliate sales data.
Select all the items sold for a particular tracking ID
This one’s straightforward: it selects all the items sold under a particular tracking ID. Most of my more sophisticated queries are built on this one, and it’s a good way to narrow down your data if you have multiple IDs.
SELECT * FROM amzn2016_schema.data WHERE `Tracking ID` = "yourtrackingidhere-20";
See which items earned the most money (“fees”) for a particular affiliate ID
SQL comments start with a #, so they’re handy for adding notes to your queries. This query selects all the items for a particular ID that earned me $5 or more, with the highest earning items at the top of the list.
#just the most profitable items from that ID SELECT * FROM amzn2016_schema.data WHERE `Tracking ID` = "yourtrackingidhere-20" AND `Ad Fees($)` >= 5 ORDER BY `Ad Fees($)` DESC;
See which items were your most frequently returned
Here’s something Amazon doesn’t make easy to figure out in their dashboard: which items you sold that got returned, and in what quantities.
This query tells you the names and tracking IDs of the items that had return counts, with the highest at the top.
#most returned items SELECT `Tracking ID`, `Name`, floor(SUM(`Returns`)) as `Return count` FROM amzn2016_schema.data GROUP BY `Name` ORDER BY `Return count` DESC;
See which items earn $15 or more per sale
What items earned you the largest commissions? This query will tell you.
You can change the 15 to whatever you want your bottom to be – for me, earning $15 on a single sale is super awesome so I wanted a list of just the items that earn $15 or more per sale. Who knows, I might find a new product to write about by digging around this list!
#items with ad fees over $15 SELECT * FROM amzn2016_schema.data WHERE `Ad Fees($)` >= 15 AND `Items Shipped` = 1 ORDER BY `Ad Fees($)` DESC;
I set it to only return items that have 1 item shipped, otherwise I get groups of items and artificially inflated Ad Fees as a result. However, since some items have only been purchased in sets of multiples, it’s a good idea to change this number to a 2, 3, or more (or remove the line altogether) to see those items, too.
Sure enough, I found some items that earned me some large commissions – $44.93, $39.75, and $36 off of these single items! These aren’t items I blog about, but… maybe they should be now that I know about them! :)
Find your most expensive item sold through Amazon Affiliate program
That got me wondering: what’s the most expensive item someone has bought through one of my Amazon Affiliate links?
This query made it easy to figure that out:
#most expensive item sold set @maxPrice = (select MAX(`Price($)`) FROM amzn2016_schema.data); SELECT * FROM amzn2016_schema.data WHERE `Price($)` = @maxPrice;
My most expensive item sold is one of those sweet Wacom tablet computers! Cool!
Notice how the ad fees (my earnings from selling this item) are capped at just $25, despite its high sales price? That’s the nature of Amazon Affiliate program – some items, even though they sell for a lot, don’t always earn a proportional amount in ad fees. (Some items that cost much less than this tablet computer actually bring in higher ad fees.)
See earnings and items sold, by affiliate ID
Here’s another fun bit of data, especially if you have multiple sites or multiple IDs for a single site and want to compare them all. This query outputs each affiliate ID, how many items it sold total, and how much money it earned.
#all IDs and how much they made, grouped by ID SELECT `Tracking ID`, floor(SUM(`Ad Fees($)`)) as `Fees earned`, COUNT(*) as `Total items sold` FROM amzn2016_schema.data GROUP BY `Tracking ID` ORDER BY `Fees earned` DESC;
Find your best-selling items and how much they earned (collectively)
If you’ve ever wanted to know what your best-selling items are across all your Amazon Affiliate IDs and how much you earned from those items, here you go:
#best-selling items by tracking id with fees earned as a total SELECT `Tracking ID`, `Name`, COUNT(`Name`) AS `number sold`, floor(SUM(`Ad Fees($)`)) AS `earnings` FROM amzn2016_schema.data GROUP BY `Name` ORDER BY `earnings` DESC;
That’s it for now! I hope you found these queries useful for analyzing your Amazon Affiliate sales data.