How to Use REGEX Formulas in Google Sheets

Handling big sets of data to analyse is quite normal if you work in SEO. On a regular basis, either in Google Sheets or Excel, I use formulas such as vlookup, index, iferror, and the list goes on, to sort quickly through endless data. Not only are they fundamental for my survival as an SEO analyst, I often still have to refer to the Distilled Excel guide to achieve what I want. But sometimes, these functions are just not good enough. They’ll do the work, but the time it takes to set them up makes me question whether I am actually working faster.

Thanks to a recent outstanding presentation delivered by Robin Lord during a digital marketing Meetup event at Distilled, I was introduced to the world of Regex. Regex is a matching expression tool returning specific values, for example, true or false, whether it finds the expression or not. It can be used on many platforms, such as Google Analytics, Python, Java, and more, to perform searches of specific strings.

You may be thinking “oh no, another complex programming language I don’t understand that complicates things even more”. And you could not be more wrong. The good news is that you can use Regex on Google Sheets to work with your data, making your life beyond easy. That’s right, I’m all about learning tools to make my life easier and lately Regex is my favourite.

The 3 main Regex formulas you can use on Google Sheets are:

  • REGEXEXTRACT
  • REGEXREPLACE
  • REGEXMATCH

They perform exactly what they say: extract, replace, and match. Since my purpose here is to demonstrate how helpful these functions are, I won’t go too much in deep into all the Regex syntax rules. There are already plenty of comprehensive guides and resources out there so if you want to learn more Regex, here is a useful Regex guide and this is a really good debugging tool.

Below I’m going to dive right into Regex formulas for Google Sheets and examples on how to use them and what you can achieve with each one.

REGEXEXTRACT

This is the function you’d want to use when you are digging out a certain string within a longer one. For one of my clients, I had a list of URLs and I had to extract certain words within each. For example, from the list of URLs below let’s suppose I had to extract the location:

However, I had many words I was looking for, and each URL could have included any of them and in a different location within the URL. Without Regexextract, I’d have to apply multiple filters and custom formula for each URL, extracting the word based on its position. I don’t have time for this and nor should you.

Let me get you started with a really simple Regex example. Let’s say I needed to extract the word “great” from the link “http://regexisgreat.com”. When you type in “=REGEXEXTRACT” in Google Sheets this is what the function requires:

The “text” part is the cell where you have something to look for, and the “regular_expression” is the expression telling Regex what to look for. In our case, the formula would become:

=REGEXEXTRACT(A3,”.*(great).*”)

Here is a little syntax context: The .* before and after the word “great” tells Regex not to worry what precedes or follows the string “great”. So “great” could be anywhere in the URL, Regex will find it:

This is a Regex example of what the formula would become if you are looking for multiple values:

=REGEXEXTRACT(A3,”.*(blue|green|red|purple|blonde).*”)

Here you probably noticed the use of pipe “|” which stands for “or”. The formula tells Regex to look for the word blue or green or red and so on.

Again, it doesn’t matter where the word is within the string. One additional thing to note is that Regex differentiates for capitalised letters, which can be quite handy sometimes.

The applications of this formula are infinite when you think that you can combine it with the functions you use every day. Even without combining REGEXEXTRACT with any other formulas, performing the task I show above without it would be quite time-consuming.

REGEXREPLACE

For one of my clients, I had a long list of title tags and I wanted to capitalise the first letter of each word except for words like “the” “in” “for” “a”, you get the point. With the formula PROPER you can capitalise each first letter of a word:

But this didn’t solve my problem. Regexreplace came to the rescue:

=regexreplace(proper(A4),”The”,”the”)

Similarly to Regexextract, the “text” part is the cell you want to modify, and the remaining part is a simple instruction telling Regex what to replace:

Here is an example of Regexreplace combined with itself:

=regexreplace(regexreplace(proper(A4),”The”,”the”),”In”,”in”)

REGEXMATCH

Regexmatch searches for a value in a cell and returns a TRUE or FALSE. For me, this became useful when I was checking whether the URLs in my list contained certain words and I didn’t want to go through endless filters.

Here is a simple Regexmatch example, looking for the word “blue” within a URL:

=regexmatch(proper(A3),”.*blue.*”)

Here is an example showing how Regex differentiates for capitalised letters:

=regexmatch(proper(A3),”.*Blue.*”)

Again, here you can use multiple values to look for within the same formulas:

=regexmatch(proper(A3),”.*Blue|blue.*”)

The applications for Regexmatch are many and you can combine it with any other function on Google Sheets.

As you may have noticed, the potential of these three simple functions are endless. Let’s not forget that there are more of syntax rules on Regex that you can use to achieve different things. Here is an additional debugging tool for those of you who want to get into coding with Regex.

Regex has made my life a lot easier and my data a lot more interesting to work with. I’m sure you’ll find it extremely useful and surprisingly easy to learn. Feel free to comment if you have any questions about the formulas or further suggestions. Keep calm and REGEX on!

How to Use REGEX Formulas in Google Sheets was posted via Internet Marketing

Advertisements

The Latest Evolution of Facebook: The Marketing Low-Down on 5 Recent Changes

Latest Facebook Updates 2018

Latest Facebook Updates 2018From the Russian ad debacle to the Cambridge Analytica scandal to suffering a major stock price blow just a few short weeks ago, Facebook has been making less-than-stellar headlines the past few months. Users are concerned about their privacy and how their data is being used—and brands and marketers are wondering whether the platform will continue to be a viable advertising and engagement platform.

In an attempt to rebuild trust, ensure better data protection and transparency, the social network is doubling down, again, on their commitment to improving the user experience and creating a fun, respectful community.

As all marketers will remember, the quest to improve user experience started way back in 2015, with the announcement it would be making refinements to its News Feed to strike a better balance between friends, public figures, publishers, businesses, and community organizations. That continued in the summer 2016 with more updates favoring friends and family content—and was still on the move when the first scandal broke later that year.

Of course, these changes didn’t do marketers and brands any favors in the organic reach department. Organic reach had already been declining, and these moves have nearly eliminated its potential. And now, more changes have arrived, presenting new challenges as well as some opportunities.

Below we share the low-down on five of such recent or rolling out changes, what they mean for social media marketers, and some potential next steps to take.

1. A New News Feed

Once again, the News Feed is getting a facelift—a big one. While Mark Zuckerberg announced back in January 2018 that changes would be rolling out throughout the year, a “major update” was announced in April, which Director of Product Management, Mark Hull, details in the video below:

https://www.facebook.com/plugins/video.php?href=https%3A%2F%2Fwww.facebook.com%2Ffacebook%2Fvideos%2F10156988765141729%2F&show_text=0&width=560

Essentially, meaningful person-to-person interaction is what will carry the News Feed ranking weight, and person-to-page interactions will continue to be second tier. Oh, and Facebook expects people to spend less time on the platform.

This sounds pretty scary for marketers. Most have adapted their strategies to zero-in on fostering engaging discussion, as well as throwing spend behind Facebook’s ad platform (which is also changing and we’ll get to that later).

But before you eliminate Facebook from your marketing mix, there are a few opportunities to consider:

Working with influencers: With Facebook continuing to elevate content from individuals, there may be no better time to start building relationships with industry influencers and thought leaders that you can collaborate with on content.

Read: Death of Organic Reach = New Opportunities for Influencer Marketing

Facebook Groups: As my colleague, Nick Nelson, reported not too long ago, while groups have long been available as a feature on Facebook, the brand-driven “Facebook Groups for Pages” were just rolled out last year. And some brands are seeing traction with them, but this isn’t something you leap into without being thoughtful.

Read: The Question on Many Marketers’ Minds: Should My Brand Start a Facebook Group?

Facebook Stories: Very recently, Facebook insiders asserted that Facebook Stories may very well be the future of connection on Facebook. Once again, as Nick Nelson pointed out: “Facebook Stories are intriguing because they offer a real chance to capture part of a user’s attention — maybe even more than the minimum amount.” And early adopters may secure an advantage.

Read: The Future of Connection on Facebook: How Stories May Change the Marketing Game

2. Stricter Ad Targeting

As of late, most of Facebook’s critiques are a result of their advertising products and practices. By increasing the targeting capabilities of their advertising products, Facebook arguably put users’ privacy at risk. To help correct that perception and protect user privacy, Facebook is making several changes to their advertising platform.

One such update was released in early July, requiring advertisers to state where they acquired people’s information for their custom audiences. Instead of simply uploading a list of emails you want to target as a custom audience, Facebook wants advertisers to take extra steps to ensure those emails came from a reputable source and that the audience has consented to those ads. With this change, Facebook hopes to improve transparency with users about why they see ads from certain brands and how they received their information.

New Facebook Ad Disclaimer(Credit: Facebook)

In addition, Facebook has also disabled their Partner Categories product, which provided targeting capabilities from third-party data providers to advertisers. This limits advertiser insight into user behavior outside of Facebook (e.g. purchasing activity), making ads appear more natural to users and less “big brother.”

From our perspective, this is good news. Consumers are increasingly wary of marketing and advertising messages and this move can help strengthen the credibility and relevance of your ads and brand. Of course, this is all assuming you can and do confirm your custom audience lists and sources fall within the new guides.

So, if you haven’t already, take the steps to review your custom audience lists and their sources. You need to be able to state if you received the information directly from your audience, a partner, or a combination. If you used a data provider like an advertising or marketing agency, double check that they’ve acquired the data honestly and they agree to Facebook’s Terms of Use.

3. New Data History Tools

Now more than ever, people want control over their data. They want to know what information is stored, who has access to it, and they want to be able to delete it.

Recognizing this need, Facebook announced a new feature called Clear History that will be released “soon.”With this new feature, Facebook is giving users the ability to see the websites and apps that store information with Facebook, and delete that information from their account.

However, Facebook will still retain aggregated analytics, but no personally identifying information will be contained.

“We’ll still provide apps and websites with aggregated analytics – for example, we can build reports when we’re sent this information so we can tell developers if their apps are more popular with men or women in a certain age group,” Facebook says. “We can do this without storing the information in a way that’s associated with your account, and as always, we don’t tell advertisers who you are.

What exactly does this mean for marketers? If you use Facebook plugins on your website (think Facebook Pixel or “Like” buttons on websites), your audience can now see and delete the information that the plugin collects; meaning it won’t be connected to their profile any longer.

Obviously, if users take advantage of this when it rolls out, clearing their history could be problematic for marketers, and maybe even users. For marketers, it will be incredibly difficult to target these folks with ads. For users, that could mean an uptick in irrelevant ads for a time.

However, the eventual upside for advertisers could be the “re-learning” that needs to happen after a history cleanse, which can lead to a more relevant and accurate look of who your audience is.

4. Poor Customer Feedback = Ban

A poor customer experience really sours your impression of a brand. An to ensure that ads on Facebook lead to positive shopping experiences, and not negative ones, Facebook will now ban brands that have low customer satisfaction ratings from advertising on their platform.

For example, if users give you too many frowning faces shown in the rating system below, Facebook will reject your ads.

Facebook Negative Review Example(Credit: Facebook)

Before you become too concerned, however, Facebook says it will: “Share feedback directly with businesses that receive high volumes of negative feedback and will give them a chance to improve before taking further action. If feedback does not improve over time, we will reduce the amount of ads that particular business can run.”

So, even if you receive negative ratings, you will have an opportunity to make improvements to prevent being banned.

The opportunity here is obvious. The better customer experience you provide, the more successful your marketing efforts will be. For Facebook ads, this is rooted in focusing on clarity and honesty within your ads.

At a minimum, Facebook suggests taking steps to ensure your ads aren’t misleading. In addition, use images or videos to make it very clear what you are selling and what you are selling it for. It’s also a good idea to set clear expectations for how users will receive your product or service. By setting clear guidelines, you’re more likely to meet customer expectations, leading to more positive user ratings. For more insight, read Facebook’s tips on how to improve customer feedback.

5. All of Your Ads, Exposed

Knowing the organization behind an ad is important. Otherwise, users might not trust the content of the ad. So, to increase ad and page transparency, Facebook now allows users to see all of the ads any page is running within their partner network.

This includes ads on Instagram, Messenger, and the rest of the Facebook partner network. In addition, even if pages aren’t advertising, Facebook will provide more information about a page including name history and the date the page was created. Together, these changes aim to give users more information about an organization so they can decide if an ad is credible or not.

Facebook Ad Transparency(Credit: Facebook)

For marketers, this change isn’t a bad thing — it may even mean more eyeballs on your advertising content. However, marketers should still be thoughtful about how their ads will be perceived by individuals outside of their target audience.

For example, even if an ad isn’t specifically designed for or served to them, users can still review (and report) your ads. As a result, you need to take extra steps to make sure your ads are consistent, clear, and friendly for all.

The Life Force of Facebook

Long before the scandals and latest privacy concerns, Facebook has been rooted in evolution. Just think what the platform started as and has become. So, while the recent and coming changes seem pretty fierce, I think it’s safe to say we were already on that trajectory. And there’s probably more to come.

Since the major changes that impacted advertisers and brands began rolling out in 2015, Facebook has maintained their actions are all in the interest of creating a better user experience. And at the end of the day, that’s what the goal of any marketer is in their quest to nurture their audience and aid them on their customer journey.

So, it’s OK if you’re a panicked, disheartened, or simply irritated. But, at this point, the platform still holds marketing opportunity, marketers just need to adapt their social media marketing strategies, try new features and avenues, and work hard to ensure they’re part of providing that great user experience.

Looking for more social media marketing news, tips, and insights? Check out all of our recent social media-related blog posts.

The Latest Evolution of Facebook: The Marketing Low-Down on 5 Recent Changes was posted via Internet Marketing

Building visual reporting in Google Sheets

If you didn’t already know, Google Sheets has a fantastic Google Analytics add-on that can be used to create custom reports in Google Sheets. For more information on the reporting capabilities of this tool, read this blog post from 2016, which will also teach you how to download the add-on and set up a custom report configuration. As an overview, this add-on allows you to:

  • Quickly pull any data from Google Analytics (GA) accounts you have access to directly into a spreadsheet
  • Easily compare historical data across custom time periods
  • Filter and segment your data directly within Google Sheets
  • Automate regular reporting
  • Easily tweak your existing reports (which will be saved to Google Drive) to get new data

Beyond how to use the tool – we have free stuff!

All the heaps of data you can pull with this tool are useful, but what if you want to quickly be able to compare data from your custom report configurations? Wouldn’t it be nice to have a reporting view that visually displays how your website is performing week-on-week (WoW) or year-on-year (YoY) by comparing the number of organic sessions and orders (and is near-automated)?

I thought so too.

Recently, I built a report using the Google Analytics add-on within Google Sheets. I have created a templated version of this report to share with you. Feel free to make a copy of it and use it as you please.

Start creating your own Google Sheets reports.

Here are some of the insights this report provides you with:

  • Organic sessions and orders WoW and YoY for the entire website
  • Organic sessions and orders WoW and YoY for different page types including category, content, product and seasonal pages
  • Organic sessions and orders WoW and YoY for the homepage and a static top 20 pages
  • Organic sessions and orders WoW and YoY for your mobile website

Using formulas, some regular expressions, and conditional formatting, their weekly SEO reporting process is now nearly automated using data from their Google Analytics.

Wait, can’t I do all of this in the GA interface already?

Not quite. Here are some of the benefits of this add-on over the standard GA interface:

  • In the add-on, you can filter on dimensions or metrics that are not already included in your report. In the GA interface if you’re looking at a report with landing pages as the only dimension, you can’t use filters to filter to just organic traffic. With the add-on, you can
    • You can do this in GA using a segment rather than a filter, but segments are more prone to causing issues with sampling than filters
  • Once you have loaded in your data with the add-on, you can manipulate it without having to continually export files
  • You can do multiple comparisons with the add-on (which I do in this report), whereas in GA you can only do two, i.e. either year on year or week on week comparisons, not both simultaneously
  • Using the add-on can provide you with a single source of truth, rather than having all the additional data offered by GA that you may not want to be diving into
  • Using conditional formatting in Google Sheets means that I have been able to flag varying degrees of positive or negative changes by colour

How you can make this report your own

This blog post will walk you through why the report is useful, how you can customise it, and then if you’re curious, I’ve also gone into further detail in an appendix below how the report works. This will also be useful for any potential de-bugging you may have to do.

With the following instructions, even if you’re a beginner to things like regular expressions and Excel/Google Sheets formulas, you should still be able to customise the report and use it yourself.  

To build the report I’ve used regular expressions within the reporting configuration to filter to specific page types that I wanted, and then in the “Comparisons” sheet, I’ve used formulas to pull the data from the sheets to then get WoW and YoY comparisons. All of this takes place within the sole Google Sheet.

For a one-time report, this would likely not be worth the time invested, but if you or a client have a need for standardised, ongoing reporting – and you have access to the GA data for the account – then this template can be a great way to give you quick, easy insight into your organic traffic trends.

It saves me close to an hour of time a week – or almost 6 working days a year.

What you will need to customise this report

To make this report your own, you’ll need:

  • To download the Google Analytics add-on for Google Sheets (instructions are here)
  • Access to the GA account you want to report on
  • Your GA View ID (instructions on how to find this here)

Other resources you may find useful:

  • GA’s Query Explorer – can be used to test the output of different combinations of metric and dimension filters
  • GA Reporting API – lists and describes all the dimensions and metrics available through the Core Reporting API

Why this report is useful

This report uses conditional formatting to make any significant positive or negative changes stand out. It also uses both macro-scale views of the website trends and more detailed views. At the top, it has the total sessions and orders for the entire website, plus the WoW and YoY comparisons, and as you go further down the report it becomes more granular.

I built this report so I could get a better idea of how specific parts of a website were performing. The primary pages I have reported on are the category pages, content pages, product pages and mobile pages. Following that, I have put in the data for a list of the top 20 pages, including the home page. At the end, there is a section for seasonal pages.

The top 20 pages that it reports on are static. These were pre-determined by looking at those pages that consistently had the highest organic sessions. We opted to use a static top 20 rather than the actual top 20 by sessions per week because using the actual would require updating the SUMIF formulas each time the report was run.

This is a report that I update weekly. The date formulas are calculated based on whatever today’s date is and are then used in the report configuration.

This allows the dates to update on their own without me manually having to change them each time I want to run the report. I just had to then schedule the report to run weekly and BAM! – no hands necessary.

To set an auto-run for your report, go to Add-ons > Google Analytics > Schedule Reports, check “Enable reports to run automatically” and then set the time and frequency you want your report to run.

This has made my life much easier, and hopefully sharing it will make your life easier too.

How to customise this report

In this report you are going to have to customise:

  • Your report configurations
  • The dates you want this to run
  • The primary page types you want to compare (we have category pages, content pages, product pages and mobile)
  • The top 20 pages you wish to report on (you might choose not to use this)
  • Your seasonal pages, if applicable

Necessary changes – Report Configurations

First level customisation

To learn how to set up and run report configurations, the blog post I referenced at the beginning can help you. For the purpose of this post, I’ll just focus on where you’ll need to tweak it for your website or client.

You will need to put in the View ID you want to report on (Row 3), and you will have to edit the regular expressions in the filters section (Row 9) to make them unique to your client, which I will cover below.

The dates I am using (Rows 4-5) are references to those I have in the comparisons tab. If you want to use different dates, you can either manually change them here, or in the next section, I explain how the date formulas work. Here’s a screenshot of the formula in cell B4 so you can see what I am talking about:

You also are likely going to want to change the Report Name (Row 2) for each column. If you do, be sure that you clearly label each section. The Report Name becomes the name of the sheet that is generated once you run the report, and later the name used in the formulas in the Comparisons tab.

Note that when you change the Report Name, it won’t replace the old one but will instead just create a new one. You’ll have to manually delete the old, unwanted sheets.

Updating the regular expressions

There are two parts of the regular expression that are unique to the website that you will have to update.

The first section that you’ll have to update is where I had to filter out PPC data that was being mistakenly reported as organic by GA.  For this site, PPC data could be identified with any URL that contained either “gclid”, “cm_mmc”, “newsl”, or “google” – this is likely to be different for you, so change what is in the quotations to reflect this.

This was being reported as organic because of the tagging used for PPC data was initially intended for a different reporting platform, so it may not be a problem for you. If so, you can delete this section.

If this is not a problem for you, then you can go ahead and delete this part of the Filters section (everything following ga:medium==organic in cells B9-D9 of the Report Configuration tab).  

The second update you’ll have to make to the regular expressions are to those used in the Filter sections for all the columns aside from the ‘Everything’ ones (cells E9-P9). These are used to identify the part of the URL path you want to filter on.

Each filter is separated by a semi-colon, so if you want to add anything to these filters be sure to have that in there. Semi-colons mean “AND” in the Core Reporting API. For commas, you use “OR”.

Here is the ‘everything’ section:

ga:medium==organic;ga:landingPagePath!@gclid;ga:landingPagePath!@cm_mmc;ga:landingPagePath!@newsl;ga:landingPagePath!@google

Aside from ga:medium==organic (which just filters to organic sessions only) this just filters out PPC data.

I’ve copied this expression across all of my sections, but for the sections on specific page types I’ve also included another regular expression to get the specific URLs I am looking for, highlighted below.  For these sections, you’ll see variations of this:

ga:medium==organic;ga:landingPagePath=~\/category\/;ga:landingPagePath!@gclid;ga:landingPagePath!@cm_mmc;ga:landingPagePath!@newsl;ga:landingPagePath!@google

For this example, it was filtering for URLs containing “/category/”. This filters that report down to just our client’s category pages. Again, you can customise this regular expression to your unique website or client. Be sure to escape any slashes you use in this section with a backslash.

The mobile sections (cells N9-P9) were a bit different, as this is a defined dimension in GA. You’ll see in those columns that I just added in “ga:deviceCategory==mobile” after the filter for organic.

Once all that is done you can run your reports and move on to customising your Comparisons tab.

Necessary changes – Comparisons tab

Date formulas

The date formulas in cells M13:S18 further automate the reporting. The report defines a week as Monday to Sunday as this was how our client defined theirs, so if this is different for you, you’ll have to change it. If you’re curious how these specific formulas work, I have covered it in more detail in the appendix.

If you do change this section, make sure that the dates are formatted as YYYY-MM-DD. To do this, go to Format > Number > More formats > More date and time formats.

I’ve also left space to enter custom start and end dates. The specific client this was built for wanted to be able to compare odd weeks for their YoY comparisons around specific holidays. These dates will only be used if cells N16-S16 are not blank.

Google Sheets formulas – for primary, top 20 and seasonal pages

Once you’re happy with the dates, the primary thing you need to update are formulas, specifically the names of the sheets being referenced and the criteria that define the pages you want to report on.

If you are getting errors when you customise the formulas, especially #N/A! errors, try re-running the cells in the comparisons sheet first by just highlighting and pressing enter.

For the primary pages at the top in cells B6:K10, if you have changed the Report Names from the previous section you only have to update the sheet names being referenced. You’ll also have to do this for the following sections.

When you’re doing this, be sure not to mix up previous week and previous year.

This can be a long and irritating process. One thing I found that helped speed it up was another Google Sheets add-on Advanced Find and Replace. This lets you use the find and replace function within formulas, which means you can simply find “Everything current week – UK” and replace it with whatever alternative you have.

This plug in has a free trial, and once that is up you can only use it once a day – so make the most of it while you have it! If you know of any other free alternatives, I’d love to hear about them.

The formulas in the top 20 pages, cells B13:K24, have slightly different formulas are different depending on the page type.

Where I’ve highlighted in the formula below is the part of this formula you’ll have to change to match your specific page type. This is from cell B14:

=SUMIF(‘Everything current week – UK’!$A:$A,”*”&”/top page 2/“,’Everything current week – UK’!$B:$B)

The number you’re seeing is a sum of all the pages with /top page 2/ in the URL from the Results Breakdown in my Everything current week – UK tab, shown below.

For the seasonal section in cells B34:K35, you’ll just have to replace where I have either “christmas” or “black-friday” to include whatever specific seasonal term you want to report on. Remember, this must be a reference that is included in the URLs.

Other changes you can make – Report Configuration

For metrics, I have used sessions and transactions, but this can be adjusted if there is a different metric you wish to report on. Just be sure to change the headings in the comparison tab so you remember what you’re reporting on.

For dimensions, I have used the landing pages. Again, you can adjust this if you wish to, for example, report on keywords instead.

I’ve set the order to be in descending rather than ascending. This organises the data but also helped to determine the top 20 pages.

I have set the limits on these to 1,000. I did this because I only really cared about the specific data for the top pages. The limit does not change the total number that is reported, it just limits the number of rows.

Unfortunately, this is also where I have to talk about sampling. In my report tabs in cells A6 and B6 it says “Contains Sampled Data, No”. If your data is being highly sampled then you need to decide if that will be a roadblock for you or not.

Here is a resource with some ways to get around sampled data.

It’s reporting time

If you’ve made the above changes, once you run your reports with the updates to your Report Configuration, you should have a Google Sheet reporting on your specific data.

That was a lot of information, so if you have any questions or need any help on a specific part of this process please comment below!

As promised, I’ve added an appendix to this post below for those of you that are curious to know in more detail how it works.

Happy reporting!

Appendix: How this report works, if you’re curious

Main report formulas

Totals, WoW and YoY for top report section

Columns B and G for the top section simply pull out whatever number is reported for the total sessions and total orders from each sheet. This is useful not only because it brings all the absolute numbers into one place, but also because I can now reference these cells in formulas.

For WoW relative (Column C), I’ve again referenced those same cells, but created a percentage with a (Current – Previous)/Previous formula.

Column D uses the percentages generated in Column C to extract the absolute number differences.

For YoY relative (Column E), I’ve followed the same exact method, just referencing the data for the previous year rather than the previous week. Again, I used these numbers to extract out the absolute numbers seen in Column F.

The grey orders section does the exact same thing, but instead references the cell in each respective configuration with the order total, rather than sessions.

I’ve also wrapped these formulas in IFERRORs, to prevent the sheet from having any error messages. This was primarily for aesthetics, although it is worth noting that sometimes this can lend to it saying there was a 0% change, when maybe there was a 100% increase as that page type did not exist in the previous year.

Date formulas

Our client wanted weekly reporting comparing weeks that run from Monday to Sunday as this was how our client defined theirs. Since GA weeks run from Sunday to Saturday, this had to be customised.

These dates are calculated based off the “=TODAY()” date in cell M14, as well as the first day of last year calculated in M16, the first Monday of last year in M18, and the week numbers in cells O12 and Q12.  

Because these dates are calculated automatically here, in the Report Configuration tab I can simply reference the specific cells from my Comparisons sheet, rather than manually having to enter the dates each time I run the report. This also made it so I can set this report to run automatically every Monday morning before I get into the office.

You’ll also notice that below the dates I have left space to enter custom start and end dates, this is again because the specific client this was built for sometimes wants to compare odd weeks for their YoY comparisons to account for specific holidays.

In the Report Configuration sheet, I have an IF formula in the cells that says, if the custom cells are blank then use the usual date, if they are not, then use those. On those occasions, it does mean I have to manually run the reports, but I guess you can’t have everything.

Top 20-page reporting

The Top 20-page section is where the formulas get a bit beastly, but this was something the client specifically requested. We initially wanted it to report on the top 20 pages from each week, but that wasn’t possible using formulas, as we needed something static to reference.

For these, I used a SUMIF formula. For example, in cell C13 I have this formula to report the WoW relative number for the home page:

=IFERROR((SUMIF('Everything current week - UK'!A:A,"*"&".co.uk/",'Everything current week - UK'!B:B)-SUMIF('Everything previous week - UK'!A:A,"*"&".co.uk/",'Everything previous week - UK'!B:B))/SUMIF('Everything previous week - UK'!A:A,"*"&".co.uk/",'Everything previous week - UK'!B:B),0)

Again, the IFERROR statement wrapped around my formula is just to clean things up so lets drop that and break down what the rest of this formula is doing.

=(SUMIF('Everything current week - UK'!A:A,"*"&".co.uk/",'Everything current week - UK'!B:B)-SUMIF('Everything previous week - UK'!A:A,"*"&".co.uk/",'Everything previous week - UK'!B:B))/SUMIF('Everything previous week - UK'!A:A,"*"&".co.uk/",'Everything previous week - UK'!B:B)

The SUMIF formula sums up cells if they meet specific criteria. It works by defining the range, in this case ‘Everything current week – UK’!A:A (every row in column A of the sheet Everything current week – UK), and then the criteria that you want to be summed. Here, it is all cells which include anything and end with “.co.uk/”.

Lastly, you define the sum range, which is the range to be summed if it is different from the original range defined. We’ve used this here because we want the sum of all the sessions, not the landing page paths. That whole thing spits out the sum of all the sessions on the homepage for the current week. I’ve then subtracted from that number the sum of all the sessions for the previous week.

Finally, I’ve divided it by the sum of all the sessions of the previous week to get the percent change.I set formatting rules in these cells to format the numbers as a percentage, but you could also just add that the formula to multiply by 100. So within these cells there are two things you are going to have to customise (1) the names of the sheets being referenced, and (2) the criteria that define the pages that you want to report on. You’ll notice that in the top 20 pages, these are different depending on the page type (they have been intentionally changed for discretion).

Building visual reporting in Google Sheets was posted via Internet Marketing

CMWorld Interview: Eli Schwartz on Curiosity & SEO Around the Globe

Should marketers ignore international and inter-linguistic considerations when it comes to their SEO strategies?

Survey says: BZZZRRRT

In a way, optimizing for search is like participating in the game show Family Feud. To align with searcher behavior and intent, marketers must make educated guesses, and ultimately it is the people who will dictate whether we’re right or wrong.

As we develop content to draw visitors for targeted keywords, search rankings and traffic trends serve as either an affirming “Ding!” or a rejecting “Buzz!” regarding our hypotheses. Web users fulfill the same function as the random samples polled to create Family Feud’s survey response boards.

Eli Schwartz, who leads digital growth initiatives for SurveyMonkey as Director of SEO & Organic Product, spends his days immersed in data, and the actionable insight it can provide. As Google continually evolves its results to be guided less by keyword volume or technical inputs, and more by human behaviors (or machine algorithms designed to mimic them), marketing needs to deeply understand the way customers seek out information.

At Content Marketing World in September, Schwartz will urge us to extend that mindset beyond borders. In his session, “10x Your Content Audience by Going International,” he’ll preach the benefits of building out a global SEO strategy and what it entails.

In advance of his talk, we asked him about the changing search landscape, the importance of thinking internationally, and how smaller brands can gain an edge when competing against the heavy hitters.

What does your role as Director of SEO & Organic Product at SurveyMonkey entail? What are your main areas of focus and key priorities?

In my role, I am responsible for the growth of organic traffic to all SurveyMonkey properties and products. As the architect of our SEO strategies, I have a deep partnership with the product, design and engineering teams to ensure everything works together to achieve the best organic results. In addition to SEO, I also lead the efforts to ensure that traffic to our surveys ultimately leads to wider adoption and engagement by people that are not yet our users.

Our mission at SurveyMonkey is “Power the Curious,” a mission that carries through every aspect of the company from the way we interact with customers to how we innovate new solutions. Grounded in People Powered Data — insights on the people that matter most powered by the latest survey technology such as machine learning — we have been able to gain deeper insights on our users — a diverse set of individuals with equally diverse needs. It’s my job to ensure  that our website offers helpful content that will be visible every time a user seeks out a solution on Google where a survey could help them make better decisions. If someone is curious to learn more about People Powered Data in action, there is exclusive content and articles that we create for pages like Curiosity at Work. Whether it’s an HR rep looking to increase employee retention, a VP of customer service who wants to measure net promoter score, or a marketer who wants to understand their brand penetration, I make sure we have content that will help satiate their curiosity.

Once I tackle this challenge (and it never ends!) from a domestic and English standpoint, I replicate this exact same effort for every country where we have users. While the needs and the search queries differ between users all over the world, it is my mission to empower all curious leaders to discover the information that will help them reach their goals.


You have a great deal of background in search and SEO. What are the most striking changes you’ve seen on this front over the course of your career?

For as long as I have been working in SEO, the ultimate SEO best practice championed by the search engines has been to optimize for users and not search engines. However, the reality was that no one got ahead by following this widely accepted strategy. Content was stuffed with keywords, doorway pages brought users to unintended landing pages, whole websites were built around things like how to tie shoes or pour water, and there was a massive market in websites that sold links specifically for SEO purposes.

As Google’s algorithm advanced, these tactics stopped working and even caused penalty demotions in search. The ranking algorithms can detect whether content is of high quality and if the inbound links are contextually relevant enough to be natural. The net result is that Google’s algorithm uses artificial intelligence to mimic human behavior, and we are closer than ever to the truism of optimization for users and not search engines.

Google is light years ahead of the keyword driven search engine it was a decade ago. In fact, I recently saw an odd insect on the outside of my home,searched on Google by typing a description of the physical features of the insect — “long neck, wide wings” — and Google returned results that all had the word “snakefly” bolded as if that was the term I searched for! Google’s suggested results will change based on the time of day, your physical location, and your most recent searches. There’s no amount of bad SEO tactics that will help you rank when Google is inside the searcher’s head and knows exactly what they want.


Why is it so important for today’s marketers to think globally with their SEO strategies?

As evidenced by the furor around GDPR, which really is only an EU law designed to protect European citizens, we live in a global Internet world where users can come from anywhere. Fear of the penalties for GDPR violations are why the most American of all sites have cookie consent banners on their websites and notices of updated privacy policies.

Yet, most of the websites that were in a midnight panic to comply with GDPR have no European SEO strategy. To me, that’s a paradox that deserves a deeper look.

Serving a global audience begins with understanding them. By gaining insights on your audience through People Powered Data, you can create an SEO strategy that matters to them and reaches them in the vernacular in which they speak. To serve a global audience, your SEO strategy needs to be just as globally informed.  

Depending on the potential value of these global users, it may not be prudent to translate the full site or offer free global shipping, but translating that one page that targets the most important international keywords is not that complicated. Additionally, companies can take the very first step towards global SEO by just having a look at where and how their website ranks on Google internationally. They may very well find some low hanging fruit worth building a strategy around.

For us at SurveyMonkey, international audience is very important. Our new accounts (and traffic) outside of the US, especially on mobile, are growing at accelerating rates. For example, on the product side, we extended our SurveyMonkey Audience panels to 100+ countries in 2017 and those global panels are now accessible to customers in 60+ markets. We want to be able to support curious companies and individuals in all countries who are excited to use our products, and it starts with thinking globally to reach individuals globally.


What are some misconceptions you often encounter about international SEO?

The greatest misconception around international SEO is not believing that there is a global audience looking for your product or website. I spent nearly two years living in Singapore and, while I was there, I was given a glimpse into how much people outside the US seek American content and online shopping. Even more surprising to me was that it was not just name brands like Amazon that people were seeking out, but they were also figuring out how to get products from brands like JC Penney or Shoe Palace shipped to them.

International SEO is at the core of having people outside of the US become customers. A website might not even offer convenient shipping options, but, if users can find the products online, they may be willing to pay extra to get the item to their home country. To this vein, easy international SEO could mean having an option to pay for shipping in a specific country or creating tailored product pages translated into other languages.

 

Where does PPC fit into the global SEO equation?

In English, smart marketers use PPC to inform SEO and vice versa. Globally, companies might not find it worthwhile to conduct PPC campaigns since they don’t know enough about the market or local keywords to run a search campaign. However, if they are generating passive SEO traffic internationally and take the time to analyze the keywords and performance, this could be a great resource for finding low cost PPC ideas to generate conversions.

On another note, if a company is seeing international traffic, it is prudent to ensure that they are bidding on their brand name in the locations where the customer base appears to be growing. If there is enough awareness that customers are bringing themselves to the website via search, there’s a high chance local competitors see this as a great opportunity to bid on your brand. Bidding on your own brand will help protect the loss of those users.


Aside from search, where are some other tactical areas where marketers could stand to think more internationally?

The most critical ingredient of a global marketing campaign is for marketers to put themselves into the target market’s shoes. Making assumptions that global customers are exactly like us or creating hypotheses for why they might act a certain way is absolutely the wrong foundation for an international marketing effort.

From my time living overseas and many customer research meetings, the most interesting customer insight I have learned is around payments. Here in the US, almost everyone has a credit card in their pocket and is very quick to use it for almost anything. Outside the US, fraud protections aren’t as robust as they are here, making people much more judicious about credit card usage. In Asia, customers are liable for all purchases if their credit card is stolen until they put in a stop request. In that environment, people are obviously pretty cautious over who they might disclose their credit card number to. Additionally, outside the US, work expensing policies are a lot more rigorous than what we are used to. Employees have to go through multiple approval hoops even for small purchases and expect to be reimbursed for purchases on their personal cards.

Ultimately, what this translates to is: companies need to adjust how they can complete transactions in foreign countries. In Asia, it might mean making arrangements for cash payments and, in Europe, this often means accepting a bank transfer. Additionally, for B2B products, direct response might be a challenge if the customer base is inhibited from an impulse purchase without getting approval.


What can smaller companies do to gain an edge in visibility when competing against larger and more authoritative brands?

In my experience, there is a very clear advantage in search for bigger brands, and brands can establish a ranking on competitive terms with little to no effort. This might make it seem like smaller brands don’t even have a chance; however, big brands have a huge disadvantage in that they are very slow to react and usually don’t focus on search the way a smaller brand can.

Even with a limited budget, a small brand can practically drown a vertical with content before a big brand can complete their myriad of strategy review meetings. Smaller brands will also have some headroom from their first mover advantage and can remain ahead by of the bigger brands by being more innovative and agile with their content.

Testing concepts before you share your content out to the world can be really effective and help smaller brands get this edge. For example, when you have a big idea that you want to get right, it can save you a lot of time and money to concept test it beforehand — just recently we’ve created a guide for our customers, many of which are smaller brands, to help them start implementing concept testing to refine their ideas, from new logos and campaigns to websites and landing pages by getting feedback directly from your target market. By the way, you’d be shocked to learn how many small companies still don’t even have websites — we conduct this quarterly Small Business Survey with CNBC, and only about a half of business owners have websites they can drive customers to.


Which speaker presentations are you looking forward to most at Content Marketing World 2018?

There are so many presentations that I am excited about at Content Marketing World and I hope I get to all of them! The ones I am really looking forward are Jay Baer’s presentation on Killer Content, John Bucher on Storytelling, Dave Charest on Customer Experience, Dorie Clark on Long Form Content, Joe Pulizzi on the evolution of Content, Scott Monty on Knowing Your Customer, Megan Golden on LinkedIn for LinkedIn.

It’s Time to Play the Content Marketing Game

The game never stops, of course, but action will really fire up on September 4th when CM World gets underway in Cleveland. Will you be on hand for the fun?

To sharpen up ahead of the event, peruse the Ultimate Guide to Conquering Content Marketing by clicking through the slides below:


Email Newsletter
Gain a competitive advantage by subscribing to the
TopRank® Online Marketing Newsletter.

© Online Marketing Blog – TopRank®, 2018. |
CMWorld Interview: Eli Schwartz on Curiosity & SEO Around the Globe | http://www.toprankblog.com

CMWorld Interview: Eli Schwartz on Curiosity & SEO Around the Globe was posted via Internet Marketing

Digital Marketing News: Facebook’s Playable Ads & Business Pages Update, Gen Z Mom Trends, & B2B’s Video Uptick

Facebook Business Pages

Facebook redesigns biz Pages for utility as feed reach declines
Facebook has released a slew of changes to its popular Business Pages offering, including updates to mobile, recommendations, events, jobs, and Facebook Local. The updates bring marketers new opportunities along with the need to re-think certain strategies that may no longer be relevant. TechCruch

Twitter loses ability to let users auto-post tweets & retweets to Facebook
Facebook changed how its API is utilized by some 60K apps, including Twitter’s, doing away with cross-posted auto-tweets unless going through the more limited posting options of Facebook’s Share feature. Marketing Land

Move Over Millennials: It’s Time To Discuss How To Win With Generation Z Moms
An examination of digital native Gen Z moms and their online brand engagement traits and habits. Forbes

Making B2B video content work: marketers from Linkedln, Dailymotion and The Smalls share best practices
Marketers from LinkedIn (client), The Small, and Dailymotion take a serious look at what’s working in B2B video marketing, what isn’t, and why. The Drum

Facebook launches playable ads, tests retention optimization for app advertising
With Facebook’s recent launch, are playable ads likely to make their way into other, non-gaming areas of digital marketing? Marketing Land

‘Better ROI than influencers’: Meme accounts attract growing interest on Instagram
Brand and publisher partnerships look at engagement via meme, where even small follower counts can produce high engagement rates. DigiDay

2018 August 10 Statistics Image

We Analyzed 43 Million Facebook Posts From the Top 20,000 Brands (New Research)
A new study from Buffer and BuzzSumo examined Facebook posts from some 20,000 top brands, and results show posting volume has been up while page engagement has decreased. Buffer

Snapchat launches ad marketplace for Discover partners & brings Commercials to Ads Manager
Snapchat’s Private Marketplace and non-skippable ad options were among several new beta features recently rolled out to publishers. Marketing Land

ON THE LIGHTER SIDE:

Marketoonist Tom Fishburne ROI of Marketing Cartoon

A lighthearted look at the ROI of marketing by Marketoonist Tom Fishburne — Marketoonist

Anti-Poser CAPTCHA Asks User to Click ‘Every Real Punk Band’ — The Hard Times

TOPRANK MARKETING & CLIENTS IN THE NEWS:

  • TopRank Marketing — Top 10 Content Marketing Blogs on the Internet Today — Blogging.org
  • Lee Odden — 50 Tips for Ad Agency New Business — Michael Gass
  • Lee Odden — Natural Language Generation Accelerates Content Marketing, But Keep Your Hands on the Wheel
    CMSWire
  • Lee Odden — 9 Expert Guides: How to Win at Influencer Marketing — Marx Communications
  • Lee Odden — Main Stage Spotlight Speakers at Pubcon Pro Las Vegas — Pubcon

What are some of your top influencer marketing news items for this week?

Thanks for reading, and we hope you’ll join us again next week for the latest digital marketing news, and in the meantime you can follow us at @toprank on Twitter for even more timely daily news. Also, don’t miss the full video summary on our TopRank Marketing TV YouTube Channel.


Email Newsletter
Gain a competitive advantage by subscribing to the
TopRank® Online Marketing Newsletter.

© Online Marketing Blog – TopRank®, 2018. |
Digital Marketing News: Facebook’s Playable Ads & Business Pages Update, Gen Z Mom Trends, & B2B’s Video Uptick | http://www.toprankblog.com

Digital Marketing News: Facebook’s Playable Ads & Business Pages Update, Gen Z Mom Trends, & B2B’s Video Uptick was posted via Internet Marketing

How to Build a Resilient Evergreen Content Marketing Forest

Evergreen Content Marketing

Evergreen Content MarketingEvergreen trees are a symbol of life and renewal because they never lose their leaves; they stay green year-round.

Tell that to anyone who has dragged a Christmas tree to the garbage on New Year’s Day.

The truth is, evergreen trees are part of an ecosystem. When they’re rooted and cared for, they thrive and the whole forest benefits. Cut one down and take it home, and it won’t be green for long.

Evergreen content is the same way. We tend to think of it as stand-alone pieces of content that bring in traffic without any additional effort. Something isolated, set-it-and-forget-it. But if you treat your content that way, it will start dropping needles on the carpet with a quickness.

Here’s how to create, care for, and bolster a thriving content marketing forest with your evergreen content.

[bctt tweet=”Evergreen trees are part of an ecosystem. When they’re rooted and cared for, they thrive and the whole forest benefits. #EvergreenContent is the same way. – @NiteWrites #ContentMarketing” username=”toprank”]

How to Create Evergreen Content

As a famous content marketer (probably Ann Handley) once said:

Some posts are born to greatness; others have greatness thrust upon them.

Some evergreen posts will naturally arise from your content library. However, unlike a viral post, evergreen is something you can actually do on purpose.

Before you start adding to your content calendar, check out analytics and search data to find existing content that reliably gets traffic. You can promote and refresh these posts (more about that later), and you can use them as springboards to generate new content ideas.

Once you find your old-growth evergreens, put some new ones in your content calendar. The following types of post are good candidates for evergreen status:

Influencer Content

When you co-create content with influencers, you’re providing your audience with content that has authority, credibility, insight, and expertise. And that kind of content tends to have enduring interest for an audience — provided the influencer stays influential. 

Read: What is Influencer Marketing? Definitions, Examples, and Resources

In-depth Guides

It’s hard for short content to get attention now — there’s just too much mediocre, shallow content to compete with. But long-form content is having a renaissance. Give your readers something they can really dig into, something that covers everything they need to know about a specific, relevant topic.

How-To Posts

There’s no simpler value proposition than learning how to do something you need to do. Make sure your how-to is simple, direct, easy to follow, and not overly-promotional, and you can create a resource built to last.

Tool Roundups

These posts are both easy to create and incredibly valuable for the reader when done properly. There are hundreds of software tools available for virtually every industry. People need help navigating the landscape. Publish your comprehensive guide to the tools you and your brand find useful, and update it frequently as the landscape changes. Here’s an example of one of our own (recently refreshed) pieces on social media marketing tools.

Example of Refreshed Tools List

History or Timeline Posts

The internet tends to have a short memory. Content that captures the historical context of your industry, or technology that your audience uses at work, is bound to get attention and stay relevant over the years. Just make sure to update your timeline with the latest developments!

How to Promote Evergreen Content

In theory, evergreen content (when crafted with a best-answer mentality) should bring in search traffic without much extra attention on your part. But you shouldn’t settle for search alone. You know the content is resonating with your desired audience, so give it as much of a chance to be seen as possible.

Influencer Promotion

We always ask influencers to amplify content at least once, when it initially goes live. But it’s also okay to check back with influencers and ask them to promote again. There’s always a reason to reach back out: “Your post is almost to 10,000 views!” “We added some stats to your post!” “People have shared your post almost 5,000 times!”

Organic Social

Don’t make your social promotion a one-and-done proposition. Social feeds move so fast, and organic reach is so limited that posting the same content multiple times is practically required. Establish a cadence with unique messaging to consistently promote your evergreen content long-term.

Paid Social

You want to put your best content forward when investing in paid social, so it makes sense to promote content that has proven to have lasting value for your brand and your audience. Promote your evergreen content with ads and test different audiences with each post.

SEO

All content should be created with SEO in mind. But as algorithms and how people search evolves, make your content even more of a search engine magnet by sprucing up your SEO. Make sure your title and tags are optimized, and that your meta description makes a compelling case for clicking through. You can also take a look at competitor content that’s ranking for similar keywords and fill in gaps in your own content.

Newsletter

Email marketing is still one of the most powerful ways to reach an audience. Use your monthly/weekly newsletter to get your evergreen content in front of your most valuable audience — you already know the content is worth their time, so be bold.

How to Keep Content Fresh

Once your evergreen forest is growing and thriving, make sure to keep it up-to-date and relevant. Here are a few ways to keep your content ecosystem healthy.

Don’t Include Dates in Your URLs

Okay, I admit that we break this rule on the TopRank Marketing Blog from time to time. So do as I say, not as I do: Keep your posts timeless by not embedding the date in your file structure. Your how-to guide may still be relevant, but if the URL says 2010, people will be disinclined to read further.

Refresh on the Regular

When you audit evergreen content, look for elements that are no longer adding value for the reader. Trim irrelevant content, update outdated info, and check for dead links. If you have influencer material, make sure their job title and bio are up-to-date as well.

Trim Invasive Species

Odds are there are a few evergreens in your forest that you don’t want around. For example, you might have a post about services your company no longer offers, or on a topic your brand doesn’t want to be known for. If these evergreens are still pulling in traffic, they can hurt your brand’s reputation. Look for posts with high traffic but high bounce rates as well — rework or remove them to improve your search engine credibility.

O, Contentbaum

Let’s be honest: Most content is temporary. No one is digging deep into a blog’s archives looking for content when there’s plenty of new stuff out there. As the content keeps piling up, it’s more and more important for brands to invest in content meant to provide value for months, even years, to come.

[bctt tweet=”As the content keeps piling up, it’s more and more important for brands to invest in content meant to provide value for months, even years, to come. – @NiteWrites #EvergreenContent #ContentMarketing” username=”toprank”]

Start planting your evergreen saplings now, cultivate your forest, and you can make your site a destination for your audience.

How else can you power your evergreen content and overall content marketing strategy? Earning links. Learn how to create link-worthy content and see examples.

How to Build a Resilient Evergreen Content Marketing Forest was posted via Internet Marketing

What We Learned in July 2018: The Digital Marketing Month in a Minute

Daily Mail, Topshop, Argos flagged as ‘not secure’ in Google Chrome

The latest version of Google Chrome (Chrome 68 went live on the 23rd of July) displays security warnings for websites that still use HTTP. As of July 2018, approximately 20% of the top 500 websites worldwide utilise HTTP. Multi-million brands like Daily Mail, Topshop, Argos, Sky Sports and many more seem to have not switched to HTTPS yet, risking losing customers at the expense of secure competitor websites.

Read our blog to learn how to perform an emergency HTTPS migration using Distilled’s ODN and avoid the security warning.

Read the full story (BBC)

Facebook close to buying London-based start-up to fight fake news

Facebook is reportedly in the process of acquiring Bloomsbury AI, a London-based startup that develops natural language processing (NLP) technology to help machines answer questions based on information gathered from documents. Facebook has plans to use the new AI technology to address content issues, such as fake news.

Read the full story (Techcrunch)

EU fines Google €4.3 billion over Android

Google has been given a record fine by the EU for ‘illegal behaviour’ as it allegedly used Android to establish its ‘dominant position’ in by forcing manufacturers to use their services and thus make Google the search default. With Android being the dominant smartphone operating system in the EU (approximately 80% of all mobile phones use it), the tech firm is guilty of forcing manufacturers to pre-install Google apps and Chrome in exchange of their access to Google’s Play Store. As expected, Google has plans to appeal.

Read the full story (BBC)

How an Offer to Sell Wistia Inspired Us to Take On $17M in Debt

As those of you that are regular visitors to the Distilled blog will know, we love Wistia! Chris and the team have one of our favourite products out there. In this post they openly discuss how they chose not to sell the company, chose to take on a huge amount of debt to buy out investors, and continue to focus on building a brand that they love running.

Read the full story (Wistia)

Google Tag Manager not recommended for Structured Data

John Mueller shared this piece of news on Twitter, suggesting that it is not ideal to use GTM to implement structured data. In the past, there have been a lot of doubts around why Google had not shared any guidelines on how to implement structured data with Google Tag Manager. Despite this recent tweet, GTM still continues to be effective for certain SEO changes when there is no other way of getting a change made.

p>

Read the full story (The SEM post)

Facebook fined half a million pounds for data breaches

The Cambridge Analytica scandal continues as Facebook has recently been hit by a £500,000 fine for its involvement. The penalty derives from two breaches of the Data Protection Act since the tech giant was not able to protect users’ data and did not act transparently.

Read the full story (The Guardian)

Google Speed Update has rolled out to all users

Google has revealed that the Speed algorithm has been rolled out to all users, meaning that page speed is now a ranking factor for mobile searches. This update will only affect ‘pages that deliver the slowest experience to users and will only affect a small percentage of queries’.

Read the full story (Google Blog)

Google My Business Insights has a new report

Google My Business has launched a new report that aims to show the most frequent search queries that trigger a local listing.  This new feature, called ‘ queries to find your business’, will be rolled out to an increasing number of businesses starting from July ‘18.

Read the full story (Search Engine Land)

Referral source updated for Google Images

In news that will alleviate the difficulties webmasters have in tracking the role of Google Images in terms of driving traffic, Google Images traffic will now have a new referrer URL – which will automatically show up in Google Analytics. The URL will have the same ccTLD (country code top level domain) as the images search engine itself:  https://images.google.com is the default option, while https://images.google.co.uk will be used for google.co.uk.

Read the full story (Google Blog)

Google’s 2017 Webspam Report is released

As every year, Google has released the new annual Webspam Report. The main trends of 2017 were outlined (website hacking being the most popular), together with new guidelines to help webmasters prevent webspam.

Want to know more on how to detect spammy backlinks to your site? Read our blog post on how to identify bad backlinks in 2018.

Read the full story (Google Blog)

Millions of fake Twitter accounts shut down

Twitter has been working hard to detect fake and suspicious accounts since May with the intent of improving public conversation on the platform. The tech giant recently acquired Smyte, a business whose services focus on ‘keeping people safe online’, fighting spam, abuse and fraud.

Read the full story (BBC)

Google Job search feature available in the UK

After releasing Google for Jobs in the USA in 2017, Google has now expanded this functionality in the UK. Ad-hoc schema markup has been rolled out for job listings to be eligible in Google for Jobs.

Read the full story (Search Engine Land)

Speakable mark up released for news to be read by Google Assistant

Google has released new markup that allows publishers to tag parts of a news page to be selected and read by voice assistants, such as Google Home. This functionality is currently available in English only for publishers in the US. For more information on its implementation, Schema.org fully covers the speakable the markup here.

Interested in voice search? Read our blog post on 2018 voice marketing tactics.

Read the full story (Search Engine Land)

Distilled News

We kick off the Distilled news this month with a flurry of posts from Will Critchlow. On the back of Chrome 68 flagging sites as insecure, Will shared a case study on how Distilled ODN was used to deploy an emergency migration getting a client onto HTTPS quickly, along with more information on how the ODN can help other enterprise businesses and new ODN features.

Off the back of his talk at SearchLove Boston Will continued to investigate Google research papers and shared his learnings on research into how children aged 8-11 are using image search to solve search queries.

Analyst Sally Poundall took her recent experiences of performing backlink audits and wrote up the blog post she wishes she’d had before kicking off that task. Learn how to spot patterns in your backlink audit to speed up the process of spotting spammy links.

From the US side of the pond, our New York consultant Samiul Huque helps us understand what UX is and why it matters within SEO and highlights some terrible UX in the American elects.

Creative Lead, Jo Harris-Cooksley helps us through the tough times when the creative juices just aren’t quite flowing with her post and tips, “5 creative blockers and how to break through them”.

Feel free to leave any comments below, or come and say hi to us over on Twitter. Until next time!

What We Learned in July 2018: The Digital Marketing Month in a Minute was posted via Internet Marketing

Design for Dummies: 5 Tips to Liven Up Your Written Marketing Content

Design Tips for Non-Designers

Marketing Design Tips for Non-DesignersI’m not a designer. I’m not artsy. I didn’t even stay at a Holiday Inn Express last night.

I’m a writer to the core. But, as much I love words, I recognize that they don’t jump off the screen and grab people’s attention. Let’s face it: even the most beautiful prose is pretty ugly, from an aesthetic standpoint. You’re not going to find a framed portrait of blocky text hanging at the Guggenheim anytime soon.

As a marketer who is admittedly lacking in design skills, I’ve done an awful lot of reading on the subject, and I do my best to soak up knowledge and advice from the awesome group of visual wizards here in the TopRank Marketing design department.

My belief is that any writer who wants their work to be seen should be seeking to sharpen expertise in this area.

Design is Essential to Content Marketing

A compelling and unique graphic on a social media link can be the deciding factor when it comes to earning a click. Once a visitor arrives at a blog post, they’re liable to quickly move on if there isn’t an eye-catching visual to immediately pull them in.

https://giphy.com/embed/5wWf7GR2nhgamhRnEuA

via GIPHY

These statistics help portray the vital importance of keeping design front-and-center:

“Design is content,” asserts Patrick Pineda, motion designer for TopRank Marketing. And he’s right: treating visual elements as separate add-ons for your content is a mistake. Without the help of integrated imagery, great writing can easily go unnoticed.

[bctt tweet=”Treating visual elements as separate add-ons for your content is a mistake. – @NickNelsonMN #ContentMarketing #Design” username=”toprank”]

The trouble, of course, is that your design department may be stretched thin or you need super quick turnaround. And while this is never an ideal scenario, if the need arises, you can create simple, professional-looking visuals that enhance your content by adhering to a few simple guidelines.

1. Find a Tool You Love

The emergence of user-friendly apps for graphic design has been huge for folks like me. No longer must we stare blankly at a Photoshop interface while trying to figure out the functional differences between three different paint-brush icons.

There are plenty of different options out there for executing simple design tasks. Find one you like and take some time to get comfortable with it. These apps are usually free up to some level, and offer efficiencies like drag-and-drop editing and templates.

Here are a few worth trying:

  • Canva: My personal go-to. Tons of templates, backgrounds, and free illustrations you can use.
  • Desygner: Very similar to Canva, with a focus on straightforward ease of use.
  • Piktochart: An intuitive tool for creating infographics.
  • Pixlr: Super helpful for quick photo editing and resizing.
  • PicMonkey: Paid app with robust feature set

Here’s an example of a recent ad template in Canva. It’s sharp and simple, and if the design and dimensions are what you’re looking for, all you need to do is edit the text.

Canva Web Ad Template Example

2. Think About Design at Every Step

Don’t treat visuals as an afterthought. Instead, build them into your content planning. When developing new concepts, think not only, “How can I write about this in a compelling way?” but also, “How can I illustrate these ideas?”

Just as written content should be strategic and purposeful, so too should visual content.

“I always ask: What’s the story? Who’s the audience? Where will my design reside?” says Pineda.

Here’s an example of a pull-quote design template from Canva. Whether you’re conducting an interview or there’s a specific takeaway you want to call out in your writing, consider turning it into a graphic to add a little pizzazz.

Canva Quote Design Template

3. Simplicity is Golden

Our eyeballs are drawn to striking visuals, but they’ll be quickly repelled by overly busy graphics. Focus on conveying the necessary information as clearly and cleanly as possible. Modern design is often defined by its simplicity (think Apple or Nike).

In the words of Antoine de Saint Exupéry:

It seems that perfection is attained not when there is nothing more to add, but when there is nothing more to remove.

4. Colors Have Feelings

Be thoughtful in your color choices. Not only is it important to stay on-brand, but colors also encompass their own spectrum of emotions. This awesome Color Emotion Guide infographic from Visually offers a framework:

Color Emotion Guide from Visually

5. Get Creative with Fonts and Layouts

The trouble with using free online design apps is that, well, lots of other people use them, too. And when you’re relying on the same default fonts and templates, your productions will inevitably end up looking like much of what’s already out there.

Whenever possible, add unique touches and flares. Maybe your company’s designers are too busy to create graphics for each blog post you write, but can provide a few customized templates or fonts for you to upload and use.

Make sure you’re balancing creativity with readability. Poppy elements to catch the eye are critical, but you always want viewers to easily find and understand the message.

[bctt tweet=”When it comes to designing visual content elements, balance creativity with readability. – @NickNelsonMN #ContentMarketing #Design” username=”toprank”]

An Eye on Better Marketing Design

When I need a high-quality visual asset for a client, our tremendously talented TopRank Marketing design team is always my first stop. But for quick one-off graphics to promote or accompany a blog post, these practices have proven really critical.

The design below took all of 10 minutes to put together in Canva. Honestly, the majority of the time was spent on finding just the right background photo. But, it looks pretty sharp, if I do say so myself.

Design Tips for Non-DesignersIf you can find the time, I recommend taking introductory design classes or working alongside your design team to see what you can pick up. Any skills you’re able develop in this regard as a writer will be helpful as we forge ahead into the era of content saturation and shortening attention spans.

Speaking of captivating visual imagery, there may be no more important platform to add some of that wow-factor than on social media. But you have to select the right visual content for the right platform. Learn best practices for choosing effective social media visuals.

Design for Dummies: 5 Tips to Liven Up Your Written Marketing Content was posted via Internet Marketing

What is SEO split testing?

Last week I tweeted an explanation of how we know that an increase or decrease in SEO performance was caused by a change that we made or by an external factor like seasonality, competitors, Google updates etc. People found it helpful and it generated a lot of questions so I thought it would be useful to post a more detailed explanation on what exactly SEO split-testing is as there seems to be a lot of confusion/misunderstanding.

One quick thing: This is deliberately a simple example with a basic explanation of the maths that we use. In reality, the maths is a lot more complicated and based on this research by Google: Inferring causal impact using Bayesian structural time series.

The purpose of this presentation isn’t to teach or explain the maths behind the ODN, it’s to, hopefully, explain the core concepts in a simple way, that allows you to imagine applying this methodology to websites with a lot more than 4 sub-category pages 🙂

If you want to dig into the testing methodology in detail, then you can visit: https://odn.distilled.net/learn-more/faqs/

With that out of the way, let’s get started.

Imagine a basic website

The site below has two simple categories, animals and countries. It has 8 sub-category pages (cats, dogs, Scotland etc.)

  • All of the animals sub-category pages use the same template
  • All of the countries sub-category pages use another template

This is critical to understand because SEO split testing is predicated on the concept of testing changes to page templates. A group of pages that share the same template can be used for SEO split-testing.

In the animals sub-category example above you can see that although the content of each page is different, they all follow the same template.

They have:

  • An H1 at the top of the page
  • A block of intro copy
  • A featured image

We could create an animal template test:

Or a countries template test:

But you can’t mix templates:

An example experiment

Imagine that we wanted to test a new Animal page template by replacing the image with a video and removing the intro copy from the animals sub-category template.

For the test to be a valid experiment, we need a set of pages to remain un-changed (the control group) and a set of pages to have the new proposed design (the variant).

Distilled’s ODN platform uses advanced maths to decide which URLs should remain on the control template and which should get the variant template.

For simplicity in this example, you can think of this as selecting URLs at random to be on each template.

In this example, the test is on 50% of pages, but you could do this on a smaller or more significant percentage of pages. During the experiment, the site would look like this:

Notice that the /cats and /badgers pages now have the new template and the /dogs and /unicorns pages remain unchanged and have the same design they have always had.

The results

The graph below shows the organic performance of the cats and badgers pages versus the dogs and unicorns pages.

Notice that just after the test started, there was no change in the difference in organic performance. That’s because Google needs to crawl the pages. Depending on the number of pages that are being tested, the amount of time that this takes can vary.

Over time we notice that the variant pages start to outperform the control pages. Once the test reaches statistical significance, we can declare the test a success and recommend that the changes be rolled out to 100% of pages instead of just 50%.

How do you know it wasn’t just seasonality?

This is a common question and you can replace seasonality with pretty much anything you like:

  1. Seasonality
  2. Google rolled out an update
  3. Competitors’ performance decreases
  4. Backlinks to your site
  5. TV campaigns
  6. Branding/direct traffic
  7. Other macro factors

By having a control group of pages that have the same intent/theme/template we can exclude external factors like seasonality because the control group of pages would also be impacted.

The analysis isn’t looking at the trend of the traffic; it’s looking at the difference in performance between the control group and the variant.

In other words, if it was seasonality, for example, Christmas, there’s no reason why /cats and /badgers would be impacted but not /dogs and /unicorns. The same would apply for something like a Google update.

Seasonality would look like this:

Although there is an upward trend, the difference in performance between the control pages and the variant pages is the same as before the test began. This example experiment would be declared as a neutral test despite that after the change was made, organic traffic went up significantly.

I hope this makes SEO split-testing easier to understand.

If you want to know more or are interested in doing SEO split-testing, the ODN is a piece of software that lets you do that. Find out more at https://odn.distilled.net/ or click the button below to contact us so I can set up a call to show you a demo of the software.

BOOK AN ODN DEMO

What is SEO split testing? was posted via Internet Marketing

SearchLove London 2018 Community Speakers Announced

SearchLove London 2018 - Will Critchlow and Kirsty Hulse

Dim the lights and start the drum roll: the time has finally come to reveal the first ever SearchLove community speakers. Our blurry-eyed events team have come out of their top secret room having watched hours of footage from over 50 excellent applicants, and are ready to share their chosen three.

So, the wait is over and we are proud to announce that our community speakers are (in alphabetical order), Andi Jarvis, Luke Carthy and Laura Hogan. We are stoked to have all three on board and can’t wait to get them into Distilled HQ to work on their presentations, and then get them on stage in front of 400 or so people at SearchLove London.

BUY SEARCHLOVE LONDON TICKETS

Let’s find out a little more about our community speakers.

Andi Jarvis

Andy JarvisAndi is the Founder and Strategy Director of Eximo Marketing, a marketing strategy agency based mainly in Belfast and occasionally in Liverpool. Originally from Bradford, Andi has worked with a number of global brands, professional sports clubs and media companies to develop and implement effective marketing strategies.

He holds an MSc in Marketing from Ulster University and also runs a fundraising project, Wifi Refugees, where marketers volunteer to provide consultations to small businesses in return for a donation to a local refugee charity.

You should also know that Andi likes to talk, at length, about: being a dad, his BBQ, rugby league and cricket.

SearchLove topic: Behavioural / buying psychology

Many marketers think marketing is about the latest tech, or advertising hack or way to game Google. But it’s not. At its heart, marketing is about people. This presentation – People are complex, here’s what you need to know to sell to them – is about the bad decisions people make, why they make them and how you can use this to make better marketing decisions.

Delving into academic research to find studies about how people behave, outline the implications and give examples of best practice from companies making the most of this knowledge. All with a smattering of abuse aimed at Donald Trump and golfers.

Laura Hogan

Laura HoganLaura is the Director of Search at Ricemedia, with over eight years experience in the industry, both in-house and agency side.  She’s worked across campaigns for clients including Vax, Forever Living, HEWI London, Ivanti, Diamond Heaven and more.

When Laura’s not smashing search, you’ll find her snuggled with her Beagle Milo, watching trash TV and inhaling a Kinder Egg.

SearchLove topic: Brand Protection / Reputation Management

Branded search is vital to all our strategies – from direct traffic, to PPC, Social and Organic. If we put together the traffic volumes from all these channels, it will be a high percentage of overall.

It feels like we often forget about our branded real estate in SERPs, when realistically if there’s negative press about you on page 1 this will put potential customers off. (We know that 73% of people look at reviews prior to purchase).

This session will give actionable advice (with case studies and examples) of what you can do to help remove those negative pieces and how you can make sure you dominate for your brand.

Luke Carthy

Luke is a Digital Lead over at MayFlex, and took his first steps into digital marketing aged 16 when he created his first eBay store. From that point onwards a romance with ecommerce and digital was created.

SearchLove topic: How to nail SEO for discontinued products

Many businesses have a habit of simply booting their discontinued items off their websites once they’re discontinued / end of line. Great for housekeeping and keeping things lean, can be a huge “hell naw” when it comes to SEO.

  • What about all those backlinks from reviews and features posted on blogs and online magazines?
  • What about all the traffic that your now discontinued, best selling kitchen accessory used to pull in?
  • What about all of the potential cross-sell opportunities you’ve lost as a result of killing the web pages for thousands of discontinued lines?
  • What about the user experience? Simply showing a 404 in place of the product or redirecting to the home page just leaves visitors cold!

This session will be covering the right way to optimise discontinued products, provide an awesome UX for customers, recycle ‘link juice’ and lap up all cross sell opportunities too!

Join us and our community speakers at SearchLove London

There are still early bird tickets available to attend SearchLove London 2018 to see our community speakers and 16 other expert speakers from across the globe. Our early bird tickets will save you £200 off full price tickets and it’s worth remembering that SearchLove London has completely sold out for the last 2 years and this year’s flash sale tickets sold out in record time. You’d best head over to our ticket page and secure your place before it’s too late. See you in October!

BUY SEARCHLOVE lONDON tICKETS

SearchLove London 2018 Community Speakers Announced was posted via Internet Marketing