MySQL – Today I Learned… in Code

This article is a tutorial for taking Amazon Associates (Amazon Affiliate program) export data into MySQL workbench.

Screenshot 2017-01-21 21.58.25

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

You can download the latest version of MySQL Workbench here.

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.

Screenshot 2017-01-21 13.46.20

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:

Screenshot 2017-01-21 13.50.21

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.

Today I learned… how to remove unwanted characters like â€ and Á from WordPress post content using a MySQL query.

Backstory: I recently migrated a blog from shared hosting to a VPS. This experience alone could fuel TILCode content into 2024, but this particular “TIL” article is about a curious character encoding artifact that occurred as I exported the WordPress MySQL data and imported it elsewhere: Á and â€ symbols everywhere!

Removing these unwanted symbols from my WordPress posts required running a few queries on my MySQL databases. Here are the steps I used.

(And if there’s a better/easier way to do this, please let me know in the comments. This was my first foray into MySQL queries, but it got the job done.)

Step 1: Log into PhpMyAdmin

Access phpmyadmin through your cPanel or yourdomain.com/phpmyadmin – access varies by host and setup

Step 2: Navigate to the SQL tab and change collation

Click on the “PhpMyAdmin” logo so that you’re at the “root” level (and not inside a database – this might work from inside a database, but I did it from outside).

Click on the SQL tab.

Here, you’ll get a large window in which you can type queries – operations you perform on your data.

The first query to run is one that will set the character encoding type.

ALTER DATABASE your_db_name_here DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

I learned this from JesseWeb’s helpful tutorial on resolving WordPress character issues, but his code didn’t work for me as-is (I suppose that’s not surprising considering his guide is nearly 5 years old). I had to remove the quotes from the database name.

Your query will look like the image below. Be sure to replace your_db_name_here with your database’s actual name (look in the left column for your db names).

Click the “Go” button on the right to run the query.

Step 3: Remove unwanted characters from existing posts

For this query, you need to navigate to the actual database itself. Click on its name on the left.

Click on the SQL tab. Now the helper text says that queries are run on the database you selected.

Enter this query string to replace all instances of â€ with an empty space:

UPDATE wp_posts SET post_content = REPLACE(post_content, 'â€œ', '');

Repeat the query for every unwanted character. Note also that you can stack ’em up in the query window like so and run ’em all at once:

UPDATE wp_posts SET post_content = REPLACE(post_content, 'â€œ', '');
UPDATE wp_posts SET post_content = REPLACE(post_content, 'Á', '');
UPDATE wp_posts SET post_content = REPLACE(post_content, 'foo', 'bar');

You may also need to clean up comments. You can do that with:

UPDATE wp_comments SET comment_content = REPLACE(comment_content, 'Á', '');

See shadez’s question on WordPress.org for more examples – his example was instrumental in helping me understand this issue.

And with that, all the unwanted characters are gone. Phew!

Category: MySQL

Analyzing Amazon Affiliate sales data with MySQL workbench and simple SQL queries