Terminé

BigQuery SQL Event Attribution

Hello, thanks for reviewing.

I am looking to have some SQL written that will run on BigQuery with standard SQL to attribute some actions to events that took place before the action following some rules.

I will provide 3 tables of dummy data impressions, clicks & activities with the following schema::

activities:

user_id STRING

timestamp INTEGER

activity_id STRING

impressions & clicks:

user_id STRING

timestamp INTEGER

campaign_id STRING

site_id STRING

The SQL should take all actions in the activity table and identify the last event in the impression/click table that occurred prior to the time of the action. It also needs to follow the rule of; if a click existed in the previous events leading up to the action, the click should get credit, if no click existed in the path leading up to the action, then the last impression prior to the action gets credit.

The output should look something like:

campaign_id | site_id | attributed_to_click | attributed_to_impression

campaign_1 | site_1 | 2 | 1

campaign_1 | site_2 | 1 | 4

When the SQL is completed full comments should be added to explain the build.

This may lead to more work on an on going basis.

Compétences : Développement de bases de données, Programmation de bases de données, Google Cloud Platform, SQL

en voir plus : regexp_contains, standard sql data types bigquery, bigquery nest, bigquery unnest, bigquery standard sql, bigquery timestamp, array_agg bigquery, bigquery data types, sql server scorecard average handle time call center, php sql update something automatically determined time, promise highest quality support work time it hopefully enjoy work project , sync sql server data mysql real time, design a logo for a web/app it needs to come in version with name and with logo only so i can use it in our web page logo for th, design a logo for a webapp it needs to come in version with name and with logo only so i can use it in our web page logo for the, hello we are looking for new logo for our new brand co2 supermarket it needs to look very similar to brand we have now attached , i have written a book it needs editing, sql developer services offered jobs computing & it jobs western cape, sql developer services offered jobs computing it jobs western cape, average time it takes to find a job, part time it jobs

Concernant l'employeur :
( 1 commentaire ) London, United Kingdom

Nº du projet : #18138285

Décerné à:

Grunty

Hey there, I think I've got the script in my head already! :-) Contact me, and let's get it done. Carlos.

%selectedBids___i_sum_sub_4% %project_currencyDetails_sign_sub_5% USD en 1 jour
(4 Commentaires)
4.2

4 freelance font une offre moyenne de $29 pour ce travail

vw8098368vw

Hi There, I can do this task of SQL Complex query. Task related specification required. Thanks...

%bids___i_sum_sub_32% %project_currencyDetails_sign_sub_33% USD en 1 jour
(3 Commentaires)
2.7
Straust

Hello! My name is Stuart, and I've never worked with BigQuery, but sounds like an interesting - and simple - project to do so! In the other hand, my experience with SQL is of quite some years, that's why I'm conf Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% USD en 2 jours
(0 Commentaires)
0.0
ahmedkamran9

Hi I've got 15 years of experience in database development. I'm sure that I will be able to come up with this query very quickly. Regards Kamran

%bids___i_sum_sub_32% %project_currencyDetails_sign_sub_33% USD en 1 jour
(0 Commentaires)
0.0