How I keep track of all my manufactured spend, portal shopping, and reselling

Introduction

When I started this blog nearly a year ago, I knew I wanted to keep track of all my MS and more importantly, shopping portal purchases for reselling.  I initially learned about this through a post by Mileonomics.  He was tracking individual VGC’s that he bought at the time, which I thought was too tedious.  However, using it to track online shopping was genius.  Thus, it took me a while to create the form, and it went through many revisions at the beginning, but these days, the only updates I do is add new credit cards to the drop down lists.  Thus, I present to you, my Spending Tracker Google Form.

Creating a Google Form

The reason why I wanted to create this in Google Drive is because I wanted to be able to access this a) on my home computer, b) on my home laptop, and c) on my work laptop.  I could make purchases from any 3 of those locations, and so I need it to be accessible anywhere.  The great thing about the Google Form is that you can set in pre-filled entries so that you don’t have to type “Chase Sapphire” all the time or have misspellings like “Chase Saphire.”  The last thing you want to do is do cleanup with your data.  So how do you get started?

First off, you will need Gmail.  And then in Google Drive, click on ‘New’ and go down to bottom and choose ‘Google Form’

 

My Form

Sorry, I’m not going to circles and arrows you into creating the form, but it’s EASY and SELF EXPLANATORY.  I’ll show you what I use and then you can create the same thing or make it better.  Let’s start off with what I have and the screenshots are below:

• Type of MS – Spend would be if I bought $2K of VGC using my WF card.  Portal & Spend is the one I use most often.  I haven’t chosen cc signup and Free Points, so I should really delete those.
• Store – Self explanatory.  This is a free form field.
• Portal – This is a drop down of all the possible choices – TCB, Ebates, AA, AS, SW, Discover Deals, etc
• Portal Rate – Free form field.  I will usually either type 10% or 10 for 10X.  The reason for this is that some sites are cashback and some are points.  I’ll explain more on this later how I differentiate the two possible entries.  Later on in the Google Sheets, I do an IF formula on this field.
• Qualified Portal Amount – This is usually the subtotal (remember portals don’t pay out on tax and shipping)
• Total Spend – This is what was charged.
• Payment used – This is whether I used a gift card, used my Chase Ink, Arrival, SPG, etc.  I should convert this to a drop down list (It wasn’t that big when I started this)
• Payment Multiplier – If I bought something at Staples.com and used my Ink, I’d put 5 here.
• Items Purchased – What you bought
• Portal Status – I always check Tracked so not sure why I even have ‘Not Tracked’
• Order # – Self explanatory
• Comments – I’ll use write ‘wife’s AA portal’ or ‘wife’s Kohl’s account’ just so I know
• Liquidation Cost – I kept this here when I was doing WF 5%, but now that I don’t do that anymore, I don’t use this field.
• SUBMIT – When I fill in everything, I hit SUBMIT

 

My Google Sheet

Now when I submit my entry, it goes into a Google sheet.  I can then open the Sheet and see something like this.

 

If it’s a shopping portal purchase, when I receive the confirmation email, I’ll go into the sheet and change the status to ‘Pending.’  I have some conditional formatting to make the cell GREEN.  If Best Buy cancels my order, I’ll go in and put ‘Cancelled’ and make it red.  This is how I track my portal purchases.

 

Fun with Formulas

You can see the peach colored columns on the right.  Those are where I put in my formulas.

• Portal Type – Possible values are Cash or Points based on a formula that says if the Portal Rate < 1, then “Cash” else “Points.”  Makese sense right?  No portal gives 100% cashback, it’s usually 10% or 2%, which is 0.1 and .02, which is less than 1 and hence it’ll be “Cash” whereas most portals are 1, 5, 10, etc which are Points based
• Total Portal Points – Simple formula of ‘Portal Rate’ * ‘Qualified Portal Amount.’
• Spend Type – Similar to Portal Type.  If the payment multiplier is say 5% aka WF 5%, then that’s cashback.  If it’s 1, it’s points.
• Total Spend Points – Simple formula of ‘Total Spend’ * ‘Payment Multiplier’

One thing to note is that when I submit a new purchase, these peach colored cells aren’t prefilled in.  I have to go into the Sheet and drag the formulas down.  It’s pretty easy.

 

Conclusion

While this took a while to set up and get going, it’s been very useful for me to track portal purchases and so on.  I also have another sheet for Gift Cards and another for reconciling FBA sales.   Down the road, I’d want to marry all 3 together.  The issue is right now, if I buy a $200 Best Buy gift card for only $180 (for the AMEX offer,) when I fill in my form, I put my ‘Total Spend” as $200 but in actuality, due to the 10% discount I got on the gift card, my ACTUAL SPEND is only $180.  The only way to do that is to marry the 2 sheets so that I know what gift card I used with every order.  Then when I sell an item on FBA, I am writing down my COGS as the invoice price, but I need to take into account portal discounts as well as GC discounts.  That’ll be a more complicated post down the road.