turbot/googlesearchconsole
steampipe plugin install googlesearchconsole

Table: googlesearchconsole_pagespeed_analysis - Query PageSpeed analysis per page 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 table allows users to analyze PageSpeed metrics for each page on their site, and compare the performance of pages across different devices.

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

  • sitemap_url: The URL of the sitemap that was submitted to Google Search Console. Example: https://www.example.com/sitemap.xml

Examples

Basic pagespeed analysis info

Retrieve essential PageSpeed insights for your site from its sitemap, including metrics like CLS, TTFB, FCP, LCP, FID, and INP, crucial for optimizing loading times and user experience.

select
sitemap_url,
loc,
overall_loading_experience,
analysis_utc_timestamp,
cls,
ttfb,
fcp,
lcp,
fid,
inp
from
googlesearchconsole_pagespeed_analysis
where
sitemap_url = 'https://example.io/sitemap-0.xml';
select
sitemap_url,
loc,
overall_loading_experience,
analysis_utc_timestamp,
cls,
ttfb,
fcp,
lcp,
fid,
inp
from
googlesearchconsole_pagespeed_analysis
where
sitemap_url = 'https://example.io/sitemap-0.xml';

Get Cumulative Layout Shift (CLS) for a specific page

Fetch detailed CLS data for https://example.io/, including scores, percentiles, and bucket ranges, to improve visual stability and enhance the user's visual experience on your page.

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

Get Cumulative Layout Shift (CLS) for a specific page in Mobile

Optimize mobile user experience by analyzing mobile-specific CLS data for https://example.io/, crucial for maintaining content stability on mobile devices.

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

Get First Contentful Paint (FCP) for a specific page

This query provides insights into the First Contentful Paint (FCP) for https://example.io/, a key metric for evaluating and enhancing the perceived speed of content rendering on a page.

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

Get First Input Delay (FID) for a specific page

Improve page interactivity with detailed First Input Delay (FID) metrics for https://example.io/, essential for assessing and enhancing user interaction responsiveness.

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

Schema for googlesearchconsole_pagespeed_analysis

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
analysis_utc_timestamptextThe timestamp of the analysis.
clstextThe Cumulative Layout Shift (CLS) of the page.
cls_bucket_rangejsonbThe bucket range of the Cumulative Layout Shift (CLS) of the page.
cls_percentilebigintThe percentile of the Cumulative Layout Shift (CLS) of the page.
fcptextThe First Contentful Paint (FCP) of the page.
fcp_bucket_rangejsonbThe bucket range of the First Contentful Paint (FCP) of the page.
fcp_percentilebigintThe percentile of the First Contentful Paint (FCP) of the page.
fidtextThe First Input Delay (FID) of the page.
fid_bucket_rangejsonbThe bucket range of the First Input Delay (FID) of the page.
fid_percentilebigintThe percentile of the First Input Delay (FID) of the page.
idtextThe ID of the page.
inptextThe Interaction to Next Paint (INP) of the page.
inp_bucket_rangejsonbThe bucket range of the Interaction to Next Paint (INP) of the page.
inp_percentilebigintThe percentile of the Interaction to Next Paint (INP) of the page.
lcptextThe Largest Contentful Paint (LCP) of the page.
lcp_bucket_rangejsonbThe bucket range of the Largest Contentful Paint (LCP) of the page.
lcp_percentilebigintThe percentile of the Largest Contentful Paint (LCP) of the page.
loctext=The URL of the page.
overall_loading_experiencetextThe loading experience of the page.
projecttext=, !=, ~~, ~~*, !~~, !~~*The GCP Project associated with the credentials in use.
sitemap_urltext=The URL of the sitemap.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
strategytext=The analysis strategy (desktop or mobile) to use. Default is desktop.
ttfbtextThe Time to First Byte (TTFB) of the page.
ttfb_bucket_rangejsonbThe bucket range of the Time to First Byte (TTFB) of the page.
ttfb_percentilebigintThe 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