Você trabalha em uma rede de lojas de roupas que possui 25 lojas espalhadas pelo Brasil.
Diariamente, os Analistas de Dados calculam o OnePage de cada loja e enviam para o gerente da respectiva loja.
O OnePage é um resumo gerencial que nos permite analisar o desempenho de uma loja com base nos seguintes indicadores:
Indicador | Meta do Ano | Meta do Dia | |
---|---|---|---|
Faturamento | 1.650.000 | 1.000 | |
Diversidade de Produtos | 120 | 4 | |
Ticket Médio | 500 | 500 |
Nosso desafio envolve três passos:
# Importa o módulo os
import os
# Importa o pandas com o apelido pd
import pandas as pd
# Importa a bibliioteca yagmail
import yagmail
# Importa a função para carregar as variáveis de ambiente
from dotenv import load_dotenv
# Caminho deste notebook
caminho_nb = os.getcwd()
# Carrega as variáveis de ambiente do arquivo .env (dotenv)
load_dotenv(os.path.join(os.path.abspath(caminho_nb), '.env'))
# E-mail do remetente
remetente = os.getenv('EMAIL_REMETENTE')
# Senha do remetente
senha = os.getenv('SENHA_EMAIL_REMETENTE')
# Metas de faturamento
meta_faturamento_dia = 1_000
meta_faturamento_ano = 1_650_000
# Metas de diversidade de produtos
meta_diversidade_produtos_dia = 4
meta_diversidade_produtos_ano = 120
# Metas de ticket médio
meta_ticket_medio_dia = 500
meta_ticket_medio_ano = 500
# Caminho da pasta com as bases de dados
pasta_bases_dados = os.path.join(caminho_nb, 'Bases de Dados')
# Importa as bases de dados
df_vendas = pd.read_excel(os.path.join(pasta_bases_dados, 'Vendas.xlsx'))
df_emails = pd.read_excel(os.path.join(pasta_bases_dados, 'Emails.xlsx'))
df_lojas = pd.read_csv(os.path.join(pasta_bases_dados, 'Lojas.csv'), sep=';', encoding='latin1')
df_vendas.head()
Código Venda | Data | ID Loja | Produto | Quantidade | Valor Unitário | Valor Final | |
---|---|---|---|---|---|---|---|
0 | 1 | 2019-01-01 | 1 | Sapato Estampa | 1 | 358 | 358 |
1 | 1 | 2019-01-01 | 1 | Camiseta | 2 | 180 | 360 |
2 | 1 | 2019-01-01 | 1 | Sapato Xadrez | 1 | 368 | 368 |
3 | 2 | 2019-01-02 | 3 | Relógio | 3 | 200 | 600 |
4 | 2 | 2019-01-02 | 3 | Chinelo Liso | 1 | 71 | 71 |
df_emails.head()
Loja | Gerente | ||
---|---|---|---|
0 | Iguatemi Esplanada | Helena | diegotorrescoder+helena@gmail.com |
1 | Shopping Midway Mall | Alice | diegotorrescoder+alice@gmail.com |
2 | Norte Shopping | Laura | diegotorrescoder+laura@gmail.com |
3 | Shopping Iguatemi Fortaleza | Manuela | diegotorrescoder+manuela@gmail.com |
4 | Shopping União de Osasco | Valentina | diegotorrescoder+valentina@gmail.com |
df_lojas.head()
ID Loja | Loja | |
---|---|---|
0 | 1 | Iguatemi Esplanada |
1 | 2 | Shopping Midway Mall |
2 | 3 | Norte Shopping |
3 | 4 | Shopping Iguatemi Fortaleza |
4 | 5 | Shopping União de Osasco |
# Mescla as duas tabelas com base na coluna ID Loja
df_vendas = df_vendas.merge(df_lojas, on='ID Loja')
df_vendas.head()
Código Venda | Data | ID Loja | Produto | Quantidade | Valor Unitário | Valor Final | Loja | |
---|---|---|---|---|---|---|---|---|
0 | 1 | 2019-01-01 | 1 | Sapato Estampa | 1 | 358 | 358 | Iguatemi Esplanada |
1 | 1 | 2019-01-01 | 1 | Camiseta | 2 | 180 | 360 | Iguatemi Esplanada |
2 | 1 | 2019-01-01 | 1 | Sapato Xadrez | 1 | 368 | 368 | Iguatemi Esplanada |
3 | 21 | 2019-01-02 | 1 | Camisa Gola V Listrado | 2 | 116 | 232 | Iguatemi Esplanada |
4 | 34 | 2019-01-02 | 1 | Sapato Listrado | 1 | 363 | 363 | Iguatemi Esplanada |
# Dicionário vazio para armazenar os dataframes relativos a cada loja
lojas = dict()
# Lista de lojas únicas
lista_lojas = list(df_vendas['Loja'].unique())
# Percorre a lista de lojas
for loja in lista_lojas:
# Cria uma nova entrada no dicionário
# Cada chave é o nome de uma loja
# Cada valor é o dataframe com os dados de vendas de uma loja, incluindo todas as colunas
# Lógica do filtro:
# "Pegue todas as linhas da tabela de vendas em que as células da coluna 'Loja' contenham o nome da loja desta iteração"
lojas[loja] = df_vendas.loc[df_vendas['Loja'] == loja, :]
# Obtém a maior data (data mais recente) da coluna de Data da tabela de vendas
data_indicador = df_vendas['Data'].max()
Nesta etapa, vamos criar uma pasta para cada loja e salvar uma planilha com os seus respectivos registros de vendas.
# Muda de diretório
os.chdir('Backup Arquivos Lojas')
# Percorre o dicionário de lojas
for loja in lojas:
# Verifica se não existe uma pasta com o nome da loja desta iteração
if not os.path.exists(loja):
# Cria uma pasta com o nome da loja
os.mkdir(loja)
# Nome do arquivo
# Exemplo: 11-30-Iguatemi Esplanada.xlsx
nome_arquivo = '{}-{}-{}.xlsx'.format(data_indicador.month, data_indicador.day, loja)
# Pasta de destino do arquivo
destino_arquivo = os.path.join(os.getcwd(), loja, nome_arquivo)
# Exporta o dataframe para a pasta de destino
lojas[loja].to_excel(destino_arquivo)
# Volta para a pasta deste notebook
os.chdir('..')
Esta função é usada para enviar os e-mails:
def enviar_email(remetente, senha, destinatario, assunto, mensagem, anexo):
try:
# Inicializa uma conexão com um servidor SMTP, criando uma instância de yagmail.SMTP
yag = yagmail.SMTP(user=remetente, password=senha)
# Envia o e-mail com os detalhes de nosso e-mail
yag.send(to=destinatario, subject=assunto, contents=mensagem, attachments=anexo)
# Exibe uma mensagem de que o e-mail foi enviado
print('E-mail enviado com sucesso.')
except:
print('Erro: o e-mail não foi enviado.')
Esta função é usada para escrever a mensagem do e-mail:
def escrever_mensagem(nome_gerente, data_indicador, meta_faturamento_dia, faturamento_dia, meta_diversidade_produtos_dia, diversidade_produtos_dia, meta_ticket_medio_dia, ticket_medio_dia, faturamento_ano, meta_faturamento_ano, diversidade_produtos_ano, meta_diversidade_produtos_ano, ticket_medio_ano, meta_ticket_medio_ano):
return f'''
<p>Bom-dia, {nome_gerente}.</p>
<p>
O resultado de ontem <strong>({data_indicador.day}/{data_indicador.month})</strong>
da loja <strong>{loja}</strong> está descrito nas tabelas a seguir:
</p>
<table>
<thead>
<tr>
<th>Indicador</th>
<th>Meta do Dia</th>
<th>Resultado</th>
<th>Cenário</th>
</tr>
</thead>
<tbody>
<tr>
<td>Faturamento</td>
<td>{meta_faturamento_dia}</td>
<td>{faturamento_dia}</td>
<td>{'▲' if faturamento_dia >= meta_faturamento_dia else '▼'}</td>
</tr>
<tr>
<td>Diversidade de Produtos</td>
<td>{meta_diversidade_produtos_dia}</td>
<td>{diversidade_produtos_dia}</td>
<td>{'▲' if diversidade_produtos_dia >= meta_diversidade_produtos_dia else '▼'}</td>
</tr>
<tr>
<td>Ticket Médio</td>
<td>{meta_ticket_medio_dia}</td>
<td>{ticket_medio_dia}</td>
<td>{'▲' if ticket_medio_dia >= meta_ticket_medio_dia else '▼'}</td>
</tr>
</tbody>
</table>
</br>
<table>
<thead>
<tr>
<th>Indicador</th>
<th>Meta do Ano</th>
<th>Resultado</th>
<th>Cenário</th>
</tr>
</thead>
<tbody>
<tr>
<td>Faturamento</td>
<td>{meta_faturamento_ano}</td>
<td>{faturamento_ano}</td>
<td>{'▲' if faturamento_ano >= meta_faturamento_ano else '▼'}</td>
</tr>
<tr>
<td>Diversidade de Produtos</td>
<td>{meta_diversidade_produtos_ano}</td>
<td>{diversidade_produtos_ano}</td>
<td>{'▲' if diversidade_produtos_ano >= meta_diversidade_produtos_ano else '▼'}</td>
</tr>
<tr>
<td>Ticket Médio</td>
<td>{meta_ticket_medio_ano}</td>
<td>{ticket_medio_ano}</td>
<td>{'▲' if ticket_medio_ano >= meta_ticket_medio_ano else '▼'}</td>
</tr>
</tbody>
</table>
<p>Segue em anexo a planilha com todos os dados para mais detalhes.</p>
<p>Caso tenha alguma dúvida, saiba que estou a disposição.</p>
<p>Atenciosamente, Diego Moura Torres</p>
'''
Estas funções são usadas para calcular os indicadores:
def calcular_faturamento(tabela):
'''Calcula o faturamento com os registros da tabela passada como argumento'''
return tabela['Valor Final'].sum()
def calcular_diversidade_produtos(tabela):
'''Calcula a diversidade de produtos com os registros da tabela passada como argumento'''
return len(tabela['Produto'].unique())
def calcular_ticket_medio(tabela):
'''Calcula o ticket médio com base nos valores de venda'''
# Agrupa os registros de venda pelo Código da Venda, somando os valores de venda
df_vendas_agrupadas = tabela[['Código Venda', 'Valor Final']].groupby('Código Venda').sum()
# Retorna a média dos valores de todas as vendas (ticket médio das vendas)
return df_vendas_agrupadas['Valor Final'].mean()
# Percorre as entradas do dicionário de lojas
for loja in lojas:
# Armazena o dataframe com os dados de vendas da loja desta iteração
df_vendas_loja = lojas[loja]
# Armazena o dataframe com os dados de venda da data mais recente
df_vendas_loja_ultimo_dia = df_vendas_loja.loc[df_vendas_loja['Data'] == data_indicador, :]
# Calcula o faturamento acumulado da loja desta iteração
faturamento_ano = calcular_faturamento(df_vendas_loja)
# Calcula o faturamento do dia da loja desta iteração
faturamento_dia = calcular_faturamento(df_vendas_loja_ultimo_dia)
# Calcula a diversidade de produtos para o ano
diversidade_produtos_ano = calcular_diversidade_produtos(df_vendas_loja)
# Calcula a diversidade de produtos para o dia
diversidade_produtos_dia = calcular_diversidade_produtos(df_vendas_loja_ultimo_dia)
# Calcula o ticket médio para o ano
ticket_medio_ano = calcular_ticket_medio(df_vendas_loja)
# Calcula o ticket médio para o dia
ticket_medio_dia = calcular_ticket_medio(df_vendas_loja_ultimo_dia)
# Nome do gerente para o qual o e-mail será enviado
nome_gerente = df_emails.loc[df_emails['Loja'] == loja, 'Gerente'].values[0]
# E-mail do gerente
destinatario = df_emails.loc[df_emails['Loja'] == loja, 'E-mail'].values[0]
# Assunto do e-mail
assunto = 'OnePage Dia {}/{} - Loja {}'.format(data_indicador.day, data_indicador.month, loja)
# Mensagem do e-mail
mensagem = escrever_mensagem(
nome_gerente,
data_indicador,
meta_faturamento_dia,
faturamento_dia,
meta_diversidade_produtos_dia,
diversidade_produtos_dia,
meta_ticket_medio_dia,
ticket_medio_dia,
faturamento_ano,
meta_faturamento_ano,
diversidade_produtos_ano,
meta_diversidade_produtos_ano,
ticket_medio_ano,
meta_ticket_medio_ano
)
# Caminho do anexo do e-mail
anexo = os.path.join(
caminho_nb,
'Backup Arquivos Lojas',
loja,
f'{data_indicador.month}-{data_indicador.day}-{loja}.xlsx'
)
enviar_email(remetente, senha, destinatario, assunto, mensagem, anexo)
E-mail enviado com sucesso. E-mail enviado com sucesso. E-mail enviado com sucesso. E-mail enviado com sucesso. E-mail enviado com sucesso. E-mail enviado com sucesso. E-mail enviado com sucesso. E-mail enviado com sucesso. E-mail enviado com sucesso. E-mail enviado com sucesso. E-mail enviado com sucesso. E-mail enviado com sucesso. Erro: o e-mail não foi enviado. E-mail enviado com sucesso. E-mail enviado com sucesso. E-mail enviado com sucesso. E-mail enviado com sucesso. E-mail enviado com sucesso. E-mail enviado com sucesso. E-mail enviado com sucesso. E-mail enviado com sucesso. E-mail enviado com sucesso. E-mail enviado com sucesso. E-mail enviado com sucesso. E-mail enviado com sucesso.
# Tabela com os valores de faturamento total de cada loja
df_faturamento_ano = df_vendas[['Loja', 'Valor Final']].groupby('Loja').sum()
# Ordena os valores de faturamento em ordem decrescente
df_faturamento_ano.sort_values(by='Valor Final', ascending=False)
# Muda de diretório
os.chdir('Backup Arquivos Lojas')
# Nome do arquivo a ser exportado para a pasta de backups
nome_arquivo = '{}-{}-Ranking de Faturamento do Ano.xlsx'.format(data_indicador.month, data_indicador.day)
# Exporta o dataframe
df_faturamento_ano.to_excel(nome_arquivo)
# Tabela com os dados de vendas do último dia (data mais recente)
df_vendas_lojas_ultimo_dia = df_vendas.loc[df_vendas['Data'] == data_indicador, :]
# Tabela com os valores de faturamento do dia de cada loja
df_faturamento_dia = df_vendas_lojas_ultimo_dia[['Loja', 'Valor Final']].groupby('Loja').sum()
# Ordena os valores de faturamento em ordem decrescente
df_faturamento_dia.sort_values(by='Valor Final', ascending=False)
# Nome do arquivo a ser exportado para a pasta de backups
nome_arquivo = '{}-{}-Ranking de Faturamento do Dia.xlsx'.format(data_indicador.month, data_indicador.day)
# Exporta o dataframe
df_faturamento_dia.to_excel(nome_arquivo)
# E-mail da diretoria
destinatario = df_emails.loc[df_emails['Loja'] == 'Diretoria', 'E-mail'].values[0]
# Assunto do e-mail
assunto = 'Ranking de Lojas Dia {}/{}'.format(data_indicador.day, data_indicador.month)
# Mensagem do e-mail
mensagem = f'''Prezados, desejo a todos um ótimo dia.
Resumidamente, hoje temos o seguinte cenário:
Análise do Dia
<table>
<thead>
<tr>
<th>Melhor Loja</th>
<th>Faturamento</th>
</tr>
</thead>
<tbody>
<tr>
<td>{df_faturamento_dia.index[0]}</td>
<td>R${df_faturamento_dia.iloc[0, 0]}</td>
</tr>
</tbody>
</table>
<table>
<thead>
<tr>
<th>Pior Loja</th>
<th>Faturamento</th>
</tr>
</thead>
<tbody>
<tr>
<td>{df_faturamento_dia.index[-1]}</td>
<td>R${df_faturamento_dia.iloc[-1, 0]}</td>
</tr>
</tbody>
</table>
Análise do Ano
<table>
<thead>
<tr>
<th>Melhor Loja</th>
<th>Faturamento</th>
</tr>
</thead>
<tbody>
<tr>
<td>{df_faturamento_ano.index[0]}</td>
<td>R${df_faturamento_ano.iloc[0, 0]}</td>
</tr>
</tbody>
</table>
<table>
<thead>
<tr>
<th>Pior Loja</th>
<th>Faturamento</th>
</tr>
</thead>
<tbody>
<tr>
<td>{df_faturamento_ano.index[-1]}</td>
<td>R${df_faturamento_ano.iloc[-1, 0]}</td>
</tr>
</tbody>
</table>
Seguem em anexo as planilhas com os rankings de faturamento do dia e do ano para cada uma das lojas.
Caso tenham alguma dúvida, estarei a disposição.
Atenciosamente,
Diego Moura Torres
'''
# Caminho do anexo do ranking do ano
anexo_ranking_ano = os.path.join(
caminho_nb,
'Backup Arquivos Lojas',
f'{data_indicador.month}-{data_indicador.day}-Ranking de Faturamento do Ano.xlsx'
)
anexo_ranking_dia = os.path.join(
caminho_nb,
'Backup Arquivos Lojas',
f'{data_indicador.month}-{data_indicador.day}-Ranking de Faturamento do Dia.xlsx'
)
# Envia o e-mail para a diretoria
enviar_email(remetente, senha, destinatario, assunto, mensagem, anexo=[anexo_ranking_ano, anexo_ranking_dia])
E-mail enviado com sucesso.