Data is essential for smart decisions in any business. To grow, your business must know:
- Who are your main customers?
- How much money did they pay you?
- How often do they buy your products or services?
But to get the most out of your data, it needs to be visual. Data visualisation is the process of telling the story of data visually. Its purpose is to combine various pieces of data from different sources or categories to analyse them more easily. Furthermore, it communicates this information in a simple and visually captivating way with various charts and graphs. This helps make better-informed business decisions, which we will discuss below.
While working with a client, our Business Intelligence team replaced their outdated, error-prone spreadsheets with an interactive dashboard, transforming time-consuming worksheets into a streamlined, insightful solution through advanced data engineering and visualisation.
Identifying the Problem
Pivot tables are the backbone of data aggregation in Excel. With the classic VLOOKUP and now more trendy XLOOKUP, pivot tables are the litmus test for “Do you know Excel?”. We aren’t talking macros or VBAs; those are for the Excel jockeys.
Pivot tables only took them so far; they needed more. They needed to visualise the data and make it interactive and actionable. There is something quite tangible about seeing your data and being able to connect the dots.
Choosing the Right Tool
Looker Studio stands out in the BI industry for its flexibility and ease of use. Unlike tools that tie users to specific ecosystems, it works with data from any source—inside or outside the Google ecosystem. This allows businesses to build a tech stack that fits their needs without unnecessary restrictions. Google also makes the tool easy to adopt by providing clear, up-to-date documentation, ensuring even beginners can use it effectively.
A key advantage of Looker Studio over Power BI is its fully cloud-based design. Mac users, in particular, benefit since it doesn’t require extra software to run on non-Windows systems. Scalable, cost-effective, and user-friendly, Looker Studio was the perfect choice for this project, offering a powerful BI solution that adapts to the business—not the other way around.
Data Sources (Cin7 Core + Xero)
The project started with the data. The client used information from Xero and Cin7 Core, but the data needed cleaning and restructuring to make it useful. We resolved inconsistencies and added extra data points like sales channels and historical data for comparative analysis to identify patterns and trends over time. We connected this cleaned data to Looker Studio via Google Sheets.
Implementing Data Visualisation
We focused on this once all the back-end work had been done (i.e., extracting, transforming, and loading data à la ETL). Our focus shifted to creating a visually cohesive dashboard with a layout and visual hierarchy carefully curated to ensure users could easily navigate and interpret the data and make informed decisions for their business.
The Outcome: a fully curated dashboard for management and executives to understand their business and profit margins down to a product level.
Key Features of a Dashboard
The dashboard wasn’t just about looks—it also needed to work seamlessly.
- Interactive Charts: Users can filter and explore data dynamically.
- Summary Metrics: Scorecards showed key financial indicators at a glance.
- Purpose-Driven Pages: Each page in the dashboard served a distinct role, contributing to the overall story of the data. For example:
- Year-To-Date Sales Overview: Provided a sales performance summary across key metrics, comparing the current financial year to the previous one.
- Category Analysis: Segmented products into categories based on gross profit over time, helping assess the profitability of each group.
These features made it easier for the financial team—and their client—to explore trends and understand business performance.
Challenges Within a Data Project
Some inconsistencies in gross profit figures pointed to issues in the data pipeline. Many sales were categorised as “Other,” highlighting the need for better-defined data-capturing controls and product categories. Fixing these issues improved data quality and ensured the dashboard would remain reliable over time.
Conclusion
The final dashboard transformed a static spreadsheet into a powerful, interactive tool directly impacting business strategy. This enabled the financial team and their clients to connect metrics with specific actions. It wasn’t just about creating a tool—it was about driving conversations that led to tangible outputs, like focusing on high-margin products or identifying opportunities for growth in underutilized customer segments. This project laid a practical foundation for operational improvement and future growth.
