[Analytics Intern] How did I develop analytics structure for the NGO social media?

In the second week of internship, I started my first task of building up the structure of social media analytics. I positioned this point as the “Revelation of status quo.” Previous analyst gathered the data in the excel spread sheet by month, just like below example.

It only shows the trend of different metrics, and those metrics were just the basic metrics provided by social media itself. It lacks several items I thought is very crucial for revealing status quo.

1. Comparison: We could hardly compare the data on monthly and yearly basis. How much did it rise or decline? Did it perform better than the previous period? We don’t know. Comparison is the way we defined our present performance with the benchmark with historic data.
2. Ratio: Some ratio is missing here. Metrics provided by social media are quite direct. But with ratio, some interesting information can be disclosed. For example, if an organization is paying for some ad campaign. It may be a meaningful ratio of paid reach over total reach. It is the metric to identify the composition of our traffic. And we could later on to locate the optimal percentage to balance the paid and organic reach.
3. Visualization: Quoted from my colleague, who is not that familiar with data and meanings behind it, she is just confused about how these numbers are telling what kinds of stories. The solution to this I think is to really present a data in a very visualized way and marked all the measurements with simple interpretation . Then I got to make some graphs and charts here.

Based on these missing items and the goal of the organization. I sketched the analytics structure in a simple way, trying to exhaust all the possibilities of breaking down the data at hand and making it super understandable.

So far I am in charge with two social media, Facebook and Twitter. For each of them, I broke down the performances into three categories: overall performance, paid ad performance and post performance.

1. Overall performance: It is the easiest part. I just copied and pasted the data collected by previous analyst. But what I did more was to list the data on the yearly basis (x axis is year ; y axis is month). Then we could see the change over year (YoY), and we can further calculate the change between months (MoM). And with the data table set already, it will be simple to plot the graph for that.
2. Paid performance: I didn’t go that far in the area. But since budget is always a big issue in every organization. I really need to leave a space for this it. So far I put the ratio of paid reach percentage and cost per reach here, trying to give the marketer a clearer picture if the budget is spent smart and worthwhile.
3. Post performance: It is always the biggest challenge for me to deal with posts. Texts are the forever issues to me. The goal in this section is to find the logic of which post has potential to create biggest value, maybe engagement, maybe shares, maybe comments. There are just so many variables that influence the performance of a single text, not to mention there are 30–40 posts a month. I just did some pivot tables here, trying to conclude what types of post are most likely to acquire most engagement. However, I will keep digging methods or tools which allow me to analyze texts in a more systematic way.

Though there are still many daunting challenges before me, it is always a good start to plot the analytics structure. It also presents some guideline for prioritization. Just like the structure above, I can then understand how much time I should spend on each analytics project and what I should learn before getting into specific sections. (i.e. text analytics and some visualization tools)

Photo by André Sanano on Unsplash

Not only do I see myself as an amateur analyst, but also I try to plod away at my lesson 101 of product/project management. Seeing myself as a product, seeing these social media as products, I learn how to use the limited resources (i.e. time) to reach the optimal solutions in the very end.

P.S. I will show more results of analytics, including charts and graphs I made (of course using some fake data), in the next medium article.

If you like the article, just feel free to click the clap button on this page. Furthermore, if you have some sources about learning text analytics and visualization, just feel free to leave some remarks and feedback in the comment areas. I will be super grateful for that :)