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.
In column B you find the random organic sources. Obviously when you handle real data on Analytics, nothing is going to be random.
In columns C and C I have provided these random figures:
Column F (ecommerce conversion rate) is a result of transactions and sessions. In simple terms, that’s how ecommerce conversion rate is calculated:
Ecommerce Conversion Rate = Transactions / Sessions *100
That’s how it was calculated in this case, with this simple formula:
Revenue data are also based on random figures.
Column G is not necessary, as you can create the last table (in which you will calculate the traffic and transactional data from each traffic source) based on column B. However I created this column so that you see how IF(ISNUMBER(SEARCH)) work and potentially you can definitely use it in other many other occasions.
That’s a list of various other search engines in which you will find the ones that can be found in our sample table, as you can see below:
Now the table that provides the final data:
And the final table which breaks down the sessions, revenue, transactions and e-commerce conversion rate per traffic source:
In order to calculate all the above (Sessions, transactions and revenue) is done with the use of SUMIFs:
All you need to is to do is to change of the ‘criteria’ within each function based on the search engine you want to calculate each time. Also if you go to transactions and revenue make sure that you adjust the ‘range’ (namely the columns).
In order to calculate the final ecommerce conversion rate don’t forget the above rule that was given:
In case no figures comes up and if you want to avoid the non-friendly DIV just replace it with a hyphen or a zero.
That’s it. You can just download the spreadsheet (.xls).
Until next time, happy analysing and keep on exceling.