I need you to create a dashboard where we will bring KPIs to an overview of my business.
The main sources will be Google Analytics and Google Sheets.
Google Analytics is already set up and working, the data collection with Google Analytics you need to create so that it perfectly meets the integration with Google Data Studio.
Come on, I'm going to divide the project into modules, so we can discuss it in stages.
These controls must be filtered by day, week, month, quarter, semester, and yearly.
[ KPI 01 - SALES CONTROL MODULE BY PRODUCT ]
Using a spreadsheet, we will analyze the daily sales index of 3 products. The main objective is to monitor which product performs better by comparing the quantity sold and revenue for each product.
Products that should be considered:
-Sale of the product "Stickers da Coleção"
-Sale of the product "Stickers Corporativos" - (Here we must calculate if there was a commission for external seller)
-Sale of the product "Stickers Personalizados"
These controls must be filtered by day, week, month, quarter, semester, and yearly. They must contain the quantity sold and the total sales revenue for each product.
In this KPI, add up all sales and show total revenue.
In this KPI, show the average ticket
[ KPI 02 - TRANSPORTED VOLUME CONTROL MODULE]
KPI that indicates the volume of products shipped by carrier, that is, it must compute how many units were shipped to each carrier.
Here we need to compute how many submissions had errors and needed to be resent and what the costs were for each resend.
[ KPI 03 - COMMISSION VISION MODULE FOR VENDORS]
Here we must show which seller is performing better and what the monthly commission amount was paid on the product "Stickers Corporativos ".
You will need a control to register sellers in Google sheets
KPI 04 - PRODUCTION MODULE]
We work with printing and would like to have an insight into printer consumption.
Factory equipment generates a CSV file showing various indicators.
Among these indicators from the CSV file, I would like to have a graphical view:
The reports must be categorized by "Support Type", this means which material is being used, for example: Adhesive, Holo, Transparent, Canvas and all materials that are registered by the equipment.
- Total spent in ml for each ink color and which ink is consuming the most
- Total spend in R$ for each color of paint and which paint is consuming the most
- What total area in M2 were printed with filter for each printer
- What is the total cost of the area?
- Total print time by day, week and month (Graph comparing other months)
Maybe this is module one that we need to explain better, show you every detail inside the CSV file and translate so that you understand. Once you understand it, it will be very easy to work with the data.
[ KPIs 05 - KEY ECOMMERCE INDICATORS Google Analytics ]
Here we will extract the data from Google Analytics
- Create dashboard design funnels:
Visits Product views
Visits Add to cart
Visits Start checkout
- Shopping Cart Abandonment Rate (CAR)
- Sales Made by Acquisition report, i.e. mapping which UTMs are bringing in the most sales
- Bounce rate
- Exit fee
This is an early project and one that we will improve over time, so we need someone who understands and can provide a long-term service. We understand that this is an early version and that we will improve a lot.
The Dashboard Layout Design should be clean and beautiful.