Factivate Bloghttp://blog.factivate.com
We discuss all things related to Digital Marketing Analytics and SpreadsheetsFri, 18 May 2018 14:20:05 +0000en-UShourly1https://wordpress.org/?v=4.9.6The Best Guide to UTM Campaign Tagging for Digital Marketershttp://blog.factivate.com/the-best-guide-to-utm-campaign-tagging-for-digital-marketers/
http://blog.factivate.com/the-best-guide-to-utm-campaign-tagging-for-digital-marketers/#respondFri, 18 May 2018 14:19:59 +0000http://blog.factivate.com/?p=999No functionality is as important to tracking the customer journey from paid ads into our digital web engagement as UTM campaign tagging. Yet, for many

]]>No functionality is as important to tracking the customer journey from paid ads into our digital web engagement as UTM campaign tagging. Yet, for many reasons, this functionality is one of the most misunderstood/mismanaged functionalities I have seen. Whether it’s been in my years helping fortune 100 marketers or working with small digital agencies, it’s always mismanaged. This is why I decided to stop having to talk about this every time I meet with a client and instead share my Best Guide to UTM Campaign Tagging for Digital Marketers with all of you.

I’m a huge proponent of UTM tags because it enables us marketers to track the effectiveness of all of our marketing efforts. Furthermore, it also helps us better understand user behavior beyond simply believing the numbers reported inside the analytics portal of the paid channel.

I am writing this guide because I believe that if marketers understood the real power of campaign tagging, with the level of data granularity they could achieve with it, then I can help marketers make more money…and let’s face it…we’re all about the cheddar.

What is UTM Campaign Tagging in Digital Marketing?

When a visitor comes into a site that has a Google Analytics tracking code installed, Google is able to capture information via data cookies: ie, where do they come from (source), what path they used to get here (organic, referral), gender, age, etc… Campaign Tags are able to overwrite this sometimes too generic information with your own custom information so that you can be specific with what Google should report.

Users can set up campaigns using “query parameters” that you add to links in your paid or unpaid marketing campaigns. When you do this correctly, Google Analytics will display when traffic comes to your site via these specific links.

Why do we need to Tag?

There are plenty of resources you want to tag all of your paid ad links. These include:

Campaign tagging consolidates all campaign data in a set of campaign reports. With these reports, you can often find real insights when you break down the data by segments.

If you’re running paid search campaigns outside of adwords, these visits will also show up as organic and give you a massive migrane.

If you’re running campaigns on social sites, Analytics will display this as referral traffic.

If you’re running a campaign via a social site, their “conversions” might be considered different from your Analytics definition of “goals” or leads. This can skew your data drastically. (Trust me, I worked with an agency that once reported 10,000 Facebook conversions, when actual revenues were closer to 3,000 conversions) BIG DIF ON CAMPAIGN ROI!

If you’re running email campaigns, campaign tags are critical. This is because traffic from email will be grossly under-reported if you don’t tag all links pointing back to your site. There are three primary causes for thi:

Visits from desktop email clients (ie Outlook) will show up in analytics as “direct” or medium = “(none)”

Visits from mobile show up as “direct” because of restrictions in passing referer data.

Suffice it to say that when you’re analyzing traffic in some way, you want to make sure you track everything correctly. This helps you accurately prove the value you’re providing as a marketer and optimize all of your campaigns using a common analytics language.

How to Build UTM Campaign Tags Correctly

Building a UTM tag requires more than going to Google’s Campaign Builder and simply copying and pasting the link in your ad. The best marketers I know maintain a UTM Campaign Library (often numbering in thousands of ads) with specific UTM naming conventions. When they do this, a great marketer can tell the ad’s objective, audience, segment targetted, source, medium, ad location, ad size, and more.

This is probably the most important part of this entire article and I recommend you try to use or expand on these lessons as I’ve had to learn them through some very painful lessons.

I’m essentially sharing my Campaign Tagging bible with you guys …so PAY ATTENTION IF YOU WANT TO MAKE MORE MONEY!

But before the cheat sheet version, let’s break down the parts of a “Campaign Tag”, starting with the big three: Medium, Source, Campaign.

What is a Campaign Medium?

Think of the medium as a channel category. You want mediums to be big, big platforms. Some of the mediums Google Analytics identifies include: organic, (none), referral, cpc, social. When tagging your links, you should follow the same form for your custom mediums. The reason you want these buckets to be big, is that it allows you to slice your data by source (ad channel) and campaign name when all of those numbers come in. If these categories are too spefici, then the segmentation becomes impossible.

Below is a list that I’ve used with my campaigns. While this list isn’t exhaustive, it’s been able to work for all of my clients throughout the past 10 years or so.

affiliate

banner

billboard

blog

cpc

direct

display

ebook

email

feed

online_radio

organic

paid_social

partner

pdf

ppc

presentation

print

qr_code

retargetting

social

tv

widget

press

Source

The source indicates where the link lives. It will track where the user also clicked on a link and it is critical that you are consistent in naming your campaign sources. Not doing so, can lead to Google Analytics reporting your twitter campaign data as t.co and twitter.com and you will have no idea why.

Note: t.co is twitter link’s wrapper. Before using t.co, links from desktop and mobile apps were showing up in analytics as direct traffic.

If you publish a link on Twitter, set your utm_source to twitter.com. If you publish on Facebook, then set it to facebook.com. Pretty straightforward. This list will expand as you work with more channels but hopefully you get the idea for why it is important.

bing

conference_brochure

constantcontact

duckduckgo

facebook

formsignature

emailsignature

google

instagram

linkedin

mailchimp

pandora

plus.google

snapchat

spotify

twitter

yahoo

event

Campaign Name

Most advertisers believe Campaign Name should be the name you’re currently giving to your advertising campaign. Strategic, well-organized marketing campaigns should have campaigns that use multiple mediums and sources. You will likely be testing different ads in different sources/mediums to see which one works best.

As easy as naming campaigns may seem, you should really think about it. Adopting a common campaign naming convention is CRITICAL to preventing confusion on different campaigns. For all of the companies I’ve consulted with, we’ve adopted a three pronged process to naming campaigns: Campaign Date_Campaign Audience_Campaign Name. When we combine all of these, we add the result as our UTM campaign name.

For my UTM Campaign Name, I would get: “daily_23to25_Reengagement_newsletter”. If I then want to review my campaigns, I can tell that this is a daily newsletter aimed a this audience with the objective Reengagement.

Here’s what it looks like in our UTM library

Campaign

Campaign

Campaign Goal/Name

Full Campaign Name

Date

Audience

(manual entry)

yyyymm_targetaudience_campaignname

daily

25to35

newsletter

daily_25to35_newsletter

Here are some of the biggest mistakes I see with this parameter when you don’t use a naming convention like the one I described:

Campaign Names are too narrow – I tend to see this with companies that send out a lot of emails. What they tend to do is give each day a unique campaign name. Sometimes, these emails follow different formats as well. Thus making it very difficult to group all of these visits.

Campaign names are too cryptic. Sometimes developers automatically generate campaign names (purposely obfuscating them) so that their competitors can’t figure out what campaigns they’re running. The problem is that this also makes this extremely difficult for marketers to understand. If it’s not feasible to rename these cryptic campaign names, set up a lookup table in your database to use for in reporting (it’s a pain though).

Campaign Names are too fragmented – Calling a campaign a unique name for each medium/source you publish to prohibits segmenting these campaigns later on. The more you can compare campaign performance across different channels, the better your strategy will become over time.

Campaign Names follow naming conventions that should be reserved for medium and/or source.

Campaign Content

The content parameter gives you additional details about your campaign. For paid search campaigns, you use it to differentiate between ads. However, you can also use it for other ads. Use campaign content to further help you group data in the following ways:

A proper Campaign Content naming convention will be a combination of things: Content Type + Content Position + Content Size. Below is a list of each of these categories that I’ve used over the years (note, the list is not exhaustive by any measure and I fully expect you to expand on it when you release your ads):

Content Type

Content Position

Content Size

from below

from below

from below

na

na

na

gif

editorial

800×90

image

footer

100×40

link

header

…

text

left+sidebar

video

right+sidebar

sidebar

color

When you combine these items to create a Content parameter, you will end up with something like this: gif_header_200x300.

Now you can start grouping paid campaigns by which contents were the most successful. Isn’t this awesome? Insights baby!

Term

The Term parameter is used in paid search campaigns to inform you of the keywords you’re bidding on. While Adwords automatically has these tags enabled, you should take this parameter to further help you understand your ads on your other paid channels. This is especially important if you’re running ads on Bing or any other paid search network since failure to do so will artificially inflate your organic search data.

For non-search paid ads, we use this tool to help us identify the persona whom we’re targeting because…why not! This helps us further segment our ad results via persona targets. While many people might recommend against using this parameter, I have found it to be very helpful to me in the past.

A Step by Step Guide through a Tagging Strategy

You want to create your strategy so that you have campaigns that span different mediums, sources, etc. Now, let’s create two fake campaigns and plan out their strategy to help you better understand the implementation using Factivate as an example

Campaign Objective: to introduce the tool to market and incentivize Facebook Ads specialists with a 7 day free trial. Reach out to leaders in the social media space.

This file also includes GA and Facebook Data that helps me get to a ROI comparison between the channels.

**Note, if you’re logged into Factivate, you can also make a copy for yourself by choosing FILE > Duplicate.

Social Tool Tagging Failures

Some social tools including Buffer and SocialFlow, automatically tag links for you hoping to save you time. This practice is sub-optimal and it’s lead me to moving away from those tools as a result. In some cases, these tools have even confused medium with source and corrupted a whole lot of data in the past. If you’re a large company, this can cause some serious under-reporting!

My point here is: make sure YOU are the one creating and managing these links. The more control you have over them, the better your understanding of your data will become.

Best UTM Tagging and Tracking Resources of 2018

Factivate’s Auto-tagging Spreadsheet with KPIs

To aid you with your tagging efforts, you can use the Factivate spreadsheet I’ve created. It will help you generate your UTM tags and will act as a UTM tagging library for your organization. Trust me, this is the most important file for my paid campaigns I’ve ever used and it has saved me from many Sunday night reporting hours!

There is also a tab titled Monthly Report where you can view all of the campaign data released for that month. I recommend you follow the instructions in the first tab to make it your own.

This will help you compare your data through all of your campaigns in one central location. Best of all, the data can extend beyond Google Analytics so that you can get a wholistic view of how your campaigns performed (as reported through the Facebook channel fore example). With it, you can now compare costs automatically.

Google Campaign Builder

Google has a tool that also explains how to build a Campaign Tag and build it for you. Here’s a link to it: https://ga-dev-tools.appspot.com/campaign-url-builder/

Questions?

If you have any questions, I’d love to hear them. I might even update this guide as more of you interact with it. You can also reach out to me via Twitter= @factivateapp

Also, let us know of any tools that could be mis-reporting / mis-tagging links. I always enjoy contacting a solutions provider in order to help them make a better tool and don’t mind the long waiting times:)

]]>When I lead a digital marketing marketing organization, we often discussed whether we needed to explain why the Facebook Insights data looked better than Google Analytics data or vice versa. It seems that for digital marketers, this question continues to come up. After years of debating, here’s our answer to “Which is better: Facebook Insights or Google Analytics?”.

The short answer is, either one is ok alone, but when analyzed correctly together (and personalized to the business); they make for a better analytics tool that even some of the best Business Intelligence tools out there.

So you already set up your Funnels, Goals, Facebook Page, and are spending money like crazy on ads. Now what? Your next step is to try to understand how you’re driving traffic, who is likely to convert, why they are becoming your customers, and how do you get them to talk about you to their entire networks to generate even more sales. This is where you want a combined look into the data. Before we get into it, let’s discuss some of the really good things each platform can do and where their limitations are.

Facebook Insights has a lot of information about the visitors and their demographics, specifically the gender/age group, country/city, language/locale. That information is based on the Facebook profiles, something Google Analytics will not have access to. Insights can also tell you when users access your content from their own wall or when they access your page directly. For data analytics newbies, all of the likes, fans, comments, and post shares allow them to feel good about their campaigns but here is the big downside with Facebook Insights: the available Insights reports are limited to what Facebook decides is important to their clients. Hence, if you want to measure KPIs that actually matter to your individual business (which aren’t offered by Facebook Insights), like your conversation rate, applause rate, amplification rate to try to measure an economic value, you are out of luck. (see: The Top 3 Facebook Insights Metrics: Conversation, Amplification, and Applause Rate if you don’t currently follow those metrics).

On the Google Analytics side, reports are more varied and more interactive, however; they are restricted in the content and context depending on how you have implemented the tracking code. The ability to set funnels and track conversions, if well executed, can help you save hundreds of dollars from using another analytics platform.

The problem with deciding which one is better is that they each have their inherent strengths. Consequently, if you try to report both Facebook Insights and Google Analytics side-by-side, you will hardly get them to match and trying to explain their differences will drive you mad. For example: Clicks for Facebook represent all of the clicks a user had (ie, open comment or like) whereas for Google Analytics, it’s a very specific metric.

So what’s the bottom line? Is Facebook Insights better than Google Analytics?

If you understand the differences and strengths between both platforms, you can use both to get a really good grasp of how you drive users to your intended goals, who they are, and ultimately, how they convert to your customers. This is why we say that Facebook Insights and Google Analytics can be very powerful together to understand users, marketing, and sales strategies at a deeper level. At Factivate, we use our amazing Factivate KPIs template to track all of our important (specifically tailored to our business) business KPIs from Google Analytics and Facebook Insights to get to an actionable metric: economic value per user. This then helps us optimize our content, message, and bottom line.

If you’re interested in having a better analytics tool than Facebook Insights or Google Analytics, take a look at our free automated spreadsheet template reports or sign up for a Factivate account to start taking ownership of your data.

Factivate is a software company part of the Google for Entrepreneurs community in Durham, NC. Factivate provides business users with a cloud-based data analytics platform that can sync with different online tools to create automated, real-time spreadsheet reports and web dashboards with just-in-time business reactions. The result of our intelligent spreadsheet is accurate data analysis that drives better business decisions while reducing manual spreadsheet report time and errors by a factor of 150+ hours/employee/year. Factivate requires no learning curve or programming knowledge. In sharp contrast to the complex and expensive business intelligence tools on the market today, if you know spreadsheets, then you know 95% of what you need to use Factivate–we’ve got the rest covered.

]]>Want to Advertise on Instagram? Here’s A Digital Marketer’s Guide to Creating and Tracking Instagram Adshttp://blog.factivate.com/digital-marketer-guide-to-instagram-ads/
Fri, 17 Feb 2017 19:06:05 +0000http://localhost:8888/factivate/?p=192Ever wondered how people are leveraging Instagram Ads in their campaigns? Did one of your clients ask you to advertise through Instagram but you’ve never

]]>Ever wondered how people are leveraging Instagram Ads in their campaigns? Did one of your clients ask you to advertise through Instagram but you’ve never done so? How do you determine your campaign’s Return on Investment to see if Instagram / Instagram Ads is a valuable for you?

Today, businesses of all shapes and sizes have the ability to publish ads on Instagram to reach a targeted audience and this blog post will help you get your Instagram Ads campaign up and running in no time.

First things first, Instagram is part of Facebook. As a result, you will need to have the following before you can continue: a Facebook Page, a Account, Facebook Business Manager account, and a Facebook Ads account with a pre-existing US Facebook profile. Let’s get started.

Connecting your Facebook Page to Business Manager

In order to get started with Instagram advertising, you need to connect your Facebook page to Facebook’s Business Manager. (If you’ve already set up Business Manager and linked it with your Instagram ads, you can skip to step 2.) In order to set up business manager, you need to go to https://business.facebook.com and create an account. Follow the on screen instructions until you get to this screen.

From here onwards, click on the button to set up the your Business Manager Account. You should see a screen like the one below. This is where we will connect our Facebook Pages, Ad Accounts, or grant/assign permissions to other individuals.

From here on, you will need to #1 – claim or get access to a page; and #2 Claim an Ad account or get access to the ad account. To do this, simply click on the options listed on the screen and follow the pop up instructions.

Before moving on, make sure that you’ve done the following:

1) Connected your Facebook Ads Account to Business Manager

2) Connected your Facebook Pages to your Business Manager

Ready? Let’s move on –

Starting a New Instagram Ads Account

If you’ve never run an Instagram ad, the business manager page will provide you with a link to Create a New Ad Account. This option is located on the Manage Settings section. When you click on it, you will be taken to a screen like the one below:

You will need to Claim a New Instagram Account. You must have an activate Instagram Account in order to do it so if you don’t have one, go register on www.instagram.com. Once you’ve confirmed your account, go back to the Business Manager Account settings screen, click on the Instagram icon on the left and claim your New Instagram Account. Enter your username and password and follow the on screen instructions.

Now let’s set up your first Instagram Ad!

Setting Up Your Instagram Ad in Power Editor

On the top ribbon, click the menu icon to option up the drop down menu and click on the Power Editor option.

If you’ve never managed an Ads account, you will need to add your payment information. Once your payment information is set, you can create your new campaign by doing the following:

1. Click on the + Create Campaign button:

2. Follow the on screen pop ups to set up all of your campaign by clicking on the Get Started button.

3. Set your Marketing Objective for your campaign and name your Campaign. Be sure to use a unique name as this will be how you distinguish your campaigns in the future.

4. Define your audience, budget, and schedule. If you would like to create a Pixel, simply follow the onscreen instructions as well.

5. IMPORTANT – Go to the Budget & Schedule section. Click on the Show Advanced options. Based on your goal, Facebook will default your Ad Delivery method. Verify that this is what you intended, ie: CPC, CPM, etc…

While you might typically want to track the KPIs that Facebook Ads suggests: reach, cost, amount spent, etc…be careful that you’re not tracking vanity metrics (ie, how the heck does impressions matter to my bottom line other than to make me feel good). That’s why you need to consider the KPIs for your ad’s success right at the time when you will be releasing it. If you’ve never done this, ask yourself, based on the campaign goals, which numbers will determine whether the ad will be a success or failure. Is it sales? Is it marketing qualified leads? Is it community growth? Conversation Rate increase? All of these will be necessary for you to determine the eventual ROI for the ad campaign.

There are hundreds of tools to track your Ad analytics. However, based on our experience, they’re limited in the following ways:

Problem 1: None of them allow you to track custom KPIs such as your Customer Economic Value or even your Ad’s real ROI, for example.

Problem 2: Reporting on these ads can be a pain. Even if you pay for an analytics tool, you will most likely end up exporting the data into your excel spreadsheet.

Problem 3: You need to be on top of your data 24 x 7 in order to react to changes within the right timeframe. Analytics tools assume you’re basically looking at their data your whole life when in reality you’re busy doing other things. You will need a tool to alert you when specific KPIs change.

If you’re already feeling overwhelmed with this stuff, bare with me. There’s a pretty easy solution that you can use and will solve all of these problems for you and I’ll show you how to set it up with just a few clicks.

2. Once you’ve opened the Instagram Ads spreadsheet, you will have a complete analytics workbook to monitor all of your campaigns with tke KPIs (automatically):

Notice that the KPIs for Conversions, Cost/Conversion, and Ad Fatigue Rate have custom formulas calculating them. If you’re an e-commerce company, subscription business, or even a freemium business, you will want to tinker with the spreadsheet formulas to make sure that those conversion numbers and ROI are calculated correctly.

3. Go to the FBAds template, and click on the Authorize icon on the top ribbon. Then, enable the account and watch your data start flowing in.

The great part about this spreadsheet is that once you’ve set it up, you’re done. No reporting…no coding…and best of all, you can start adding other ad campaigns from Facebook, Twitter, etc and start tracking it all in one place using your own KPIs.

If you’d like to do this, you can sign up via www.factivate.com or click on the button below:

If you have any visual content that you can use to promote your business, products, communities, or services, Instagram is a great platform to consider. Be sure to consider how you’re going to track your ad’s success at the same time you’re setting it up in order to gain the maximum benefit from your ad campaign.

If you have had success running Instagram ads, we’d love to hear from you. Share your results in the comments section below!

]]>Create the Only Spreadsheet a SaaS Entrepreneur Will Ever Need – a how to guide on SaaS analytics to help SaaS companies run, measure, react, improve and communicate what mattershttp://blog.factivate.com/saas-analytics-guide-for-companies-to-run-measure-react-improve-communicate-matters/
Fri, 17 Feb 2017 19:05:58 +0000http://localhost:8888/factivate/?p=194ABSTRACT Have you ever wondered why an investor gave money to another startup and not yours? This article summarizes how to increase your investor’s (and

Have you ever wondered why an investor gave money to another startup and not yours? This article summarizes how to increase your investor’s (and your own) awareness of your business by helping you build a SaaS startup analytics platform in a spreadsheet. Our step-by-step process results in an automated startup SaaS Analytics platform that helps you track, measure, and react to the key insights for your business. Follow the steps discussed below to build the only spreadsheet a SaaS entrepreneur could ever need. Use this spreadsheet template to help you run your SaaS startup and impress all of your current and future investors by providing a transparent (and comprehensive) understanding of all of your revenue KPIs, advertising KPIs, marketing KPIs within your business model.

The Only Spreadsheet a SaaS Entrepreneur Will Ever Need: Why build it?

I’ve met many entrepreneurs who seem to struggle devoting enough time to building and understanding their own KPI and financial models. We all know how important it is to track our data yet, due to time constraints, we rarely put in enough effort to do so. As a consequence, we miss key data points along the way that have the potential to make or break our businesses. As a fellow entrepreneur, I am well aware of the level of personal effort required to build or update that new KPI model, Pirate Metrics Model, Financial Model, even an Ad campaign report at 2am.

In an effort to stop wasting time building spreadsheets and spend more time on data analysis and strategy execution, I asked a few key figures, including some prominent venture capitalists like David Gardner and David Skok, if there are any tools out in the market that would help me aggregate my KPI data into my business model. I also mentioned to them how important it was that I could also share such information with my investors and team members 24×7. The responses I received were a little deflating. For the most part, it was suggested that:

There are plenty of dashboard tools out there, like Baremetrics or ChartMogul, that will let you visualize your data but you can’t combine them with your own financial models.

You’re probably better off sticking with spreadsheets since KPIs are constantly evolving and require a level of constant customization that only a spreadsheet can give you.

To really get what you want, you must be able to fetch data from different systems and then be able to frame the data to your own needs. You can invest in a consultant who can write a number queries to fetch the data but it could get expensive to maintain or modify.

Since the data resides in several system silos, you have to use a platform that already has connectors to the systems you use to collect the data. To this day, I am not aware of any spreadsheet templates that will let you do that.

Every single time someone gave me one of these responses, I honestly couldn’t believe it. With so much innovation, how has someone not yet solved this problem.

This article was created to show other SaaS entrepreneurs how to create their own spreadsheet analytics system to help better run their companies by spending more time analyzing and reacting to key data instead of aggregating it. I hope that by the end of this tutorial your spreadsheet template can resemble our very own “The Best Spreadsheet Template for SaaS Startups“.

While I attempt to provide a comprehensive look into the key metrics that we believe are commonly needed to run and optimize a SaaS business, keep in mind that I will not cover every single metric related to your particular business. If you fall under that category, use this how-to guide as a stepping stone to: 1) help you build a data-centric culture, and 2) build a better thought-out KPI strategy that you can track as you continue to grow.

Check out the video below to see our spreadsheet template in action:

(Note: although I focus on SaaS specifically, this article can be applicable to other subscription businesses and spreadsheet templates.)

Learning About SaaS Analytics –

There are plenty of articles on the nature of a SaaS business and the importance of customer acquisition and retention metrics. This article assumes you already know the meaning behind the SaaS Metrics we discuss. If you need help understanding SaaS metrics, I recommend you read our own SaaS Metrics 3.0 article or search SaaS metrics online.

Getting Your Business Ready for SaaS Analytics Business –

There are hundreds of analytics tools that can give you insights on your website, marketing campaigns, revenue, and even your own app’s performance. While I can’t recommend one over the other, I decided to take a practical approach and talk about the most commonly used (and most cost effective) analytics tools known to entrepreneurs (aside from Factivate). For SaaS startups (and our spreadsheet template), we boiled these tools down to: Stripe (revenue data), Google Analytics and AdWords (website data), and Facebook Insights and Ads (Social Media data). I then combined the data from these tools in an analytics spreadsheet template in order to give SaaS startups:

An Investor Dashboard

A Cap Table example

A Month-over-Month Dashboard

A Week-over-Week Dashboard

A Marketing KPI Report

A Content Calendar

An Advertising KPI Report

A Sales KPI Report

A Financial Model (Realized)

A Projected Financial Model

My hope is that building this template will not only help you WOW your investors (or future investors) but that this automated spreadsheet will be the only spreadsheet your startup will ever need to build. If you don’t want to go through the process of building this system, you can use the Factivate automated Saas Analytics spreadsheet template titled “The Best Spreadsheet Template for Saas Startups”.

Note: all of the spreadsheet screenshots will be zeroed out (for obvious reasons) but if you have any questions about the data itself, feel free to email us at info@factivate.com or add your questions in the comments section.

Step 1: How to Set up your Digital Marketing KPIs

1. Setting Google Analytics for Conversion Tracking – First things first, your website should be integrated with Google Analytics. This means that you have a Google Tracking ID and landing pages that you plan to continously check for user traction using AB tests, Micro-conversions, and Macro-conversions. (To learn how to set your Google Analytics structure, you can view our How to Track Marketing KPIs or Sales KPI conversions with Google Analytics Funnels. ) In our case, we break down the data that we want to track on a daily (or weekly) basis into three categories:

AB Tests – refers to the pages we’re actively comparing to each other to learn about message, design, and user responses. In our case, we’re consistently testing different variations of our consumer-facing website pages from the home screen to the pricing page.

Macro-Conversions – We call these Sales Qualified Leads or Sales Conversions. These are the landing pages we have added in Google Analytics as our Goals which result in a user being one to two steps away from buying our service. These include demo presentations and account creation thank you pages.

Micro-Conversions – We call these our customer touch points that can lead to a macro conversion. Examples of these would be downloading white papers, playing how-to videos, subscribing to an email list, etc…

The end result in our marketing tab includes the three sections (like the image below):

The image displays part our marketing team’s spreadsheet tab. To get data in it, all we need to do is replace the columns with the “/URLs”, enter the landing page URLs, and then the rest of the data fills up automatically. We find that these three sections give us an indication about a data spike or a flat user response related to our website tests. The second we notice a dramatic change in the data, we then dig deeper into the data to make sure we understand the reason for the data fluctuation. Finally, we decide whether we can repeat the same actions that led to the data spike, stop execution on a flat campaign, or continue to closely monitor.

This leads me to my next point: understanding why, how, and where your conversions came from is critical to iterating on positive conversions. As a result, the data analysis mentioned above goes hand in hand with understanding where your conversions can come from:

The traffic source information can or cannot coincide with your page conversions; it’s up to you to figure that out as quickly as possible. This section can help quickly identify the “where did they come from” question(s). You can continue digging deeper by learning about demographics, location, etc… but the above data should help you get a quick head start and not get lost in the universe of too much data.

Do you feel you have a good handle on your website KPIs and have your structure set up correctly? The end result could look like the screenshot below:

Now it’s time to further increase your marketing efforts by combining them with a content and social media strategy.

2. Setting up your Content Calendar to Increase Brand Awareness

Just as we’re constantly testing AB pages and landing page conversions, we need to be testing content and digital interactions. This is probably the section that will fluctuate the most since most entrepreneurs are in a constant state of experimentation (aside from product experimentation). Either way, I’ve found that setting a monthly content calendar, based on a content goal, helps us focus and execute. The content calendar also gets thrown right out the window the second we identify a conversion spike in a particular channel with a particular message. When that happens, we rewrite the entire calendar to try to milk as many conversions as possible. Once we see it that content conversion strategy flatten, we move back to new content experiments. To set up a content calendar, we add the following columns:

Date

Categories

Subcategories

Target

Call to Action

Post / Content

# of the same Post variations for that day (we use this mostly if we’re AB testing)

Impressions

Comments

Likes

Shares

Clicks

Total Engagement

So now that you’ve set up your content calendar, it’s time to see how your content is perceived in individual social networks!

3. Setting up a Social Media KPI marketing structure:

Social media is a great way to create brand awareness and community relations but it can also seem mystical if you’re not tracking the right data. I frequently run into entrepreneurs who are active in social media and when I start asking them about how their interactions lead to micro or macro conversions they never give me a good answer. Sometimes they flat out tell me that social media just doesn’t work for their company demographics so they don’t track it. This realization is often a symptom of their inability to tracking the right KPIs. I must admit that even I was in that same boat at one point. It wasn’t until I began focusing on the following data that I really started improving on social media:

We track these KPIs because they help us get to three key ratios that indicate how our social media strategies are having an impact: conversation rate, amplification rate, and applause rate. (To read more about these ratios click on our post on the top 3 facebook insights metrics: conversation, amplification, and applause rate.)Each ratio gives us an idea on how we should be executing and reacting based on our marketing strategy.

Remember that the goal for any entrepreneur is to quickly identify the marketing strategy that will lead to the most conversions and reduce wasting time and effort on channels and messages that don’t work. When you’ve set up your spreadsheet, as this post hopes to show you, you can pull any amount of data together to calculate your Marketing ROI. To do so, you have to consider your marketing spend and conversions. Your marketing spend can be calculated by $ spent on marketing activities which include the salaries of your marketing employees or consultants. Having a conscious understanding of your Marketing ROI will quickly show you whether your marketing spend is working well (or not). The sooner you arrive at this conclusion, the better off you will be.

Step 2: How to Set up your Advertising KPIs

I started this article on Marketing KPIs because, as a company rule, we do not spend any money on advertising until we have some indication from our marketing efforts on social media, memes, content, or even landing pages of a good response/conversion rate. This little strategy has saved me thousands of dollars from advertising mistakes!

Let’s assume that you have now noticed the following from your marketing data: your landing page has a good conversion rate and low bounce rates, a marketing message in social media has a great conversation rate, and you’ve noticed a good micro (or macro) conversion rate from the message; now it’s time to amplify that message and (most importantly) track its performance through different channels. To do this, most startup entrepreneurs typically start with either an Adwords campaign or a Facebook Ads campaign.

1. Google Adwords Campaign (PPC) KPIs

Before you begin your Adwords campaign, be sure to link your Adwords account to your Google Analytics account (Click here if you don’t know how to do this). Once you have done so, you will be able to track your conversions just as you did with your marketing KPIs. In the end, you could end up following these Google Adwords KPIs per campaign:

From the data above, we pay particular attention to Ad Fatigue Rate, Quality Score, and Cost/Conversion. When we see that Ad Fatigue Rate is trending up and Quality Score begins to trend down, we monitor the ad closely to see when the cost/conversion gets close to the $ per new user conversion. In our case, if our ad’s ROI is only 20% more than the cost/conversion, or our ad fatigue rate has trended significantly upwards (within a certain number of days), we typically decide stop our ad and move on. To ensure that we never miss these important KPIs, we have our spreadsheet send us an SMS alert based on these formulas (ie, ROI is only 20% more than cost/conversion). That way, we’re always on top of our data even when we’re not looking at our spreadsheet. (Learn how to have your spreadsheet send an SMS when a cell value changes to never miss any important information).

Since we run multiple campaigns (remember we continuously test), we set up our spreadsheet template to help us track them all in one place rather than going to each platform to fetch the data. Our marketing/advertising person (sometimes even the CEO) is responsible for updating the Campaign names, budgets, and creating alerts for campaign balances and the KPIs I mentioned above. This helps us keep a close eye on all of our campaigns even during nights and weekends. The end result of our template looks something like this:

Note: We track our campaign results on a daily basis (since we obsess about our data) but you don’t have to do that. Depending on the nature of your ad, you might not see results for weeks so just be sure to consider that not all ads are the same and some (which might lead to great conversion rates in due time) might take some time to grow. As a company that iterates continuously, we find that daily or weekly analysis works best for us since we’re not testing the effectiveness of the message (remember, we already saw some indication through our marketing efforts) but rather the effectiveness of the channel.

2. Social Media Advertising (Facebook Ads) KPIs

While not all social media platforms give us comparable data to Adwords (see differences between Facebook Ads and Google), we track ratios that can indicate the effectiveness of the channel (and ad) as opposed to the typical impressions/visits/vanity metrics. This results in an Advertising spreadsheet tab that considers Ad Fatigue Rate, Cost/Conversion, and $ per new user conversion as the top level KPIs that we track (rather than clicks or CPC for example).

3. Putting your Advertising KPIs together

Tracking more than 20 campaigns at a time can be very difficult when you’re an entrepreneur that has to wear many hats. That’s why we created two summary views which we can quickly glance when our analysis time is limited.

The first section focuses on a per channel view and it includes an aggregate of our active campaigns on a week by week basis:

The second view relates to a high level overview of all of our advertising efforts in which we consider SaaS conversions as our primary objective:

Internally, whenever we’re running an ad campaign, this spreadsheet tab is always open in an independent monitor in our office. We do this to remind ourselves that every single dollar is important and if our KPIs are indicating waste or opportunity, we act on it immediately. So far, this strategy has worked really well for us.

Step 3. Setting up your Sales KPIs

Now that you are driving some great inbound sales through your marketing and advertising funnels and you have a solid foundation to measure your customer acquisition KPIs, you need to consider your SaaS revenue metrics. From our experience, most of the entrepreneurs we speak to use Stripe to manage their online subscription payments so we connected our automated spreadsheet template to Stripe.

Note: if you use a different payment method, the spreadsheet structure will still work but note that if you use Factivate’s automated spreadsheet template, it will only fetch Stripe data for now.

There are a few spreadsheet templates that have helped entrepreneurs track their SaaS Metrics including: David Skok’s spreadsheet template on SaaS Metrics and Christopher Janz’s Financial Plan v2.0. My problem with these spreadsheets was not that they weren’t really useful. Rather, it related to the inability of the spreadsheet to automatically pull data from our payment processor, Stripe, in order to let us compare the KPIs we cared about (in context of the business and not just sales) in real time. As a result, we used those spreadsheet examples as a foundation for our own Sales KPI reports and improved on the foundations laid out by Skok and Janz by automating the spreadsheet to collect the data from Stripe. The Sales report is tracked on a monthly basis in this tab because our spreadsheet template also includes revenue data in the Week-over-Week tab and Month-over-Month tab.

The top part of spreadsheet tab looks at our Bookings and MRR Summary:

The bottom part of the spreadsheet tab focuses on Customer information by Plan:

Since the spreadsheet was built for SaaS startups, or subscription-based companies, there is a significant focus MRR and Churn KPIs. If these don’t work for you, feel free to customize your spreadsheet as you see fit.

Customer Aquisition KPIs, CHECK! Revenue KPIs, CHECK! Congratualtions, you’re quickly becoming a KPI superstar. So how do you stay sane with all of data that you need to consider when you’re already busy running a company? Dashboards give us a great top-level view of all of our activities (in context) so naturally, our spreadsheet template includes several dashboard views.

For Factivate, our dashboard uses the AARRR metrics foundation to monitor our company’s health. Conseqently, our dashboard is divided by the following sections:

Our tab essentially pulls all of the data that we collect (from Google, Social Networks, and even Stripe) into this pirate metrics dashboard. We are then able to add spreadsheet reactions to specific KPIs in order to be able to react to changes in data in an instant (whether we’re asleep, or even on vacation). Some of these examples include:

Send me an Email when Marketing spend = (# of new users converted * $ per new Conversion)

Send me an Email when Advertising spend = (# of new users converted * $ per new Conversion)

Send my customer implementation team an Email when there is a failed conv from trial

Send me an Email when my Customer Acquisitoin cost exceeds ____

and so on…

1. How to Set up your WoW Startup Dashboard

Think of this Week-over-Week Dashboard tab as your business’ management tab. It paints a picture of all of the business pieces working together on a weekly basis and, if you wish to dive deeper, all you need to do is go into the other individual spreadsheet tabs.

Per company policy, my team typically makes notes on the top section of this spreadsheet tab (based on our data analysis & recommendations) before every team meeting so that we can discuss the important data stories and results when we plan our strategy. We also add a few charts to demonstrate our points.

2. How to Set up your MoM Startup Dashboard

Like the Week-over-Week Dashboard, our Month-over-Month Dashboard helps management (and even our board members) gain a good top-level view of our data. We use the same structure (Pirate Metrics) for our MoM Dashboard and end up with a spreadsheet tab like the one below:

Our spreadsheet tab also showcases the month over month growth from our macro conversions and micro conversions to help add context to some of our data.

Note the GOAL column. Before the beginning of each month, we specify our company goals here. These drive our entire business strategy for the upcoming month. It’s good practice for entrepreneurs to try to do the same as we have found it incredibly beneficial in keeping us focused.

The Month-over-Month Dashboard also includes a section for Analysis & Recommendations at the top. I find this extremely helpful as it forces our team to take a step back from our day to day (or week to week) analysis and consider the bigger picture.

Step 5: How to integrate your SaaS metrics KPIs within your business model

There are plenty of ways to build a business financial model to suit your startup. In our spreadsheet template (and article) we include a business model inspired by Cofounders Capital (a current investor in Factivate). The startup financial model provides an elegant way for entrepreneurs to structure their business assumptions and test against them. After spending years building complex models (with some very fancy S curves if I might add), I find this model is a great way to get started.

Your spreadsheet template should include at least two business model tabs. The first one is your projected model (as you see below) and portrays a basic Revenue/Costs picture of the business. You can edit the assumptions on the left hand column and see how it affects the rest of the business later on.

The second business model reflects the realized (actual) numbers from your business just like the image below. This tab is where you will go in and add your actual data at the end of the month.

We use these two tabs in order to learn how far off we typically are from our projections and goals. Sometimes we exceed these goals and we adjust our assumptions and sometimes we fall below our projections and have to adjust our cashflow. In the end, investors (and future investors) really care about the projected vs realized comparison so be sure to have some version of it in your spreadsheet system.

Step 6: How to set up an Investor Dashboard

If you really want to impress your investors (or future investors) you will need a way to integrate everything you have built (KPIs, business model, and strategy) into a dashboard that is specifically built for your current and future investors. With this spreadsheet tab, each investor will have a view into your business 24×7 without you ever needing to update the data manually. The investor dashboard should include three basic things:

An “Updates & Requests” Section – while some entrepreneurs will find sharing their needs and problems (with potential investors) as problematic, our typical experience is that the investors that were truly interested in our company, took us more seriously and, at the same time, appreciated how in tune and transparent we were with any potential problems related the business. We also had some outside investors offer us introductions because of what we posted in our Needs section.

A Current Month KPIs section – Each investor looks at different KPIs when they are working with your business. We find that simply placing those KPIs at the very forefront helps in our communication. The beauty in managing this information through a spreadsheet is that we have the flexibility to edit/manage all of their requests without spending any time coding or writing complex queries.

A Bookings Metrics Graphs overview – SaaS companies should provide a graphical (top-level) view of their data to their investors over a prolonged period of time. In our case, we provide a semi-annual view of our MRR and Churn metrics for our investors.

A MoM Metrics section – We added this section to show our investors (both current and future) how we were doing when compared to our monthly goals. If we want to add or remove a metric, it’s as simple as editing a spreadsheet formula. Notice that we stay away from vanity metrics and focus on ratios, conversions, and revenue-driving activities.

Factivate allows us to share individual tabs so we typically share this one tab with investors that we’re speaking with (including current investors of course). When we do so, we limit their read and write permissions to editing only dates on cells M1,M2 and N1,N2. As such, we make surethat they won’t mess with the model while still giving them the flexibility to view different MoM data.

Step 7: How to build your Cap Table

While this tab doesn’t have anything to do with your KPIs, I’ve met so many entrepreneurs that have a difficult time building one so I figured I would add it. I’m not an expert in building Cap Tables, but this tab reflects how we’ve managed our Cap Table throughout my various companies. Feel free to edit it as you see fit or to use the same structure I’ve used.

Using Factivate’s “Best Spreadsheet Template” for SaaS Entrepreneurs

Factivate uses a similar spreadsheet to manage our company. To use it effectively, we have had to be careful on the read/write permissions given to the team and investors. In our case, the marketing team only has access to the marketing tab and content calendar tab. The Advertising Manager only has access to the Ads tab and Content Calendar. Investors only have access to the Investor Dashboard, and so on… Take the permissions consideration carefully in order to prevent data corruption.

While I tried to showcase how SaaS entrepreneurs can build a robust data analytics spreadsheet that helps them run, measure, react, improve, and communicate important data, if you decided to build the spreadsheet from scratch rather than use Factivate’s template, take this into account: the spreadsheet is only as good as its ability to reflect the “pure data” pulled from the different cloud-based sources. You can spend hours building your own spreadsheet template but if you’re also spending hours updating it, you’re likely better off just emailing us and asking us for a deal in a subscription (hint hint, wink wink)

Disclaimer: There are thousands of data sources that we could have included to help companies gain better insight, but in an effort to focus on practicality, we decided to focus on what we believed were the core data feeds for a majority of entrepreneurs. We also understand that our KPIs might be different than yours. The beauty of the spreadsheet, however, is that you can customize it to fit your needs. If you have any insights or comments about this article, different KPIs, or the spreadsheet itself, please share them in the comments section so that we can improve this template and make it even better for all SaaS entrepreneurs! That being said, the spreadsheet’s purpose cannot be overstated, use it to build a culture that spends more time focussing on data to drive better business results instead of building reports.

I hope this guide helps you throughout your SaaS endeavors, and let me know if we can be of any further help (support@factivate.com)!

]]>How to have your spreadsheet automatically send out an email when a cell value changeshttp://blog.factivate.com/spreadsheet-automatically-send-email-cell-value-changes/
Fri, 17 Feb 2017 19:05:52 +0000http://localhost:8888/factivate/?p=196We get this question all the time: how do I make my spreadsheet automatically send out an email when a cell value changes? Say you’re

]]>We get this question all the time: how do I make my spreadsheet automatically send out an email when a cell value changes?

Say you’re tracking bounce rates for a particular campaign and you want your spreadsheet to automatically send you a email when bounce rates go above 10% or maybe send you an email when your budget goes below $1,000 – how do I do this in Excel or Google Sheets? Is it even possible for Excel to automatically send out an email?

The short answer to this question is yes, this is possible BUT it does require a moderate to advanced user to pull this off.

I should mention that to do this in Factivate, this type of functionality can be added within 2 clicks. To do so, go to the actions panel, select the email icon, then add your formula (example: B2<10%), and then add an email subject, the person who will get the email and click on save. Once that’s done, your Factivate spreadsheet will automatically generate an email onceB2 changes below 10%. Easy…Fast…and Effective. Check out the video below to see how to send an email from your spreadsheet:

If you’re dead set on learning how to do this with Google Sheets, however, you will need the following Script:

Here’s a step by step guide on what you need to do to implement this script that auto sends email when a spreadsheet value changes:

1) You need to decide how you want this script to run; with a button click or automated. It’d recommend automated so you don’t have to worry about it.

2) When you’re in the script editor, go up to the clock icon *should say triggers when you hover/click). You’ll want to set up a condition that will run your function.

Clock icon > Click to set up new trigger > “Run checkValue”

Change “time driven” to “Spreadsheet”

Change “on open” to “on edit”

3) Now that you have the script running by itself, we need to make some adjustments to the code. First we need to pass the “edit argument”. So instead of :

function checkValue()

we’ll have

function checkValue(e)

This will pass along information about what was edited within your spreadsheet. Since we only care about once cell, even if it’s edited, we’ll have to define the (cell location) of the ‘edit’ variable (‘e’).

var rangeEdit = e.range.getA1Notation();

Then, before we check to see if the Cell value C7 is over your max, we’ll first check to see if C7 was even the most recently updated cell (this way you won’t get an email every time you update your spreadsheet).

If you have any questions about how this should work, feel free to email us at info@factivate.com or submit a comment below. We’re happy to help with all spreadsheet automated reactions.

If, on the other hand, you don’t want to build these queries, you can sign up for a free trial for Factivate and use our automated spreadsheet reactions. To sign up, simply click on the button below:

About Factivate:

Factivate is a software company part of the Google for Entrepreneurs community in Durham, NC. Factivate provides business users with a cloud-based data analytics platform that can sync with different online tools to create automated, real-time spreadsheet reports and web dashboards with just-in-time business reactions. The result of our intelligent spreadsheet is accurate data analysis that drives better business decisions while reducing manual spreadsheet report time and errors by a factor of 150+ hours/employee/year. Factivate requires no learning curve or programming knowledge. In sharp contrast to the complex and expensive business intelligence tools on the market today, if you know spreadsheets, then you know 95% of what you need to use Factivate–we’ve got the rest covered.

]]>How to Set Up Your Social Media Analytics Excel Spreadsheetshttp://blog.factivate.com/setup-social-media-analytics-excel-spreadsheets/
Fri, 17 Feb 2017 19:05:47 +0000http://localhost:8888/factivate/?p=198Digital Marketers: do you know how to set up your Social Media Analytics in Excel spreadsheets? I spend a lot of time talking to analysts

]]>Digital Marketers: do you know how to set up your Social Media Analytics in Excel spreadsheets?

I spend a lot of time talking to analysts and data scientists about how they work, what their challenges are, and what makes their job easier. While there are thousands of tools and applications that enable social media analytics in one wayor another, it seems to be that almost all of them continue to turn to spreadsheets for their analysis and data manipulation because it is a language and tool that they’re accustomed to.

Companies spend thousands of dollars a month generating content, speaking with customers, and building creative ideas on new ways to interact with users. When it comes to analyzing that data, however, it seems that some of them rely on outdated spreadsheets or really old models. While there thousands of complex models that we can share, this article should be used to help you get started on how to set up your social media analytics in spreadsheets. In doing so, I will show you how to analyze the engagement and social media posts using Excel.

Set Up Your Social Media Analytics Spreadsheet

There are two ways to build your social media analytics spreadsheet. The first one is to use this template from Factivate (www.factivate.com), add your social media account logins and then have a dynamic spreadsheet that will display data in real time. This takes a few minutes

The second one is to do it the old manual way in Excel. Note that you will be required to update your spreadsheet continuously in order to keep your data fresh.

Why do I need a Social Media Analytics Spreadsheet?

Regardless of the size of your business, when it comes to social media analytics we should always measure two things (to begin with): engagement and community.

To ensure that you continue to deliver interesting and relatable content that resonates with your followers, you need to continuously track, test, and measure the reactions to your own posts. That’s what this spreadsheet helps you do.

Step 1: Build Your Spreadsheet

To begin, open Excel and create 14 columns that will contain the following categories:

Date

Network

Categories

Subcategories

Target

Calls to Action

Meta-tags

Posts

Impressions

Comments (replies)

Likes (favorites)

Shares (retweets)

Clicks

Total Engagement

An example of a social media analytics spreadsheet template

The last four columns are where you store the engagement numbers for your content. Remember that you can also add weights to measure content based on importance (ie., I only want my clicks to weigh about 30% of a Comment) so that you can refine your total Engagement numbers based on what you have learned.

Since every network has three basic engagement actions: like, comment, and share, you should be measuring that user’s behavior or reposting and tagging the author.

Step 2: Identify Categories and Subcategories

Base your categories on the major topics of your posts and your overall content strategy. Once you have that list, you can use them to identify the major trends in content performance across the different channels and networks. In our example, we have used some of the Categories that Factivate has used in the past but typical categories should include:

Product: Posts about major product categories

Holidays/Seasonal: Posts with seasonal themes. These are really helpful if your business has any sort of ecommerce component.

Third-party content: Posts with content or references from other websites such as blog posts, white papers, presentations, news, and articles.

You can create as many categories as necessary but be sure to keep them general.

Once you have the list of categories, break them down into subcategories. This will enable you to identify microtrends as well as learn which pieces of content work best within your particular segments. Based on the categories above, examples can include:

Subcategories can be as specific as necessary but make sure they align within your overall marketing campaign and that they are thematic. Each subcategory should have at least three posts per your campaign (we use 7 as a rule of thumb); otherwise, it may be too specific and should instead be a meta-tag.

Step 3: Outline your Target Demographics and Calls To Action

These two columns require a really good understanding of your target market and overall goals for the marketing campaigns. Be sure to sit down with your entire team if you have not taken the time to consider these two columns.

Target Demographic

Your target demographic should be your intended audience for your content. In general, the more specific you can get, the more impact your content will have. For the sake of this example, we have used job functions inside marketing organizations for our target demographics. If you have not already done so, be sure to spend time with your team considering all of the different types of personas in your audience and build content/context around those individual personas you wish will read your content.

Calls to Action (CTA)

CTAs can take many forms and it depends on the individual goals of the post in each category. Note that not all posts need CTAs and not all CTAs should be related to products since audiences today tend to shy away from the constant advertising pushed to them. Instead think about what you can do to get them excited to talk about your brand (think Ice Bucket Challenge).

Examples of CTAs include:

Download

Visit website

Subscribe

Buy Now

Share

Meta-tags

If subcategories had subcategories, these would be our meta-tags. For a much deeper analysis on your content type, you should use meta-tags to describe the individual elements of the content. These can include keywords, image descriptions, and even tone descriptors for the copy (such as humorous or excited).

Meta-tags will help isolate trends in your creative content so you can build more dynamic context for your posts. (i.e. #lovethyspreadsheet came from our meta-tag of passion for data). For the sake of this example, we have removed all meta tags and posts, and impressions but feel free to add to our comments section if you would like further explanation).

Step 4: Collecting your data

Here is where you will start importing information from different sources into the spreadsheet. Note that about 88% of spreadsheets have errors associated with these importing and manual processes so be careful that you do this correctly. If you want this process to be done for you automatically, be sure to sign up for Factivate. Once you’re a Factivate user, this process will be done in real time for you.

In the example below, we’ve imported information from Twitter Analytics, Facebook Insights, and Linkedin. Be sure to devote at least 2 hours every time you want to import from these sources into Excel since you will need to format the imports and check that no human errors have occurred.

You will need to export regularly to make sure your data is up to date.

If you tend to post the same link multiple times, make sure that you’re attributing your click to the right content by using tools like HootSuite, Buffer App, Pardot, or Marketo to help you track which specific posts generate the clicks. Some of these tools are offered for free and will allow you to measure posts on multiple networks.

Step 5: Formatting, and Sorting your Data

In order to identify your best-performing content across all of your marketing channels, sort the Total Engagement column in descending order. Once your data is sorted, the best-performing posts will show up at the top of the column. If you want to be really efficient, you should be measuring this information consistently (we do it every 3 days) and then refining your content on the best performing post categories, subcategories and meta data.

If you wish to find the best performing posts for specific social networks, like Twitter or Facebook, you can also filter by Network and then by total engagement.

Step 6: Analyze Categories

The next step is to manually filter for the categories and subcategories you’re interested in reviewing and begin to analyze trends. Once you have that information, see if you can compare it to your historical data trends to see if there is anything that pops out to you.

In order to manually filter in Excel, you should make sure that you include more than one category or subcategory assigned to content. You will need to use the “contains” input in the Excel filter feature rather than selecting the check boxes that appear when you start the filter process.

Once you identify the top content per channel, per category, it’s time to analyze them for your post ROI. Compare it to historical data to see if you can notice any emerging trends or downward trends. Ask yourself what can help you optimize content for each strategy.

From the data below, what does the analysis show you? One conclusion could be that one of the image categories did extremely well and that we should be iterating on that basis to increase user engagement in Twitter. Based on our historical data, this is an apt conclusion.

Conclusion

You don’t need to be a data scientist to build your first social media analytics portal. With a little elbow grease and basic understanding, you can find which content performs best. Remember to keep iterating and trying different things continuously to keep your content fresh and relatable. Be sure to use your spreadsheet to reveal important insights about your post engagement and continue to update your spreadsheet to keep your data as up to date as possible. You can also use Factivate to keep your data up-to-date to make the best-informed decisions about your content strategy.

There are many variables that can be used to measure your social media impact. Hopefully this post will help you get started in discovering what these are. If you don’t want to continue updating your spreadsheet manually and would rather have a prebuilt spreadsheet that automatically updates with your social media KPIs, you can use one of our social media KPI spreadsheet templates available by signing up for Factivate by clicking on the button below:

About Factivate:

Factivate is a software company part of the Google for Entrepreneurs community in Durham, NC. Factivate provides business users with a cloud-based data analytics platform that can sync with different online tools to create automated, real-time spreadsheet reports and web dashboards with just-in-time business reactions. The result of our intelligent spreadsheet is accurate data analysis that drives better business decisions while reducing manual spreadsheet report time and errors by a factor of 150+ hours/employee/year. Factivate requires no learning curve or programming knowledge. In sharp contrast to the complex and expensive business intelligence tools on the market today, if you know spreadsheets, then you know 95% of what you need to use Factivate–we’ve got the rest covered.

]]>How to Analyze Social Media Advertising KPIs on a Spreadsheethttp://blog.factivate.com/social-media-advertising-kpis-spreadsheet/
Fri, 17 Feb 2017 19:05:41 +0000http://localhost:8888/factivate/?p=200So your company has decided to start pushing ads in the social-media-sphere. Great idea! After all, the audience segmenting capabilities of social media platforms allow

]]>So your company has decided to start pushing ads in the social-media-sphere. Great idea! After all, the audience segmenting capabilities of social media platforms allow you to pinpoint consumers based on demographics provided in user profiles. But you can’t stop there! While social media advertisements are ultimately used for a number of reasons (building brand awareness, recognition and relationships, starting conversations, etc.), you want to test and use real time data to make sure these ads are giving you the return on investment you want. You need to test and keep testing. In this article, we will show you how to analyze your social media advertising KPIs using your spreadsheet.

How exactly can you measure ad success you might ask? We’ve broken it down into three categories and eight different key performance indicators (KPIs) to think about so you can better understand and analyze your ad performance.

Note: At Factivate, we’ve built a spreadsheet template to help you track all of your social media ad KPIs and will be referencing that spreadsheet in order help you in this article. If you’d like to use our automated spreadsheet template for social media ad reporting, you can search and sign up for the template through Factivate’s Spreadsheet Template marketplace by clicking on the button below:

If you still would like to build your own, let’s get started breaking down the rates and ratios you’ll need to set up in your spreadsheet.

Costs & Actions

When you push an ad, you’ve got goal in mind. Most commonly, it’s to gain a certain amount of new subscribers, users or customers. Without the proper tracking of data from your ads, you won’t know if you’re even getting close to what you want to achieve. You’ll want to measure the actions taken as a result of your ads (i.e. conversions), and how much it will cost you.

Conversions are the visitors to your site that “convert” into an actual customer. A conversion can be the result of watching a video, signing up for a newsletter, downloading a demo, etc. (something that shows that they are interested in what you are selling). In order to track a conversion through social media ads, you must include a pixel or url code to track the call to action you want your visitor to take (ie, go to your landing page) as a result from the ad. If you omit this, you will not be able to track visitor conversions beyond the given social media site where the ad was placed–and you want to know where they are coming from! Once you’ve set up pixels on your Facebook Ads, etc, you can calculate the conversion rate by dividing the number of conversions attributed to the ad campaign by the total number of people who performed the call to action from your ad in the given campaign.

Cost-per-Acquisition (otherwise known as Cost-per-Conversion) takes the cost of the campaign and divides it by the amount of conversions. This is a metric we pay a lot of attention to because it indicates the amount of money spent to obtain a potential new customer.

Ad Performance

Once you’ve placed your ad, its important to monitor it to know when to increase your spend or take it down. This is where money can be thrown away if you’re not looking at the right key performance indicators (KPIs). Think about it: your ad has flat-lined, is gaining no conversions but you continue to pay for it over the course of a month because you’re seeing thousands of impressions (can you say vanity metrics!) and this runs you about $20,000. You probably say to yourself you’d never do something like that, but if you don’t look at the right ad KPIs, you’re very susceptible to that risk. The following two metrics are often overlooked and they are responsible for saving businesses thousands of dollars.

Fatigue Rate is the KPI that basically tells you the rate at which your ad is being ignored. In brevity, Ad fatigue factors in the number of Click-Throughs over the frequency that your ad is being displayed. This is a metric our campaign managers pay close attention to. Since our campaign managers use our automated Factivate spreadsheets (which are updating 24×7), we are able to immediately spot when an ad is no longer profitable to us when we compare the fatigue rate, conversion rate, and $ per user converted. In such cases, we either tweak the ad or replace it.

Quality Score is a metric that essentially tells you how relevant your ad is. The more relevant your ads are to your demographic, typically, the higher your click-through-rate will be. Facebook doesn’t provide a quality score metric for their ads but has recently started providing Relevance scores to their customers. This is not to be confused with the Quality Score that Adwords will typically provide. In Facebook’s case, think of the relevance score as the grade on your ad before you push it out – not how it is currently being perceived.

Let’s consider a scenario that combines the two together: the fatigue rate of your ad campaign is increasing but the quality score is also high, this means your ad is hitting the right target market, but you are running the risk that your potential customers are overwhelmed with that same message. At that point, doing so AB tests for the ad might help you continue your upwards trend.

Engagements

Impressions refer to the number of times your ad is viewed or displayed on a web page. It’s a great metric to measure how many people your ads are reaching, however, in order to gain further insight, you should reference this metric with the activity data points like number of clicks (which leads to your click-through-rate).

Click-Through-Rate (CTR) is the number of times your ad is clicked divided by the number of impressions. This is a good indicator of how relevant your ad is to your audience. If your CTR is high, it usually tells you that your ad is effective in driving traffic.

Bounce Rate considers the number of people who click-through your ads, but immediately leave the next page. High bounce rates are a huge indicator that there is a problem somewhere in your campaign, content, or even design. In that case, the advertisements with good CTRs can grab the attention of your audience, but either don’t accurately represent what you’re selling or the landing page isn’t meeting the needs of what you are trying to achieve so they bounce.

Hopefully these rates and ratios help you get an idea of how beneficial it is to track you ad campaign data. You can now begin building your own spreadsheet to optimize ad spending (and budgets) by implementing these KPIs! Don’t want to build and always update your own spreadsheet? You can check-out ours here!

]]>Four Spreadsheet Templates to Help you Analyze Your Social Mediahttp://blog.factivate.com/four-spreadsheet-templates-to-analyze-social-media/
Fri, 17 Feb 2017 19:05:34 +0000http://localhost:8888/factivate/?p=202One of the most often requested Google searches relates to finding the ideal spreadsheet template that digital marketers can use to analyze social media. The

]]>One of the most often requested Google searches relates to finding the ideal spreadsheet template that digital marketers can use to analyze social media. The problem with many of these searches is that many of you might not have a lot of confidence that the KPIs you’re currently tracking are the same ones that many industry veterans now consider to be best practices. In this article, we’ll provide you with a few key resources to help you build and analyze your social media content which include some of the most popular spreadsheet templates to help you analyze you social media. It’s time to optimize your digital marketing scheme and reach those benchmarks your CEO is looking for!

1. Social Media Audit Spreadsheet Template

As mentioned in a recent Harvard Business Review article, 25% of content about a brand or company is voiced by consumers on social media networks. So, first and foremost, it’s important to understand where your brand sits in the social-media-sphere. HBR’s social media audit template is a great place to start (the image below is an example already filled in), and you can easily enter the categories into a spreadsheet to keep your findings handy. By conducting an audit, you will gain an understanding of what your consumers are saying about your brand and potentially find insights to spark upcoming campaign concepts.

2. Social Media Strategy Spreadsheet Template

After analyzing what’s being said (or not said) about your company across the different social communities, you can begin to devise a strategy. There are many ways to go about this (and also get lost going about this) so Hubspot created a guide that helps lay out and organize what needs to be done for your brand on social media and how to go about it. It might not exactly be a spreadsheet, but you need this tool!

3. Editorial Content Calendar Spreadsheet Template

Now that you’ve devised the types of messages you want to send to your audience, you can create an editorial content calendar (hint: 90% of marketers are using content calendars according to Curata research). Calendars allow you to organize details and topics that you’d like to incorporate in your posts so you can craft and curate the best content. At Factivate, we create content agendas for all our social media sites, but pay special attention to what we push out on Facebook because that’s where we are currently seeing the most activity.

4. Social Media Performance Spreadsheet Template (the fun part!)

In a report published by the Social Media Examiner, 88% of marketers want to learn more about measuring the success of their social media efforts and 87% don’t know what tools are best to do so. But don’t worry, we’re here to help. Now that you’re rolling with content–well-thought out, organized and effective thanks to your spreadsheet calendar–you can get to analyzing how your posts are performing. Are you taking strides toward your social goals? Are you gaining desired traction? It’s time to look at what is working and what is not so you can get that ROI.

At Factivate, we start by considering the three main social media key performance indicators (KPIs): conversation rate, applause rate, and amplification rate (learn more about them here). These metrics essentially tell you in different ways whether the messages you’re sending are being accepted by your target audience. If we notice a significant increase in any of the rates, we then look a little closer into the metrics from individual posts to figure out why they did so well and what actions were taken by consumers as a result.

There are plenty more KPIs to look at when analyzing your data, but make sure that you focus primarily those that express some sort of behavior. Did they interact with the post? Did they go to the landing page or follow the desired call-to-action? As digital marketing expert Jay Baer puts it best, “The end goal is action, not eyeballs.” Below we’ve included a couple screen shots to give you an idea of what our spreadsheets look like.

So there you have it. Some of the best ways to start analyzing your social media.

Interested in finding spreadsheets to analyze more? We have a full line up of all the spreadsheets you’ll ever need to measure your marketing metrics and KPIs. Seriously, we make reporting these numbers a breeze. Check out our template marketplace here!

]]>5 Instagram KPIs You Can Track in a Spreadsheethttp://blog.factivate.com/5-instagram-kpis-can-track-spreadsheet/
Fri, 17 Feb 2017 19:05:26 +0000http://localhost:8888/factivate/?p=204If you are new to Instagram ads, then you might be feeling a bit confused as to the differences between the social media platforms. This

]]>If you are new to Instagram ads, then you might be feeling a bit confused as to the differences between the social media platforms. This article was written to help you find your bearings by laying out the top five Instagram KPIs you should be tracking. We’ve also included included images of how we set-up our spreadsheets to measure these KPIs to help you as you build your own reports.

Setting you Your Marketing Analytics Spreadsheet with Instagram KPIs

Across all social media websites, the metrics and key performance indicators you want to measure are relatively standard. The differences depend on how each social media website defines its user engagements. For Instagram, engagements include double-tapping which constitutes a like (applause) or commenting (conversation). There is no current method for sharing (amplification) on Instagram like there is Twitter and Facebook so in the following spreadsheet images, the amplification column is either zeroed or omitted. Below is a preview of a template we used to automatically track KPIs using rates and ratios to track our content performance.

Now that you have a visual understanding of what we are using, let’s dive into what exactly it is that we look at with Instagram ads.

1. Applause Rate

This refers to the average number of likes each post earns. Essentially, applause rate tells the base number of people who expressed some sort of emotional to your ad. We look at this to understand help us understand how many of our followers are actively looking at our content.

2. Conversation Rate

This is the number of people who felt compelled to join or start a discussion about the post. Conversation rate is typically lower than applause rates due to the extended effort it takes to comment, thus showing who has increased interest in your content.

Usually, what would come next for us as a KPI we measure on all of our social media marketing would be amplification rate. This metric refers to the number of times a post is shared for a 2nd level audience to see, meaning followers of followers (those not directly following your account). However, due to the fact that sharing can only be executed through a third party application or by direct messaging, there is currently no trackable way to share/re-post on ads Instagram.

3. Total Social Actions per Post

To gather the measurement for total social actions per post, divide the total number of likes and comments by the number of posts during a given amount of time. This gives an overview of how the content was generally received by your audience. This will give you an idea of what time of content to share on your Instagram for ideal traffic. Obviously, a post with a higher number of total social actions is something you want to look further into for an opportunity to expand on the topic of the content further.

You will see in the image below that we use a composite of the first three metrics on this list to determine if an ad is ready to be created into a campaign. We use A/B testing to determine the best option for all of our ads. In the far right column, rows 17 and 18 were determined “ad ready” because of they earned a total of 50 social interactions (this is our benchmark–yours will be higher or lower depending on the stage of your company).

4. Network Growth

Instagram’s sponsored ad show up in the feeds of people who do not follow you. Tracking your network growth from your ads is a great way to see whether or not your content is enticing enough to bring in new followers. In order to determine your network growth, you must divide the number of follower you end with and divide by the number you started with during a given amount of time.

5. Click-Through Rate

Although Instagram does not allow links or urls in post descriptions, they have added “learn more” button with sponsored content so you can redirect your audience to your website or blog. This will allow you to see exactly who is interested enough in your content to further investigate what your company is all about.

While you want to track likes, comments and network growth often, click-through rate is a metric that determines your ROI. Think about it. If you pay to push an ad that attracts nobody to your website, it ultimately will have little effect on your conversion rates. You want to keep a close eye on this number to make sure your ad is not just something people scroll past an ignore.

]]>The top Google Analytics Digital Marketing KPIs for SaaS Entrepreneurshttp://blog.factivate.com/top-google-analytics-digital-marketing-kpis-saas-entrepreneurs/
Fri, 17 Feb 2017 19:05:19 +0000http://localhost:8888/factivate/?p=206In the age of web tracking and data collection, knowing your numbers is more important than ever. Not only can you set goals, but you

]]>In the age of web tracking and data collection, knowing your numbers is more important than ever. Not only can you set goals, but you can also identify areas of your website, marketing and sales strategies that are in need of improvement. This article will highlight the top Google Analytics key performance indicators (KPIs) that your SaaS or e-commerce company should be paying attention to and why.

Before diving too far deep into the pool of web analytics and monitoring KPIs, you should have some sort of goal in mind. For SaaS start-ups, goals often focus on gaining a specific number of new subscribers (to test and determine best the best path to your goal, we’ve outlined how to set up a Google Analytics Conversion Funnel you might want to take a look at). You should also understand that your data points and KPIs can do one of two things: they can simply show you something, or they can prove something.

As far as web tracking is concerned, Google Analytics measures data points like total visits, unique visitors, page views, and session duration. While it’s good to be aware of these numbers, they don’t necessarily give you a clear insight as to what is working and what’s not. In fact, these KPIs can often lead to assumptions like saying, “My website traffic is up 50% from last month – this is great!” Such assumptions can potentially lead to empty advertising and marketing investments. How you might ask?

Consider the following scenario:

Your company puts up a billboard along the highway; in one day, 10,000 people drive by and see it. This doesn’t mean 10,000 customers for your business because not all of the people driving by are within the audience you are trying to reach. Instead, you’d want to focus on the fact that only 500 people called the phone number on the billboard and ordered the free trial (or what would be considered a 5% conversion rate).

The same concept applies to the data from your website. Say your company’s site has 600 unique visitors. The number you should really be looking at is the 100 people who subscribed to your newsletter, blog or purchased a product (about a 17% conversion rate) as a result of looking around on your website.

Fortunately, Google Analytics also tracks actionable KPIs like conversions (as mentioned above), bounce rates, and measures A/B testing as well. These KPIs are great to monitor because they offer insight into your customer base so let’s break them down a bit.

Conversions

User conversions happen when a visitor turns takes a desired action into your client pipeline. This could be a visitor filling out a form, being highly active on the website, or making a purchase. Essentially, this is when a visitor converts into a customer. Very low conversion rates identify that there’s an issue with your product, or at the very least, a serious problem with the way you present it on your website.

Bounce rate

The user bounce rate is a ratio that refers to the percentage of people who leave your site after viewing one page. This can often tell you whether or not people are finding what they are looking for when visiting your site. In marketing terms, a low bounce rate can mean you’re reaching your target audience and bringing in the right group of potential customers (vice-versa).

A/B testing is a method used that compares the success rate of two different scenarios (most commonly testing web pages). When used correctly, this method can guide you away from mistakes with your marketing campaigns. You might choose to test two different landing pages, one of which yields 25% more sign-ups that the other. Moving forward, you would continue to use the landing page with the greater success in order to facilitate a higher conversion rates (and sales).

Keeping an eye on these metrics allows you to better identify how customers are reacting to your over all product, website and marketing tactics. After all, this data is unique and actionable for your company–use it right!

So how can you get all of these metrics that lead to power-decisions for your business?

At Factivate, we’ve created a custom template to help you track all of your Google Analytics KPIs in the friendly form of a cloud-based spreadsheet. Automatically updating 24×7, our spreadsheets and dashboard displays allow for real-time analytics to help you become more informed about your business in the snap of a finger. Click here to check out your very own Google Analytics spreadsheet template!

Have some favorite KPIs or ratios of your own? We want to hear – share and comment below!