Uncategorized

If you’re measuring Social Media, one of the questions you’ll invariably receive, or should be asking yourself, is who your influencers are. At the least you’ll have some theories—some blogs that cover your brand all the time and who seem big enough. Depending on the size of your company, you’re almost guaranteed to have a PR department of a reasonable size and, quite likely, they will already be engaged with said blogs/news writers in some fashion. Maybe you’re really advanced and engaging with forums/Twitterers too.

But of these people that you engage with, which ones are really moving the needle? Who is truly shaping the conversation?

To help answer that question, we’re going to build off of our last article which analyzed headline repeats, but we’re going to restrict our results to a particular source.

The Query:

SELECT Headline FROM table WHERE articleURL LIKE ‘%http://www.engadget.com%’;

Query Explanation:

The above is requesting all Headlines where the URL contains http://www.engadget.com. 5 of the results are below.

Now we need to figure out how many times each headline was counted. Let’s start with “How would you change the netbook as we know it?” headline.

SELECT COUNT(Headline),Headline FROM table WHERE Headline LIKE ‘%How would you change the netbook as we know it?%’;

As you can see, this particular headline garnered 80 repeats. If we were to go through all of the Engadget headlines in this fashion, we would eventually end up with a report like the following.

Engadget

Repeats – Headline 1

Repeats – Headline 2

Using those figures, I propose we offer 3 measures per source.

1. Headlines (How many stories did the source in question write about your brand?)
2. Total repeats (How many total repeats did those stories generate?)
3. Average repeats (On average, how often was a story about your brand, on this source, repeated on other blogs?)

Below is a simple web form/PHP script I set up to do just that. Here you can see some results for http://www.engadget.com, as well as the sums of my limited data set.

From there on, if you are ever asked “Who is more influential, A or B?”, you have some metrics in addition to CommentCount to fall back on and to provide a more holistic picture. As you become more familiar with who your influencers are, you can start tagging them in your database and running reports to show how your brand is performing relative to the big fish.

Perhaps just as important, you now have a key area to focus on to determine what people think about your brand. Social media analysis is a new field, and chances are, your resources are tight. If someone asks you, the poor analyst, to analyze multiple topics and give them an idea of what people thought, you will need to prioritize. By reading the top 5-6 sites for your topic, and summarizing the main position, issues and themes highlighted within the body and comments, you are getting the best bang for your buck.

Speaking of bang for your buck, you will note that this report requries you to query every headline individually. A time sink to say the least. Thus I propose you work with your IT staff to automate the queries. If you’re interested in how to automate it, read on (I discuss how I did it personally and you’re free to take my terrible code), otherwise, the rest of this article is going to be very dry for you.

*Note to real coders/developers: I am as amateur at PHP as I am with MySQL. If you are a real developer, the code from here on out may cause you to cry. I’m in marketing, cut me some slack
Creating the HTML form
Our first step is to create a very simple web form so that we, and others, can make use of all of our hard work. Open up and editor and save the following as something you’ll remember. For the sake of argument, I’ll call it form.php.

<HTML>
<form action =”script.php” method = “post”>
Source <input value=”<?PHP echo $_SESSION;?>”<br>
Topic <input value=”<?PHP echo $_SESSION;?>”<br>
<input value=”send”>
</form>
</HTML>
Creating the page
Now it’s time to create our influencer page. Since the form calls script.php, we’re going to call the following file the same name. I’ll break the file into sections for discussion.

First we need to get the data from form.php and include the form we just created on the resulting page.

<?PHP

session_start();

$_SESSION;

<?PHP include(“form.php”); ?>

<?PHP

if(isset($_POST))

{
Prepping the Table
Next we’ll use PEAR’s HTML_Table package and prep the tables.

// Include the HTML_Table package

require_once “HTML/Table.php”;

// Create an array of table attributes

$attributes = array(‘border’ => ‘1′);

// Create the table object

$table = new HTML_Table($attributes);

// Set the headers

$table->setHeaderContents(0, 0, “Repeats”);

$table->setHeaderContents(0, 1, “Headline”);
Connecting to MySQL and Defining the Variables
Next we will connect to MySQL, and define the variables (based on the form)

//Connect to MySQL

$connect = mysql_connect(“localhost”,”user”,”password”);

//Select the Database

mysql_select_db(“database”,$connect);

//Define Source (suggest doing this via form)

$sources = $_POST;

$topic = $_POST;
Querying the Database
Now we can start querying. The first thing we’re going to do is get all headlines where the article URL is like our “Sources” field, and, optionally, refine the search by topic.

//Query the Database for Headline matched to Source URL

$result = mysql_query(“SELECT Headline FROM table WHERE articleURL LIKE ‘%$sources%’ AND Headline LIKE ‘%$topic%’;”);

We then need to put the result of that query into an array, so that we can query the database using all of the headline results in the next step. We also need to escape the apostrophes in a headline, as MySQL search will fail otherwise.

//Put the result into Array $source

$source = array();

//While Source Headline = the prior query

while ($sourceheadline = mysql_fetch_array($result,MYSQL_NUM)){

//MySQL requires that you escape apostraphes for the search. This str_replace replaces all ‘ of the headline with \’

$sourceheadline2 = str_replace(“‘”,”\’”,$sourceheadline);

Finally, we’re going to query the database looking for headline count, where the headline comes from our initial array.

//Now lets query

$repeats = mysql_query(“SELECT COUNT(Headline),Headline FROM table WHERE Headline LIKE ‘%$sourceheadline2%’ ORDER BY COUNT(Headline) DESC;”);

//Lets dump the query results into our initial array

$source[] = mysql_fetch_array($repeats,MYSQL_NUM);

}
Prepping some Summary Figures
Before outputting this all to a table, we’re going to make a bit of a summary. First we’re going to dump the repeat value into yet another array so that we can get some values out of it.

sums = array();

foreach($source as $key => $value)

$sums++;

Now we’re going to get three values out of that last array, a count of articles, the total repeats for the source, and the average repeats per headline.

$articlecount = count($source);

$averagecount = array_sum($sums)/$articlecount;

$totalrepeatcount = array_sum($sums);
Printing the Results to Our Table
Finally, lets print this mess into a slightly more refined table.

// Cycle through the array to produce the table data

for($rownum = 0; $rownum < count($source); $rownum++) {

for($colnum = 0; $colnum < 2; $colnum++) {

rsort($source);

$table->setCellContents($rownum+1, $colnum,

$source);

}

// Output the data

echo “<H1>” . $sources . ” ” . “</BR>” . “</H1>” . “<H2>” . “Headline Count:” . ” ” . $articlecount . “</BR> ” . “Total Repeat Count:” . ” “ . $totalrepeatcount . “</BR>” . “Average Repeats:” . ” ” . $averagecount . “</H2>”;

echo “</BR>” . “</BR>”;

echo $table->toHTML();

}