Split Google Analytics Campaign and Keyword Fields on the Fly in Columns

Having campaign names and keywords like DE_DE_foo_bar_11112013_baz, you can easily expand them to columns on the fly, even when they are not complete or are null. 
The following example also includes the duplicate removal for transaction ids, taking the one with the first date and time:
select
tr.*, trc.*, trk.*
from
(
    select transactionId, tdate, thour, source, medium, campaign, keyword, transactions
    from
        (
            SELECT row_number() over ( partition by transactionId order by tdate asc, thour asc ) rnum,
                transactionId, tdate, thour, source, medium, campaign, keyword, transactions
            FROM views.ga_transactions
        ) a where a.rnum=1
) tr,
texttable (
    tr.campaign
    COLUMNS
    "country" string,
    "language" string,
    "audience" string,
    "age" string,
    "startdate" string,
    "partner" string
    DELIMITER'_'
) trc,
texttable
(
   tr.keyword
   COLUMNS
   "country_keyword" string,
   "language_keyword" string,
   "audience_keyword" string,
   "age_keyword" string,
   "startdate_keyword" string,
   "partner_keyword" string DELIMITER '_'
)
trk

Articles in this section

Comments

Articles in this section

Related articles