JoshSelect
New Member
- Joined
- Mar 10, 2015
- Messages
- 1
So I'm designing a spreadsheet that will help our company analyse and compare our own social network activity/popularity vs. our competitors.
The table, thus far, details the amount of likes/followers that companies (including our own) have on both their Facebook & Twitter accounts. It is updated every Friday.
The end result is a table that can provide analytic graphs displaying the progressive change/decline in Social Account popularity over time, whilst also displaying which companies have the most likes/follows in total.
The "Y (far-left column) Axis" columns display a list of competitors followed by a column defining what social network is being analysed on the row. So for each competitor, there are two networks; Facebook & Twitter. e.g. 4-5A Merged: Logic Vending, 4B: Facebook, 5B: Twitter.
The "X (top row) Axis" rows display each month of the year, followed by date and then data fields. e.g [1C-1V Merged: January] followed by [2C-F Merged: 2nd], [2G-J Merged: 9th], [2K-N Merged: 16th], [2O-R Merged: 23rd], [2S-2V: 30th]. An example of the data fields beneath "2C-F" - [3C: Network Figure (likes/follows per account), 3D: Change Per Period (difference in Network Figure since last date +/-), 3E: Totals (total of likes and follows across both accounts), 3F: Total Change Per Period (difference in Totals figure since last date +/-).
I'm not sure how easy that was to understand but for examples sake: Say I was watching "Express Vending" [Company], my table shows how many likes it had on Facebook, on February 6th 2015 (69)[Network Figure], how many followers it had on Twitter on February 6th 2015 (1,028)[Network Figure] and calculates the total of those figures combined (1,097)[Totals]. It also shows me how many likes it had on February 13th 2015 (90)[Network Figure], calculates the difference between this figure and the figure displayed on February 6th 2015 (21)[Change Per Period], how many followers it had on Twitter on February 13th 2015 (1,033)[Network Figure], calculates the difference between this figure and the figure displayed on February 6th 2015 (5)[Change Per Period], the total of the new figures combined (1,123)[Totals] and the difference between this figure and the figure displayed on February 6th 2015 (26)[Total Change Per Period]
For every Friday of every month I input the new Likes & Follows into the table, formatted equations work out the Totals and Differences. However, what I am struggling to do, is create a table that shows the companies (in ascending order, most to least) with their total likes/follows for the specified Friday date. So I have one comprehensive table, and then three additional tables; one focussing on the total Facebook likes and differences, one that focusses on the total Twitter followers and differences and one that focusses on the total figures combined and total differences, all in leader board fashion, for every Friday of every month of the year. Same as the main table that already exists. At the moment, this is a manual process. But I would like to be able to input data into the main table and have it translated into the three additional tables, automatically; moving companies depending on their current "rank" (1-15 companies in total). 1 being the best, 15 being the worst.
Over time I can monitor growth and change of each company and cross-check this information with other tables I have monitoring the type of content AND consistency of activity each page displays. Valuable stuff if you want to find out what works through Social Media in your industry.
In an ideal world, I would have three additional sub-tables that show the current leader boards at a glance. One main table that shows all account change over time, three comprehensive sub-tables that show network/total specific change over time and three further sub-tables that show the current figures.
Any ideas?
The table, thus far, details the amount of likes/followers that companies (including our own) have on both their Facebook & Twitter accounts. It is updated every Friday.
The end result is a table that can provide analytic graphs displaying the progressive change/decline in Social Account popularity over time, whilst also displaying which companies have the most likes/follows in total.
The "Y (far-left column) Axis" columns display a list of competitors followed by a column defining what social network is being analysed on the row. So for each competitor, there are two networks; Facebook & Twitter. e.g. 4-5A Merged: Logic Vending, 4B: Facebook, 5B: Twitter.
The "X (top row) Axis" rows display each month of the year, followed by date and then data fields. e.g [1C-1V Merged: January] followed by [2C-F Merged: 2nd], [2G-J Merged: 9th], [2K-N Merged: 16th], [2O-R Merged: 23rd], [2S-2V: 30th]. An example of the data fields beneath "2C-F" - [3C: Network Figure (likes/follows per account), 3D: Change Per Period (difference in Network Figure since last date +/-), 3E: Totals (total of likes and follows across both accounts), 3F: Total Change Per Period (difference in Totals figure since last date +/-).
I'm not sure how easy that was to understand but for examples sake: Say I was watching "Express Vending" [Company], my table shows how many likes it had on Facebook, on February 6th 2015 (69)[Network Figure], how many followers it had on Twitter on February 6th 2015 (1,028)[Network Figure] and calculates the total of those figures combined (1,097)[Totals]. It also shows me how many likes it had on February 13th 2015 (90)[Network Figure], calculates the difference between this figure and the figure displayed on February 6th 2015 (21)[Change Per Period], how many followers it had on Twitter on February 13th 2015 (1,033)[Network Figure], calculates the difference between this figure and the figure displayed on February 6th 2015 (5)[Change Per Period], the total of the new figures combined (1,123)[Totals] and the difference between this figure and the figure displayed on February 6th 2015 (26)[Total Change Per Period]
For every Friday of every month I input the new Likes & Follows into the table, formatted equations work out the Totals and Differences. However, what I am struggling to do, is create a table that shows the companies (in ascending order, most to least) with their total likes/follows for the specified Friday date. So I have one comprehensive table, and then three additional tables; one focussing on the total Facebook likes and differences, one that focusses on the total Twitter followers and differences and one that focusses on the total figures combined and total differences, all in leader board fashion, for every Friday of every month of the year. Same as the main table that already exists. At the moment, this is a manual process. But I would like to be able to input data into the main table and have it translated into the three additional tables, automatically; moving companies depending on their current "rank" (1-15 companies in total). 1 being the best, 15 being the worst.
Over time I can monitor growth and change of each company and cross-check this information with other tables I have monitoring the type of content AND consistency of activity each page displays. Valuable stuff if you want to find out what works through Social Media in your industry.
In an ideal world, I would have three additional sub-tables that show the current leader boards at a glance. One main table that shows all account change over time, three comprehensive sub-tables that show network/total specific change over time and three further sub-tables that show the current figures.
Any ideas?
