SEO boost: Analyzing My Website's Google Search Performance Report (April 2023)

SQL, Excel (analysis), Excel (visualization)

Apr 27, 2023

Want to practice? The dataset is available for you here!1 You too can analyze it as I did, or in a different way, if you wish.

The data analysis was done, mostly, using SQL, and a bit with Excel. The visualizations were done with Excel.

let's connect! ping me on LinkedIn

Side note: Rumor has it that positions such as user acquisition manager, media buyer, and marketing analysts are relatively easy entry level positions for folks interested in being data analysts. I wasn’t one, but to know the basics of SEO, or Search Engine Optimization, is generally nice to know if you are active online.

I believe it was around early April 2023 when Google emailed me a summary of my website's, s-rabi.com,2 Search Performance.3 This seemed like a nice opportunity to analyze some interesting data and take action accordingly to improve my website / website ranking on google. So I did it :)

After analyzing and discovering some insights, I took action and updated my website in order to rank better on Google and gain more traffic.

It’s not that my personal website has suddenly become an internet sensation, but there was certainly an improvement in terms of number of impressions, total clicks, average click-through-rate, and even average page-ranking position.

time span: 6 months, late Feb to mid Aug 2023

Terminology

Clicks is how many times a user clicked through into the website.
Impressions is how many times a user saw a link to the website in the search results.
CTR (Click-Through Rate) is the percentage of impressions that resulted in a click ([clicks/impressions]*100).
Position is the average position of the website in the search results, based on its highest position whenever it appeared in a search.

Getting To Know The Data

I downloaded the web report for the maximum time: 24 Dec. 2021-24 Apr. 2023.

It was immediately clear that my first Medivia4 life-thread was the primary source of traffic.

| Clicks | Top Pages

| 478 | medivia-journey

| 8 | home page

| 3 | search/label/gaming

| 1 | Tutorial-How-to-Host-Free-WordPress-Website-Google-Cloud-Platform

| 1 | medivia-journey-lera-levels-201-300

| 1 | New-Old-Libya-R9X-Strike-Case-Revealed-US-Ramps-Up-Ninja-Bomb-Targeted-Killings

| 1 | welcome (about me page)

People from 42 different countries visited my website. The majority of them were from Brazil and Poland.

I scoped in on the data for an overview of the performance trend. The best scenario would have been to see a trend in which the Positioning trend and the CTR move towards one another.

Position improvement = decreasing numbers, website appearing higher in the search results; and
CTR improvement = increasing numbers, more clicks per impression).

But this was not the case, as can be seen in the figure below:

figure B: Quarterly CTR & Position Trend

However, the data overall was not too bad as the website generally got more and more clicks.

This made me think that I needed to improve the SEO of the website because people were interested in its content. Yet there was a mismatch between their search words and the keywords used in my posts.

Therefore, I proceeded to analyzing the Google users search queries made by the people who visited my website.

Preparing The Data For Analysis

I downloaded the data as a CSV. Available here for you too <click here>.

CSV Stage

I first used the =LEN(...) to identify the strings length per query.

I then used the =MAX(...) to identify the maximum length.

That was used to determine the length of the variable named TopQueries.

In this case, VARCHAR 55 since the lengthiest was 54.

Afterwards, I eliminated the percentage format for the CTR variable (%) and changed it to General. I then defined accordingly the CTR variable as DECIMAL(5,4).

Microsoft SQL Server Management Studio Table Creation

IF OBJECT_ID('Queries', 'U') IS NOT NULL
DROP TABLE Queries
------
------
CREATE TABLE Queries (
  TopQueries VARCHAR(100) NOT NULL,
  Clicks INT,
  Impressions INT,
  CTR DECIMAL(5,4),
  Position DECIMAL(5,2)
);
-----
-----
BULK INSERT Queries
FROM 'C:\Users\userName\Desktop\Practice set\GoogleSearchReport\Queries.csv'
WITH (
FORMAT='CSV',
FIRSTROW=2
);
------

SQL Data Analysis: Top Search Queries

In short, the idea was to identify the top-performing keywords to do search engine optimization (updating posts titles, metadata, etc.) by finding single keywords which were top-performing and to then find duo/bigram5 keywords which were top-performing as well.

Single Keywords Analysis

SQL Code

--Temporary table creation: saving single keywords and their metrics.
CREATE TABLE #SingleKeywordAnalysis (
    Keyword NVARCHAR(55),
    Clicks INT,
    Impressions INT,
    CTR DECIMAL(18, 4),
    Position DECIMAL(18, 4)
);

--Insert single keywords and their metrics into the temporary table.
WITH QueryKeywords AS (
    SELECT
        LTRIM(RTRIM(m.value)) AS Keyword
        , Clicks
        , Impressions
        , CTR
        , Position
    FROM Queries
    CROSS APPLY STRING_SPLIT(TopQueries, ' ') AS m
)

INSERT INTO #SingleKeywordAnalysis (Keyword, Clicks, Impressions, CTR, Position)
SELECT
    Keyword,
    SUM(Clicks) AS Clicks,
    SUM(Impressions) AS Impressions,
    SUM(Clicks) / NULLIF(SUM(Impressions), 0) AS CTR,
    AVG(Position) AS Position
FROM QueryKeywords
GROUP BY Keyword
;

--Select single keywords and the metrics, sorted by descending desired metric
SELECT
    Keyword
    , Clicks
    , Impressions
    , CTR
    , Position
FROM #SingleKeywordAnalysis
ORDER BY Clicks DESC, Impressions DESC, CTR DESC
;

--Drop the temporary #SingleKeywordAnalysis table
DROP TABLE #SingleKeywordAnalysis
;

Output

There were 326 different keywords. The below image contains selected part.

I have quickly went over the keywords in order to identify if there were any weird ones which I should avoid in advance in including them in the next part of my analysis. I found a number of them and made sure to exclude them for the next part.

Bigram Keywords Analysis

SQL Code

--Temporary table creation: saving bigrams and their metrics.
CREATE TABLE #BigramAnalysis (
    Bigram NVARCHAR(55),
    Clicks INT,
    Impressions INT,
    CTR DECIMAL(18, 4),
    Position DECIMAL(18, 4)
);

--Define ignore list (table with words to ignore).
DECLARE @IgnoredWords TABLE (Word NVARCHAR(55))
;

INSERT INTO @IgnoredWords (Word)
VALUES ('of'), ('on'), ('for'), ('at'), ('the'), 
('to'), ('by'), ('+ñ+É+Æ+£'), ('a'), ('+£+¬+¬'), 
('+¿+¢+û'), ('in'), ('+º+ñ+í+ò+£+ò+¬'), ('+á+ò+û+£+Ö'), ('+á+ù+£+ö')
;

--Generate bigrams and their metrics.
WITH QueryKeywords AS (
    SELECT
        LTRIM(RTRIM(m.value)) AS Keyword
        , Clicks
        , Impressions
        , CTR
        , Position
    FROM Queries
    CROSS APPLY STRING_SPLIT(TopQueries, ' ') AS m
    WHERE NOT EXISTS (SELECT 1 
                      FROM @IgnoredWords 
                      WHERE Word = LTRIM(RTRIM(m.value))
                      )
),
Bigrams AS (
  SELECT
  LAG(Keyword, 1) OVER (PARTITION BY Clicks, Impressions, CTR, Position       ORDER BY Id) AS FirstWord
 , Keyword AS SecondWord
 , Clicks
 , Impressions
 , CTR
 , Position
 , ROW_NUMBER() OVER (PARTITION BY Clicks, Impressions, CTR, Position ORDER BY Id) AS Id
 FROM (SELECT Keyword
       , Clicks
       , Impressions
       , CTR
       , Position
       , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Id 
       FROM QueryKeywords) AS t
)

INSERT INTO #BigramAnalysis (Bigram, Clicks, Impressions, CTR, Position)
SELECT
    FirstWord + ' ' + SecondWord AS Bigram,
    SUM(Clicks) AS Clicks,
    SUM(Impressions) AS Impressions,
    SUM(Clicks) / NULLIF(SUM(Impressions), 0) AS CTR,
    AVG(Position) AS Position
FROM Bigrams
WHERE FirstWord IS NOT NULL
GROUP BY FirstWord, SecondWord
;

--Select bigrams and their metrics, sorted by descending desired metric.
SELECT
    Bigram
    , Clicks
    , Impressions
    , CTR
    , Position
FROM #BigramAnalysis
ORDER BY Clicks DESC 
     , Impressions DESC
     , CTR DESC
;

--Drop #BigramAnalysis temporary table.
DROP TABLE #BigramAnalysis;

Output

Combined Analysis: Last Analysis Step

I cut down the scope of the next part of the examination to single and duo- keywords with at least 250 Impressions.

I reviewed the results in Excel, manually, in order to gain a deeper understanding compared to the depth of understanding I anticipated I'd obtain by further SQL query analysis, such as union all for the two truncated tables and counting the frequency that the single keywords appeared within the bigrams. Et cetera.

Additionally, I removed the non-Medivia related keywords because Medivia proved to be where effort needed to be invested for the most impact on traffic.

Findings:

I was able to improve the SEO of my already existing post by adjusting the title, metadata and initial paragraphs of the post to include words such as Medivia Archer Guide, leveling guide, hunting places, faction progress, mining guide, et cetera.
It was clear I could use screenshots I saved from the progress of two other sub characters I had on Lera's account, a Mage6 and a Druid, to attract additional traffic given the top performing keywords.
I would be able to leverage the gained knowledge here in advance as I'd create my Knight Iron Man project life thread.7 This was a request I received two weeks ago anyway to create such a life thread.

Google email me once a while some funny achievements, statistics and other notes. The updates slowly but surely resulted with improvements to gaining organic traffic. About a month or so after taking actions, I got this, for example:

Ping me on LinkedIn

https://drive.google.com/file/d/18xA8thwUnXKzPYLkFWJGcBxQifypYdss/view?usp=sharing

www.s-rabi.com

https://search.google.com/search-console

www.medivia.online

https://en.wikipedia.org/wiki/Bigram

https://www.s-rabi.com/2023/03/medivia-mage-guide-sorc-iron-man-project.html

https://www.s-rabi.com/2023/04/medivia-knight-guide-iron-man-project-1.html

https://www.s-rabi.com/2023/06/medivia-knight-guide-iron-man-project.html