CODEWITHSUNDEEP
google-analyticsgoogle-bigquery
Nhi Yen
Nhi Yen

Reputation: 45

Replicate Top Conversion Path by Bigquery

Based on Google Analytics sample dataset I want to replicate the Top Conversion Path in Google Analytics by Bigquery. Anyone has any idea? enter image description here

The expected Output, the columns from left to right be like: enter image description here

enter image description here I want to find out the timestamp for each source/medium path also. For example, the source/medium path is A > B > C > Purchase, at which timestamp did A, B, C etc happen?

Thanks in advance. Really appreciate your help!

Upvotes: 0

Views: 651

Answers (1)

ewertonvsilva
ewertonvsilva

Reputation: 1955

it's hard to get an table grouped by the path because the paths can vary a lot based on the clicks the client did during the session.

The key here for you is use an aggregation function to convert the paths (that are mostly stored in RECORDS) to an string. To do this, I'm using this nice aggregation function STRING_AGG(hits.page.pagepath," > ") with the separator >, as you did in your example.

Here is an croqui with almost you need, please check the schema of the public dataset and personalize the fields you need.


WITH
  pathTable AS (
  SELECT
    visitId,
    AVG(totals.totalTransactionRevenue)/10000 AS revenue,
    STRING_AGG(hits.page.pagepath," > ") AS Fullpath
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
    UNNEST(hits) AS hits
  WHERE
    totals.totalTransactionRevenue IS NOT NULL
  GROUP BY
    visitId)
SELECT
  date,
  a.visitId,
  a.clientid,
  a.device.devicecategory,
  p.revenue,
  p.Fullpath
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*` AS a
JOIN
  pathTable AS p
ON
  p.visitId = a.visitId
LIMIT
  10;

And that is the example output for it: enter image description here

  • This link contains some examples of how to query there public datasets.

  • Another interesting link for you to undesrtand better about aggregation functions.

Upvotes: 2

Related Questions