Fixing WordPress site’s low mobile score in Google’s PageInsights with W3 Total Cache

In this article: Steps I took to optimize a WordPress-based site for Google’s upcoming mobile search prioritization. It started with a PageSpeed Insights score of 8/100 and ended at 62/100.

Plugins used: W3 Total Cache

Time spent: Several hours of trial and error, but this guide should help you through it much quicker.

Google announced in January that they’re going to take a site’s mobile speed into account for searches beginning July 2018. I’ve always tried to make my WordPress sites mobile-friendly, but it looks like there’s some room for improvement.

One of my biggest (and highest-earning) sites scored just an 8 out of 100 for mobile optimization. Ouch.

(You can test your own site here: https://developers.google.com/speed/pagespeed/insights/ )

What I’ve already done to optimize this WordPress site

I’ve managed this site for a few years and have already put some effort into optimizing it for search and mobile.

The site already scores well for these categories:

  • Avoid landing page redirects
  • Enable compression
  • Minify CSS
  • Minify HTML
  • Minify JavaScript
  • Optimize images
  • Prioritize visible content

Some of those scores are probably due to the site already using a CDN, lazy loading, a good caching plugin, and a good hosting plan. Here’s what the site uses:

However, there’s more work to be done! Here’s what I did to raise my Google Insights mobile score.

Fixing render-blocking JavaScript on above-the-fold content on a WordPress site using W3 Total Cache and a CDN

Let’s look at this render-blocking JS/CSS problem first:

Eliminate render-blocking JavaScript and CSS in above-the-fold content. 

Your page has 8 blocking script resources and 12 blocking CSS resources. This causes a delay in rendering your page.

None of the above-the-fold content on your page could be rendered without waiting for the following resources to load. Try to defer or asynchronosly load blocking resources, or inline the critical portions of those resources directly in the HTML.

This message is followed by a list of all the JS and CSS scripts causing the render blocking, which were 8 JavaScript files and 3 CSS files in my site’s case.

These guides were helpful to me while working on this problem:

To fix the blocking JavaScript files I had to do a number of things to configure W3 Total Cache. This part took a couple hours of trial and error and some steps were not covered in the guides I used (or my situation was different from theirs), so I’ve attempted to document the process as I experienced it in this guide.

First, I enabled Minify in the general W3 Total Cache settings. Go to Performance > General Settings > Minify > check the “Enable” box and select the “Manual” radio button.

Next, go to Performance > Minify to work on minify settings as they pertain to JavaScript and CSS. You’ll need the list of blocking files from PageSpeed Insights report. 

Watch out! The file paths shown in the PageSpeed Insights results might be truncated. To get the full path, you have to hover over the truncated path and copy the path from the tool tip

In other words, make sure you are copying a complete path:

https://your-cdn.netdna-ssl.com/wp-includes/js/jquery/jquery.js?ver=1.12.4

and not a path with … in it:

https://your-cdn.netdna-ssl.com/-includes/js/jquery/jquery.js?ver=1.12.4

In Performance > Minify there are a variety of options that apply to all the script in a particular area (before head, after body begins, before end of body). In my case, code in the head tag was already minified, so I set it to “Combine only” (rather than re-minify it). I also needed to keep it blocking, or else my page filled Chrome’s console with “can’t find JQuery!” errors.

Next, under JS file management, add all of the blocking files from the PageSpeed Insights results – be sure to get the full file path, not the truncated version.

Also! If you’re using a CDN, you’ll have to change the domain in the file paths that you paste. 

Instead of this:

https://your-cdn.com/wp-includes/etc

You need to use your website’s actual domain. For each path you paste, change the CDN part to be your site’s domain instead, like this:

https://yourwebsitesrealdomain.com/wp-includes/etc

Or you can remove that part of the url entirely and go with something like:

/wp-includes/etc

…which is what I did for mine:

Note: I needed to set the jquery files to embed in <head> to avoid a slew of console errors when visiting the site. As per the earlier settings, anything embedded in head is going to be blocking. I think some blocking files might be inevitable; the point of these settings is to tease out which files belong where in the load sequence.

Save settings and purge all caches, then test again in PageSpeed Insights.

Woohoo – no JS files blocking loading. I also tested the site manually, and verified that it doesn’t look like garbage and doesn’t fill the console with errors. So far, so good.

Fixing render-blocking CSS with Autoptimize

I initially set up all the CSS file paths in W3 Total Cache but ran into these two cases that I couldn’t figure out how deal with through W3 Total Cache alone:

One blocking css file is from w3tc itself:

https://mywebsite.com/?w3tc_minify=6951f.default.include.89e358.css

And another is from Google Fonts:

https://fonts.googleapis.com/css?family=Open+Sans%3A300%2C400%2C600%2C700%7CMuli%3A300%2C300italic%2C400%2C400italic%2C600%2C600italic%2C700%2C700italic%2C900%2C900italic%7CPlayfair+Display%3A400%2C400italic%2C700%2C700italic&subset=latin%2Clatin-ext

To fix the Google Fonts, I installed the very lightweight plugin Autoptimize and checked these boxes:

I also used Autoptimize to disable Google Fonts. Whether you want to do this is up to you, but I went ahead with it because I am happy with a default system font for this site.

To fix the w3tc minify, I disabled CSS in W3TC and let Autoptimize handle it. I also had to uncheck this setting:

Finally, for the sake of completeness, here’s what my HTML minify settings look like:

At this point, I loaded my site and found it was using serif typefaces on buttons and it wasn’t scaling the hero (header) image correctly. To fix this, I added custom CSS to Autoptimize’s custom CSS section. This CSS gets loaded right away, so the page doesn’t get stuck waiting on the js or css to “fix” certain issues that are visible right away to a visitor.

.button {
   font-family: "Open Sans", sans-serif !important;
}

.header-homepage {
   padding-top: 120px !important;
}

One more thing: I also uploaded a significantly more compressed “hero” banner image jpg to the site (old size: about 350kb, new size: about 110kb).

The reward for all these optimizations:

Note that I still have one render-blocking w3tc js file. For now, I’m going to call this “good enough”, since I don’t know how to fix this last one without breaking some fundamental aspect of the site, so I’m going to move onto the expiry problems and see if I can get the score higher that way.

Fixing “leverage browser caching” with W3 Total Cache

PageSpeed Insights found a few files it thinks could leverage browser caching. These files are all .js files.

I found this guide helpful and accurate at the time of this writing: Leverage Browser Caching with W3 Total Cache

The first step is to enable browser caching in W3 Total Cache’s settings. Go to Performance > General Settings and make sure Browser Cache is enabled.

Then, go to the actual Browser Cache section of the plugin. As per the guide, I ensured these boxes were checked:

  • Set expires header
  • Set cache control header
  • Set entity tag (eTag)
  • Set W3 Total Cache Header – this one was not already checked for me
  • Enable HTTP (gzip) compression

(I re-ran the PageSpeed Insights test at this time just in case that one checkbox was all it took. Alas, there was no change in the results.)

The next thing I did was scroll down a little bit to the CSS & JS section. My “expires header lifetime” was set to 172800, which is way less than the recommended 604800 (2 weeks) the guide recommends. I saved that change, cleared cache, and ran the test again.

Now I have just three files with unsuitable caching times:

Alas, two of the remaining files are out of my hands: they are Google Analytics’s own .js files. After reading this guide to browser caching Google Analytics, I decided not to try to cache the analytics js file myself. Doing so would require manually updating it periodically. Even the guide’s author says they don’t recommend this method. That’s fine by me.

But how about this emoji js file? I don’t use emojis on my site, so I wouldn’t mind removing them and reaping a small page speed boost.

Normally, removing them is as simple as adding these two lines in your site’s functions.php:

remove_action( 'wp_head', 'print_emoji_detection_script', 7 );

remove_action( 'wp_print_styles', 'print_emoji_styles' );

But I’m using a pro theme that gets automatic updates (which will clobber this code if I add it to functions.php), and I don’t want to make a child theme for the express purpose of removing emojis.

So, I did what any good WP user does and installed yet another plugin: Disable Emojis. (Yes, I also canceled Christmas and summer break while I was heartlessly removing emojis from my very srs blog.)

I ran PageSpeed Insights again and….

Woohoo! (It initially came out worse, but network latency has some affect on the score, too. I tried again and got a 62/100).

My report has never looked better:

PSI estimates this page requires 2 render-blocking round trips and ~26 resources (0.4MB) to load. The median page requires 4 render-blocking round trips and ~75 resources (1MB) to load. Fewer round trips and bytes results in faster pages.

Fixing the “flash of unstyled content”

A new problem has been introduced by all this work: now my page looks unstyled for half a second or so while it’s loading.

I used this tool: Critical Path CSS to identify a the “minimum” CSS needed to make the “above the fold” content look good (it’s a huge block, I’m sure a human could produce something more elegant but for now, it’ll do).

I put this CSS into Autoptimize’s “above the fold” CSS section and the flash of unstyled content seems to look better now, though I still see the fonts looking bad before the preferred “open sans” style comes into effect.

Done… for now

At this point, I’ve gotta call it good enough for now. My site’s score went from 8/100 to about 62/100. I still have a one blocking JS file that I don’t know what to do with and two files from Google Analytics with unsuitable cache expiration times.

I’ll update this page when I make further improvements to the site.

How to convert a 4-byte hexadecimal sequence in a little-endian architecture into decimal

Here is my technique for solving the problem of converting a 4-byte hexadecimal sequence in a little-endian architecture into decimal.

This may seem rather niche but it was a surprisingly large part of Week 5 in my CS271 class. The class’s materials and extra help I found around the web seemed to go off on tangents that were interesting but unrelated to solving this kind of problem quickly, and all I really wanted was a simple step-by-step guide I could use on exams.

Therefore, I am sharing my simple 5-step technique for converting hex to decimal in a little-endian architecture here! Hope someone else finds it helpful.

Example problem:

The four-byte sequence 0x86 0x65 0x53 0x82 stored in consecutive memory cells in a little-endian architecture represents ___________ (decimal) when interpreted as a 32-bit signed integer.

From reading this, we know: 

  • it’s little-endian, so we are going to reverse the order of the bits
  • our result will be signed

Step 1: Reverse the bytes

Take the bits in blocks of two and work right to left.

0x86 0x65 0x53 0x82 becomes 0x82536586

Step 2: Look at the most significant bit and determine if there will be a negative result

0x82536586   <-- that's this dude in red here

The most significant bit here contains an 8.

We know that in hex a most-significant bit of 7 or more means we are looking at a negative number. (If this were a positive number, ie: the most significant bit is between 0 and 6, then skip ahead to Step 4.)

Step 3: Since we are working with a negative number, flip the bits (subtract our hex sequence from FFFFFFFF) and add 1

  FFFFFFFF
- 82536586
  7DAC9A79

Add one to the result:

7DAC9A79
      +1
7DAC9A7A

The result is the hex sequence we will use for the next step.

7DAC9A7A

Step 4: Multiply each term by 16 raised to a power

To convert a hex value into a decimal value, we multiply each “position” in the hex sequence by 16 raised to a power. Working from right to left, we know that furthest-right position is 16^0 (so, just a 1). The second-from-right position is 16^1 (so, just a 16). The third-from-right position is 16^2, and so on.

Recall that A = 10, B = 11, C = 12, D = 13, E = 14, and F = 15 when working in hex.

(7*16^7)+(D*16^6)+(A*16^5)+(C*16^4)+(9*16^3)+(A*16^2)+(7*16^1)+A

The result of this long sequence is:

2108463738

Remember that negative sign? Now’s a good time to stick it on.

-2108463738

And there you have it: the result is -2108463738.

Some final notes

  • Be sure to observe whether the problem expects a signed decimal result or an unsigned decimal result. If the problem is asking for unsigned, you can skip the FFFFFFFF subtraction step entirely, even if the most significant bit is 7 or higher.
  • Remember that when working with a signed hexadecimal number, you look at the most significant bit to determine if it’s negative or positive.
    • 0-7 = positive
    • 8-F = negative
  • If you had to do the flipping step, don’t forget to put that negative sign onto your final answer!

Fix: Font Awesome icons missing with Mesmerize PRO theme and MaxCDN

In this post: missing Font Awesome icons due to missing CDN configuration.

I have a WordPress blog that uses MaxCDN, W3 Total Cache, and the Mesmerize PRO theme by Extend Themes which includes Font Awesome icons.

After installing and activating the theme on my website the Font Awesome icons appeared as boxes:

Missing icon looks like a box
Another missing font awesome icon
Another missing font awesome icon

And I could see these (canceled) errors in the Network tab:

Errors in the Chrome Network tab. fontawesome-webfont status is canceled.
Errors in the Chrome Network tab. fontawesome-webfont status is canceled.

The referrer is

https://cdn1-mycompany.netdna-ssl.com/wp-content/themes/mesmerize-pro/assets/font-awesome/font-awesome.min.css?ver=1.0.221

That referrer, cdn1-mycompany.netdna-ssl.com, isn’t allowed to serve this file. But there’s an easy fix: you can whitelist the CDN itself in the CDN.

The fix: whitelist the CDN itself

In MaxCDN, go to Pull Zones > Security > Whitelist.

You might already have yourdomain.com in here. What you need to add is the domain your CDN files are pulled from.

MAXCDN settings: Pull Zone tab, Security tab, whitelist tab, add cdn1-domain.netdna-ssl.com to whitelist

In my case, that was a domain that took the form of cdn1-mydomain.netdna-ssl.com, but you can find out what yours is by looking in the Network tab while you try to load your site. Look for a red-colored error message and open the Headers.

Anyway, the fix is as easy as adding the domain to this list of whitelisted domains and waiting a few minutes (for me it was about 10 minutes). Reload your website and the Font Awesome icons should now appear.

Note: You will probably need to Purge All Caches, too, once the time has passed to actually see the change in your browser (I use the dropdown in the WP toolbar).

WordPress toolbar with W3 Total Cache Performance section rolled out, Purge All Caches selected

My W3 Total Cache settings

(Just in case it’s useful to someone else trying to debug this problem)

My W3 Total Cache settings are set to upload .css, .tff, .otf, .woff, and .woff2 files.

W3 Total Cache settings for which theme files to upload by extension. css, js, gif, tff, otf, woff, woff2, etc.

Provided again as text so you can copy/paste.

wp-includes file types to upload:

*.css;*.js;*.gif;*.png;*.jpg;*.xml

Theme file types to upload:

*.css;*.js;*.gif;*.png;*.jpg;*.ico;*.tff;*.otf;*.woff;*.woff2

This is a pretty specific configuration issue but hopefully it’ll help someone else out there!

I made an AngularJS web app! Check out the OSU CS Course Explorer

Browse 300+ candid student reviews and helpful tips for OSU’s online CS degree courses

Check it out: https://osu-cs-course-explorer.com/

What it does: Aggregates real student course reviews from the OSUOnlineCS subreddit survey, which dumps data into this spreadsheet, and displays that same data in an easier-to-navigate format.

Who it’s for: OSU CS Ecampus students

Technologies used:

The rest of this post is about the app itself (not its code). If you’re hoping for some code walkthroughs, stay tuned – I’ll be writing a few in the next month or two before I move onto my next side project.

App inspiration

I started OSU’s online CS degree program Fall 2016, and I am so grateful for the opportunity the school has given me to receive a formal CS education.

Ever since I applied to the program I’ve been an avid reader of the OSU CS subreddit. I was always looking for info on my next class – survival tips, strategies, what to study, etc. The course survey (linked in the sidebar) contains years of useful student data but at 300+ entries, it was becoming difficult to browse or parse on a macro level (ie: there was no way to look at it and determine how time-consuming on average a particular class might be).

I had the idea for a simple web app that would take the Google spreadsheet data and reorganize it by course, listing all the tips (with timestamps) and aggregating the time spent and difficulty data into a couple of easy-to-read pie charts. I knew other people would find this useful, too, so I planned to make it publicly and freely available once it was presentable. (From this experience I also wanted a finished, portfolio-worthy app that I might show to a potential employer.)

I started the project in the summer of 2017, worked on it bit by bit whenever I wasn’t swamped with classwork (or my full-time job, or my baby who was 9-13 months old while I worked on this, or the cross-country move I did in August). It’s definitely a testament of what you can build even if you don’t have loads of contiguous free time, as long as you are consistent and keep going.

Browse the code

If you want to browse the app’s progress (and see some of the mistakes I made along the way) you can browse the GitHub repo for it here. I tried to leave concise comments explaining what I was doing, in hopes that other students and beginning web devs would find it helpful.

I plan to write a few blog posts dedicated to different sections of the app’s code. I love “here’s an app I built and how I built it” type posts myself and owe a lot of my own knowledge to them, so I’ll try to give back a few contributions of my own.

Why I chose AngularJS

I know everyone’s got the hots for React these days but I had just come off of 2 years of working in Ember and wanted to return to my ancestral headwaters for a bit and build something in the framework I got my start in. I wanted to see if all my “I liked how that worked in Angular better” feelings towards Ember were actually accurate or just some rose-colored tinting of history (as it turns out, I really do prefer Angular to Ember :P)

I don’t know if I’d pick it again, though. The world has largely moved on from AngularJS (to Angular 2 and beyond, and React), but there’s still a ton of helpful blog posts and Stack Overflow questions about every imaginable Angular topic (way more than Ember has, that’s for sure) so for that reason, I think it’s still a good, established choice if you’re new to web development frameworks and want to try something.

Thanks to contributors

Special thanks goes to Yong Joseph Bakos for his pull requests after the project launched – cleaning up some cruft in the codebase, improving tests, documentation, etc.

Special thanks also goes to Jonathan Burley for helping unstick me at some critical points in development, like when I needed help customizing the canvas legends in a way that wasn’t documented and when Heroku was being a pain.

I feel like I always learn so much from even the briefest encounters with other developers, so I am grateful for the help I received along the way.

OSU eCampus CS261 Data Structures review & recap

This post is part of an ongoing series recapping my experience in Oregon State University’s eCampus (online) post-baccalaureate Computer Science degree program. You can learn more about the program here.

Six-word summary: Loads of implementations, light on context.

Heaps, AVL trees, hash tables, and a whole bunch of ways to sort data – you’ll do it all in CS261, but you might finish the course wondering why.

CS261 Review

This class is all about data structures. Some colleges combine data structures and algorithms into one semester-long class, so I’m happy that Data Structures got its own dedicated 10 weeks in OSU’s program.

Some of your new friends in CS261

Class structure

  • 10 weeks
  • 6 assignments total, evenly distributed through the quarter
  • Proctored midterm, proctored final
  • Nice slow-down in assignments and workload as midterm and final approach, giving sufficient time to study
  • Weekly group obligation: 2-5 worksheets to submit as a group (however you divvy it up is up to you) and typed weekly meeting minutes
  • Steady, even workload

The first 4 weeks will revisit topics from CS162, so if you wanted more practice reversing a linked list or resizing an array, you’ll get to do those things again in CS261.

After the midterm, the class spends 1 week on each topic: AVL trees (including tree sort), min heaps (including heap sort), hash tables (open addressing and chaining), and graphs (including Dijkstra’s). Big-O complexity is also covered in the context of the aforementioned subjects.

The video lectures are decent but to truly understand these topics I recommend searching the Interwebs for additional resources. These are big topics and there are many excellent explanations, diagrams, walkthroughs, etc. available on YouTube. (This is just one of them.)

The workload is consistent and gets lighter before the midterm and final, which left plenty of time to dedicate to studying for the exams.

CS261 books: the one they tell you to get, and the one you SHOULD get

I love a good book. The books we used for CS161, CS162, and CS225 were excellent. I did not love the lack of a book in CS261.

Instead of a book, CS261 provides pdfs to read every week. They are dry, boring, and do not display well on a mobile device. There are also worksheets that open with several paragraphs of reading. These are also dry, boring, and do not display well on a mobile device.

For some reason, this is the recommended (but optional) book for CS261: C Programming Language. It’s an okay book, but it’s almost completely beside the point of the class. It’s a reference to the C language. I don’t know why they recommend it – none of the class material relates back to it. If you’ve taken CS161/CS162/CS165, you know enough C++ to hobble along in C until you get your footing. If you run into any trouble (and you probably won’t past the second week or so) you’ll Google your error message, figure it out, and be on your way.

Then, at the end of fall quarter, I joined the CS325 Slack channel in preparation for my Winter 2018 quarter. There, I saw someone recommending the book Grokking Algorithms to incoming CS325 students.

THIS is the book you should buy for CS261.

I ordered it to get a jump start on CS325, but when I opened it my heart sank a bit: where the F*** was this book when I was still in CS261?! 

Every section of this book is concise and clear, with simple explanations and memorable illustrations. Most topics get multiple explanations and diagrams. The book covers so many topics and I’m just kicking myself for not having this book while I was still in CS261.

Grokking Algorithms has everything from visualizing Big-O runtimes…

to performing a search on a graph…

to adding neighbors as you traverse nodes on a graph…

to sorts, complete with graphs, Big-O complexities, and pseudocode…

to understanding how the stack can be used to perform a set of instructions recursively.

This is just a small sampling of what’s in this amazing book.

I wish someone had told me about this book before I waded through CS261’s long, tedious PDFs on these subjects (which often felt like they were written for people who already understood the topic).

Do yourself a favor. If you are about to take CS261, get this book and refer to it often. It is way better than the materials they provide you in the class.

CS261 uses C, not C++

I’m pointing this out because I see a lot of questions (and complaints) about the switch to C from C++ in CS261. It’s true: unlike CS161 and CS162, CS261 assignments use the C language. HOWEVER – it’s really not a big deal. Any panicky feelings you may have over this at the start of the course will soon pass.

Instead of this:

for (int i = 0; ...)

you’ll do this:

int i;
for (i = 0; ... )

And you’ll write comments like this:

/* comment goodies here */

Little things. Nothing you can’t solve with a quick Google query and by the 2nd or 3rd week you’ll probably forget you even went through this.

They ask you to use an IDE… but you don’t need to

This surprised me: CS261’s first week includes an assignment in which you bring your code into an IDE and then submit a screenshot to show you did it.

As far as I could tell, there’s no reason to keep using the IDE past week 1. You don’t have to choose a particular IDE (or an IDE at all) for the sake of CS261. The class doesn’t teach you how to use the features that make an IDE worth using, like using the IDE’s debugger to step through code.

I tried CodeBlocks for a week, but it crashed so often it was a serious roadblock to productivity until I dumped it and went back to Sublime Text. (I’m on a Mac and the last release of CodeBlocks is from 2013 – maybe that was part of the problem.) 

An IDE is just a tool, and I feel like which IDE you choose is a personal choice and none of the school’s business unless the class is going to teach specific features of that IDE or provide workflow tools that only work with the IDE, which CS261 does not. There’s no reason you should have to abandon whatever tools you’re already comfortable with. This was a strange requirement, in my opinion.

CS261 assignments

The 6 assignments are like “coding madlibs” – they provide you with a collection of files and you fill in the functions that are missing code. The assignments offer little opportunity for creativity, which probably makes grading them easier but also made them more forgettable.

The real shortcoming with the assignments was the missed opportunity to tie the data structures and sorts to real world applications. The greater context of why you might choose a particular data structure or how to analyze a problem and then decide what data structure or sorting algorithm to use is, sadly, largely absent in this class’s materials.

I felt like the “fill in the blank” nature of the assignments didn’t help my brain make links between Real Life Problems and All That Code I Wrote in a Vacuum for CS261.

Here’s a ton of code I wrote. What’s it for? Getting a good grade in CS261, of course.

This “missing context” was further illustrated to me when I told a friend about the class I was taking, and he posed a question:

"Suppose you have a million images. Filenames/filesize/etc. don't matter, just the content of the image. I give you a new image and you have to tell me if you already have this image in your set. How do you figure it out? What data structures do you use?" 

                                                   - my smart friend

CS261 isn’t going to prepare you for this kind of question.

The solution – and surely there’s more than one – to my friend’s question was basically to hash all the image blobs into a hash table, then hash the new image to figure out where it would go and compare it to what’s in the table. You don’t hash on filename or filesize or anything like that, because with image hashing, you want to go by the content of the pictures themselves.

I include this example not to point out what a dunce I am – I already know that – but to illustrate a missed opportunity in CS261 to tie the course material to real life applications. Here’s some more reading on the image hashing question, if you’re curious.

CS261 group work

The weekly group work is going to be either great or a chore depending on who you get grouped with.

In the first week of the quarter everyone was assigned to a group of 5, with the sorting criteria being our time zones and general availability as noted in a spreadsheet that the instructor shared the week before class started (hope you were checking your email!).

For 10 weeks, my group was responsible for submitting 2-5 completed “worksheets” each week by end of day Sunday and a typed log of our discussion, called “meeting minutes” (basically who said what). I use the term “worksheet” loosely, some of them were solidly in “assignment” territory.

In a perfect world, your group will engage in vigorous, insightful discussion on each worksheet, leading to new insights and reinforced learning. (Some of my classmates assured me this is what they were getting out of their CS261 group experience.)

In the actual world, your group will probably struggle to find a meeting time that works for everyone and cope with weekly absences from the group meeting. Our “discussions” were rather thin – we either agreed the worksheet was easy or hard, and then kind of struggled to find anything else to talk about.

I don’t blame my groupmates, I think the worksheets just didn’t lend themselves well to discussion. There wasn’t anything controversial or debatable about the worksheet problems, just cut and dry “fill in the blank” code.

CS261 exams

The midterm and final exams for CS261 proctored, so you’ll have to go to a nearby test center or get set up with an online proctor. Both exams are 1 hour, 50 minutes long.

The midterm is coding heavy – expect to write methods in Word docs, save out a pdf for each problem, and attach them to the test. Many questions require you to download an image or attachment from the test, open it, and answer the question in the test’s text box. This was a clunky workflow and I was constantly afraid I would accidentally navigate away from the test. The final exam, however, only required one external doc to be produced and attached, and only a couple questions required opening an attachment. (Phew.)

As for what to study: study trivia. There’s a ton of trivia sprinkled throughout this course. You’ll want to memorize the runtime complexity of every sort, when to use what data structure (according to the class materials), properties of each data structure, and so on. A lot of that trivia will show up on the test. I found it helpful to make notecards of little tidbits of info that are sprinkled throughout the lectures and reading materials – they helped, but I was still caught off-guard by some of the questions.

My CS261 flash cards and worksheet organizer were invaluable.

A few final tips for CS261

Do all the worksheets yourself, even if your group comes up with some kind of “worksheet rotation”. By the time this class was done I think I’d done every worksheet at least 2 or 3 times, because they’re good practice for the tests.

Use a visualization tool. This is the one I used – it’s not perfect (I wish it showed individual steps for things like AVL rotations) but it’ll help you check your work.

Print all the worksheets. I didn’t have a printer going into this class but I quickly saw the need for one. Most worksheets provide you with function signatures and helper methods, and your job is to “fill in the blank”. Therefore, I felt it was better to have the worksheet printed in front of me so I could fill in the blanks, rather than working in an editor (or worse, a word processing doc), separated from the rest of the worksheet.

Some of my CS261 final exam prep: I redid the weekly worksheets until I had them down cold, and I printed the questions from the practice final.

Practice on paper, and get fast. The tests are, collectively, over half your grade in this class, so your performance on the exams really matters. I felt pressed for time on both exams, finishing within 5 minutes of the time limit both times. I’ve never really been one to study for tests, figuring I should’ve learned the material during the class itself, but I had to buckle down and get serious about studying for CS261. I made flash cards, practice tests, and put probably 20 hours into studying for the midterm and over 50 into studying for the final.

Knowing the code (implementations) is important, but the final exam (which is 30% of the final grade) focuses more on knowing the step-by-step behavior of altering a particular data structure. Be sure you know how to draw specific kinds of trees and how they change when a node is removed.

Join the Slack channel! Lots of my classmates could be found there every week discussing the homework and answering each others’ questions.

Get the Grokking Algorithms book. Seriously, and while you’re at it, invent a time machine and tell me about this book before I take CS261.

And… that’s it for this quarter! I’ll be in CS325 Analysis of Algorithms next quarter (Winter 2018), so be sure to say hi if you see me (Mandi) on Piazza or Slack!

Reports from other students in the program

Are you also blogging about your journey through OSU’s eCampus CS program? Leave me a comment and I’ll add a link here!

Fixed: Missing images (403) when sharing WordPress posts on Twitter and Facebook

One of my WordPress-based sites uses this particular combination of plugins and utilities:

  • WordPress version 4.8.2
  • W3 Total Cache
  • MaxCDN

And this was the disappointing, image-less result I got whenever I shared one of its posts on Twitter:

Twitter/Facebook missing image debugging tools

Here are two tools I used while debugging my missing images problems on social media.

Rather than spam your friends or pollute your feed with tests, you can use Twitter’s own validator and Facebook’s sharing debugger to try your posts and see how they render.

Twitter: https://cards-dev.twitter.com/validator

Facebook: https://developers.facebook.com/tools/debug/sharing/ (you may need to hit the “Scrape again” button in between tries)

The Twitter/Facebook 403 fix

There were two problems with my site:

Problem 1: My site’s posts didn’t define any Open Graph images in the first place. I figured Twitter, Facebook, etc. were smart enough to scrape the post and pick an image all on their own, but it seems that’s not always the case.

To explicitly declare a social media image for each post, I installed the “Facebook Open Graph, Google+, and Twitter Card Tags” WordPress plugin.

Now, at the bottom of every post, is the option to explicitly define an image to use when sharing. This image can be larger than images you might normally include in a post (maybe even custom-made for the purpose) and need not appear in the post itself. (Unfortunately, you do have to go back and manually add an image to each post.)

After doing this I was still getting a 403 when previewing my post in the Facebook and Twitter tools.

Problem 2: The other part of the problem was with my CDN settings themselves. Twitter (and Facebook, etc.) aren’t actually allowed to link to images hosted on my CDN – they aren’t whitelisted. My CDN is set up to only serve images on my site itself, so other people can’t link directly to my CDN images and effectively steal the bandwidth that I pay for (truthfully, I wish people were this eager to link to my content).

I had to add Facebook and Twitter to my W3 Total Cache’s list of rejected user agents.

Under the Performance tab (left side of WordPress interface), click on CDN:

Then scroll down into the Advanced section and find “Rejected user agents”. Type facebook.com and twitter.com. These agents are not allowed to access files hosted within the CDN. (Which is what we want, because the CDN won’t let them do it anyway.)

You may need to also do Performance > Purge All Caches from the top toolbar in WordPress, too.

Finally, the Twitter and Facebook previews have images!

OSU eCampus CS225 Discrete Structures in Computer Science review & recap

This post is part of an ongoing series recapping my experience in Oregon State University’s eCampus (online) post-baccalaureate Computer Science degree program. You can learn more about the program here.

Six-word summary: Not as bad as I feared

Here’s the deal: OSU’s eCampus CS degree only requires one math class. A lot of people dread it, though, because it’s their first math class in years (or decades).

However, I am happy to report that CS225 is completely doable by someone who isn’t a natural at math. You would be hard-pressed to find someone with less of a math background than me, and yet, I managed a 94% in CS225!

Behold, my math “credentials”:

  • I had to take algebra twice (in 8th grade, then again in 9th)
  • I only earned a B in sophomore year geometry despite really trying
  • My last high school math class was Algebra II (another B)… not pre-calc, not calc, not physics, nothing past Algebra II
  • My only college math class (and I do mean only) was Math 118, and it was the sort of math class you take when you are pursuing a fine arts degree
  • I got a single digit score on my ALEKS assessment at the start of OSU’s CS program

Not my strongest subject, by far. But I think there’s plenty of room in programming even for those of us who suck at math, so if this is you, worry not – CS225 won’t derail your dreams.

CS225 Review

This was a good class. The homework problems made sense, the pacing was good, and the test questions were (mostly) recycled from the book, homework, and practice quizzes. The instructor seems to run the whole thing on autopilot but I didn’t need to contact her for anything.

There’s only a handful of homework questions due each week, but potentially dozens more you can do from the book and “last quarter’s homework” if you want. (I did all the extra work, and I think it really paid off at test time.)

Good news: this didn’t happen in CS225!

Class structure:

  • 10 weeks
  • 2 homework assignments per week
  • 1 quiz per week (7 total, no quizzes before/after midterm and none in the final week)
  • 1 midterm around week 6
  • 1 final in week 10

The tricky part about this class is the grade weighting. 90% of the grade is from quizzes and tests. The midterm is worth 30%, the final is worth 30%, and your 6 best-score quizzes are worth 30% (collectively). The homework is a mere 10% of your final grade. Since both the midterm and the final are proctored, you do have to know your stuff inside and out before you sit down to take the exam.

Fortunately, nearly all of the test questions were outright copies of, if not very similar to, questions you would have seen in the homework and practice quizzes.

If there’s anything this class isn’t so good at, it’s the video lectures. (Seems none of these OSU courses so far have decent videos.) On the bright side, YouTube is bursting with math enthusiasts who have made short, memorable videos on every topic this class will cover.

My little CS225 algebra prep guide

Many people ask online what they should study in preparation for CS225. I think the ALEKS modules are complete overkill. I found myself Googling these seemingly basic concepts that I completely forgotten in the 15 years since my last math class:

  • factorials – what does 5! mean?
  • adding fractions – a/b + c/d = ?
  • multiplying fractions – a/b * c/d = ?
  • dividing fractions – (a/b)/(c/d) = ?
  • factoring these things – (2a + 1)(a + 2)

And that’s it. The rest you can pick up in the class itself.

More CS225 tips

Here are my general tips for succeeding in CS225.

Do the extra assignments, too

Every week has the homework that’s required, and then a larger set of extra questions that are just for your own edification. I did those extra problems and found them worthwhile come test time. I also did all the practice quizzes.

Get a Chegg subscription

I never heard of chegg.com before I took this class (I found it through Googling my questions). $15/month gets you unlimited access to the book’s answer key. Not because you’re going to copy the answers and blow off the homework, no – you need to know what you’re working towards on every question you tackle. How else will you know if you got it right? :D

Warning: not every single answer is in Chegg. I think I found maybe 4 of my assigned problems that were just blank in Chegg. But.. chances are if you Google the problem, someone on the math Stack Exchange or their own blog has posted a solution. Or you can solve a different but similar one that does have an answer, and then take a pretty good guess at the one you’ve been assigned.

Get the physical copy of the book

Yes, this is the most expensive book of the entire program, but you can buy it used on Amazon (which is how I got mine, and the book was in great shape).

A physical copy is great. It’s easier to jump around in than a .pdf, you can sticky note various topics, and you can take it with you and study in more places! I also rented a .pdf version of the book, but it didn’t display well on my tablet or my phone.

Study hard for the tests – and attempt every question

The two proctored exams are 60% of your grade (30% is your 6 quizzes, 10% is the weekly homework). Some of the questions are taken word for word from the book or homework.

Here’s a hint: simply attempting an exam question is worth a lot of points. Every exam question is worth 10 points and they’re ‘free form’ (you type in a box, not multiple choice). Even if you can’t (or don’t have time to) solve it, explaining what you would do to solve it can get you 7/10 or more points on the problem. Therefore, the winning-est strategy is to pace yourself such that you have enough time to at least lay out the basics of your answer to every question.

I ran out of time on the midterm and had to slap together half-baked responses to the last three questions. However, since I was able to type out what I would do if I had more time, and still ended up with a 93% on the test. Phew!

I took the class by itself

I’m on the 4-year track and while CS225 is popular to pair with CS161, I’m glad I took it by itself. As I said earlier, I am not the greatest at math – so taking the class by itself helped me have plenty of time to dedicate to extra problems, studying for exams, and re-watching the same videos until I felt like I understood the topics.

And.. that’s it! My next class starts in autumn, so I’ll report back on my OSU studies in December.

 

OSU eCampus CS162 Intro to Computer Science II review & recap

This post is part of an ongoing series recapping my experience in Oregon State University’s eCampus (online) post-baccalaureate Computer Science degree program. You can learn more about the program here.

Six-word summary: Brutally paced pointers pointers pointers pointers

The beatings are over I survived CS162! If you were wondering where OSU’s eCampus computer science weeder course is, this is it.

The material is challenging and the workload is punishing. The video lectures are crap, but the book is good. The class isn’t well organized (expect poorly written assignments with unclear requirements). 10 weeks is nowhere near enough.

However: I learned a lot. I got faster than I ever thought possible and worked with topics I thought would have had their own standalone courses.

In this article: topics covered in CS162, survival guide, what I thought of the class.

Note: While I normally love to post code here, I can’t share any of my project code (against student code of conduct) or specific assignment requirements, test questions, etc.

Topics covered in CS162

The class is taught in C++ and teaches (or at least requires you to know):

  • pointers to arrays
  • pointers in arrays
  • pointers to pointers
  • pointers to instances of classes
  • passing pointers to functions
  • linked lists made of pointers
  • abstract class and subclasses
  • virtual functions
  • overloading operators (give == an exciting new meaning!)
  • polymorphism
  • writing functions that do something recursively, like reverse a string, sum numbers in an array, solve a math problem
  • data structures: queue, dequeue, stack, linked lists, circularly linked lists
  • sorting algorithms

    Equipment

CS162 is a programming class. Like CS161, I found my Macbook to be sufficient for this class.

I don’t know why they tell people to get a Windows PC and use Visual Studio. I didn’t need it and you won’t need it – there is no assignment based on Visual Studio knowledge.

In fact, judging just by Piazza posts, a number of students seemed confused by the extra files and the “another thing to learn” factor introduced by Visual Studio.

In case you are curious: I did every assignment in Sublime Text and a Terminal window.

Workload

At any given time expect to be working on a 1-week lab assignment and a 2-week project, and mid-course, a group project on top of the first two. You will also write a multi-page document (PDF) for each project with a test plan (input, expected outcome chart), your designs, ideas you changed along the way, reflections on the assignment. This document always took me at least 3-5 hours to complete to my standards. There’s a quiz every couple weeks (15 minutes, not proctored).

I’m a proactive student who started each week’s material the moment it was released, worked on it daily, and generally finished just moments before a new load of work was dumped onto me.

The first two weeks hit like a ton of bricks: a simple Langton’s Ant simulation by itself would have been a large assignment, but CS162 adds a user-navigable menu, input validation, user-adjustable parameters (dynamically sized board, variable turns to run, etc.), and other features that have to be designed, coded, tested, debugged.

Oh, yeah – it has a visual component, too.

There are also two lab assignments to complete while you work on the Langton’s Ant project.

This pattern continues until the end of the course. The toughest week was the one where I was working on a project, a lab, and a group assignment all at the same time.

CS162 Survival Tactics

It’s easy to find people complaining online about how disorganized and intense this class is but what I really wanted when I was in the thick of it was some help. (The OSU eCampus CS subreddit is a good place to look for advice on CS162, too.)

Here’s what I did (or wish I’d done sooner) that made CS162 more bearable.

Take it by itself (if you can)

I know there are financial aid reasons not to but if you’re normally taking 2 classes a quarter, consider taking CS162 alone (or at least with fewer classes than you normally would). It’s a ton of complex material – assuming you haven’t worked a lot with C++ pointers, memory, arrays, etc. yet – and the assignments are black holes for time.

Diagram things out on paper

I found many of CS162’s assignments too complex to hold entirely in my brain.

For example, here’s a crazy thing we did: create a circularly linked list (in C++), let the user fill it and delete nodes from it. When you exit the program, it has to unload from memory node by node. “Empty nodes” are marked with -1 for their values. This drawing I made helped a ton (actually, this was probably the 5th iteration of this drawing).

And here’s another one, this one showing how the heroes in the polymorphism project either rejoin the tournament queue or get kicked out to each player’s respective loser pile.

PS: Some people got really precious about their diagrams having to be works of art, but you don’t have to for these assignments. Just draw this stuff out, paper/pencil (or a little whiteboard) is sufficient, and plan to trash your first few drawings. If you need some material to bulk out your retrospective writing assignments for each project, photos of the diagrams you make are excellent fodder.

Go straight to Google (and Stack Overflow, and YouTube)

Sure, it sucks to pay $1880 to do so much self-teaching but you’ll get a lot of mileage out of the skill. The recorded lectures are crappy and I felt like they were wasting my time. There are better guides to all of the concepts covered in this class on YouTube.

Buy the book

Here’s the book on Amazon: Starting out with C++ Early Objects

I recommend getting a physical copy so you can have it open next to your computer while you work, and use your monitor(s) for your code and Stack Overflow. It’s the same book you use for CS161 (if you haven’t taken that yet, either), so at least you get two classes out of it. You can also buy the loose leaf edition to save some cash.

After two classes with it I can say it’s a good book. The examples are relevant to the coursework, they’re clear and easy to understand, and there are a good number of examples.

Start every week’s material the day it drops – and don’t count on weekends

Sometimes the new week will be released on a Friday. Sometimes a Saturday. Sometimes a Sunday! And no, the instructor doesn’t move that week’s deadline out just because you lost the weekend due to the materials not being released in a timely manner.

For this reason, I recommend setting up your life (at least for these 10 weeks) to allow for working on this class work during the week (ideally daily). Don’t expect to fit it all into the weekend. Even my light weeks were at least 15 hours spent on the class (usually closer to 25 or 30).

Check Piazza religiously

If they improve anything in CS162, I hope it’s the unclear assignment requirements. They requirements were notoriously bad – assignments are written in a conversational style and it’s hard to know what is really being asked for.

Many students took to Piazza (the class forum) to question (or worse, debate) the requirements until a TA or the instructor herself came along to clarify.

Pretty much every week went like this:

  1. New assignment comes out – oh no, it has unclear requirements
  2. Someone asks for clarification on Piazza
  3. TA makes a call and says do it X way
  4. 2 days later, another student asks the same question
  5. Different TA makes a different call on this new thread and says do it Y way
  6. They have a fight, triangle wins…
  7. You already coded it, so do you redo it to Y or keep it as X or what?

For what it’s worth, every time this happened (and I had already coded it), I kept my code the original way and still got 100% on the assignment.

Test on Flip!

So many things happened in my code in the Flip environment that did not happen on my Macbook. Weird things, too – things that weren’t easy to spot without thorough testing. I made the mistake of not testing on Flip until I thought I was done with the assignment, only to find heaps of bugs that only occurred in that environment.

Only after the class ended did I find out about this awesome way to use Git to deploy to Flip. (I was uploading in Filezilla like a n00b.)

Use Valgrind early and often

It’s a memory management tool that’s already installed on Flip and you won’t be able to find memory leaks without it. If you wait until you are done coding your assignment, it will be harder to find the memory leak. Upload your code and check it with Valgrind every so often and you will save yourself a lot of pain.

The worst weeks were weeks 1, 2, and the final project. 

Okay, they were all brutal. There were many weeks where I thought there was no way I would get everything done and I’m a workaholic. The biggest workloads were the first project (Langton’s Ant) and the final project at the end (make an adventure game).

You do actually have to contribute to the Canvas conversation topics

It’s a misguided requirement because these “conversation” threads quickly turn into 100+ people dumping commentary and links in one place. It’s not a conversation, it’s just a dogpile.

But if you contribute a few posts (I think I made 5 decent, paragraph-sized comments between January and March, evenly spaced throughout the quarter), it’s an easy 20 points that you will (probably) be very happy to have at the end.

Take every quiz twice

(Unless you get a great score the first time around, of course).

You get two attempts for each quiz. A quiz is 20 questions in 15 minutes. (I liked this format). Only the grade from the last attempt is kept. Questions are drawn from a larger pool, so you won’t get exactly the same ones the second time you take it.

My strategy was to use the first attempt to see what’s on the quiz and make my best guesses as to the answers. This gave me a feel for the questions and sometimes revealed tricky ones. I usually got about a 16/20 on my first try but Canvas tells you which ones you got wrong, and what the correct answers were, so this is a chance to see what you missed and study it some more. After a little studying I’d retake it, usually getting 18/20 or 19/20 that second time.

Grading

The final’s worth 15% of your grade, so save some steam for the end. There was a bonus, extra credit lab announced right at the last minute, too, but its points could only be applied to your lab grade.

  • Weekly labs – 30% of your grade
  • Quizzes – 15% of your grade
  • Projects – 30% of your grade
  • Group activities – 10% of your grade
  • Final project – 15% of your grade

Overall impression: OSU CS162

I hated it at times, but it was worth it. I never would have pushed through some of this stuff on my own, without a grade and a degree motivating me. OSU’s online CS is meant for people with a full-time job and the occasional signs of life outside of class but I had to take a couple days off work here and there to accommodate the workload, and I barely saw my family. The most difficult parts of this class – deciphering confusing and unclear requirements – were totally unnecessary. OSU should hire a proofreader and clean up CS162’s assignments. And some weeks the workload was just pure insanity – the week with the group project stands out in particular.

But… it’s over! And I got a 97%, so I’m going to take a much-deserved week to rest up for the next class.

Onwards to CS225!

Other student reviews of CS162

Leave a comment and I’ll update this post with your review!

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

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-

Screenshot 2017-01-21 20.11.59

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

Screenshot 2017-01-21 13.40.15

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…

Screenshot 2017-01-21 13

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…”

Screenshot 2017-01-21 16.29.24

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.

Screenshot 2017-01-21 16.53.25

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.

Screenshot 2017-01-21 14

Follow the prompts and choose your cleaned up .csv file. If everything works, you should end up with a screen like this:

Screenshot 2017-01-21 16.54.49

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.

Screenshot 2017-01-21 16.56.41

Here’s what you should have now: an editor pane and a bunch of results below.

mysql_queries_amazon_affiliate_data

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.

Screenshot 2017-01-21 20.40.39

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! :)

mysql_query_most_profitable_amazon_items

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!

most_expensive_item_amazon_affiliate_2016_my_data

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.

 

Passing a 2D array to a function in C++ as an array or pointer

I recently had an opportunity to experience the joys of passing 2D arrays (both as themselves and as pointers) to functions in C++.

Both of my examples here assume you already know the size of your 2D array (3×3, in this case).

int array[3][3] = {{0,0,0},{0,0,0},{0,0,0}};

Passing a 2D array to a function in C++

Notice the empty first set of brackets. For some reason, C++ only needs to know the columns. It can figure out the rows on its own (though it won’t break if you do include the row size).

int array[3][3];

void doStuff(int arr[][3]) {
   //accessing stuff inside it
   int someCellData = arr[0][0];
}

doStuff(array);

Passing a pointer to a 2D array to a function in C++

array[3][3];
int (*p_array)[3][3] = &array;

void doStuff(int (*arr)[3][3]) {
    //accessing stuff inside it
    int someCellData = (*arr)[0][0];
}
doStuff(p_array); //notice you pass it without the *

Additional notes on passing a pointer to a 2D array to a function in C++

When you declare and define the pointer to the array, you have to include the rows and columns sizes in accompanying square brackets.

If you don’t, you’ll get this error on that pointer’s definition line when you compile:

cannot initialize a variable of type 'int (*)' with an rvalue of type 'int (*)[3][3]'

So, don’t define your pointer to your 2D array like this:

int (*p_array) = &array; //wrong!

Do it like this:

int (*p_array)[3][3] = &array; //include rows & columns

More reading

These Stack Overflow queries helped me figure this out: