How to Calculate Your eCommerce Expense Tracking Easily

Looking to track your ROI between your Shopify, or WooCommerce stores? Well look no further, this file will help you consolidate your metrics all in one place so you can easily pull your data without guessing. You’ll know your exact numbers compared side by side, easily.

This tutorial requires an existing knowledge of excel. Please understand, the formulas need to be altered to your products. 

Enable circular references on your spreadsheet by going to options–>calculation and use iterative calculation

WooCommerce Store Export

If you’re on WooCommerce you need to setup your export correctly in order for the spreadsheet to work. Navigate to WooCommerce and open your plugin called CSV Export tool. Export your orders, with the following custom format.

See the image:

Once you have obtained your export, copy/paste the export into the tab called “WP Dump”. Dump the raw metrics in there. Then setup your cost calculators.

Setting up the cost calculators

Navigate to the Code Builder tab and fill out the Product name and Price of all your products. Then it will generate a formula on the right hand side for you.

The formula is redundant and is as follows:

=IF(GC3=”Product Name”,6.99,IF(GC3=”Product Name”,1.99,IF(GC3=”Product Name”,3.99,IF(GC3=”Product Name”,7.99,

You have a max limit for the number of queries per formula, that’s why there are 2 columns that basically do the same thing. Once you have imported the formulas, drag the formulas to all the cells in the column. Then you should be calculating your costs correctly. Your pivot tables are already setup too.

Shopify Store Export

You need to export your Shopify orders, not the reports section. Export all your orders and import the same way you would above. Your columns should already be setup. Use the Code builder to build out the product price same as above.

The formula will be redundant. Fill it out for all the products in your store.

Facebook Export

Navigate to your Ad Sets and arrange your columns like below. If you don’t know how to arrange your columns, learn how to arrange your columns on Facebook.

Reporting StartsReporting EndsAd Set NameDeliveryBidBid TypeResultsResult IndicatorImpressionsAmount Spent Today (USD)Amount Spent (USD)Website Purchases Conversion ValueWebsite Purchase ROAS (Return on Ad Spend)Cost per ResultsCTR (All)CPC (All) (USD)CTR (Link Click-Through Rate)CPC (Cost per Link Click) (USD)Link ClicksCPM (Cost per 1,000 Impressions) (USD)Website Content ViewsWebsite Adds to CartWebsite Checkouts InitiatedWebsite PurchasesCost per Website Content Views (USD)Cost per Website Add to Cart (USD)Cost per Website Purchase (USD)FrequencyBudgetBudget TypeEndsStartsCost per Any Action (USD)3-Second Video ViewsVideo Percentage WatchedVideo Average Watch TimePeople Taking ActionButton Clicks

Other expenses

Once you export your Ad Sets from Facebook, import them into the “Facebook Dump” tab. The pivot tables should take care of the remaining calculations. Navigate over to the “Other expenses tab” and insert your other expenses. Follow the format on the doc.

Make sure you select the dates in the Date Pivot” tab by clicking on the relevant dates on the filters. 

Once you have all the data inserted, push the Data–>Refresh All and you should get your numbers in the Day Profit Analyzer. If you are getting #REF errors, comment below and I’ll try to help you.

Comment below if you have questions. 

Leave a Reply

Your email address will not be published.