How to Analyze Competing Affiliate Programs | The Blueprint

How to Analyze Competing Affiliate Programs | The Blueprint

If you’re launching or already running an affiliate program, one of your priorities should be to perform a competitive analysis, covering also competing affiliate programs and the activity of the merchants behind them. And I don’t mean just having a general idea of their name, products or services, and position on the market. I mean knowing their every step, their every campaign, their every policy, and their every strategy. Yes, it will take some time, effort, and resources to gather all the data and process it but it will be worth it. In what follows, we’re excited to bring you the complete blueprint for affiliate competitive analysis.

Benefits of Analyzing Competing Affiliate Programs

affiliate marketing focusFollowing your competitors’ activity can help you in numerous ways that go far beyond the affiliate program.

As you engage in gathering competitive data, always remember that the real purpose of this exercise is not to just gather the required data, but to learn (from it) and act (upon your learnings).

Since our focus is on affiliate marketing, you can count on the following benefits:

  • Confirmation that affiliate marketing works for your business model. We know that, done right, affiliate marketing works for almost any business model. This is especially true for these 15 niches. Merchants can benefit from it in at least these 10 ways. You can confirm that by simply searching online for phrases like: [competitor brand] + affiliate program, [competitor brand] + partnerships, etc. In times when more than 81% of merchants and 84% of publishers leverage the power of affiliate marketing, you really shouldn’t have doubts.
  • Informed choice of the affiliate program tracking solution. The decision of whether to run your affiliate program in-house or on an affiliate network should take into account your competitors. If most of them, especially the most successful ones, run their programs on the same affiliate network, it means that the publishers they work with are there too. It doesn’t make sense to start your affiliate program in-house or on another network and require publishers to take additional registration efforts. By joining that network yourself, you’ll have more opportunities to reach out to them and higher chances to convince them to accept.
  • Inspiration for your affiliate program terms and presentation. Just as you compete with other brands for buyers, you compete with other merchants for affiliates. It is important to come up with a unique, attractive offer. You also need to clearly regulate affiliates’ use and promotion of your brand. You can learn a lot about that from your competitors.
  • Inspiration for your future strategies and new opportunities. Some of your competitors do better than others. By monitoring and comparing competing affiliate programs (affiliate commission rates, creatives, deals, and promos, etc.) and their evolution (e.g. network ranking evolution, brand reputation, conversion, EPC, etc.), you can figure out what works and what doesn’t. You can apply or at least test the most promising solutions in your own program.
  • Identify and recruit competitors’ affiliates and other similar publishers. Just as you target the same buyers, you can also target the same affiliates. You just have to find out who those are and reach out to them with a similar or better offer. There are numerous ways to do that, some easier than others. We’ll review the most effective of them in the following lines.

How to Gather the Competitive Intelligence

Gathering competitive dataWe’ve already established how studying competing affiliate programs can benefit you. Now let’s look at ways to do it. You surely have a lot going on and would like to gather as much data as possible with as little effort as possible.

Our very own Geno Prussakov anticipated this need almost a decade ago when he provided solutions in his article for SearchEnginePeople:

  • Joining competitors’ affiliate programs – You will need an affiliate account for that, but the effort of creating it will be worth it. Besides being able to join and analyze various affiliate programs, receive their newsletters, and have access to their statistics and deals, you will also get to see your program through affiliates’ eyes.
  • Befriending and following competitors on social media – It goes without saying that you should use a private, individual account and not your corporate one. Follow their activity to see how they promote themselves and what strategies they use to grow their brand and their affiliate program. Subscribe to their newsletters, RSS feeds, and follow their forums activity.
  • Playing the customer – You want to sell more, and knowing how your competitors’ do it can help. Pretend to be a potential buyer and see what they do to convince you. It could be anything from last-minute deals and discounts to remarketing, abandoned cart recovery campaigns, and more, sometimes through in-house efforts, other times through affiliates.
  • Setting up automatic monitoring – Tools like Google Alerts can help you find out what is being published and by whom about both you and your competitors’ brands and affiliate programs. With SEMRush, you can track everything from keywords usage to competing websites and backlinks.
  • Employing traffic measuring tools – Similar Web’s browser extension is a free and easy to use solution to measure traffic, visitor dynamics, and social media engagement with a single click. For more in-depth analyses, the Alexa tools stack is impressive.

These five solutions alone can provide you with an incredible amount of information. What should you be looking at and how should you use it? We’ll cover that in the following lines.

Data to Monitor in Competing Affiliate Programs

1. Tracking Solution

On which network do most of your competitors run their programs? Do they use in-house tracking software, and, if they do, which provider did they choose? It may be a good idea to follow their lead or at least consider the options they chose when making your decision.

2. Program Name

Obviously, your affiliate program’s name should be based on your brand name but that may not be representative enough for your line of business. You need a name that will tell publishers what you sell and what they’d be promoting. Looking at how your competitors do it can help you come up with it.

3. Program Description

We’ve covered the basics of writing a good affiliate program description here, but turning theory into practice is not always easy. Reading your competitors’ descriptions can help you come up with one even better than theirs.

4. Commission Structure

As explained in our previous post on how to calculate affiliate commission rates, it is very important that you look at the commission structure of competitor affiliate programs. You need to stay competitive without breaking the bank. To do that, take into account performance bonuses and tiered commission increases as well, not just the standards.

5. Average Commission

Sometimes, the commission percentages that your competitors list on their affiliate program page are not accurate. They could be their minimum rate or their maximum one. What you want to know is how much they are actually paying, and looking at their average commission rate will help.

6. Program Rules and Policies

We’ve already covered the importance of having a sound affiliate program agreement in place, what rules and policies the agreement should include, and that merchants need to police affiliates to avoid parasitism and other activities that do not add value.

You can learn a lot about these by looking at what your competitors are doing. What does their PPC policy say about direct linking and bidding on TM and TM+? Do they work with coupon affiliates, prohibit deceitful advertising or promoting codes unavailable through the affiliate program? Maybe you should too. Whatever you decide, make sure to have clear rules in place and enforce them.

7. Competing Affiliate Program Creatives

Your affiliates will need creatives like text links, banners, coupons, discount codes, maybe even videos, apps, and widgets to promote you. Personalized creatives and co-branded landing pages can be very important for affiliates as well. You can learn a lot about what works and what doesn’t from analyzing your competitors’ creative arsenal. It can also serve you as a source of inspiration. Your designer may have an easier time delivering what you need and improving banner conversion if you provide some examples.

8. Affiliate Offerings

Depending on your line of business, you may want to offer your affiliates free samples to review in photos or videos, in-depth presentations of your products or services, lists of keywords to use or avoid, audience details, and more. Looking at what competitor affiliate programs are offering can represent a great starting point for structuring your own offer. Geno’s recommendations on equipping affiliates may come in handy as well.

9. Product Prices

Yes, this information does not necessarily pertain to competing affiliate programs but it could be useful. If your competitors’ products are higher priced than yours, you could leverage the price difference to your advantage. If yours are higher-priced, perhaps you should be looking at offering buyers 0 APR payment options, or find additional ways to justify the price difference.

10. Average Order Value

Besides product prices, another detail you want to be looking at is the average order value. When you notice average order values higher than the average product price, study that competitors’ activity carefully. It means that they or their affiliates are doing a great job at convincing buyers to buy several products at the same time. That’s something you want to learn and apply as well.

11. Conversion Rate

How successful are you at converting website visitors into buyers? Consider looking at your more successful competitors and learning from them. When having to choose which merchants to promote, affiliates will settle for the ones with the highest conversion rates, as they know that, with those, the traffic they can send has better chances of turning into sales.

When you notice high conversion rates, don’t hesitate to dig deeper. Go to your competitor’s website, add products to your shopping cart to receive their abandoned cart email, and see what they do to bring customers back. Look at their popups and overlays, at how they advertise any ongoing promotions. If you don’t have such solutions implemented on your website, you should test them. If you don’t have the resources to do it yourself, you can find a conversion optimization affiliate to do it for you!

12. EPC

Tightly connected to commissions and conversion rate, EPC (earnings per click – measured usually per 100 clicks) is the metric most publishers look at when choosing which affiliate programs to promote. The higher your EPC rate is, the more attractive your program will be to affiliates. Checking the EPC rates of competing affiliate programs will help you identify the ones that perform best and learn from them.

13. Cookie Life

As you already know, cookie life, also known as tracking period, measures the time period you give affiliates to convert clicks into purchases. Statistics show that most purchases occur immediately after the first clicks. The journey from click to purchase can be longer for higher-priced products but that’s not the point of our discussion.

Generally, you want to set a tracking period similar to or longer than your competitors. You won’t be able to do that if you don’t know what they’re offering. Also, most of the time, a cookie life extension will make your affiliate program more attractive to affiliates without actually costing you anything.

14. Offers to Buyers

What products or services do your competitors offer that you don’t? Perhaps you could expand your own inventory and, thus, reach out to a wider audience or increase each customer’s lifetime value. Moreover, knowing that 60% of shoppers look for coupons and discounts online before making a purchase, most merchants started offering them.

If you want to stay competitive, you should at least keep up, and knowing what your competitors are offering is a great way to do that. Besides checking the deals and promos in competing affiliate programs, also check competitor websites and read their newsletters. Look at the discount amount vs. price, promo duration, ways to benefit from the promo (coupon code, automatically at checkout, code by email, etc.), dedicated creatives, etc.

15. Affiliates

Believe it or not, nowadays technology allows you to identify affiliates in competing programs. For example, SEMRush helps you identify the websites linking to your competitors’. Sure, not all of them will be affiliate websites but you can export the list, identify the ones that are, and reach out to them and invite them to promote you as well. Other tools, like Publisher Discovery, help you identify affiliates in competing affiliate programs. The information will not always be accurate or complete but it could be worth gold in the right hands.

Tips on Using Competitive Intelligence Data

Now you know what to look at in competing affiliate programs. Here are a few more tips to help you use that information to your advantage:

  • Track several competitors, the more the merrier. Even if only one seems to represent a threat to your business, others surely target the same audience. Monitoring their affiliate programs as well will provide you a wider perspective and help you steer your own program in the right direction. 
  • Monitor your competitors’ activity at all times, not just when launching your affiliate program. They will adjust their strategies regularly, and it is important that you adjust yours as well. Review changes at least once a month if you cannot do it weekly. 
  • Learn from your competitors’ mistakes. Not everything they do is right, and exploiting their mistakes could be a great way to take the lead. For example, some may announce promotions too late or only send newsletters every couple of months. You can and should do better than that.
  • Don’t take everything you see or read for granted. Some facts or numbers reflect a partial reality that you want to put in a bigger context. Geno did a great job explaining how affiliate program statistics could be misleading you here.
  • Don’t sleep on your success! Just because you’ve managed to learn from your competitors on a couple of occasions and turn things in your favor it doesn’t mean they’ll give up and wait to see all their customers turn to you. You should constantly look for ways to improve your offer to both buyers and affiliates, to gather even more information, and use it more efficiently.

Finally, and more importantly, don’t hesitate to ask for help! Monitoring competing affiliate programs, processing the information, and using it could take a lot of time and resources, especially if you have limited or no experience in affiliate program management and a business to manage. You don’t have to do it alone. At AM Navigator, we’ve helped hundreds of merchants overcome their competitors. Let us help you too by getting in touch and scheduling a free exploratory call!

 

 

How to Analyze Affiliate Data Like a Pro (and Why It’s Important)

How to Analyze Affiliate Data Like a Pro (and Why It’s Important)

How to Analyze Affiliate Data Like a Pro (and Why It’s Important)

Introduction

Wanna know a life-changing, jaw-dropping truth:

Excel is the best thing you’ll ever be able to explore in this god forsaken, barren land we call earth!

Don’t forget that info is power.

This power is the result of a super thorough analytical mindset.

You must know everything.

Every detail is a giant step for you to know more and become better.

The ability to know everything comes through Excel and its many awesome tools for data analysis.

In this article, us Excel experts are gonna explore some of the most crucial data parameters you must bear in mind to succeed.

We’re gonna go through all the relevant details, with a special focus on what’s gonna help you be the king of media buying analysis.

We will refer to the most important Excel tools and show you how to use them for max efficiency.

Ready for a post about the best Excel tips and tricks for affiliates?

Prepared for some awesomely advanced Excel tricks that will optimize your media buying experience?

Let’s begin our journey through affiliate marketing databases and learn some super cool Excel tricks!

The Importance of Affiliate Data

In your life, you’ll be tasked with making a bunch of relevant decisions.

Where should you buy groceries if you want the cheapest supermarket prices?

Which restaurant to pick for a lovely night out with your boo?

How do you deal with all these decisions and their ramifications?

Info.

You always need information to decide.

How do you get this info?

By analyzing data.

man analyzing data

This is why a complete dataset makes all the difference.

Quality data usually means quality decisions.

Why not always?

Because it depends on the way that data is actually analyzed.

Later on, we’ll go through the data-analysis best practices.

For now, though, we’re gonna focus on datasets.

Which question should we ask ourselves?

“Which is the most relevant data to know in the media buying game?”

Ready for a rundown of important affiliate metrics?

Let’s go check them so that you can be ready for the best tips and tricks for Microsoft Excel!

Affiliate Metrics: Data Becomes Real

You know as well as any other affiliate marketer out there that affiliate metrics are remarkably important.

They allow you to measure results and get the data you need to optimize faster like a master!

Ready to understand which are the metrics for affiliate marketing that you need to check to analyze that sweet data?

Clicks

The quantity of clicks you received in a banner.

Visits

The quantity of visits on the prelander or landing page.

Costs

The costs you must pay to generate the number of clicks/visits you have.

It’ll always be a relation between volumes and the costs per click/impressions/visits you have.

Conversions

The number of conversions/subscriptions you get from your traffic.

Revenues

The revenues you’ve gotten from the conversions/subscriptions your traffic has generated.

person with money bills

It’s always a relation between the CPA/payout and the number of conversions.

Margin

What you’ve really earned.

This is your profit.

The revenues minus the costs.

This is the info you need if you want to start.

What?

You wanna dive deeper?

Then we’ll have to start exploring a real special little something:

Affiliate data details.

The Difficulty of Analyzing Data in Affiliate Marketing

You’re analyzing data.

The vibe is right, you seem up for it, it’s all going your way.

But wait!

What’s the main difficulty you experience when analyzing data in affiliate marketing?

There are three main issues that come about.

Let’s check each one in detail, shall we?

Three Data-Related Difficulties

Volumes

Now here’s something that can be super hard.

The quantity of data can make sure that tracking volumes is a complicated endeavor.

If you start by working on a couple of campaigns, then maybe you can follow their evolution.

What if you begin adding campaigns willy-nilly?

Tracking all the volumes is gonna get mighty complex.

The difficulties will start troubling you.

worried man

You’ll have a hard time understanding which campaigns are losing money and where.

What to do?

Follow our advice and check a couple of tools.

Tracking Platform

This is where you can configure the campaigns and receive all the information you need to calibrate optimizations like a master.

You can track conversions made by country, carrier, operating system, device, browser and much more.

Excel

You’ll need to analyze huge databases.

This means you’ll need a tool like Excel to help you.

In this article, we’ll show you how to use it properly for you to really take max advantage.

Details

If you want to succeed in this industry, you need to go into detail.

You must thoroughly analyze each parameter and its many correlations.

You’ll need to know your most profitable campaigns and where you should focus your attention.

Look at the websites and understand which ones are golden.

Check operating systems with a high performance and others which aren’t as well known.

Look for the specific niches in which you can increase your profits.

bills of money falling

Correlations

With so many parameters, it’s only natural that there are many correlations.

One of the most obvious correlations is the one which occurs between the iOS operating system and iPhone/iPad devices.

iOS not showcasing the best results?

It may be due to one of those devices.

There are lots of unexpected correlations.

For example, don’t expect that certain sets of banners will only work for a particular website.

What to do?

Go into detail.

Check different correlations that may appear.

For that, you need a tool that allows for an easy and super fast analysis.

Is it Excel, you ask?

You know it, bro!

How to Analyze Data in Media Buying Campaigns

Do you wanna analyze media buying campaigns like a master?

Then it’s super important to have a great tracking solution or at least to configure the postback correctly.

It’s the only way for you to get relevant and highly-valuable data to analyze.

Let’s say that you have one of these tools implemented.

Then it’s gonna be easier for you to effectively optimize your data using Excel.

Excel allows you to match, configure, relate and organize data.

This will allow you to come up with conclusions.

But how can you analyze a media buying campaign?

Check some clues!

Analyzing Campaigns

Overview

First thing to do?

Get an overall view of your campaigns.

Which ones are losing, which ones are mighty profitable and which ones need your immediate attention.

How to do it?

Just match the campaign’s revenues and the campaign’s costs per day.

You’ll have an idea about how your investment is actually going and what’s the effective impact of your actions.

excel parameters

Look for margin rates.

It’ll help you control your investment and to know when to stop or invest more.

First, go and check the campaign which appears to be losing a whole lotta money.

Then, take quick and swift action.

This will have a major impact on the total margin.

Now’s the moment to go into detail.

Choose a campaign and analyze all the parameters thoroughly.

Check for heavy losses, websites, devices, operating systems, etc.

Banners

We may begin the optimization by looking at banners.

banner parameters

Always have 3 banners.

Why?

Because you should be following the optimal rule of banners.

Two good banners and one that you wanna test.

See the image above?

We believe it’s easy to see that the banner which has the highest CTR is also the one with the lowest eCPA.

It isn’t always the case.

Even so, it’s frequent that there actually is a correlation between both.

What to do?

Stop banner 3 and add a new one to be tested.

Pro tip:

Always remember that banners have no correlation with other parameters!

Websites

websites parameters

Let’s imagine your campaign has a payout of 0.85€.

Looking at the eCPA values, it’s easy to see that SITE 5 is the one you should stop.

Devices VS Operating Systems

devices parameters

operating systems parameters

In these two images, you can see that Android is correlated with several devices.

Let’s suppose that the payout is 0.50€.

It means that – with an eCPA of 0.53€ for Android – you’re losing money.

Should you stop Android?

No.

Why not?

Because of correlations.

Samsung, Samsung tablets and Lenovo are the sources of the bad performance.

Stopping these will make Android profitable and the campaign positive.

By analyzing the correlations, you’ve made your campaign profitable.

There are also positive correlations.

Check your best targets, the correlation between them, and then target those specific ones by creating a new campaign.

Finally, take a look at the prices of your campaign.

campaign prices

On day 6, there’s a decrease of impressions and a CPM increase due to competition.

In this case, it’s clear that someone has gotten your position, therefore decreasing both your volumes and traffic quality.

Since your CPM increased, someone has been able to get a position below you.

The result is an increase of the eCPA, which may turn a profitable campaign into a black hole.

These analyses are fundamental if you want to turn your campaigns into gold mines.

With Excel, you can improve your efficiency and reach the next level.

How to use Excel to Analyze Data in Media Buying Campaigns

Excel is an extremely useful tool for data analysis.

If you don’t want to get involved with programming, then Excel really is both the fastest and easiest tool for you to use.

Anyone can learn how to explore Excel.

It’s just a matter of getting used to the functions and their specific goals.

Why is Excel the belle of the ball?

Here are three good reasons why!

  • Easier to analyze data
  • Great at summarizing in different ways
  • Useful for you to compare building a pivot table report instead of having to go through the process of building the same report manually

Boring, indeed!

Mobile marketing gets bigger, more popular, and more interesting by the minute.

As you get into it, you’ll understand that there are a lot more parameters and variables to be tracked and analyzed.

Through the use of pivot tables, you can perform a successful A/B testing and also split test offers.

There are even some Excel Pivot table tricks you can use to maximize success.

Excel will basically help you create cross tabulations of keywords.

Pivot Tables provide you with a versatile way to handle all those extra columns that you have to deal with in your reports.

Then, all this precious info will be processed through pivot tables in order to optimize your campaigns.

Pivot tables are terrific to analyze data on the fly without building a full program.

This really is one of the best tricks in Microsoft Excel, brother!

You don’t even have to know any Excel formulas.

The name “Pivot Table” somehow makes it sound like it’s some advanced gizmo only reserved for Excel wizards.

It’s actually quite the opposite.

It’s an easy, fast, yet powerful way for marketers who aren’t familiar with Excel to quickly analyze large amounts of data.

Are you someone who believes Excel gives you a general headache?

We both promise this experience is gonna be free of agonizing pain!

Compare building a pivot table report to the process of building the same report manually.

Excel Features You Can Use to Analyze Data

Analyzing your data used to be quite the boring process.

It was a ton of work.

With Excel, though, everything changes for the better.

Indeed, with this amazing tool, it’ll only take a few steps for you to analyze like a pro.

You can instantly create different types of charts, including line and column charts, or add miniature graphs.

You can also apply a table style, create Pivot Tables, quickly insert totals, and apply conditional formatting.

Amazing!

So many cool tricks in Excel, so little time!

Here’s a neat trick we’ll teach you.

Trick – First Part

Select the cells that contain the data you want to analyze.

Trick – Second Part

Click the Quick Analysis button that appears at the bottom right of your selected data (or press CRTL + Q).

quick analysis excel

Which analytical feature should you use?

If you’re not sure which analytical option to pick, here’s a quick overview.

Analytical Tool Overview

Quick Analysis

In the “Quick Analysis” gallery, select the tab you want.

For example, formatting lets you highlight parts of your data by adding things like data bars and colors.

This lets you see high and low values, among other things.

formatting excel

Charts

Excel recommends different charts, based on the type of data you’ve selected.

If you don’t see the chart you want, click on “More Charts”.

charts excel

You might notice that the options you can choose from aren’t always the same.

That’s because the options change based on the type of data you’ve selected in your workbook.

Totals

Totals allow you calculate the numbers in columns and rows.

For example, the “Running Total” inserts a total that grows as you add items to your data.

Click the little arrows on the right and left to see additional options.

totals excel

Tables make it easy for you to filter and sort your data.

tables excel

Sparklines are like tiny graphs that you can show along your data.

They provide you with a quick way to see trends.

sparklines excel

Best Excel Shortcuts for Data Analysis

There are hundreds of Excel shortcuts and tricks for you to explore.

The problem with this is that it can become overwhelming for you to have to know them all by heart.

You can’t possibly absorb 233 new shortcut keys and start using them.

That’s why we’ve got you covered, bro!

The following sections cover some of the best shortcut keys.

Try to incorporate a new shortcut key into your Excel routine every week.

Prepared for a lesson about the best tricks in Excel?

Dive into it!

Excel Shortcuts List

Move Quickly

This is one of the best Excel shortcuts tips and tricks.

Let’s understand how to move quickly among worksheets.

Ctrl + Page Down jumps to the next worksheet.

Ctrl + Page Up jumps to the previous worksheet.

Let’s pretend that your workbook has 12 worksheets named Jan, Feb, Mar, . . . Dec.

If you’re currently on the Jan worksheet, hold down Ctrl and press Page Down five times to move to Jun.

Great, isn’t it?

It really is one of those awesome Excel tricks for you to explore.

Jump to the Bottom

Jump to the bottom of data with Ctrl+Arrow

As long as there are no blank cells in your data, just press Ctrl+Down Arrow to move to the last row in the data set.

Use Ctrl+Up Arrow to move to the first row in the data set.

Quick and easy!

Keep reading for some more useful tricks in Excel.

Current Region

This is one of the most important and useful Excel tips and tricks for beginners.

Select the current region with Ctrl+*

Press Ctrl * to select the current range.

The current range is the whole dataset, in all directions from the current cell until Excel hits the edge of the worksheet or a completely blank row and column.

Let’s say you’ve just selected A2:J987654 but you’re staring at the bottom-right corner of your data.

Press Ctrl + Period to move to the next corner of your data.

Since you’re at the bottom-right corner, it will take two presses of Ctrl + Period to move to the top-left corner.

Even though this moves the active cell, it doesn’t undo your selection.

Popping Open the Right-Click Menu

More useful Excel tricks?

Go for it!

Pop open the right-click menu using Shift + F10

Pressing Shift + F10 opens the right-click menu.

Use the up/down arrow keys to move to various menu choices and the right arrow key to open a fly-out menu.

When you get to the item you want, press Enter to select it.

Time-Stamp

Date-stamp or time-stamp using Ctrl shift ; or Ctrl shift :

Here’s an easy way to remember this shortcut.

What time is it right now?

It’s 11:21 here.

There’s a colon in the time.

Press Ctrl+Colon to enter the current time in the active cell.

Note that this is not the same as using =NOW() or =TODAY().

Those functions change over time.

These shortcuts mark the time or date in which you pressed the key and the value does not change.

Neat, right?

Another one of those splendid tricks for Excel you have to explore.

Last Task Repeat

Repeat the last task with F4.

Let’s say you’ve just selected a cell.

Here’s what happened next:

Home, Delete, Delete Cells, Delete Entire Row, OK.

You need to delete 24 more rows in various spots throughout your data set.

Select a cell in the next row to delete and press F4, which repeats the last command but on the currently selected cell.

Select a cell in the next row to delete and press F4.

Before you know it, all 24 rows will be deleted.

That’s right!

You didn’t have to click on Home, Delete, Delete Cells, Delete Entire Row, OK 24 times.

The F4 key works with 92% of the commands you’ll use.

Try it.

You’ll love it.

It’ll be obvious when you try to use one of the unusual commands that can’t be redone with F4.

Simple, right?

Now that’s one of the most useful Excel spreadsheet tricks for you to dive into!

Dollar Sign

Add dollar signs to a reference with F4.

When you’re entering a formula and you need to change A1 to $A$1, click F4 while the insertion point is touching A1.

You can press F4 again in order to freeze only the row with A$1.

Press F4 again to freeze the column with $A1.

Press again to toggle back to A1.

The Most Important Excel Formulas You Need to Know to Analyze Data

Ready to use the most famous formulas like a pro?

Need some Excel formula tricks from these two masters?

Pay attention, then!

VLOOKUP

This is a blessing for any mobile marketer.

It’s one of the best tricks on Excel!

This incredible function is great for comparing and connecting different sets of data.

Basically, this formula will ascertain whether a given value from one table exists in the first column of another table, and then return a value from the second table.

vlookup

Tip:

Don’t forget to lock the range of the second table.

This is a common error when copying this formula.

CONCATENATE or &.

This is pretty useful.

It’s one of those Excel sheet tricks everyone should know.

It comes in handy when you’re creating different keyword lists or adding tracking parameters to URLs:

Tip:

We personally prefer using the “&” symbol.

Why?

Because it’s shorter than typing a full “concatenate” formula, but does the same exact thing.

concatenate

LEN

This is great when you’re writing for a banner.

That’s why it’s one of the most popular Excel tricks and shortcuts for affiliates.

Simply put this formula next to your description cells.

What will happen?

It will tell you when there is more room available, or when it’s over the character limit.

len

Text to Columns

We find this Excel magic trick to be super useful, especially when we work with destination URL lists.

It’s one of those Excel spreadsheet tips and tricks that you need to grasp!

Let’s say you’ve got a URL report and that you’d like to analyze the performance per unique URL.

However, every destination URL has a unique parameter attached.

Check the image below to see magic happen.

text to columns

Were we right or not?

See?

This is one of those cool Microsoft Excel tricks everyone should know.

Examples of How to Analyze Data Using Excel

You’ve checked all these amazing Excel tricks and awesome examples.

Now, what?

It’s time to put these new Excel tricks into practice.

Doing mobile affiliate marketing?

Then you know as well as we do that there are a lot of variables which can be tracked.

You can track brands, models, carriers, websites and so on.

Imagine you had amazing data about affiliate marketing.

It represented the traffic an affiliate had sent to an offer.

They want to find what they could do with the data to find the campaign’s best-converting segments.

As previously mentioned, Pivot tables will allow you to easily analyze this data.

There are a bunch of Excel pivot table tips and tricks for you to know.

Step One

Select all, table.

excel pivot table

Step Two

Insert/Pivot Table/OK

pivot tables insert

Step Three

In any pivot table, you need to fill the box with the corresponding variable.

variable box

a) Filter – pre-selection of what you want
b) Rows – for your x-axis
c) Columns – for your y-axis
d) Values
§ Count
§ Sum
§ Max
§ Min
§ Average

Select the field you want to see in the row fields area.

In this case, you’ll select Campaign.

This is what you want to analyze.

For the columns, press clicks, conversions, and revenue.

Step Four

After this, you need to change the format of the cells accordingly.

Since the revenue isn’t in euros, you need to select the column/format and insert the currency.

Step Five

Wanna check one of the best Excel tricks and tips?

Keep reading!

Imagine you want to analyze CellFlirt – mobile T-Mobile campaign in more detail.

You also wanna analyze its top-converting brands.

It has 27 conversions and 162€ in revenue.

You want to select the field that it’ll open into.

Click “brand”.

It adds all the brands for the campaigns in the row section.

pivot table 2

pivot table 3

The brands will appear for all the campaigns.

Even so, we only want to analyze that specific one.

How to go about it?

Simple!

Select all/options under pivot table/tools, and collapse the entire field.

After that, just hit the plus button of the campaign you want.

pivot table 4

Since you’re optimizing conversion rates, you want to add a calculated field because the conversion rate isn’t available in the data as it wasn’t calculated.

To do so, insert calculated field & format accordingly, after selecting the pivot table.

Formula = conversions (insert field) /clicks (insert field)

pivot table 5

pivot table 6

The brands Sharp & Softbank have high conversion rates.

The next step would be to insert that field on the report filter and only select those two to pinpoint them.

In order to conclude the analysis, press SubID.

It shows you the ads which have performed best for those two specific brands.

pivot table 7

If you go deeper, you can see that ads 23, 19, 13, 22 are performing better.

Ad 10 is performing poorly.

What to do?

You should pause that ad in those two brands.

Now is this one of the coolest Microsoft Excel tricks or what?

Conclusion

Enjoyed these Microsoft Excel tips and tricks?

Think you know so many great Excel tricks that your affiliate marketing colleagues are gonna feel sad?

Spread the word on these neat Excel tricks, bro!

You know everything you need.

Working on Excel involves a lot of copy-pasting, cleaning, formatting, and calculating.

You can do it all manually, of course.

But you can read up, learn the rules of the game, and use Excel’s mighty powerful functionalities in order to become much more efficient.

The tips that we’ve shown in this article will increase the efficiency of your analysis.

More importantly, they will also make sure you come up with better conclusions.

The actions that you’ll take after coming up with your conclusions will have a huge impact on the performance and profits of your campaigns.

Performance. Metrics. Improvement. Success.

This is what knowing the best Excel tricks is all about.

Use this master article, keep checking the most useful Excel tricks, and become an Excel pro who uses this super cool tool to increase affiliate marketing revenues.

Cheers!

André Martins

Business Intelligence Team Member

Rodrigo Salema

Business Intelligence Analyst

Meteora Automation Tool to Boost Instagram Engagement and Sales

Meteora Automation Tool to Boost Instagram Engagement and Sales

By now, it’s been clearly proved that Instagram isn’t something of a wet squib. It has exploded on the social media scene like a big bomb, and gone on to become one of the fastest growing social networks. All kinds of product-based businesses and influencers from...

Instagram 5xx Server Error – What is that mean? [Explanation]

Instagram 5xx Server Error – What is that mean? [Explanation]

No website can avoid running into the occasional glitch and problem, and when you consider how vastly complicated many social media websites are, it’s not surprising that their users encounter lots of different errors and difficulties at various times. There are so...

Instagram Live Isn’t Working, Why? [Quick Fix – 2020]

Instagram Live Isn’t Working, Why? [Quick Fix – 2020]

What Is Instagram Live? Social media websites are always looking for new tools and features to appeal to their users, and Instagram is certainly no exception to that rule. Instagram Live panders to the trend of immediacy, and also creates a sense of urgency to be...

Instagram, Reporting A Problem (How can you report?)

Why You Might Need To Report A Problem Instagram is amongst the most popular of social media websites, and enjoys enormous amounts of traffic and users everyday. It has several different facets and functions available to its users, so it’s no surprise that sometimes...

Instagram Hashtags Not Working [Solution – 2020]

Why Use Hashtags? Hashtags are one of the biggest trends on the internet right now. They’re a very useful tool which allows people to collate related data, connecting it across different channels and cutting through some of the noise generated by social media’s...

Instagram Won’t Let Me Log In [How to fix it? – 2020]

Social media is becoming increasingly popular, and Instagram is amongst the top ones, but that doesn’t mean it doesn’t run into issues occasionally. These can occur on the browser, the app, or both, but no matter what, they often lead to frustration and time wasted...

Instagram Keeps Crashing When I Open It [Solution – 2020]

If you, like most people, find yourself checking your phone compulsively for updates, new pictures, posts to favourite, and are generally a social media addict, you may find that you do run into the occasional frustrating glitch, even with your favourite programmes....

How to Recover deleted Facebook messages? [Solution – 2020]

If you use Facebook, you know that Messenger is a great way to communicate with friends or family, business associates, and others to send files and photos, videos, links, and other content. It’s a convenient way to share information with specific contacts without...

Subscribe