Clash of Clans: An Economic Analysis

Clash of Clans: An Economic Analysis

Or: How I made the CoCulator.

1-Im5dRNtQJ6Xbu84fE4Xlsw.png My humble village.

Clash of Clans has been a little obsession of mine over the last few years. The economy of this game always fascinated me, and I have finally found the time to analyze it. I will be providing the raw data and Excel workbooks for your entertainment throughout this post.

Part 1: Analysis

Cost

The number one question I had before I began this project was what a fully maxed Town Hall 10 base would cost if one were to upgrade using only gems, i.e. the nominal value of a Clash of Clans base. This chart shows the cost to upgrade to the base level from the max of the previous base level:

1--9Y_dx0Ve5N_OnFrSZFnsg.png

With heroes, the growth rate becomes negative from Town Hall 9 to Town Hall 10. Without, it only slightly drops. This is because the cost addition of the Archer Queen at Town Hall 9 is so large: at Town Hall 9, the Queen can be upgraded from level 1 all the way to level 30, cumulatively costing 2,397,500 Dark Elixir. The cumulative cost from 31–40 is only 1,775,000. Thus, calculating them separately yields a slightly smoother curve:

1-7PIhOBQtYyTRV_pkwjvYVg.png Green line is actual data, blue line tracks the Pareto curve trend.

The distribution we see is a example of a Pareto distribution: in this case, just over 80% of the upgrade cost falls under the last two Town Hall levels. You can view this data in this Google Sheet. However, the charts aren’t as pretty.

Cost Progression

The buildings each have a respective cost that change quite a bit over time. I exported images of the chart below from the worksheet and compiled them as a gif. Note the changes to scale:

1-UrgNz-KF22eWdUxq2L5EJA.gif

From Town Hall 1 to Town Hall 10. See .mp4 version here. Units: gold/elixir.

Troops have a similar pattern, with the costs exponentially rising through the course of the game. Below are the charts for standard light and dark troops.

1-Tvz3cz4Hltm0lN99pOzkTA.gif Troop upgrades start at Town Hall 3, not Town Hall 1. .mp4 available here. Units: elixir.

1-i3W5q_20NkQ0pDxAagDMbg.gif Dark troop upgrades start at Town Hall 7, not Town Hall 1. .mp4 available here. Units: dark elixir.

The graph for Heroes does not show very much progression, as there are only 2 types and most of the upgrades are in the later levels. Here is the final chart for Town Hall 10 Heroes:

1-86tmZSnaRkOsuAk98v_1Lw.png Units: Units: dark elixir.

As you can see, the Queen far outpaces the King.

Town Hall Upgrade Cost Calculator

The real reason for this post was my curiosity of what it would take me to fully upgrade my base. My spreadsheet can do this quite easily. Once I enter the data to the respective places (outlined below) the worksheet will give you your data. Here’s how mine looked:

1-w_IX5z_XJ9Byb9AoRVrA1w.png Cost progression with columns U to AG hidden.

1-o6oRoc2ZCTiKdn6ebqjIJw.png Cost progression summary with columns B to AF hidden.

As you can see, it would cost me 572k gems or $4,086 to fully upgrade my base to Town Hall 9.

All calculations are completed using this spreadsheet. It is also hosted in this Google Sheet. The Google Sheet will do the math but cannot render the charts as far as I can tell.

Happy CoCulating!

Part 2: Methodology

To make things as clear as possible, I want to explain my methods and document how my methods work for those who may wish to expand this research and manipulate the data. I also document the assumptions I’ve made to simplify some of the more complicated facets of this project.

Data Sources

All of the data I used came from the charts on the Clash of Clans Wiki. Each troop/building/spell has a small chart that I used to import into a large worksheet. Huge thanks to all of the hardworking people who contribute to the Wiki!

1-kAHZD-4AJ8ILm9ZqXFW4JQ.png Screenshot from http://clashofclans.wikia.com/wiki/Haste_Spell

You can download the Excel worksheet here or view it in Google Sheets here. I will be using this data source to make all of the calculations in the rest of the project. For this analysis, I only used the columns pertaining to the level, cost, and requirement columns. However, all of the data is in the table if you want to do your own experimentation.

Assumptions

1-rItVdyefoyiKCQX7QOJFOw.png

Data Inputs

This began as a project to calculate the upgrade cost (in gems and dollars) to max out my base. Thus, I needed to be able to input the current building data for my base.

1-7I4rH14Spmx99tAi90iYgQ.png Organizational structure of the data-entering system.

I created a column-based system to enter the level of each building: each row has a different building, followed by 7 columns used to enter the level of each building. It may take some time to get everything entered properly, but this is the simplest way to make the relevant calculations.

I found the easiest way to do this was to put my village into edit mode, remove all of the pieces, and use the list of structures to enter the data into the fields.

Calculations

All calculations are done in this spreadsheet. It is also hosted in this Google Sheet, but some formulas and charts are not supported.

1-bxAR4-e7XBS-nXQcsGl2nA.png

Each row corresponds to a row from the above data-entering system. Extra rows added in case additional buildings are added. Generally all columns except for “Calculation” are hidden.

Finally, the fun part! There are many different calculations that go into this worksheet. Foremost, we must get the data from the correct column for the Town Hall level:

=IF(OR(ISNUMBER(B10,$Q10=B10),IFERROR(SUM(OFFSET(Tables!$A$75,MATCH($A10,Tables!$A$75:$A$106,0)-1,1,1,$Q10)),0),0)*P10

Then, we must calculate the cost of each individual building (up to 7 buildings):

=IF(OR(ISNUMBER(C10),$Q10=C10),IFERROR(SUM(OFFSET(Tables!$A$75,MATCH($A10,Tables!$A$75:$A$106,0)-1,C10+1,1,$Q10-C10)),0),0)

Once all of the individual building upgrade costs have been calculated, they are summed and pushed into charts. All of the calculations from this point are basic sums or other simple arithmetic.

Now go forth with your newfound knowledge and Clash On!

This post will be updated when the stats for the recently announced Town Hall 11 (and it’s respective buildings) are released.

Discussion: r/ClashOfClans