Documentation
Google Sheets, table by table
Pull a client's KPIs, prompts, citations and competitors into a Google Sheet with IMPORTDATA, from one read only feed link.
A feed link (see Looker Studio and Google Sheets) is a CSV of one client's numbers that a sheet pulls. The same link reads four files: the KPIs it always served, and the console's Prompts, Citations and Competitors tables. Put each in its own tab and the sheet keeps itself current.
The four files
| File | One row per | The same numbers as |
|---|---|---|
| metrics.csv | number on the client report | The KPIs on the client report, each with its counts and likely range. |
| prompts.csv | tracked or archived prompt | The Prompts table: answers, mentions, citations, first mentions, position and tone. |
| citations.csv | cited domain, most cited first (up to 200) | Citations, Domains: the answers citing each domain out of the answers measured, its links, pages and models. |
| competitors.csv | brand, you first | The Competitors board: mentions, share of the mentions, first mentions, position, citations and tone. |
Set it up
- 1With the client selected, open Connections and create a feed link under Looker Studio and Google Sheets. It takes an admin seat.
- 2Under Use in Google Sheets, copy the formula for each table. The link is shown once, so copy all four before you close the box.
- 3In a Google Sheet, make a tab per table and paste its formula into cell A1, like =IMPORTDATA("your link ending in prompts.csv").
For a link made earlier, change metrics.csv at its end to prompts.csv, citations.csv or competitors.csv. Google Sheets refreshes an IMPORTDATA formula about once an hour.
Which answers the tables read
The three tables open on the console's default view: the last 30 days, and the prompts that do not name the client. Add a query to the end of a link for another view, with the console's own choices.
| Add | Values | Reads |
|---|---|---|
| ?days= | 0, 7, 28, 30, 90, 180 or 365 | The last that many days. 0 is all time. |
| ?prompts= | unbranded, branded or all | The prompts that do not name the client, the ones that do, or every prompt. |
=IMPORTDATA("your link ending in competitors.csv?days=90&prompts=all")Every row says the prompt set and the first and last day it read, in prompt_set, window_from and window_to. The KPIs file takes neither query: it is the client report's numbers, over the report's own days.
How the numbers are written
- Counts first: each rate sits beside its count and what it is out of, as a fraction of one (0.25 is 25 percent).
- Under 8 answers a rate is blank, because the console prints the counts there instead.
- Position is the average place among the brands the client tracks, over the answers that mention it, beside those answers. It is never a rank on an AI platform.
- A competitor's tone is counted on the answers that also name the client, where it is graded, as the board says it.
- Citations are blank for a brand with no known site: not known, never zero.
Keeping it safe
- The four files share one read only credential for one client. It cannot change anything and reads no other client.
- Revoking the link on Connections stops all four files at once. A sheet pulling any of them then shows an import error.
- To rotate a link, create a new one, paste its formulas into the sheet, then revoke the old one.
- Each link has its own hourly read limit, shared by its four files, far above what four hourly refreshes need.
Feed links are part of API access, which starts on the Monitor plan. A link keeps working after a plan change; creating a new one needs the plan.