Sitemap Import & Performance

Overview

Below you'll find a Google Sheet template that can be scheduled to routinely import your website's full sitemap and add Google Analytics stats to each page listing to do any analysis you please based on page performance, page titles, page descriptions, or even page labels (see second option) that you can customize as ways of slicing up the data.

Sitemap Performance Report – Without Custom Labels

1. Make a copy of the sheet above to get started and organize in your Drive as needed. Definitely add the client name to the name of the file if you're doing multiple of these.

2. To get started, run down the Settings tab and update all custom fields.

   1. • Enter the URL of your sitemap

      • Customize the schedule at which you'd like to refresh your sitemap. Would recommend doing this weekly, as it takes some time to index all of the Page Titles & Descriptions when the import is refreshed.

      • Go to Sheet Controls > Sitemap Import Scheduling Options > Schedule Sitemap Import and a trigger will be created to refresh the sitemap at the schedule you selected.

      • Link your Google Analytics account – make sure you're signed in with the account that has access to the appropriate GA account! To update the options in the dropdown, go to Sheet Controls > Refresh Google Analytics Account List. You may have to authorize the script and try again if the dropdown options don't change. When they've updated, select the View from which you'd like to pull data.

      • Update the date range as needed.  The template uses formulas in the Start Date and End Date fields to dynamically pull the last 180 Days, but you can customize as you need.

      • If you'd like, you can make adjustments to the Metrics being imported. Each cell has dropdowns for customizing the imported metrics. You won't be able to customize the dimensions, of course, since those are keyed up to align with the Sitemap page listing.

      • Update sorting (doesn't really do anything here, but you can) and filtering, as needed.

      • Schedule the refresh of Google Analytics data – this script runs relatively quickly, so updating daily will just keep the metrics fresh. But you can set it to a lower frequency if you'd like.

      • After updating the schedule in that table, go to Sheet Controls > Google Analytics Import Scheduling Options > Schedule Google Analytics Data Refresh and a new trigger will be created at the specified schedule. If you'd like to update this at any point, simply edit the schedule in row 45 and select that menu option again.

   2. Test everything manually to make sure it's working!

      • Select Sheet Controls > Manually Refresh Sitemap

      • Select Sheet Controls > Download Google Analytics Data

      • Check the Sitemap tab to see if your page list has been updated and the Google Analytics data has been added.

Sitemap Performance Report – With Custom Labels

Same thing as the sheet above, but you can now also assign up to two labels per page based on a RegEx table at the bottom of the settings page. Helpful for categorizing certain pages on the site at a high, dynamic level and breaking out performance. Labels can be added/edited at any time. They update in formulas in Columns G and H on the Sitemap tab.

1. Follow the steps from the first sheet – they should be exactly the same through the end.

2. In Row 48, you'll see the Page Categorization section.

3. Select which fields you'd like the RegEx to search – just the URL, the Page Title and/or the Page Description. When a match is found in any of the selected fields, the corresponding label will be added to that row.

4. In the Green RegEx table, you can structure your Regular Expressions and their corresponding labels. Note: if a page matches more than one label, the labels will be prioritized in the order that they are entered into the table. And only two labels can be added per row. So the first two matches found as it looks down the table will be assigned. If only one match is found, Column H will just be empty.

5. You can add exclusion terms in the Red table. The exclusions are just blanket exclusions that will prevent any field that contains them from being matched – i.e. it will just ignore the word that you enter and if it matches a RegEx, it will not get the label assigned. But the rest of the words around it can still be matched. The "Exclusion Type" field really doesn't do anything other than help you keep track of why you added a particular exclusion.

6. That should do it!