Tracking URL’s are special characters added to paid search campaigns, social media campaigns, email campaigns, and pretty much anything that you want to specifically track for marketing purposes. While great for separating out channels by 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 after a special 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 to 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 after a certain 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 special character. As a note, you don’t want any special characters on 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 a special character, you then tell Excel what to exactly look for when they arrive to 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 visualize see that with 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 formula 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 clean part of the URL (under 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 on the left column. This will turn column C into a #VALUE! tag since the 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 into a pivot table. You will see that many pages are now separated by a row. The pivot table will combine all of these duplicates and add up the sessions for them. Here is that view below that sorts the sessions from highest to lowest.
If you have more than just one type of common 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 actually remove everything before the question mark, you will need to use the RIGHT command in excel. To do this, you will basically follow the same steps, but you will need to update the formula to include the RIGHT function. This is the snapshot below on how this will look.