Turning Supermetrics & Google Sheets into a Reporting Engine: Automation Tips from a Data Lead.

Hands up (or cursors up?) if you agree that manual reporting is one of the biggest silent productivity killers for a digital marketing agency.  

Hours lost to exporting platform data. Misaligned dates. Broken formulas. Shocking load speeds when your entire agency is generating reports at the same time, Monday morning.

And then it all happens again next week.

In a recent internal masterclass, our Data Lead (Crestabella, if you’re unfamiliar) unpacked practical data automation techniques using Supermetrics in Google Sheets – designed to eliminate repetitive reporting work, protect data integrity, and make performance analysis faster and more accurate.

You’d think we’d keep this trade secret, but between gatekeeping and giving data analysis a far better reputation in this industry, we pick the latter. 

So, here’s a breakdown of the key techniques, workflows, and spreadsheet structures shared in the session. Courtesy of Crestabella herself. 

Always pull data at a daily level.

One of the most important principles covered? 

Daily data is the foundation of good automation. Pulling weekly or monthly data may seem efficient, but it limits flexibility. Weekly data can’t easily be broken back down into days. But daily? That can always be combined into weeks, months, or custom date ranges.

This single decision dramatically increases reporting flexibility.

When setting up Supermetrics queries, the recommendation is to:

• Select the relevant data source (e.g. Google Ads, Meta Ads, TikTok, Pinterest, Bing, Shopify… You know the drill)
• Set the date range to include both last year and year-to-date
• Split data by the “Date” dimension
• Avoid unnecessary weekly or monthly breakdowns

Daily granularity gives marketers full control over how performance is summarised later.

Protect data integrity from day one.

Automation is only useful if the data remains clean and historically consistent.

When configuring Supermetrics queries in Google Sheets, several settings are critical:

• Show all time values
  
  • Ensures dates with zero spend are still pulled as zero. This prevents misaligned date rows when refreshing queries.
    
• Combine new results with old
  
  • Allows data to append rather than overwrite historical results.
    
• Keep previous results on refresh errors
  
  • Protects historical data if a refresh fails.

These options ensure that refreshing a query weeks or months later doesn’t corrupt historical reporting. Filters should only be applied when necessary (e.g. filtering by campaign name or label for specific historical analysis). 

Over-filtering? Now that can create unnecessary complexity in ongoing automation.

Keep spreadsheet structure lean.

Multiple queries across weekly and monthly breakdowns can slow down spreadsheets significantly. The best digital marketers we’ve seen? They recommend:

• Pulling only daily breakdowns via Supermetrics
• Building weekly and monthly views using formulas
• Freezing header rows for easy navigation
• Keeping queries centralised and clean

If dates appear misaligned, the issue is typically caused by the “show all time values” setting not being selected. Clean structure prevents long-term reporting headaches.

Use SUMIFS to turn daily data into weekly reports.

Once daily data is pulled, weekly and monthly reporting can be built using formulas, specifically the SUMIFS function – which we’ll never stop raving about.

The SUMIFS formula allows:

• Summing cost or revenue within a defined date range
• Using criteria such as:
  
  • Greater than or equal to a start date
  • Less than an end date
    ‍
    

By anchoring columns using dollar signs ($), cell references remain fixed when copying formulas across rows and columns.

Instead of manually dragging formulas down, entire sections can be highlighted using keyboard shortcuts and pasted at once – significantly speeding up spreadsheet setup.

This structure transforms daily raw data into dynamic weekly reports without requiring additional Supermetrics queries. And if this sounds confusing all spelled out, we promise it’s a lot clearer visually or over a demo. Get in touch with us here if you’d like one. 

Monthly reporting, and smarter visualisation.

Monthly reports follow the same logic:

1. Copy daily headers into a new sheet.
2. Set the first day of each month.
3. Reuse the SUMIFS structure to aggregate daily results into monthly totals.

From there, visualisation becomes simple.

When comparing metrics with different scales – let’s say Google Ads cost vs. Shopify Sales – one data series can be moved to the right vertical axis within Google Sheets charts. This ensures both metrics remain readable and meaningful on the same graph.

Automated monthly charts make trend analysis significantly faster, especially during performance reviews or QGR preparation, or pre-sale vs. post-sale insights. 

Building a date-specific performance calculator.

Beyond standard weekly and monthly views, automation can also support flexible analysis.

Call upon our beloved SUMIFS again, and you can build yourself a simple calculator with user-defined start dates and end dates. 

The formula references these date cells – again using dollar signs to anchor them – and can subsequently allow instant calculation of:

• Total spend
• Total revenue
• CPA
• AOV
• Other efficiency metrics, et cetera
  
  

Benefits of this structure include quick troubleshooting and timeframe-specific performance analysis, all without rebuilding reports.

Where do automations fit in broader reporting?

Automated spreadsheets serve a different purpose to dashboards.

• Dashboards (e.g. Looker Studio) are excellent for client-facing visual summaries.
• Automated spreadsheets are essential for deeper internal analysis, troubleshooting, and structured QGR preparation.
  
  

Between us, plenty of DataSauce’s reporting infrastructure integrates this automated approach before data is visualised in tools like Looker Studio. The result?

• Faster reporting cycles
• Reduced manual input
• Greater historical accuracy
• Easier cross-channel comparison
• Better-informed optimisation decisions

Automation doesn’t just save time, it improves decision quality.

Common mistakes to avoid.

• Pulling weekly or monthly data instead of daily
• Forgetting to enable “show all time values”
• Overcomplicating spreadsheets with multiple redundant queries
• Relying on conditional formatting instead of transparent formula logic
• Not anchoring cell references in formulas
  
  

Small structural mistakes compound over time. Getting the foundation right prevents reporting rebuilds later, and speeds up what could be hours of waiting. 

Of course, we admit that the above aren’t necessarily tips for a beginner. 

We’re working under the assumption that you’ve already got a smidge of experience across spreadsheets, Looker Studio, and the constituent channels you’re reporting on. 

But if you’d like a more foundational overview into how we do Data Science at DataSauce, head here to explore more – or chat with us if a 1-1’s a little more your thing. 

Final Takeaways? What Good Data Automation Unlocks

Good data automation unlocks a whole new world. When set up correctly, it can:

• Eliminate repetitive manual exports
• Protect historical data integrity
• Enable flexible timeframe analysis
• Support campaign-level optimisation
• Power faster QGR preparation
• Create cleaner integration into dashboard tools, plus heaps more.
  
  

And who wouldn’t want that? For digital marketers and eCommerce brands in particular, the real advantage isn’t just operational efficiency – it’s analytical speed.

When the data is already structured, clean, and ready to manipulate, digital agencies (like ourselves) spend less time building reports and more time extracting insights. Win for all.