As a digital analyst, I probably end up using Excel just every few hours. 

I think there are a lot of tricks/formulas that analysts can use to better explain the story. Besides your reporting platform, having the ability to wrangle data in Excel for ad-hoc analyses definitely gives you flexibility to shape your story. I'll use examples that analysts will find themselves running into...hopefully, this post will help.

1. Finding out which posts were contest based [and therefore, had a higher engagement rate] 

Contest posts usually perform very well and when you're evaluating content, you should mark them as outliers or include it but point it out to others. Knowing the impact of contest posts for always-on content helps you plan better.

How would you find this out in Excel?

Your contest post message copy could contain a hashtag or not. But one thing's given, it'll probably have the words such as 'contest', 'win' in the copy to ring a bell with the audience and get 'em participating.

Here's a screenshot of dummy data from Facebook Insights post level data in csv.

Within the text, the formula checks for the word "contest" in D5 using the SEARCH function...If it finds the word, it tells you the ordinal position from where the word starts. Once you have that, you ask Excel if this cell is now a number? As you have an ordinal position, it's a TRUE....and if it's a TRUE, you can now use an IF function to label the column as "Contest Post"...if FALSE, it goes as "Non contest post"...Put this into Pivot tables and see how contests help bump up engagements.

2. Extracting Twitter profile names from Tweets you sent out, adding the URL to their profile as text and hyperlinking it.

Say, you had a really good month and engaged with a host of Twitter folks. Now that you've broken the ice with 'em, your client has asked for a list of users you've interacted with...hmmm.

How would you do this in Excel?

Once you download your sent tweets from Twitter, your pattern is that the tweets sent out to users have a space between the user's handle name and the actual copy...You do have two challenges:

1. The challenge here is that each user will have a different length[characters] for username...so your pattern is the space between user profile and the tweet " ".

2. Your brand's tweets are going to be in some rows in between and we need to first know if a tweet was sent to an individual user or posted as part of the content calendar?

Ok, so there's quite a bit going on here...let's break it down:

We only want to extract the user profile links from posts that are sent out to other users...brand tweets posted from the content calendar need not be included here...

We use an IF function to check if the first character of the text is "@"...this would mean that that we sent a tweet to an individual user...

If yes, We use the MID function to start extracting the text from the second position,

We continue extracting text in the MID function till we find " " [space between user handle and copy]...we then subtract 2 characters from it to be exactly till the end of user profile.

We then append a text string to it with "www.twitter.com/".

and finally, we hyperlink the user profile link using the HYPERLINK function....

IF the first character is not "@", then it's part of the FALSE condition and we just label it as "brand post".

fun, right?

3. Finding out engagement rates by audience tags being used in Facebook posts.

With the new preferred audiences tags in Facebook, post data download now has a new column 'audience tags'. What if you wanted to know which tags are helping posts with engagement rates?

How would you do this in Excel?

You could easily use a Text to Columns feature under Data tab to separate out the tags into columns like this...

Now you have the number of tags used and the engagement rates for posts...

You could look at relationships to find out the ideal number of audience tags....

You could create a new column to check if generic/branded/competitor audience tags (using the function in the first tip) perform better in terms of engagement rate.

4. Finding out which posts contain a question

So you already know the performance on link/photo/video/status posts...what if you could add a layer in your pivot table to segment the data for question posts...check if they get more comments from your community...

So you used the steps in the first tip to find '?' in the text...but this time, column D wrongly says that text "we've read" is a question post when it isn't...

This is because '?' is a wildcard operator and Excel cannot understand that you're trying to find cells that contain '?'

How would you find this in Excel?

Adding the tilda key in the SEARCH function tells Excel that you're NOT using the question mark as a wildcard operator...

Now when you run the same nested function from the first tip, column E correctly returns "general" for the last text post.

That's all the time I have now...will keep adding posts as part of 'Tricks for digital analysts' series.

If you're looking for help with Excel, some cool places to learn:

• Mr. Excel forum

• Excel subreddit

• Excel Is Fun YT Channel

• Chandoo.org

N'joy!

Adil