Skip to content

Data Visualisation

patdbro edited this page May 26, 2020 · 25 revisions

12 Month Browser Daily Snapshot

Field Name Description Data Type Required Nullable
Device_Browser Name of Browser String No Yes
Month_year Observation Month & Year Header No Yes
Browser_count Numbers of Browser Integer No Yes
Percent_month Percent of the total amount Integer No Yes
Post_stamp Time of observation Date_stamp No Yes

Data Source: Google Analytics

Script location: https://github.com/govau/GAlileo/blob/master/dags/bq_scripts_project_ursa_major/dta_sql_browser_snapshot_12months_daily_doi

12 Month Browser Version Daily Snapshot

Field Name Description Data Type Required Nullable
Device_Browser_version Name of Browser String No Yes
Month_year Observation Month & Year Header No Yes
Browser_count Numbers of Browser Integer No Yes
Percent_month Percent of the total amount Integer No Yes
Post_stamp Time of observation Date_stamp No Yes

Data Source: Google Analytics

Script Location: https://github.com/govau/GAlileo/blob/master/dags/bq_scripts_project_ursa_major/dta_sql_browser_ver_snapshot_12months_daily_doi

12 Month Device Brand Daily Snapshot

Field Name Description Data Type Required Nullable
Device_Brand Name of Device String No Yes
Month_year Observation Month & Year Header No Yes
Device_count Numbers of Device Integer No Yes
Percent_month Percent of the total amount Integer No Yes
Post_stamp Time of observation Date_stamp No Yes

Data Source: Google Analytics

Script Location: https://github.com/govau/GAlileo/blob/master/dags/bq_scripts_project_ursa_major/dta_sql_devicebrand_snapshot_12months_daily_doi

12 Month Device Category Daily Snapshot

Field Name Description Data Type Required Nullable
Device_Category Category of Device String No Yes
Month_year Observation Month & Year Header No Yes
Device_Category_count Numbers of each Device Category Integer No Yes
Percent_month Percent of the total amount Integer No Yes
Post_stamp Time of observation Date_stamp No Yes

Data Source: Google Analytics

Script Location: https://github.com/govau/GAlileo/blob/master/dags/bq_scripts_project_ursa_major/dta_sql_devicecategory_snapshot_12months_daily_doi

12 Month Operating System Daily Snapshot

Field Name Description Data Type Required Nullable
Device_opsys Category of Device operating system String No Yes
Month_year Observation Month & Year Header No Yes
Device_Opsys_count Numbers of each Device operating system Integer No Yes
Percent_month Percent of the total amount Integer No Yes
Post_stamp Time of observation Date_stamp No Yes

Data Source: Google Analytics

Script Location: https://github.com/govau/GAlileo/blob/master/dags/bq_scripts_project_ursa_major/dta_sql_opsystem_snapshot_12months_daily_doi

12 Month Operating System Version Daily Snapshot

Field Name Description Data Type Required Nullable
Device_opsys_ver Category of Device operating system String No Yes
Month_year Observation Month & Year Header No Yes
Device_Opsys_ver_count Numbers of each Device operating system Integer No Yes
Percent_month Percent of the total amount Integer No Yes
Post_stamp Time of observation Date_stamp No Yes

Data Source: Google Analytics

Script Location: https://github.com/govau/GAlileo/blob/master/dags/bq_scripts_project_ursa_major/dta_sql_opsystem_ver_snapshot_12months_daily_doi

12 Month Screen Resolution Daily Snapshot

Field Name Description Data Type Required Nullable
Device_screen_res Category of Device screen resolution String No Yes
Month_year Observation Month & Year Header No Yes
Screen_res_count Numbers of each screen_resolution Integer No Yes
Percent_month Percent of the total amount Integer No Yes
Post_stamp Time of observation Date_stamp No Yes

Data Source: Google Analytics

Script Location: https://github.com/govau/GAlileo/blob/master/dags/bq_scripts_project_ursa_major/dta_sql_screen_res_snapshot_12months_daily_doi

90 Day Unique Visitors Daily Snapshot

Field Name Description Data Type Required Nullable
Total_visitors total visitors String No Yes
Unique_visitors Approximate unique visitors String No Yes
Posted_timestamp Time of observation Date_stamp No Yes
Visit_date Time of observation Integer No Yes

Data Source: Google Analytics

Script Location:

https://github.com/govau/GAlileo/blob/master/dags/bq_scripts_project_ursa_major/dta_sql_unique_visitors_snapshot_90days_daily_doi

90 Day Unique Visitors Hourly Split Week Snapshot

Field Name Description Data Type Required Nullable
Total_visitors Total visitors by domain Integer No Yes
Unique_visitors unique visitors by domain Integer No Yes
Unique_visitors_approx Aggregate total visitors Integer No Yes
Visit_hour Hour of observation Integer No Yes
Visit_date Day of observation Character No Yes
Posted_timestamp Time of observation Date_stamp No Yes
Data Source: Google Analytics

Script Location:

https://github.com/govau/GAlileo/blob/master/dags/bq_scripts_project_ursa_major/dta_sql_unique_visitors_snapshot_90days_daily_doi

Website Count

Field Name Description Data Type Required Nullable
Registered_domain_count Total number of websites Integer No Yes
Agency_count Text Total number of Agencies No Yes
Post_stamp Date of check Time Stamp No Yes

Data Source: Google Analytics

Script Location: https://github.com/govau/GAlileo/blob/master/dags/bq_scripts_project_ursa_major/dta_sql_websites_count_doi

Clone this wiki locally