The Creative CFO team recently hosted a Teach Learn Share (TLS) session on Looker Studio, a data visualisation tool.
The session, presented by Chloe, Mark and Lonwabo from the Business Intelligence Unit, focused on how Looker Studio can be used to create insightful business performance dashboards for high-growth SMEs.
The key takeaways from the Looker Studio Business Intelligence session were:
- The importance of clean and structured data: Chloe, Lonwabo, and Mark emphasised that the quality of the underlying data is crucial for generating meaningful visualisations. They recommended using tools like Xero and Google Sheets to organise and transform data before importing it into Looker Studio.
- The ease of use of Looker Studio: Lonwabo demonstrated how easy it is to connect Looker Studio to data sources and create visualisations. They also highlighted the tool’s seamless integration with other Google Workspace applications.
- The benefits of using Looker Studio to create business performance dashboards: Lonwabo showed how Looker Studio can be used to track key sales metrics, cost drivers, profit margins and, identify trends and uncover insights. He also discussed how these dashboards can be used to improve decision-making and drive business growth.
What are some of the main business performance metrics that can be analysed in Looker Studio?
The main dashboard features built for the customer and demonstrated by Lonwabo included:
- Sales Overview: Provide a snapshot of revenue, cost of sales, gross profit (GP), and GP percentage across different months and quarters, with comparisons to the previous year. This section also allowed for filtering by specific revenue streams.
- Comparative Analysis of Revenue Streams: This involves comparing revenue streams based on predefined categories. It also includes visualisations like bar charts and tables to understand each revenue stream’s performance.
- Customer and Service Analysis: This analyses revenue by customer and service, allowing for filtering by customer name and month. It also provides a breakdown of services provided to each customer.
- Supplier Analysis: Analysing costs by supplier, with similar filtering options as the customer analysis. This section helped identify cost drivers and potential areas for cost reduction.
- Working Capital Analysis: Providing a snapshot of key balance sheet metrics like current assets, current liabilities, cash on hand, current ratio, quick ratio, accounts receivable days, and accounts payable days. This section also included target lines for comparison and analysis which is very useful to compare against your overall business goals.
What are some of the tools that can be used to transform and aggregate data before importing it into Looker Studio?
Lonwabo demonstrated how using tools like Xero and Google Sheets can be used to organise and transform data before importing it into Looker Studio.
Specifically, having a well set-up chart of accounts is an important first step. The final data organisation a lot quicker if the accounts are correctly categorised into revenue, cost of sales, and expenses. Once the data has been extracted into Google Sheets, using G-Accon, it’s time do the final categorising and transformation of the data in Google Sheets.
What were the main data clean steps done in Google Sheets?
Google Sheets was used to transform and aggregate the data, and organise it into input, transformation, and output tabs. This included creating pivot tables, summarising data, and adding formulas and calculations.
By organising and structuring the data in this way, it was easier to identify and fix errors and ensured that the data was in a format that Looker Studio could easily read and visualise.
Functions like SUM, AVERAGE, and VLOOKUP were used to aggregate data, while other formulas where used to aggregate data by month.
For example, an additional column was inserted with a formula to transform the date (in DD/MM/YYYY format) into a simple ‘Oct 2024’ format. This helps with summarising data into Pivot tables, or just cleaner, simpler data sets, that can be pulled into Looker Studio.
How quick is it to set up a Looker Studio dashboard once your data is organised in Google Sheets?
Lonwabo confirmed that it took approximately 30 minutes to set up the data in Google Sheets and with some additional time (an hour or two) to categorise and clean the data. Once that’s done, a basic dashboard could be created in Looker Studio in as little as a day, depending on the complexity and desired visualisations.
Looker Studio is a powerful tool for business intelligence teams looking to create business performance dashboards that provide valuable insights for SMEs. By ensuring data is clean, well-structured, and organised through tools like Xero and Google Sheets, businesses can quickly transform raw data into actionable insights.