Standard SQL query in Google BigQuery on an advanced eCommerce data set

Encerrado Postado há 5 anos Pago na entrega
Encerrado Pago na entrega

I need a query to extract the previous 10 values of a custom session level dimension before a purchase event and the value at time of purchase.

This is from a Google 360 dataset running advanced eCommerce. Purpose is to track the accuracy of custom Audience modelling.

From the results I need to:

Validate product purchased matches Audience value

Calculate / report on number of visits and time elapsed before a purchase is made

Report on how the Audience values change over time before purchase

Multiple products need to stringified or piped into a single field.

Output will be extracted to Tableau so all results need to be unnested. Query needs to be in Standard Sql

Please note that I cannot give access to the bigQuery instance.

I expect that I will need these values:

visitId,

fullVisitorId,

ClientId,

UserId,

session_id,

[login to view URL],

visitNumber,

date, (yyyymmdd)

visitStartTime, ("%Y-%m-%d %H:%M:%S", 'Australia/Sydney' )

transactionId,

[login to view URL],

[login to view URL],

[login to view URL],

[login to view URL],

[login to view URL],

(Note a single transactionId may have several different products)

[login to view URL], where index=144 AS dimension144,

visitNumber where transactionId is NOT NULL as t_visitNumber

dimension144 where transactionId as t_dimension144

dimension144 where visitNumber is = t_visitNumber - 1 as minus1

dimension144 where visitNumber is = t_visitNumber - 2 as minus2

dimension144 where visitNumber is = t_visitNumber - 3 as minus3

etc to 10

first occurrence (or minus10) dimension144 value as first_ dimension144

time between visits in days and hours for visits t to minus10 calculated against transaction time

time between first_ dimension144 to t_dimension144

TIMEFRAME: 1-2 HOURS

Google Analytics Google Cloud Platform SQL

ID do Projeto: #18374233

Sobre o projeto

10 propostas Projeto remoto Ativo em há 5 anos

10 freelancers estão ofertando em média $179 nesse trabalho

dat2na

Greetings, I'm professional database expert with 10+ years experience. Currently I'm working as DBA and DB developer in one of the leader company. As you can see in my profile, I have deep experience of same jobs lik Mais

$277 AUD in 2 dias
(2 Comentários)
4.2
tracygearth

I will create query and get required fields. thank you for posting job. I look forward to hear you. anuj

$200 AUD in 7 dias
(1 Comentário)
1.6
sasibhushanm

I'm having more than 2 years experience with Google Analytics and other Google Products. I primarily work on data analytics services to the clients with the focus being on Google Analytics and its Enterprise Solution ( Mais

$222 AUD in 3 dias
(1 Comentário)
0.5
raki06

I am interested this work and I had good experience of this work. I do my best of this work at as soon as possible.

$166 AUD em 1 dia
(0 Comentários)
0.0
dineshrajputit

Hi, I have 7 years of experience and working on hadoop, spark, nosql, java, BI tools(tableau, powerbi), cloud(Amazon, Google, Microsoft Azure)... Done end to end data warehouse management projects on aws cloud with ha Mais

$166 AUD in 2 dias
(0 Comentários)
0.0
hancieta

Currently working in bigquery and tableau, it could certainly meet your needs on the agreed day. I have a trajectory of 8 years working in Business Intelligence with different types of sql languages and management tool Mais

$200 AUD in 3 dias
(0 Comentários)
0.0