In “Utilizing Google Analytics to Observe Profitability by Product,” I defined the way to import product price into Google Analytics to calculate gross revenue for every merchandise offered. It was a difficult train. I obtained suggestions and questions.
On this submit. I’ll break the method down by utilizing Google Information Studio and information mixing.
Gross Revenue by SKU
To arrange reporting for the price of items offered, create a Google Sheet that lists SKUs and their price. Create two columns:
- Product SKU
- Wholesale price of every product.
Create a Google Sheet that lists SKUs and their wholesale price.
Subsequent, mix this information with gross sales information from Google Analytics.
- Create a Information Studio report and add your Google Analytics account as an information supply. (For extra, see my Google Information Studio article and this submit from Google for Information Studio assist)
- Add the Google Sheet with the 2 columns for “Product SKU” and “COGS” as an information supply to your Information Studio report
- Create a desk in Information Studio by clicking “Add a chart,” then choosing a Desk chart
Create a desk in Information Studio by clicking “Add a chart.” Click on picture to enlarge.
- Mix the 2 information sources for this chart by clicking “BLEND DATA” underneath the Information Supply part for the chart. (This text on information mixing from Google is value studying earlier than getting began.)
Mix information sources by clicking “BLEND DATA.”
- Within the instance under, the Google Analytics account for www.greekmarket.com is the primary information supply.
- The Google Sheet with COGS information is the second information supply.
- The “Be a part of key” is “Product SKU” from every information supply.
- Report “Amount,” “Product Income,” and “Avg. Value” from the Google Analytics information supply.
- Report “COGS” from the Google Sheet information supply.
After information is added, save and shut the mixing information supply. Click on picture to enlarge.
Save and shut the mixing information supply then add two extra metrics. To do that, click on the “Add Metric” hyperlink underneath the listing of accessible metrics.
Add two extra metrics.
The primary metric is “Whole Product Price.” The components is COGS * Amount.
Add the full product price metric utilizing the components COGS*Amount. Click on picture to enlarge.
The second metric is “Internet Revenue After COGS.” The components is Product Income – (COGS*Amount).
Add the “Internet Revenue After COGS” metric utilizing the components Product Income – (COGS*Amount). Click on picture to enlarge.
The ultimate listing of metrics ought to seem like the next, with the 2 calculated metrics added.
The ultimate listing with the calculated metrics added.
Your chart ought to seem like the next with the “Internet Revenue After COGS” column reporting the important thing metric you need: the money move from gross sales minus price of products offered.
Further metrics will be derived from the information, similar to return on funding after COGS: (Product Income – (COGS*Amount))/(COGS*Amount). Click on picture to enlarge.
Have in mind it is a simplified report. It doesn’t take into account the next.
- Product prices altering over time. An extra column might be added to the Google Sheet that exhibits the date for every product price.
- Coupons utilized to orders. A calculated discipline that adjusts “Product Income” might be created. It might use an estimate of the online income, minus coupons, on the product degree.
- Price of delivery bills not handed on to the shopper. An estimate of the income loss to cowl delivery bills might be utilized to an adjusted “Product Income” metric.
- Promoting price for every SKU.
Driving Money Move
Use the method on this submit to ascertain the optimum worth to drive probably the most money move after product price. It may possibly additionally assist set up promoting price by product class.