Saltar al contenido principal
waffle.svg
Domo

Creating an R Forecast

Version 1

 

Important: When referencing this page outside of Knowledge Base, use this link: http://knowledge.domo.com?cid=rforecast

domo_forecast.sql

#fcast_prep
select
    t1.dt,
    datediff(t1.dt,(select min(dt) from dp17_thinking_ahead_user_growth)) as dt_index,
    t2.intercept,
    t2.lin,
    t2.quad
from date_dimension t1
left join user_growth_model t2
    on 1 = 1
where t1.dt >= (select min(dt) from dp17_thinking_ahead_user_growth) and 
    t1.dt <= date_format(current_date(),'%Y-12-31')

#fcast_final
select
    dt,
    dt_index,
    round(intercept + dt_index*lin + power(dt_index,2)*quad,0) as forecast
from fcast_prep

#final
select
    t2.dt,
    t1.total_users,
    t2.forecast
from dp17_thinking_ahead_user_growth t1
right join fcast_final t2
    on t1.dt = t2.dt

user_growth.r

library(DomoR)
library(ggplot2)
library(dplyr)

DomoR::init('dp17-customer','cc172d70a4421f90d26d2e1fc07ee5a8f087f770f97205f0') #Token expires May 22, 2017
user_counts <- fetch('22ee81a9-2dbd-4a03-82ac-17c909b6f5a0')

uc_model <- user_counts %>% mutate(dt_index=as.numeric(dt - min(dt)))

ggplot(data=user_counts) + 
    geom_line(aes(x=dt,y=total_users)) + 
    scale_y_continuous(labels=scales::comma) + 
    labs(x='Date',y='User Count') + 
    theme_bw()

summary(mylm <- lm(total_users ~ dt_index + I(dt_index^2),data=uc_model))

fcast <- data.frame(dt=seq(from=as.Date(min(uc_model$dt)),to=as.Date(format(max(uc_model$dt),'%Y-12-31')),by='day')) %>% 
    mutate(dt_index=as.numeric(dt - min(dt))) %>% left_join(uc_model)

fcast$fcast <- predict(mylm,fcast)

ggplot(data=fcast) + 
    geom_line(aes(x=dt,y=total_users)) + 
    geom_line(aes(x=dt,y=fcast),color='red') + 
    scale_y_continuous(labels=scales::comma) + 
    labs(x='Date',y='User Count') + 
    theme_bw()

up_model <- data.frame(t(coefficients(mylm)))
names(up_model) <- c('intercept','lin','quad')

#This line creates a new data set
#DomoR::create(up_model,'User Growth Model','Model coefficients')

#This line replaces an existing data set
#repl <- DomoR::replace_ds('533b3f4f-2f80-41c9-9397-2061cea39710',up_model)