Weekly report in Google Sheets
A self-updating sheet with your visibility trend, competitor table and outreach list.
YOUR_API_KEY with a key from Settings → API and MCP. Keys are shown once when you create them.What you'll build
- A data tab per dataset that refreshes on its own
- A visibility trend for you and your competitors, weighted correctly
- A list of sites to pitch, sorted by how often they're cited without you
You need: a read key, created in Settings → API and MCP and named after the sheet.
1. Pull the data
Create four tabs named visibility, prompts, sources and answers. In cell A1 of each, enter its dataset:
=IMPORTDATA("https://getonym.com/api/v1/domains/acme.example/datasets/visibility.csv?range=90d&key=YOUR_API_KEY")Repeat with prompts.csv, sources.csv and answers.csv. Each tab fills with a header row and data, and Sheets refreshes it about once an hour.
2. Visibility by run and brand
Add a tab named trend. In A1:
=QUERY(visibility!A:I, "select A, sum(F)/sum(E) where A is not null group by A pivot C", 1)That gives one row per run date and one column per brand. Each cell divides the summed named by the summed answers across surfaces, so surfaces with more answers count for more. Format the columns as percentages, select the table and insert a line chart.
To see a single surface, add a condition: where A is not null and B = 'ChatGPT'.
Don't average the rate column. A surface with 3 answers would count as much as one with 300. Summing named and answers first gives the true rate.
3. Who to pitch
In a new tab:
=QUERY(sources!A:H, "select A, C, H, D where H > 0 and C <> 'Yours' and C <> 'Competitor' order by H desc limit 20", 1)That lists the 20 third-party sites most often cited in answers that name a competitor and not you, with their type and total citations. Find outreach targets explains what to do with them.
4. Share it
Share the sheet with your team. Anyone who can open it can see the key in the formulas; if the sheet leaves your company, revoke the key and create a new one.