Ok, listen up - you know who you are, you Outliers and Oddities - but now it is time for everyone else to learn your deep, dark secrets as we name names and tell all - at least all that can be told from the CSI data! Grab your popcorn and let’s dive in, shall we? (If you overlooked Part 1 of this series you might want to check it out to recall our Methodology.)
In looking at Outliers we focus on the Residential market since there is greater potential for consumers in that space to be exploited. After all, if you are building a 500 kW commercial project and you don’t do your homework, well, shame on you. But folks in the Residential space are more likely to be exposed to high-pressure sales tactics and other forms of abuse.
Another reason for looking at Residential systems is that the size range is very narrow: from 1 kW to 10 kW. That limits the amount of cost variance you would expect to find (and we found) if you were looking at larger systems. So the data here consists of Residential projects, both purchased and leased, but excluding MASH and delisted projects. This subset of the larger overall data set consists of 454 companies, 13,619 projects with a total capacity of 75.8 MW and an average cost of $5.84/W.
(Full disclosure, projects by Run on Sun came in at $4.78/W - proving that sometimes you can save money when you go with the best! But I digress…)
Companies in this subset varied between having just one project in the data (152 companies appear only once) to SolarCity which has a whopping 2,326 projects.
To make any meaningful comparisons we have limited the companies in this analysis to just the very largest players. Whereas last year we made that cut at 500 kW installed or more, today that filter still includes too many companies to readily compare, so this year we set the bar at an even 1 MW. Even at that threshold, we still have 15 companies on this list. Surely companies that large can afford to pass along their savings to their customers, right?
Here’s what we found:
Reducing our sample size in this way brought the number of projects down to 8,555 (62.8% of the total), total installed capacity to 45.6 MW (60.2%) and average cost down to $5.82/W. This last point is news since in the past, the average cost for the largest group was actually greater than the overall average. Not this year.
The next thing to notice is that SolarCity - the largest player in this field with 28.9% more projects than their closest competitor, Verengo - now is right smack on average at $5.82/W. It would appear that at least for CSI purposes, their cost data is no longer an outlier (though it may still be Odd - more on that later). However, their smaller competitor Verengo (at least as measured in this data) once again beat them on price.
On the low end of the scale, Petersen-Dean has snagged the crown from two-time low cost leader HelioPower - and they did it by far, being the only company to show a price below $5/W. Nicely done!
Which leaves us with the high end. The first thing to note here is that the industry seems to have cleaned up its act a bit from what we had found last year and the year before. According to the data, the highest price charged has dropped from $13.32 in 2011 (49% above the 2011 average of $8.91) to $10.50 in 2012 (45% above the average of $7.23) to our present $7.50 (29% above our average today of $5.82). So the data has really flattened out and our extremes just aren’t as extreme any more.
And yet, if relatively tiny Run on Sun, with no economy of scale going for it, can bring our projects in at $4.78/W, what is the possible justification for charging, on average, and over hundreds of installs, a price that is more than 50% higher than ours?
CSI’s cost cap as of today is $7.10/W for the Residential program. That means that two companies, GCI Solar and American Solar Direct, are averaging costs that exceed the CSI cap. American Solar Direct is huge - they have 675 projects in the data (ranking them third) accounting for a capacity of 2.6 MW (ranking them fourth). And yet with all of that size - more than twice as many projects as cost leader Petersen-Dean - they are still $1.29 above the average!
And what can we say of GCI Solar? That is the DBA of Killion Energy, Inc. which happens to be located at 15261 Connector Lane in Huntington Beach. But somehow that DBA seemed awfully familiar. And guess what, adjacent to 15261 Connector Lane is 15262 Pipeline Lane which is the home of perennial outlier, Galkos Construction. Could these be one and the same, just with a subtle name change? In the end, it doesn’t really matter - bottom line, $7.50/W is just too much for a large company to be charging.
Solar companies can also be outliers in ways other than cost - most notoriously, how long do they take to complete their projects. To take a look at that, we limited our data to Residential projects that also showed a First Completed Date in the data. We then averaged the number of days to complete projects as measured by the difference between the First Completed Date and the First New Reservation Request Date. Needless to say, that yields a smaller number of projects, so we lowered our threshold to 500 kW installed for inclusion on our list. Our new “time-to-complete” list includes seventeen companies, 4,865 projects and an installed capacity of 25.9 MW. Surely these folks must have their operational act together, right?
Here’s what we found:
The first thing we note is that, depressingly, the amount of time that it takes on average to get a project completed has gone up - from 157 days last year to 166 days now (a 5.7% increase). We are getting bigger, but as a whole, we are getting slower - not a trend designed to please consumers.
But some companies have improved significantly - most dramatically, SolarCity, going from 212 days last year to just 157 now - a 26% improvement, and comfortably below the overall average. Verengo, which actually has more completed projects in this data than does SolarCity, also improved from 134 days last year to 122 this year. One surprise - GCI Solar, our most expensive installer, was also the fastest of any of these companies to complete a project, taking just 115 days on average.
And then there is Elite Electric. Frankly we have no idea what is going on here but 320 days, on average, to complete a project is just plain crazy.
Last year we derived a somewhat novel metric for looking more closely at the impact of delay times in the overall industry. We called it System-Years of Delay, or SYD, which is the product of the total number of systems installed by a company times their difference in average days to install compared to the overall average, divided by 365. Thus companies that do better than average end up with a positive number, scaled by their impact on the industry in terms of the number of systems they installed, whereas laggards get a negative number, similarly scaled. We see this as an indication of whether a company is propelling the industry forward, or dragging it backward.
Here are our results for the data from this year (some companies that are grouped around the average are omitted for clarity):
As it did last year, Verengo continues to pull the industry forward and by a wider margin this year than before (172 compared to 111). Last year’s laggard, SolarCity, is now moving things in the right direction. But Sungevity continues to impede progress as do Burke Electric and Elite Electric.
Looking at this another way, assume that a system will produce, on average, 5 kWh/day for every 1 kW installed. Then the delay caused by these laggard companies ends up having real environmental impacts since we are missing out on their energy production for every day that they are completed beyond the overall average. Collectively, that amounts to more than 3 GWh of clean energy that were not produced and an additional 997 tons of CO2 emitted into the atmosphere.
Which brings us to the Oddities section of this post.
In the past two years we found something really odd in how SolarCity was pricing its systems, at least as it was reported in CSI data. We had noted back in 2011 that SolarCity’s price for its leased systems was far, far beyond the price it was citing for purchased systems, and far beyond what its competitor, Verengo was reporting. Then last year we discovered that the two competitors were now charging exactly the same thing for leased systems and that meant that SolarCity’s price had plummeted in just a short time. Going back and looking at all projects across the state resulted in this, rather startling graph:
So now that SolarCity’s IPO has come and gone, what are they - and Verengo - charging for leased systems? (We are excluding sold systems because they are really a de minimus share of either company’s business.) Here’s the cost data for installed, Residential projects, along with linear trend lines:
Interestingly, at the beginning of the year, SolarCity was actually reporting a lower cost than was Verengo. But as the year has gone on, SolarCity’s cost has crept upward - not by a lot, but the trend upward is clear. In contrast, Verengo’s costs have come down and their trend is equally clear.
Once again, Verengo follows a trajectory that mirrors the rest of the industry, while SolarCity pursues a course uniquely its own. Perhaps that is no longer odd, but to be expected.
Finally, we return to the strange case of that Ruler of the Delist - PsomasFMG.
In Part 1 we wrote about delisted projects. One company, PsomasFMG, LLC, had an astounding 79% of all of its projects end up being cancelled and thus ended up being “Delisted". That’s 33 projects with a total capacity of 8.6 MW - or more than 260 kW apiece. We thought that this was odd indeed and decided to look a little more closely at what was going on here. (H/T Imogen!)
Of the 33 delisted projects, all of their rebate applications were submitted in a four day window between January 25 and 29, 2013. All but one of those projects were submitted under rebate step 8b; the last one, on the 29th, missed the cutoff and fell to step 9b (it was later withdrawn). The 32 projects submitted under the higher rebate rate were apparently for Government entities in the cities of Pomona (29) and Diamond Bar (3). (We have searched for news accounts announcing those projects but so far have turned up nothing.)
All 32 of these projects submitted under rebate step 8b were cancelled by SCE for failure to pay the CSI-mandated application fee by the due date. Under the CSI Guidelines, non-residential projects that are larger than 10 kW are required to pay an application fee, based on the size of the project, to secure their rebate reservation (see section 126.96.36.199). Thus, to secure rebate reservations for these 32 projects that PsomasFMG submitted, they or their customers would have had to pony up $250,000 within 30 days of submitting the initial application. The rebate reservations at issue here were worth nearly $3.9 million - which sounds like a pretty good investment: pay out $250,000 to secure $3.9 million. But it never happened, and the applications were all cancelled.
So while we know why the applications became delisted - failure to pay the rebate application fee by the deadline - we still do not know why the company was submitting so many applications in such a narrow window of time that all ended up being cancelled. Odd indeed.
In Part 1 of this series we laid out our methodology and identified some trends in the CSI data for the first half of 2013. Here in Part 2 we are going to see who is hot and who’s not as we continue to assess the State of SoCal Solar, 2013.
For this analysis, we left out projects from the MASH program. We also excluded delisted projects but included both pending and completed. That leaves a total of 13,946 projects overall for these analyses.
We begin by looking at the leading solar module manufacturers as evidenced in the SoCal market. We break this down by Residential and Commercial market segments and we will also look at sold versus third-party owned systems.
There are two ways to look at who’s on top - by number of projects using a particular manufacturer’s product or by total number of modules being used - we will report both stats. (One caveat - CSI data allows for up to seven different module manufacturers to be associated with any one project. However, for this analysis, we are only looking at the first manufacturer listed. There is not a great loss of visibility from this choice; out of 13,946 total projects, 13,824 - or 99% - only specified one module manufacturer.)
Oh, and of some note, one particularly infamous module manufacturer is nowhere to be seen in the data - Recom - and how fitting is that?
In the Residential market segment, there are 13,619 total projects and 90 different solar module manufacturers represented (compared to 97 last year), accounting for some 337,476 modules being designated (compared to 228,372 last year - an increase of 47.7%). Only 16 manufacturers managed to capture more than 1% of the total modules used (compared to 15 last year) - here they are:
Yingli Energy from China has snatched the top spot from SunPower (last year’s champ) even though SunPower continues to hold the edge in total number of projects using their products. Sadly, Sanyo - now owned by Panasonic - did not even crack this list, reaching just 7/10 of 1% of the total. Oh, how the mighty have fallen.
Those are the results overall but does it matter whether you slice the data by purchased versus leased systems? Indeed it does. For purchased systems, beleaguered German module maker SolarWorld grabs the lead with 13% of all sales (10,411 units) followed by SunPower 12% (9,643 units), Canadian Solar 10% (7,396 units), SMX Capital 10% (7,912 units) and Sharp 5.6% (4,453 units). For leased (i.e., third-party owned) systems the leaders shift again: Yingli Energy recaptures first place with 22% (56,843 units) followed by Trina Solar 20.7% (53,375 units), SunPower 19.1% (49,149 units), REC Solar 9.6% (24,718 units) and LG Electronics 7% (18,066 units).
As for specific models, here are the top ten:
Trina Solar breaks through with four of the top ten models (although two are functionally the same). But this year SunPower has the most specified module model in the Residential market as designated in the CSI data!
Turning to the Commercial market, 48 different manufacturers are represented in the data (down from 60 a year ago), accounting for the sales of 291,227 modules (down from 350,360 last year - a drop of 17%). Showing greater diversity at the top of the heap than in the Residential market, some 21 different manufacturers made it above the 1% of sales threshold. Here they are:
MEMC Singapore takes the lead over SunPower, selling 40,830 units compared to SunPower’s 37,858 - despite SunPower having a clear advantage in the number of projects for which they were selected (10.4% compared to MEMC’s 6.4%).
So who is MEMC Singapore? We had never heard of them before - and they did not crack the 1% mark in last year’s analysis. Turns out MEMC Singapore is a subsidiary of MEMC Electronic Materials, Inc. - which also owns SunEdison. We found this news report from last April indicating that Fox Energy - the PV arm of Foxconn Technology (of iPhone fame) - had entered into an agreement with MEMC Singapore for Fox to manufacture up to 350 MW of solar modules at a facility in Juarez, Mexico - for SunEdison. Indeed, according to the data, SunEdison is the solar company using these modules.
Meanwhile, last year’s leader - Suntech - fell to fourth place this year as it deals with insolvency.
As for the most popular commercial solar module models, here are the top five: SunPower’s SPR-327NE (31,366 units), MEMC Singapore MEMC-M315 (23,554 units), Schuco USA MPE 240 (11,583 units), Suniva OPT315 (11,179 units) and Trina Solar TSM-240 (10,162 units).
Analyzing inverter sales is a bit different since many projects have multiple inverters and, in the case of systems with microinverters there is one inverter per solar module. So it is not too revealing to report that Enphase sold 17 times as many inverters as its nearest rival, Power-One. Instead, as we did last year, we will look at this by the percentage of projects that designated a particular manufacturer’s product as Inverter #1 in the data.
Twenty-nine manufactures are represented in the data (up from 24 last year), but only 7 exceeded 1% of projects (down from 8 last year). Here they are:
Hottest of the hot is Power-One with 31.5% of all residential projects (up from just 16% last year!), followed by Enphase at 26.5% (up from 21%), SMA at 16.3% (down from 31%), SunPower at 10.4% (down from 16%), and Fronius at 7.9% (down from 10%) rounding out the top five.
In the past SMA has observed, correctly, that you must add-in the market share from SunPower since those are all SMA inverters re-branded for SunPower. True in the past, but somewhat less so today. Looking at the specific inverter models designated under SunPower shows that only 8.3% of SunPower’s 10.4% share is attributable to SMA, with the rest going to inverters made by Fronius and AC modules (which use SolarBridge microinverters).
Once again, does the data change significantly if we compare sold versus leased systems? It does indeed - in the sold category, Enphase dominates with 41.7% compared to SMA at 18.5%, Power-One at 13.3%, SunPower at 8.3% and SolarEdge at 6.1%. For leased systems we get: Power-One at 36.9%, Enphase at 22.1%, SMA at 15.7%, SunPower at 11% and Fronius at 8.6%.
Turning to the Commercial market, 19 manufacturers are found in the data, with 13 of them holding more than 1% market share. Here they are:
Wow - now that is a surprise: Enphase has taken over the Number 1 spot! Enphase jumped to 13.2% of commercial projects with Power-One right behind at 12.5%, SatCon (last year’s leader but now in bankruptcy) at 11.6%, SMA at 10.7% and Fronius at 10.1% rounding out the top five.
As SatCon dropped 15%, someone had to pick up the slack and it appears that Enphase got the lion’s share - moving up 9% from last year. Here’s your overall hot/not hot chart:
The data would suggest that we could have nearly 3,200 different pairings of module and inverter manufactures, but in reality, the combinations that actually occur are far fewer. Here are the most popular pairings in the data overall:
Collectively, these top five pairings account for just under 45% of all projects in the data, and SunPower continues to demonstrate the joy of vertical integration - but its advantage isn’t as strong as it was last year when its pairings accounted for over 19% of all projects. As would be expected by its hot performance overall in the inverter category, Power-One was part of the leading pair for three manufacturers and Enphase cracked the top five with REC Solar.
Now let’s see what these products are commanding in price. We will look across all projects (excluding delisted and MASH) to see what panels are involved with systems commanding the highest $/W. Here are our results:
Keep in mind that the overall average cost was just $5.24/W - and yet we have modules being used in project at two to three times as much. Nor are the majority of these what you would call top-tier manufacturers - confirming once again, that spending a lot of money is no guarantee of getting quality in return.
Interestingly, the Sun Energy panels at the top of this list were also at the top of the list last year - only the system cost has actually increased from $16.02 to $16.38/W!
Finally, we shift our emphasis from equipment manufacturers to look at who is installing what. Here’s a list of all installation companies with 100 or more projects and the modules and inverters they use the most:
Couple things of note here. First, SolarCity has taken the number 1 spot from Verengo and by a comfortable margin. Second, SMA doesn’t crack this list until the 10th ranked installer. Meanwhile, Power-One has four in the top ten and Enphase has three with Fronius making the top ten twice.
Altogether, overall inverter leader Power-One was the preferred vendor for 7 installers (average loyalty 58%), Enphase was preferred by 8 installers (loyalty 87%), Fronius by 2 (loyalty 74%), SMA by 4 (loyalty 58%) and SunPower by 3 (loyalty 59%).
Collectively, the 24 companies on this chart accounted for 9,805 of the projects in the overall data set - 70.3%. But did that volume lead to better prices for their clients? We will try to answer that question - and possibly raise a few more - in our next post: Outliers & Oddities.
Today is our first of three posts looking at the state of solar in Southern California for the first half of 2013 as revealed by the data compiled for the California Solar Initiative (CSI). This first post explains our methodology and highlights some interesting trends in the data.
Let’s start with our methodology. The CSI collects a significant amount of data as part of managing its rebate program and most, though not all, of that data is made available to the public. For example, while CSI collects data from installers that identify four components of system cost - solar modules, inverters, permitting and everything else - the data that is made available rolls all of that up into just one number - Total Cost. Nevertheless, the CSI data is the most comprehensive data set available and covers all projects being built in the service areas of California’s three investor-owned utilities: PG&E, SCE and SDG&E.
For our analysis, we started with the CSI Working Data set from July 31, 2013. (Here’s a link to the CSI Working Data download page and here’s a link to the data set (11 MB zip file) that we used for this analysis.) This analysis just looks at data from the SCE service area and to limit our time period to just the first half of this year, we added a Status Date field to the data which is the latest of a series of milestone dates recorded in the data (from First Reservation Date to First Completed Date). We then extracted our data for dates from 1/1/2013 to 6/30/2013. After that it is a matter of pivot table magic!
One other note - there are multiple ways to identify the size of a project from the data: Nameplate Rating (i.e., DC Watts), CEC PTC Rating (which accounts for the PTC rating of the solar modules used times the efficiency of the inverters chosen) and CSI Rating (which applies the Design Factor to the CEC PTC Rating and which is the basis for rebate payments. For the most part, unless otherwise noted, we will be using CEC PTC Rating for system size since that accounts for some measure of equipment quality but isn’t confounded by site complications such as shading or the use of tracking systems. In particular, our $/W figures are computed this way.
Overall, our data set reflects 14,459 projects, up from 9,669 over the same period last year - an increase of 49.5%.
There are three program divisions in the data: Residential (projects under 10kW), Commercial (projects greater than or equal to 10 kW) and the Multifamily Affordable Solar Housing (MASH). The overwhelming majority of these projects are in the Residential program: 14,028 (97% of the total) are Residential, followed by 387 (2.7%) Commercial and just 44 MASH projects. However, MASH projects are generally associated with affordable housing developments and so they can be quite large overall. In fact, the average MASH project is 158.7 kW, compared to just 5.6 kW for Residential and 216.3 kW for Commercial. Altogether, Commercial projects account for 83.7 MW, followed by 78.8 MW in the Residential program with just 7.0 MW in the MASH program.
Compared to last year, which saw a decline in the amount of solar capacity in the data, capacity this year is up 59% over last year:
At the same time, the average system size continues to decline from 14.2 kW in 2012 to just 11.7 kW this year. Likewise, rebates are falling (by 32%) but so are overall prices (15.2%), just not as fast.
Still, in raw dollars, the reduction in system price of $0.97/W from a year ago, nearly triples the decline in rebate rate.
We will have lots more to say about system costs in subsequent posts.
The pace of projects is accelerating, driven by dramatic growth in the Residential program (as always, click on the chart to see it full size):
Since it is a relatively small slice of the pie we are going to generally ignore the MASH program but before we leave it aside, we thought we should at least take a peak at who are the players in this program. Here’s what we found:
Four projects have no solar contractor associated with them in the data (curious). The big winner, however, is Shorebreak Energy Developers - not a company on our radar, but obviously a major player with nearly 2 MW worth of projects in the program. A few familiar names - HelioPower, SolarCity and even our friends down the road at PHAT Energy (well done, Philippe!).
Roughly half of these projects are third-party owned: 24 of the 44 for a total of 3.5 MW out of a total 6.98 MW. The average cost per CEC PTC Watt is also almost identical, $5.52 for third-party owned versus $5.55 for sold systems. Yet within that average was a very significant range from a high of $13.12 for the 141 kW project being built by Lite Solar, all the way down to $4.32 for the 601 kW project being built by SPG Solar. Shorebreak, the leader in the amount being installed and total number of projects is also a leader on price, coming in at $4.90.
One stat we continue to find intriguing is the number of delisted projects - projects that got entered into the data but then cancelled at some point. Altogether, there are 471 projects delisted, or just 3.26% of the total, down from 4.2% last year. Those delisted projects accounted for 16.8 MW of capacity or 9.9%, which is up significantly from last year. In other words, fewer projects were delisted, but they accounted for a bigger piece of the potential installed capacity. The majority of the delisted projects, 324 of them (68.8%) were third-party owned.
149 different companies made their way onto the Delist - not surprising for very large players, but as before we decided to look at these companies as a share of their overall project total. We limited to companies with at least 10 delisted projects and then rank ordered them by the percentage of their projects that are delisted. Here they are:
What is up with this? PsomasFMG has 79% of its total projects delisted? Turns out their 33 delisted projects account for nearly half the the total delisted projects by capacity. Not surprisingly, these are all third-party owned Commercial projects ranging in size from 61 kW to just under 1 MW.
The other big winners, Electricare, Smart Energy, and Petersen-Dean had all of their delisted projects in the Residential program with sizes ranging from under 3 kW to just under 10 kW. Interestingly, of the worst actors last year: Remodel USA, Herca Solar and A1 Solar Power, both A1 Solar (4 projects) and Remodel USA (1) still made the delist this go around, but with greatly improved numbers. (Herca Solar has 44 projects overall in the data, none delisted.)
While Commercial projects are necessarily more complex, making cancellations more likely, it is hard to understand a 15% delist rate for Residential projects. Caveat emptor.
Finally, we turn to the question of size, and in particular how well does the size of a project drive down its cost? For this analysis (and most of our analyses going forward) we are excluding delisted and wait list projects from our data. Here’s our result for systems at or below 10 kW:
As in the past, we had to exclude a couple of outliers above $15/Watt: the farthest out there was Sun Pacific Solar Electric which charged $21/W for a 1.5 kW system and right behind was California Solar who installed a 7.9 kW project for over $16/W! We will have lots more to say about such Outliers next week.
Overall, the trend is clearly moving in the system buyer’s direction with the trend line reaching down to $5.40/W for systems sized at 10 kW, while a typical residential project of 5 kW comes in around $5.70/W.
Nevertheless, that is a very gradual slope indicating that prices are fairly consistent across system sizes in this program segment.
What about larger systems, do we see more of a downward trend there as system sizes increase? Here’s the data:
Interestingly, for the smaller projects there is very tight clustering right around the $5.70 mark where the trend line begins. However, as you move up in system size, the clustering breaks down and system costs vary far more widely than they do for smaller systems. Still the trend has 1 MW projects coming in around $3.77/W, compared to nearly $5.20 last year.
Ok, that’s enough to get us started. Monday we will report on Who’s Hot and Who’s Not, and Tuesday will be our favorite post: Outliers and Oddities. Stay tuned!
The first half of the year is in the books which means that it is time for one of our perennial favorite posts - our analysis of the State of Solar in California based on the CSI data. Here’s a teaser…
We will kick off the series tomorrow documenting our methodology for analyzing the CSI data, and identifying some key trends about the number of projects, the cost of those projects, and how project size influences project cost.
Then on Monday we will continue with an assessment of Who’s Hot and Who’s Not - manufacturers and installation companies alike.
That will invariably lead to our third - and most popular - installment on Tuesday about the Outliers and Oddities in the industry. Will long time champs Galkos Construction and SolarCity continue to dominate this closely contested category - or will some unexpected player suddenly jump out of the data and into infamy?
It promises to be an informative - and irreverent - take on the State of Solar 2013. You won’t want to miss it!
Oh, and if there is something you want us to include in our analysis, please mention it in the comments and we will try to work it in.
Today’s post has nothing to do with solar, except in a sort of meta sense. You see, the process of creating these posts is not really driven by some Grand Plan but rather what is going on now or at least strikes us as interesting. But of course what we think is interesting is in no way guaranteed to interest You, the reader. Every blogger, heck, every writer wants to create content that readers will want to read. Finding that match is the key - but how?
We came across an interesting article on point the other day thanks to SEOMoz. The article, titled: Two Amazing Bar Charts: % Content Consumption, % Share of Search, introduced us to a geeky way of attacking the question of what content connects with our readers. The author, Avinash Kaushik, suggested that you could gain some useful insight into how well you were doing by combining data from two sources: your website analytics provider (Google Analytics in our case) and your content management system (in our case, b2Evolution).
By summarizing your posts by categories and then totaling unique page views for those categorized posts, you could see how much content you are creating in each category and how much those posts are driving traffic. If you are on track, there should be a direct correlation - categories where you are writing more are getting the most visits. But are they? We decided to run the analysis for one year’s worth of posts, from last July through the end of June.
Seems simple enough, here’s where it gets geeky. (Note: if you just want to see the results, feel free to skip down to the cool chart at the end of all the geek-speak.)
Way back when we started this blog we had to decide what software to use. For better or worse, we chose b2Evolution, although most of what you see out there seems to be on Wordpress. One of our biggest complaints about b2Evo is that it provides pretty meager statistics - and nothing like the data summary that we needed. Of course the database itself must have what we needed, it was just a question of figuring out where. Oh, and how would be important too!
The b2Evo database contains 52 separate tables - which one(s) have the information that we need? After a fair amount of poking around and a couple of false starts, we determined that a query combining two tables would allow us to retrieve the data that we wanted: a new table that would list the URL of each post during the time period combined with the name of the primary category under which the post was filed. (We frequently associate multiple categories with a given post, but each post has a primary category and that is what we were seeking for this analysis.) For the mySQL geeks out there, here is the query we (finally) concocted:
SELECT post_ID, post_datecreated, post_main_cat_ID, post_urltitle, cat_name FROM evo_items__item
INNER JOIN evo_categories ON (post_main_cat_ID = cat_ID)
WHERE post_status = ‘published’ AND
post_datecreated > ‘2012-06-30 23:59:59′ AND
post_datecreated < ‘2013-07-01 00:00:00′
ORDER BY post_datecreated DESC
We were then able to export the results table to a text (CSV) file for importing into Excel. Now we needed to get our analytics data.
Google Analytics (GA) is a much easier platform from which to extract lots and lots of data. For this analysis what we needed was to filter by our one year start and end dates and look at the Content - Overview report. That provided more than what we needed for this analysis, but significantly it gave us the URL of the viewed page (within the runonsun.com domain) and unique pageviews. We opted for unique pageviews to filter out those instances where someone visits the same page within a session as what we were after was the post-reader connection and unique pageviews is the best handle on that relationship.
Once again, we were able to export the data into a text file for importing into Excel.
So after importing the two data files into separate tabs of our Excel workbook we were ready to start pairing things up. But of course, things are never that easy. For starters, the manner in which our URLs appeared from b2Evo and from GA were not at all the same and Excel’s look-up functionality could not resolve the difference. Take this example:
To match them up, we wanted to slice off everything to the right of the last ‘/’ and use that to do our matching. Turns out that is a more complicated task than you might imagine. A little probing on the Internet turned up this, somewhat counter-intuitive solution:
=RIGHT(A2, LEN(A2) - FIND("|",SUBSTITUTE(A2,"/","|",LEN(A2) - LEN(SUBSTITUTE(A2,"/","")))))
(I warned you this was going to be a geekfest!)
A new column was created in the GA data table and populated with the results of that lovely formula and voilà, we had URLs in both tables that would yield a match. Now our b2Evo data could include a value for the unique pageviews from each post that was written during the previous twelve months. From there it was a simple matter of creating a pivot table based on categories, counting the number of posts in each category (listed as a percentage of the column total) and summing the unique pageviews in each category (again, displayed as a percentage of the column total). We filtered for the top ten categories (by pageviews) and that accounted for 110 separate posts and 13,659 unique pageviews.
Put all that into a chart and here is what you get.
(Please click on this and view the full size image to do this justice!)
So having gone through this exercise, what does this tell us? Well, my two largest categories for posts - Solar News and Ranting (which is where this post is categorized) - are also my two largest categories for views - so that is consistent with what we would like to see. (Ok, perhaps a little less ranting - good luck with that!)
On the other end of the scale, we are clearly writing more about both Commercial Solar and Energy Storage than our present viewership supports. That’s ok, we are looking to build readers in those vital categories so we are willing to be leading a bit there.
But look at the two major outliers: CSI 2012 and SCE. The CSI 2012 category only had four posts - the three part data analysis series of CSI data that we did last year, plus a teaser post - yet it drove more than 15% of our views! Perhaps rather than doing this once a year, which is what we have done the past two years, we should do this every six months? That’s a lot more work - those posts are very labor intensive and require rigorous editing - but in terms of what our audience is reading, it would seem to be time well spent.
The SCE category is more interesting still since it really doesn’t involve as much effort as the CSI category does. There were three posts in that category yet they accounted for more than 14% of all views! Wow - didn’t see that coming! Looks like it is time for us to spend a little more time looking at SCE and putting informative posts out there for our readers. Message received!
Perhaps the most surprising result from this analysis was that we were surprised by the results. We like to think that we have a pretty good handle on what our audience wants to see - and for the most part the above graph suggests that we do - but still there were surprises. Like any other business metric, knowing what your customers want from your blog - or the products that you are selling - is key to being successful.
If you are a solar company writing a blog and you are serious about better connecting with your audience - colleagues and potential clients alike - you might want to try performing a similar analysis and see where it leads you.