CODEWITHSUNDEEP
google-analyticsgoogle-bigquery
James Harrington
James Harrington

Reputation: 103

How to create a conversion funnel based on pages and events for GA data in BigQuery

I have the following data in BigQuery:

date            fullVisitorId                              sessionId   hitNumber    type                      url   eventCategory   eventAction    eventLabel
20210101   973454546035798949   973454546035798949162783837520210101           1    PAGE             homepage.com            Null          Null          Null 
20210101   973454546035798949   973454546035798949162783837520210101           2   EVENT    homepage.com/purchase            View          Book  Harry_Potter
20210101   973454546035798949   973454546035798949162783837520210101           3   EVENT    homepage.com/purchase        Purchase          Book  Harry_Potter
...

I want to create a conversion funnel based on URLs and events, not necessarily sequential. For example, I want to calculate the number of distinct users (fullVisitorId) and the number of distinct sessions (sessionId) in which:

  1. Users visited the homepage (homepage.com).
  2. Then the event with Category View, Action Book and Label Harry_Potter was triggered,
  3. Then the event with Category Purchase, Action Book and Label Harry_Potter was triggered.

Again the hits are not necessarily sequential, which means that the hit numbers could be 1, 4, and 8, respectively, for these 3 steps. Also, the real number of desired steps is more than 10.

Ideally, the final results should look like this:

    Type          Date   Step 1  Step 2  Step 3  Step 4
   Users    01/01/2021      120     110      90     ...
   Users    02/01/2021      130      80      70     ...
Sessions    01/01/2021      200     120     100     ...
Sessions    02/01/2021      220      80      70     ...

where Step 1, Step 2, and Step 3 represent the number of users and sessions in which the particular step was done.

Any ideas? Thanks!

Upvotes: 0

Views: 428

Answers (1)

Mr.Batra
Mr.Batra

Reputation: 833

You will have to do something like this, below SQL code. For every condition u can have a CTE and then join.

WITH STEP1 AS
(
SELECT fullVisitorId, Date, SUM(hitNumber) AS STEP1
FROM `data-to-insights.ecommerce.all_sessions_raw`
WHERE STARTS_WITH(url, "homepage.com") AND fullVisitorId IS NOT NULL
GROUP BY fullVisitorId, Date
),
STEP2 AS 
(
SELECT fullVisitorId, Date, SUM(hitNumber) AS STEP2
FROM `data-to-insights.ecommerce.all_sessions_raw`
WHERE eventCategory = 'View'  AND eventAction = 'Book' AND eventLabel = 'Harry_Potter' AND fullVisitorId IS NOT NULL
GROUP BY fullVisitorId, Date
),
STEP3 AS 
(
SELECT fullVisitorId, Date, SUM(hitNumber) AS STEP3
FROM `data-to-insights.ecommerce.all_sessions_raw`
WHERE eventCategory = 'Purchase'  AND eventAction = 'Book' AND eventLabel = 'Harry_Potter' AND fullVisitorId IS NOT NULL
GROUP BY fullVisitorId, Date
),
JOINED_DATA AS
(
SELECT  'Users' AS Type,
        coalesce(SUB_QUERY.Date,STEP3.Date),
        STEP1.STEP1,
        STEP2.STEP2,
        STEP3.STEP3
FROM    STEP3 FULL OUTER JOIN
        (
        SELECT  coalesce(STEP1.fullVisitorId,STEP2.fullVisitorId) AS fullVisitorId,
                coalesce(STEP1.Date,STEP2.Date) AS Date
        FROM STEP1 FULL OUTER JOIN STEP2 
        ON STEP1.DATE = STEP2.DATE AND STEP1.fullVisitorId = STEP2.fullVisitorId
        ) AS SUB_QUERY 
        ON STEP3.fullVisitorId = SUB_QUERY.fullVisitorId AND STEP3.Date = SUB_QUERY.Date
)
SELECT * FROM JOINED_DATA

Upvotes: 0

Related Questions