The Website Quality Audit Sheets workbook is, we believe, the most crucial aspect of the Recipe.
It allows you to make decisions on your data from the comfort of the Sheets interface, then push those decisions up to BigQuery to be accessed later.
The three key decisions you'll use it to make:
- Page type review: Which category does each URL belong to - blog, category, product, etc. These page types will trigger different actions - for example, the WQA only recommends pruning low-traffic blog pages, not core info pages.
- Action review: Are recommended actions relevant? Do you plan to work them, or should they be ignored?
- Action update: For actions that you've decided to take, how have they progressed? Are they blocked, ready for review, or completed?
Even if you're not actively working actions, this browser can be helpful to dig into the various actions that you + your team have open across sites.
Note: You only need to set up the workbook one time for each BigQuery project you're using. It can be used to review actions + page types across all of your sites that live within that project.
Setting up your workbook
As with any of our Sheets templates, the WQA workbook contains a 'Getting Started' tab with detailed step-by-step instructions.
There are 3 settings to configure:
- Your BigQuery project ID + dataset name. Your dataset name will be wqa_your_org_name. So if your org was named 'QR', your dataset would be wqa_qr.
- The site you'd like to review. The 'Getting Started' tab contains pivot tables with counts of page types + actions for each site, so you know what needs attention.
- [Optional] The page type to review. For sites larger than 20k pages that'd likely bump up against Sheets data size limits, selecting a single page type allows you to keep the Sheet running quickly.
Once you've selected those 3, you can pull in page type + action details using the 'Query.Recipes' menu (next to the Help menu):
Reviewing page types
Page type reviews happen on the Page Type Review tab.
You'll likely only want to review each page type classification once, unless your site structure changes significantly.
Use the filters at the top-left of the tab to review a specific segment:
- Page type segment
- Review status (Unreviewed / Reviewed)
- 1st subfolder: search for a string that it may contain
- Sitemap contains: to tag all pages in a post-sitemap, product-sitemap, etc with the same page type
Each time you change the filters, any page type updates that you've written in will be deleted, so be sure to commit your changes before changing filters.
When you're ready to make a page type decision for a given URL, use the dropdown menu in column I of the Page Type Review tab. Then, push them to BigQuery using the Query.Recipes menu (Push Updates to BigQuery -> Commit Action Updates).
If you'd like to use page types that aren't present in the dropdown, you can add them to the validation list from column H of the workbook_settings hidden tab.
When the push to BigQuery completes, it'll also pull in updated page type information, which will reflect your recent changes.
The counter in the top-left corner of the Page Type Review tab will also tick up:
If you believe your page type updates didn't go through to BigQuery, please contact support via live chat and we'll help you debug.
Reviewing action recommendations
The process for reviewing actions is very similar to the review process for page types.
On the Action Review tab, you'll see a summary pivot table of your selected site's actions + statuses:
From there, you can select a few filters at the top-left:
- [REQUIRED] Action Type: Selecting an action type (Technical, Off-page, etc) pulls in those specific URLs with actions recommended in that bucket, as well as analytics / crawl data relevant to make a decision on that action recommendation.
- Action: Filter for a specific action within the bucket (ie 'Update metas to include main or best keyword' for 'On-page: meta tags' actions).
- Status: From Unreviewed through to Completed
- Priority: Low / medium / high, as set by you
- Page Type
Each time you reset these filters, any updates that you'd written into the Sheet will be cleared.
To update an action, update values in columns C-F:
- Update Status: See notes below
- Modify Action: To override the recommended action with a separate action of your own
- Set Priority: Low / medium / high
- Notes: Any rationale for your decision, that'll help you or your team navigate actions when you review them next
To start with, we recommend reviewing all actions with status = Unreviewed or Updated (meaning the recommendation changed since your last review).
If you find an action to be relevant, and plan to work it at some point, you can mark it as To schedule and circle back to it later.
If an action is not something you'll work at any point, you can mark it as status = Not relevant, which will filter it out of future recommendations. Adding notes
If you want to only update *one* of those values (for example, move the Status from Final Review to Completed), you can just modify that single column. The rest of your values will remain as-is.
To push your updates to BigQuery, select Push Updates to BigQuery -> Commit Action Updates from the Query.Recipes menu:
When your updates are pushed to BigQuery, the workbook will also pull in updated values back from BigQuery, to reflect your modifications.
The counter in the top-left corner of the Actions Review will also tick up to reflect your updates:
The updates you've made will also be reflected on your team's Data Studio report, which we use for gauging the pace of progress.
Adding custom views to your Workbook
Consider this template to be your bridge between Sheets and BigQuery.
If you so desire, you can use it to set up custom queries of your WQA data into Sheets (unrelated to actions or page types).
Common uses for custom queries for keyword research (using GSC data) or detecting traffic anomalies for pages using GA data.
Custom queries can be added to the bottom of the Query from BQ tab:
- Group: set to custom
- Sheets tab name: the tab you'd like to write data to (data will write to cell A1)
- Query: the BigQuery SQL query to run. We recommend first validating your query in the BigQuery console. Make sure to prepend #standardSQL to your query, as you see for the other queries in the Query from BQ tab.
- Append results?: Generally we set this to FALSE, which will clear the tab each time the query runs
Columns E-F (Last run, Error message, Columns) are system columns that should be left blank.
Running Pull from BigQuery -> Get Custom Queries from the Query.Recipes menu will refresh all of your custom queries.
As always, drop us a line in live chat and we'll help you get cooking.