Why Include Dynamic Links in Dashboards
When I create dashboards, I like to add as many resources as possible for stakeholders who want more information. And it’s important to make your dashboards as self-sustaining as possible. If you have to be there to walk viewers through your dashboard, you’re going to run into issues because once you loosen your grip on it and send it out into the wild, there’s no telling whose desk it’s going to show up on — or in what for s/he will view it.
For internal dashboards, a line item may warrant additional research. To wit, someone might see a report from a tool like SEMRush (one of my favorite marketing tools) or Google Search Console and want to research it further. To facilitate this research (or even discussion), you can create dynamic links to individual reports.
Here are just a few ways you can use these links:
- Link to a specific Google Analytics report — e.g., landing page, referral, channel, PPC keyword, etc.
- Link to the Queries report in Google Search Console** (GSC) to get data on a particular keyword — under Search Traffic > Search Analytics
- Link to the Pages report in GSC — also in the Search Analytics report — to see the keywords generating impressions and clicks for each URL in the report
- Link to the Internal Links report in GSC to investigate internal linking patterns
- Link to Google’s Mobile-Friendly Test for a report with a list of pages from your site to spot check pages to see if they’re mobile friendly
- Link to SEMRush reports — e.g., keywords, landing pages domains — to get competitive data
- Link to Buzzsumo reports to research popular articles for particular topics, pages on a domain, etc. (Tip: Use data validation to create a drop-down menu that allows dashboard users to choose recency (e.g., past 24 hours, week, month, six months, year) to save yourself a step when you click through to Buzzsumo.)
- Link to Newswhip to see trending topics
You get the idea. Many marketing tools use unique URLs for their individual reports, and any that do can be included in your dashboards to provide easy links to learn more.
** FKA Google Webmaster Tools
Examples from the Video Tutorial
I start the video off with a simpler example using keyword reports in SEMRush.
Using a helper column, you’ll learn how to dynamically create a link to the keyword report for each keyword listed that will ultimately look like this: http://www.semrush.com/info/funky+shoes.
Things get a little more intense with this example because, unlike the SEMRush example that only has one variable element (the keyword), the Google Analytics reports we will dynamically create links to will have three variable elements:
- Landing page
- Start date
- End date
Also, Google Sheets has a handy feature for creating date pickers that makes this process a breeze for your dashboard users. I show you how to use the TEXT function to convert these dates to the format used in the Google Analytics report URLs.
You can get a date picker in Excel, but it’s much more complicated (thanks, Obama). In Excel 2013 for PC users you can use a form control (learn more), or for 2011 Mac users you can use an add-in (learn more).
Brace yourselves. This example requires multiple steps. But I walk you through it step by step.
By the time you get through this video you’ll have an understanding of how to rock the following functions to create dynamic links:
If you want to follow along, you can use the practice file I use in the video. If you want to use it for yourself, create a copy by logging in to Google and going to File > Make a Copy.
You can view the video below. I’ll warn you: This video is longer than most of mine because I wanted to demonstrate a more complicated example. But if you understand these two examples, you should be able to dynamically generate links to any marketing tool that uses a custom URL to generate the report.
For more beastly data visualization tips, check out out my Annielytics Dashboard Course offerings.