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.
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:
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.
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.
Ad Set Name
Amount Spent Today (USD)
Amount Spent (USD)
Website Purchases Conversion Value
Website Purchase ROAS (Return on Ad Spend)
Cost per Results
CPC (All) (USD)
CTR (Link Click-Through Rate)
CPC (Cost per Link Click) (USD)
CPM (Cost per 1,000 Impressions) (USD)
Website Content Views
Website Adds to Cart
Website Checkouts Initiated
Cost per Website Content Views (USD)
Cost per Website Add to Cart (USD)
Cost per Website Purchase (USD)
Cost per Any Action (USD)
3-Second Video Views
Video Percentage Watched
Video Average Watch Time
People Taking Action
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.