Search Engine Optimization (SEO) or the process of improving a website in order to increase its visibility is a complex one. There are several tools available in the market today focusing specifically on this. However, these tools can be prohibitively expensive, especially when each new add-on feature increases the costs.
Organic traffic is one of the most sought-after types of traffic. It indicates that people are searching for solutions to their problem, and your product shows up as a solution leading to more business leads, and revenue.
The premise sounds simple right?
Google connects the two sides of the problem. People with questions can reach sites with answers.
But to ensure that your site appears first when your potential visitors are searching requires monitoring and improving various metrics.
An automated SEO dashboard is able to check the search results ranks, and provide information about how well your website is placed in them.
Once you have an SEO Dashboard, you will know which keywords are well ranking, and be able to get a better idea about what kind of content is working and what isn’t. You can update pages that aren’t performing well, you can build new links and see how they affect your ranking.
With this in mind, our goal today is to create an SEO Dashboard that can help us improve our organic reach. We will also learn some interesting concepts about using Appsmith, its widgets, API connections, and more.
Let’s go!
Clean UI for Clean Insights
Here’s a look at the SEO Dashboard we will build.
As you can see, for a good SEO reporting dashboard, the main KPIs should be clear.
Right at the top, we can see our main key performance indicators (KPIs). They give us a clear vision of our overall status, what has been improving, and what isn’t.
Of course, the indicators used in our SEO Dashboard are just a suggestion. You can create your own metrics based on what you need to track.
Overview of your SEO Performance
These lists show us performance for many keywords at a glance. These components allow for searching, filtering, pagination.
In addition, the dropdown component modifies the data table. This allows you to see not just the current period but past months as well.
This SEO dashboard includes a component of keyword-specific analysis. This allows you to select which keyword you want to see, and then analyze its progression chart.
The final touch in our SEO reporting dashboard is the aggregate rankings chart. This gives you an understanding of how many well-ranking keywords you have.
Now let’s start building our dashboard!
If you’re curious to see the app in action, take a look here:
A good SEO Dashboard starts with pen and paper
It might seem odd. But good reporting starts with good planning.
If you plan ahead, you know exactly which data points you need to collect, what kind of transformations you need to do, and how your data is presented.
But you don’t need to design a full mockup using pen and paper. Once you know what kind of information you want, it’s possible to use Appsmith itself for a mockup. Follow
In this example, we created a page and just used the drag and drop tool to add our desired components to a page. We can add our data later.
At this point we just need to get a feel of what the SEO Dashboard looks like and if it has all the information we need.
Planning your KPIs and metrics
In the end, our main KPIs are:
- #1 Keywords – How many keywords we have in the 1st place of search results
- Backlinks.- How many external links lead to our site
- Keywords in top 10 – How many keywords are in 1-10 places of search results
- Organic visits – how many visits came from our SEO efforts
In addition, we want to know:
- Rank position for each keyword on a specific date
- Visits coming from a specific keyword on each date
These two points allow us to build the tables and charts.
You can add other metrics as well. This will depend on what kind of reporting you need in your own SEO dashboard. Here are some examples of interesting metrics that you could use.
Here are some examples of interesting metrics that you could use:
- Bounce rate
- Conversion rate coming from organic results
- ROI
- Time spent per visit
- Word count vs Keyword Ranking
- Exit pages
- Last Updated Date vs Keyword Ranking
Just keep in mind that each of these requires more data. If you have them all already, then it’s just a matter of loading it all into Appsmith.
Google Sheets: Our Data Source
Appsmith allows you to connect different data sources, databases and even perform cURL imports.
However, data source of choice today is Google Sheets. Yep, no DB, no fancy API. Just good old Google Sheets.
Of course, there’s no right or wrong, just what works for you. And in SEO Dashboards, having a flexible data source is immensely helpful.
Google sheets allows you to connect with external tools such as Google Analytics, store management, and paid traffic campaigns. And then you can manipulate this data using its spreadsheets functions, giving you the exact data in the exact format you need.
This saves a lot of time and effort in data processing. Instead of coding a lot of JS functions, you just use a simple formula.
We will learn how to use JS code here and there as well, but Google Sheets gives you the option of doing it all in separate spreadsheets.
The key to a clean Google Sheets import is to make use of its lookup functions, formulas, and spreadsheets. This allows you to build tables that work in the ways we need them to on our widgets.
This way, you do the data entry once, and then just manipulate it in the ways you need to.
You can follow along in our
In our example, we have three main tables that are our data sources:
- KW List – This table contains a simple list with all our monitored keywords
- Raw KW Rank – This table contains the search rank results for each of our keywords on each date along with the URL.
- Raw KW Visits – This table contains the visits for each keyword on each specific date
And with these three tables, we generate all other tables of our SEO Dashboard. This is an interesting no-code path if you just want to use Appsmith and don’t have much JS experience.
If you want to build along with this tutorial, you can create a copy of our sheet and play around with it. You can store and transform data from one sheet to another. And, you can see how each sheet is tailor-made for a specific widget.
This makes it easy to load the relevant data.
- The KPIs sheet gives us all our KPIs as well as the text about them
- KW Rank Summary has our overall rankings that are used in the columns chart
- Backlinks can be used in a chart by itself but is used to create our backlinks KPI
- KW Rank transforms the Raw KW Rank, making it ready to be loaded as a list in Appsmith
- KW Visit does the same with the Raw KW Visit, transposing it, allowing easier readability as a list
Now we have our data and tables. But how can we update it?
Auto-updating Keyword Rankings
And here we are, the question of the century. How do I know the exact rank for each of my keywords?
The answer: Well, it’s tricky.
First of all, if you try to create a simple crawler and query google with your 100 desired keywords it might work. But it’s likely that it won’t work for long.
As a site gets bigger, unwanted crawling turns into a big problem. It's fine to have a couple of people visiting your site and maybe a bot or two loading pages en-masse. But as you grow, you might have thousands of bots loading thousands of pages at the same time, while your server is busy loading pages to real users and customers.
To avoid crashes, many of these sites will block crawlers except the ones that they really want (Google and Bing, for example). For you, this means that if you try crawling one of these sites, you will be blacklisted, blocked and left with no data.
Then, what’s the solution? There are many APIs out there to help you. The best one to get started is Google’s own search API.
It allows you to create your own search tool, which then can be used to search the entire web, just like Google does.
You can create your search tool on the
After this step, you will need to sign up for an
By far, the best and easiest way to load the API data into Google sheets is to use a custom
There’s also an option to do it using only the Google Sheets formulas, in case you want to give it a try. There’s an example of that in the “Crawler” sheet.
Even though Gsheets doesn’t have a JSON reading function, you can use the IMPORTDATA function to load it. The trick is, this data isn’t fully understood. We need to add helper columns to understand the current search position. Then we need to find where our desired search result is.
After that, you can use macros and recorded actions to loop through all your keywords. In addition, macros can be scheduled to run every day, for example. Thus your results are always updated.
You can use the same technique to load your conversions, backlinks, and sales. Anything else that you want to include in your dashboard can be added to Google Sheets to make it easier to load into Appsmith.
SEO Reporting Dashboard with Appsmith
Now that we have everything ready, let’s tie it all together in Appsmith. Our SEO Dashboard mockup was done in our planning phase, but we will show you how to create each of the components we have as well as the data connections.
Once you have your first page created, let’s load some content into it.
How to Connect GSheets and Appsmith
You can connect your Google sheets straight from the API connection under “Datasources”. Click the + icon and select Google Sheets:
You can read more about
Then, create one API call for each of our sheets. Here are the names we used:
- getKWRankList – Gets KW Rank
- getKWRankSummary – Gets KW Rank Summary
- getKeywords – Gets KW List
- getKWVisits – Gets KW Visit
- getKPIs – Gets KPIs
Adding our main widgets
In our dashboard, we used an image for our logo, and a container for all the other components.
Make sure you drag and drop them into your page.
There’s nothing special about their setup, we just add the logo URL, resize it and make sure that it is centered making it look nice.
Adding our SEO KPIs
Next, we add our KPIs. We will be able to display them using the stats box. You could use other components if you want to as well. But the stats box has the most appropriate design for displaying KPIs.
The stats box itself is a container, thus you need to select the internal components to edit them. We don’t need an image here, let’s delete it. Then select the first title and in the text section use.
{{getKPIs.data[0].Name}}
This should load the KPI name automatically:
In short, what we are saying to Appsmith is: “In this section, I want you to use the getKPIs API call, then load its data, select just the first row, and get the name of that row.”
The same applies to the KPI value and text we use {{getKPIs.data[0].Value}} and {{getKPIs.data[0].Txt}}. We are getting the first row, and the Value or Txt columns.
Now you can duplicate this widget and just replace [0] (first row) with 1, 2, 3. This makes you get the second, third and fourth rows. Therefore, we will have all KPIs loading dynamically.
Here is an example of the value of the third row:
How to create the period switcher for the data table
The data table itself is quite simple to implement. We just use our API call in the Table Data field, and it is auto-filled with our Sheets data.
But we want the ‘Rank’ column to load different values depending on the “Period” dropdown. The same goes for the visits table.
Now it’s time to create our first JS function.
The idea here is to create a reusable function since we might use the same dropdown for multiple components. Thus, we head over to the Explorer > JS Objects:
Then we create a new function inside of myObj, that allows us to get the current row name, depending on the period. If the period is “current,” we know that the KW Rank table should load the “Rank” column. But if it’s 30 days ago, then the KW Rank column name is Rank30d. The visits columns have different names, thus we need to check which kind of table we are looking at.
This allows us to add more columns as well. You can include a backlinks table, and add another type to the function. Here is the full code:
export default {
currentkwRow: ( type = null ) => {
var current = "none";
if ( type == "visit" ) {
// exception for visit type
switch( periodSelect.selectedOptionValue ) {
case '30d':
current = "Last30dVisits";
break;
case '60d':
current = "Last60dVisits";
break;
default:
current = "CurrentVisits";
}
} else {
//default behavior for rank variable
switch( periodSelect.selectedOptionValue ) {
case '30d':
current = "Rank30d";
break;
case '60d':
current = "Rank60d";
break;
default:
current = "Rank";
}
}
return current;
}
}
Here, it’s important to have the dropdown name and values correct.
This is our current dropdown:
Now we have a function that gets the dropdown value and gives us the right column name. Thus we can make the “Rank” column to be like this:
This is the value we use:
{{ currentRow[ myObj.currentkwRow() ] }}
In essence, what we are saying to Appsmith is “the value for this row is the myObj.currentRow() index of the currentrow Array”.
And the same applies to the Visits table, but the “Visits” column passes the “visit” type to our function, it outputs the column name for the visits table, not the rank table:
{{ currentRow[ myObj.currentkwRow('visit') ]}}
Creating an Inverse Line Chart in Appsmith
When it comes to rankings for an SEO dashboard, a line chart is not enough. You might have noticed that we use a line chart with negative values in our mockup:
This works fine and gives us the correct understanding of our data. Lower values are better here, then they are higher.
But the negative values can be a bit annoying. We can use a different chart type instead.
One of the beautiful things about Appsmith is that you can use any Fusion Chart you want. Just pass the right arguments based on chart type and the correct chart is built.
Here we can use the InverseMSLine chart. You can read more about it in the
Thus, when creating your chart, just pick the “Custom Chart” type, and pass your data. This is what we are using here:
{
"type": "InverseMSLine",
"dataSource": {
"chart": {
"showBorder": "0",
"xAxisName": "Dates",
"yAxisName": "Rank",
"theme": "fusion",
"lineColor": "#f8692b",
"yAxisMinValue": 1
},
"dataset": [
{
"data": {{ myObj.filterkw(); }}
}
],
"categories": [
{
"category": [
{
"label": "120d"
},
{
"label": "90d"
},
{
"label": "60d"
},
{
"label": "30d"
},
{
"label": "today"
}
]
}
]
}
}
Notice that we have a function in our dataset:
"data": {{ myObj.filterkw(); }}
That’s because we use a switcher here, just like we did for the data tables.
Our dropdown is just our KW list, with a small adjustment where the values have underscores instead of spaces. This is needed to make sure it works with JS code.
This is our code:
{{
getKeywords.data.filter( (item) => (item.value = item.label.replaceAll( ' ', '_') ) );
}}
In short, this filters through all values, but it returns all of them, while it creates a new index for item.value that is just the label with underscores replacing spaces.
Thus, our chart just needs to get the value from this dropdown and return the keyword ranks for that specific keyword from the getKWRankList.
Think of it as a chart version for a single line of the KW rank table.
This is the code for the filterkw method in our myObj Object:
filterkw: () => {
var newdata = [];
var compare;
getKWRankList.data.forEach( function(el, i){
compare = el.Keyword;
compare = compare.replaceAll( ' ', '_') ;
if ( compare == kwselect.selectedOptionValue ) {
newdata[0] = { value: el.Rank120d };
newdata[1] = { value: el.Rank90d };
newdata[2] = { value: el.Rank60d };
newdata[3] = { value: el.Rank30d };
newdata[4] = { value: el.Rank };
}
});
return newdata;
}
How to create a custom column chart
Our line chart has many interesting things going on that you might have missed one thing: it has a custom color.
This goes back to the fusion chart options we’ve mentioned. But let’s expand on this idea for the columns chart.
It’s possible to pass multiple colors for column charts, creating an interesting visual effect.
We use a custom chart again, but for the most part, we are interested in the chart setup now.
This is what we use:
{
"type": "column2d",
"dataSource": {
"chart": {
"xAxisName": "Rank Position",
"yAxisName": "No. of Keywords",
"palettecolors": "f8692b, f9814d, fa8f61, fb9d74, fbab88",
"theme": "fusion"
},
"data": {
{
getKWRankSummary.data;
}
}
}
}
The magic lies in the “palettecolors” variable. Each chart has its own setup for colors, thus it’s worth checking the fusion charts reference when you are building your own charts.
Today we went step by step, from planning to implementation of a complete SEO Dashboard. We explored many aspects of data structure, crawling, Appsmith widgets, custom charts, and custom styling.
I hope you enjoyed this tutorial, and I can’t wait to see your SEO reporting dashboards!
If you have any questions, feel free to reach me at [email protected] and I’d be glad to help you.
Thanks!