Tracking URL’s are special characters added to paid search campaigns, social media campaigns, email campaigns, and pretty much anything that you want to track for marketing purposes individually. While great for separating out channels from the source, you will quickly realize the nightmare it creates when you want to check your data. In this blog post, I am going to show how to update your excel formula to remove everything from a unique character like a question mark. You want to do this, so you are presented with a clean URL (without the tracking), so you can put everything into a Pivot Table and review the data. Here are the steps below.
Excel Steps to Remove Text After a Specific Character
Here is an example of exporting the top landing pages with the sessions that they drove from Google Analytics. I can see clean URL’s and I can see marketing URL’s that have a question mark associated with them. If I went through this manually, I would spend a lot of time removing parts of the URL to make it clean. Since we want to clean up all of this data before we put it into a pivot table, we will need to use the FIND function in Excel.
Step 1: Create a New Column To Start the Function
The new column needs to go to the right. You can call the new column whatever you want, but I call it “fix” for this example. This new column will be where our function will be created to run the rule to remove everything from a particular character.
Step 2: Create the LEFT and FIND Formula
The LEFT command tells Excel that this rule needs to look to the left to begin the operation. The “A5” part of the formula lets’ Excel know that this is where Excel needs to look to FIND a unique character. As a note, you don’t want any special characters in this section because you don’t want to lock any part of this function.
Step 3: Specify The Special Character
After looking to the left and looking for an individual character, you then tell Excel what to exactly look for when they arrive at column A. Since we are looking for marketing tracking in the URL, I know that this is created dynamically after a question mark. You can visually see that in this list below. So the formula now goes look to the left, looks in column A, and tries and find a question mark. From there, the method reads to then look one over to the right of the question mark and then only keep one left of the question mark. You will see this in the example at the bottom where the formula ran correctly and kept a clean URL.
Step 4: Sort
After the formula has been dropped down to all sections of column A, we need to sort the values. Sorting the values will allow us to then manually move the clear part of the URL (under the column called fix) to the landing page column in column A.
Step 5: Replace all URL’s with the Clean Version
After we’ve sorted all the data in the fixed column, we are going to copy all the changed URL’s and paste them in the left column. This will turn column C into a #VALUE! Tag since then everything has been fixed. The formula is still running, so you can see that there are no more tracking URL’s in column A and everything is ready to go.
Step 6: Put Everything into a Pivot Table
With all the URL’s in a clean version, we need to put everything on a pivot table. You will see that a row now separates many pages. The pivot table will combine all of these duplicates and add up the sessions for them. Here is that view below that sorts the courses from highest to lowest.
If you have more than just one type of standard tread (like if you have internal search pages that are indexed), you will need to repeat these steps but change the rule to include the common thread. This command in excel looks for the character that you tell it to and removes everything after that. If you wanted to do the reverse and remove everything before the question mark, you would need to use the RIGHT command in excel. To do this, you will follow the same steps, but you will need to update the formula to include the RIGHT function. This is the snapshot below of how this will look.