Blog

The following Excel trick is best used when you have data located inside of your URLs. This happens a lot when you have an ecommerce website that generates dynamic confirmation URLs after a purchase is made.

In one particular case, one of our clients reached out to us hoping to find a PPC management company. He had a unique situation: he couldn’t afford to take his ecommerce store and migrate it to a modern CMS or even redesign it, but wanted to still have the ability to provide ecommerce data from Google Analytics. The problem was that installing ecommerce tracking within Google Analytics was going to require hundreds of hours of programming time, and he simply could not afford it. We were able to use our data-driven marketing techniques by using the following formula.

Pulling ecommerce transaction data from dynamically generated transaction URLs

In dealing with many ecommerce SEO and PPC campaigns, often times I have to pull transaction data from dynamically generated URLs. I use a combination of MID and IF to pull data like transaction values or any other transaction values that may be pulled from a list of confirmation URLS as shown in the example below. You can also use this data to pull things like category IDs, product IDs and anything else your dynamically generated URL contains by making a few tweaks to the function.

The MID formula is used to remove unwanted words or data before or after the data you are interested in is extracted from the specified cell. The formula by default looks like this: = MID (Text , Start_num , Num_chars)

But in our example the “text” field included a nested FIND function, which means the formula will have to initiate the find function according to its specifications. In this particular case, the FIND function was created to search for the parameter within the URL that precedes the subtotal value of the transaction. Find functions by default look like this:

=FIND(find_text, within_text, [start_num])

So, all we are doing is filling in the fields “find_text,” which is again, “sub_total=.” Then, specify where to find the text (A2), which gives us exactly what we need for the MID function to work harmoniously with the FIND function.

If you look at the formula in the first screenshot, the text parameter in the nested FIND function is labeled “sub_total=” because that is the term used in each URL right before the subtotal amount is displayed (as shown above).

With that set of instructions, the formula looks in A2, then finds the value “sub_total=.” Once the “sub_total=” text is found the function is then instructed to pull the data according to the Start_Num, which is the placement of the character that you want to begin pulling data from. In other words, starting from the first letter in “sub_total=,” Excel needs to know at which character to start extracting data from. Since “sub_total=” contains nine characters, we instructed the formula to start at the 10th character and to include the following five characters. Hence, the formula “=MID(A2,FIND("sub_total=",A2)+10,5)."

The outcome is the five values shown after “sub_total=,” which happens to be $83.95. Once the formula is applied to one cell, you can double click the little square on the bottom right hand corner of that cell and it will apply the formula to each row, pulling the unique subtotal from each cell. You can also copy or move formulas in Excel to quickly organize or re-arrange your data by copying or cutting the formula as described here.

How to organize transaction data from dynamic URLs with Excel

This particular formula is best used for sites that do not have the proper ecommerce tracking enabled with Google Analytics. Sometimes, shopping site owners don’t have the funds to redesign their site. They end up stuck with an archaic shopping site that makes it very difficult to install ecommerce tracking with Google Analytics. This is a solution that can be used to pull ecommerce data with a simple Excel formula. Whether you own a shopping site or you are a PPC management company that offers marketing services, having these kinds of formulas and knowing how to use them can save you time and help you manage your data better.

After you become familiar with using this type of MID formula, I recommend applying secondary dimensions such as "medium" or "campaign" to see a more comprehensive report that shows which mediums or campaigns are generating revenue. If you apply a secondary dimension of "landing page" or "keyword" before exporting your page report that contains the transaction URLs, you can also get great insights as to which keywords or landing pages are performing better than others. Below I have created a report using landing page as the secondary dimension, so we can see what a report with landing pages added would look like.

Conclusion

All in all, this a great Excel tip that you can add to your arsenal as a way to make yourself a more lean, mean and efficient search engine optimizing machine. What you do next with the Excel tip I just shared is up to you.

You will likely find you can use this type of formula to do many other things. In the above screenshot, I organized the data using a table; you might also find it helpful to use Pivot tables instead. I wrote a blog post on how to use pivot tables to organize your analytics data titled, “SEO Keyword Research Tips for Excel." I encourage you to share your experiences in using this formula in the comments section!

Innovative SEO services

SEO is a patience game; no secret there. We`ll work with you to develop a Search strategy focused on producing increased traffic rankings in as early as 3-months.

A proven Allinclusive. SEO services for measuring, executing, and optimizing for Search Engine success. We say what we do and do what we say.

Our company as Semrush Agency Partner has designed a search engine optimization service that is both ethical and result-driven. We use the latest tools, strategies, and trends to help you move up in the search engines for the right keywords to get noticed by the right audience.

Today, you can schedule a Discovery call with us about your company needs.

Source: