Greg recently wrote a post about The Tools We Use (for playing the game) that talked about many of the tools we use to earn more miles and points. In email chatter between the three of us (Greg, Stephen, and me) there was a lot of talk about various tools we use to stay organized in this hobby of collecting miles and points. One tool that #TeamFM relies upon highly is Google Sheets, which is a free web-based spreadsheet app (like Microsoft Excel, but free). Before I worked for Frequent Miler, I had a very basic understanding of how a spreadsheet worked. After almost two years here at Frequent Miler, I still feel like I have a very basic understanding as compared to Greg’s mastery of all things spreadsheet, but I’ve come to use them to stay organized. That is especially important for anyone doing any level of manufactured spending – small mistakes can wipe out the value of any rewards being earned. Today, I wanted to share a simple spreadsheet that could be used to help keep track of gift card purchases.
In our Manufactured Spending Complete Guide, we list a lot of different techniques – form healthy & easy to risky, difficult, or some that are totally dead. One technique listed under the “Healthy
but risky, difficult, or local” heading involves buying certain debit gift cards and using them to purchase money orders. This technique is highly location dependent; in some areas it is difficult or impossible to purchase either debit gift cards with a credit card or money orders with a gift card or both. Beyond that, there is the chance (likelihood in some cases) that your bank account may be shut down for depositing many money orders, etc. It’s worth reading the pertinent information in the Complete Guide before diving in (and even then it is advisable to crawl before you run).
That said, I know there are readers who use this technique. Just the other day in our Frequent Miler Insiders group, a member lamented the fact that he thought he had lost a couple of valuable gift cards. My stomach sank for them because that’s a fear that anyone who has bought gift cards has probably shared at some point (and in fact, we had gone through a brief similar scare in my household only a few nights prior before finding the cards in question had just been “put away” in the wrong place).
When I spoke at the Chicago Seminars in October, I mentioned the fact that I use Google Sheets to keep track of the cards I buy and how I use them. A couple of people asked me if I could share the spreadsheet with them. In the shuffle in the days following the seminars (I went from Chicago to the Conrad Fort Lauderdale, where I’ve previously shared that my family was taken down by what they are convinced was The Plague), I lost email addresses and thus hadn’t been able to share it with those who had asked.
Today, I’ve created a sharable version of my sheet that you can access here. To use it, you’ll first want to make a copy (File–>Make a copy) so that you can edit the cells as necessary.
The sheet itself is fairly basic. It includes columns for things like the date purchased, store, receipt number, etc. I record all of that information in the hopes that it might be helpful should I lose something and catch it fast enough to try to have the card replaced.
The column that says “Activation last 4” is where I record the 4 digits that show up on the gift card activation receipt. The “Card Number” field is one I’ve included but I think may or may not be wise to use. Personally, I wouldn’t want to record the full card numbers of a bunch of gift cards on a spreadsheet that is available via the Internet. That said, if you use some type of encryption (I believe Google Sheets does offer the ability to encrypt it), your comfort level may vary. The reason the column exists in the sheet is two-fold: by having the numbers recorded, you have the information you need to get the card replaced if you misplace it (and catch it before someone else uses it). You can also have the information you need to check balances and be sure that you’ve fully used your cards. If you’re going to record those numbers, I’d want to have your sheet password-protected with a strong password at the very least. Alternatively, you might consider keeping the sheet locally via Excel or something like that.
The column that says “Credit Card” is where I’d record which card I used to make the purchase. I have the sheet set up to then automatically fill in the “Rewards Rate” column based on the second tab of the sheet (titled, “Earn” at the bottom). You’ll see that tab is totally incomplete — I only list a few credit cards. That’s for two reasons: First, we list more than 150 cards on our Best Offers page and I was not going to enter all of them in the “Earn” tab. Second, I thought the sheet will likely be more useful if you spend a little time customizing it. Enter the cards you have on the “Earn” sheet and their base earning rate (1x, 1.5x, etc). Then, make sure to sort that sheet A->Z in column A (if you don’t sort it, Sheet 1 won’t populate the rewards rate properly):
Once you’ve sorted it, you should be able to go back to Sheet 1 and enter your card names and it will populate the base earn rate. Back in Sheet 1, column “N” is labeled “bonus”. This is where you would enter any bonus category points. For example, the Ink Cash card earns 4 additional points per dollar at Office Supply stores, so you’ll see that I’ve added 4 under the “bonus” section of row 5.
The formula in row O should then calculate the number of rewards you should have earned from your purchase. From there, following further to the right on the sheet gives you the ability to enter information about how you’ve used the card and at what cost so that you can calculate your cost per point.
You’ll notice the “#N/A” under columns M and O. That’s because I dragged the formula in those columns down — you can always copy the formula onward as you run down the sheet (right now, it will fill in those spaces as you enter the relevant numbers in those rows).
Note that this sheet is simple and isn’t exactly the same as the one I use, but it’s a starting tool for those who may find themselves in need of a bit more organization. The fun thing about Google Sheets is that you can totally customize yours to fit your needs, adding formulas and changing or deleting columns. A key to organization is making it your own so that it makes sense to your brain, so I expect that many people will have a different way to organize and/or want more or less detail, etc.
It’s also worth noting that these days, Amex has been known to deny welcome bonuses to those who it deems have met the spending requirements by purchasing gift cards. Also note that not all gift cards have PINs. Again, I’d suggest reading the guide and experimenting to see what works/doesn’t in your area. I’d also suggest being careful not to get too invested in something that can change at any minute. Have a backup plan or two or three when you buy gift cards.
I’d also love to get your suggestions / additions / changes / customization ideas as this is a document that could surely be improved upon. Again, it’s meant as a basic organization tool — and one that think everyone should develop in some form if you’re going to participate in collecting credit card rewards. Staying organized is one of the most important tools of the game, whether for these purposes or others and I’ll look to highlight some other organization tools in weeks to come.
Last updated on March 31st, 2019