I frequently use Excel to split stings of text, which comes in particular handy for URLs. It’s not always the best tool for the job, but it’s quick and dirty; good enough for us analysts. This guide is designed to help navigate Excel’s text functions in the context of URLs, starting with the Text to columns function.
Text to Columns
Typically used to split one text text string into multiple cells based on commas and tabs, this function can also split up a URL based on any character. Want to remove the pesky query strings from the end of your URLs to effectively ‘dedupe’ them? Copy the list of URLs and paste into the adjacent column (you’ll now have both columns A and B with the full URL).
Text to Columns for URL Parsing 1
Then, highlight column B and go into the data menu, Text to Columns. Choose ‘Delimited’ (fixed width is mostly worthless).
Text to columns for URL Parsing 2
Text to Columns for URL Parsing 3
When asked which character to separate on , uncheck the default (tab), and check ‘Other’, entering ? into the text box.
Text to Columns for URL Parsing 4
You’ll now have the full URL in Column A, the ‘canonical’ URL in column B (ie, without the query string), and the query string itself in column C. Next step would be to run a Pivot Table on the whole set, using the Canonical URL to summarize stats across variations of the page.
Keep in mind that some metrics summarize better than other metrics. For example, you’re able to add the number of pageviews to www.mysite.com/ and www.mysite.com/?q=junk, but you’re not able to sum visits (because a visitor may have seen both during a session, in which case we’d be double counting) and UVs to these two pages.
Related posts: