Following on of my series of recreating Google Analytics reports in Big Query, this time we are looking at the Landing page report.

Previously we have looked at:

• Audience overview

• Channel Acquisition

Landing pages follows a lot of the same rules of channel acquisition but with one major difference. There is no landing page dimension in BigQuery. So helpful, right! To get the landing page you need to get the first page in the session, that would be the one with the smallest hit number. Now, you can’t assume that it will be the first hit, as sometimes there are non-interactive events firing before the page view. This is why you need to rank all the pages in a session and get the first one, as the query below shows.

This line here:

RANK() OVER (PARTITION BY fullvisitorid, visitid, date ORDER BY hits.hitNumber) AS ranking`

will rank each hit from 1 to x based on the hit number, and by adding:

and hits.type = “PAGE”

we limit the ranking to pages only.

Now we have the first page in the session, we can get the rest of the metrics, just like channel groupings.

Any requests on what to do next or questions, feel free to comment!