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, inpfrom googlesearchconsole_pagespeed_analysiswhere sitemap_url = 'https://example.io/sitemap-0.xml';
select sitemap_url, loc, overall_loading_experience, analysis_utc_timestamp, cls, ttfb, fcp, lcp, fid, inpfrom googlesearchconsole_pagespeed_analysiswhere 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_rangefrom googlesearchconsole_pagespeed_analysiswhere loc = 'https://example.io/';
select loc, cls, cls_percentile, cls_bucket_rangefrom googlesearchconsole_pagespeed_analysiswhere 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_rangefrom googlesearchconsole_pagespeed_analysiswhere loc = 'https://example.io/' and strategy = 'MOBILE';
select loc, cls, cls_percentile, cls_bucket_rangefrom googlesearchconsole_pagespeed_analysiswhere 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_rangefrom googlesearchconsole_pagespeed_analysiswhere loc = 'https://example.io/';
select loc, fcp, fcp_percentile, fcp_bucket_rangefrom googlesearchconsole_pagespeed_analysiswhere 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_rangefrom googlesearchconsole_pagespeed_analysiswhere loc = 'https://example.io/';
select loc, fid, fid_percentile, fid_bucket_rangefrom googlesearchconsole_pagespeed_analysiswhere loc = 'https://example.io/';
Schema for googlesearchconsole_pagespeed_analysis
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form. | |
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. | |
loc | text | = | The URL of the page. |
overall_loading_experience | text | The loading experience of the page. | |
project | text | =, !=, ~~, ~~*, !~~, !~~* | The GCP Project associated with the credentials in use. |
sitemap_url | text | = | The URL of the sitemap. |
sp_connection_name | text | =, !=, ~~, ~~*, !~~, !~~* | Steampipe connection name. |
sp_ctx | jsonb | Steampipe context in JSON form. | |
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