Author: Franz Malten Buemann

“I’ve never been a natural, all I do is try, try, try.”
These Taylor Swift lyrics, in the song “mirrorball”, perfectly explain my relationship with numbers, math, and anything concerning data analytics.
However, as a marketer, data analysis is one of the most important aspects of my job. But like most marketers, who prefer strategy and creativity, numbers and Excel reports don’t come naturally to me.
That’s why it’s important to know how to work in Excel and find shortcuts to help make the process easier.
Today, we’ll dive into one of those processes — how to find and remove duplicates in Excel.

1. Find and highlight duplicates in Excel through conditional formatting.
The first step of removing duplicates will be to find them. An easy way to do this is through conditional formatting.
You can do that by following these steps:

Make sure you’re on the Home tab.

Select the entire table by hitting the button on the top left.

Click Conditional Formatting → Highlight Cells Rules → Duplicate Values.

In the area titled “Format With”, change how you want duplicates to be highlighted. You can choose highlighting, bolding text, changing the color of text, etc.

And voila. Your duplicates are now highlighted. It should look something like this:

2. Count duplicates in Excel.
Now that you’ve found your duplicates, you might want to count them and see how many there are, especially if you have a large dataset.
To do this, you can use this formula =COUNTIF(A:A, A2). The formula means that Excel is going to count how many times a certain value is used in a certain place.
The column A:A stands for the data table you’re looking at. This will probably be a different value on your Excel sheet. Then, A2 references the value you want to count the frequency of.
To do this, follow these steps:

Create a new sheet in your Excel doc. I found that the easiest way to count duplicates in Excel is to create a new sheet in your Excel workbook. Then, copy and paste the column that you want to count duplicates in. In the example below, I copied and pasted the blog titles from the editorial calendar to see if there were any duplicate titles. Then, create another column for “occurrences.” This is where we’ll put the formula. Your new sheet should look something like this:

Insert the formula.

Now, you can insert the formula in the first cell under occurrences. You’ll type in or copy and paste the formula. Then highlight A:A (we’re going to replace this with your dataset), and click on the sheet in your Excel doc that has the data. Now, you can click on the top left to select the entire sheet, or you can just highlight the column or rows with your data.
For the second value, you’ll want to go back to your second sheet, highlight the A2, and choose the value next to it on the left. In most cases, this will stay A2, A3, A4, etc.
See what this looks like in action here:

3. Remove duplicates with the remove duplicates feature.
Now it’s time to remove the duplicates from your dataset.
Before you do this, I’d recommend duplicating/copying your dataset into another sheet or a whole other workbook altogether. You always want to keep your original data intact, even though you can use Excel to remove and filter the data you want. You don’t want to lose data because of a wrong click.
Once you’ve made a copy of your data, it’s time to remove the duplicates.
To remove duplicates, follow these steps:

Select the worksheet that has duplicate values that you want to remove. Click Data → Table Tools → Remove Duplicates.

Choose the columns where you want to remove the duplicates. In this case, I only want to remove duplicate blog titles. So I’ll choose column D. I kept “My list has headers” checked because there are two rows of headers before the data starts on this sheet. Remember that Excel will remove the entire row where the duplicate value is.

Review data.

Excel will now show you have many duplicate values were found, removed, and how many unique values remain.

Now you can review your data. Comparing my first dataset to this dataset, you can see that all the rows containing the same blog title were deleted.
This is what the sheet used to look like:

And this is what it looks like now:

When you remove duplicates from Excel, it’s important to note which column you want to remove duplicates from and remember that Excel is removing duplicates within a selected table range. You can highlight the whole workbook or you can just highlight the rows that have data in it.
Excel will automatically keep the first occurrence of the value.
Working on marketing reports or any marketing Excel sheet can leave you frustrated and banging your head against the wall (is it just me?). That’s why using Excel templates and following these simple instructions on formulas can help you level up your game.

Hey Folks, Join our growth community at Growthfolks.in and join the largest Growth Community in India to get answers. insights and quality reviews from the top growth marketers on your questions. Take part in interactive sessions, webinars and our famous “Brunch Sessions”. Get Guidance & mentorship from the best in the industry.
submitted by /u/GrowthFolks [link] [comments]

Agencies and marketers can benefit from offering branded email campaigns to their clients. However, some might struggle to convey how branded email campaigns contribute to better results for their clients. 
In this article, we’re sharing the biggest benefits your clients may experience by opting for branded emails. 
Cultivate a sense of familiarity and recognition
With a signature style, readers will quickly come to recognize your clients’ emails in their inboxes. Even incorporating a brand’s color alone has been shown to increase brand recognition by as much as 80%. 
This has strong implications for how often subscribers will open your clients’ emails and how favorably they will respond. For example, TGI Golf, a collective group of elite PGA Professionals, found that their branded emails led to an incredible 58% average open rate.
TGI’s Head of Communications, Mark Millard, attributes this success to how Campaign Monitor’s drag-and-drop builder makes it easy to create branded campaigns for their clients:
“The ease-of-use, particularly the email builder, has been extremely helpful with what we are trying to accomplish. Making changes is a quick, easy, and stress-free experience for me as a marketer.”
Check out how recognizable this branded email campaign is by one of TGI’s clients:

Image source.
Boost brand trustworthiness through brand consistency
Brand consistency is crucial to making sure consumers trust the brand. 
A 2019 survey found that 24.5% of company leaders reported that inconsistent branding led to confusion in the market, and 18.6% reported that inconsistent branding led to a damaged company reputation. Additionally, 60% of company leaders said that having a consistent brand was “very important” for generating leads. 
Breakdowns in brand consistency can feel jarring for consumers. If a company’s email marketing doesn’t closely match the branding on their socials or website, consumers will likely lose trust in the brand and emails will be less effective.
For example, compare this branded email campaign by surf company, Rip Curl, to their other marketing: 

Image source.

Image source.
Across their marketing channels, the Rip Curl brand remains consistent, with a consistent logo, consistent fonts and colors, and similar image choices.
To help with consistency in your emails, it’s worth creating one or two custom email marketing templates to keep each piece of communication on brand.
Strengthen brand meaning beyond the product or service
A key role that branding plays is creating meaning beyond the product or service. 
Branding goes beyond a company’s visual identity, helping to convey a company’s core values to consumers. And values are becoming more and more important: 77% of consumers want to buy from brands that share their values
If a client has a strong brand, it’s vital that the brand be infused into their email marketing so that consumers continue to recognize the brand for its identity and values. In turn, it’s likely that strong, branded emails will lead to increased revenue. 
For example, Bluetent, a digital marketing agency, found that using custom branded emails for their clients increased revenue by $50,000. 
Ryan Austin, Bluetent’s Director of Marketing, appreciates that Campaign Monitor’s email builder allows them to code their own emails for their clients:
“Ultimately, we went with Campaign Monitor because we felt that the platform was extremely innovative and perfectly aligned with our goals. The app offers an entire portfolio of templates, but we also have the freedom to code and implement our own.” 
For example, check out one of the branded client emails Bluetent created: 

Image source.
Wrap Up
When it comes to standing out in crowded consumer inboxes, the best strategy is to create strong, branded emails. Branded emails help consumers quickly recognize a brand in their inbox, feel more secure trusting the brand, and remember the brand’s values. 
The post How Clients Benefit From Branded Email appeared first on Campaign Monitor.

Hi marketing hive mind, I have implemented and used a few tools before (Marketo, Hubspot) but neither seem ideal for the role as we need hyper personalisation across the website and app and for B2C. The primary purpose will be well segmented dynamic email campaigns but later we will want features such as push notifications based on geolocation. A few I have on my list to demo are onesignal, braze, webengage, iterable and Clevertap. Has anyone had any good/bad experiences of these platforms or can recommend others? Budget is not a major issue here I just want something that can scale as our business grows, especially as I know how much time it can take to set up really robust workflows. Thanks in advance! If you have any questions let ’em at me.
submitted by /u/whenthepennydrops [link] [comments]

The robots.txt file helps major search engines understand where they’re allowed to go on your website.
But, while the major search engines do support the robots.txt file, they may not all adhere to the rules the same way.
Below, let’s break down what a robots.txt file is, and how you can use it.

What is a robots.txt file?
Every day, there are visits to your website from bots — also known as robots or spiders. Search engines like Google, Yahoo, and Bing send these bots to your site so your content can be crawled and indexed and appear in search results.
Bots are a good thing, but there are some cases where you don’t want the bot running around your website crawling and indexing everything. That’s where the robots.txt file comes in.
By adding certain directives to a robots.txt file, you’re directing the bots to crawl only the pages you want crawled.
However, it’s important to understand that not every bot will adhere to the rules you write in your robots.txt file. Google, for instance, won’t listen to any directives that you place in the file about crawling frequency.
Do you need a robots.txt file?
No, a robots.txt file is not required for a website.
If a bot comes to your website and it doesn’t have one, it will just crawl your website and index pages as it normally would.
A robot.txt file is only needed if you want to have more control over what is being crawled.
Some benefits to having one include:

Help manage server overloads
Prevent crawl waste by bots that are visiting pages you do not want them to
Keep certain folders or subdomains private

Can a robots.txt file prevent indexing of content?
No, you cannot stop content from being indexed and shown in search results with a robots.txt file.
Not all robots will follow the instructions the same way, so some may index the content you set to not be crawled or indexed.
In addition, If the content you are trying to prevent from showing in the search results has external links to it, that will also cause the search engines to index it.
The only way to ensure your content is not indexed is to add a noindex meta tag to the page. This line of code looks like this and will go in the html of your page.
<meta name=”robots” content=”noindex”>
It’s important to note that if you want the search engines to not index a page, you will need to allow the page to be crawled in robots.txt.
Where is the robots.txt file located?
The robots.txt file will always sit at the root domain of a website. As an example, our own file can be found at https://www.hubspot.com/robots.txt.
In most websites you should be able to access the actual file so you can edit it in an FTP or by accessing the File Manager in your hosts CPanel.
In some CMS platforms you can find the file right in your administrative area. HubSpot, for instance, makes it easy to customize your robots.txt file from your account.
If you are on WordPress, the robots.txt file can be accessed in the public_html folder of your website.

WordPress does include a robots.txt file by default with a new installation that will include the following:
User-agent: *
Disallow: /wp-admin/
Disallow: /wp-includes/
The above is telling all bots to crawl all parts of the website except anything under the /wp-admin/ or /wp-includes/ directories.
But you may want to create a more robust file. Let’s show you how, below.
Uses for a Robots.txt File
There could be many reasons you want to customize your robots.txt file — from controlling crawl budget, to blocking sections of a website from being crawled and indexed. Let’s explore a few reasons for using a robots.txt file now.
1. Block All Crawlers
Blocking all crawlers from accessing your site is not something you would want to do on an active website, but is a great option for a development website. When you block the crawlers it will help prevent your pages from being shown on search engines, which is good if your pages aren’t ready for viewing yet.
2. Disallow Certain Pages From Being Crawled
One of the most common and useful ways to use your robots.txt file is to limit search engine bot access to parts of your website. This can help maximize your crawl budget and prevent unwanted pages from winding up in the search results.

It is important to note that just because you have told a bot to not crawl a page, that doesn’t mean it will not get indexed. If you don’t want a page to show up in the search results, you need to add a noindex meta tag to the page.

Sample Robots.txt File Directives
The robots.txt file is made up of blocks of lines of directives. Each directive will begin with a user-agent, and then the rules for that user-agent will be placed below it.
When a specific search engine lands on your website, it will look for the user-agent that applies to them and read the block that refers to them.
There are several directives you can use in your file. Let’s break those down, now.
1. User-Agent
The user-agent command allows you to target certain bots or spiders to direct. For instance, if you only want to target Bing or Google, this is the directive you’d use.
While there are hundreds of user-agents, below are examples of some of the most common user-agent options.
User-agent: Googlebot
User-agent: Googlebot-Image
User-agent: Googlebot-Mobile
User-agent: Googlebot-News
User-agent: Bingbot
User-agent: Baiduspider
User-agent: msnbot
User-agent: slurp     (Yahoo)
User-agent: yandex
It’s important to note — user-agents are case-sensitive, so be sure to enter them properly.
Wildcard User-agent
The wildcard user-agent is noted with an (*) asterisk and lets you easily apply a directive to all user-agents that exist. So if you want a specific rule to apply to every bot, you can use this user-agent.
User-agent: *
User-agents will only follow the rules that most closely apply to them.
2. Disallow
The disallow directive tells search engines to not crawl or access certain pages or directories on a website.
Below are several examples of how you might use the disallow directive.
Block Access to a Specific Folder
In this example we are telling all bots to not crawl anything in the /portfolio directory on our website.
User-agent: *
Disallow: /portfolio
If we only want Bing to not crawl that directory, we would add it like this, instead:
User-agent: Bingbot
Disallow: /portfolio
Block PDF or Other File Types
If you don’t want your PDF or other file types crawled, then the below directive should help. We are telling all bots that we do not want any PDF files crawled. The $ at the end is telling the search engine that it is the end of the URL.
So if I have a pdf file at mywebsite.com/site/myimportantinfo.pdf, the search engines won’t access it.
User-agent: *
Disallow: *.pdf$
For PowerPoint files, you could use:
User-agent: *
Disallow: *.ppt$
A better option might be to create a folder for your PDF or other files and then disallow the crawlers to crawl it and noindex the whole directory with a meta tag.
Block Access to the Whole Website
Particularly useful if you have a development website or test folders, this directive is telling all bots to not crawl your site at all. It’s important to remember to remove this when you set your site live, or you will have indexation issues.
User-agent: *
The * (asterisk) you see above is what we call a “wildcard” expression. When we use an asterisk, we are implying that the rules below should apply to all user-agents.
3. Allow
The allow directive can help you specify certain pages or directories that you do want bots to access and crawl. This can be an override rule to the disallow option, seen above.
In the example below we are telling Googlebot that we do not want the portfolio directory crawled, but we do want one specific portfolio item to be accessed and crawled:
User-agent: Googlebot
Disallow: /portfolio
Allow: /portfolio/crawlableportfolio
4. Sitemap
Including the location of your sitemap in your file can make it easier for search engine crawlers to crawl your sitemap.
If you submit your sitemaps directly to each search engine’s webmaster tools, then it is not necessary to add it to your robots.txt file.
sitemap: https://yourwebsite.com/sitemap.xml
5. Crawl Delay
Crawl delay can tell a bot to slow down when crawling your website so your server does not become overwhelmed. The directive example below is asking Yandex to wait 10 seconds after each crawl action it takes on the website.
User-agent: yandex  
Crawl-delay: 10
This is a directive you should be careful with. On a very large website it can greatly minimize the number of URLs crawled each day, which would be counterproductive. This can be useful on smaller websites, however, where the bots are visiting a bit too much.
Note: Crawl-delay is not supported by Google or Baidu. If you want to ask their crawlers to slow their crawling of your website, you will need to do it through their tools.
What are regular expressions and wildcards?
Pattern matching is a more advanced way of controlling the way a bot crawls your website with the use of characters.
There are two expressions that are common and are used by both Bing and Google. These directives can be especially useful on ecommerce websites.
Asterisk: * is treated as a wildcard and can represent any sequence of characters
Dollar sign: $ is used to designate the end of a URL
A good example of using the * wildcard is in the scenario where you want to prevent the search engines from crawling pages that might have a question mark in them. The below code is telling all bots to disregard crawling any URLs that have a question mark in them.
User-agent: *
Disallow: /*?
How to Create or Edit a Robots.txt File
If you do not have an existing robots.txt file on your server, you can easily add one with the steps below.

Open your preferred text editor to start a new document. Common editors that may exist on your computer are Notepad, TextEdit or Microsoft Word.
Add the directives you would like to include to the document.
Save the file with the name of “robots.txt”
Test your file as shown in the next section
Upload your .txt file to your server with a FTP or in your CPanel. How you upload it will depend on the type of website you have.

In WordPress you can use plugins like Yoast, All In One SEO, Rank Math to generate and edit your file.
You can also use a robots.txt generator tool to help you prepare one which might help minimize errors.
How to Test a Robots.txt File
Before you go live with the robots.txt file code you created, you will want to run it through a tester to ensure it’s valid. This will help prevent issues with incorrect directives that may have been added.
The robots.txt testing tool is only available on the old version of Google Search Console. If your website is not connected to Google Search Console, you will need to do that first.
Visit the Google Support page then click the “open robots.txt tester” button. Select the property you would like to test for and then you will be taken to a screen, like the one below.
To test your new robots.txt code, just delete what is currently in the box and replace with your new code and click “Test”. If the response to your test is “allowed”, then your code is valid and you can revise your actual file with your new code.

Hopefully this post has made you feel less scared of digging into your robots.txt file — because doing so is one way to improve your rankings and boost your SEO efforts.