Good afternoon everyone,
I've been monitoring the community feeds and have looked at multiple discussions on this subject but have not yet seemed to have found an answer to my own problem.
Among the kinds of data that I am tasked with creating reporting for is Web Sessions by Source & Channel. For example, Google|Organic or Bing|CPC, We have tools that I've written outside of Qlik that can process this data, but I'm hoping to re-invent the logic within the Data Load Editor of Qliksense.
IF I was to use SQL to do this kind of processing, I would use the following:
Source =
CASE
WHEN UrlReferrer like '%www.local.com%' THEN 'OTHER'
WHEN UrlQuery like '%gclid=%' OR UrlReferrer like '%google%' THEN 'GGL'
WHEN UrlQuery like '%utm_source=bing%' OR (UrlReferrer like '%bing.com%' OR UrlReferrer like '%www.msn.com%') THEN 'BING'
WHEN UrlReferrer like '%yahoo.com%' THEN 'YAHOO'
WHEN UrlReferrer like '%$(clienturl)%' THEN 'CORPORATE'
WHEN UrlQuery = '' And UrlReferrer = '' then 'OTHER'
ELSE 'OTHER'
END,
Channel =
CASE
WHEN UrlReferrer like '%www.local.com%' THEN 'REFERRAL'
WHEN UrlQuery like '%gclid=%' or UrlQuery like '%utm_medium%' THEN 'CPC'
WHEN UrlQuery = '' AND (UrlReferrer like '%google.c%' OR UrlReferrer like '%bing.com%' Or UrlReferrer like '%yahoo.com%' OR UrlReferrer like '%www.msn.com%') THEN 'ORG'
WHEN UrlQuery = '' and UrlReferrer = '' THEN 'DIRECT'
ELSE 'REFERRAL'
END,
However our hope was to drop the table data into QVD's and then process from there. I have attempted to use a combination of IF and Wildmatch, but so far have had no success.
| if(wildmatch(UrlReferrer,'%www.local.com%'),'OTHER', |
| | if(wildmatch(UrlQuery,'%gclid=%'),'GGL', |
if(wildmatch(UrlReferrer,'%google%'),'GGL',
if(wildmatch(UrlQuery,'%utm_source=bing%'),'BING',
if(wildmatch(UrlReferrer,'%bing.com%','%www.msn.com%'),'BING',
| | if(wildmatch(UrlReferrer,'%yahoo.com%'),'YAHOO', |
if(wildmatch(UrlReferrer,'%$(clienturl)}%'),'CORPORATE',
| | if(UrlQuery = '' And UrlReferrer = '','OTHER', |
'OTHER')))))))) as Source,
| if(wildmatch(UrlReferrer,'%www.local.com%'),'REFERRAL', |
| | if(wildmatch(UrlQuery,'%gclid=%','%utm_medium%'),'CPC', |
| | if(UrlQuery = '' AND wildmatch(UrlReferrer,'%google.c%','%bing.com%','%yahoo.com%','%www.msn.com%'),'ORG', |
| | if(UrlQuery = '' AND UrlReferrer = '','DIRECT', |
| 'REFERRAL'))))) as Channel, |
When I run the code I get no errors but I consistently get OTHER and REFERRAL as the results...so it's always falling to the ELSE aspect if the originating IF statement.
URLQuery is a long string, that could look like the following:
utm_source=bing&utm_medium=cpc&utm_campaign=Fort-Worth
UrlReferrer is also a long string that could look like the following (or a thousand other iterations - hence Other):
http://www.google.ca/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=0CEEQFjAD
Anybody have an idea of what I'm doing wrong...or is there a better way of handling the kind of data that I'm looking at?
Thanks!
Tim Norton
www.dacgroup.com/
