How to Use Google Data Studio to Automate Post-Publish Content Review [Template Included]

Posting fresh content is a lot of work. So, you want to make sure you get as much value from it as possible. Tracking the traffic can make it easier to determine if a page requires attention or updating.

This process can be streamlined by automating a report. The data still needs to be reviewed but at least getting to that point can be automated.

We’re going to walk through how to create a dashboard to track pageviews for your top five articles. We’ll track the traffic based on the article published date. We’ll review all pageviews and specifically monitor traffic from 30 days, 60 days, and 90 days after publish.

Here’s an example of what the final dashboard will look like:

At the end of this tutorial you’ll have the information you need to duplicate this dashboard for yourself. Hopefully, you’ll also have a better idea of what is possible when you combine Google Analytics with Google Sheets and Data Studio. That way, you can modify this tutorial to work with your business goals.

The Tools Required to Automate the Process

In order to create the automated post-publish content review dashboard you need a few tools.

1. Google Analytics / Google Tag Manager
2. Google Sheets
3. Google Data Studio

Tracking Publish Date with Google Analytics & Tag Manager

First, you need to be tracking your website using Google Analytics.

One of the tricky pieces to this puzzle is the reports will be based off of the article publish date. Since we need to determine data based on the publish date we need to add this information into Google Analytics. To do this we’ll setup a custom dimension.

To setup a custom dimension I recommend using Google Tag Manager. If your Google Analytics tracking is already setup using Tag Manager than it makes perfect sense to use Tag Manager to implement the custom dimension as well. If not, this is a good time to consider moving your Google Analytics install to Tag Manager.

If you don’t want to (or can’t) use Google Tag Manager to implement the custom dimension than you will need a javascript developer to assist. The process we’ll walk through is specific to Tag Manager but a javascript developer can apply the process directly to your website.

Before we go into the step-by-step process I want to give credit to a few helpful websites.

When I started this process I referenced the guide found on UpBuild. It’s a great guide but the first step is to use the class name on the date field. Since my date field didn’t already have a class or ID, I decided to use the meta information.

To add custom javascript to track meta information I found a tutorial on Lynuhs. This showed how to capture author information from a meta tag. I was able to make a few modifications to capture the date and time information instead.

Now, we’re ready to add our custom dimension.

Step 1: Find the tag you want to track.

1. Right click on an article on your website and click view page source.
2. On the source code page, press ctrl+F to search for the word time in the code.
3. If you have a WordPress blog, you should see the following two meta tags with a date.
   <meta property="article:published_time" content="DATE HERE">
<meta property="article:modified_time" content="DATE HERE">
   (If you don’t use WordPress the meta tags may look different than above.)

Step 2: Create a variable in Tag Manager.

1. In Tag Manager navigate to Variables and click New under User-Defined Variables.
2. Click the Variable Configuration box to edit and select Custom JavaScript.
3. To track the most recent update date with WordPress use the following JavaScript:

   function (){
   var metaName = "article:modified_time";
   var metas = document.getElementsByTagName('meta');
   var content = undefined;
   
   for (i = 0; i < metas.length; i++){
   if (metas[i].getAttribute("property") == metaName){
   
   content = metas[i].getAttribute("content");
   }
   }
   return content;
   }
   

   (If you want to track the original published date instead of the updated date than replace modified_time with published_time.)
4. Press Save and name your variable Post Date.

Step 3: Create a Custom Dimension

1. In Google Analytics go to the Admin section (click the gear icon in lower left).
2. Under Property click on Custom Definitions > Custom Dimensions.
3. Click New Custom Dimension.
4. Configure your custom dimension:
   1. Name your dimension “Post Publish Date”.
   2. Keep the scope set to Hit.
   3. Make sure the active checkbox is checked.
5. Click Create.

Once you’re back on the custom dimension summary page take a look at your new dimension. Look at the Index column and make a note of what index number corresponds with your new custom dimension.

Step 4: Add Dimension to Google Analytics Tracking Tag

1. In Tag Manager go to Variables.
2. Select your Google Analytics settings variable under the User-Defined Variables. The name will vary depending on how it was named when first configured.
3. Click in the Variable Configuration section to edit it.
4. Under More Settings > Custom Dimensions enter in the new dimension you created.
   1. Under Index enter the number found in the Index column in Google Analytics.
   2. Under Dimension Value click the icon to the right of the field to select the Post Date variable you created in Step 2.
5. Click Save.

Step 5: Preview and Publish Your Changes

1. In Tag Manager, click Preview.
2. Navigate to an article on your website.
3. In the Preview pane at the bottom click 2 DOM Ready on the left.
4. Then, click Variables at the top.
5. Scroll down until you see the Post Date variable. You should see the article date to the right.
6. If everything looks good with the preview, go back to Tag Manager and click Publish!

Now you have a custom dimension set up that tracks your article publish date. Next, we’ll pull all of our data into Google Sheets. Once we do that we’ll go over how to make it pretty with Google Data Studio.

Pulling Google Analytics Data into Google Sheets

To pull Google Analytics Data into Google Sheets you’ll need the Google Sheets Add-On. This add-on allows you to connect your Google Analytics profile and pull your data directly into a Google Sheets spreadsheet.

First, we’ll discuss how to setup the spreadsheet at a high-level. Then, we’ll provide a link to the template and directions to get started with the template.

Pull Your Article Content to Analyze

The first report you’ll grab from your Analytics data contains a list of your content. I choose to do a timeframe of the last year. So, the report looks at 365 days ago until today.

I pulled a metric of pageviews and the dimensions of page path and our custom dimension. What that means, is the report will list out the URLs (page path) and the publish date (our custom dimension) and display the number of pageviews (the metric).

When you run this report it provides a big list. And when you review it you may notice a few issues. You likely have duplicate URLs. If you have various URL parameters appended to your URLs (such as ?fbclid=abcdefg) to track campaigns you’ll see all of those variations.

Also, you’ll notice we have the date but it’s difficult to read and includes a time.

We have the data, but it needs to be cleaned up.

Create a Worksheet to Clean Up Your Data

Have no fear, we can clean this up. We don’t want to override the data that gets pulled automatically from Google Analytics so we’ll create a new sheet.

We can use REGEXREPLACE to remove any URL parameters so we’re left with the part we care about. Here is an example of the formula:

=REGEXREPLACE(Content!A16,"(.*)\?.*","$1")

This basically says look at all content in the specified cell until you see a question mark. Then only display the first content that was found prior to that question mark. Since URL parameters begin with a question mark this was what we choose to clean up the URLs.

We’ll use a similar method to clean up our dates.

Instead of a question mark, all of the information in the date that we want to remove comes after a T. So we’ll change the question mark in our formula to a T:

=REGEXREPLACE(Content!B16,"(.*)T.*","$1")

We’ll also copy the pageviews to our new sheet so all of the information is together.

Now our data is clean, but we have a lot of duplication. Time to consolidate our data.

Consolidate the Clean Data

To consolidate this data we’ll create another sheet. On this sheet we’ll add a pivot chart. For the rows in the pivot table use the Page and Post Date dimensions from the previous worksheet. For the value, we’ll use the sum of pageviews.

Make sure your Post Date is formatted as YYYY-MM-DD. This will be important for Google Data Studio and ensures that Google Sheets sees it as a date field.

On your pivot table sheet we’ll want to add three columns. We want to calculate the date for 30 days, 60 days, and 90 days after publish. If your Post Date is formatted as a date calculating the other dates is as easy as adding 30, 60, and 90 respectively.

Add Reports for Top Content

Now that we have our list of top content we can add several more reports. Go back to the main Report Configuration tab.

We want to pull several more reports from Google Analytics. For each article you want to track we want to pull a report for 30 day, 60 day, 90 day and all time performance. So, if we’re tracking the top five content pieces we add 20 reports.

For each of these reports we’ll add dynamic information based on our pivot table. That way, as content is updated and changing we don’t have to update our report settings.

The start date will be the article published date. The end date will be either the 30 day, 60 day or 90 day date. Or, for all time data it will be set as today.

We’ll want to pull the pageviews metric for all of the reports. And for dimensions we will want both the URL (page path) and the date. Without the date we’ll just get a total number and we won’t be able to add a graph to visualize the trend in Data Studio.

We will also want to add a filter to these reports. We want to filter the data to only look at the one page we are concerned about. To do this we can add ga:pagePath=@[your content url] in the filter cell. The =@ operator means it needs to contain the following.

Run and Schedule the Report

One you have everything set up you can run the report. To keep the data updated you’ll want to schedule the report to run on a daily or weekly basis. These settings can be found under Add-ons > Google Analytics.

Using the Google Sheets Post-Publish Content Review Template

Step 1: Download the Template and Add-on

You will need the following two items:

• Google Sheets Post-Publish Content Review Template
• Google Analytics Add-on for Google Sheets

Step 2: Configure Report

1. To make edits go to File > Make a Copy.
2. Name the copy and click OK.
3. To get your View ID we’ll create a new report. Go to Add-ons > Google Analytics > Create new report.
4. Name your report anything. Then, make sure you select the Google Analytics view you want to grab data from. You can leave the other fields blank and click Create Report at the bottom.
5. The new report will be added to the last column (column W). Go to it and copy the View ID.
6. Copy the View ID number into every report in the View ID row (columns B-V).
7. Delete the new report you added (column W).

Step 3: Run Reports

1. Before you run the reports, verify the dimension index is correct. Under the dimensions in the first report (cell B7 on the Report Configuration tab) you’ll see we’ve specified dimension1. Change the dimension number to match the index number for your custom dimension in Google Analytics. So, if your custom dimension is in index 3 change ga:dimension1 to ga:dimension3.
2. To run your report go to Add-ons > Google Analytics > Run report.
3. The first time you run it only one report will run successfully (the Content report). This is because the other reports rely on data collected in the first report.
   1. If this is the first time you’ve ran the report click OK on the error message.
   2. Then, navigate to the Data sheet (tab named Data at the bottom).
   3. Wait for the data to populate in the pivot table then navigate back to the main Report Configuration tab.
4. Run the report again by going to Add-ons > Google Analytics > Run reports.
5. All of the reports should have completed successfully. Click OK to close the dialog box.

Step 4: Schedule the Reports

To make the process automated we will schedule these reports to run. That way you don’t have to come back to the spreadsheet and manually run the reports any time you want to review data.

1. To schedule the reports to run go to Add-ons > Google Analytics > Schedule reports.
2. Check the checkbox next to Enable reports to run automatically.
3. Select the timeframe that matches the frequency you need.
4. Click Save.

Visualizing the Google Sheets Data with Google Data Studio

Once you have the data you want to make it easy to look at. Data Studio is the perfect solution. You can integrate your Google Sheets document and with a few clicks make the data beautiful.

You’ll want to create a new dashboard in Data Studio. Once you do that you’ll need to import your spreadsheets.

Import Your Data

You don’t need to add every sheet from your document. Instead, only import sheets with data you want to add to a chart. I consolidated all of the data I needed into one sheet (the sheet named Data) so that is the only one I’ll import.

When you import your data into Data Studio make sure to verify the field type is correct. You want to make sure any date fields are set to Date and not Number or Text.

Add Your Charts

Once your data is imported you can start adding your charts. You’ll see the Add a chart dropdown at the top. Choose the chart you want to use to visualize your data. If you aren’t sure, play around with the different options.

I choose to use a Table chart at the top with the data from my pivot table. Then I combined Scorecards with Time Series charts to display the data from the top content.

Using the Google Data Studio Top Content Performance Template

1. Download the Top Content Performance Template for Data Studio.
2. Click Use Template in the top right.
3. Click the Select a datasource dropdown and scroll down to the bottom and click Create New Data Source.
4. Scroll down to Google Sheets and click Select.
5. Select your Content Summary spreadsheet.
6. Under Worksheet, scroll down and select Data.
7. Keep Use first rows as headers checked.
8. Click the Connect button in the top right.
9. Change the Post Date field to a YYYYMMDD date format.
10. Review field format for each field. Everything other than Post Date should populate properly.
11. Click the Add to report button in the top right.
12. Now you should be back at the template preview screen. Click Use Template again.
13. You can now select your spreadsheet in the Select a datasource dropdown. Select it and click Copy Report.

That’s it! Now you have your dashboard to track 30, 60, 90 day content performance.

A Few Notes

Data Only Pulls Items with the Custom Dimension

If you’ve just set up your custom dimension to track published data your data may not look accurate. This system only pulls content if it finds our custom dimension associated with it. So, you may have to wait and gather data before you see the data you want to review.

Data May Look Incomplete If You Update Often

I update my content all the time because every time I post a new article I add internal links wherever it is relevant and useful. If you update often you may find not very many top content articles have data for 60 and 90 day periods.

For my data, I’m okay with that. If that doesn’t make sense with your goals you may want to change the custom dimension to track the original published date instead of the updated date.

How did this system work for you? Did you make any modifications? Let us know in the comments!