How to Build Reach & Frequency in Google Sheets & Excel

1. Manual ways to calculate reach & frequency

Before we scale with automation or AI, it helps to master the basics. Here are practical, step-by-step manual methods your team is probably using today.

Method 1: Simple reach & frequency in Google Sheets

1. Open a new Sheet (or reuse your performance sheet).
2. Create columns: Channel, Campaign, Impressions, Unique Reach.
3. Paste in your data from ad platforms (e.g., Meta, Google Ads, LinkedIn).
4. In a new column Frequency, use the classic formula:
   • In cell E2, enter: =C2/D2 (assuming C is Impressions, D is Unique Reach).
5. Copy the formula down the column.
6. Add conditional formatting to highlight frequency bands (e.g., red if >10, yellow if <2).

Official help: review formulas and functions in Sheets here: https://support.google.com/docs

Method 2: Reach & frequency with GRPs in Excel

1. In Excel, create columns: Market, Rating, Spots, Population.
2. Add a GRP column with formula: =B2*C2 (Rating × Spots).
3. Add an Average Persons column: =D2*B2/100 (Population × Rating / 100).
4. Add an Impressions column: =E2*C2 (Average Persons × Spots).
5. If you have estimated unique reach, add a Frequency column: =Impressions / UniqueReach.
6. Summarize by channel using a PivotTable (Insert → PivotTable) to see total GRPs, impressions, and average frequency.

Official Excel help center: https://support.microsoft.com/excel

Method 3: Manual weekly tracking

1. Create a “Week” column in Sheets/Excel.
2. Log impressions and reach per week.
3. Use SUMIF or SUMIFS to aggregate by channel and week.
4. Compute weekly frequency (Impressions / Unique Reach).
5. Plot a line chart to watch for rising frequency (and ad fatigue).

This manual pattern works for small accounts, but quickly becomes brittle once you add more platforms, more weeks, and more campaigns.

2. No-code automation with Google Sheets & Excel

Once you’re tired of copy–paste, no-code tools can take over the repetitive data movement while keeping your calculator inside familiar spreadsheets.

Method 4: Use Google Sheets add-ons and connectors

1. Pick a data connector (e.g., native Google Ads connector or a third-party add-on) that can sync ad data into Sheets.
2. Configure the connector to pull daily metrics: Impressions, Clicks, Unique Reach (if available), Campaign, Ad Set.
3. Point the connector at a raw data tab (e.g., RAW_DATA).
4. In a separate tab (e.g., RF_CALC), use formulas like =UNIQUE(RAW_DATA!A:A) to list campaigns and =SUMIFS to aggregate impressions and reach by campaign and date range.
5. Apply the same Frequency = Impressions / Reach formula, plus any benchmarks you need (e.g., flags for <2x or >10x).
6. Schedule automatic refreshes via the connector’s settings so your calculator updates daily without manual exports.

You can find and manage add-ons inside Sheets via Extensions → Add-ons.

Method 5: Automate Excel with Power Query

1. In Excel, go to Data → Get Data to connect to CSV exports, databases, or even APIs (where supported).
2. Use Power Query to define a repeatable transformation: rename columns, filter dates, standardize channel names.
3. Load the cleaned data into an Excel table (e.g., tblPerformance).
4. Build your reach & frequency formulas against this table using structured references (e.g., =[@Impressions]/[@UniqueReach]).
5. Refresh the query daily or weekly (Data → Refresh All) to update all calculations and charts.

Power Query basics are covered in detail here: https://support.microsoft.com/excel

Method 6: Zapier/Make + Sheets

1. Create a Google Sheet tab for raw events (e.g., AD_PLATFORM_RAW).
2. In Zapier or Make, set up workflows that trigger on new rows in your exported platform sheets or on a schedule pulling via API.
3. Append new rows to AD_PLATFORM_RAW with standardized columns.
4. Use pivot tables or QUERY() (Sheets) to aggregate by campaign and compute reach and frequency.

No-code gives you reliable refreshes, but each new platform or metric usually means another flow to maintain.

3. AI agent methods at scale (with pros & cons)

This is where AI computer agents shine: they can operate your desktop, browser, Google Sheets, and Excel like a tireless analyst—at massive scale.

Method 7: Let an AI agent maintain your Sheets/Excel calculator

Workflow:

1. You define a master reach & frequency template in Google Sheets and an Excel counterpart for offline planning.
2. Each morning, the AI agent:
   • Logs into each ad platform in a browser.
   • Exports performance reports.
   • Cleans column names and date formats.
   • Pastes or imports data into the correct tabs.
   • Recalculates frequency and flags campaigns by benchmark band.
3. The agent then sends you a summary: “5 campaigns below 2x, 3 above 10x; suggested reallocations attached.”

Pros:

• Works across tools with no extra APIs.
• Follows complex, multi-step workflows (logins, 2FA, downloads).
• Transparent execution: you can inspect every action and tweak steps.

Cons:

• Needs an initial “playbook” (which screens, which tabs, which columns).
• Should be tested carefully before running on production accounts.

Method 8: AI agent as a media ops assistant

Workflow:

1. You store your planning assumptions (target frequency range, CPM thresholds, budget caps) in a configuration tab.
2. The AI agent reads this config, opens your reach & frequency calculator, and:
   • Identifies campaigns outside the target frequency range.
   • Simulates budget changes in Excel (e.g., increasing spend on low-frequency, high-ROI channels).
   • Writes recommended changes into a “Proposed Plan” tab.
3. Optionally, the agent drafts emails or Slack messages to clients or internal stakeholders summarizing what should change and why.

Pros:

• Moves beyond reporting into decision support.
• Keeps human reviewers in control while eliminating grunt work.

Cons:

• Still depends on accurate inputs from ad platforms.
• Requires clear guardrails (e.g., never exceed budget X without approval).

Method 9: Fully automated refresh + alerting loop

Workflow:

1. On a schedule (e.g., every night), the AI agent runs the full loop: data collection → Sheets/Excel update → recalculation → alerting.
2. If any channel’s frequency falls below 2 or above 10, the agent:
   • Highlights those rows.
   • Exports a PDF or image of the dashboard.
   • Sends a summary to your sales or marketing Slack channel.

Pros:

• You wake up to answers, not raw data.
• Scales across dozens of accounts without additional headcount.

Cons:

• Requires solid monitoring so you notice if a platform login or layout changes.

By combining familiar tools (Google Sheets and Excel), no-code automation, and AI agents, you gradually move from manual number-crunching to a self-updating, insight-generating reach and frequency engine that your team simply supervises.