Measure Sessions & E-Commerce Data From Search Engines With Excel (Reporting)

Let’s assume that you need to prepare a basic excel spreadsheet that you need to report on your client in which you will report the sessions, including transactional data (transactions, revenue, ecommerce conversion rate). Sure, you could report it via a Custom Report from Analytics but you are kind of limited in what you report and you cannot the depth of data that you could do using a spreadsheet and a couple of functions/formulas.

This sample report, easily calculates for you the overall number of sessions, revenue, transactions and ecommerce conversion rate from each traffic source and you can know how all these KPIs perform per easy source.

All you need to do is to copy/paste the data from analytics and the spreadsheet will do the rest, either you copy/past 20 landing pages or 20,000.

I created an easy example of sports shoes (landing pages) and provided random figures using RANDBETWEEN for the sake of our example.

Continue reading

How To Create SEO-Friendly Facets With Spreadsheets

There are a lot of ecommerce sites and we are talking about mega sites, that use filters or facets in the URL structure. The users do not pay attention – and he doesn’t have to do it- on the URL structure, on the page titles, meta descriptions nor h1s, because they do not have to.

However SEOs have to do pay attention to onsite elements like page titles,h1s or URL structure as they have an impact in terms of onsite SEO.

Therefore the use of facets play an important role in terms of onsite SEO as it can allow an ecommerce siste to rank for a greater range of keywords. In a nutshell, you don’t need to rank just for the term ‘formal shirts’, or ‘slim fit shirt’, but you can rank for a combination of terms such as, ‘black formal shirts’. In this way, you can rank for longer tail keywords, which obviously don’t generate a huge amount of search volume, but these longer tail keywords, will reach users who perform more targeted queries and therefore they are more likely to convert as they know what they are looking for.

However this task may seem quite daunting as ecommerce sites have thousands of URLs and it can be quite time consuming.

Continue reading

How To Use Google Tag Manager In E-Commerce Sites

If you want to track events, without your developer’s involvement, then the implementation of events via Google Tag Manager is the best solution. It will allow you to track the untrackable within your site, without having to involve any developer, saving you time and money.

Those of you who read my posts, I had written a post where you can generate events through a JavaScript snippet on Excel.

With Tag Manager no hardcoded JavaScript events are required. Google Tag Manager can simplify the process. However there’s a big problem when it comes to e-commerce sites. How can you track events that matter to an e-commerce is you don’t know how to track the elements that you want to track?

This post deals with a favourite part of mine and that is Google Tag Manager of course.

Continue reading

How To Quantify Link Earning

After a brief time of silence in the site, it’s time to start again with something relatively easy. The purpose of this task is to show in a very easy way to quantify and demonstrate the impact of link earning – as part of your SEO strategy- in terms of traffic and revenue.

Most companies need to know what the impact of link acquisition is and they need to know if what they get back in return is worthy of the budget they spend.

This spreadsheet is really basic, but it gives you an idea of what a spreadsheet can do. Namely you can get data from different sources like Moz, Majestic, Ahrefs, Google Anaytics etc and match them all on a spreadsheet.

Continue reading

Advanced Keyword Analysis with Excel

My previous post was all about keyword analysis using a basic template. This one allows to analyse more data.

I have covered most the below from my previous post, but since repetition is the mother of knowledge, I am repeating the same information.

That data can be 50 or 50,000 keywords. However, let’s cut to the chase and analyse the columns:

In the Mastersheet, you will find everything you need to segment, analyse and interpret data.

Continue reading

Keyword Analysis with Excel

Who said that data analysis  on spreadsheet cannot be science and art at the same? Now make  data (keyword) analysis like a boss.

We often have situations where need to simply analyse the performance of your SEO project by comparing two different date ranges.

It can often be a time-consuming process because it can be possible that you need to analyse 50,000 keywords for a big brand that you handle, which make the situation even harder. The proper use of Excel can allow you to speed up the process of analysing all these keywords and actually spend on the actual analysis, rather than having to spend it on how to create this spreadhsheet.

Continue reading

How To Categorise URLs with Excel – TM Lewin case study

Let’s assume that you have a medium size website, or a larger website (e.g TM Lewin).

How  would like to categorise the performance of your individual parent Categories from your website, in a very easy way?

For this reason, I have prepared a very basic template that will allow you to do so. Namely to review the performance of your parent Categories through the Landing Pages section on Google Analytics.

Continue reading

Canonical, Hreflang, 301, Event Tracking, UTM Generator in Excel

How would you like to automate the whole process of canonicals, events, etc into one spreadsheet?

Since I received quite few mails, I have merged all these spreadsheets into one spreadsheet in 5 tabs so that work easier; in this spreadsheet you will find the:

  • Rel Canonical Generator
  • Hreflang Generator
  • 301 Redirection Generator
  • Event Tracking Generator
  • UTM Generator

Continue reading

Create UTM Parameters with Excel

UTM parameters are tags that allow to track effectively your marketing campaigns (e.g mail marketing, social media, link building campaigns). When these links are clicked, these tags are sent to Google Analytics allowing to track the effectiveness of your marketing campaigns, based on the data that you have collected.

Continue reading

Event Tracking Generator in Excel for Universal Analytics

We have all been there where we need to create event tracking codes in Universal Analytics and we had to spent countless hours until we find a credible web-based generator and still we have to wait for hours until all necessary event tracking codes are generated. Now with this spreadsheet the event tracking code is simply spitted out on column F!

But first things first… let’s start from the basics….the event tracking codes allow us to track specific elements within a site; these elements could be links, buttons. By tracking these elements, if they under-perform, we can make improvements through CRO or we can use the tracking of those elements for remarketing purposes in order to target a specific segments of your visitors.

Continue reading