Analyzing E-Commerce Sales Data: Using Google Sheets and Looker Studio

For New Zealand e-commerce operators, from boutique Manawatū artisans to Wellington tech startups, understanding sales data isn't a luxury—it's foundational. Relying solely on platform-native reports often means missing the nuanced insights crucial for strategic growth, especially when dealing with the unique dynamics of the NZ market, including specific payment gateways like Windcave or POLi, and courier integrations. This guide addresses the common challenge of disparate data sources by demonstrating how to consolidate and visualise your e-commerce sales data using readily available, cost-effective tools: Google Sheets and Looker Studio (formerly Google Data Studio). We'll focus on building genuinely actionable dashboards without the prohibitive costs associated with enterprise-level business intelligence (BI) platforms, ensuring your analysis is compliant with the Privacy Act 2020 and provides real value to your bottom line, factoring in the 15% GST where applicable.

Before diving into complex algorithms, the first step in effective ecommerce analytics NZ is robust data aggregation. Google Sheets, often underestimated, serves as an incredibly flexible and powerful data warehouse for small to medium-sized enterprises (SMEs) in New Zealand. Its ability to import data from various sources—CSV exports from Shopify, WooCommerce, Xero, or even custom APIs—makes it an ideal central repository.

Looker Studio then transforms this raw data into compelling, interactive visualisations. Think of it as your digital whiteboard, presenting key performance indicators (KPIs) like average order value (AOV), conversion rates, customer lifetime value (CLTV), and regional sales performance (e.g., Auckland vs. South Island) in an easily digestible format. This combination allows for a 'DIY' approach to business intelligence that's both powerful and budget-friendly, aligning perfectly with the lean operational ethos common among successful NZ businesses.

Here’s a step-by-step approach to setting up your bespoke e-commerce sales tracking dashboard, designed specifically for New Zealand businesses.

1. Data Extraction and Centralisation in Google Sheets:
   • Source Data: Export sales data from your e-commerce platform (e.g., Shopify, WooCommerce, Magento) as CSV files. Ensure you capture critical fields: Order ID, Date, Product Name, SKU, Quantity, Unit Price, Discount, Shipping Cost, Total Sales (excl. GST), Total Sales (incl. GST), Customer ID, Customer City/Region, Payment Method. For NZ context, ensure your platform correctly applies 15% GST and separates it.
   • Automate Imports: For recurring data, use Google Apps Script to automate the daily or weekly import of CSVs from a Google Drive folder or directly from an API if your platform supports it. Alternatively, use third-party connectors like Supermetrics or Funnel.io for more complex platform integrations (note: these incur monthly NZD costs).
   • Data Cleaning and Standardisation: Create a 'Cleaned Data' tab. Use ARRAYFORMULA, VLOOKUP, QUERY, and TEXT functions to standardise product names, categorise sales (e.g., 'Apparel', 'Homeware'), and calculate key metrics like profit margin per product if cost data is available. Ensure all date formats are consistent (YYYY-MM-DD).
2. Preparing Data for Looker Studio in Google Sheets:
   • Create a 'Reporting View' Tab: This sheet will serve as your direct data source for Looker Studio. It should contain only the columns needed for your dashboard. Avoid unnecessary calculations here; keep it lean for performance.
   • Add Calculated Fields (Optional but Recommended): Examples include Year, Month, Day of Week from your Date column, Profit (excl. GST) if cost data is present, Average Unit Price (excl. GST). Use formulas like =YEAR(A2).
   • Ensure Data Integrity: Regularly audit a sample of your imported data against your e-commerce platform reports to catch any discrepancies early. This is crucial for accurate financial reporting and compliance with the Fair Trading Act regarding advertised pricing.
3. Connecting Google Sheets to Looker Studio:
   • New Report: Go to Looker Studio, start a new blank report.
   • Add Data Source: Select 'Google Sheets' as your connector. Authorise access to your Google Drive.
   • Select Spreadsheet & Worksheet: Choose the specific Google Sheet and then the 'Reporting View' tab you created. Ensure 'Use first row as headers' is checked. Click 'Connect'.
   • Review Fields: In the Looker Studio data source editor, review the detected data types (e.g., Date, Number, Text). Correct any misidentified types (e.g., sometimes Order ID might be detected as a number; change it to Text if it contains alphanumeric characters).
4. Building Your Core Dashboard Visualisations:
   • Key Performance Indicators (KPIs): Add Scorecards for Total Sales (incl. GST), Average Order Value (incl. GST), Number of Orders, Conversion Rate (if you track website sessions externally). Use comparison periods to show growth month-over-month or year-over-year, vital for tracking seasonal trends in NZ.
   • Time Series Charts: Create a Time Series chart for Total Sales (incl. GST) by Date to visualise trends. Add a breakdown dimension for Customer City/Region to see geographical performance (e.g., how sales differ between Auckland, Christchurch, or Palmerston North).
   • Bar Charts for Product Performance: Use bar charts to show Top 10 Products by Sales or Top 10 Products by Quantity Sold. This helps identify best-sellers and potential inventory issues.
   • Geographic Map (Optional): If you have Customer City/Region data, a Geo Chart can visually represent sales distribution across New Zealand, highlighting strong and weak regional markets.
   • Filters and Controls: Add Date Range controls, Dropdown lists for Product Category, Payment Method, or Customer Region to allow dynamic filtering by users.
5. Refining and Sharing Your Dashboard:
   • Branding: Customise the dashboard's theme with your brand colours and logo. Infynt, being a Palmerston North-based agency, understands the importance of local brand identity.
   • Performance Optimisation: For large datasets, consider aggregating data within Google Sheets or using Looker Studio's data blending features carefully. Too many complex charts on a single page can slow performance.
   • Sharing: Share your report with relevant team members (e.g., marketing, operations, finance) with appropriate view or edit permissions. Schedule email delivery of the report for regular updates.
   • Mobile Performance: Test your dashboard's responsiveness on various devices. A well-designed dashboard should be accessible and clear on a mobile phone, especially for busy NZ business owners on the go.

While Google Sheets and Looker Studio offer significant advantages for cost-effective ecommerce analytics NZ, it's crucial to understand their limitations compared to more robust enterprise BI solutions. The choice often depends on data volume, complexity, and internal technical capabilities.

For many New Zealand businesses, the DIY approach provides an excellent starting point. However, as your data grows, complexity increases, or you require deeper integrations with NZ-specific systems like local payment gateways or courier tracking, the limitations become apparent. This is where Infynt's expertise in custom business intelligence dashboards and portals becomes invaluable, offering scalable, tailored solutions that bridge the gap between DIY and expensive enterprise tools.

A dashboard is only as valuable as the actions it inspires. With your robust e-commerce sales analytics NZ setup, you can now answer critical business questions:

• Which products are performing best in specific NZ regions? Use this to tailor marketing campaigns (e.g., a targeted Facebook ad campaign for Gisborne if you see a sales spike there).
• What is the average order value for customers paying with POLi vs. credit card? This might inform payment gateway promotion strategies.
• Are sales dipping during specific times of the year or week, especially around NZ public holidays? Adjust staffing, inventory, or promotional efforts accordingly.
• How effective are your discount codes? Track the Discount field to understand the true impact on profitability, considering the 15% GST implications.
• What's the churn rate for first-time buyers vs. repeat customers? This helps refine customer retention strategies, a key factor for sustained growth in the competitive NZ market.

By regularly reviewing your dashboard, you move beyond reactive decision-making to proactive strategic planning. This data-driven approach is fundamental for any New Zealand business aiming for sustainable growth, whether you're selling handmade goods from Palmerston North or high-tech solutions from Auckland.

While Google Sheets and Looker Studio are powerful for initial data analysis, every growing New Zealand business eventually reaches a point where off-the-shelf tools or DIY solutions become a bottleneck. Perhaps you need:

• Deeper Integrations: Connecting to complex ERP systems, custom NZ inventory management software, or advanced marketing automation platforms.
• Predictive Analytics: Forecasting future sales trends, identifying potential supply chain issues specific to NZ import/export, or optimising pricing strategies.
• Real-time Operational Dashboards: Māoring live stock levels across multiple warehouses, tracking courier status for NZ Post or Aramex deliveries, or managing customer service queues.
• Bespoke Reporting Portals: Providing custom access levels and tailored views for different departments or even external stakeholders, all under your brand.

This is where Infynt, based in Palmerston North, steps in. Our team of web engineers and data strategists specialises in building custom business intelligence dashboards and portals tailored precisely to your unique operational needs. Whether you require a simple, integrated reporting solution for your $3,495 E-commerce site, or a complex AI-driven predictive analytics platform, we deliver. We understand the nuances of the New Zealand business environment, from the Privacy Act 2020 to local payment providers, ensuring our solutions are not just powerful but also compliant and relevant. Our transparent service packages, including custom AI/software engineering, provide a clear pathway from data chaos to actionable insights, empowering your business to thrive.