Example Google Analytics 360 analysis with Rax

This notebook shows how you can use Rax to analyse raw Google Analytics 360 data directly where it lives - in Google Big query. For this analysis, we're going to use the GA360 example dataset: London Cycle Helmet dataset.

Getting the data

Rax can process the data directly in the datastore, provided it's an SQL-enabled datastore. BigQuery fits this definition, so we can simply point Rax to where the data lives using the import statement:

In [1]:
{[$:fullVisitorId, #:visitNumber, #:visitId, #:visitStartTime, $:date,                                
  $:ts_referralPath, $:ts_campaign, $:ts_source, $:ts_medium, $:ts_keyword, $:ts_adContent]} : ga_sessions :=
import [
  ($)"fullVisitorId", (#)"visitNumber", (#)"visitId", (#)"visitStartTime", ($)"date",
  ($)"trafficSource.referralPath", ($)"trafficSource.campaign", ($)"trafficSource.source",
  ($)"trafficSource.medium", ($)"trafficSource.keyword", ($)"trafficSource.adContent"
] "LondonCycleHelmet.ga_sessions_20130910";

In the above code, we've created a Rax ga_sessions set by importing the ga_sessions_20130910 table from the LondonCycleHelmet dataset. This table, however, contains only whole sessions, but not the individual hits within sesions. Google Analytics uses a nested array to store individual hits within sessions. To convert this construct into a normal relational form, we use BigQuery's UNNEST operator:

In [2]:
{[$:fullVisitorId, #:visitNumber, #:visitId, #:visitStartTime, $:date,                                
  $:ts_referralPath, $:ts_campaign, $:ts_source, $:ts_medium, $:ts_keyword, $:ts_adContent,           
  #:h_time, #:h_hour, #:h_minute, $:h_referer,                                                        
  $:h_p_pagePath, $:h_p_pageTitle, $:h_type]} : ga_hits :=                                            
import [                                                                                              
  ($)"fullVisitorId", (#)"visitNumber", (#)"visitId", (#)"visitStartTime", ($)"date",                 
  ($)"trafficSource.referralPath", ($)"trafficSource.campaign", ($)"trafficSource.source",            
  ($)"trafficSource.medium", ($)"trafficSource.keyword", ($)"trafficSource.adContent",                
  (#)"time", (#)"hour", (#)"minute", ($)"referer",                                                    
  ($)"page.pagePath", ($)"page.pageTitle", ($)"type"                                                  
] "(SELECT * FROM LondonCycleHelmet.ga_sessions_20130910, UNNEST(hits)) AS GA";

Now we also have a ga_hits set containing individual hits. Note that so far no data was copied out of BigQuery. We've just created a mapping between Rax sets and BigQuery tables.

Extracting interesting events

The next step is converting the URLs into events interesting for us. Also, we'd like to take full advantage of Rax temporal operator, so we want to convert the visitStartTime and h_time fields into a Rax interval type (|):

In [3]:
// We're only interested in the 'PAGE' hit types
ga_hits := select [.h_type == "PAGE"] ga_hits;

// GA uses Unix timestamps, we need to convert them to Rax time by adding number
// of seconds (.visitStartTime) to Unix Epoch (midnight on 1 Jan 1970)
@:UnixEpoch := (@)"1970-01-01T00:00:00";

// Encode the page hits as events, give every event a single-character code
{[$:UserID, $:EventType, @:Timestamp]} : Events :=
project [                                                      
  .fullVisitorId,                                                                                                 
  .h_p_pagePath =~ 'confirm.html'            ? "!" : //order                                                      
  .h_p_pagePath =~ 'basket.html'             ? "B" : //product in basket
  .h_p_pagePath =~ '(vests|helmets)/.*.html' ? "P" : //product page                                               
  .h_p_pagePath =~ '(vests|helmets)/'        ? "C" : //category page                                              
  .h_p_pagePath == "/"                       ? "H" : //landing page                                               
                                               "_",  //other page                                                 
  UnixEpoch + .visitStartTime * (^)"PT1S" + (&).h_time/10000 * (^)"PT1S"                                          
] ga_hits;

If you would like to see what the event set looks like now, add a log statement to print a few:

In [4]:
`log Events![.UserID, .Timestamp] \ 10;

(42):

UserID EventType Timestamp
"380066991751227408" "P" 2013-09-10T09:36:16
"380066991751227408" "H" 2013-09-10T09:36:16
"380066991751227408" "H" 2013-09-10T09:36:17.74660
"380066991751227408" "C" 2013-09-10T09:36:18.02110
"380066991751227408" "P" 2013-09-10T09:36:18.26950
"712553853382222331" "C" 2013-09-10T09:10:18
"712553853382222331" "P" 2013-09-10T09:10:18.96110
"712553853382222331" "_" 2013-09-10T09:10:20.04580
"712553853382222331" "B" 2013-09-10T09:10:20.99600
"712553853382222331" "_" 2013-09-10T09:10:21.77090

Modeling gaps

If a user left our website for a longer time, we would like to see it as a separate gap event. Rather than using session identifiers created by Google Analytics, we will use Rax's temporal bridge operator (@|-) to find gaps only based on time between consecutive events. This methodology is much more reliable than session IDs and we also have full flexibility in defining how long a gap should be to count as a separate event:

In [5]:
// Add gaps longer than 15 minutes as 'gap'event with character code " "
\Events: Gaps :=
  project [.UserID, " ", .#1.begin + (^)"PT2S"] // make sure that gap event starts 2 seconds after previous event
  select [.#1.absolute > (^)"PT15M"] // select gaps that are > 15 minutes
  @|-:[.UserID] // apply bridge
  project [.UserID, .EventType, (|)[.Timestamp, (^)"PT0S"]] // convert events into 0-second intervals
  Events;

There are a few things that needed explanation. Reading from the bottom:

  • The (|)[.Timestamp, (^)"PT0S"] statement converts timestamps into 0-second intervals. This is only needed for the bridge operator which works on sets of intervals, rather than sets of timestamps
  • The bridge operator @|-:[.UserID] first partitions the set by UserID and then computes gaps between intervals within each partition
  • The select [.#1.absolute > (^)"PT15M"] statement returns only gaps whose absolute duration is greater than 15 minutes
  • Finally, we project the set of gaps back to a set of events: project [.UserID, " ", .#1.begin + (^)"PT2S"]. We give these new events a special code " " and we shift the event timestamps 2 seconds forward to make sure that they don't have the same start times as the page visits - otherwise we will run into trouble when sorting events on Timestamp.

Add a log statement to take a look at the set of gaps:

In [6]:
`log Gaps![.UserID, .Timestamp] \ 10;

(52):

UserID EventType Timestamp
"4158255675143559999" "" 2013-09-10T13:14:42.82240
"4484693148352576696" "" 2013-09-10T11:04:21.23770
"4898962642635257761" "" 2013-09-10T10:42:38.58290
"5711522334224447562" "" 2013-09-10T09:02:06.77980
"6274630192118831678" "" 2013-09-10T13:10:24
"6486884456525143279" "" 2013-09-10T13:21:54
"6895663419150465961" "" 2013-09-10T09:42:23.79270
"7405989496315895794" "" 2013-09-10T09:01:40.79700
"8266000033835867941" "" 2013-09-10T09:20:05.50940
"9007483028904009722" "" 2013-09-10T09:01:47

Modeling traffic sources

Now we also want to add various traffic sources as events in our customer journeys. In the London Cycle Helmet dataset, there are three major traffic sources, encoded in columns ts_source and ts_medium: organic google search, paid google search and referrals from techsauce.com website. The below code creates these events:

In [7]:
// Add the 'traffic source' events
// Organic search, coded as "O"
\Events : SearchEvents := project [                                                                               
  .fullVisitorId,                                                                                                 
  "O",                                                                                                            
  UnixEpoch + (.visitStartTime - 1) * (^)"PT1S"                                                                   
] select [.ts_source == "google" && .ts_medium == "organic"] ga_sessions;                                         

// Paid search, coded as "A"
\Events : PaidSearchEvents := project [                                                                           
  .fullVisitorId,                                                                                                 
  "A",                                                                                                            
  UnixEpoch + (.visitStartTime - 1) * (^)"PT1S"                                                                   
] select [.ts_source == "google" && .ts_medium == "cpc"] ga_sessions;                                             

// Referral traffic coded as "R"
\Events : ReferralEvents := project [                                                                            
  .fullVisitorId,                                                                                                 
  "R",                                                                                                            
  UnixEpoch + (.visitStartTime - 1) * (^)"PT1S"                                                                   
] select [.ts_medium == "referral"] ga_sessions;

The UnixEpoch + (.visitStartTime - 1) * (^)"PT1S" expression requires some explanation. Basically we take the timestamp of the event that resulted from this traffic source and we substract one second. This will be the timestamp of the new event - Google search, Google ad click or a visit to the referring website. This timestamp we then convert from Unix timestamp to Rax timestamp.

Putting it all together

Now we can put all events together: page visits, traffic source events and gaps. Then we use the string concatenation aggregate /cat() to glue paths into strings and print them out.

In [8]:
// Put all types of events in a single set
Events := SearchEvents \/ PaidSearchEvents \/ ReferralEvents \/ Gaps \/ Events;
                                                                                                                  
// Sort on user ID and time, so that they appear in the paths string in the right order
Events := Events![.UserID, .Timestamp];                                                                           
 
// Create string path representations
{[$:UserID, $:PathString]} : Paths :=                                                                             
  fold [.UserID, /cat(.EventType)]                                                                                
  Events;

`print Paths;
UserID PathString
"1677140157296205498" "APB_B__!"
"1835100872530393153" "AHCP"
"1856398683343353505" "OPCH"
"2799810042573824329" "ACP"
"2863775295455491161" "OHC"
"2879713562608983525" "HCPHCPAC"
"3163427106339104046" "RP_HCPB"
"3730804243329645579" "ACP_RP"
"380066991751227408" "RPHHCP"
"3825294378540130216" "P"
"3825449499873960749" "P"
"3878411695083249177" "OHC_HAHCPB__!"
"3960256913998800485" "HCPP"
"4158255675143559999" "OHC_H APB__!"
"4484693148352576696" "OCP OCP_"
"4898962642635257761" "OPH_ AHCPB__!"
"5150229340531647568" "OP"
"5622271784237590416" "AC"
"5682534891274575276" "HCPP_B__!"
"5711522334224447562" "HCP H_B__!OP"
"5731434086076965727" "PPHCP_B"
"5745347847380722644" "RPB__!"
"5844330423893548973" "HPCHCP"
"6274630192118831678" "AC OH"
"6363969437475725110" "HCP"
"6486884456525143279" "RP ACB__!"
"6647964159829802091" "AC_HCPCP"
"6728238996967595065" "OPPP_HC OP"
"6865576331513008945" "OC_HCPC_HCPPB__!"
"6895663419150465961" "H_P P"
"6955732612793949850" "OP_B__!"
"712553853382222331" "OCHP_B__!"
"7236424239009423246" "HHHHCPB__!"
"7237196959460548458" "HRP"
"7300674583230606186" "OPP_B__!"
"7405989496315895794" "H_ OP"
"7715166900416604175" "ACP_HCPCPB__!"
"8266000033835867941" "RP_ OC_PHCPB__!"
"8285885775366518775" "H_"
"8773537577630099787" "OP_B"
"881288060286722202" "OCP OHOH_HCPB"
"9007483028904009722" "ACP AH_HCPOP!B__"

Now we can do all sorts of pattern matching on these paths. For example, find all paths that started with a paid ad:

In [9]:
`print select [.PathString =~ '^A.*'] Paths;
UserID PathString
"1677140157296205498" "APB_B__!"
"1835100872530393153" "AHCP"
"2799810042573824329" "ACP"
"3730804243329645579" "ACP_RP"
"5622271784237590416" "AC"
"6274630192118831678" "AC OH"
"6647964159829802091" "AC_HCPCP"
"7715166900416604175" "ACP_HCPCPB__!"
"9007483028904009722" "ACP AH_HCPOP!B__"

Find all paths that started in organic search, had at least one gap and ended in conversion:

In [11]:
`print select [.PathString =~ '^O.* .*!.*'] Paths;
UserID PathString
"4158255675143559999" "OHC_H APB__!"
"4898962642635257761" "OPH_ AHCPB__!"

Compute the conversion percentage for all paths starting with a paid ad

In [12]:
\Paths : PaidAdPaths := select [.PathString =~ '^A.*'] Paths;
\Paths : ConvertedPaidAdPaths := select [.PathString =~ '^A.*!.*'] Paths;
`print "Conversion rate for paid ads: " + ($)((&)#ConvertedPaidAdPaths/#PaidAdPaths);

Conversion rate for paid ads: 0.333333333333333

Note that all the processing is done by BigQuery, no data is being transfered in or out. Rax translates all its statements into SQL query snippets. The snippets are glued together until you actually request some output, for example try to print the conversion rate. Then Rax sends a big SQL query to the backend, BigQuery in this case. If you want to see the generated SQL, use the %sql on; statement:

In [13]:
%sql on;
`print "Conversion rate for paid ads: " + ($)((&)#ConvertedPaidAdPaths/#PaidAdPaths);

SQL> SELECT count() FROM (SELECT A.rownum AS rownum, C01 AS C01, C02 AS C02 FROM (SELECT MIN(rownum) AS rownum, C01 AS C01, STRING_AGG(C02, "" ORDER BY rownum) AS C02 FROM (SELECT rownum, C01 AS C01, C02 AS C02 FROM (SELECT ROW_NUMBER() OVER(ORDER BY C01, C03) AS rownum, C01 AS C01, C02 AS C02, C03 AS C03 FROM (SELECT 0 AS rownum,C01 AS C01, C02 AS C02, C03 AS C03 FROM (SELECT 0 AS rownum,C01 AS C01, C02 AS C02, C03 AS C03 FROM (SELECT 0 AS rownum,C01 AS C01, C02 AS C02, C03 AS C03 FROM (SELECT 0 AS rownum,C01 AS C01, C02 AS C02, C03 AS C03 FROM (SELECT 0 AS rownum, C01 AS C01, "O" AS C02, (2108668032000000)+(((C04)-(1))(10000)) AS C03 FROM (SELECT A.rownum AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, C04 AS C04, C05 AS C05, C06 AS C06, C07 AS C07, C08 AS C08, C09 AS C09, C10 AS C10, C11 AS C11 FROM (SELECT 0 AS rownum,COALESCE(fullVisitorId,"") AS C01, COALESCE(visitNumber,0) AS C02, COALESCE(visitId,0) AS C03, COALESCE(visitStartTime,0) AS C04, COALESCE(date,"") AS C05, COALESCE(trafficSource.referralPath,"") AS C06, COALESCE(trafficSource.campaign,"") AS C07, COALESCE(trafficSource.source,"") AS C08, COALESCE(trafficSource.medium,"") AS C09, COALESCE(trafficSource.keyword,"") AS C10, COALESCE(trafficSource.adContent,"") AS C11 FROM LondonCycleHelmet.ga_sessions_20130910) AS A WHERE (((C08)=("google"))AND((C09)=("organic")))) AS A ) AS A UNION ALL SELECT 0 AS rownum,C01 AS C01, C02 AS C02, C03 AS C03 FROM (SELECT 0 AS rownum, C01 AS C01, "A" AS C02, (2108668032000000)+(((C04)-(1))(10000)) AS C03 FROM (SELECT A.rownum AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, C04 AS C04, C05 AS C05, C06 AS C06, C07 AS C07, C08 AS C08, C09 AS C09, C10 AS C10, C11 AS C11 FROM (SELECT 0 AS rownum,COALESCE(fullVisitorId,"") AS C01, COALESCE(visitNumber,0) AS C02, COALESCE(visitId,0) AS C03, COALESCE(visitStartTime,0) AS C04, COALESCE(date,"") AS C05, COALESCE(trafficSource.referralPath,"") AS C06, COALESCE(trafficSource.campaign,"") AS C07, COALESCE(trafficSource.source,"") AS C08, COALESCE(trafficSource.medium,"") AS C09, COALESCE(trafficSource.keyword,"") AS C10, COALESCE(trafficSource.adContent,"") AS C11 FROM LondonCycleHelmet.ga_sessions_20130910) AS A WHERE (((C08)=("google"))AND((C09)=("cpc")))) AS A ) AS B ) AS A UNION ALL SELECT 0 AS rownum,C01 AS C01, C02 AS C02, C03 AS C03 FROM (SELECT 0 AS rownum, C01 AS C01, "R" AS C02, (2108668032000000)+(((C04)-(1))(10000)) AS C03 FROM (SELECT A.rownum AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, C04 AS C04, C05 AS C05, C06 AS C06, C07 AS C07, C08 AS C08, C09 AS C09, C10 AS C10, C11 AS C11 FROM (SELECT 0 AS rownum,COALESCE(fullVisitorId,"") AS C01, COALESCE(visitNumber,0) AS C02, COALESCE(visitId,0) AS C03, COALESCE(visitStartTime,0) AS C04, COALESCE(date,"") AS C05, COALESCE(trafficSource.referralPath,"") AS C06, COALESCE(trafficSource.campaign,"") AS C07, COALESCE(trafficSource.source,"") AS C08, COALESCE(trafficSource.medium,"") AS C09, COALESCE(trafficSource.keyword,"") AS C10, COALESCE(trafficSource.adContent,"") AS C11 FROM LondonCycleHelmet.ga_sessions_20130910) AS A WHERE ((C09)=("referral"))) AS A ) AS B ) AS A UNION ALL SELECT 0 AS rownum,C01 AS C01, C02 AS C02, C03 AS C03 FROM (SELECT 0 AS rownum, C03 AS C01, " " AS C02, (C01)+(20000) AS C03 FROM (SELECT A.rownum AS rownum, C01 AS C01, C02 AS C02, C03 AS C03 FROM (SELECT 0 AS rownum, ts AS C01, la AS C02,C01 AS C03 FROM (SELECT , LEAD(ts, 1) OVER (PARTITION BY C01 ORDER BY ts, pc) AS la, SUM(pc) OVER (PARTITION BY C01 ORDER BY ts, pc) AS sum FROM (SELECT -1 AS pc, C03 as ts,C01 FROM (SELECT ROW_NUMBER() OVER(ORDER BY C01, C02, C03, C04) AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, C04 AS C04 FROM (SELECT 0 AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, (C03)+(0) AS C04 FROM (SELECT 0 AS rownum, C01 AS C01, CASE WHEN( (CASE WHEN(REGEXP_CONTAINS(C16, "confirm.html"))THEN(1)ELSE(0)END)<>0 )THEN(("!"))ELSE((CASE WHEN( (CASE WHEN(REGEXP_CONTAINS(C16, "basket.html"))THEN(1)ELSE(0)END)<>0 )THEN(("B"))ELSE((CASE WHEN( (CASE WHEN(REGEXP_CONTAINS(C16, "(vests|helmets)/(.).html"))THEN(1)ELSE(0)END)<>0 )THEN(("P"))ELSE((CASE WHEN( (CASE WHEN(REGEXPCONTAINS(C16, "(vests|helmets)/"))THEN(1)ELSE(0)END)<>0 )THEN(("C"))ELSE((CASE WHEN( (CASE WHEN((C16)=("/"))THEN( 1 )ELSE( 0 )END)<>0 )THEN(("H"))ELSE((""))END))END))END))END))END AS C02, ((2108668032000000)+((C04)(10000)))+(CAST(((CAST(C12 AS FLOAT64))/(CAST(10000 AS FLOAT64)))(10000) AS INT64)) AS C03 FROM (SELECT A.rownum AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, C04 AS C04, C05 AS C05, C06 AS C06, C07 AS C07, C08 AS C08, C09 AS C09, C10 AS C10, C11 AS C11, C12 AS C12, C13 AS C13, C14 AS C14, C15 AS C15, C16 AS C16, C17 AS C17, C18 AS C18 FROM (SELECT 0 AS rownum,COALESCE(fullVisitorId,"") AS C01, COALESCE(visitNumber,0) AS C02, COALESCE(visitId,0) AS C03, COALESCE(visitStartTime,0) AS C04, COALESCE(date,"") AS C05, COALESCE(trafficSource.referralPath,"") AS C06, COALESCE(trafficSource.campaign,"") AS C07, COALESCE(trafficSource.source,"") AS C08, COALESCE(trafficSource.medium,"") AS C09, COALESCE(trafficSource.keyword,"") AS C10, COALESCE(trafficSource.adContent,"") AS C11, COALESCE(time,0) AS C12, COALESCE(hour,0) AS C13, COALESCE(minute,0) AS C14, COALESCE(referer,"") AS C15, COALESCE(page.pagePath,"") AS C16, COALESCE(page.pageTitle,"") AS C17, COALESCE(type,"") AS C18 FROM (SELECT FROM LondonCycleHelmet.ga_sessions_20130910, UNNEST(hits)) AS GA) AS A WHERE ((C18)=("PAGE"))) AS A ) AS A ) AS A ) AS IN1 UNION ALL SELECT 1 AS pc, C04 as ts,C01 FROM (SELECT ROW_NUMBER() OVER(ORDER BY C01, C02, C03, C04) AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, C04 AS C04 FROM (SELECT 0 AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, (C03)+(0) AS C04 FROM (SELECT 0 AS rownum, C01 AS C01, CASE WHEN( (CASE WHEN(REGEXP_CONTAINS(C16, "confirm.html"))THEN(1)ELSE(0)END)<>0 )THEN(("!"))ELSE((CASE WHEN( (CASE WHEN(REGEXP_CONTAINS(C16, "basket.html"))THEN(1)ELSE(0)END)<>0 )THEN(("B"))ELSE((CASE WHEN( (CASE WHEN(REGEXP_CONTAINS(C16, "(vests|helmets)/(.).html"))THEN(1)ELSE(0)END)<>0 )THEN(("P"))ELSE((CASE WHEN( (CASE WHEN(REGEXPCONTAINS(C16, "(vests|helmets)/"))THEN(1)ELSE(0)END)<>0 )THEN(("C"))ELSE((CASE WHEN( (CASE WHEN((C16)=("/"))THEN( 1 )ELSE( 0 )END)<>0 )THEN(("H"))ELSE((""))END))END))END))END))END AS C02, ((2108668032000000)+((C04)(10000)))+(CAST(((CAST(C12 AS FLOAT64))/(CAST(10000 AS FLOAT64)))(10000) AS INT64)) AS C03 FROM (SELECT A.rownum AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, C04 AS C04, C05 AS C05, C06 AS C06, C07 AS C07, C08 AS C08, C09 AS C09, C10 AS C10, C11 AS C11, C12 AS C12, C13 AS C13, C14 AS C14, C15 AS C15, C16 AS C16, C17 AS C17, C18 AS C18 FROM (SELECT 0 AS rownum,COALESCE(fullVisitorId,"") AS C01, COALESCE(visitNumber,0) AS C02, COALESCE(visitId,0) AS C03, COALESCE(visitStartTime,0) AS C04, COALESCE(date,"") AS C05, COALESCE(trafficSource.referralPath,"") AS C06, COALESCE(trafficSource.campaign,"") AS C07, COALESCE(trafficSource.source,"") AS C08, COALESCE(trafficSource.medium,"") AS C09, COALESCE(trafficSource.keyword,"") AS C10, COALESCE(trafficSource.adContent,"") AS C11, COALESCE(time,0) AS C12, COALESCE(hour,0) AS C13, COALESCE(minute,0) AS C14, COALESCE(referer,"") AS C15, COALESCE(page.pagePath,"") AS C16, COALESCE(page.pageTitle,"") AS C17, COALESCE(type,"") AS C18 FROM (SELECT FROM LondonCycleHelmet.ga_sessions_20130910, UNNEST(hits)) AS GA) AS A WHERE ((C18)=("PAGE"))) AS A ) AS A ) AS A ) AS IN2) AS SUB1) AS SUB2 WHERE sum = 0 AND la IS NOT NULL AND la != ts) AS A WHERE (((C02)-(C01))>(9000000))) AS A ) AS B ) AS A UNION ALL SELECT 0 AS rownum,C01 AS C01, C02 AS C02, C03 AS C03 FROM (SELECT 0 AS rownum, C01 AS C01, CASE WHEN( (CASE WHEN(REGEXP_CONTAINS(C16, "confirm.html"))THEN(1)ELSE(0)END)<>0 )THEN(("!"))ELSE((CASE WHEN( (CASE WHEN(REGEXP_CONTAINS(C16, "basket.html"))THEN(1)ELSE(0)END)<>0 )THEN(("B"))ELSE((CASE WHEN( (CASE WHEN(REGEXP_CONTAINS(C16, "(vests|helmets)/(.).html"))THEN(1)ELSE(0)END)<>0 )THEN(("P"))ELSE((CASE WHEN( (CASE WHEN(REGEXPCONTAINS(C16, "(vests|helmets)/"))THEN(1)ELSE(0)END)<>0 )THEN(("C"))ELSE((CASE WHEN( (CASE WHEN((C16)=("/"))THEN( 1 )ELSE( 0 )END)<>0 )THEN(("H"))ELSE((""))END))END))END))END))END AS C02, ((2108668032000000)+((C04)(10000)))+(CAST(((CAST(C12 AS FLOAT64))/(CAST(10000 AS FLOAT64)))(10000) AS INT64)) AS C03 FROM (SELECT A.rownum AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, C04 AS C04, C05 AS C05, C06 AS C06, C07 AS C07, C08 AS C08, C09 AS C09, C10 AS C10, C11 AS C11, C12 AS C12, C13 AS C13, C14 AS C14, C15 AS C15, C16 AS C16, C17 AS C17, C18 AS C18 FROM (SELECT 0 AS rownum,COALESCE(fullVisitorId,"") AS C01, COALESCE(visitNumber,0) AS C02, COALESCE(visitId,0) AS C03, COALESCE(visitStartTime,0) AS C04, COALESCE(date,"") AS C05, COALESCE(trafficSource.referralPath,"") AS C06, COALESCE(trafficSource.campaign,"") AS C07, COALESCE(trafficSource.source,"") AS C08, COALESCE(trafficSource.medium,"") AS C09, COALESCE(trafficSource.keyword,"") AS C10, COALESCE(trafficSource.adContent,"") AS C11, COALESCE(time,0) AS C12, COALESCE(hour,0) AS C13, COALESCE(minute,0) AS C14, COALESCE(referer,"") AS C15, COALESCE(page.pagePath,"") AS C16, COALESCE(page.pageTitle,"") AS C17, COALESCE(type,"") AS C18 FROM (SELECT FROM LondonCycleHelmet.ga_sessions_20130910, UNNEST(hits)) AS GA) AS A WHERE ((C18)=("PAGE"))) AS A ) AS B ) AS A ) AS A ) AS FOLD GROUP BY C01) AS A WHERE (REGEXP_CONTAINS(C02, "^(A(.))"))) AS A
SQL> SELECT count() FROM (SELECT A.rownum AS rownum, C01 AS C01, C02 AS C02 FROM (SELECT MIN(rownum) AS rownum, C01 AS C01, STRING_AGG(C02, "" ORDER BY rownum) AS C02 FROM (SELECT rownum, C01 AS C01, C02 AS C02 FROM (SELECT ROW_NUMBER() OVER(ORDER BY C01, C03) AS rownum, C01 AS C01, C02 AS C02, C03 AS C03 FROM (SELECT 0 AS rownum,C01 AS C01, C02 AS C02, C03 AS C03 FROM (SELECT 0 AS rownum,C01 AS C01, C02 AS C02, C03 AS C03 FROM (SELECT 0 AS rownum,C01 AS C01, C02 AS C02, C03 AS C03 FROM (SELECT 0 AS rownum,C01 AS C01, C02 AS C02, C03 AS C03 FROM (SELECT 0 AS rownum, C01 AS C01, "O" AS C02, (2108668032000000)+(((C04)-(1))(10000)) AS C03 FROM (SELECT A.rownum AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, C04 AS C04, C05 AS C05, C06 AS C06, C07 AS C07, C08 AS C08, C09 AS C09, C10 AS C10, C11 AS C11 FROM (SELECT 0 AS rownum,COALESCE(fullVisitorId,"") AS C01, COALESCE(visitNumber,0) AS C02, COALESCE(visitId,0) AS C03, COALESCE(visitStartTime,0) AS C04, COALESCE(date,"") AS C05, COALESCE(trafficSource.referralPath,"") AS C06, COALESCE(trafficSource.campaign,"") AS C07, COALESCE(trafficSource.source,"") AS C08, COALESCE(trafficSource.medium,"") AS C09, COALESCE(trafficSource.keyword,"") AS C10, COALESCE(trafficSource.adContent,"") AS C11 FROM LondonCycleHelmet.ga_sessions_20130910) AS A WHERE (((C08)=("google"))AND((C09)=("organic")))) AS A ) AS A UNION ALL SELECT 0 AS rownum,C01 AS C01, C02 AS C02, C03 AS C03 FROM (SELECT 0 AS rownum, C01 AS C01, "A" AS C02, (2108668032000000)+(((C04)-(1))(10000)) AS C03 FROM (SELECT A.rownum AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, C04 AS C04, C05 AS C05, C06 AS C06, C07 AS C07, C08 AS C08, C09 AS C09, C10 AS C10, C11 AS C11 FROM (SELECT 0 AS rownum,COALESCE(fullVisitorId,"") AS C01, COALESCE(visitNumber,0) AS C02, COALESCE(visitId,0) AS C03, COALESCE(visitStartTime,0) AS C04, COALESCE(date,"") AS C05, COALESCE(trafficSource.referralPath,"") AS C06, COALESCE(trafficSource.campaign,"") AS C07, COALESCE(trafficSource.source,"") AS C08, COALESCE(trafficSource.medium,"") AS C09, COALESCE(trafficSource.keyword,"") AS C10, COALESCE(trafficSource.adContent,"") AS C11 FROM LondonCycleHelmet.ga_sessions_20130910) AS A WHERE (((C08)=("google"))AND((C09)=("cpc")))) AS A ) AS B ) AS A UNION ALL SELECT 0 AS rownum,C01 AS C01, C02 AS C02, C03 AS C03 FROM (SELECT 0 AS rownum, C01 AS C01, "R" AS C02, (2108668032000000)+(((C04)-(1))(10000)) AS C03 FROM (SELECT A.rownum AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, C04 AS C04, C05 AS C05, C06 AS C06, C07 AS C07, C08 AS C08, C09 AS C09, C10 AS C10, C11 AS C11 FROM (SELECT 0 AS rownum,COALESCE(fullVisitorId,"") AS C01, COALESCE(visitNumber,0) AS C02, COALESCE(visitId,0) AS C03, COALESCE(visitStartTime,0) AS C04, COALESCE(date,"") AS C05, COALESCE(trafficSource.referralPath,"") AS C06, COALESCE(trafficSource.campaign,"") AS C07, COALESCE(trafficSource.source,"") AS C08, COALESCE(trafficSource.medium,"") AS C09, COALESCE(trafficSource.keyword,"") AS C10, COALESCE(trafficSource.adContent,"") AS C11 FROM LondonCycleHelmet.ga_sessions_20130910) AS A WHERE ((C09)=("referral"))) AS A ) AS B ) AS A UNION ALL SELECT 0 AS rownum,C01 AS C01, C02 AS C02, C03 AS C03 FROM (SELECT 0 AS rownum, C03 AS C01, " " AS C02, (C01)+(20000) AS C03 FROM (SELECT A.rownum AS rownum, C01 AS C01, C02 AS C02, C03 AS C03 FROM (SELECT 0 AS rownum, ts AS C01, la AS C02,C01 AS C03 FROM (SELECT , LEAD(ts, 1) OVER (PARTITION BY C01 ORDER BY ts, pc) AS la, SUM(pc) OVER (PARTITION BY C01 ORDER BY ts, pc) AS sum FROM (SELECT -1 AS pc, C03 as ts,C01 FROM (SELECT ROW_NUMBER() OVER(ORDER BY C01, C02, C03, C04) AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, C04 AS C04 FROM (SELECT 0 AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, (C03)+(0) AS C04 FROM (SELECT 0 AS rownum, C01 AS C01, CASE WHEN( (CASE WHEN(REGEXP_CONTAINS(C16, "confirm.html"))THEN(1)ELSE(0)END)<>0 )THEN(("!"))ELSE((CASE WHEN( (CASE WHEN(REGEXP_CONTAINS(C16, "basket.html"))THEN(1)ELSE(0)END)<>0 )THEN(("B"))ELSE((CASE WHEN( (CASE WHEN(REGEXP_CONTAINS(C16, "(vests|helmets)/(.).html"))THEN(1)ELSE(0)END)<>0 )THEN(("P"))ELSE((CASE WHEN( (CASE WHEN(REGEXPCONTAINS(C16, "(vests|helmets)/"))THEN(1)ELSE(0)END)<>0 )THEN(("C"))ELSE((CASE WHEN( (CASE WHEN((C16)=("/"))THEN( 1 )ELSE( 0 )END)<>0 )THEN(("H"))ELSE((""))END))END))END))END))END AS C02, ((2108668032000000)+((C04)(10000)))+(CAST(((CAST(C12 AS FLOAT64))/(CAST(10000 AS FLOAT64)))(10000) AS INT64)) AS C03 FROM (SELECT A.rownum AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, C04 AS C04, C05 AS C05, C06 AS C06, C07 AS C07, C08 AS C08, C09 AS C09, C10 AS C10, C11 AS C11, C12 AS C12, C13 AS C13, C14 AS C14, C15 AS C15, C16 AS C16, C17 AS C17, C18 AS C18 FROM (SELECT 0 AS rownum,COALESCE(fullVisitorId,"") AS C01, COALESCE(visitNumber,0) AS C02, COALESCE(visitId,0) AS C03, COALESCE(visitStartTime,0) AS C04, COALESCE(date,"") AS C05, COALESCE(trafficSource.referralPath,"") AS C06, COALESCE(trafficSource.campaign,"") AS C07, COALESCE(trafficSource.source,"") AS C08, COALESCE(trafficSource.medium,"") AS C09, COALESCE(trafficSource.keyword,"") AS C10, COALESCE(trafficSource.adContent,"") AS C11, COALESCE(time,0) AS C12, COALESCE(hour,0) AS C13, COALESCE(minute,0) AS C14, COALESCE(referer,"") AS C15, COALESCE(page.pagePath,"") AS C16, COALESCE(page.pageTitle,"") AS C17, COALESCE(type,"") AS C18 FROM (SELECT FROM LondonCycleHelmet.ga_sessions_20130910, UNNEST(hits)) AS GA) AS A WHERE ((C18)=("PAGE"))) AS A ) AS A ) AS A ) AS IN1 UNION ALL SELECT 1 AS pc, C04 as ts,C01 FROM (SELECT ROW_NUMBER() OVER(ORDER BY C01, C02, C03, C04) AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, C04 AS C04 FROM (SELECT 0 AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, (C03)+(0) AS C04 FROM (SELECT 0 AS rownum, C01 AS C01, CASE WHEN( (CASE WHEN(REGEXP_CONTAINS(C16, "confirm.html"))THEN(1)ELSE(0)END)<>0 )THEN(("!"))ELSE((CASE WHEN( (CASE WHEN(REGEXP_CONTAINS(C16, "basket.html"))THEN(1)ELSE(0)END)<>0 )THEN(("B"))ELSE((CASE WHEN( (CASE WHEN(REGEXP_CONTAINS(C16, "(vests|helmets)/(.).html"))THEN(1)ELSE(0)END)<>0 )THEN(("P"))ELSE((CASE WHEN( (CASE WHEN(REGEXPCONTAINS(C16, "(vests|helmets)/"))THEN(1)ELSE(0)END)<>0 )THEN(("C"))ELSE((CASE WHEN( (CASE WHEN((C16)=("/"))THEN( 1 )ELSE( 0 )END)<>0 )THEN(("H"))ELSE((""))END))END))END))END))END AS C02, ((2108668032000000)+((C04)(10000)))+(CAST(((CAST(C12 AS FLOAT64))/(CAST(10000 AS FLOAT64)))(10000) AS INT64)) AS C03 FROM (SELECT A.rownum AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, C04 AS C04, C05 AS C05, C06 AS C06, C07 AS C07, C08 AS C08, C09 AS C09, C10 AS C10, C11 AS C11, C12 AS C12, C13 AS C13, C14 AS C14, C15 AS C15, C16 AS C16, C17 AS C17, C18 AS C18 FROM (SELECT 0 AS rownum,COALESCE(fullVisitorId,"") AS C01, COALESCE(visitNumber,0) AS C02, COALESCE(visitId,0) AS C03, COALESCE(visitStartTime,0) AS C04, COALESCE(date,"") AS C05, COALESCE(trafficSource.referralPath,"") AS C06, COALESCE(trafficSource.campaign,"") AS C07, COALESCE(trafficSource.source,"") AS C08, COALESCE(trafficSource.medium,"") AS C09, COALESCE(trafficSource.keyword,"") AS C10, COALESCE(trafficSource.adContent,"") AS C11, COALESCE(time,0) AS C12, COALESCE(hour,0) AS C13, COALESCE(minute,0) AS C14, COALESCE(referer,"") AS C15, COALESCE(page.pagePath,"") AS C16, COALESCE(page.pageTitle,"") AS C17, COALESCE(type,"") AS C18 FROM (SELECT FROM LondonCycleHelmet.ga_sessions_20130910, UNNEST(hits)) AS GA) AS A WHERE ((C18)=("PAGE"))) AS A ) AS A ) AS A ) AS IN2) AS SUB1) AS SUB2 WHERE sum = 0 AND la IS NOT NULL AND la != ts) AS A WHERE (((C02)-(C01))>(9000000))) AS A ) AS B ) AS A UNION ALL SELECT 0 AS rownum,C01 AS C01, C02 AS C02, C03 AS C03 FROM (SELECT 0 AS rownum, C01 AS C01, CASE WHEN( (CASE WHEN(REGEXP_CONTAINS(C16, "confirm.html"))THEN(1)ELSE(0)END)<>0 )THEN(("!"))ELSE((CASE WHEN( (CASE WHEN(REGEXP_CONTAINS(C16, "basket.html"))THEN(1)ELSE(0)END)<>0 )THEN(("B"))ELSE((CASE WHEN( (CASE WHEN(REGEXP_CONTAINS(C16, "(vests|helmets)/(.).html"))THEN(1)ELSE(0)END)<>0 )THEN(("P"))ELSE((CASE WHEN( (CASE WHEN(REGEXPCONTAINS(C16, "(vests|helmets)/"))THEN(1)ELSE(0)END)<>0 )THEN(("C"))ELSE((CASE WHEN( (CASE WHEN((C16)=("/"))THEN( 1 )ELSE( 0 )END)<>0 )THEN(("H"))ELSE((""))END))END))END))END))END AS C02, ((2108668032000000)+((C04)(10000)))+(CAST(((CAST(C12 AS FLOAT64))/(CAST(10000 AS FLOAT64)))(10000) AS INT64)) AS C03 FROM (SELECT A.rownum AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, C04 AS C04, C05 AS C05, C06 AS C06, C07 AS C07, C08 AS C08, C09 AS C09, C10 AS C10, C11 AS C11, C12 AS C12, C13 AS C13, C14 AS C14, C15 AS C15, C16 AS C16, C17 AS C17, C18 AS C18 FROM (SELECT 0 AS rownum,COALESCE(fullVisitorId,"") AS C01, COALESCE(visitNumber,0) AS C02, COALESCE(visitId,0) AS C03, COALESCE(visitStartTime,0) AS C04, COALESCE(date,"") AS C05, COALESCE(trafficSource.referralPath,"") AS C06, COALESCE(trafficSource.campaign,"") AS C07, COALESCE(trafficSource.source,"") AS C08, COALESCE(trafficSource.medium,"") AS C09, COALESCE(trafficSource.keyword,"") AS C10, COALESCE(trafficSource.adContent,"") AS C11, COALESCE(time,0) AS C12, COALESCE(hour,0) AS C13, COALESCE(minute,0) AS C14, COALESCE(referer,"") AS C15, COALESCE(page.pagePath,"") AS C16, COALESCE(page.pageTitle,"") AS C17, COALESCE(type,"") AS C18 FROM (SELECT FROM LondonCycleHelmet.ga_sessions_20130910, UNNEST(hits)) AS GA) AS A WHERE ((C18)=("PAGE"))) AS A ) AS B ) AS A ) AS A ) AS FOLD GROUP BY C01) AS A WHERE (REGEXP_CONTAINS(C02, "^(A(.)!(.)*)"))) AS A
Conversion rate for paid ads: 0.333333333333333