Skip to content

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?

How do I give the sheet to the ERP?

  1. In Google Sheets, create a new sheet (for example ‘My website map’).
  2. 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.
  3. Copy the sheet’s address from the browser.
  4. 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.
  5. Click Test connection. The Sheet line says whether the account can write to it.
  6. 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:

ColumnWhat it contains
rutaThe page’s address without the domain (/control-horario/). It is the key the dashboard uses to join the sheet with Search Console and Analytics
urlThe page’s full address
tituloIts title
ambitoComercial, Pilar, Función, Producto, Blog, Glosario, Ayuda, Categoría or Otra
clusterIts cluster (empty if it has none)
ejeThe cluster’s axis: Producto or Tema
pilarWhether it is its cluster’s pillar page
busqueda_objetivoThe query it targets
intencionCompra, Aprender, Pregunta, Cómo se hace or Marca
idiomaThe page’s language (es, en…)
estadoPublicada, Borrador, Pendiente, Programada, Privada or Retirada
indexableWhether Google can have it in its index: published, without ‘noindex’ and without a canonical to another address
fecha_publicacionThe day it was published
choques_abiertosHow many open conflicts it has with other pages of yours
ultima_revisionThe day of its latest pre-publication review
semaforoThe 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.

  1. Click Create my dashboard in Looker Studio.
  2. In Looker Studio, click Edit and share and accept the data sources: they connect with your Google account.
  3. 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?

  1. At <https://lookerstudio.google.com>, create a Blank report.
  2. 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.
  3. Add the Google Analytics source: your account and your GA4 property.
  4. Add the Google Sheets source: your sheet, the Mapa tab, with Use first row as headers ticked.
  5. In the Search Console and Analytics sources, create the ruta field with the formula in the next section.
  6. 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:

SourceWhat it givesruta field
Search ConsoleThe full address (Landing Page; in Spanish, Página de destino), sometimes with a #… anchorREGEXP_REPLACE(REGEXP_EXTRACT(Landing Page, "^https?://[^/]+(/[^?#]*)"), "^(.*/[^/.]+)$", "\\1/")
AnalyticsThe 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.

FieldWhereFormulaWhat it is
marcaSearch ConsoleCASE WHEN REGEXP_CONTAINS(LOWER(Query), "mi ?marca") THEN "Marca" ELSE "Sin marca" ENDWhether the query names your brand (here, ‘mimarca’ or ‘mi marca’)
busqueda_normalizadaSearch ConsoleREGEXP_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_normalizadoSheetThe same formula on busqueda_objetivoThe target query written the same way
es_la_objetivoBlend of Search Console and sheetCASE WHEN busqueda_normalizada = objetivo_normalizado THEN "Sí" ELSE "No" ENDWhether the query that brings the visit is the one the page targets
urls_propiasSearch ConsoleCOUNT_DISTINCT(ruta)How many of your pages appear for a query: with two or more, they overlap (cannibalisation)
ctrSearch ConsoleSUM(Url Clicks) / SUM(Impressions) (percentage format)Clicks per impression
conversionAnalyticsSUM(Key events) / SUM(Sessions) (percentage format)Key events per session that lands on that page
dias_desde_publicacionSheetDATETIME_DIFF(TODAY(), fecha_publicacion, DAY)Days since it was published
tramo_publicacionSheetCASE 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" ENDHow 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?

PageSourcesWhat it showsWhat to look at
Performance by cluster and scopeSearch Console + sheetImpressions, clicks and ctr by cluster and by ambito, and how they evolve week by weekWhich topics bring traffic and which don’t take off
CannibalisationSearch 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_objetivoThe queries that two of your pages share. They are the same ones the ERP shows as shared-query conflicts
Sales pages: from query to conversionSearch Console + Analytics + sheet, filtered by ambito Comercial (and Pilar)By page: impressions, clicks, sessions, key events and conversionWhich sales pages are seen but don’t convert, or convert and are hardly seen
OpportunitiesSearch Console + sheetQueries and pages with an average position between 5 and 20 and many impressions (for example, 100 or more), from most to fewest impressionsWhat is close to Google’s first page: improving that page pays off more than writing another
Recent publicationsSheet + Search Console + Analytics, filtered by dias_desde_publicacion of 90 or lessBy tramo_publicacion: impressions, clicks, sessions and key events of what was published in the last 30, 60 and 90 daysWhether what is published is starting to get traffic
Brand versus genericSearch ConsoleImpressions, clicks and ctr by marca, by weekHow 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?

Back to search visibility.

More about Search positioning