Connect with us

SEO

The Top 19 Tools For Managing Social Media Accounts

Published

on

The Top 19 Tools For Managing Social Media Accounts

Social media has become a massive part of brand marketing strategy. And managing multiple accounts can be pretty overwhelming.

How do you stay organized? What tools should you use to manage social media accounts?

Social media platforms like Facebook, Twitter, and Instagram, have evolved into a significant source of information for millions of people around the globe.

As such, they play a crucial role in shaping public opinion.

In addition, these platforms also provide businesses with powerful marketing opportunities to connect with and grow their audience.

Since scheduling posts and analyzing social media insights is now a must for any content strategy, it’s vital to equip yourself with the right tools.

So now, let’s break down the top tools for social media management:

MeetEdgar is fun and easy to use. You can pick topics like recipes, humor, or questions and schedule these types of subjects to publish on any day you want.

Screenshot from meetedgar.com, August 2022

If you ever get into a content lull with social media, which everybody does, MeetEdgar is an excellent solution.

MeetEdgar will add a bit more fun to your social media calendar. It only publishes content that Edgar thinks is worthy of engagement.

One of the best features of MeetEdgar is that once your favorite topics are defined and scheduled, Edgar ensures the content will never run out, so you don’t have to refill or pick new topics constantly. Instead, Edgar does it for you.

Once everything from a topic has been published, Edgar starts to reshare old content that people may have missed the first time around.

So, considering the limited amount of organic reach on social media, resharing content gives it another chance for engagement.

Pricing Tiers: Free trial, then $29.91 or $49.58 monthly.

Post Planner is one of my personal favorite social media content tools. To see what types of content are shared around a specific subject or person, search by:

  • Topic.
  • Trend.
  • Keyword.
  • Hashtag.
  • Facebook.
  • Twitter handle.

Search their recommended feeds based on industry and interests and explore new content ideas.

The tool will also give each piece of content a performance engagement ranking. Based on Post Planner’s custom algorithms, this ranking helps determine whether a post is worth sharing.

Post Planner can determine such ratings by reviewing past performance and predicting future engagement specific to your audience. You also get a scheduling calendar with Post Planner. And it has numerous pricing tiers for any budget.

Pricing Tiers: $6, $19, $39, $79, or $349 monthly, with a discount when paid yearly.

Agorapulse has numerous products, such as kits for social media publishing, social media inbox management, and social media monitoring.

Agorapulse webpageScreenshot from agorapulse.com, August 2022

In addition, it has options for multiple levels of integration and tools to help a brand manage and analyze its social media accounts.

Pricing Tiers: Initial free account, then $79 or $199 monthly. It also offers custom packages at the enterprise level and a free trial.

Sprout Social is one of the best social media management tools. More than 30,000 companies, like Glassdoor and Shopify, trust Sprout for:

  • Social media scheduling.
  • Engagement reports.
  • Analytics.
  • Brand monitoring.

Whether you’re a small business focusing on a few social media networks, an agency managing multiple brands, or an enterprise company needing it all, Sprout Social will help you save time and accomplish your daily social media tactics.

Pricing Tiers: Free 30-day trial, and then it’s on to a monthly plan of $99, $169, or $279.

Hootsuite allows you to connect, monitor, and schedule posts for numerous social networks. This tool is fantastic and offers an array of social media features.

This platform can integrate over 150 apps into your social media strategy.

In addition, the platform is available in six languages if you have an international team and following.

Pricing Tiers: 30-day free trial, then $49 or $179 monthly. If you’d like enterprise management, you’ll need to request a demo to grant access to Hootsuite’s custom solutions.

Sensible understands the ever-changing world of social media and aims to stay on top of trends and insights to help brands thrive.

Sendible webpageScreenshot from sendible.com, August 2022

It also recognizes the importance of authentic storytelling and offers opportunities to optimize your profiles across multiple platforms. It is also a Meta business partner.

In addition, Sendible highlights its customer service.

Pricing Tiers: Free 14-day trial, then $29, $89, $199, $399 monthly. You can save by paying yearly.

When it comes to social media tools for Pinterest, there aren’t a lot of options. But there is Tailwind.

Tailwind provides data for both Pinterest and Instagram, the two image-based social media networks.

Once you have the tool, you’ll be able to track when someone pins an image from your website or engages with an Instagram post.

In addition, it lets you generate hashtag data, monitor brand mentions across the two social sites, and more.

You can start a free trial to see if this social media measurement tool is right for your Pinterest and Instagram accounts.

Pricing Tiers: $9.99, $19.99, and $39.99 monthly. It also offers a free version so you can test its tools.

Buffer is a famous social media tool. You can share content and schedule posts on Facebook, Instagram, Twitter, LinkedIn, Pinterest, and Google Plus with one easy click.

Buffer allows you to stagger content throughout the day so that your social media feeds are consistently updated. Plus, you can schedule ahead of time, which is handy.

Also, you can use Pablo Image Creator to create custom graphics. And you can review analytics about engagement and reach of your posts.

If you own or work for a registered nonprofit organization, you must look into Buffer. It offers all registered nonprofit organizations a 50% discount (on what is already competitive pricing).

Pricing Tiers: Free, $6, $12, or $120 monthly. It also offers yearly discounts and a 14-day free trial.

Crowdfire is a super cool app. It’s designed to help drive traffic to your website and landing pages while increasing social engagement.

Crowdfire webpageScreenshot from crowdfireapp.com, August 2022

Crowdfire will make recommendations designed to help improve your social media strategy. Here’s how:

  • Crowdfire connects to your social networks and begins to get to know your brand.
  • Then, Crowdfire takes you through a few quick daily tasks that are personalized to your goals.
  • Finally, it begins to create and find content that your audience will love and posts for you once you give it the thumbs up.

Pricing Tiers: Free or $9.99, $49.99, or $99.99 monthly.

MavSocial is for brands with multiple locations. This platform helps you consolidate management all in one place while offering tools to take your brand to the next level.

You can manage publishing, engagement, reviews, and advertising for your locations at MavSocial. It also uses two-step verification and AI Image Recognition tools to secure the platform.

Pricing Tiers: 14-day free trial and then $78, $199, and $499 monthly.

Later helps brands get full coverage through auto-publishing, hashtag suggestions, and insights for the best times to post.

Later webpageScreenshot from later.com, August 2022

It also has options for brands to add Linkin.bio so customers can quickly check out your website.

Pricing Tiers: $18, $40, and $80 monthly. It also has a free plan; you can save by paying yearly.

Manage multiple Twitter profiles? This is the perfect Twitter tool for you!

The dashboard may remind you of Hootsuite, but it’s different and customized just for your Twitter needs. TweetDeck:

  • Organizes your Twitter account by building collections, filing lists, searches, activity, and more.
  • Tracks topics, events, and hashtags to help brand monitor and stay in the know of trending conversations.
  • Manages multiple Twitter accounts and lets you tweet, monitor, and follow from one dashboard. In addition, TweetDeck completely removes the hassle of logging in and out.

SOCi is another excellent scheduling tool for social media. However, it offers something slightly different than the normal Hootsuite and Sprout Social plans.

If Post Planner and Sprout Social combined, the result would be named SOCi.

One of the best parts of SOCi is the Content Center. This is where SOCi scores the social web with its unique algorithms to tell you what social content is engaging and what’s not.

SOCi then provides a numerical score and recommendations to you on what to post, similar to Post Planner.

In addition to content ideas and scheduling, it also socially provides review and reputation management, lead generation, reporting, and more.

Pricing Tiers: Unfortunately, SOCi doesn’t offer pricing details to the public. You’ll have to schedule a demo and discuss your exact needs to get a custom quote.

I’m a bit biased here as I work for the company, but Semrush is one of those one-stop-shop tools that recently improved its social portion.

Semrush webpageScreenshot from semrush.com, August 2022

Its emphasis is on keyword research and SEO. Still, the tool has over 55 features for content marketers, link builders, social media marketers, and every digital marketing role.

In addition, Semrush has numerous SEO, content, social media, competitive research tools, and an agency growth kit.

Pricing Tiers: $119.95, $229.95.95, or $449 monthly. Discounted rates are available for yearly plans.

Ecommerce businesses and Instagram users will love Foursixty. This tool is designed just for retailers who want to share user-generated content from Instagram on their websites to help increase sales and monitor their brand.

The idea behind Foursixty is to showcase a company’s products by featuring Instagram posts that users share and tag about the company.

The product display from Instagram can be customized and displayed across shopping pages, email newsletters, and just about anywhere you can embed code.

Seeing these Instagram posts help encourage shoppers to add additional items to their cart and follow along on Instagram.

Aside from the UGC angle and sales increase, Foursixty also lets you schedule Instagram posts from the platform.

Pricing Tiers: There’s a 21-day free trial, and then $50, $300, or $500 monthly.

This is a tool every social media manager should have.

Regardless of what scheduling tool you use, this is a tool you’ll want to log into daily to access everything you need to know about your social profiles.

With Emplifi, you get real-time insights for Facebook, Twitter, Instagram, Pinterest, YouTube, and LinkedIn.

In addition, you can track engagement, share reports, and see social advertisement results on one platform.

But this social media tool does more than measure analytics. You can generate reports to compare the performance on social media profiles or industries, which means you can track your competitors and the industry’s social performance.

What an incredible opportunity to one-up your social competitors!

Pricing Tiers: You can request a free trial or book a demo to uncover options for pricing.

Need to know how your Twitter or Facebook account measures up?

Brandwatch webpageScreenshot from brandwatch.com, August 2022

Brandwatch offers everything you need to know about performance and engagement for multiple social platforms.

Pricing Tiers: Book a meeting here to get information on pricing.

Finding it difficult to find people to collab with? BuzzSumo is the perfect place to connect with the right influencers for your brand.

BuzzSumo can help you:

  • Find influencers.
  • Assess the best ones per platform.
  • Manage notifications for when they post.
  • Consolidate and download analytics.

Another bonus? You can search for key influencers by topic and start following them directly inside BuzzSumo.

Once you have followed a few key influencers, you can see what content and topics they share most often and the domains they share.

See what’s happening here? You can follow and see influencer and competitor data within the tool.

Pricing Tiers: Free 30-day trial, then $99, $179, or $299 monthly. You can also have them bill annually at a discount.

Bitly is a must. Not every tool allows you to shorten your ugly URLs, but Bit.ly does, and it has done so for over 30 billion links so far.

With multiple social media networks, you will want a unique tracking parameter on each URL, not one that the social media users will see.

Bit.ly allows you to shorten, customize, and track URLs, making lengthy coded URLs into neat, pretty little URLs.

Pricing Tiers: Free initial account and then $8, $29, or $199 monthly. It also offers custom plans and a yearly discount.

Final Thoughts

Social media has become an integral part of modern communication. And if you want to use it effectively, you need the right tools.

Many apps and services can help you manage and analyze your accounts.

Now, it’s time to take a deeper look into the platforms I’ve mentioned so you can alleviate some of the heavier aspects of social media management.

More Resources:


Featured Image: Rawpixel.com/Shutterstock



Source link

SEO

Essential Functions For SEO Data Analysis

Published

on

Essential Functions For SEO Data Analysis

Learning to code, whether with PythonJavaScript, or another programming language, has a whole host of benefits, including the ability to work with larger datasets and automate repetitive tasks.

But despite the benefits, many SEO professionals are yet to make the transition – and I completely understand why! It isn’t an essential skill for SEO, and we’re all busy people.

If you’re pressed for time, and you already know how to accomplish a task within Excel or Google Sheets, then changing tack can feel like reinventing the wheel.

When I first started coding, I initially only used Python for tasks that I couldn’t accomplish in Excel – and it’s taken several years to get to the point where it’s my defacto choice for data processing.

Looking back, I’m incredibly glad that I persisted, but at times it was a frustrating experience, with many an hour spent scanning threads on Stack Overflow.

This post is designed to spare other SEO pros the same fate.

Within it, we’ll cover the Python equivalents of the most commonly used Excel formulas and features for SEO data analysis – all of which are available within a Google Colab notebook linked in the summary.

Specifically, you’ll learn the equivalents of:

  • LEN.
  • Drop Duplicates.
  • Text to Columns.
  • SEARCH/FIND.
  • CONCATENATE.
  • Find and Replace.
  • LEFT/MID/RIGHT.
  • IF.
  • IFS.
  • VLOOKUP.
  • COUNTIF/SUMIF/AVERAGEIF.
  • Pivot Tables.

Amazingly, to accomplish all of this, we’ll primarily be using a singular library – Pandas – with a little help in places from its big brother, NumPy.

Prerequisites

For the sake of brevity, there are a few things we won’t be covering today, including:

  • Installing Python.
  • Basic Pandas, like importing CSVs, filtering, and previewing dataframes.

If you’re unsure about any of this, then Hamlet’s guide on Python data analysis for SEO is the perfect primer.

Now, without further ado, let’s jump in.

LEN

LEN provides a count of the number of characters within a string of text.

For SEO specifically, a common use case is to measure the length of title tags or meta descriptions to determine whether they’ll be truncated in search results.

Within Excel, if we wanted to count the second cell of column A, we’d enter:

=LEN(A2)
Screenshot from Microsoft Excel, November 2022

Python isn’t too dissimilar, as we can rely on the inbuilt len function, which can be combined with Pandas’ loc[] to access a specific row of data within a column:

len(df['Title'].loc[0])

In this example, we’re getting the length of the first row in the “Title” column of our dataframe.

len function python
Screenshot of VS Code, November, 2022

Finding the length of a cell isn’t that useful for SEO, though. Normally, we’d want to apply a function to an entire column!

In Excel, this would be achieved by selecting the formula cell on the bottom right-hand corner and either dragging it down or double-clicking.

When working with a Pandas dataframe, we can use str.len to calculate the length of rows within a series, then store the results in a new column:

df['Length'] = df['Title'].str.len()

Str.len is a ‘vectorized’ operation, which is designed to be applied simultaneously to a series of values. We’ll use these operations extensively throughout this article, as they almost universally end up being faster than a loop.

Another common application of LEN is to combine it with SUBSTITUTE to count the number of words in a cell:

=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1

In Pandas, we can achieve this by combining the str.split and str.len functions together:

df['No. Words'] = df['Title'].str.split().str.len()

We’ll cover str.split in more detail later, but essentially, what we’re doing is splitting our data based upon whitespaces within the string, then counting the number of component parts.

word count PythonScreenshot from VS Code, November 2022

Dropping Duplicates

Excel’s ‘Remove Duplicates’ feature provides an easy way to remove duplicate values within a dataset, either by deleting entirely duplicate rows (when all columns are selected) or removing rows with the same values in specific columns.

Excel drop duplicatesScreenshot from Microsoft Excel, November 2022

In Pandas, this functionality is provided by drop_duplicates.

To drop duplicate rows within a dataframe type:

df.drop_duplicates(inplace=True)

To drop rows based on duplicates within a singular column, include the subset parameter:

df.drop_duplicates(subset="column", inplace=True)

Or specify multiple columns within a list:

df.drop_duplicates(subset=['column','column2'], inplace=True)

One addition above that’s worth calling out is the presence of the inplace parameter. Including inplace=True allows us to overwrite our existing dataframe without needing to create a new one.

There are, of course, times when we want to preserve our raw data. In this case, we can assign our deduped dataframe to a different variable:

df2 = df.drop_duplicates(subset="column")

Text To Columns

Another everyday essential, the ‘text to columns’ feature can be used to split a text string based on a delimiter, such as a slash, comma, or whitespace.

As an example, splitting a URL into its domain and individual subfolders.

Excel drop duplicatesScreenshot from Microsoft Excel, November 2022

When dealing with a dataframe, we can use the str.split function, which creates a list for each entry within a series. This can be converted into multiple columns by setting the expand parameter to True:

df['URL'].str.split(pat="/", expand=True)
str split PythonScreenshot from VS Code, November 2022

As is often the case, our URLs in the image above have been broken up into inconsistent columns, because they don’t feature the same number of folders.

This can make things tricky when we want to save our data within an existing dataframe.

Specifying the n parameter limits the number of splits, allowing us to create a specific number of columns:

df[['Domain', 'Folder1', 'Folder2', 'Folder3']] = df['URL'].str.split(pat="/", expand=True, n=3)

Another option is to use pop to remove your column from the dataframe, perform the split, and then re-add it with the join function:

df = df.join(df.pop('Split').str.split(pat="/", expand=True))

Duplicating the URL to a new column before the split allows us to preserve the full URL. We can then rename the new columns:🐆

df['Split'] = df['URL']

df = df.join(df.pop('Split').str.split(pat="/", expand=True))

df.rename(columns = {0:'Domain', 1:'Folder1', 2:'Folder2', 3:'Folder3', 4:'Parameter'}, inplace=True)
Split pop join functions PythonScreenshot from VS Code, November 2022

CONCATENATE

The CONCAT function allows users to combine multiple strings of text, such as when generating a list of keywords by adding different modifiers.

In this case, we’re adding “mens” and whitespace to column A’s list of product types:

=CONCAT($F$1," ",A2)
concat Excel
Screenshot from Microsoft Excel, November 2022

Assuming we’re dealing with strings, the same can be achieved in Python using the arithmetic operator:

df['Combined] = 'mens' + ' ' + df['Keyword']

Or specify multiple columns of data:

df['Combined'] = df['Subdomain'] + df['URL']
concat PythonScreenshot from VS Code, November 2022

Pandas has a dedicated concat function, but this is more useful when trying to combine multiple dataframes with the same columns.

For instance, if we had multiple exports from our favorite link analysis tool:

df = pd.read_csv('data.csv')
df2 = pd.read_csv('data2.csv')
df3 = pd.read_csv('data3.csv')

dflist = [df, df2, df3]

df = pd.concat(dflist, ignore_index=True)

SEARCH/FIND

The SEARCH and FIND formulas provide a way of locating a substring within a text string.

These commands are commonly combined with ISNUMBER to create a Boolean column that helps filter down a dataset, which can be extremely helpful when performing tasks like log file analysis, as explained in this guide. E.g.:

=ISNUMBER(SEARCH("searchthis",A2)
isnumber search ExcelScreenshot from Microsoft Excel, November 2022

The difference between SEARCH and FIND is that find is case-sensitive.

The equivalent Pandas function, str.contains, is case-sensitive by default:

df['Journal'] = df['URL'].str.contains('engine', na=False)

Case insensitivity can be enabled by setting the case parameter to False:

df['Journal'] = df['URL'].str.contains('engine', case=False, na=False)

In either scenario, including na=False will prevent null values from being returned within the Boolean column.

One massive advantage of using Pandas here is that, unlike Excel, regex is natively supported by this function – as it is in Google sheets via REGEXMATCH.

Chain together multiple substrings by using the pipe character, also known as the OR operator:

df['Journal'] = df['URL'].str.contains('engine|search', na=False)

Find And Replace

Excel’s “Find and Replace” feature provides an easy way to individually or bulk replace one substring with another.

find replace ExcelScreenshot from Microsoft Excel, November 2022

When processing data for SEO, we’re most likely to select an entire column and “Replace All.”

The SUBSTITUTE formula provides another option here and is useful if you don’t want to overwrite the existing column.

As an example, we can change the protocol of a URL from HTTP to HTTPS, or remove it by replacing it with nothing.

When working with dataframes in Python, we can use str.replace:

df['URL'] = df['URL'].str.replace('http://', 'https://')

Or:

df['URL'] = df['URL'].str.replace('http://', '') # replace with nothing

Again, unlike Excel, regex can be used – like with Google Sheets’ REGEXREPLACE:

df['URL'] = df['URL'].str.replace('http://|https://', '')

Alternatively, if you want to replace multiple substrings with different values, you can use Python’s replace method and provide a list.

This prevents you from having to chain multiple str.replace functions:

df['URL'] = df['URL'].replace(['http://', ' https://'], ['https://www.', 'https://www.’], regex=True)

LEFT/MID/RIGHT

Extracting a substring within Excel requires the usage of the LEFT, MID, or RIGHT functions, depending on where the substring is located within a cell.

Let’s say we want to extract the root domain and subdomain from a URL:

=MID(A2,FIND(":",A2,4)+3,FIND("/",A2,9)-FIND(":",A2,4)-3)
left mid right ExcelScreenshot from Microsoft Excel, November 2022

Using a combination of MID and multiple FIND functions, this formula is ugly, to say the least – and things get a lot worse for more complex extractions.

Again, Google Sheets does this better than Excel, because it has REGEXEXTRACT.

What a shame that when you feed it larger datasets, it melts faster than a Babybel on a hot radiator.

Thankfully, Pandas offers str.extract, which works in a similar way:

df['Domain'] = df['URL'].str.extract('.*://?([^/]+)')
str extract PythonScreenshot from VS Code, November 2022

Combine with fillna to prevent null values, as you would in Excel with IFERROR:

df['Domain'] = df['URL'].str.extract('.*://?([^/]+)').fillna('-')

If

IF statements allow you to return different values, depending on whether or not a condition is met.

To illustrate, suppose that we want to create a label for keywords that are ranking within the top three positions.

Excel IFScreenshot from Microsoft Excel, November 2022

Rather than using Pandas in this instance, we can lean on NumPy and the where function (remember to import NumPy, if you haven’t already):

df['Top 3'] = np.where(df['Position'] <= 3, 'Top 3', 'Not Top 3')

Multiple conditions can be used for the same evaluation by using the AND/OR operators, and enclosing the individual criteria within round brackets:

df['Top 3'] = np.where((df['Position'] <= 3) & (df['Position'] != 0), 'Top 3', 'Not Top 3')

In the above, we’re returning “Top 3” for any keywords with a ranking less than or equal to three, excluding any keywords ranking in position zero.

IFS

Sometimes, rather than specifying multiple conditions for the same evaluation, you may want multiple conditions that return different values.

In this case, the best solution is using IFS:

=IFS(B2<=3,"Top 3",B2<=10,"Top 10",B2<=20,"Top 20")
IFS ExcelScreenshot from Microsoft Excel, November 2022

Again, NumPy provides us with the best solution when working with dataframes, via its select function.

With select, we can create a list of conditions, choices, and an optional value for when all of the conditions are false:

conditions = [df['Position'] <= 3, df['Position'] <= 10, df['Position'] <=20]

choices = ['Top 3', 'Top 10', 'Top 20']

df['Rank'] = np.select(conditions, choices, 'Not Top 20')

It’s also possible to have multiple conditions for each of the evaluations.

Let’s say we’re working with an ecommerce retailer with product listing pages (PLPs) and product display pages (PDPs), and we want to label the type of branded pages ranking within the top 10 results.

The easiest solution here is to look for specific URL patterns, such as a subfolder or extension, but what if competitors have similar patterns?

In this scenario, we could do something like this:

conditions = [(df['URL'].str.contains('/category/')) & (df['Brand Rank'] > 0),
(df['URL'].str.contains('/product/')) & (df['Brand Rank'] > 0),
(~df['URL'].str.contains('/product/')) & (~df['URL'].str.contains('/category/')) & (df['Brand Rank'] > 0)]

choices = ['PLP', 'PDP', 'Other']

df['Brand Page Type'] = np.select(conditions, choices, None)

Above, we’re using str.contains to evaluate whether or not a URL in the top 10 matches our brand’s pattern, then using the “Brand Rank” column to exclude any competitors.

In this example, the tilde sign (~) indicates a negative match. In other words, we’re saying we want every brand URL that doesn’t match the pattern for a “PDP” or “PLP” to match the criteria for ‘Other.’

Lastly, None is included because we want non-brand results to return a null value.

np select PythonScreenshot from VS Code, November 2022

VLOOKUP

VLOOKUP is an essential tool for joining together two distinct datasets on a common column.

In this case, adding the URLs within column N to the keyword, position, and search volume data in columns A-C, using the shared “Keyword” column:

=VLOOKUP(A2,M:N,2,FALSE)
vlookup ExcelScreenshot from Microsoft Excel, November 2022

To do something similar with Pandas, we can use merge.

Replicating the functionality of an SQL join, merge is an incredibly powerful function that supports a variety of different join types.

For our purposes, we want to use a left join, which will maintain our first dataframe and only merge in matching values from our second dataframe:

mergeddf = df.merge(df2, how='left', on='Keyword')

One added advantage of performing a merge over a VLOOKUP, is that you don’t have to have the shared data in the first column of the second dataset, as with the newer XLOOKUP.

It will also pull in multiple rows of data rather than the first match in finds.

One common issue when using the function is for unwanted columns to be duplicated. This occurs when multiple shared columns exist, but you attempt to match using one.

To prevent this – and improve the accuracy of your matches – you can specify a list of columns:

mergeddf = df.merge(df2, how='left', on=['Keyword', 'Search Volume'])

In certain scenarios, you may actively want these columns to be included. For instance, when attempting to merge multiple monthly ranking reports:

mergeddf = df.merge(df2, on='Keyword', how='left', suffixes=('', '_october'))
    .merge(df3, on='Keyword', how='left', suffixes=('', '_september'))

The above code snippet executes two merges to join together three dataframes with the same columns – which are our rankings for November, October, and September.

By labeling the months within the suffix parameters, we end up with a much cleaner dataframe that clearly displays the month, as opposed to the defaults of _x and _y seen in the earlier example.

multi merge PythonScreenshot from VS Code, November 2022

COUNTIF/SUMIF/AVERAGEIF

In Excel, if you want to perform a statistical function based on a condition, you’re likely to use either COUNTIF, SUMIF, or AVERAGEIF.

Commonly, COUNTIF is used to determine how many times a specific string appears within a dataset, such as a URL.

We can accomplish this by declaring the ‘URL’ column as our range, then the URL within an individual cell as our criteria:

=COUNTIF(D:D,D2)
Excel countifScreenshot from Microsoft Excel, November 2022

In Pandas, we can achieve the same outcome by using the groupby function:

df.groupby('URL')['URL'].count()
Python groupbyScreenshot from VS Code, November 2022

Here, the column declared within the round brackets indicates the individual groups, and the column listed in the square brackets is where the aggregation (i.e., the count) is performed.

The output we’re receiving isn’t perfect for this use case, though, because it’s consolidated the data.

Typically, when using Excel, we’d have the URL count inline within our dataset. Then we can use it to filter to the most frequently listed URLs.

To do this, use transform and store the output in a column:

df['URL Count'] = df.groupby('URL')['URL'].transform('count')
Python groupby transformScreenshot from VS Code, November 2022

You can also apply custom functions to groups of data by using a lambda (anonymous) function:

df['Google Count'] = df.groupby(['URL'])['URL'].transform(lambda x: x[x.str.contains('google')].count())

In our examples so far, we’ve been using the same column for our grouping and aggregations, but we don’t have to. Similarly to COUNTIFS/SUMIFS/AVERAGEIFS in Excel, it’s possible to group using one column, then apply our statistical function to another.

Going back to the earlier search engine results page (SERP) example, we may want to count all ranking PDPs on a per-keyword basis and return this number alongside our existing data:

df['PDP Count'] = df.groupby(['Keyword'])['URL'].transform(lambda x: x[x.str.contains('/product/|/prd/|/pd/')].count())
Python groupby countifsScreenshot from VS Code, November 2022

Which in Excel parlance, would look something like this:

=SUM(COUNTIFS(A:A,[@Keyword],D:D,{"*/product/*","*/prd/*","*/pd/*"}))

Pivot Tables

Last, but by no means least, it’s time to talk pivot tables.

In Excel, a pivot table is likely to be our first port of call if we want to summarise a large dataset.

For instance, when working with ranking data, we may want to identify which URLs appear most frequently, and their average ranking position.

pivot table ExcelScreenshot from Microsoft Excel, November 2022

Again, Pandas has its own pivot tables equivalent – but if all you want is a count of unique values within a column, this can be accomplished using the value_counts function:

count = df['URL'].value_counts()

Using groupby is also an option.

Earlier in the article, performing a groupby that aggregated our data wasn’t what we wanted – but it’s precisely what’s required here:

grouped = df.groupby('URL').agg(
     url_frequency=('Keyword', 'count'),
     avg_position=('Position', 'mean'),
     )

grouped.reset_index(inplace=True)
groupby-pivot PythonScreenshot from VS Code, November 2022

Two aggregate functions have been applied in the example above, but this could easily be expanded upon, and 13 different types are available.

There are, of course, times when we do want to use pivot_table, such as when performing multi-dimensional operations.

To illustrate what this means, let’s reuse the ranking groupings we made using conditional statements and attempt to display the number of times a URL ranks within each group.

ranking_groupings = df.groupby(['URL', 'Grouping']).agg(
     url_frequency=('Keyword', 'count'),
     )
python groupby groupingScreenshot from VS Code, November 2022

This isn’t the best format to use, as multiple rows have been created for each URL.

Instead, we can use pivot_table, which will display the data in different columns:

pivot = pd.pivot_table(df,
index=['URL'],
columns=['Grouping'],
aggfunc="size",
fill_value=0,
)
pivot table PythonScreenshot from VS Code, November 2022

Final Thoughts

Whether you’re looking for inspiration to start learning Python, or are already leveraging it in your SEO workflows, I hope that the above examples help you along on your journey.

As promised, you can find a Google Colab notebook with all of the code snippets here.

In truth, we’ve barely scratched the surface of what’s possible, but understanding the basics of Python data analysis will give you a solid base upon which to build.

More resources:


Featured Image: mapo_japan/Shutterstock



Source link

Continue Reading

DON'T MISS ANY IMPORTANT NEWS!
Subscribe To our Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

Trending

en_USEnglish