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:

OSU eCampus CS161 Intro to 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.

With my successful completion of CS161, I am now 1/16th of the way to a Computer Science degree! Despite being labeled an “intro to CS” course, this class wasn’t a walk in the park – the two exams (worth 60% of the grade) were the tough part.

CS161 syllabus, topics

My CS161 was taught by Tim Alcon. He was remarkably responsive on the class forum (“Piazza”) and over email. There were something like 200+ students in the class, though, so assignment grading was done by a group of teacher assistants (TAs).

Syllabus: CS161 syllabus

Topics covered:

  • Introduction to C++ language
  • variables
  • methods
  • arrays
  • passing data into methods
  • data types
  • while loops, for loops
  • memory management (introduction)
  • pointers
  • object-oriented program structure
  • good coding style (comments, naming)

If you’re a prospective student reading this and these topics are all brand new to you, you should get familiar with them prior to the class. The class moves pretty quickly, especially past week 3.

These are all courses/video series I’ve worked through myself and recommend to anyone wanting to get started with programming:

CS161 class format

The course is 10 weeks long. Every Wednesday at midnight the next week’s homework and reading (called a “module”) becomes unlocked in the class portal (known as “Canvas”). You can do the reading/homework as fast or as slow as you want, as long as it’s turned in by the due date, typically the next Sunday or Wednesday (so you had either 5 or 7 days to get everything done before the next batch opened up). There weren’t any videos or supplementary materials beyond the book. Basically it’s read the chapter, do the homework, repeat. 

Early in the course, I found this drip-feed of content frustratingly slow. I wanted to work ahead and take advantage of free time when I had it. I usually had my assignments done within a day of their release. After about the halfway point, though, the class picked up the pace and I was turning in homework much closer to the Wednesday night deadline.

CS161 homework assignments

Every week had a reading assignment (usually 1-2 chapters from Starting out With C++: Early Objects) and a coding assignment. Some weeks also had a group assignment and/or a reflection assignment (1-2 pages written response). For the code assignments, you upload .cpp files to upload via “TEACH” (OSU’s interface for uploading homework).

The time commitment was reasonable, in my opinion. My daughter was born the same day class started so I had the advantage of being on maternity leave from work, but a newborn is demanding and I ended up squeezing homework and reading in anywhere I could, at odd hours of the day. In the early weeks of the class I spent about 4-5 hours a week on reading and homework combined. In the later weeks, I was spending 8-10 hours per week on the reading and homework combined. However, it’s worth adding that I’ve worked as a programmer (front end web developer) for the last 2 years, have already completed a web dev bootcamp, and have put myself through various online self-paced CS courses (this one just happens to be worth a college credit).

I thought the assignments were clear in their requirements and it was easy to understand what I needed to do.

The course materials suggest that you need a Windows machine and Visual Studio for CS161, but that’s not necessary – I did a lot of my homework on my Macbook and I never opened Visual Studio on my Windows machine. On my Windows computer, I used MinGW as my C++ compiler and wrote my code in Sublime Text. With this setup, I didn’t have to worry about Visual Studio adding anything extra to my files and my homework always compiled on Flip, the school’s Linux environment that you can log into and upload your project files to for testing. (This is the same environment the TA’s use to check if your work compiles.)

Some weeks had group assignments, which were coordinated over the Discussion groups in Canvas. The two major group assignments were basically “everyone upload last week’s homework, then decide amongst yourselves which one is best and write a page explaining why you picked it”. I found my classmates responsive and eager to complete the assignment; this wasn’t the group assignment horror-show you might remember from high school, at least not here in CS161 where it wasn’t about producing a completed project together.

Assignments were usually graded about a week or so after turning them in. Feedback was a line, at most, usually “Looks good!” or “You didn’t give this method the right name, -2 points”.

screenshot-2016-12-25-14-46-44

CS161 exams

The most important thing to understand about OSU CS eCampus exams is that they are “proctored”, which is academia-speak for “supervised”. This was my first experience with proctored exams. (And this will be the case of all exams for the online CS degree program, not just CS161.)

There are two proctoring options:

  • Take it in person at a local university/college/community college/test center (this is what I did)
  • Take it at your own computer with a live webcam stream of your face running the whole time with a service like ProctorU

I thought the ProctorU option was creepy as hell, plus I couldn’t guarantee the people I live with would actually be quiet while I took the exam. They require a 360 view of the room you’re in before you start, and any interruption is grounds for your test to be disqualified. Getting up to yell at someone / deal with a fire / etc is enough to make the person supervising you over ProctorU disqualify your test. Reading others’ first-hand experiences with ProctorU also put me off the service pretty quickly, but I never actually tried it myself.

Taking the exams at my local community college’s test center was easy once I confirmed with them that they were approved for OSU tests. I scheduled a time (they even had Saturday hours), showed up, sat down at a computer, logged into my OSU Canvas account, and started the test. The test is timed, and the test center provides paper and pencil (which they keep after the exam). The test runs in your web browser. My local test center charged $35 per test.

As for the exams themselves, I found them rather tricky. Each of the exams (there are 2) are 20 multiple choice questions with 4-5 answers to pick from. You have to hand-trace through the code (which is often iterative) and spot any errors that might be included in the code. (The paper and pencil help a lot here.)

In real life, you get the help of a compiler and unit tests (that I’m sure you wrote because you’re a good programmer) to help you understand (or debug) a bit of code, so the tests felt unnecessarily difficult and unlike my professional coding experience.

Here’s an example. One exam question had something like this (embedded in a much larger block of code):

if (x = 3) {
   ...stuff here...
}

I caught the single = sign, but I didn’t know it would actually do something in this case. In C++ (and other languages), this evaluates to true and sets x to 3. I’m just citing this as an example of how you have to know the fiddly bits of C++ to really excel at the exams. (I managed an 80% and an 85% on the exams.)

The test is graded as soon as you submit it, but you won’t know which ones you got wrong until they unlock the tests a few weeks after they’re done. Furthermore, while they’ll eventually tell you which questions you got wrong, they won’t tell you what the correct answer was.

screenshot-2016-12-25-14-43-32

So, don’t expect the exams to be much of a learning experience until the course has ended and you can work through the questions with an IDE and compiler.

Overall impression: OSU CS161

OSU’s eCampus Computer Science program looks solid from here, and I thought CS161 was a good value. The materials were well put together, the pacing was reasonably challenging, and the course’s time demands seem manageable for someone with a full-time job and family (I was on maternity leave while I took the course). I came into the class with more prior experience than the class is really meant for, but I still felt satisfied and challenged by the material. The OSU eCampus Reddit forum is a helpful resource for information on individual classes, admission process, instructor reviews, etc.

Onwards to CS162!

Review: Starting Out With C++: Early Objects, Student Value Edition (9th Edition) book

I got into Oregon State University’s post-bacc computer science degree program! Fall quarter begins September 21st, 2016 and I’m excited to start. (More on that later!)

This post, however, is about the book I needed for the first class, CS 161. The book, “Starting Out With C++: Early Objects (9th Edition)”, is available on Amazon, but it is expensive. And it has a 1-2 week processing wait.

That wasn’t gonna cut it – class starts next week.

starting-out-with-c-plus-plus-early-objects-book

I wanted a real dead tree book, not an ebook, and I live too far from OSU to “rent” the book from their bookstore for the quarter. My search led me to something called the “Student Value Edition” of Starting out with C++: Early Objects”.

The “Student Value Edition” is significantly cheaper than the usual version. But it had no reviews, and I couldn’t find anyone else talking about it online, so I was kind of hesitant to order it.

(I ordered it anyway.)

I’m happy to report that this book is the real deal.

2016-09-15-17-56-49

It arrived 3 days after I ordered it. It’s basically an unbound stack of papers 1.5 inches thick. Nothing holds it together except for the plastic sleeve it arrives in.

Other than that, though, it’s exactly the same content you’d get with an actual binding and cover. It’s in color, printed on both sides, and the individual pages are thin(ish) but probably no thinner than what’s in the actual book.

2016-09-15-18-09-58

An inexpensive 2″ slant-ring binder like this one solved the lack of binding problem.

2016-09-16-09-02-23

And there you have it – a slightly more affordable way to get CS 161’s book in paper format.

As for the book’s actual content, I’ll be working through it over the next 11 weeks and I’ll let you all know what I think. I’ve read the first two chapters and it looks like it’ll be a solid introduction to computer science fundamentals textbook.

If you’re starting CS161 soon and want to save some money on the book, check out the “Student Value Edition” of Starting out with C++: Early Objects”.

How to see an app’s jQuery version in browser console (Chrome)

Recently, I was working through a dependency conflict in an Ember app. I needed to verify which version of jQuery was in use by the app to see if it matched what I expected it to be using.

Complicating things was the fact that the Ember app in question uses a proprietary UI element library (like Bootstrap, but specific to the company developing the app) that has its own version of jQuery.

In your browser’s console (I did this on Chrome), use this to see the page’s jQuery version:

jQuery.fn.jquery
"1.8.2"

And use this to see the version of jQuery in use by your app’s version of Ember:

Ember.$.fn.jquery
"2.1.4"

Why are the versions different?

As far as I can tell, it’s because in my case the “vanilla” jQuery was coming from the UI library, and the Ember version of jQuery was the one defined in my Ember app’s bower.json file.

There isn’t more to this story; it’s just a useful set of commands that might save someone else some digging.

Ember: Using queryParams to distinguish “editing a record” from “making a new record” on a route

This guide uses Ember 1.13

ember_logo

The concept is simple and familiar: you have a page (“route”) in your Ember app that you want to use to either enter new data or modify existing data on the same record.

Example: your user is presented with a blank form to enter their shipping address.  The user enters their info and submits it (thus creating a new record). When your user goes to edit their saved shipping address, that same form is populated with their existing shipping information (loading a record). The user edits, saves, and the updated record is persisted.

One form, two behaviors (new and edit). 

This, like many things in Ember, was surprisingly painful to get working. I found queryParams to be a good solution, but the documentation on queryParams was difficult for this beginner to understand and it contains (what I think is) at least one mistake that held me up for a long time while I figured it out.

It didn’t take much code to get this working, just a lot of guessing and trying, so I thought I’d add my knowledge to the (vast, mostly out-of-date now that it’s 2016) library of Ember queryParms knowledge out there on the internet. Hopefully this helps someone.

About this tutorial

In this guide, I’m going to show you how I used queryParams in Ember to distinguish the “new” state from the “edit” state on the same route (“basics”).

About the example app

This guide uses my Ember app, “Classis”, for many of its examples, which you can explore in full on Github (it’s a work in progress but the queryParams part is done on the “basics” route if you want to look at the app that inspired this post).

“Classis” is a (fake) web app where people sell “seats” in classes they teach: tutoring, dog training, etc. It’s a lot like listing a room on AirBnB, dog boarding on Rover.com, etc. Think of classes as products people are selling, and you’ve got the idea. I call refer to “classes” as “products” in this tutorial to cut down on confusion with the programming concept, but they are called classes in my app. 

About queryParams in the context of editing records

You can use queryParams for lots of things. This particular guide shows you how to use them for editing records that have ids.

If the user is trying to edit an existing record, we can get its id and pass it along to the route where the user will edit that record. If the route sees you gave it the id, it will behave differently.

The “I’m making a new one” url looks like this:

http://localhost:4200/basics

The “hey, I’m editing a specific one” url looks like this:

http://localhost:4200/basics?id=1

Pass the queryParam from somewhere

There are lots of ways to pass a queryParam around, but in my case, I needed to pass it from an “Edit” button contained within a “class-card” component. That component’s job is to display a user’s completed product, and one “class-card” is displayed for each product the user has. Product data is passed into a class-card, and that product data includes the id.

Basically, I have a product with an id. The component knows about the product’s data, including its id. I want to pass that id to the basics page where I will edit the basic info about a product.

(I simplified the styling classes in this example and bolded the relevant part)

components/class-card.hbs

{{#link-to 'basics' (query-params id=class.id) class="mdl-button"}}
  EDIT
{{/link-to}}

Now the class id (product id) will get sent to the ‘basics’ route.

You can also pass queryParams on a transition in, say, an action. Here’s a hypothetical action method I made just for this post, it’s not in my app anywhere but I included it here in case you need to pass a queryParam from an action:

goToBasics() {
  const route = this;
  const id = this.controller.get('id') || '';
  route.replaceWith('basics', {queryParams: {id: id}});
 },

Define the queryParam on the “parent” controller

There are many ways to structure an Ember app, and I can’t claim to know them all. I do know, however, that my app uses application.js as its parent controller, so this is where I defined queryParams.

That definition looks like:

application.js

import Ember from 'ember';

export default Ember.Controller.extend({
 queryParams: ['id']
});

On a complex app, you might bury this deeper down inside the app, so that (perhaps) user ids don’t get confused with product ids or whatever, but on this simple app, the parent-most parent can handle it.

Add the queryParam to the route in router.js

All of my app’s routes are defined in app/router.js.  ‘basics’ is the route that you can pass an id to to distinguish “new” from “editing”.

Contrast with location/:id, which treats id as a dynamic segment (link goes to Ember documentation on the subject).

I included this here to illustrate the difference, because it’s easy to confuse the two (like I did when I was getting started with this stuff).

app/router.js

Router.map(function() {
  this.route('basics', {queryParams: 'id'}); //queryParam
  this.route('location', {path: 'location/:id'}); //dynamic segment
  this.route('main');
});

Url with a query param looks like:

http://localhost:4200/basics?id=1

Url with a dynamic id segment looks like:

http://localhost:4200/location/45

Long explanation of why I did it this way:

In my app, the user goes from basics to location, in that order. Basics is the first page the user encounters. It can be used to either create a new record or edit an existing. Location, by contrast, is always used to edit a record that exists, because that record was created by basics, the page before it. If you’re on /location, it’s because you are making changes to a record that has already been created, even if you’re entering location data for the first time.

In Ember, having some understanding of what order pages are encountered in can help you make these sorts of architectural decisions (at least, until your designer decides location should come first, then you get to change this stuff or maybe handle everything with a queryParam in the first place, or whatever makes sense for your app).

Add the queryParam to the route itself

Basics is where the user edits “name” and “description” of an individual product.

Inside Route.extend, define the queryParms object like so:

routes/basics.js

export default Ember.Route.extend({
  queryParams: {
    id: {refreshModel: true}
  },

Now /basics knows that it might sometimes get passed an id, and if it does, it should refresh its model.

Get the queryParams inside the model

!!This part was hard to figure out!!

Ember’s documentation just shows one parameter passed into model, but try as I might, param just came in as a useless empty { } object. I could see my queryParam in the url, but I couldn’t get at it from param.

After much Googling, I found the answer: the queryParams are on the second parameter accessed by model, which you can name transition:

(still in) routes/basics.js (right below the queryParams object you just made)

//queryParams are contained inside transition, which has to be passed as    the 2nd parameter to model

  model(param, transition) {
    let id = transition.queryParams.id;
    if (id) {
      return this.store.peekRecord('class', id);
    }
  },

IT WORKS!

I’m not sure if it’s my Ember newbieness or an actual oversight in the 1.13 documentation, but model(…) {…} totally takes more than one parameter and the second one contains that sweet, sweet queryParam you passed in.

The code in my example above looks at transition, sees if it contains an id, and if it has an id, it gets the matching ‘class’ record out of the store.

A lot of Ember tutorials perpetuate the idea that if you do model(param), the queryParam will be accessible on it, so maybe this worked at one point in Ember’s history or I just set it up wrong in mine.

Curiously enough, the guide to model hooks that enlightened me to the transition parameter also says there’s a third parameter for model straight up called “queryParams”(!!!), but in my case, that third parameter comes in empty. Only the second parameter (transition) had anything useful. I cannot explain this, I’m just reporting it in case it’s useful to someone else. (Maybe someday I will understand all of Ember’s mysteries.)

Load existing record data in setupController (but only if it exists)

Almost there: let’s load that model data into the controller now that we have it.

(still in) routes/basics.js (below the model method) 

setupController(controller, model) {
  let application = this.controllerFor('application');
  application.set('pageTitle', 'Basics');

  if (model) {
    //we're editing an existing class
    this.controller.set('existingClass', model);
    this.controller.set('editingExisting', true);
    this.controller.set('title', model.get('title'));
    this.controller.set('description', model.get('description'));
  } else {
    //this is a new class
    this.controller.set('title', '');
    this.controller.set('description', '');
  }

Now, when we visit /basics with a queryParam in our URL, we’ll see our product’s title and description! If we visit /basics without a queryParam, title and description will be empty.

We are almost done…

Update the existing Ember record if we’re editing an existing one, create a new record if it’s a new one

Finally, we need to tell our route what to do when continuing past this page. If we have a queryParam, we want to update that record and move on to /location. If we don’t have a queryParam, we want to make a new record and move on to /location.

routes/basics.js (in the action hash now) 

actions: {

  transitionToNext(newClass) {
    console.log("going to location");
    this.replaceWith('location', newClass);
  },

  failure() {
    console.log("failed!");
  },

  continue() {

    //editing an existing class
    if (this.controller.get('editingExisting')) {
      let existingClass = this.controller.get('existingClass');
      existingClass.set('title', this.controller.get('title'));
      existingClass.set('description', this.controller.get('description'));

      existingClass.save().then(() => {
        //go to location and pass along the this existing class's id
        this.replaceWith('location', existingClass.get('id'));
      }).catch(this.failure);

  } else {

    //making a new class
    var newClass = this.store.createRecord('class', {
      title: this.controller.get('title'),
      description: this.controller.get('description')
    });

    newClass.save().then(() => {
      //pass along the newly created class's id
      this.replaceWith('location', newClass.get('id'));
     }).catch(this.failure);
    }
  }
}

It’s the biggest block of code in this guide but it’s straightforward: if you have the editingExisting flag set in the setupController, set the updates and move onto location. If you don’t have this flag, make a new record.

Debugging advice

If you get stuck, remember you can look in the Ember Inspector to see if your records actually exist and confirm that they have the IDs you expect.

ember-data-record

You can also console log the id at various points in your code to see if it’s coming where you expect it to.

You can also display the id in the template, which I found helpful in confirming that what I was seeing was indeed, what I was getting, like so:

ember-display-class-id

I click EDIT and that same ID is now in the url:

ember-queryParams-url-example

That’s it!

This post was inspired by my work on a personal project I keep publicly available here on github – check it out if you’re new to Ember and want to see a relatively simple Ember app demonstrating queryParams and other concepts.

Ember: Making a set of radio buttons that change a value set on the controller

This guide was written for Ember 1.13

ember_logo

So, Ember doesn’t have anything in the way of built-in radio buttons, but there’s a great add-on called ember-radio-button, which you can install via ember-cli:

ember install ember-radio-button

In this particular tutorial, we’ll use ember-radio-button to set up two radio buttons that work as a pair (so only one can be “on” at a time) and set a variable on your controller based on which one the user picks. We will also set one of the radio buttons to be on by default.

Like this:

ember-radio-button-tutorial

This guide assumes you already have a template (yourpage.hbs) and a corresponding route (yourpage.js) to work in.

In Ember, adding your code to templates and routes is kind of chicken-and-eggy (where to start?), but I tend to start in the template (.hbs) file.

In your route’s .hbs file

Use the {{#radio-button}} label {{/radio-button}} syntax if you want labels for your radio buttons (which you probably do).

This will give you two side-by-side radio buttons:

{{#radio-button
  value="vanilla"
  groupValue=flavor
  changed="flavorToggled"}}
  Vanilla
{{/radio-button}}

{{#radio-button
  value="chocolate"
  groupValue=flavor
  changed="flavorToggled"}}
  Chocolate
 {{/radio-button}}
  • value is what will get automagically passed to the controller when you click this radio button
  • groupValue should match across all radio buttons in the set, or your radio buttons won’t know to un-select themselves when you click a different one
  • changed is the action method called when this radio button is clicked

When the user clicks one of these radio buttons, flavorToggled(choice) will be called and the value passed to it as choice will be “vanilla” or “chocolate”.

In your route’s .js file

Add a new action to your action hash. You can call “choice” anything you want (or omit it entirely if you don’t need a passed-in value for whatever reason).

The value that gets passed in here as “choice” was taken from the radio button’s “value” property.

actions: {
  flavorToggled(choice) {
    console.log("changing flavor choice", choice);
    this.controller.set('flavor', choice);
  }
}

If you want one of these radio buttons to be checked by default, you can do that in the setupController in your .js file like so:

setupController() {
  this.controller.set('flavor', "vanilla");
},

All done!

That’s all there is to it! The documentation that comes with ember-radio-button shows a few more things this tutorial did not cover. Thanks, yapplabs!

 

Ember: Making a button component that calls an action

This guide uses Ember version 1.13.

ember_logo

I recently started a new Ember 1.13 app to explore Google’s Material Design Lite style library. I use Ember in my day job, too, and something that has always stuck out to me as “should be easy but isn’t” in Ember is making a button component that calls an action. 

I wanted a “continue button” component that…

  • can be re-used throughout my app
  • calls an action customized per route (ie: the continue button on the ‘basics’ route goes to the ‘location’ route, the continue button on the ‘location’ route goes to the ‘students’ route, etc)

This was surprisingly unintuitive in Ember. I expected to pass an action into the component from the parent template and have the component know where to find that action.

What not to do

I think many people who try to put an action on a component start with something like this. Note that this approach doesn’t actually work:

badExampleRoute.hbs

{{bad-example-button action=”wontWork”}}

badExampleRoute.js

actions: {
  wontWork() {
    console.log("you'll never see this!");
  }
}

What’s missing? The call to the route’s action has to come from the component itself.

The rest of this guide will show you what does work for getting a component to fire an action on the parent’s route in Ember.

Generating the button component

Do this step inside your Ember project directory (assuming you are using ember-cli):

ember generate component continue-button

In the route’s template

First, add the component into your .hbs template. The action it calls, “continue”, needs to be defined in the action hash in this route’s .js file.

basics.hbs

{{continue-button actionToCall="continue"}}

In the route’s js file

In your route’s .js file, create an action hash if you don’t have one already (actions: {…}) and put a “continue” method inside it. I like to stick a console log in here, too, on the first try so I have something to look for in Chrome to confirm it’s working.

basics.js

actions: {
  continue() {
    console.log("continuing on to location page");
    this.replaceWith('location');
  }
}

Code for the button component

When you generated the button component you got an .hbs template file and a .js file.

continue-button.hbs

The important piece here is that the <button> tag contains {{action “doButtonThing”}}.

<button {{action "doButtonThing"}} class="mdl-button mdl-js-button mdl-button--raised mdl-button--accent">Continue</button>

continue-button.js

doButtonThing is an action on the component’s own action hash. When you call doButtonThing, it’s going to fire a sendAction that references the actionToCall defined up on the component in the route template.

import Ember from 'ember';

export default Ember.Component.extend({
  actions: {
    doButtonThing() {
      this.sendAction('actionToCall');
    }
  }
});

Yes, this is confusing as #$*! and I’ve not seen a better way to do this in my year or so of developing in Ember. It feels very much like a child (the component’s .js) is telling a parent (the route’s .js) what to do.

Another way to think of it

The {{continue-button …}} up in basics.hbs knows what to do when clicked (call “continue” action in basics.js) but it’s not going to do it until told to do it. It sits around listening for the “fire!” command.

When the user clicks the button, the code on the button component itself has its own action to call. That action yells “fire!” to the instance of the button waiting up there on the route. The {{continue-button …}} up in basics.hbs hears the fire command, delivered from the component itself in the form of a send-action.

There is no “passing” of an action into the component, it’s more like a pairing of broadcaster and listener. 

The Ember community calls the larger concept at work here “data down, actions up” which took me a while to internalize. You can read more about DDAU here.

What to change in MaxCDN settings after you change web hosts

I just moved one of my blogs to a new host (yay!). This blog uses MaxCDN for its content delivery, and moving the blog to a new host messed up the site’s styles and it took me a while (plus some back and forth with support) to get everything fixed because MaxCDN was still referencing the old host.

maxcdn_logo

In case I ever do this again, here’s what needed to be done to move my WordPress blog to a new host with MaxCDN as my content delivery network.

Step 1: Add a CNAME record to your new host

My new host has CPanel (Digital Ocean, by contrast, had a Networking tab with a link to Domains and their records were accessible through there). If you have CPanel, click on Simple DNS Zone Editor.

Add a new CNAME record. It’ll probably look something like this:

Name
cdn1.yoursitename.com.

Record
cdn1.yourbusinessname.netdna-cdn.com

(Your CPanel might add the . at the end of name for you, and it might autocomplete for you if you just type the subdomain portion and then tab out of the field.)

MaxCDN has good documentation on updating CNAME records for a variety of hosts, too.

Note: I use a custom domain in MaxCDN because I don’t want it to use the default “business name” URL that MaxCDn gives you.

Step 2: Update the Origin IP over in MaxCDN’s settings for your pull zone

Go to Zones > Pull Zones > Settings and get into that particular pull zone’s settings. At the bottom is Origin Information. Check the checkbox and enter the IP address for your new host. Click Update button to save.

Step 3: Whitelist your new IP

Go into your Account > look under the API section > click Manage > add your new IP as a whitelist IP.

You may also need to whitelist your own IP address, if you get problems with cURL requests failing when you try to clear CDN cache.

Step 4: Update your WP caching plugin

You may need to reconfigure your WP cache plugin. I use W3 Total Cache which, for reasons unbeknownst to me, likes to replace my entry for “Replace site’s hostname with:” with the word “Array” instead of the URL I give it.

For reference, “Replace site’s hostname with:” should be followed by your cdn url, like cdn1.yoursite.com.

Step 5: Purge all caches and check your site

When you’ve done all of the above, purge your CDN cache and your WP cache via your caching plugin.

It might also help to flush your local DNS. I’m on Windows and I do that in a command prompt with ipconfig /flushdns

Open a Chrome incognito tab and load your site – if your styles and images load, you’re good to go. If your site looks incomplete, look in the console for an error message. I found many of them (like 502 bad gateway) to be covered in MaxCDN’s documentation.

More tools for debugging DNS, caching issues

If you’re having problems, try these tools:

Did it propagate yet? Whatsmydns.net is a great way to check propagation and see what is actually getting served when you try to hit your CDN’s URL.

If you are using a custom domain with MaxCDN like I am, then putting that custom domain into whatsmydns should yield the actual “business” domain in the results list. In other words, if you search for cdn1.yourcustomdomain.com and you get responses of cdn1.yourbusiness.netdna-cdn.com, you’ve got it set up correctly.

What’s your site’s IP? In a command prompt / Terminal window, ping yoursite.com to get its IP address.

Is your CDN URL responding? In a command prompt / Terminal window, ping cdn1.yourcustomdomain.com and see if you get anything. If it can’t find your host, this could indicate an error with your CNAME record with your new hosting service.

Are you seeing stale or current stuff in your browser? I use Chrome incognito because each window starts with a fresh cache and no cookies. CTRL SHIFT N opens up a new incognito window.

You may also want to flush your DNS in between tests. ipconfig /flushdns does this in Windows.

If all else fails, email MaxCDN’s customer support. Even on a US holiday, I got a response within 20 minutes and they helped me get things working again.

 

Tutorial: Easily move a WordPress site to a new host with minimal downtime using UpdraftPlus

Moving a WordPress site from one host to another with minimal of downtime doesn’t have to be a huge hassle, and it’s easy to do it yourself even if you aren’t a web developer. Here is the process I use to move a WordPress site to a new host, with about 10 minutes or less of actual downtime (and because of caching, many visitors during the migration may not even see the outage).

I like this method because:

  • It’s easy
  • You don’t have to mess around in MySQL
  • It’s free
  • It’s maybe 10 minutes of downtime for your site, depending how fast you can upload your backup and how much you have to do to get your caching plugin/CDN (if you have them) on board with the new IP address

Before you begin, make sure you have:

  • A WordPress site on your current host
  • Access to your new hosting account (preferably with CPanel and phpMyAdmin to get the most out of this guide)
  • Access to your domain’s DNS records (yoursite.com may be registered with your current host, or a separate registrar)
  • Nameservers for your new host (they usually look like ns1.newhost.com)
  • FTP access to old host and new host via your choice of FTP software (I use Filezilla) *optional* – you can do the same stuff through your host’s CPanel File Manager if they have it
  • About an hour of time to dedicate to reading this guide and the actual migration

Step 1: Install UpdraftPlus plugin on your site

updraft_plus

Log into your WordPress dashboard (http://yoursite.com/wp-admin) and install the free UpdraftPlus plugin.

This plugin is awesome and I recommend it for use outside of just moving your WordPress site to a new host. Here’s why:

  • The backup files do actually work (this plugin has saved my ass a couple times now)
  • You can use it to make a manual backup of your site at any time
  • You can set it up to create automatic backups and put them on the cloud storage service of your choice (personally, I back up to Google Drive)

Step 2: Use UpdraftPlus to make a backup of your site

Use Backup Now to start the backup process.

move_wordpress_site_new_host_updraft_step1

I like to do this right before I’m ready to start the migration process, so the backups are as fresh as can be.

Step 3: Download your backup files

Go back to your UpdraftPlus plugin page and go to the Existing Backups tab. Find today’s date and click each of the buttons (database, plugins, themes, uploads, others).

move_wp_to_new_host_easy_no_downtime_save_backups_updraft

Updraft will prepare each backup file for you (there are 5 total). Wait for Updraft to prepare the files, then click Download to your computer for each one.

move_wp_new_host_easy_download_each_backup_file

You’ll get 5 compressed files:

save_backup_files

Step 4: Set up an account with your new host and install WordPress there

If you haven’t done so yet, sign up for an account at your new host.

I’m digging SiteGround as my host these days, and I’ve already moved several of my sites to SiteGround (including my top money-making blog).

I use the StartUp package for my up-and-coming sites, and the GoGeek plan for my top performers. You can upgrade your plan at any time as a site grows. I especially like the GoGeek plan because they throw in SSL for free (or at least they did for my first year) and because it has a separate staging environment for testing stuff on a copy of the site before pushing it live.

Next, install WordPress on your new host. Many modern hosts (including SiteGround and BlueHost) have an easy one-click install for WordPress nowadays – look in the CPanel or just the dashboard in general once you’re logged in.

Don’t worry about picking a login/password you want to use in the long run, your Updraft backups will replace whatever you choose during setup with whatever your existing site already has. Do write down whatever name/password you choose here, you’ll need it to access your new WP install until you overwrite it with your backups.

successful_wp_install

It’ll probably tell you the installation was successful and you can go see it at the following url, but that link won’t work because you haven’t updated your domain’s nameservers yet.

Step 5: Change your domain’s nameservers

I do this in dynadot.com’s domain manager because that’s where my domain is managed, but your domain may be attached to your old hosting. In any case, change its two nameservers from ns1.oldhost.com and ns2.oldhost.com to ns1.newhost.com and ns2.newhost.com (or similar).

It should propagate fairly quickly (check it here: https://www.whatsmydns.net/) but it may take a while to see the change on your machine. One way to speed it up (on Windows, anyway) is to open a command prompt (cmd) and type ipconfig /flushdns.

Load your site again (in an Incognito window in Chrome or after clearing browser cache) and you should now see your new WP install.

Step 6: Install UpdraftPlus on your new blog and restore backups

Now that you have dashboard access to your new WordPress installation, install UpdraftPlus and click Restore.

updraftplus_restore

Drag your 5 files here and wait for them to upload.

updraft_restoring_backups

When those are done uploading, click Restore.

restore

Follow the prompts until you’re force to re-log in to your site. It should now look exactly like it used to on your old host, but you can confirm that it’s actually on your new host by pinging it in a command prompt or Terminal window (ping yoursite.com). If the IP address returned matches your new host’s, you’re good to go.

Extra step for CDN users:

I’m on MaxCDN, but regardless of what CDN you use (if you use one) there will probably be some additional setup steps to make sure your existing CDN account references your new IP and host.

I went through this process for MaxCDN and documented it here: http://www.tilcode.com/what-to-change-in-maxcdn-settings-after-you-change-web-hosts/

Step 7: You may need to do some other setup on your new host

Leave your old host active for a little while while you confirm everything’s working on your new site over the next couple days.

You may need to move the following separately:

  • Email accounts. If you had email accounts set up at your old host, take note that they don’t come with the Updraft migration and you’ll have to recreate them (and redo any redirects) on your new host.
  • Favicon: if your site had a custom favicon sitting in your site’s root directory, you might need to copy it from your old host and upload it to your new one
  • Google Analytics .html file: If you put any .html files for analytics tracking (Google Analytics is the one I always have to move manually) in your old site’s root folder, you will have to copy them to your new host
  • Robots.txt and anything else sitting in root (this will vary by site)
  • Images or other media in dedicated folders: Anything that’s part of your site but not part of WordPress will have to be manually moved. For me, this is sometimes a site logo or images on the site that I keep in a separate images folder, not uploaded to WordPress’s file manager.

If you’re afraid of losing anything off your old site, take the time now to download a copy of its entire directory off your old host, before you shut down your old hosting account. That way, if you find something missing later on, at least you can dig around the old files and maybe find it.