Wednesday, June 24, 2015

King Q2 2015 revenue forecast. Is the saga over for King?

Update 4 August 2015
The deterioration in search volume for King's main titles in Q2 2015 wasn't as bad as forecasted, thanks to an uptick in Candy Crush popularity in the last week. The consensus forecast for King's EPS for Q2 from Nasdaq puts EPS at $0.36. Based on the latest SVI data, I would expect King's revenue to be around $517 M. An EPS of $0.36 seems quite low. I would assume it to be close to $0.45.


The mobile game developer behind the Candy Crush Saga, King, had a record year in 2014, with revenue increasing 19% from the year before. But since then, things have changed. From the search volume for their top three titles, Candy Crush, Bubble Witch and Farm Heroes, we can see that none of the other titles have really taken off. What's worse, interest in their top title Candy Crush is going down.


What could this mean for revenues 2015? Revenue is already on a down trend. If we extrapolate out the search volume trend to the end of the year, the number of Google searches will have decreased by 43%.
The end of 2015 is of course half a year away, but if the trend continues, we should see a drop in King's revenue by 50% to $1105 M.


If we look a bit closer in time, ahead for Q2 2015, the same analysis puts revenue at $454 M. for the quarter. If we remove the first quarter 2013, that number goes up to $550, on par with the previous quarter.


The analysis is based on the assumption that Google searches equals general interest which translates into revenue. It's limited by the fact that Candy Crush accounts only for 50% of revenue, but is 95% of the search volume variation measure used here. A drop in new users wouldn't either translate into a direct drop in revenue, as existing users keep playing King's game.

The time of explosive growth looks to be over for King. If you have the analyst's revenue forecast for Q2, leave a note in the comments. How do you think the stock market will react to a continued revenue decline in Q2 2015?




Rovio forecasted revenue 2014 versus actuals

Back in March, I claimed that Rovio's revenue would decline from €153.5 M. to €152 M. The actuals are out, and it seems like I was only off by €4 M. Even better, the model could forecast a change in trend based on Google search data, which is very interesting to see.

The model used was slightly different from the one used for forecasting Supercell's revenue for the year. Previously I have worked with the direct correlation between revenue and search volume. This time, a log change model was instead used, and proved to be effective in this case.

Here's the previous post containing the forecast.

Financial ratio summary

Rovio Entertainment Oy
2010/12
2011/12
2012/12
2013/12
2014/12
Companys turnover (1000 EUR)
523275395152171153516148332
Turnover change %
622.10620.60101.800.90-3.40
Result of the financial period (1000 EUR)
26003535655615258987964
Operating profit %
56.6062.1050.5022.806.70
Company personnel headcount
-98311547729

Sunday, June 21, 2015

How to calculate the virality coefficient in SQL

One of the most powerful way to grow an online business is through word of mouth. Cohort analysis is a powerful way to understand how effective you are at turning users into advocates. The number of users brought in by an invite program is called the virality coefficient. It measures how many guests (new users) your hosts (existing users) are bringing in. Depending on your business, you will be more concerned with the new user virality coefficient, or the active user virality coefficient. Here, I explain how to calculate a generic one week new user virality coefficient with SQL.

First, some definitions


  • Host: An existing user.
  • Guest: A potential users that has been invited by a host.
  • Conversion: In this case, we define conversion as making a payment.
  • Cohort: A collection of host that share some attribute. If we are calculating the new user virality coefficient, the shared attribute is that they converted in the same time frame.
  • Time limit: To make the data comparable across cohorts, we must look at the same time frame. Here, we use one week. If you are familiar with cohort tables, they show the value across several time periods.
  • One week new user virality coefficient = Number of converted guests by an existing user within one week of that user converting themselves
  • 1wnuvc=(guests,1w | host)/hosts

What the database is assumed to contain

A typical database structure for an invite program could look something like this:

Invite_table
Host_id
Guest_id
Guest_first_payment_date

User table
Id
Sign_up_date
First_payment_date

Step 1

First we define what cohort we want to measure. In this example, we will define a cohort as the week of the first payment.

select 
date_format(user_table. first_payment_date, '%x-%v')
, count(user_table.first_payment_date)
from user_table
group by 1

By using %x instead of %Y to calculate the year, we make sure to calculate the year correctly even when the year changes.

Step 2

We then calculate the virality coefficient per host within given time frame. Let's say we want to measure the one week virality coefficient. The case statement checks if the difference between the conversion of the guest and the host is less than seven days and only counts those cases.

Since the date of the host's first payment is stored in user_table, we need to do a join to be able to calculate the difference.

select
invite_table.host_id
,sum(case when
datediff(invite_table.guest_first_payment_date, user_table.first_payment_date) <=7
then 1
else 0
end) as individual_one_week_vc
from invite_table
join user_table
on user_table.id = invite_table.host_id
group by 1

Step 3

Finally, we combine the two. The virality coefficient for the weekly cohort is calculated as the sum of the individual virality coefficient, divided by the number of hosts in the cohort.

select 
date_format(user_table. first_payment_date, '%x-%v')
,count(user_table. first_payment_date)
,sum(conversions.individual_one_week_vc)/count(user_table. first_payment_date) as one_week_virality_vc
from user_table

left join (
select
invite_table.host_id
,sum(case when
datediff(invite_table.guest_first_payment_date, user_table.first_payment_date) <=7
then 1
else 0
end) as individual_one_week_vc
from invite_table
join user_table
on user_table.id = invite_table.host_id
group by 1
) conversions
on conversions.host_id=user_table.id

group by 1


Entertaining Blogs - BlogCatalog Blog Directory
Bloggtoppen.se