TLDR; Use this Google sheet template to track your subscription box finances month to month.
Step 1. Download Transactions from Cratejoy
The above spreadsheet is designed to work with Cratejoy’s “All Transactions” export which is kind of hidden within the General Analytics section of the dashboard.
From the “General Analytics by Month” screen, select a date range that makes sense, then click on the Export button and select “All Transactions”.
This will download a .csv file that you can open with Excel or Numbers.
Step 2. Copy Transactions to Google Sheet
Once you have the document open in Excel (or Numbers) you can copy/paste all of the relevant transactions into the Google Sheet on the appropriate month tab.
Step 3. Manually add other expenses
Go to the “Totals” tab in the Google sheet and fill in the blue columns with the appropriate values for your business.
Step 4. Customize Inputs tab
In the Google sheet, you will notice a tab called “Inputs”. This tab should be updated with the appropriate values for your business. Most notably, it needs to know the cost of your box for Month to Month, 3 Month, 6 Month and 12 Month prepays. It uses these values to attempt to figure out how many future boxes were purchased in the event of a prepay purchase and calculate future cost of goods for those boxes.
That’s it! This spreadsheet is hopefully a good starting place and can be customized to your liking. If you have more than one subscription product for example, you can take out the Inputs and manually calculate COGS if that is easier. I have found this spreadsheet to provide a good high level account of how you are doing but in conjunction with other tools such as Quickbooks to get detailed accounting. I’m sure there are better ways to do some of this so please send feedback to [email protected]!