Google Analytics now classifies visits from hundreds of social networks for you, and with the API, we have full access to the details. Do you know how much your site traffic depends on these social channels? In this article, we will build a report with NEXT Analytics that shows the top social channels as a percentage of the total daily visits.
To begin, we grab the data we need for the report – the dimensions of Date and Social Network, plus the metric Visits. Note that non-social visits are included in the results, with a Social Network value of “(not set)”. This is good, because it means the total of each day’s traffic will be the total number of visits to the web site that day, and we need to keep our results in context.
Switching to the Pivot tab, we check the box and NEXT automatically creates a daily trend of our traffic. Note that the social networks are listed in the order in which they appear in the downloaded data, so all of the first day’s activity appears first, followed by any additional social networks that appeared in the second day’s numbers, and then any additional ones that appeared in the third day, and so on. This is not a very good way to order the results, but with NEXT we have several options to make it better.
We could sort it by the last day’s numbers; this would be good if we were really interested in the most recent period (day). Alternatively, we could create a total column and sort by the total for the entire reporting period, which is a typical approach is many reports. Another solution is to sort by the peak value on any day in the date range, which ensures that spiky traffic, typical of social media/viral posts, will rise quickly in the report.
The easiest way to get this is simply to sort the query itself by the number of visits. Although this puts the dates out of order, it would put the peak social channels first in the list, so that when we pivot the data, the channels with the higher peaks will be listed before those with lower peaks.
Notice that NEXT automatically puts the dates in the right order for us!
So far so good. Our next challenge is to express the visits as a percentage of the total visits for each day. With NEXT, we leverage the built-in analytics script commands from the Analyze tab, selecting Compare – To Axis Aggregate, expressing the results as a percent of the Sum of the Column. Now each cell contains the number of visits expressed as a percentage to the total visits for that day.
That is a lot of results, and it includes the non-social traffic as well. We need to remove the non-social row and it would be good to group channels that aren’t in the top 20 into an ‘Other” category for reporting.
To get rid of the non-social rows, we select the row with “(not set)” and remove the selected row.
To group rows not in the top 20, we select the top 20 rows, then combine the unselected into an “Other” row.
This results in a new “Other” row at the top of the list. In most reports, people want to see the “Other” group at the bottom, so we select it and move it.
All of the commands we add are recorded and saved to our social_actions worksheet, so they can be replayed automatically when we refresh the report. At this point we should save the query to our worksheet.
Moving to Excel, we can now easily create a Top 20 chart from the data by simply inserting a new chart.
That new chart can then me moved to a new worksheet and formatted as desired for our report. At any time, we can refresh the workbook with NEXT to see the latest trends.
This report is available for download here.