Merch by Amazon Journey – Q1 2018 Update

This post may contain affiliate links to products, software or services which may result in my earning commissions at no additional cost to you.

TL;DR: Q1 was not as good as ~~I~~ anyone had hoped but it’s still “good money” so I’m excited about the future. I tested pricing in Q1 and I found that **for evergreen designs** it doesn’t matter too much in the niches I’m in – so I’ll keep my prices in the $20+ range for standard. I’m also sharing the link to the spreadsheet I created & use to manage my Merch business. It’s pretty but sort of complicated (it’s homegrown, what do you expect!?!) so I’ve included some instructions & explanation.

****

**Status Update**

Alright, this is going to be a very quick update for Q1 because the meat of this post is all about the spreadsheet. In my last post I said I would be focusing more on my affiliate website for 2018 and so far that’s going well. I did over $200 with it last month… and it now has 4 articles instead of just 3 (just over 12K total words)! I’ll probably start a case study about it over in r/juststart since it’s showing some promise and I’ve learned some things that I could share. My goal is to get 50 articles up by the end of Q3 which, based on current conversion numbers, should mean I’m making $4000/month. I’m deciding if I should use this account or post updates using the alt I normally use over there… hmmm, what to do? Either way, it’s keeping me busy.

As such, I will have less interaction on Reddit and I’m only going to update my Merch sales numbers quarterly (and each post will probably include something I tested/learned for that prior quarter).

Okay, here we go:

#Merch Numbers:

**Tier:** T4000

**Total Live Designs:** 2247 (As of March 31st)

**Total Sales for Quarter:** 522

**Selling Designs:** ~ 120 (in total 571 designs have sold at least once)

**What Worked:**

– *Changing Bullets & Updating Price* – I keep hearing the word “curate” being tossed around so I decided that I would make sure I had solid bullets and consistent themes around each of my 95 different “niche brands”. To do this I updated the bullets & prices of over 600 products this past quarter (around 200 total designs). About 2 days after I started doing this I got sales on designs that hadn’t ever sold and to date I’ve had 40 designs sell for the first time.
– *Created a self-imposed 90 day cull* – I’ve had conversations with a few folks recently regarding a hypothetical scenario: What if Amazon instituted a rolling 90 day limit? I’ll save discussion about this for another day, but I created a formula on my spreadsheet that tells me how many days it has been since a design sold. Anything over 100 days I have been adjusting the bullets, changing price or killing the design completely (more rare). On my first pass at this I updated about 50 designs that used to sell pretty well (ones that have sold more than 10X in their life but had died). Of those 50 I got 10 to sell again after I updated them. This is positive.
– *My wife is still helping me.* She hasn’t run away yet but she has only really worked about 1 time per week so far. We’ll look to increase that in the next few weeks.
– **Takeaway from Q1?** Mess with your designs occasionally.

**What Didn’t Work:**

– *Wrinkle Effect!* Many of you already know my disdain for the Wrinkle Effect so I won’t get into it too much. But let’s just say that about 50% of the designs mentioned above stopped selling after the wrinkle effect went into place. Some of them were terrible. It looks like that has been fixed/changed recently but it killed some momentum that I was getting.
– *Testing prices:* From my last update you may recall that I’ve been testing prices this year. I’m going to continue to test but early results show that it doesn’t matter for me. I haven’t seen an increase or decrease in sales based on pricing. Granted it’s really difficult to isolate just *price* in a meaningful test but early indications show that it doesn’t change much in the niches I’m in (and the designs I make). I stand by the idea that designs come first, price second as long as it’s not on either extreme.

That’s about it. Much better quarter than last year (considering I got accepted Feb. 28th!). I’m happy with the progress.

****

#Spreadsheet Magic

Okay – for those who want it, [here’s the share link to my spreadsheet]( https://docs.google.com/spreadsheets/d/1W7zuZq9uykkoqDaOngeIHql-TXTiV2zAWqg3hCAlHbs/edit?usp=sharing) As I’ve mentioned before, I created this to help me manage my business based on the way I want to manage it. It’s not the cleanest, fastest, prettiest but it works well for me. The last time I shared it I explicitly told everyone that I wasn’t willing to hold anyone’s hand to figure it out. I’ll be more giving this time (it’s the nice thing to do).

I would be willing to record a video tutorial/how-to for it if it proves to be too complicated, but only if that’s something that a few people want or need. Just let me know.

**Housekeeping**

– The spreadsheet is currently on Google Sheets as “view only”. If you have a Google account you can click on “file” and “duplicate” (while logged in) to create a copy in your drive folder that you can use (editable).
– Word of warning: it has a LOT of complex formulas so it’s starting to get slow for me at just under 3000 total sales and 500+ selling designs. I may do what I recommend below.
– It runs way faster in Excel. You can click on “file”, “download as” and then save it as an xlsx file on your computer somewhere. You’ll have to update the graphs and a few other things will be wonky but it’s a TON faster once you get it going. It’s a good start anyway.
– It may have a few errors… I’m not perfect. But if you find one, please PM me and let me know.
– I’ll update the file occasionally and may add some graphs in the future.
– There’s a bunch of dummy data in there currently so you’ll have to delete it all before using it. I just wanted to show examples of how the data is entered… that actually took longer than creating the dashboard graphs!!
– I created it so it’s mostly “copy & paste” from the sales & earnings reports. If the cell has a yellow-ish background then that’s an area where you manually enter data. Everything else should be a formula (so don’t touch it! Haha).
– I would recommend opening the spreadsheet and follow along while looking at it, otherwise nothing below will make any sense.
– Finally, I created it to track from the beginning on Merch (late 2015) for most graphs but the main “profit” tab isn’t updated for 2015. If you need that and don’t know how to do it, let me know. I just got lazy.

#OPEN THE SPREADSHEET NOW

… or just stop reading if you don’t want to see/use/care about the spreadsheet.

**How to Use**

If you’ve played around with spreadsheets before this should be pretty easy. The formulas are complex-ish, and there’s a lot going on but overall it’s pretty easy to use. Excel is powerful but it has it’s limitations. Something may come along that gives me the same amount of flash & information but currently this is the best option for the way I want to manage things. It’s pretty much an exercise in copy and paste now that all the formulas are worked out so it’s pretty easy to use.

The biggest thing you’ll have to do to get going is update the “Raw ASINS” tab. This is where you house a list of your live designs & a little info about them. Once that’s up to date then the *workflow* goes something like this:

– download your sales report from the Merch Analyze tab (based on the time period you want – I usually just do the past 7 days except around the 15th of the month I’ll grab from the 1st of the prior month so I capture any reconciliations which may have changed “history”).
– Open it in Excel and copy & paste the values into the “SALES” tab (make sure you don’t overlap/copy over the wrong date range).
– there is a built in check to see if an ASIN is on your Raw tab or if it’s on the profit tab. If it’s missing from either I make sure and add it.
– I usually add the number of rows I need to the profit & units tabs for designs that aren’t already there, copy an existing row and paste it into the added slots (this might seem confusing but it’s really pretty easy, you’re just adding rows for designs that hadn’t previously sold but you have to copy all the formulas or things start breaking). Then copy over the newly sold ASINS into column A and everything should update.
– Then you go to your dashboard and admire your pretty new graphs.

As far as managing things, I’m going to go tab by tab and explain what’s going on and how to use it.

– *2018 DASH* – This tab is where the magic happens (no, not *that magic*, let’s keep it PG!). This will give you all the pretty eye candy that you’ve seen before. The top row of charts show you your sales & profit for the Current Year, your average revenue per unit and your total product mix month by month. The second row shows a pie chart breakdown of color mix, sizes, days of the week and styles for ALL TIME. Then you have a monthly breakdown of sales & royalties, yearly breakdown by product type and below that is a daily tracker by year. If you haven’t been in the program that long (3+ years) you can mess with the charts a little so it compresses things and maybe change some colors so it fits your style. Finally, I recently added a breakdown based on design source & loaded date. We’ll chat more about this below. You shouldn’t have to do anything with this tab unless you want to. You don’t enter anything here.
– *Profit* – this is the tab I use to manage my *business*. It breaks down all your selling designs, when they sold, how many sold and pretty much everything else. It’s all about ROYALTIES on this tab, unit tracking is on the next tab. On this tab, at the bottom, you’ll see all the rows to help you manage stuff. You have rows for total royalties, earnings, any balance roll forward, a check column which compares the sales reports to the results above to see if you’re missing a formula somewhere, total units, and some averages to help you out. You’ll really only have to enter stuff in column A (ASINs), the Notes Column, and the Total Live Designs, Tier, and financial rows (under COSTS) – the areas in yellow. Everything else is a formula. (I currently have 2016 & 2017 columns hidden, you can unhide them if you want to check stuff out)
– *Units* – this is the tab I use to really manage my *designs*. This tab shows you your total unit sales, how many times a design has sold, when and how long ago it sold. Anything over 100 days should show up in red in column AS. Again, here you should only have to enter data in column A (but make sure to copy the whole row when adding an ASIN so you get all the formulas). To the far right you’ll see the total sales for all time (all columns are sort-able too), days since last sale and the Products tab. We’ll talk a bit more about this for the RAW ASINS tab, but basically this gives you a snapshot of which products this design is on. From here I’ll figure out which products are missing and be able to see what’s selling and that helps me target new design ideas. At the bottom of the sheet you’ll see a subtotal and a check value to see if you’re missing something.
– *SALES* – one of the easiest tabs to use. This is where you copy your sales reports from the CSV files you download. Since you can only do 90 days at a time from your analyze reports in the Merch dashboard, you’ll just have to start at the beginning of time for your account and copy and paste the data into this spreadsheet to get it all. You shouldn’t have to do anything else on this tab.
– *EARNINGS* – same thing with this tab. Here you’ll copy and paste your earnings reports. Easy-peasy.
– *RAW ASINS* – For those who know databases, this could be described as your Primary Key area. This is where you keep data about your live designs. There are ways (and tools) which allow you to quickly download your entire ASIN list if you don’t already have it. You could use [any of the methods I’ve written about previously]( https://www.reddit.com/r/AmazonMerch/comments/77h0dt/solved_finally_heres_how_you_can_export_a_listing/). I haven’t read that post in awhile but I think it’s still relevant. The FASTEST and EASIEST way to get it is using MerchTools chrome extension. For now it’s free and he (Hamza, the creator) is trusted in the Merch Community. But if you’re very skeptical, you can use the other chrome extension method listed (table copy). I’m not affiliated with MT… but I digress. I manage this tab by adding my ASINs which are live, the name of the item and the product it’s on. I’ll usually append “PREMIUM”, “LS”, “SS” or “HOODIE” to the name of the design when I put it on different products. The standard tee is where I “call out” the other products that design is on using P, L, S or H. Hopefully this makes sense. It’s a lot easier to show someone than to explain it I’m finding. Anyway, I also manually enter when the design was loaded (I use ‘Feb-18 for example. The ‘ mark makes it text otherwise you’ll have a bunch of dates and the formulas won’t work…) and I indicate the source of the design. I have SELF, OUTSOURCED, READY MADE, V-OUTSOURCED and V-READY MADE. The latter two being variations I’ve created from designs I purchased (like changing the colors, sizes or using the asset/element in another unique design). There are some check formulas here too that indicate whether it’s on the sold (aka sales) tab, or the profit tab. This is where I’ll glance to see when something might be rolling off or if it’s ever sold.
– *2017 DASH* – this is a summary of 2017. As each year rolls forward I’m going to create a copy of the detail charts so I can see how things changed.

Okay, I’m sick of typing at this point so hopefully what I wrote & what I shared is helpful in some way. I really wanted to share because I think it’s neat to see but it’s also useful in managing your Merch business. Please let me know it anyone has any questions.

EDIT: Clarified & formatting.

Originally Posted on Reddit