5 Replies Latest reply on Jun 12, 2012 12:11 PM by Tracy Rodgers

# Need help with a filter formula

I have a dimension that comes into Tableau like this: /?source=zyx&utm_medium=google&utm_term=keyword&utm_campaign=campaign.

I need to be able to split that out so that I can create segments based on medium =, term = and campaign =

Can anyone give me any pointers on how writing a formula to accomplish this assuming that's the best way to accomplish this (if you have another idea let me know).

Additionally, I won't always know what mediums, campaigns and keywords will be coming through. How would the formula work for that?

Finally is it possible to create three new dimensions (medium, campaign and keyword)? If so how?

Any/all help is appreciated.

Thanks!

• ###### 1. Re: Need help with a filter formula

Hi Matt,

First, create a couple of calculations to find the points in the URL. Similar to the following:

source:  find([URL field], 'source=')

campaign:  find([URL field], 'campaign=')

medium:  find([URL field], 'medium=')

term:  find([URL field], 'term=')

Then, from here, create a number of a calculated fields to determine the correct Medium, Campaign, Term and Source similar to the following:

Source:

mid([URL field], [source]+7, [medium]-[source]-7 )

Medium:

mid([URL field], [medium]+7, [term]-[medium]-7)

Campaign:

mid([URL field], [campaign]+9)

Hope this is what you were looking for!

-Tracy

• ###### 2. Re: Need help with a filter formula

Hey Tracy,

Thanks for taking a stab at this, however I'm still a bit unclear. Is step 1, finding the location of the words in the URL, separate from step 2 or is it(could it be) one big formula? If so, I know how to create calculated fields but is finding the location (step 1) also it's own calculated field - if not what is that classified as (where do I do it)

Thanks!

• ###### 3. Re: Need help with a filter formula

Hi Matt,

The first step is comprised of three different calculations, although these could be used in each of the three calculations in the second step (sometimes it's easier for me to do it step by step to see and understand it better). With the two combined it would look similar to this;

mid([URL field], (find([URL field], 'source='))+7, (find([URL field], 'medium='))-(find([URL field], 'source='))-7 )

I hope this is what you're asking!

-Tracy

1 of 1 people found this helpful
• ###### 4. Re: Need help with a filter formula

Thanks Tracy, that worked with some tweaking of the - 7 to a minus 12. Excuse my ignorance but how did you choose the number 7 and is there another way to determine write this formula without guessing as to the size. Let's say in another case that I don't know the size of the string I'm trying to isolate.

• ###### 5. Re: Need help with a filter formula

Hi Matt,

Sorry, I should've explained that piece. 7 was chosen because that how many characters are in "source=" which is why for the campaign calculation 9 was chosen. This way it won't be guess work, it will just depend on what is in the string that you're looking for. I hope that makes more sense!

-Tracy

1 of 1 people found this helpful