jhnvdw
jhnvdw
/ aw_page.csv
Created
March 6, 2020 14:12
We can make this file beautiful and searchable if this error is corrected: It looks like row 4 should actually have 17 columns, instead of 11. in line 3.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| hostname,page,previous_page,pagepath_level_1,pagepath_level_2,pagepath_level_3,pagepath_level_4,page_title,landing_page,second_page,exit_page,entrances,pageviews,unique_pageviews,pages_per_session,exits,exit_rate | |
| orangevalley.nl,https://orangevalley.nl/werken-bij/senior-content-consultant,,/werken-bij,/senior-content-consultant,,,Senior Content Consultant | Werken bij OrangeValley,https://orangevalley.nl/werken-bij/senior-content-consultant,,https://orangevalley.nl/werken-bij/senior-content-consultant,68,68,68,1.0,68,1.0 | |
| orangevalley.nl,https://orangevalley.nl/,,,,,,OrangeValley - Grensverleggend in Online Marketing,https://orangevalley.nl/,,https://orangevalley.nl/,387,387,386,1.0025906735751295,387,1.0 | |
| orangevalley.nl,https://orangevalley.nl/display-advertising/doubleclick/doubleclick-bid-manager/,,/display-advertising,/doubleclick,/doubleclick-bid-manager,,Google Marketing Platform: Display en Video 360 | OrangeValley,https://orangevalley.nl/display-advertising/doubleclick/doubleclick-bid-manager/,,https:/ |
jhnvdw
/ aw_page.sql
Last active
March 20, 2023 12:37
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| WITH | |
| -- Subquery to define static and/or dynamic start and end date for the whole query | |
| period AS ( | |
| SELECT | |
| '20200220' AS start_date, | |
| FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS end_date), | |
| -- Subquery to prepare and calculate page view data | |
| pages AS ( | |
| SELECT | |
| user_pseudo_id, |
jhnvdw
/ aw_event_count.sql
Last active
March 20, 2023 12:40
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| WITH | |
| -- Subquery to define static and/or dynamic start and end date for the whole query | |
| period AS ( | |
| SELECT | |
| '20200220' AS start_date, | |
| FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS end_date) | |
| SELECT | |
| -- Event count per user (metric | the number of times an individual event (change event_name 'page_view' to event that needs to be counted) was triggered divided by amount of users) | |
| COUNT(DISTINCT | |
| CASE |
jhnvdw
/ aw_traffic.csv
Last active
March 20, 2023 12:41
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| default_channel_grouping_user | source_medium_user | campaign_user | default_channel_grouping_session | source_medium_session | campaign_session | full_referrer | |
|---|---|---|---|---|---|---|---|
| Organic Search | google / organic | (organic) | Organic Search | google / organic | (organic) | https://www.google.com/ | |
| hs_email / email | Kennisevent Customer Journey Orchestration | 26.03.2020 | hs_email / email | Kennisevent Customer Journey Orchestration | 26.03.2020 | ||||
| Direct | (direct) / (none) | (direct) | Direct | (direct) / (none) | (direct) | ||
| Direct | (direct) / (none) | (direct) | Organic Search | google / organic | (organic) | https://www.google.com/ | |
| Direct | (direct) / (none) | (direct) | Referral | vprx.xyz / referral | (referral) | http://vprx.xyz/results.php?wd=waarom%20you%20tube%20niet%20mag%20ontbreken | |
| Organic Search | google / organic | (organic) | Direct | (direct) / (none) | (direct) | ||
| Organic Search | google / organic | (organic) | Organic Search | google / organic | (organic) | https://www.google.nl/ | |
| Referral | sanderheilbron.nl / referral | (referral) | Referral | sanderheilbron.nl / referral | (referral) | https:/ |
jhnvdw
/ aw_traffic.sql
Last active
March 20, 2023 12:43
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| WITH | |
| -- Subquery to set the start and end date once for the whole query | |
| period AS ( | |
| SELECT | |
| '20200220' AS start_date, | |
| FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS end_date), | |
| -- Subquery to prepare and calculate traffic source data based on user and session id | |
| traffic AS ( | |
| SELECT | |
| user_pseudo_id, |
jhnvdw
/ aw_time.csv
Last active
May 31, 2022 00:44
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| date | year | iso_year | month_of_year | month_of_the_year | week_of_year | week_of_the_year | iso_week_of_the_year | iso_week_of_iso_year | day_of_the_month | day_of_week | day_of_week_name | hour_of_day | hour | minute | date_hour_and_minute | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 20200223 | 2020 | 2020 | 202002 | 02 | 202008 | 08 | 07 | 202007 | 23 | 0 | Sunday | 2020022323 | 23 | 08 | 20200223238 | |
| 20200223 | 2020 | 2020 | 202002 | 02 | 202008 | 08 | 07 | 202007 | 23 | 0 | Sunday | 2020022323 | 23 | 08 | 20200223238 | |
| 20200223 | 2020 | 2020 | 202002 | 02 | 202008 | 08 | 07 | 202007 | 23 | 0 | Sunday | 2020022323 | 23 | 08 | 20200223238 | |
| 20200223 | 2020 | 2020 | 202002 | 02 | 202008 | 08 | 07 | 202007 | 23 | 0 | Sunday | 2020022323 | 23 | 08 | 20200223238 | |
| 20200223 | 2020 | 2020 | 202002 | 02 | 202008 | 08 | 07 | 202007 | 23 | 0 | Sunday | 2020022323 | 23 | 08 | 20200223238 | |
| 20200223 | 2020 | 2020 | 202002 | 02 | 202008 | 08 | 07 | 202007 | 23 | 0 | Sunday | 2020022323 | 23 | 08 | 20200223238 | |
| 20200223 | 2020 | 2020 | 202002 | 02 | 202008 | 08 | 07 | 202007 | 23 | 0 | Sunday | 2020022323 | 23 | 08 | 20200223238 | |
| 20200223 | 2020 | 2020 | 202002 | 02 | 202008 | 08 | 07 | 202007 | 23 | 0 | Sunday | 2020022323 | 23 | 08 | 20200223238 | |
| 20200223 | 2020 | 2020 | 202002 | 02 | 202008 | 08 | 07 | 202007 | 23 | 0 | Sunday | 2020022323 | 23 | 08 | 20200223238 |
jhnvdw
/ aw_time.sql
Last active
March 20, 2023 12:45
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT | |
| -- Date (dimension) | |
| event_date AS date, | |
| -- Year (dimension) | |
| FORMAT_DATE('%Y', PARSE_DATE("%Y%m%d", | |
| event_date)) AS year, | |
| -- ISO Year (dimension) | |
| FORMAT_DATE('%G', PARSE_DATE("%Y%m%d", | |
| event_date)) AS iso_year, | |
| -- Month of Year (dimension) |
jhnvdw
/ aw_engaged_session_calc.csv
Last active
May 31, 2022 00:44
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| date | engaged_sessions | |
|---|---|---|
| 2020-02-23 | 181 | |
| 2020-02-22 | 221 | |
| 2020-02-21 | 452 | |
| 2020-02-20 | 526 |
jhnvdw
/ aw_engaged_session_calc.sql
Last active
May 31, 2022 00:44
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| WITH | |
| -- Subquery to define static and/or dynamic start and end date for the whole query | |
| period AS ( | |
| SELECT | |
| '20200220' AS start_date, | |
| FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS end_date), | |
| -- Subquery to get all sessions with a length > 10 seconds | |
| session_length AS ( | |
| SELECT | |
| user_pseudo_id, |
jhnvdw
/ aw_session_calc.csv
Last active
May 31, 2022 00:43
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| sessions | engaged_sessions | engagement_rate | engagement_time | bounces | bounce_rate | event_count_per_session | |
|---|---|---|---|---|---|---|---|
| 1898 | 686 | 0.36143308746048475 | 54.935732254047316 | 874 | 0.46048472075869334 | 1.5152792413066385 |
NewerOlder