The Looker Studio dashboard: your content map alongside what Google measures
Looker Studio is Google’s free tool for building dashboards. It reads Search Console and Analytics directly, but it doesn’t know what each page of your website targets, which cluster it belongs to or whether it is a sales page or a blog post. The ERP knows that. That is why the ERP writes its content map to a Google sheet, and the dashboard brings the three things together: what you decided in the ERP, what Google shows and what converts.
What do I need to set up the dashboard?
- Your website connected with Google in the ERP, with its service account (see connect your website with Google).
- An empty Google sheet, shared with that service account’s email as Editor. The ERP writes the map to it.
- Your own Google account with access to your Search Console property, your Analytics property and the sheet. Looker Studio reads all three with your account, not with the ERP’s.
How do I give the sheet to the ERP?
- In Google Sheets, create a new sheet (for example ‘My website map’).
- Click Share and add the service account’s email (shown on the ERP’s Google card, something like
erp@tu-proyecto.iam.gserviceaccount.com) with the Editor permission. - Copy the sheet’s address from the browser.
- In the ERP, open your website in the Sites tab and, on the Google card, paste it into Sheet for Looker Studio. The whole address is fine: the ERP keeps the sheet’s identifier.
- Click Test connection. The Sheet line says whether the account can write to it.
- Click Write the sheet now to have it straight away, without waiting for the next synchronisation.
From then on, each synchronisation rewrites the sheet with the day’s map. With Open the sheet you see it in Google Sheets.
What does the ERP write to the sheet?
A tab called Mapa, with one row for each page on the map (drafts and withdrawn pages too: in the dashboard they are filtered by their status) and these columns:
| Column | What it contains |
|---|---|
ruta | The page’s address without the domain (/control-horario/). It is the key the dashboard uses to join the sheet with Search Console and Analytics |
url | The page’s full address |
titulo | Its title |
ambito | Comercial, Pilar, Función, Producto, Blog, Glosario, Ayuda, Categoría or Otra |
cluster | Its cluster (empty if it has none) |
eje | The cluster’s axis: Producto or Tema |
pilar | Whether it is its cluster’s pillar page |
busqueda_objetivo | The query it targets |
intencion | Compra, Aprender, Pregunta, Cómo se hace or Marca |
idioma | The page’s language (es, en…) |
estado | Publicada, Borrador, Pendiente, Programada, Privada or Retirada |
indexable | Whether Google can have it in its index: published, without ‘noindex’ and without a canonical to another address |
fecha_publicacion | The day it was published |
choques_abiertos | How many open conflicts it has with other pages of yours |
ultima_revision | The day of its latest pre-publication review |
semaforo | The traffic light of that review: Verde, Ámbar or Rojo |
There are no impressions, clicks or visits: the dashboard reads those directly from Google, always up to date.
Can I write in the sheet?
In the Mapa tab, no: the ERP rewrites it in full in each synchronisation and whatever you put in it is lost. What you decide about a page (its cluster, its target query…) is decided in the ERP, on the content map; the ERP never reads the sheet.
The other tabs in the sheet are yours: the ERP doesn’t touch them. If you already had a tab called ‘Mapa’, the ERP doesn’t overwrite it and asks you to rename it.
How do I create the dashboard in one click?
If your company has a template report ready, the Google card shows Create my dashboard in Looker Studio. It opens Looker Studio with a copy of the dashboard already connected to your sheet, your Search Console property and your Analytics property.
- Click Create my dashboard in Looker Studio.
- In Looker Studio, click Edit and share and accept the data sources: they connect with your Google account.
- Check in Resource → Manage added data sources that each source is your website’s.
The button only shows when the website has a sheet and a Search Console property. If it doesn’t show, set up the dashboard by hand with the steps in the following sections.
How do I set up the dashboard by hand?
- At <https://lookerstudio.google.com>, create a Blank report.
- Add the Search Console source: your property, the URL impression table (Impresión de URL in Spanish) and the Web search type. This table is the one that has the page for each query.
- Add the Google Analytics source: your account and your GA4 property.
- Add the Google Sheets source: your sheet, the Mapa tab, with Use first row as headers ticked.
- In the Search Console and Analytics sources, create the
rutafield with the formula in the next section. - Create the other calculated fields and the dashboard pages, as explained below.
The names of the sheet’s columns don’t change: in Looker Studio you can give them whatever name you like (for example, ‘Target query’ for busqueda_objetivo) without the dashboard stopping working.
How are Search Console, Analytics and the sheet joined?
By each page’s path: its address without the domain, with its trailing slash and without parameters. The sheet already has it like that. Search Console and Analytics each give the address in their own way, and in each source a ruta field is created that makes it the same:
| Source | What it gives | ruta field |
|---|---|---|
| Search Console | The full address (Landing Page; in Spanish, Página de destino), sometimes with a #… anchor | REGEXP_REPLACE(REGEXP_EXTRACT(Landing Page, "^https?://[^/]+(/[^?#]*)"), "^(.*/[^/.]+)$", "\\1/") |
| Analytics | The landing page without the domain (Landing page; in Spanish, Página de destino) | REGEXP_REPLACE(REGEXP_REPLACE(Landing page, "[?#].*$", ""), "^(.*/[^/.]+)$", "\\1/") |
The first part removes the domain, the parameters and the anchor; the second adds the trailing slash to addresses that aren’t a file. When you type the formula, the Looker Studio editor suggests the field name as it appears in your language.
To bring two or three sources together in a chart you use Blend data (Combinar datos in Spanish): the condition is ruta equal to ruta, and the join, Left outer (externa izquierda in Spanish) from the Google source. That way each Google row gets its page’s scope, cluster and target query.
What calculated fields do I need?
With these fields you build all the dashboard pages. Where it says ‘the brand’, put the ways your brand is written.
| Field | Where | Formula | What it is |
|---|---|---|---|
marca | Search Console | CASE WHEN REGEXP_CONTAINS(LOWER(Query), "mi ?marca") THEN "Marca" ELSE "Sin marca" END | Whether the query names your brand (here, ‘mimarca’ or ‘mi marca’) |
busqueda_normalizada | Search Console | REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(LOWER(TRIM(Query)), "[áàäâ]", "a"), "[éèëê]", "e"), "[íìïî]", "i"), "[óòöô]", "o"), "[úùüû]", "u") | The query without capitals or accents (the ‘ñ’ stays) |
objetivo_normalizado | Sheet | The same formula on busqueda_objetivo | The target query written the same way |
es_la_objetivo | Blend of Search Console and sheet | CASE WHEN busqueda_normalizada = objetivo_normalizado THEN "Sí" ELSE "No" END | Whether the query that brings the visit is the one the page targets |
urls_propias | Search Console | COUNT_DISTINCT(ruta) | How many of your pages appear for a query: with two or more, they overlap (cannibalisation) |
ctr | Search Console | SUM(Url Clicks) / SUM(Impressions) (percentage format) | Clicks per impression |
conversion | Analytics | SUM(Key events) / SUM(Sessions) (percentage format) | Key events per session that lands on that page |
dias_desde_publicacion | Sheet | DATETIME_DIFF(TODAY(), fecha_publicacion, DAY) | Days since it was published |
tramo_publicacion | Sheet | CASE WHEN dias_desde_publicacion <= 30 THEN "0-30 días" WHEN dias_desde_publicacion <= 60 THEN "31-60 días" WHEN dias_desde_publicacion <= 90 THEN "61-90 días" ELSE "Más de 90 días" END | How long ago it was published, in bands |
The scope, the cluster, the axis and the intent don’t need a formula: they come in the sheet.
What pages does the dashboard have?
| Page | Sources | What it shows | What to look at |
|---|---|---|---|
| Performance by cluster and scope | Search Console + sheet | Impressions, clicks and ctr by cluster and by ambito, and how they evolve week by week | Which topics bring traffic and which don’t take off |
| Cannibalisation | Search Console (+ sheet) | The queries with urls_propias of 2 or more, with the pages that appear for each one, their impressions and their position, and es_la_objetivo | The queries that two of your pages share. They are the same ones the ERP shows as shared-query conflicts |
| Sales pages: from query to conversion | Search Console + Analytics + sheet, filtered by ambito Comercial (and Pilar) | By page: impressions, clicks, sessions, key events and conversion | Which sales pages are seen but don’t convert, or convert and are hardly seen |
| Opportunities | Search Console + sheet | Queries and pages with an average position between 5 and 20 and many impressions (for example, 100 or more), from most to fewest impressions | What is close to Google’s first page: improving that page pays off more than writing another |
| Recent publications | Sheet + Search Console + Analytics, filtered by dias_desde_publicacion of 90 or less | By tramo_publicacion: impressions, clicks, sessions and key events of what was published in the last 30, 60 and 90 days | Whether what is published is starting to get traffic |
| Brand versus generic | Search Console | Impressions, clicks and ctr by marca, by week | How much traffic comes from people who already know you and how much from people looking for what you sell |
What should I know about the dashboard?
- Without key events in Analytics, conversions show as zero. Mark what counts on your website (a form submitted, a purchase) as key events in Analytics → Admin → Key events.
- Search Console runs two or three days behind and doesn’t provide very infrequent queries: the sum of a page’s queries is lower than its impressions.
- The average position in a blend is an average of averages. For a page’s exact position, look at it in a chart with Search Console only.
- Looker Studio requests the data from Google each time a chart is opened. With many charts that blend sources, Analytics may respond that the property’s quota has run out. Put fewer charts per page, shorten the period or use Extract data on the heaviest sources. When your website grows a lot, the way out is to export Search Console and Analytics to BigQuery and have the dashboard read from there.
- The sheet is brought up to date in each synchronisation. What you change in the ERP (a cluster, a target query) reaches the dashboard after the next synchronisation or after Write the sheet now.
Back to search visibility.
More about Search positioning
- Guest posts
- Proposed clusters
- Connect your website with Google: Search Console and Analytics
- Query real search data: what it costs
- Cross-checks between SEO and ads
- Add your website and test the connection
- Search data: volume, difficulty and Google results
- Marketing guidelines: what marketing tells the plan
- The editorial calendar
- A piece’s brief
- The content map: what each page of your website targets
- The editorial plan