v1.0 for Steampipe, Powerpipe, Flowpipe, 116 plugins, and 44 mods →

turbot/googlesearchconsole

steampipe plugin install googlesearchconsolesteampipe plugin install googlesearchconsole

googlesearchconsole_indexing_status googlesearchconsole_pagespeed_analysis googlesearchconsole_pagespeed_analysis_aggregated googlesearchconsole_site googlesearchconsole_sitemap

Table: googlesearchconsole_pagespeed_analysis_aggregated - Query PageSpeed analysis on the site using SQL

PageSpeed Insights (PSI) reports on the user experience of a page on both mobile and desktop devices, and provides suggestions on how that page may be improved.

Table Usage Guide

The googlesearchconsole_pagespeed_analysis_aggregated table runs PageSpeed analysis on the site, and returns aggregated PageSpeed scores.

Important Notes You must specify the following column in where or join clause to query the table:

site_url: The URL of the property as defined in Search Console. Examples: http://www.example.com/ for a URL-prefix property, or sc-domain:example.com for a Domain property

Examples

Basic pagespeed analysis info

This query fetches the overall loading experience and individual PageSpeed metrics for a site, providing insights into the user experience and performance of the site.

select
  site_url,
  overall_loading_experience,
  analysis_utc_timestamp,
  cls,
  ttfb,
  fcp,
  lcp,
  fid,
  inp
from
  googlesearchconsole_pagespeed_analysis_aggregated
where
  site_url = 'https://example.io/';

select
  site_url,
  overall_loading_experience,
  analysis_utc_timestamp,
  cls,
  ttfb,
  fcp,
  lcp,
  fid,
  inp
from
  googlesearchconsole_pagespeed_analysis_aggregated
where
  site_url = 'https://example.io/';

Get aggregated Cumulative Layout Shift (CLS) for a site

This SQL query fetches the Cumulative Layout Shift (CLS) scores, percentile rankings, and score distribution for your site, essential for assessing visual stability and improving user experience.

select
  site_url,
  cls,
  cls_percentile,
  cls_bucket_range
from
  googlesearchconsole_pagespeed_analysis_aggregated
where
  site_url = 'https://example.io/';

select
  site_url,
  cls,
  cls_percentile,
  cls_bucket_range
from
  googlesearchconsole_pagespeed_analysis_aggregated
where
  site_url = 'https://example.io/';

Get aggregated Cumulative Layout Shift (CLS) for a site in Mobile

Optimize mobile user experience with this query that retrieves mobile-specific CLS data for your site, highlighting the need for stable content on mobile devices.

select
  site_url,
  cls,
  cls_percentile,
  cls_bucket_range
from
  googlesearchconsole_pagespeed_analysis_aggregated
where
  site_url = 'https://example.io/'
  and strategy = 'MOBILE';

select
  site_url,
  cls,
  cls_percentile,
  cls_bucket_range
from
  googlesearchconsole_pagespeed_analysis_aggregated
where
  site_url = 'https://example.io/'
  and strategy = 'MOBILE';

Get aggregated First Contentful Paint (FCP) for a site

This query provides First Contentful Paint (FCP) metrics for your site, key to understanding and enhancing perceived page load speed, a critical factor in SEO and user satisfaction.

select
  site_url,
  fcp,
  fcp_percentile,
  fcp_bucket_range
from
  googlesearchconsole_pagespeed_analysis_aggregated
where
  site_url = 'https://example.io/';

select
  site_url,
  fcp,
  fcp_percentile,
  fcp_bucket_range
from
  googlesearchconsole_pagespeed_analysis_aggregated
where
  site_url = 'https://example.io/';

Get aggregated First Input Delay (FID) for a site

Identify and improve interactivity on your site by fetching First Input Delay (FID) scores, a vital metric for enhancing responsiveness and user engagement.

select
  site_url,
  fid,
  fid_percentile,
  fid_bucket_range
from
  googlesearchconsole_pagespeed_analysis_aggregated
where
  lsite_urloc = 'https://example.io/';

select
  site_url,
  fid,
  fid_percentile,
  fid_bucket_range
from
  googlesearchconsole_pagespeed_analysis_aggregated
where
  site_url = 'https://example.io/';

Schema for googlesearchconsole_pagespeed_analysis_aggregated

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
analysis_utc_timestamp	text		The timestamp of the analysis.
cls	text		The Cumulative Layout Shift (CLS) of the page.
cls_bucket_range	jsonb		The bucket range of the Cumulative Layout Shift (CLS) of the page.
cls_percentile	bigint		The percentile of the Cumulative Layout Shift (CLS) of the page.
fcp	text		The First Contentful Paint (FCP) of the page.
fcp_bucket_range	jsonb		The bucket range of the First Contentful Paint (FCP) of the page.
fcp_percentile	bigint		The percentile of the First Contentful Paint (FCP) of the page.
fid	text		The First Input Delay (FID) of the page.
fid_bucket_range	jsonb		The bucket range of the First Input Delay (FID) of the page.
fid_percentile	bigint		The percentile of the First Input Delay (FID) of the page.
id	text		The ID of the page.
inp	text		The Interaction to Next Paint (INP) of the page.
inp_bucket_range	jsonb		The bucket range of the Interaction to Next Paint (INP) of the page.
inp_percentile	bigint		The percentile of the Interaction to Next Paint (INP) of the page.
lcp	text		The Largest Contentful Paint (LCP) of the page.
lcp_bucket_range	jsonb		The bucket range of the Largest Contentful Paint (LCP) of the page.
lcp_percentile	bigint		The percentile of the Largest Contentful Paint (LCP) of the page.
overall_loading_experience	text		The loading experience of the page.
site_url	text	=	The URL of the site.
strategy	text	=	The analysis strategy (desktop or mobile) to use. Default is desktop.
ttfb	text		The Time to First Byte (TTFB) of the page.
ttfb_bucket_range	jsonb		The bucket range of the Time to First Byte (TTFB) of the page.
ttfb_percentile	bigint		The percentile of the Time to First Byte (TTFB) of the page.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- googlesearchconsole

You can pass the configuration to the command with the --config argument:

steampipe_export_googlesearchconsole --config '<your_config>' googlesearchconsole_pagespeed_analysis_aggregated