A walk-thru of marketing analysis with Excel using the Index Match functions for up to date and dynamic data across cell ranges.

“[The files] they’re *in* the computer?” – Hansel in Zoolander

Excel is a wonderful marketing analysis tool and is one of those universal tools to work with data. When I was working for an agency with multiple clients we struggled to find an affordable business intelligence solution for an agency with multiple clients. Everything kicked off in Excel.

My favorite tool in Excel (as of right now) is the Index Match function. Actually it uses both the Index and the Match functions together to make a range of data dynamically update. There are multiple use cases but I will walk you through how I was taught and still use this function today:

EXCEL:

Unless you already have data to work with I would set up some sample data to play with and see if you can get this function to work for you before throwing it into official sheets. The function works by setting a code found using the index function, gets the position by the match function, and always returns the last cell in the range.

Disclaimer, I use this function to return data only, not words/phrases/etc.

INDEX:

For the Index function we will be using the first block to indicate the Row number (ROW#) as pictured above. The Row should correspond to the row where your data range exists, from left to right. Each ROW# should be the same row number (ex: Row 20 is 20:20).

Traditionally, you would use the first block to indicate the column where your data exists, and where you will be returning a final value from.

MATCH:

The second block in the Index function will be used to specific the updating data in the range, or the last cell with data inside it, from left to right. You have the large number, and then the same Row you used before, exactly as you used it before in the second block of the Match.

Traditionally, you would use the final block to indicate the column/row you want to look up against.

The Large Number:

The large number (9.999999E) must be there, and as I am not an excel specialist, I can only say that it must be large to correctly narrow to the correct numerical data. It represents the lookup value but in our usage it reflects the numerical range. I believe it could be replaced in order to narrow to words/phrases if desired.

The image above is a sample of one of the Index Match functions I am currently using in my Analysis Sheets. I will show you exactly how I am using this function now that you know how it works. Remember I am using this for number data, not words, so test your version accordingly.

The C highlight references Column I, or the final range of data included in the function. Only the furthest right data will be referenced, in this range, which would be C, the value is 537. The bottom 2/3rds of my sheet represents the Deep Dive section – where all the micro data lives, period after period.

D:

The D highlight references the top 1/3rd and macro data view of the data in the Deep Dive. At the top, for easy access is what is used most by Management or when sharing the sheet. The data marketers care about is in the deep dive – it’s not pretty down there but it’s where we see opportunities.

The Index Match function dynamically pulls the most recent (furthest right) data in the sheet and displays it in the top area automatically. This helps us to limit how much data we are inputting and manually updating. There are ways to automate even that to some extent.

Kevin Dieny

Marketing Professional

All in all I use these tools to maximize the most important work that I do and minimize the tedious and unimportant work. Automation is vital for the mundane tasks. Once you have sheets set up you can jump into the optimization.

I’ve figured out how to install and troubleshoot Google Tag Manager in WordPress after countless hours of problems, mistakes, and misfires.

“Poor unfortunate souls.” – Ursula

Working at a Marketing Agency means that you will often get tasks and jobs that seem far above you or way beneath you. I had come to assume that installing a UTM Tracker like Google Tag Manager was pretty far underneath my skillset. I even scoffed that anyone could do it – so why should I?

I confess, I was dead wrong. I had multiple sites working in tandem across domains and subdomains seamlessly carrying attribution from one to the next until it arrived in the CRM. Then my analytics stalled and data started coming in erratically.

The answer was, my Google Tag Manager Script wasn’t working. I am here to help you learn how to install this wonderful utility in WordPress, and even to troubleshoot the basics. I will start with the simple steps and work my way into the complex.

Basic Installation:

First and foremost, installing Google Tag Manager requires you have created an account there with Google, you have a WordPress site running, and you have access to the editor files. I also want to throw this disclaimer out there that installations can vary depending on themes… what works for some won’t for others because some themes and WordPress installations are more custom.

The Google Tag Manager script/code is broken into two parts the header code, and the body code. Within GTM you can view the code by navigating to the appropriate Property (will list the name and the GTM-###### at the top so verify it’s the correct one you want) then going to Admin > Install GTM.

In another tab or window you need to login and navigate to your Admin Dashboard of the WordPress site you want to install GTM on. On the left there is a Paintbrush or Appearance > Editor. Once the editor fires up you should see the theme you are currently using selected in the top right. Click the “Theme Header” or “Header” (header.php) from the right sidebar.

Even if you do not understand PHP or HTML, you can do this without blowing everything up – however, I do recommend that you create a backup before you go further (if you do not know how – learn, I will provide insight on this at a later time sorry). A lot of web designers I’ve worked with have recommended loading up the header in Dreamweaver or other program instead of within the WordPress editor.

Start at the top of the window and start scrolling down with your eyes… you should be able to find the word “head” between two brackets <, >. You can move your cursor after the last bracket and click enter two or three times to make some white space for yourself. Copy the Header text here.

Finally, scroll down till you see the header end usually denoted with a backslash “/” and the word “head” which should be followed by the word body within two brackets, just as the header was. There might also be php code with the words “body_class” here, but you can ignore it.

Make some white space again by clicking on the line below the body tag and making room to paste. Make sure you do not cut off any code and that you are careful to make space without breaking code… you will know if your site blows up and you have to reinstall the backup.

Paste your body code here. Now verify everything looks good and if you are sure, have everything backed up… then click the “Update File” button beneath the window to save.

Testing:

If your site works, you have the script added to your files/theme, then you are ready to test. The easiest and most simple way to test is to tab over to your Google Tag Manager account. Verify you are logged into the correct property, then using the dropdown in the top right, click “PREVIEW” (Preview and Debug).

The window will reload and an orange colored banner should appear in your Google Tag Manager saying “Now Previewing Workspace.” Great, now tab back to your website and load up your home page/domain where you installed Google Tag Manager.

Once your website it loaded you will know if Google Tag Manager is working there because an overlay will appear at the bottom of the page. You should see the blue icon or the whole window appear with the name at the top “Google Tag Manager.”

Another way to test if pixels are firing is to load up the extensions. There are extensions for several of the pixels, the big ones: Google and Facebook, have their own pixel/testing extensions which can help you verify if things loaded and what was happening on a more granular level.

Troubleshooting:

Case #1 – The Invalid or Missing Account ID

The solution to the invalid or missing account ID issue is the easiest one I know – you don’t do anything. Google’s a little behind on having added GTM ID’s with additional digits, and if you are new to the GTM scene then you likely have a longer than usual ID number and you will get this error. Do not fret, it’s all okay, and Google has said they will update this in a future fix.

Case #2 – The Theme

What I figured out after losing a few hours to stumbling around the internet looking for solutions to my problem I ended up discovering it was a theme issue. In fact, my Tags were loading, just not reliably and I couldn’t get the Preview overlay to come up. I knew there was an issue and I didn’t want to risk inaccurate data.

I tried updating the theme, it was updated. I tried manually configuring the code in so many ways but couldn’t get it to work. Some of you may use themes like I have where they have a built in area where you can add your tracking codes. These have always worked for me but not this time.

Even when adding the script into the header.php file I was still not able to get my preview overlay to load up. I was seeing that multiple analytics codes were firing and some were not. Oddly the fix for me came with a plugin. I tried everything and eventually tried a 3rd-party plugin.

I searched for a WordPress plugin that could deploy Google Tag Manager, and settled on DuracellTomi’s Google Tag Manager for WordPress. This plugin saved me when everything I was doing did not. The lesson here of course is to monitor your Analytics and watch for errors routinely to make sure your data is solid.

Case #3 – Wrong Property

I mentioned this throughout my installation guide but make sure that you are working within the correct property in Google Tag Manager. It is so easy to be in the wrong account. Check it and make sure before you go poking around.

Case #4 – Tags Not Firing

I cannot go too deeply into specific Google Tag Manager errors but this one comes up a lot so I am keeping it in here. A tag relies on the installation of Google Tag Manager to be correct and stable within the environment of WordPress and the Theme in which it is installed.

If everything is working besides a tag firing then the best place to start is the trigger. If you have recently moved over to HTTPS, that could be an issue. If you have the trigger even slightly off you can pinpoint the issue by CTRL + Clicking on the elements with the preview overlay on, check the log, and see if you had the right trigger. Don’t forget to publish your changes – they take affect only then.

Tags not firing can be so broad and complex that if you are still struggling I recommend having a web developer or coder look it over for you, it’s worth not wasting days of your time.

Kevin Dieny

Marketing Professional

I hope that you find this guide useful. It is accurate up to today. Let me know in the comments if you have suggestions for me to add or write about in more detail.

Use this five point checklist to ensure that you have Adwords Search set up to maximize the overall marketing plan of your company.

“Everything should be made as simple as possible, but not simpler.” – Albert Einstein

The five point setup for Google Adwords helps evaluate if your marketing plan is focused. Focus is a big part of segmentation for marketing because we only want to target and produce content for segmented audiences to maximize their engagement.

You want to use this checklist to make sure that each of the overarching goals of your Adword Search Campaigns lines up with the funnel. This list can be used with any industry that uses Adwords Search and curates interest using a blog. If you aren’t doing this yet – start a blog and produce weekly content.

1st:

Every product, every service, is unique and requires a specific strategy. Even similar products are different when offered by two different brands. You want to promote your product and brand simultaneously because you are persuading people to trust you and to part with their hard earned money.

Select a single product or service, be niche, be specific, and put yourself in your customer’s shoes.

2nd:

The content you create on your blog needs to be curated towards your best customers. You want to target a specific customer and deliver the best experience for them. Your blog should be providing resources and answering questions that crop up over time. The best content is specific and helpful.

3rd:

For a lot of industries their products and services sell because of their amazing blog and content. For the rest of us we need to pixel and capture as much information about our visitors as possible. By pixeling and then segmenting your blog you will be able to target them again on Facebook and from Google retargeting.

4th:

Finally, we move into Adwords. A lot goes into making sure that Adwords Search Campaigns are successful. It’s not easy to make positive returns on Adwords because your competition is likely paying rooms of professional SEO and Adwords experts to outbid and outperform your attempts. This is why it is crucial to have a content engine and set yourself apart from the crowd.

Keywords are where we are focused. You want to target highly relevant strings of words, the more the better, that are specific to your blog and your product/service. You want to avoid being too broad – you need to be exact. In fact, I would only start out with broad match or broad match (+) if you don’t have any idea of what people are searching related to your keywords. Otherwise, stick with phrase and exact matches.

Create Ad Sets that correspond to those keyword types, so one Ad Set that is “Phrase” and one that is [Exact]. You will set the bid about 20% higher for the Exact Ad Set so you stress higher preference for an exact match query.

Manage your negative keywords and keep an ongoing reference list as you track your search terms to manage what is getting in the way or throwing your searches off. Using all of this you should be able to ensure you are getting a higher CTR. It’s not about impressions or clicks – it’s about the click thru rate and the beginning/exit conversion rates.

5th:

Last but not least you need to test. Multivariate testing or (A/B Testing) ensures that your Adwords Search is successful and constantly optimizing. You will not get it right the first time! You need to be tweaking your Ads with testing weekly. Look at your headlines and below the fold text for areas you think could be rewritten, and test it.

The optimal way to test is to test only one element at a time. Try to stick with testing only a few elements and give it time. I would suggest a week or at least a hundred impressions (more is way better) before you resolve a test.

Kevin Dieny

Marketing Professional

To conclude there are a lot of other ways to maximize your adwords search campaigns but these 5 steps will make sure that you have the macro goals focused. You need to get the basics right every single time no exception. I will post again with advanced and expert strategies of setting up adwords campaigns at a later time.

One of the most effective steps you can take as a marketer right now is to start tracking the data of your most important key performance indicators.

“Prove it to me, and you’ll get the budget.” – An old boss

Digital marketing analysis can be broken down for beginners and the advanced but only if we are talking about the key performance indicators. If you want insight you need data and not all data is the same you need the right metrics.

Let’s talk about a few of the most important metrics for each stage of the funnel and why these might be key performance metrics for your own business. My goal here is to give you the tools you need to get started and figure this out on your own and apply it to your own situation.

VISITS

Visits is one of the most important metrics because it is the knock at the door, the first glance, and the moment that your brand makes first contact and brings them back for more. You want to track these because they represent the beginning of the funnel and come in handy for optimizing traffic.

Tip: Not all visitors are the same – unique visitors are not the same as sessions or page views.

LEADS

Leads are all too common a metric that is lumped into one fat category. Leads are vital but my suggestion is that not all leads are created equal – they must be segmented as early as possible. If your leads came from newsletters, products, or opt-ins then tag or segment them as such.

Tip: Attribute your leads using UTM parameters and hidden fields in forms as often as possible.

ROI

The final metric is usually one reserved for the CFO or the CEO – it’s also one that winds up sucking the life out of the directors and managers at the end of a month or quarter. The ROI can be tracked weekly, daily, and monthly, however you like – but you must track it. Learn how and add it.

Tip: I would recommend that you stick to tracking the return on investment from marketing activities only and then adding it to the overall accounting picture at the end.

The top priority skills that every person in marketing should be working on and improving to make themselves more valuable.

“He who opens a school door, closes a prison,” – Victor Hugo

From my experience there are five essential skills in the industry that can have the greatest impact on your overall value. I built this list from first-hand experience and from the opinions of recruiters. If you have an industry; find that industry version of the following skills.

Not in any specific order:

Give back, share what you know, do the part of the job you love and find sell your services, and also try new things.

Email Standards

Email deliverability is an overlooked asset – you need to be optimizing your emails with every send and deep dive into the smallest metrics, because they matter!

Code Writing

Prioritized languages from recruiters and my own experience are as follows: JavaScript, HTML/CSS, and SQL. I would also consider looking into API based languages such as Ruby or PHP to get further niche.

Customer Research

Humble yourself and realize that you may have a very clear idea of your target customer but there will always be more you can learn. Continually refine your customer and adapt your marketing to fit.

Kevin Dieny

Marketing Professional

These skills should help any marketer advance themselves – but it’s important to understand these are not a place anyone should start to learn marketing. Consider these advanced tips to keep marketers on the right path.