Extracting dates from inconsistent URL strings
Louis Nicholson Mar 15, 2017 5:50 AMHi,
I'm working on building different views and Filters for Search URLs and Page Views (it's Google Analytics Page Data).
Attached is a packaged workbook which shows the type of URLs I'm working with (where I pull in everything starting with "search"), and what I've built so far (I've built a Filter for Destination and one for Date - which picks up "main_date:" in the URL and I've built a Calculated Field which references what to show it as).
But what I really want to do is build a Filter which also picks up "date:during|" in the URL and then extracts any of the dates immediately after.
The difference between "main_date:" and "date:during|" is that main date picks up YYYY-MM in the same format (so it's easy to predict and account for), but "date:during|" picks up YYYY-MM-DD (not always in a double digit format for Days).
There are several problems with this URL data:
1) "date:during|" does not appear in every URL and in some, appears multiple times (the multiple options reflect multiple choices for departure dates - i.e. "main_date" is just one YYYY-MM but "date:during|" can return multiple records
2) The formatting of this URL data is not always consistent
3) The positioning of "date:during|" changes between URLs - dependent upon other Search URLs/configurations
What I want to achieve is to pull out any instances of "date:during|" and then extract the dates immediately thereafter (but just the dates and nothing else). For multiple entries of "date:during|" I'd be happy to count each instance (i.e. for the same URL, each "date:during|" can equate one Page View).
Once I've got these dates and the number of times search combinations occurred, I could set up some kind of visualisation which shows search patterns for each website and market etc. etc.
Does anyone have any thoughts on how to go about building the Calculated Fields for this?
I'd usually be more comfortable cleaning the URL data and building in Excel, but this is something I need to automate and so ideally, Tableau will be the way forward.
Many thanks
-
Search By Date.twbx 957.4 KB