Job description
The application should enable analysis of profitability of individual GC (Chrobry Group Ltd.) products based on the following data:
Advertising cost
Warehousing cost (not necessarily)
Sales revenue
Margin
We would like to use this application to evaluate how much profit is generated by a given product, how expensive is its advertising and warehousing cost and then, on this basis, evaluate the next steps.
The application should be in the form of a spreadsheet with the possibility of filtering and sorting and searching, as well as specifying a date range. Another possible solution is (if possible) to properly configure reports in Google Analytics. It is important that we are able to do analysis on a product-by-product basis.
The application should be available online from anywhere. Ideally, it should be based on Google Sheets.
One row should concern one product.
There are hundreds of products and the number is growing.
Data should be updated as often as possible, but this is negotiable.
Data for the application should be downloaded automatically from the following sources:
Source of product information: e.g. XML
Cost of advertising: data taken from Google Analytics. Could be based on click-through rate on product link in store(s) and manually averaged rate per few, or a reliable rate by source (e.g. Google Adwords click-through rate). Based on this, the cost of advertising for a given product over a given period of time would be calculated.
Warehouse cost: would be based on manually entered data, which would count the daily cost of keeping a given product in the warehouse, i.e. rate times the number of days.
Sales revenue: would be collected via API from the IAI system (Idosell). That is, the number and value of orders for a given product in a given period of time.
Margin: would be set manually or calculated from the sale price.
