How to replicate the 'Acquisition | All traffic | Referrals' report

As a Google Analytics user you are probably quite attached to the default reports in the user interface of Universal Analytics. It can be hard to make sense of the data in the BigQuery export tables.

Let me enable you to replicate the reports you're familiar with. I'll try to keep it basic here.

Referrals report

In the Acquisition | All traffic | Referrals report you'll find data about acquisition, behaviour and conversion of your users, segmented by referral source and landing page.

Let's query!

select 
  trafficsource.source,
  -- (select page.pagepath from unnest(hits) where isentrance = true) as landing_page,
  count(distinct fullvisitorid) as users,
  count(distinct(case when totals.newvisits = 1 then fullvisitorid else null end)) as new_users,
  count(distinct concat(fullvisitorid, cast(visitstarttime as string))) as sessions,
  count(distinct case when totals.bounces = 1 then concat(fullvisitorid, cast(visitstarttime as string)) else null end ) / count(distinct concat(fullvisitorid, cast(visitstarttime as string))) as bounce_rate,
  sum(totals.pageviews) / count(distinct concat(fullvisitorid, cast(visitstarttime as string))) as pages_per_session,
  ifnull(sum(totals.timeonsite) / count(distinct concat(fullvisitorid, cast(visitstarttime as string))),0) as average_session_duration,
  ifnull(sum(totals.transactions),0) as transactions,
  ifnull(sum(totals.totaltransactionrevenue),0)/1000000 as revenue,
  ifnull(sum(totals.transactions) / count(distinct concat(fullvisitorid, cast(visitstarttime as string))),0) as ecommerce_conversion_rate
from
  `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`
where
  totals.visits = 1
  and trafficsource.medium = 'referral'
group by
  trafficsource.source
  -- ,landing_page
order by
  users desc