What social listening measures first
Every day people mention brands in posts, comments, replies, reviews and forum threads without ever writing to the company. Social listening is the habit of collecting those public mentions and reading them in a structured way: how many there are, where they appear, what they are about and whether the tone is friendly. It is different from the channel statistics a social media manager reports. Channel statistics describe what happened to your own posts. Listening describes what other people wrote, whether or not they tagged you.
The first measure in any listening report is mention volume: the number of posts that named the brand in a period. It looks too simple to need a lesson, and yet most reporting mistakes start here. A wrong range puts a date into a total. An unlocked divisor produces shares that do not add up. An average is taken over the wrong cells. Later measures such as net sentiment, share of voice or an alert threshold are all built from mention counts, so an error at this stage travels through the whole report.
This article works through the same table as the lab above: fourteen days of mentions of Quillhaven Tea Co. on four channels. Quillhaven is a fictional tea brand; all companies, people, posts and numbers on this page are invented for learning, and every benchmark is illustrative. The channel names (X, Instagram, TikTok, Reddit) are used descriptively. Nothing here is official platform data, and the arithmetic works the same whichever listening tool exported the counts.
Where the counts come from, and what they do not tell you
A mention count is the result of a query: a set of keywords such as the brand name, common misspellings, product names and the brand's handle. A tool, or a person with a search box, collects the public posts that match. Three things follow from that, and each of them is worth one line in the footnote of a report.
- The query defines the number. Add the product name “Cold Brew Kit” to the query and the volume rises overnight without anybody talking more. Keep the query fixed for a reporting period and note every change.
- Only public posts are counted. Private messages, closed groups and stories that have expired are invisible. Volume is a sample of the conversation, never all of it.
- A mention is not a person and not an opinion. One author can write ten posts, and a post can be praise, a question or a complaint. Counting comes first; reading comes afterwards.
The mention table: one row per day, one column per channel
The standard layout is a small grid. Each row is a day, each column a channel, each cell the number of mentions on that channel on that day. Two directions of adding answer two different questions. Adding across a row gives the daily total: how loud was that day? Adding down a column gives the channel total: how loud is that channel over the period? Keeping the two apart is most of the skill in this lesson. From those totals come three derived figures. A channel share is the channel total divided by the grand total. The average day is the mean of the daily totals and serves as a first baseline. The peak-to-average ratio divides the busiest day by the average day and says in one number whether the period was flat or contained an event.
A fully worked example: Quillhaven Tea Co., 1–14 June 2026
Here is the complete table from the lab, with the calculated total column, the total row and the share row already filled in. On 9 June Quillhaven launched its Cold Brew Kit.
| Day | X | TikTok | Total mentions | ||
|---|---|---|---|---|---|
| 1 Jun | 42 | 58 | 25 | 12 | 137 |
| 2 Jun | 38 | 61 | 22 | 15 | 136 |
| 3 Jun | 45 | 55 | 30 | 11 | 141 |
| 4 Jun | 40 | 63 | 27 | 14 | 144 |
| 5 Jun | 36 | 60 | 24 | 13 | 133 |
| 6 Jun | 30 | 72 | 35 | 9 | 146 |
| 7 Jun | 33 | 70 | 38 | 10 | 151 |
| 8 Jun | 44 | 57 | 26 | 16 | 143 |
| 9 Jun | 96 | 88 | 120 | 34 | 338 |
| 10 Jun | 71 | 90 | 84 | 29 | 274 |
| 11 Jun | 50 | 66 | 45 | 18 | 179 |
| 12 Jun | 43 | 59 | 31 | 14 | 147 |
| 13 Jun | 39 | 62 | 28 | 12 | 141 |
| 14 Jun | 35 | 68 | 33 | 11 | 147 |
| Total 14 days | 642 | 929 | 568 | 218 | 2,357 |
| Share of mentions | 27.2 % | 39.4 % | 24.1 % | 9.2 % | 100.0 % |
Read the table before calculating anything. On the first eight days the daily total moves between 133 and 151 mentions. On 9 June it jumps to 338, on 10 June it is still 274, on 11 June 179, and from 12 June it is back in the old range. That shape, a sharp rise and a decline over two or three days, is typical of a single event such as a launch, a creator video or a news story.
Step 1: daily totals with SUM across the row
The daily total in F4 is =SUM(B4:E4), copied down to F17. The range covers the four channel columns and nothing else. For 6 June (row 9) this gives 30 + 72 + 35 + 9 = 146 mentions; for the launch day (row 12) 96 + 88 + 120 + 34 = 338.
=SUM(A9:E9) therefore returns 46,325 instead of 146.Step 2: channel totals and the grand total
The 14-day total of X in B18 is =SUM(B4:B17). Copied to the right as far as F18, the same formula adds each channel and, in column F, the daily totals. The results: X 642, Instagram 929, TikTok 568, Reddit 218, and a grand total of 2,357 mentions. There is a built-in check: the four channel totals must add up to the total of column F. 642 + 929 + 568 + 218 = 2,357, so rows and columns agree.
Step 3: channel shares with an absolute reference
The share of X in B19 is =B18/$F$18. The dollar signs make F18 an absolute reference: when the formula is copied to C19, D19 and E19, the numerator moves along (C18, D18, E18) while the divisor stays on the grand total. Without the dollar signs the copy in C19 would read =C18/G18, and because G18 is empty the result is the error #DIV/0!.
The shares are 27.2 % for X, 39.4 % for Instagram, 24.1 % for TikTok and 9.2 % for Reddit. Instagram carries the largest part of the conversation, and Reddit less than a tenth. Add the four shares and you get 100 %. Make that sum a habit: shares that add up to 97 % or 104 % always point to a range that is one row too short or a divisor that moved.
Step 4: the average day
The average day in F21 is =AVERAGE(F4:F17): 2,357 mentions ÷ 14 days = 168.4 mentions per day. The important detail is which cells are averaged. =AVERAGE(B4:E17) looks similar but averages the 56 single channel-day cells and returns 42.1, a quarter of the right answer. The average day is the average of the daily totals. An average is pulled up by unusual days. Without 9 and 10 June the other twelve days average 145.4 mentions, and the median day, the middle value of all fourteen, is 145. Neither number is wrong. The mean answers “how many mentions did a day bring on average?”, the median answers “what does a normal day look like?”. Lesson 2 of the course replaces both with a rolling baseline that only looks at the days before the one you judge.
Step 5: the peak-to-average ratio
The last cell, F22, divides the busiest day by the average day: =MAX(F4:F17)/F21 = 338 ÷ 168.4 = 2.01. A value close to 1 would mean that every day looked alike. A value of 2 says that the loudest day carried twice the usual volume, a clear event. As a rough, illustrative reading: below 1.3 the period was flat, between 1.3 and 2 it was lively, and from 2 upwards something happened that deserves a sentence in the report. The ratio understates the launch slightly, because the peak days are part of the average they are compared with. Measured against the twelve ordinary days, 9 June is 338 ÷ 145.4 = 2.32 times a normal day. That is exactly the weakness the rolling baseline of lesson 2 removes.
How to read the result: who made the peak?
A total hides its parts. To see which channel produced the peak, compare each channel's mentions on 9 June with its own average on the eight days before.
| Channel | Average day, 1–8 June | Mentions on 9 June | 9 June ÷ its own average | Share of the 9 June total | Share of the 14 days |
|---|---|---|---|---|---|
| X | 38.5 | 96 | 2.5× | 28.4 % | 27.2 % |
| 62.0 | 88 | 1.4× | 26.0 % | 39.4 % | |
| TikTok | 28.4 | 120 | 4.2× | 35.5 % | 24.1 % |
| 12.5 | 34 | 2.7× | 10.1 % | 9.2 % |
TikTok had 28.4 mentions on an average day before the launch and 120 on 9 June, 4.2 times its normal level. Instagram, the largest channel overall, only rose to 1.4 times its average. On the launch day TikTok held 35.5 % of all mentions, against 24.1 % over the fortnight. The sentence for the report is therefore not “mentions doubled” but “the launch was carried by TikTok, where volume on the launch day was about four times the usual level”. Even that sentence says nothing about tone. A launch that people like and a product fault that people film produce the same column chart. Volume tells you when and where to look; the posts themselves tell you what you are looking at. That is why the course moves from counting to sentiment, topics and authors.
Common mistakes and how to spot them
- The date column inside the SUM.
=SUM(A4:E4)adds the date's serial number. Totals above 46,000 on a quiet day give it away. - A channel left out.
=SUM(B4:D4)ignores Reddit. The check is the cross-total: the channel totals must add up to the total of column F. - Unlocked grand total.
=B18/F18works in B19 and breaks when copied to the right. Write$F$18. - Dividing by the wrong total. TikTok ÷ Instagram is a ratio between two channels, not a share. A share always divides by the whole.
- Averaging the wrong cells.
=AVERAGE(B4:E17)returns 42.1, the average channel-day cell, instead of the average day of 168.4. - Typing results instead of formulas. A typed 2,357 does not change when a day is corrected. The lab checks that each cell contains a formula for the same reason.
- Comparing periods of different length. A 14-day total against a 30-day total says nothing. Compare averages per day, or equal periods.
- Changing the query mid-period. A jump that starts on the day a keyword was added is a change of method, not of the conversation.
Quick reference: functions in this lesson
| Function / technique | What it does here | Example |
|---|---|---|
SUM across a row | Total mentions of one day | =SUM(B4:E4) |
SUM down a column | Total mentions of one channel | =SUM(B4:B17) |
| Absolute reference | Keeps the grand total fixed when the formula is copied | =B18/$F$18 |
AVERAGE | The average day, the baseline for comparisons | =AVERAGE(F4:F17) |
MAX | The busiest day | =MAX(F4:F17) |
| Peak-to-average ratio | One number for flat or eventful | =MAX(F4:F17)/F21 |
MEDIAN (optional) | The typical day, unaffected by one peak | =MEDIAN(F4:F17) |
Using this on your own brand
- Export or note the daily mention counts per channel for at least two weeks. If you have no listening tool, a manual search count per channel at the same time every day is enough to learn the method.
- Lay the data out as in the lab: dates in column A, one column per channel, a total column on the right and a total row below.
- Add the cross-check first: the sum of the channel totals must equal the sum of the daily totals.
- Calculate the channel shares with a locked grand total and confirm that they add up to 100 %.
- Calculate the average day and the peak-to-average ratio, then open the posts of the peak day before you write a single sentence about it.
- Write the query, the period and the date of the export under the table. A count without its query cannot be repeated.
- Keep the raw counts and the calculations apart, as in the lab: when a corrected export arrives you paste the new numbers into B4:E17 and every total, share and ratio updates itself.
What you will build in the rest of this course
Lesson 1 is free. Lessons 2 to 20 each have their own live lab with a new Quillhaven data set, and they follow the workflow of Figure 1 from counting to reading and acting:
- Lab 2: Spikes: a Rolling 7-Day Baseline, the Mentions-to-Baseline Ratio and a Spike Flag. You build a rolling baseline from the seven days before each day, compare every day with it, flag spikes with a rule kept in a cell and chart the three weeks.
- Lab 3: Sentiment Coding: Counting Positive, Neutral and Negative Comments, Shares and Negative Share per Channel. You count hand-coded comments per sentiment label, turn the counts into shares, compare the likes behind each label and find the channel with the highest negative share.
- Lab 4: Net Sentiment: (Positive − Negative) ÷ Total, the Variant Without Neutrals and the Positive-to-Negative Ratio. You calculate weekly net sentiment, the positive-to-negative ratio and two period-level versions of net sentiment from the sums, then chart the weekly line.
- Lab 5: Sentiment Scores: a −2 to +2 Scale, the Simple Mean, a Reach-Weighted Mean and a 0–100 Index. You score posts on a five-point scale, compare the simple mean with a reach-weighted mean, rescale it to a 0–100 index and measure how much reach sits behind negative posts.
- Lab 6: Share of Voice: Brand Mentions ÷ Category Mentions, Ranks and Share of Voice per Channel. You total the mentions of five brands, calculate each brand's share of voice, rank the brands, split one brand's share of voice by channel and chart the result.
- Lab 7: Share of Positive and Negative Voice: Who Owns the Praise, Who Owns the Complaints, and a Positive Index. You compare five brands on net sentiment, share of voice, share of positive and negative voice and an index that shows who gets more than a fair share of the praise.
- Lab 8: Topic Coding: Counting Comments per Topic, Negative Share by Topic and the Topic That Drives Complaints. You count coded comments per topic, find each topic's negative share and the likes behind its complaints, return the top complaint topic with INDEX/MATCH and colour the negative rows.
- Lab 9: Keyword Rules: Pre-Coding Comments with SEARCH, a Flag Grid with Mixed References, Coverage and False Hits. You build a keyword flag grid with SEARCH and mixed references, count hits per comment and per keyword, weight the keywords by likes and measure how much of the data the rules cover.
- Lab 10: Alert Thresholds: Baseline Mean, Standard Deviation, z-Scores and a Mean + k × SD Rule. You derive an alert threshold for negative mentions from a baseline period with AVERAGE and STDEV.S, score the monitored days with z-scores, flag alerts and colour them.
- Lab 11: Tuning Alerts on Past Incidents: Alerts Fired, False Alarms, Precision and Recall for Five Thresholds. You replay five alert thresholds against sixteen past days with known incidents, count true alerts and false alarms, calculate precision and recall and chart the trade-off.
- Lab 12: Crisis Timeline: Hourly Negative Share, Cumulative Mentions, the Trigger Hour, the Peak Hour and Time to Peak. You reconstruct an hour-by-hour crisis log: negative share, running total, the first hour above a trigger, the peak hour, the hours between the two and a combo chart of the day.
- Lab 13: Response Time: Minutes from Post to First Reply, a Response Target, Median vs Mean and Channel Averages. You turn two timestamps into response minutes, check each case against a response target, compare the median with the mean and find the slowest channel with AVERAGEIFS.
- Lab 14: Who Drives the Conversation: Engagements per Mention, Author Tiers, Ranks and the Top-3 Share. You profile the most active authors: engagements per mention, a tier from follower limits, a rank, the share of engagements held by the top three and the share of mentions written by small accounts.
- Lab 15: Quality Check: Tool vs Analyst Labels, Accuracy, Precision and Recall of the Negative Label, and a Confidence Cut-Off. You audit an automatic sentiment tool against an analyst's labels: agreement per comment, accuracy, precision and recall for the Negative label and accuracy above a confidence cut-off.
- Lab 16: How Exact Is a Sentiment Share? Margin of Error, Confidence Bounds, a Reporting Rule and the Sample to Code. You attach a 95 % margin of error to the negative share of each topic, build lower and upper bounds, decide which shares are solid enough to report and size a coding sample.
- Lab 17: Expected Volume: a Linear Trend with SLOPE and FORECAST.LINEAR, Deviation from Expected and an Unusual-Week Flag. You fit a linear trend to twelve weeks of mentions, forecast the next two weeks, measure how far the actual weeks deviate from the expectation and flag the unusual one.
- Lab 18: Why Did Net Sentiment Move? Topic Contributions, Month-on-Month Change and the Topic That Dragged It Down. You split net sentiment into topic contributions for two months, calculate the change per topic, check that the parts add up to the total and name the topic that explains the drop.
- Lab 19: What-If Grid: Agents Needed by Spike Size and Negative Share, a Heat Map, Shift Capacity and the Backlog. You build a two-way grid of agents needed for different spike sizes and negative shares with mixed references, shade it as a heat map, solve for the largest spike a shift can answer and size a backlog.
- Lab 20: Final Project: Monthly Listening Scorecard with Mentions, Net Sentiment, Share of Voice, a Review Flag and a Combo Chart. You roll sixteen weekly log rows up into a monthly scorecard with mentions, negative mentions, net sentiment and share of voice, flag the months to review, colour them and chart volume against negative mentions.
Summary
Mention volume is the foundation of a listening report. Daily totals are a SUM across the channels, channel totals a SUM down the column, and a channel share divides by a grand total locked with dollar signs. In the Quillhaven example 2,357 mentions in 14 days give an average day of 168.4, Instagram holds 39.4 % of the conversation, and the launch day reaches 338 mentions, 2.01 times the average, driven mainly by TikTok. Volume shows when and where something happened; it does not show whether people were pleased. Now scroll up to the lab and build the sheet yourself. It takes about a quarter of an hour, the Coach checks every step, and you finish with a table layout you can reuse for your own brand's mentions.
Frequently asked questions
What is social listening?
Social listening means collecting public posts, comments, reviews and forum threads that mention a brand, and analysing them in a structured way: how many mentions there are, on which channels, about which topics and in what tone. It looks at what other people write, not at the statistics of the brand's own posts.
What is mention volume and how do I calculate it in Excel?
Mention volume is the number of posts that name the brand in a period. With one row per day and one column per channel, the daily total is =SUM(B4:E4) across the row and the channel total is =SUM(B4:B17) down the column. The sum of the channel totals must equal the sum of the daily totals.
How do I calculate a channel's share of mentions?
Divide the channel total by the grand total and lock the grand total with dollar signs, for example =B18/$F$18. Copied to the other channels, the divisor stays fixed. All channel shares together must add up to 100 %.
What is the peak-to-average ratio?
It is the busiest day divided by the average day: =MAX(F4:F17)/AVERAGE(F4:F17). A value near 1 means a flat period. A value of 2 means the loudest day had twice the usual volume, which usually points to an event such as a launch, a creator video or a complaint wave.
Does a spike in mentions mean something went wrong?
No. A product launch, a giveaway and a product fault all produce a spike. Volume shows when and where to look. Whether the tone is positive or negative only becomes clear when the posts are read and coded by sentiment and topic.
Do I need a paid listening tool to follow this course?
No. The labs run in the browser with fictional data. The methods work with any export of mention counts or coded comments, and even with counts collected by hand, because the course teaches the arithmetic rather than a specific tool.
Keep going: Social Listening & Sentiment Analysis: Mention Volume, Spikes, Net Sentiment, Share of Voice, Topic Coding, Alerts & Crisis Timelines
This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.
- Spikes: a Rolling 7-Day Baseline, the Mentions-to-Baseline Ratio and a Spike Flag
- Sentiment Coding: Counting Positive, Neutral and Negative Comments, Shares and Negative Share per Channel
- Net Sentiment: (Positive − Negative) ÷ Total, the Variant Without Neutrals and the Positive-to-Negative Ratio
- Sentiment Scores: a −2 to +2 Scale, the Simple Mean, a Reach-Weighted Mean and a 0–100 Index
- Share of Voice: Brand Mentions ÷ Category Mentions, Ranks and Share of Voice per Channel
- Share of Positive and Negative Voice: Who Owns the Praise, Who Owns the Complaints, and a Positive Index
- Topic Coding: Counting Comments per Topic, Negative Share by Topic and the Topic That Drives Complaints
- Keyword Rules: Pre-Coding Comments with SEARCH, a Flag Grid with Mixed References, Coverage and False Hits
- Alert Thresholds: Baseline Mean, Standard Deviation, z-Scores and a Mean + k × SD Rule
- Tuning Alerts on Past Incidents: Alerts Fired, False Alarms, Precision and Recall for Five Thresholds
- Crisis Timeline: Hourly Negative Share, Cumulative Mentions, the Trigger Hour, the Peak Hour and Time to Peak
- Response Time: Minutes from Post to First Reply, a Response Target, Median vs Mean and Channel Averages
- Who Drives the Conversation: Engagements per Mention, Author Tiers, Ranks and the Top-3 Share
- Quality Check: Tool vs Analyst Labels, Accuracy, Precision and Recall of the Negative Label, and a Confidence Cut-Off
- How Exact Is a Sentiment Share? Margin of Error, Confidence Bounds, a Reporting Rule and the Sample to Code
- Expected Volume: a Linear Trend with SLOPE and FORECAST.LINEAR, Deviation from Expected and an Unusual-Week Flag
- Why Did Net Sentiment Move? Topic Contributions, Month-on-Month Change and the Topic That Dragged It Down
- What-If Grid: Agents Needed by Spike Size and Negative Share, a Heat Map, Shift Capacity and the Backlog
- Final Project: Monthly Listening Scorecard with Mentions, Net Sentiment, Share of Voice, a Review Flag and a Combo Chart