For our example, we want to create a dashboard for a one month record of sales of our products. The basic data for the products are stored in the sheet named items. The aggregate daily sales data are stored in another sheet named dataperday.
data visualization google sheets, data stored in different sheets
Some of the data are summarized in the dashboard sheet:
We intentionally left spaces here because we will insert charts and KPIs here later.
2. Calculate KPIs and Metrics by Adding Formulas
Analysis means processing the raw data that you have to get meaningful KPIs. There are several KPIs you can use, and they all depend on the type of dashboard you want to use. Some of them are the following:
eCommerce: Average order value, Conversion rate, Lifetime value
Customer satisfaction: Customer satisfaction score, Net promoter score, Number of support tickets a month
Digital marketing: Bounce rate, Click-through rate, Return on Ad Spend
For our dashboards, we want to calculate the average order value for each day and for each item. We add a column for each of the two tables in the dashboard, giving us the AOV each day and the AOV for each product.
data visualization google sheets, data summarized in dashboard
3. Visualize the Data
Using the steps in the previous section, visualize the data by using the built-in charts in Google Sheets. You can visualize using either the built-in charts or the SPARKLINE function that sets mini-charts within cells.
For our example sheet, we plot the daily sales, profit, and AOV for the last 30 days.
google sheets data visualization, dashboard in google sheets
We now have a simple dashboard that imports data and visualizes it!
Is there Something More Straightforward?
This article seems straightforward, but you may find difficulty in importing data from different platforms.Your Google Sheets may also load more slowly because of it! If that is your concern, how about you try Lido? It is essentially Google Sheets but with built-in ability to import from a wide variety of eCommerce and marketing platforms.