Skip to content

Instantly share code, notes, and snippets.

Created May 20, 2016 18:36
Show Gist options
  • Save randyzwitch/7a9c48e7132e6ed9dfb0d02ec906961c to your computer and use it in GitHub Desktop.
Save randyzwitch/7a9c48e7132e6ed9dfb0d02ec906961c to your computer and use it in GitHub Desktop.
Adobe Analytics Data Feed - Useful Columns for My Purposes
create view usefuldata as
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