13 jan, 2012
Calculando previsão de vendas com PHP e PostgreSQL
Posted by: Tiago Silva In: PHP|PostgreSQL
Olá Amigos!
Hoje eu vou tratar de um assunto da época da faculdade, o cálculo da previsão de vendas de um determinado produto, com base nas vendas anteriores, para tal iremos utilizar o PostgreSQL e PHP.
Mas antes de mais nada, estou disponibilizando um documento do Excel com os mesmos dados da figura abaixo, juntamente com um gráfico contendo os resultados e informações importantes como o valor do r² e a equação do nosso modelo. Também achei importante disponibilizar os slides utilizados pelo Prof. Darlan quando este assunto foi ministrado, lá você irá encontrar mais conteúdo e uma bagagem teórica solida e enxuta.
Primeiramente vamos ao nosso problema:
Quanto eu devo investir em publicidade para ter um determinado ganho em vendas?
Quando nós chegarmos ao ponto de extrair os dados do Postgres perceberemos que na modelagem do banco estes dados (investimento e vendas) não tem nenhuma correlação, veja bem, do ponto de vista da modelagem. Por isso é muito importante que você compreenda bem nosso problema, e para simplificar na tabela abaixo temos os investimentos (ou gastos) com publicidade representado pela variável x, e as vendas representadas pela variável y. Note que além desses dados temos outros como x², y² e xy, que iremos utilizar posteriormente.
Veremos estes mesmos dados a seguir quando estivermos trabalhando no banco de dados, coloquei a tabela de ante-mão para abstrair melhor nosso trabalho, e na seção seguinte do post iremos abordar os conceitos matemáticos envolvidos e os nomes das colunas (as variáveis) irão aparecer nas fórmulas, e como já vimos estruturadamente, ficará mais fácil.
O objetivo então é que possamos construir uma aplicação que nos informe qual será o valor que iremos vender, baseando-se no valor que iremos investir em publicidade.
Ahh, antes que os mais críticos se envolvam, gostaria de dizer que este é um exemplo simples, se você quiser pode melhora-lo de acordo com a sua necessidade 
A matemática por traz do cálculo
Antes que muitos torçam o nariz ou pulem essa parte, já vou adiantando que nossa matemática será breve e simples. É fundamental que você entenda essas fórmulas (além de ler os slides que falei acima) para entender como funciona o nosso modelo.
E por falar em modelo, aqui temos um estatístico probabilístico, de regressão linear simples. Veja a representação do nosso modelo de equação abaixo:
Aqui temos que a variável y é explicada pela variável x, que por sua vez é explicativa. Então veja que, se estamos representando os gastos com publicidade por x, e as vendas por y, temos que os gastos com publicidade é que vão determinar o valor das vendas.
Note também que temos dois coeficientes, chamados de a e b. As duas fórmulas a seguir irão utilizar os nossos dados que estão na tabela (conforme a figura da primeira seção), que na verdade iremos regastar do Postgres. Veja que abaixo temos a representação da fórmula onde iremos encontrar o valor de a:
Você deve ter percebido que para calcular o valor de a, antes é necessário obter o valor de b, pois bem, abaixo está a fórmula para o cálculo, veja um detalhe importante: o valor de n é exatamente o número de linhas da nossa tabela (ou o número de registros no banco de dados).
Agora que você já tem as duas fórmulas poderá chegar na equeção linear do nosso modelo, que será parecida com esta:
y = 2,0754 + 2,0884.x
Como saber se o modelo é confiável?
Para sabermos se o nosso modelo é confiável contaremos com o Coeficiente de Correlação de Person, ou o famoso r². Podemos calcular o valor do nosso coeficiente com a singela fórmula abaixo:
O resultado de r², irá variar entre -1 e 1, de forma que dependendo da variação, ela terá estes significados:
- r² = -1 : Existe uma correlação inversa entra as variáveis, ou seja, quando uma aumenta a outra diminui.
- r² = 0 : Modelo ruim, quanto mais próximo de 0, menos acertivo será seu modelo.
- r² = 1 : Significa que seu modelo é muito bom! Ou seja, quanto mais próximo de 1, melhor!
No nosso exemplo, r² é de aproximadamente 0,93 e observe que ele está bem próximo de 1, o que nos indica que nosso modelo é consistente.
Construção das tabelas, queries, e dados no PostgreSQL
Este é o ponto mais importante do trabalho, aqui é onde iremos armazenar os dados que servirão de base para construírmos o modelo estatístico de previsão, para isso tome como base as duas tabelas a seguir conforme o modelo mostrado anteriormente:
CREATE TABLE investimento_publicidade ( cod_investimento SERIAL, valor_investimento DOUBLE PRECISION NOT NULL, data_realizacao TIMESTAMP NOT NULL DEFAULT NOW(), PRIMARY KEY (cod_investimento) );
CREATE TABLE vendas ( id_produto SERIAL, nome VARCHAR(100) NOT NULL, preco_unitario DOUBLE PRECISION NOT NULL, quantidade_vendida INTEGER NOT NULL, PRIMARY KEY (id_produto) );
Agora iremos abastacer o nosso modelo com os mesmos dados que utilizamos acima. Para agilizar e para podermos testar depois, disponibilizei o script SQL com as instruções INSERT abaixo:
INSERT INTO investimento_publicidade (valor_investimento) VALUES (3);
INSERT INTO investimento_publicidade (valor_investimento) VALUES (4);
INSERT INTO investimento_publicidade (valor_investimento) VALUES (8);
INSERT INTO investimento_publicidade (valor_investimento) VALUES (12);
INSERT INTO investimento_publicidade (valor_investimento) VALUES (14);
INSERT INTO vendas (nome, preco_unitario, quantidade_vendida) VALUES ('Produto A', 7 , 1);
INSERT INTO vendas (nome, preco_unitario, quantidade_vendida) VALUES ('Produto B', 14, 1);
INSERT INTO vendas (nome, preco_unitario, quantidade_vendida) VALUES ('Produto A', 15, 1);
INSERT INTO vendas (nome, preco_unitario, quantidade_vendida) VALUES ('Produto C', 28, 1);
INSERT INTO vendas (nome, preco_unitario, quantidade_vendida) VALUES ('Produto C', 32, 1);
Muito bem! Agora temos os nossos dados, e neste ponto vamos extaí-los do banco para elaborarmos a equação do nosso modelo de previsão com o PHP!
Então, considere as duas queries abaixo, primeiramente a query que nos retornará os valores investidos em publicidade:
SELECT SUM(valor_inestimento) AS x_somatoria_invest_publicidade, SUM(valor_inestimento^2) AS x_quadrado FROM investimento_publicidade
Veja o que estamos retornando nesta query: a nossa variável x, que é a somatória dos valores investidos em publicidade e o x², que iremos utilizar nas fórmulas.
E agora a query com os valores das vendas:
SELECT SUM((preco_unitario*quantidade_vendida)) AS y_somatoria_vendas, SUM((preco_unitario*quantidade_vendida)^2) AS y_quadrado FROM vendas
Ok, temos retorno das variáveis y e y², repare que eu fiz umas brincadeirinhas, como a multiplicação do valor vendido pelo preço unitário, se você quiser pode brincar um pouco com os modelos depois alterando estes valores diretamente na tabela vendas.
NOTA: Eu poderia realizar toda a previsão de vendas somente dentro do Postgres, mas aqui quero utilizar o máximo de ferramentas possíveis, a previsão de vendas somente no PostgreSQL fica para um próximo post ok? =)
Exibição do modelo e simulação de dados com PHP
Veja abaixo o código de uma classe que irá realizar tanto os cálculos quanto as queries no Postgres, os comentários no código já explicam o que está acontecendo:
class PrevisaoVendas
{
/**
* Atributo estático onde armazenaremos nosso objeto PDO para nossa comunicação com o banco de dados.
*/
private static $conn;
/**
* Construtor da classe onde criamos e armazenamos nosso objeto PDO.
*/
public function __construct()
{
$opcoes_conexao = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_PERSISTENT => true);
self::$conn = new PDO("pgsql:host=127.0.0.1 dbname=PREVISAO_VENDAS", "postgres", "senha", $opcoes_conexao);
}
/**
* Método responsável por retornar um objeto (stdClass) com os valores do x e x quadrado (investimento em publicidade)
*/
private function getValoresInvestimentosX()
{
$sql = "SELECT SUM(valor_inestimento) AS x_somatoria_invest_publicidade, SUM(valor_inestimento^2) AS x_quadrado "
. "FROM investimento_publicidade";
$stmt = self::$conn->prepare($sql);
$stmt->execute();
return $stmt->fetchObject();
}
/**
* Método responsável por retornar um objeto (stdClass) com os valores do y e y quadrado (vendas)
*/
private function getValoresVendasY()
{
$sql = "SELECT SUM((preco_unitario*quantidade_vendida)) AS y_somatoria_vendas, SUM((preco_unitario*quantidade_vendida)^2) AS y_quadrado "
. "FROM vendas";
$stmt = self::$conn->prepare($sql);
$stmt->execute();
return $stmt->fetchObject();
}
/**
* Método que calcula o valor de xy, a dica é você olha no manual do PHP as funções array_combine e array_sum
*/
private function getValorXY()
{
/**
* Obtendo os valores de x;
*/
$stmt = self::$conn->prepare("SELECT valor_inestimento AS x FROM investimento_publicidade LIMIT 5");
$stmt->execute();
$x = array();
while($rows = $stmt->fetchObject())
$x[] = $rows->x;
/**
* Obtendo os valores de y;
*/
$stmt = self::$conn->prepare("SELECT preco_unitario*quantidade_vendida AS y FROM vendas LIMIT 5");
$stmt->execute();
$y = array();
while($rows = $stmt->fetchObject())
$y[] = $rows->y;
/**
* Calculando o valor da soma de xy
*/
$xy_aux = array_combine($x, $y);
$xy = array();
foreach($xy_aux as $key => $value)
$xy[] = $key * $value;
return array_sum($xy);
}
/**
* Obtem o número de registros de vendas, o "n" utilizado nas equações, observe que o número de vendas poderá não ser igual ao número de
* investimentos, caberá a você criar um mecanismo para definir que sempre os dados analizados estarão na mesma proporção e consistentes,
* uma dica é a utilização de LIMIT e OFFSET.
*/
private function getNumVendas()
{
$sql = "SELECT COUNT(*) FROM vendas";
$stmt = self::$conn->prepare($sql);
$stmt->execute();
return $stmt->fetchColumn();
}
/**
* Método onde estamos resolvendo a equação para encontrar o valor de "a".
*/
private function getValorEquacaoA()
{
$x = $this->getValoresInvestimentosX();
$y = $this->getValoresVendasY();
$n = $this->getNumVendas();
$b = $this->getValorEquacaoB();
$a = ($y->y_somatoria_vendas - ($b * $x->x_somatoria_invest_publicidade)) / $n;
return $a;
}
/**
* Método onde estamos resolvendo a equação para encontrar o valor de "b".
*/
private function getValorEquacaoB()
{
$x = $this->getValoresInvestimentosX();
$y = $this->getValoresVendasY();
$n = $this->getNumVendas();
$xy = $this->getValorXY();
$b = (($n * $xy) - ($x->x_somatoria_invest_publicidade * $y->y_somatoria_vendas)) / (($n * $x->x_quadrado) - pow($x->x_somatoria_invest_publicidade, 2));
return $b;
}
/**
* Método para a identificação do valor de r quadrado, onde veremos que conforme o valor do índice teremos o nível de confiabilidade do modelo.
*/
public function getValorRQuadrado()
{
$x = $this->getValoresInvestimentosX();
$y = $this->getValoresVendasY();
$n = $this->getNumVendas();
$xy = $this->getValorXY();
$r = (($n * $xy) - ($x->x_somatoria_invest_publicidade * $y->y_somatoria_vendas)) / (sqrt(($n * $x->x_quadrado) - pow($x->x_somatoria_invest_publicidade, 2)) * sqrt(($n * $y->y_quadrado) - pow($y->y_somatoria_vendas, 2)));
$r_quadrado = pow($r, 2);
return $r_quadrado;
}
/**
* Método para obteção de uma string contendo a fórmula do modelo.
*/
public function getStringFormulaModeloPrevisor()
{
$a = $this->getValorEquacaoA();
$b = $this->getValorEquacaoB();
$string = "y = " . number_format($a, 4, ",", "") . " + " . number_format($b, 4, ",", "") . ".x";
return $string;
}
/**
* Método que capta um dado valor em investimento de publicidade, e por meio do nosso modelo, retorna a previsão das vendas.
*/
public function getValorSimuladoVendas($valor_invest_publicidade)
{
$a = $this->getValorEquacaoA();
$b = $this->getValorEquacaoB();
$y = $a + ($b * $valor_invest_publicidade);
return $y; // Nosso valor estimado das vendas.
}
}
Veja um exemplo de utilização da classe:
$preview = new PrevisaoVendas(); $preview->getStringFormulaModeloPrevisor(); // Irá nos retornar a fórmula. $preview->getValorRQuadrado(); // Valor do r quarado com a precisão do nosso modelo. $preview->getValorSimuladoVendas(10.0); // Retorna o valor das vendas, se no caso, invesrtirmos $ 10,00 em propaganda.
Então amigos, é isso ai, espero que vocês tenham gostado da simplória abordagem. Eu gostaria muito de saber das experiências de vocês, então fiquem a vontade para comentar!
Referências:
- Análise de Regressão – Slides
- Wikipedia – Coeficiente de Correlação de Person
- PHP Documentation – Função pow()
- PHP Documentation – Função sqrt()
- PHP Documentation – Função array_combine()
- PHP Documentation – Função array_sum()
Abraços,
