-
-
Save randyzwitch/7a9c48e7132e6ed9dfb0d02ec906961c to your computer and use it in GitHub Desktop.
Adobe Analytics Data Feed - Useful Columns for My Purposes
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
create view usefuldata as | |
select | |
s."V1" as accept_language, | |
s."V2" as browser_id, | |
s."V3" as connection_type_id, | |
s."V4" as country, | |
CASE WHEN length(s."V5") = 0 THEN NULL ELSE s."V5"::timestamp END as date_time, | |
s."V6" as domain_, | |
s."V7" as evar1, | |
s."V8" as evar2, | |
CASE WHEN length(s."V82") = 0 THEN NULL ELSE s."V82"::numeric END as exclude_hit, | |
s."V84" as first_hit_page_url, | |
s."V85" as first_hit_referrer, | |
CASE WHEN length(s."V86") = 0 THEN NULL ELSE to_timestamp(s."V86"::numeric) END as first_hit_time_gmt, | |
s."V87" as geo_city, | |
s."V88" as geo_country, | |
s."V89" as geo_dma, | |
s."V90" as geo_region, | |
s."V91" as geo_zip, | |
s."V92" as ip, | |
s."V93" as javascript, | |
s."V94" as language_, | |
CASE WHEN s."V95" = '0' or length(s."V95") = 0 THEN NULL ELSE to_timestamp(s."V95"::numeric) END as last_hit_time_gmt, | |
CASE WHEN length(s."V98") = 0 THEN NULL ELSE s."V98"::boolean END as new_visit, | |
s."V99" as os, | |
CASE WHEN length(s."V100") = 0 THEN NULL ELSE s."V100"::numeric END as post_browser_height, | |
CASE WHEN length(s."V101") = 0 THEN NULL ELSE s."V101"::numeric END as post_browser_width, | |
s."V104" as post_cookies, | |
CASE WHEN length(s."V106") = 0 THEN NULL ELSE to_timestamp(s."V106"::numeric) END as post_cust_hit_time_gmt, | |
s."V107" as post_evar1, | |
s."V108" as post_evar2, | |
s."V182" as post_event_list, | |
s."V188" as post_keywords, | |
s."V189" as post_page_event, | |
s."V193" as post_pagename, | |
s."V196" as post_page_url, | |
s."V197" as post_persistent_cookie, | |
s."V199" as post_prop1, | |
s."V200" as post_prop2, | |
s."V201" as post_prop3, | |
s."V202" as post_prop4, | |
s."V203" as post_prop5, | |
s."V204" as post_prop6, | |
s."V205" as post_prop7, | |
s."V206" as post_prop8, | |
s."V207" as post_prop9, | |
s."V208" as post_prop10, | |
s."V209" as post_prop11, | |
s."V210" as post_prop12, | |
s."V211" as post_prop13, | |
s."V275" as post_referrer, | |
s."V276" as post_search_engine, | |
s."V278" as post_visid_high, | |
s."V279" as post_visid_low, | |
s."V280" as post_zip, | |
s."V281" as prev_page, | |
s."V282" as ref_domain, | |
s."V283" as ref_type, | |
s."V284" as resolution, | |
s."V285" as service, | |
s."V286" as user_agent, | |
s."V287" as visit_keywords, | |
s."V288" as visit_num, | |
s."V289" as visit_page_num, | |
s."V290" as visit_referrer, | |
s."V291" as visit_search_engine, | |
s."V293" as visit_start_page_url, | |
CASE WHEN length(s."V294") = 0 then NULL ELSE to_timestamp(s."V294"::numeric) END as visit_start_time_gmt | |
from servercalls as s; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment