In short: Google Sheets, Apps Script, and Looker Studio combine into a free stack that replaces manual weekly client reports. The first client dashboard takes an hour to build. Each additional one takes 20 minutes. Scheduled email delivery ships the report every Monday without touching it, saving 8 to 10 hours per client per month.
Key Takeaways
- Marketing automation saves 6+ hours per week on routine tasks
- Marianne Gatlabayan dropped one client reporting workflow from 10 hours per month to 15 minutes per month
- The stack is Google Sheets for data, Apps Script for pulls, Looker Studio for visuals
- Six metrics carry the report. Everything else lives on page two
- Scheduled email delivery removes the last manual step
Why weekly reports eat your best hours
Marketing automation saves an average of 6+ hours a week on routine tasks. Report creation ranks second among the most-automated marketing tasks, right behind customer profiling. Yet the freelancers and agency staff most likely to benefit from this automation are also the ones with the least time to set it up.
Most still build client reports by hand every Monday. Screenshot from Google Analytics, screenshot from Ads Manager, screenshot from Search Console, paste into a Google Doc, add commentary, format for the client's brand colors, attach to email, send. Repeat for every client. Every week. Forever.
That workflow costs 10 hours a month per client. In one case study on marianne.click, that same job dropped to 15 minutes once automated. This post is the exact build so any freelancer or agency staffer can replicate it on their own client base by the end of the workday.
What is the free stack for weekly client dashboards?
Everything free. Everything Google-native. Nothing to install locally, nothing to license monthly.
Google Sheets holds the raw data. Every metric the report shows sits in a Sheet tab. This decoupling means the dashboard reads from a single source of truth that you control, not from live API connections that break when credentials expire.
Google Apps Script pulls data from APIs into Sheets on a schedule. Analytics, Ads, Search Console, and any other API-accessible source. The script runs on Google's servers, not your laptop, so no manual work is needed each week.
Looker Studio reads the Sheet and visualizes it. Charts, scorecards, tables, and text blocks compose the report layout. Every visual element updates automatically when the Sheet data changes.
Scheduled email delivery pushes the report to the client every Monday. Built into Looker Studio, no extra tool required. Total cost: $0. Total build time on the first client: under an hour.
Step 1: How do you structure the source Google Sheet?
Create a new Google Sheet. Name it after the client, something like "Acme Corp - Client Data 2026". Consistent naming matters when you have 10+ client sheets and need to find the right one fast.
Create four tabs. Traffic, Conversions, Search, Notes. Each tab holds one category of data. Splitting into tabs keeps queries fast and prevents accidental column collisions.
Each tab needs a header row that matches what Apps Script will write. For the Traffic tab: Date, Sessions, Users, Bounce Rate, Avg Session Duration. For Conversions: Date, Conversions, Conversion Rate, Revenue. For Search: Date, Impressions, Clicks, Position. For Notes: Date, Author, Comment.
This structure matters because Looker Studio reads column names directly. Rename a column later and every chart breaks. Set up the header row correctly the first time, freeze it (View > Freeze > 1 row), and never rename anything. Marianne Gatlabayan learned this the hard way after a Monday morning of scrambling to fix broken charts because a client wanted "Conversions" renamed to "Leads" in the source Sheet.
Step 2: What are your three options for populating the Sheet?
Three options depending on your technical comfort. Pick based on how many clients you serve.
Option A: Manual paste weekly. Slowest but works. Export CSVs from GA4, Search Console, and Ads. Paste into the corresponding tabs. Set a Monday reminder in your calendar. Total weekly cost per client: about 30 minutes. Suitable for one or two clients when you are just testing the workflow.
Option B: Looker Studio native connectors. Skip the Sheet layer entirely. Connect GA4, Search Console, and Ads directly to Looker Studio. Fastest to set up because Looker Studio walks you through the OAuth flow. Less flexible when the client adds a custom KPI that requires blending or calculated fields. Suitable for standard reporting on three to five clients.
Option C: Apps Script pulling APIs. Best long-term. Runs on schedule. Writes to Sheets. Looker Studio reads the Sheets. This is the option the case study on marianne.click uses. Suitable for any freelancer serving five or more clients or any agency staffer building the reporting template for a whole client base.
Pick Option C if you serve more than three clients. The setup pays back within a month because every subsequent client dashboard clones from the first one in 20 minutes flat.
Step 3: What does the Apps Script for GA4 look like?
Simplified version to get you started. This pulls the last 7 days of core traffic metrics.
function pullGA4Data() {
const SHEET_ID = 'YOUR_SHEET_ID';
const PROPERTY_ID = 'YOUR_GA4_PROPERTY_ID';
const request = {
dateRanges: [{startDate: '7daysAgo', endDate: 'yesterday'}],
dimensions: [{name: 'date'}],
metrics: [
{name: 'sessions'},
{name: 'totalUsers'},
{name: 'bounceRate'},
{name: 'averageSessionDuration'}
]
};
const response = AnalyticsData.Properties.runReport(request, `properties/${PROPERTY_ID}`);
const sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName('Traffic');
response.rows.forEach(row => {
sheet.appendRow([
row.dimensionValues[0].value,
row.metricValues[0].value,
row.metricValues[1].value,
row.metricValues[2].value,
row.metricValues[3].value
]);
});
}
The script requests date-level data for the last week, gets sessions, users, bounce rate, and average session duration for each day, and appends every returned row to the Traffic tab. Enable the Google Analytics Data API in Apps Script's Services panel before running or the AnalyticsData reference will not resolve.
Set a weekly time-driven trigger. The Sheet now updates itself every Monday morning at 6 AM before anyone is looking. When you arrive at 9 AM, the data is already in place waiting for the dashboard to render.
Step 4: How do you connect Looker Studio to the source Sheet?
Open Looker Studio at lookerstudio.google.com. Click Create, then Data source. Pick Google Sheets. Select the client sheet and pick the Traffic tab.
Repeat for the other three tabs (Conversions, Search, Notes). Each becomes its own data source. Alternatively, connect all four tabs as one blended source, though separate sources are easier to debug when something breaks.
You now have four data sources ready. Create a new report. Add the first data source. Drag the first chart. Looker Studio suggests chart types based on the data structure, which speeds up the first few components. Ignore its suggestions after you develop your own layout preferences.
Step 5: Which six metrics actually get read?
Most client reports have 25 metrics on page one. Client scans, sees noise, closes the tab. Every additional metric on page one dilutes attention on the metrics that actually matter.
The six that consistently earn attention across every industry:
- Sessions this week vs previous week
- Conversions this week vs previous week
- Top 5 landing pages by conversions
- Top 10 search queries by clicks
- One highlight chart (whatever moved most)
- One line of context from the account manager
Everything else lives on page two or gets cut. Ruthless focus is what separates a report that drives client meetings from one that sits unread in the inbox. Marianne Gatlabayan has A/B tested this across dozens of client dashboards and the 6-metric page one consistently gets higher click-through rates than the 20-metric alternative.
The comparison to previous week matters more than absolute numbers. A client checking their traffic report cares less about "14,000 sessions" and more about "up 12% from last week". Set every scorecard to show a comparison delta.
Step 6: How should you design a client dashboard for readability?
Three design rules that matter for a client report. These come from watching real clients read real dashboards on phones and laptops.
One color per metric type. Traffic in one color, conversions in another, revenue in a third. Consistent color coding trains the client's eye across weeks. By month three the client sees a green number and knows it is revenue without reading the label. Pick colors once, apply them across every dashboard, never deviate.
Numbers first, charts second. Scorecards at the top. Charts below. A client checking on their phone sees the number without scrolling. Most executive readers only see the top of the report because that is all their thumb reaches before they close the tab. Put the most important numbers where they land in that first thumb-sized view.
White space beats density. Two charts per row on desktop, one per row on mobile. Looker Studio has a mobile toggle that lets you set separate layouts for each screen size. Use it. The default full-width chart layout looks fine on desktop and awful on phones. The five minutes it takes to configure the mobile layout is the difference between a client who reads the report and a client who skips it.
Step 7: How do you automate the delivery to the client?
In Looker Studio, click the Share button in the top right. Click Schedule delivery.
Set frequency to weekly. Set day to Monday. Set time to 8 AM in the client's timezone. Not your timezone. Not UTC. The client's local Monday morning when they are settling into the week and glancing at their inbox.
Add the client's email. Add your team's email. Add anyone else who should get the report. Save. Every Monday morning, a PDF of the current report lands in the client's inbox with no human touching anything.
The PDF version includes every chart and metric visible in the report at the moment the schedule runs. Filters and date ranges apply. Interactive elements like drill-downs do not, but the static PDF is what most executives prefer to skim anyway. For clients who want to interact with the data, include the live report link in the email body.
Step 8: How do you add human context to an automated report?
Automated reports need human context or they feel cold. A number without a story is just a number. A client reading an automated report with no commentary asks "so what?" and the answer needs to come from the account manager, not the dashboard.
Add a text component at the top of the report. Update it every Friday with two or three lines of what happened this week. What went up, what went down, what to expect next week. Use plain language. "Traffic dropped 8% because we paused the Facebook remarketing while we retested creative. Expected to recover next week when the new creative launches."
Looker Studio does not have a native commenting field for scheduled emails to auto-populate, so this stays manual. Five minutes of writing per client per week keeps the report feeling like it came from a person rather than a bot. Marianne Gatlabayan does this every Friday afternoon as the last task of the week for each client, and reports the commentary is the single most-cited reason clients say the reports feel personal.
What are the common client dashboard failures and fixes?
The scheduled email did not send. Check that the Looker Studio owner has edit access to every data source. If you built the report on one account and moved it, the schedule breaks. Also check that the scheduled sender is still an active employee with valid Google credentials. Scheduled sends fail silently when the owner leaves the organization.
Numbers do not match Google Analytics. Looker Studio applies its own default date range and sampling. Set explicit date ranges on every chart and confirm the property matches. If the numbers still differ, check whether GA4 has data thresholding enabled, which suppresses small-count data for privacy reasons and shows different totals than the raw API returns.
Chart shows "Configuration incomplete." The dimension or metric got renamed at the Sheet level. Rebuild the chart or restore the column name. This is why the earlier warning about not renaming columns matters so much. Every rename cascades into broken charts that take time to fix.
What does one dashboard unlock across a client roster?
Once one client dashboard exists, the next one takes 20 minutes. Make a copy of the report in Looker Studio (File > Make a copy). Swap the data source connections to point at the new client's Sheet. Update the client name in the header text. Change the color scheme if the new client's brand requires it.
At three clients, you have three hours a week back. At ten clients, you have a full workday back. That reclaimed time is worth more than the automation itself because it lets you take on more clients without hiring or push your existing clients into higher-margin work.
Freelancers who nail this workflow typically raise their retainer within six months because the perceived quality of the reporting jumps while the delivery cost drops. The client sees a dashboard that updates weekly with professional design and thoughtful commentary. They do not need to know it took the freelancer 15 minutes to produce.
When do you need to move beyond Looker Studio?
Looker Studio free hits limits at a few specific thresholds.
Five blended data sources per chart. Complex marketing stacks with GA4, Google Ads, Facebook Ads, LinkedIn Ads, and TikTok Ads blow past this. The workaround is to pre-blend in Google Sheets via QUERY before Looker Studio touches the data.
Ten million rows per Sheet source. Not usually a client-report problem because most reports aggregate to date-level, but ecommerce clients with transaction-level detail can hit this ceiling. Solution: aggregate the data in the Sheet before Looker Studio reads it.
No native alerting when a KPI drops. You get scheduled sends only, not conditional alerts. For alert-driven reporting, layer on a simple Apps Script that checks KPI values and emails when something crosses a threshold.
For most freelancers and small agencies, none of these matter for years. When they do, Power BI or a paid analytics platform makes sense. Until then, Looker Studio wins on price and speed by a wide margin.
Frequently Asked Questions
Do I need a separate Looker Studio account for each client?
No. You can build all client dashboards under one Looker Studio account. Share individual reports with each client via email or link. This is how most agencies structure their Looker Studio use.
Will Looker Studio work with agencies that require white-labeling?
Looker Studio Pro at $9 per user per month adds team workspaces and Google Cloud project management. For full white-label branding, most agencies use paid tools like AgencyAnalytics or Whatagraph instead. Looker Studio does not offer true white-labeling.
Can this stack handle paid ad reporting?
Yes. Google Ads, Facebook Ads, and LinkedIn Ads all offer native Looker Studio connectors or Sheets integrations via Apps Script. Meta's Marketing API and LinkedIn's Ads API both support the same pull pattern as GA4 in the earlier code example.
What if the client wants a live dashboard instead of a PDF?
Share the Looker Studio link directly. Set permissions to "Anyone with the link can view." The dashboard updates automatically whenever they open it. Some clients prefer the live version because they can filter by date range or campaign themselves.
How long does it take to move one client from manual to automated reporting?
The first client takes about an hour to fully set up if you follow the Apps Script route. Every additional client takes 20 minutes because you copy the template. The break-even against the time spent manually building reports arrives in the first month for any weekly reporting cadence.
What to read next
- Looker Studio Calculated Fields Every Marketer Should Know for KPIs the client asks for
- The Beginner's Guide to Automating Recurring Reports with Google Apps Script for scheduling data pulls
- Setting Up Your First Data Pipeline for when Looker Studio hits its ceiling
About the Author: Marianne Gatlabayan is a Systems Strategist and Industrial Engineer focused on optimizing operations, automating workflows, and driving business growth. With over a decade of experience in analytics, reporting, and process improvement, she helps organizations build scalable systems that turn complexity into measurable results. In her free time, Marianne enjoys discovering and sipping her favorite coffee bean of the week. Connect on LinkedIn.