Notas¶
Veja outros detalhes do projeto no github: https://github.com/gtherezan/ecommerce-insights
Cenário¶
Em uma empresa varejista de brinquedos serei o profissional responsável pela equipe de análise de dados da plataforma de vendas online. Serão criadas importantes métricas e indicadores de performance, como: iniciativas de marketing pago, taxas de conversão, impacto de novos produtos no portifólio e indicadores de qualidade.
Método¶
Utilizaremos consultas SQL para realizar análises dos dados presentes em um servidor MySQL hospedado localmente. Também será utilizada a linguagem de programação Python e bibliotecas de visualização de dados para a criação de gráficos. Serão empregadas técnicas de data storytelling baseadas no livro Storytelling com dados, Cole Knaflic (2016).
Este relatório foi desenvolvido utilizando o Jupyter Notebook em um ambiente virtual da distribuição Anaconda.
SQL Connection Setup¶
Carregamento da extensão do IPython: SQL
In [1]:
%load_ext sql
Configuração da conexão do toolkit SQL Alchemy (driver PyMySQL) com o servidor MySQL hospedado localmente.
In [2]:
%sql mysql+pymysql://root:Mysqlpw@localhost:3306/mavenfuzzyfactory
Out[2]:
Seleção do banco de dados a ser utilizado:
In [3]:
%sql USE mavenfuzzyfactory
Out[3]:
NOTA: o comando acima é redundante mas é uma forma simples de testar a conexão.
Iniciando Configuração do Python¶
Importação das bibliotecas utilizadas:
In [4]:
import numpy as np # importando a biblioteca numpy com o alias abreviado "np"
import pandas as pd # importando a biblioteca pandas com o alias abreviado "pd"
import seaborn as sns # importando a biblioteca seaborn com o alias abreviado "sns"
import matplotlib.pyplot as plt # importando o módulo da biblioteca matplotlib com o alias abreviado "plt"
import locale # importando a biblioteca locale
import plotly.express as px # importando o módulo express da biblioteca plotly com o alias "px"
Configuração da saída dos gráficos gerados pelo matplotlib para exibi-los diretamente neste documento:
In [5]:
%matplotlib inline
Configuração da região, idioma e outros para Português do Brasil.
In [6]:
locale.setlocale(locale.LC_TIME, "pt_BR");
Com o objetivo de simular um cenário real, os dados são limitados até o momento da análise que respeitam uma cronologia .
Análise de Fonte de Tráfego Pago¶
Análise mostra quais fontes trazem mais visitantes ao site.
In [7]:
%%sql
SELECT
utm_source, -- exibição de parâmetro de rastreio source
utm_campaign, -- exibição de parâmetro de rastreio campaign
http_referer, -- exibição da origem da requisição http
COUNT(website_session_id) AS sessions -- função de agregação para contagem das sessões
FROM
website_sessions -- escolha da tabela de sessões do website
WHERE
created_at < '2012-04-12' -- filtro de dados para data da análise periodo: início até 12/04/2012
GROUP BY 1 , 2 , 3 -- agrupamento por utm_source, utm_campaign, http_referer respectivamente
ORDER BY sessions DESC -- ordenação decrescente pelo número de sessões
Out[7]:
Os resultados apontam que 97,4% das visitas vêm do Google utm_source = gsearch através de buscas não relacionadas à marca utm_campaign = nonbrand
Notada a grande frequência de tráfego advindo do Google podemos extender a análise e verificar o quanto é convertido em vendas.
Taxa de Conversão do Google Search Nonbrand¶
Análise percentual de conversão das sessões vindas do Google utm_source = gsearch e buscas não relacionadas à marca utm_campaign = nonbrand em vendas realizadas.
Definida a meta de ter uma conversão de 4% podemos traçar planos de ação caso o resultado seja:
- Inferior - diminuir despesas nesse tipo de tráfego pago
- Superior - aumentar investimentos para elevar o volume de sessões e vendas
In [8]:
%%sql
SELECT
COUNT(DISTINCT ws.website_session_id) Sessions, -- função de agregação para contagem das sessões
COUNT(DISTINCT o.order_id) Orders, -- função de agregação para contagem das vendas
COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id) Session_to_Order_Conv_Rate -- cálculo percentual da taxa de conversão
FROM
website_sessions ws -- escolha da tabela de sessões do website (alias ws)
LEFT JOIN
orders o USING (website_session_id) -- junção da tabela de compras (alias o) usando a coluna "website_session_id"
WHERE
ws.created_at < '2012-04-14'AND -- filtro de dados para data da análise período: início até 14/04/2012
ws.utm_source = 'gsearch' AND -- filtro de dados para a fonte gsearch
ws.utm_campaign = 'nonbrand' -- filtro de dados para a campanha nonbrand
Out[8]:
Os resultados mostram uma taxa de conversão de 2,88%. Este percentual está muito abaixo de nossa meta, logo, devemos diminuir as despesas dessa forma de tráfego pago e buscar outros meios de trazer Leads Qualificados.
Taxa de Conversão por Tipo de Dispositivo¶
Com base nos resultados das análises anteriores há a necessidade de investir os recursos em tráfego pago de forma mais inteligente. Desta forma, dando prosseguimento às análises em busca de oportunidades de melhoria, mensuramos a taxa de conversão de acordo com o dispositivo usado para o acesso, podendo ser Desktop ou Mobile.
In [9]:
%%sql
SELECT
ws.device_type Device, -- exibição do tipo de dispositivo
COUNT(DISTINCT ws.website_session_id) Sessions, -- função de agregação para contagem das sessões
COUNT(DISTINCT o.order_id) Orders, -- função de agregação para contagem das vendas
COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id) Session_to_Order_Conv_Rate -- cálculo percentual da taxa de conversão
FROM
website_sessions ws -- escolha da tabela de sessões do website (alias ws)
LEFT JOIN
orders o -- junção da tabela de compras (alias o) usando a coluna "website_session_id"
USING (website_session_id)
WHERE
ws.created_at < '2012-05-11' AND -- filtro de dados para data da análise período: início até 11/05/2012
ws.utm_source = 'gsearch' AND -- filtro de dados para a fonte gsearch
ws.utm_campaign = 'nonbrand' -- filtro de dados para a campanha nonbrand
GROUP BY
ws.device_type -- agrupamento dos dados por tipo de dispositivo
Out[9]:
In [10]:
#extração dos dados para um dataframe
output = %sql SELECT ws.device_type Device,COUNT(DISTINCT ws.website_session_id) Sessions,COUNT(DISTINCT o.order_id) Orders,COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id) Session_to_Order_Conv_Rate FROM website_sessions ws LEFT JOIN orders o USING (website_session_id) WHERE ws.created_at < '2012-05-11' AND ws.utm_source = 'gsearch' AND ws.utm_campaign = 'nonbrand' GROUP BY ws.device_type;
plataform_data = output.DataFrame()
#configuração e exibição do gráfico
sns.set_theme(context='notebook', font_scale=1.5, style='white', palette='colorblind', color_codes=True, rc={'figure.figsize':(10,3), 'axes.edgecolor':'dimgrey', 'axes.titlecolor':'dimgrey','axes.titlelocation':'left', 'axes.titlepad':'30' , 'xtick.color':'dimgrey','ytick.color':'dimgrey','text.color':'dimgrey', 'axes.labelcolor':'dimgrey', 'xaxis.labellocation': 'left', 'yaxis.labellocation': 'top'})
ax = sns.barplot(data=plataform_data, x='Session_to_Order_Conv_Rate', y='Device', color='b', orient="h")
plt.figtext(x=.015, y=1, s='Taxa de conversão por plataforma', fontsize='large')
ax.set_xlabel(None)
ax.set_ylabel(None)
ax.set_yticklabels(labels=['Desktop', 'Mobile'], fontsize='medium')
ax.tick_params(labelbottom=False)
ax.bar_label(ax.containers[0], labels=list(map("{:.2f}%".format, plataform_data['Session_to_Order_Conv_Rate']*100)), padding=3)
sns.despine(left=True, bottom=True);
É evidente que acessos Mobile tem baixa conversão em vendas. Nota-se uma oportunidade de otimizar gastos nas campanhas concentrando recursos aos usuários Desktop.
É também recomendável a investigação causal da baixa taxa de conversão em vendas de usuários de plataformas móveis.
Análise de Impacto das Mudanças¶
Foram realizadas mudanças recomendadas na campanha no dia 13/05/2012. Faremos, a seguir, a análise de impacto comparando números anteriores e posteriores à nova estratégia de tráfego pago.
In [11]:
%%sql
SELECT
MIN(DATE(ws.created_at)) AS Week_Start_At, -- exibição de início das semanas
SUM(CASE
WHEN ws.device_type = 'desktop' THEN 1
ELSE NULL
END) AS Desktop_Sessions, -- contagem de sessões originadas de dispositivos Desktop
SUM(CASE
WHEN ws.device_type = 'mobile' THEN 1
ELSE NULL
END) AS Mobile_Sessions -- contagem de sessões originadas de dispositivos Mobile
FROM
website_sessions ws -- escolha da tabela de sessões do website (alias ws)
WHERE
ws.created_at BETWEEN '2012-04-15' AND '2012-06-09' AND -- filtro de dados para data da análise período: 15/04/2012 até 09/06/2012
ws.utm_source = 'gsearch' AND -- filtro de dados para a fonte gsearch
ws.utm_campaign = 'nonbrand' -- filtro de dados para a campanha nonbrand
GROUP BY
YEAR(ws.created_at), WEEK(ws.created_at) -- agrupamento de dados por semana do ano
Out[11]:
In [12]:
#extração e tratamento dos dados para um dataframe
output = %sql SELECT MIN(DATE(ws.created_at)) AS Week_Start_At, ws.device_type, COUNT(ws.device_type) AS sessions FROM website_sessions ws WHERE ws.created_at BETWEEN '2012-04-15' AND '2012-06-09' AND ws.utm_source = 'gsearch' AND ws.utm_campaign = 'nonbrand' GROUP BY YEAR(ws.created_at), WEEK(ws.created_at), ws.device_type
plataform_timeseries = output.DataFrame()
plataform_timeseries['Week_Start_At'] = pd.to_datetime(plataform_timeseries['Week_Start_At']).dt.strftime('%d/%m')
#configuração e exibição do gráfico
sns.set_theme(context='notebook', font_scale=1.5, style='ticks', palette='colorblind', color_codes=True, rc={'figure.figsize':(14,7),'lines.linewidth': 3, 'axes.edgecolor':'dimgrey', 'axes.titlecolor':'dimgrey','axes.titlelocation':'left', 'axes.titlepad':'30' , 'xtick.color':'dimgrey','ytick.color':'dimgrey','text.color':'dimgrey', 'axes.labelcolor':'dimgrey', 'xaxis.labellocation': 'left', 'yaxis.labellocation': 'top'})
ax = sns.lineplot(data=plataform_timeseries, x='Week_Start_At', y='sessions', hue='device_type', palette=['C8', 'C7'])
plt.figtext(x=.035, y=1, s='Aumento das sessões', fontdict={'fontsize':'large'})
ax.set_xlabel('Período semanal', loc='left', labelpad=20)
ax.set_ylabel('Contagem de sessões', loc='top', labelpad=20)
ax.set_ylim(0,800)
ax.text(4, 800, 'Início da nova estratégia → ', ha='right', va='top')
ax.set_xticks(plataform_timeseries['Week_Start_At'])
ax.tick_params(axis='x', labelsize='medium')
ax.legend(['Desktop','Mobile'], loc='upper left')
ax.axvline(4, color='black', linestyle =':', linewidth=1.5)
sns.despine();
Os dados apontam crescimento de 52.26% nas sessões Desktop (média de 209 acessos) e diminuição de 27.83% nas Mobile (média de 68 acessos). Logo, notamos um aumento total médio de 141 acessos.
Bounce Rate¶
A análise da taxa de rejeição (bounce rate) nos ajuda a identificar a frequência que os visitantes abandonam o site após ver somente a primeira página.
In [13]:
%%sql
SELECT
COUNT(DISTINCT website_session_id) AS sessions, -- função de agregação para contagem das sessões
SUM(CASE
WHEN gped.n_pages_visited > 1 THEN 1
ELSE NULL
END) AS not_bouced, -- contagem de sessões não rejeitadas
SUM(CASE
WHEN gped.n_pages_visited = 1 THEN 1
ELSE NULL
END) AS bounced, -- contagem de sessões rejeitadas
SUM(CASE
WHEN gped.n_pages_visited = 1 THEN 1
ELSE NULL
END) / COUNT(DISTINCT website_session_id) AS bounce_percentage -- cálculo da taxa de rejeição
FROM
(SELECT
website_session_id, -- exibição do id da sessão
COUNT(website_pageview_id) n_pages_visited -- contagem de páginas visualizadas
FROM
website_pageviews -- escolha da tabela de visualizações de páginas
WHERE
created_at < '2012-06-14' -- filtro de dados para data da análise período: início até 2012/06/14
GROUP BY website_session_id) AS gped -- seleção da tabela produzida pela sub consulta (alias gped)
Out[13]:
Os dados mostram que 59% dos usuários abandonam o site após visitar a primeira página. Realizada esta etapa da análise podemos prosseguir e tentar identificar oportunidades de otimização.
Landing Pages¶
As páginas de destino (landing pages) é para onde seus usuários são direcionados depois de clicar no seu anúncio, ou seja, o primeiro contato do usuário com o site. A seguir, apontamos a frequência das páginas de destino do site.
In [14]:
%%sql
SELECT
wp.pageview_url, -- exibição do url da página
COUNT(DISTINCT fpage.website_session_id) AS sessions -- função de agregação para contagem das sessões
FROM
(SELECT
website_session_id, -- exibição do id da sessão
MIN(website_pageview_id) AS fpageid -- função de agregação para exibição do menor id de visualização de página
FROM
website_pageviews -- escolha da tabela website_pageviews
WHERE
created_at < '2012-06-14' -- filtro de dados para data da análise período: início até 14/06/2014
GROUP BY
website_session_id -- agrupamento por id da sessão
) AS fpage -- seleção da tabela produzida pela sub consulta (alias fpage)
LEFT JOIN
website_pageviews wp -- junção da tabela de visualizações de páginas (alias wp) usando as colunas da linha seguinte:
ON fpage.fpageid = wp.website_pageview_id
GROUP BY
wp.pageview_url -- agrupamento por url da página visitada
Out[14]:
Os resultados mostram que TODOS novos usuários veem primeiro a página /home antes de outras páginas do site.
Teste A/B de Landing Pages¶
Após detectar que a página /home era a única landing page do site, iniciamos a criação de uma nova versão da página. Quando foi concluída foi ao ar e teve parte dos novos acessos direcionados a ela. Assim, obtivemos dados da página antiga e nova durante o mesmo período de tempo, evitando a contaminação dos resultados causados por sazonalidades.
In [15]:
%%sql
WITH bounced_table AS( -- criação da CTE bounced_table
SELECT
ws.website_session_id,
wpv.pageview_url,
CASE WHEN COUNT(wpv.website_pageview_id) = 1 THEN 1 ELSE NULL END AS bounced
FROM website_sessions ws
LEFT JOIN website_pageviews wpv
USING (website_session_id)
WHERE
ws.created_at BETWEEN (
SELECT MIN(created_at)
FROM website_pageviews
WHERE pageview_url = '/lander-1') AND
'2012-07-28' AND
ws.utm_source = 'gsearch' AND
ws.utm_campaign = 'nonbrand'
GROUP BY ws.website_session_id)
SELECT
bounced_table.pageview_url, -- exibição da url da página visitada
COUNT(bounced_table.website_session_id) total_sessions, -- função de agregação de contagem das sessões
SUM(bounced_table.bounced) AS bounced_sessions, -- função de agregação de soma das sessões abandonadas
SUM(bounced_table.bounced) / COUNT(bounced_table.website_session_id) AS bounce_rate -- cálculo da taxa de abandono
FROM
bounced_table -- escolha da tabela produzida pela CTE bounced_table
GROUP BY
bounced_table.pageview_url -- agrupamento por url da página
Out[15]:
O projeto foi um grande sucesso e a nova página reduziu em 5,10% a desistência dos usuários.
Análise de Impacto por Semana¶
Afim de aprofundar o conhecimento sobre o impacto da nova página /home faremos análise semanal do período anterior e posterior ao projeto. Assim, asseguramos que os resultados positivos se mantiveram com o passar do tempo.
In [16]:
%%sql
WITH bounced_data AS( -- criação da CTE bounced_table
SELECT
WEEK(ws.created_at) 'week',
ws.created_at,
wpv.pageview_url,
CASE WHEN wpv.pageview_url = '/home' THEN 1 ELSE NULL END AS home_sessions,
CASE WHEN wpv.pageview_url = '/lander-1' THEN 1 ELSE NULL END AS lander_sessions,
CASE WHEN COUNT(wpv.website_pageview_id) = 1 THEN 1 ELSE NULL END AS bounce
FROM website_sessions ws
LEFT JOIN website_pageviews wpv
USING (website_session_id)
WHERE
ws.created_at BETWEEN '2012-06-01' AND '2012-08-31' AND
ws.utm_campaign = 'nonbrand' AND
ws.utm_source = 'gsearch'
GROUP BY ws.website_session_id)
SELECT
MIN(DATE(bounced_data.created_at)) AS week_start, -- função da agregação para exibição do início de cada semana
SUM(bounced_data.bounce) / COUNT(bounced_data.created_at) AS overall_bounced_rate, -- cálculo da taxa de abandono geral
SUM(bounced_data.home_sessions) AS home_sessions, -- soma de abandonos da antiga landing page
SUM(bounced_data.lander_sessions) AS lander_sessions -- soma de abandonos da nova landing page
FROM
bounced_data -- escolha da tabela produzida pela CTE bounced_table
GROUP BY bounced_data.week -- agrupamento por semana
ORDER BY week_start ASC -- ordenação ascendente por início da semana
Out[16]:
In [17]:
#extração e tratamento dos dados para um dataframe
output = %sql WITH bounced_data AS(SELECT WEEK(ws.created_at) 'week',ws.created_at,CASE WHEN COUNT(wpv.website_pageview_id) = 1 THEN 1 ELSE NULL END AS bounce FROM website_sessions ws LEFT JOIN website_pageviews wpv USING (website_session_id)WHERE ws.created_at BETWEEN '2012-06-01' AND '2012-08-31' AND ws.utm_campaign = 'nonbrand' AND ws.utm_source = 'gsearch'GROUP BY ws.website_session_id)SELECT MIN(DATE(bounced_data.created_at)) AS week_start,SUM(bounced_data.bounce) / COUNT(bounced_data.created_at) AS overall_bounced_rate FROM bounced_data GROUP BY bounced_data.week ORDER BY week_start ASC
bounce_rate = output.DataFrame()
bounce_rate['week_start'] = pd.to_datetime(bounce_rate['week_start']).dt.strftime('%d/%m')
bounce_rate['overall_bounced_rate']= bounce_rate['overall_bounced_rate'] * 100
#configuração e exibição do gráfico
sns.set_theme(context='notebook', font_scale=1.5, style='ticks', palette='colorblind', color_codes=True, rc={'figure.figsize':(14,7),'lines.linewidth': 3, 'axes.edgecolor':'dimgrey', 'axes.titlecolor':'dimgrey','axes.titlelocation':'left', 'axes.titlepad':'30' , 'xtick.color':'dimgrey','ytick.color':'dimgrey','text.color':'dimgrey', 'axes.labelcolor':'dimgrey', 'xaxis.labellocation': 'left', 'yaxis.labellocation': 'top'})
ax = sns.lineplot(data=bounce_rate, x='week_start', y='overall_bounced_rate', color='C5')
plt.figtext(x=.035, y=1, s='Redução da taxa de abandono', fontdict={'fontsize':'large'})
ax.set_xlabel('Período semanal', loc='left', labelpad=20)
ax.set_ylabel('Taxa de abandono', loc='top', labelpad=20)
ax.set_ylim(ymin=0,ymax=100)
ax.set_yticks(range(0,101,10), list(map("{}%".format, range(0,101,10))))
ax.tick_params(axis='x', labelsize=14)
ax.axvline(3, color='black', linestyle =':', linewidth=1.5)
ax.axvline(9, color='black', linestyle =':', linewidth=1.5)
ax.text(6, 62, 'Período dos testes', ha='center', va='bottom')
ax.axhline(bounce_rate['overall_bounced_rate'].max(),linestyle =':', linewidth=1, color='silver')
ax.axhline(bounce_rate['overall_bounced_rate'].min(),linestyle =':', linewidth=1, color='silver')
sns.despine()
Durante o período dos testes observamos uma diminuição na taxa de abandono da nova landing page em 3.9%.
Análise de Funil de Conversão¶
As análises de funil de conversão reúnem dados e métricas sobre cada uma das etapas que os clientes passam em sua jornada até a compra.
Criação da Tabela Temporária - One Hot Encoding
In [18]:
%%sql
CREATE TEMPORARY TABLE count_clicked
SELECT
funnel_flags.website_session_id,
MAX(funnel_flags.lander) AS clicked_lander,
MAX(funnel_flags.products) AS clicked_products,
MAX(funnel_flags.mr_fuzzy) AS clicked_mr_fuzzy,
MAX(funnel_flags.cart) AS clicked_cart,
MAX(funnel_flags.shipping) AS clicked_shipping,
MAX(funnel_flags.billing) AS clicked_billing,
MAX(funnel_flags.thank_you) AS clicked_thank_you
FROM (SELECT ws.website_session_id,
CASE WHEN wpv.pageview_url = '/lander-1' THEN 1 ELSE NULL END AS lander,
CASE WHEN wpv.pageview_url = '/products' THEN 1 ELSE NULL END AS products,
CASE WHEN wpv.pageview_url = '/the-original-mr-fuzzy' THEN 1 ELSE NULL END AS mr_fuzzy,
CASE WHEN wpv.pageview_url = '/cart' THEN 1 ELSE NULL END AS cart,
CASE WHEN wpv.pageview_url = '/shipping' THEN 1 ELSE NULL END AS shipping,
CASE WHEN wpv.pageview_url = '/billing' THEN 1 ELSE NULL END AS billing,
CASE WHEN wpv.pageview_url = '/thank-you-for-your-order' THEN 1 ELSE NULL END AS thank_you
FROM website_sessions AS ws
LEFT JOIN website_pageviews AS wpv
USING (website_session_id)
WHERE ws.created_at BETWEEN '2012-08-05' AND '2012-09-05' AND
ws.utm_source = 'gsearch' AND
ws.utm_campaign = 'nonbrand') AS funnel_flags
GROUP BY funnel_flags.website_session_id;
-- Pré visualização da tabela temporária count_clicked:
SELECT *
FROM count_clicked
LIMIT 10
Out[18]:
Taxa de Cliques¶
A seguir, analisaremos a proporção em que cada etapa das sessões foram acessadas e promoveram que o usuário prosseguisse no processo de compra.
In [19]:
%%sql
SELECT
COUNT(count_clicked.website_session_id) AS sessions, -- função de agregação de contagem das sessões
SUM(count_clicked.clicked_products) / SUM(count_clicked.clicked_lander) AS lander_success_rt, -- cálculo da taxa de prosseguimento da página lander
SUM(count_clicked.clicked_mr_fuzzy) / SUM(count_clicked.clicked_products) AS products_success_rt, -- cálculo da taxa de prosseguimento do catálogo de produtos
SUM(count_clicked.clicked_cart) / SUM(count_clicked.clicked_mr_fuzzy) AS mr_fuzzy_success_rt, -- cálculo da taxa de prosseguimento do produto Mr Fuzzy
SUM(count_clicked.clicked_shipping) / SUM(count_clicked.clicked_cart) AS cart_success_rt, -- cálculo da taxa de prosseguimento do carrinho de compras
SUM(count_clicked.clicked_billing) / SUM(count_clicked.clicked_shipping) AS shipping_success_rt, -- cálculo da taxa de prosseguimento da página de frete
SUM(count_clicked.clicked_thank_you) / SUM(count_clicked.clicked_billing) AS billing_success_rt -- cálculo da taxa de prosseguimento da página de pagamento
FROM count_clicked; -- escolha da tabelá temporária count_clicked
Out[19]:
In [20]:
#data setup
output = %sql SELECT SUM(count_clicked.clicked_products) / SUM(count_clicked.clicked_lander) AS 'Página inicial', SUM(count_clicked.clicked_mr_fuzzy) / SUM(count_clicked.clicked_products) AS 'Catálogo de produtos', SUM(count_clicked.clicked_cart) / SUM(count_clicked.clicked_mr_fuzzy) AS 'The Original Mr. Fuzzy', SUM(count_clicked.clicked_shipping) / SUM(count_clicked.clicked_cart) AS 'Carrinho de compras', SUM(count_clicked.clicked_billing) / SUM(count_clicked.clicked_shipping) AS 'Frete', SUM(count_clicked.clicked_thank_you) / SUM(count_clicked.clicked_billing) AS 'Pagamento' FROM count_clicked;
funnel_data = output.DataFrame().melt(var_name='Página', value_name='Taxa de sucesso')
#cálculo cumulativo de desistência dos usuários
funnel_prod_rt=[]
result=1
for x in funnel_data['Taxa de sucesso']:result=result*x;funnel_prod_rt.append(round(result*100,2))
#personalização e exibição do gráfico
fig=px.funnel(data_frame=funnel_data,y='Página',x=funnel_prod_rt,orientation='h',color_discrete_sequence=['#0173B2'],title='Funil de conversão')
fig.update_layout(xaxis=dict(showline=True,showgrid=False,showticklabels=True,linecolor='dimgrey',ticks='outside',tickfont=dict(family='sans-serif',size=20,color='dimgrey')),yaxis=dict(showgrid=False,zeroline=False,showline=False,linecolor='dimgrey',showticklabels=True,tickfont=dict(family='sans-serif',size=20,color='dimgrey')),plot_bgcolor='white',legend=dict(font=dict(family='sans-serif',size=16,color='dimgrey'),title=dict(text='')),font=dict(family='sans-serif',color='dimgrey'),title=dict(font=dict(size=26),pad=dict(l=-40)))
fig.update_yaxes(title=dict(text=''))
fig.update_traces(hoverlabel=dict(font=dict(size=14,color='black'),bgcolor='white'),hovertemplate='Taxa de conversão: %{x}%<br>Página: %{y}<extra></extra>',texttemplate='%{value}%',textfont=dict(family='sans-serif',size=14))
fig.show(renderer='notebook_connected')
Os resultados indicam que os pontos fracos da nossa jornada de compra são a página do produto Mr. Fuzzy e a de Pagamento.
Teste A/B da Página de Pagamento¶
Após descobrirmos a alta taxa de abandono da página responsável pela etapa de pagamento, iniciamos um projeto para a criação de uma nova página para esta etapa.
In [21]:
%%sql
SELECT
wpv.pageview_url, -- exibição do url das páginas
COUNT(DISTINCT ws.website_session_id) AS sessions, -- função de agregação para contagem de sessões
COUNT(DISTINCT o.order_id) AS orders, -- função de agregação para contagem de compras
COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id) AS billing_to_order_rt -- cálculo da taxa de compras por sessão
FROM
website_sessions AS ws -- escolha da tabela de sessões do website (alias ws)
LEFT JOIN
website_pageviews AS wpv USING (website_session_id) -- junção da tabela de visualização das páginas (alias wpv) usando a coluna website_session_id
LEFT JOIN
orders AS o USING (website_session_id) -- junção da tabela de compras (alias o) usando a coluna website_session_id
WHERE
ws.created_at BETWEEN (SELECT
MIN(created_at)
FROM
website_pageviews -- escolha da tabela de visualizações de páginas (alias wp)
WHERE
pageview_url = '/billing-2'
GROUP BY pageview_url) -- sub consulta que retorna a primeira data que a nova página de pagamento foi ao ar
AND '2012-11-10' -- filtro de dados para data da análise período: "data retornada pela subconsulta acima" até 10/11/2012
AND pageview_url IN ('/billing-2' , '/billing') -- filtro de daddos para urls da página /billing-2 e /billing
GROUP BY wpv.pageview_url -- agrupamento por url da página
Out[21]:
O projeto teve um grande impacto e mostra que a nova página aumentou a taxa de conversão em 16,97%.
Análise de Padrão de Negócio¶
As análises de padrão de negócio visam gerar conhecimentos que auxiliarão na maximização da eficiência e antecipação de tendências futuras.
Diário por Hora¶
Ao analisar as tendências diárias por hora podemos, por exemplo, otimizar o atendimento ao cliente alocando mais colaboradores para a tarefa durante certo período do dia.
In [22]:
%%sql
WITH b_pattern AS (
SELECT
DATE(ws.created_at) AS created_at,
WEEKDAY(ws.created_at) AS weekday,
HOUR(ws.created_at) AS hour,
COUNT(DISTINCT ws.website_session_id) AS sessions
FROM website_sessions AS ws
WHERE ws.created_at BETWEEN '2012-09-15' AND '2012-11-15'
GROUP BY
DATE(ws.created_at),
WEEKDAY(ws.created_at),
HOUR(ws.created_at)
)
SELECT
bp.hour, -- exibição da hora do dia
ROUND(AVG(CASE WHEN WEEKDAY(bp.created_at) = 0 THEN bp.sessions ELSE NULL END),1) AS 'Monday', -- funções para arredondamento da média de sessões da Segunda-Feira
ROUND(AVG(CASE WHEN WEEKDAY(bp.created_at) = 1 THEN bp.sessions ELSE NULL END),1) AS 'Tuesday', -- funções para arredondamento da média de sessões da Segunda-Feira
ROUND(AVG(CASE WHEN WEEKDAY(bp.created_at) = 2 THEN bp.sessions ELSE NULL END),1) AS 'Wednesday', -- funções para arredondamento da média de sessões da Quarta-Feira
ROUND(AVG(CASE WHEN WEEKDAY(bp.created_at) = 3 THEN bp.sessions ELSE NULL END),1) AS 'Thursday', -- funções para arredondamento da média de sessões da Quinta-Feira
ROUND(AVG(CASE WHEN WEEKDAY(bp.created_at) = 4 THEN bp.sessions ELSE NULL END),1) AS 'Friday', -- funções para arredondamento da média de sessões da Sexta-Feira
ROUND(AVG(CASE WHEN WEEKDAY(bp.created_at) = 5 THEN bp.sessions ELSE NULL END),1) AS 'Saturday', -- funções para arredondamento da média de sessões da Sábado
ROUND(AVG(CASE WHEN WEEKDAY(bp.created_at) = 6 THEN bp.sessions ELSE NULL END),1) AS 'Sunday' -- funções para arredondamento da média de sessões do Domingo
FROM b_pattern AS bp -- escolha da tabela produzida pela CTE b_pattern
GROUP BY
bp.hour -- agrupamento por hora do dia
ORDER BY bp.hour ASC -- ordenação ascendente por hora do dia
Out[22]:
In [23]:
#extração e tratamento dos dados para um dataframe
output = %sql WITH b_pattern AS ( SELECT DATE(ws.created_at) AS created_at, WEEKDAY(ws.created_at) AS weekday, HOUR(ws.created_at) AS hour, COUNT(DISTINCT ws.website_session_id) AS sessions FROM website_sessions AS ws WHERE ws.created_at BETWEEN '2012-09-15' AND '2012-11-15' GROUP BY DATE(ws.created_at), WEEKDAY(ws.created_at), HOUR(ws.created_at) ) SELECT bp.hour, ROUND(AVG(CASE WHEN WEEKDAY(bp.created_at) = 0 THEN bp.sessions ELSE NULL END),1) AS 'Monday', ROUND(AVG(CASE WHEN WEEKDAY(bp.created_at) = 1 THEN bp.sessions ELSE NULL END),1) AS 'Tuesday', ROUND(AVG(CASE WHEN WEEKDAY(bp.created_at) = 2 THEN bp.sessions ELSE NULL END),1) AS 'Wednesday', ROUND(AVG(CASE WHEN WEEKDAY(bp.created_at) = 3 THEN bp.sessions ELSE NULL END),1) AS 'Thursday', ROUND(AVG(CASE WHEN WEEKDAY(bp.created_at) = 4 THEN bp.sessions ELSE NULL END),1) AS 'Friday', ROUND(AVG(CASE WHEN WEEKDAY(bp.created_at) = 5 THEN bp.sessions ELSE NULL END),1) AS 'Saturday', ROUND(AVG(CASE WHEN WEEKDAY(bp.created_at) = 6 THEN bp.sessions ELSE NULL END),1) AS 'Sunday' FROM b_pattern AS bp GROUP BY bp.hour ORDER BY bp.hour ASC
hourly_data = output.DataFrame()
weekhours = np.array([])
weekhours = np.append(weekhours, hourly_data['Monday'])
weekhours = np.append(weekhours, hourly_data['Tuesday'])
weekhours = np.append(weekhours, hourly_data['Wednesday'])
weekhours = np.append(weekhours, hourly_data['Thursday'])
weekhours = np.append(weekhours, hourly_data['Friday'])
weekhours = np.append(weekhours, hourly_data['Saturday'])
weekhours = np.append(weekhours, hourly_data['Sunday'])
#configuração e exibição do gráfico
sns.set_theme(context='notebook', font_scale=1.5, style='ticks', palette='colorblind', color_codes=True, rc={'figure.figsize':(14,7),'lines.linewidth': 3, 'axes.edgecolor':'dimgrey', 'axes.titlecolor':'dimgrey','axes.titlelocation':'left', 'axes.titlepad':'30' , 'xtick.color':'dimgrey','ytick.color':'dimgrey','text.color':'dimgrey', 'axes.labelcolor':'dimgrey', 'xaxis.labellocation': 'left', 'yaxis.labellocation': 'top'})
ax = sns.lineplot(x=list(range(0,24*7)), y=weekhours)
plt.figtext(x=.06, y=.95, s='Número de sessões na semana', fontdict={'fontsize':'large'})
ax.set_xlabel('Dia da semana', loc='left', labelpad=20)
ax.set_ylabel('Sessões', loc='top', labelpad=20)
ax.set_ylim(ymin=0,ymax=30)
ax.set_xticks(range(0,168,24),labels=['Seg','Ter','Qua','Qui','Sex','Sáb','Dom'])
ax.set_xmargin(0.13)
ax.axhline(weekhours.mean(),linestyle =':', linewidth=1.5, color='black')
ax.text(0, weekhours.mean(), 'Média', ha='right', va='bottom')
ax.set_xmargin(0.13)
sns.despine()
In [24]:
#extração e tratamento dos dados para um dataframe
output = %sql WITH b_pattern AS ( SELECT DATE(ws.created_at) AS created_at, WEEKDAY(ws.created_at) AS weekday, HOUR(ws.created_at) AS hour, COUNT(DISTINCT ws.website_session_id) AS sessions FROM website_sessions AS ws WHERE ws.created_at BETWEEN '2012-09-15' AND '2012-11-15' GROUP BY DATE(ws.created_at), WEEKDAY(ws.created_at), HOUR(ws.created_at) ) SELECT bp.hour, bp.weekday, CASE WHEN bp.weekday = 0 THEN 'Segunda-Feira' WHEN bp.weekday = 1 THEN 'Terça-Feira' WHEN bp.weekday = 2 THEN 'Quarta-Feira' WHEN bp.weekday = 3 THEN 'Quinta-Feira' WHEN bp.weekday = 4 THEN 'Sexta-Feira' WHEN bp.weekday = 5 THEN 'Sábado' WHEN bp.weekday = 6 THEN 'Domingo' ELSE NULL END AS 'Dia da semana', ROUND(AVG (bp.sessions),1) AS 'Sessões' FROM b_pattern AS bp GROUP BY bp.hour, bp.weekday ORDER BY bp.hour ASC
hourly_data = output.DataFrame()
#configuração e exibição dos gráficos
sns.set_theme(context='notebook', font_scale=1.5, style='ticks', palette='colorblind', color_codes=True, rc={'lines.linewidth': 3, 'axes.edgecolor':'dimgrey', 'axes.titlecolor':'dimgrey','axes.titlelocation':'left', 'axes.titlepad':'30' , 'xtick.color':'dimgrey','ytick.color':'dimgrey','text.color':'dimgrey', 'axes.labelcolor':'dimgrey', 'xaxis.labellocation': 'left', 'yaxis.labellocation': 'top'})
fg = sns.relplot(data=hourly_data, x='hour', y='Sessões', row='Dia da semana', kind='line', height=6, aspect=2.2, color='b')
plt.figtext(x=0.02, y=1, s='Média diária de acessos por hora', color='dimgrey', fontdict={'fontsize':'large'})
fg.map(plt.xlabel, xlabel='Hora do dia', loc='left', color='dimgrey', labelpad=15)
fg.map(plt.axhline, y=weekhours.mean(), color='black', linestyle =':', linewidth=1.5)
fg.map(plt.text, x=0, y=weekhours.mean(), s='Média', ha='left', va='bottom', color='dimgrey')
fg.map(plt.xticks, ticks=range(0,24,1), labels=list(map("{}h".format, range(0,24,1))), color='dimgrey', fontsize=13)
fg.map(plt.tick_params, labelbottom=True, color='dimgrey')
Out[24]:
Nos dias úteis o número de acessos são maiores e intensificam no período da 6h às 16h, havendo declínio após esse horário. Os acessos têm auge nas quartas-feiras com pico registrado às 15h de 25 acessos.
Mensal¶
Ao analisar as tendências mensais do ano podemos, por exemplo:
- diminuir despesas e melhorar o desempenho das infraestruturas em nuvem conforme as demandas previstas;
- adequar os níveis de estoque de determinados produtos reduzindo prejuízos e gastos desnecessários.
In [25]:
%%sql
SELECT
YEAR(ws.created_at) AS year, -- função para exibição do ano
MONTH(ws.created_at) AS month, -- função para exibição do mês
COUNT(DISTINCT ws.website_session_id) AS sessions, -- função de agregação para contagem das sessões
COUNT(DISTINCT o.order_id) AS orders -- função de agregação para contagem das compras
FROM website_sessions AS ws -- escolha da tabela de sessões do website (alias ws)
LEFT JOIN orders AS o -- escolha da tabela de compras (alias o) usando a coluna website_session_id
USING(website_session_id)
WHERE YEAR(ws.created_at) = 2012 -- filtro de dados para o ano 2012
GROUP BY YEAR(ws.created_at), MONTH(ws.created_at) -- agrupamento por meses do ano
Out[25]:
In [26]:
#extração e tratamento dos dados para um dataframe
output = %sql SELECT MONTH(ws.created_at) AS month, COUNT(DISTINCT ws.website_session_id) AS 'Sessões', COUNT(DISTINCT o.order_id) AS 'Vendas' FROM website_sessions AS ws LEFT JOIN orders AS o USING(website_session_id) WHERE YEAR(ws.created_at) = 2012 GROUP BY YEAR(ws.created_at), MONTH(ws.created_at)
monthly_data = output.DataFrame()
monthly_data = monthly_data.melt(id_vars='month')
#configuração e exibição do gráfico
sns.set_theme(context='notebook', font_scale=1.5, style='ticks', palette='colorblind', color_codes=True, rc={'figure.figsize':(14,7),'lines.linewidth': 3, 'axes.edgecolor':'dimgrey', 'axes.titlecolor':'dimgrey','axes.titlelocation':'left', 'axes.titlepad':'30' , 'xtick.color':'dimgrey','ytick.color':'dimgrey','text.color':'dimgrey', 'axes.labelcolor':'dimgrey', 'xaxis.labellocation': 'left', 'yaxis.labellocation': 'top'})
ax = sns.lineplot(data=monthly_data, x='month', y='value', hue='variable')
plt.figtext(x=.015, y=.95, s='Crescimento no primeiro ano de atividade', fontdict={'fontsize':'large'})
ax.set_xlabel('2012', loc='left', labelpad=20)
ax.set_ylabel('Sessões', loc='top', labelpad=20)
ax.set_xticks(range(3,13,1),labels=['Mar','Abr','Mai','Jun','Jul','Ago','Set','Out','Nov','Dez'])
ax.set_xmargin(.05)
ax.legend(title=None)
sns.despine()
In [27]:
#Gráfico focado no eixo de vendas.
#configuração e exibição do gráfico
sns.set_theme(context='notebook', font_scale=1.6, style='ticks', palette='colorblind', color_codes=True, rc={'figure.figsize':(14,7),'lines.linewidth': 3, 'axes.edgecolor':'dimgrey', 'axes.titlecolor':'dimgrey','axes.titlelocation':'left', 'axes.titlepad':'30' , 'xtick.color':'dimgrey','ytick.color':'dimgrey','text.color':'dimgrey', 'axes.labelcolor':'dimgrey', 'xaxis.labellocation': 'left', 'yaxis.labellocation': 'top'})
ax = sns.lineplot(data=monthly_data[monthly_data['variable'] == 'Vendas'], x='month', y='value', color='C1')
plt.figtext(x=.035, y=.95, s='Análise detalhada das vendas', fontdict={'fontsize':'large'})
ax.set_xlabel('2012', loc='left', labelpad=20)
ax.set_ylabel('Vendas', loc='top', labelpad=20)
ax.set_xticks(range(3,13,1),labels=['Mar','Abr','Mai','Jun','Jul','Ago','Set','Out','Nov','Dez'])
ax.set_yticks(range(0,601,100))
ax.set_xmargin(.05)
sns.despine()
O primeiro ano de atividade foi um sucesso! Mês após mês, tivemos crescimento do número de acessos e vendas.
Apesar disso, devemos iniciar uma investigação para apurar as causas do declínio de acessos e vendas no mês de Novembro.
Análise de Produto e Finanças¶
Com o objetivo de mensurar a saúde financeira e impacto monetário dos projetos na empresa, analisaremos o seu primeiro ano de atividade. As informações serão particionadas por mês e apontarão:
num_sales- o número de vendas;total_rev- receita total;total_profit- lucro total.
In [28]:
%%sql
SELECT
YEAR(o.created_at) AS year, -- função para exibição do ano
MONTH(o.created_at) AS month, -- função para exibição do mês
COUNT(o.order_id) AS num_sales, -- função de agregação para contagem das compras
SUM(o.price_usd) AS total_rev, -- função de agregação para soma da receita de cada venda
SUM(o.price_usd - o.cogs_usd) AS total_profit -- função de agregação para soma do lucro de cada venda
FROM
orders AS o -- escolha da tabela de compras (alias o)
WHERE
o.created_at < '2013-01-01' -- filtro de dados para data da análise período: início até 01/01/2013
GROUP BY
YEAR(o.created_at) , MONTH(o.created_at) -- agrupamento por meses do ano
Out[28]:
In [29]:
#extração e tratamento dos dados para um dataframe
output = %sql SELECT YEAR(o.created_at) AS year, MONTH(o.created_at) AS month, SUM(o.price_usd) AS 'Receita', SUM(o.price_usd - o.cogs_usd) AS 'Lucro' FROM orders AS o WHERE o.created_at < '2013-01-01' GROUP BY YEAR(o.created_at), MONTH(o.created_at)
fin_data = output.DataFrame()
fin_data = fin_data.melt(id_vars='month', value_vars=['Receita', 'Lucro'])
#configuração e exibição do gráfico
sns.set_theme(context='notebook', font_scale=1.5, style='ticks', palette='colorblind', color_codes=True, rc={'figure.figsize':(14,7),'lines.linewidth': 3, 'axes.edgecolor':'dimgrey', 'axes.titlecolor':'dimgrey','axes.titlelocation':'left', 'axes.titlepad':'30' , 'xtick.color':'dimgrey','ytick.color':'dimgrey','text.color':'dimgrey', 'axes.labelcolor':'dimgrey', 'xaxis.labellocation': 'left', 'yaxis.labellocation': 'top'})
ax = sns.lineplot(data=fin_data, x='month', y='value', hue='variable', palette=['C4','C2'])
plt.figtext(x=.015, y=.95, s='Reflexo positivo das vendas nas finanças', fontdict={'fontsize':'large'})
ax.set_xlabel('2012', loc='left', labelpad=20)
ax.set_ylabel('Dólares', loc='top', labelpad=20)
ax.set_xticks(range(3,13,1),labels=['Mar','Abr','Mai','Jun','Jul','Ago','Set','Out','Nov','Dez'])
ax.set_xmargin(.05)
ax.legend(title=None)
sns.despine()
Em análises anteriores identificamos crescimento nas vendas. Nesta nova análise identificamos também aumento na receita e lucro durante maior parte do primeiro ano de atividade da empresa.
Venda Cruzada¶
Na data 25/09/2013 foi implementado a funcionalidade de venda cruzada no site para o aumento das vendas. Isso significa que agora o cliente recebe ofertas complementares durante o processo de compra.
A seguir, analisaremos o impacto financeiro dessa mudança.
In [30]:
%%sql
SELECT
CASE
WHEN DATE(wpv.created_at) BETWEEN '2013-08-25' AND '2013-09-24' THEN 'A. Pre_Cross_Sell'
WHEN DATE(wpv.created_at) BETWEEN '2013-09-25' AND '2013-10-24' THEN 'B. Post_Cross_Sell'
END AS time_period, -- divisão e exibição do período anterior e posterior a mudança
COUNT(DISTINCT CASE WHEN wpv.pageview_url = '/cart' THEN wpv.website_session_id ELSE NULL END) AS cart_sessions, -- função de agregação para contagem de carrinhos de compras
COUNT(DISTINCT CASE WHEN wpv.pageview_url = '/shipping' THEN wpv.website_session_id ELSE NULL END) / COUNT(DISTINCT CASE WHEN wpv.pageview_url = '/cart' THEN wpv.website_session_id ELSE NULL END) AS cart_ctr, -- cálculo da taxa de prosseguimento do carrinho de compras
COUNT(DISTINCT oi.order_item_id) / COUNT(DISTINCT o.order_id) AS products_per_order, -- cálculo da taxa de itens por compra
SUM(CASE WHEN wpv.pageview_url = '/cart' AND oi.is_primary_item = 1 THEN o.price_usd ELSE NULL END) / COUNT(DISTINCT o.order_id) AS avg_order_value, -- cálculo do preço médio dos produtos por compra
SUM(CASE WHEN wpv.pageview_url = '/cart' AND oi.is_primary_item = 1 THEN o.price_usd ELSE NULL END) / COUNT(DISTINCT CASE WHEN wpv.pageview_url = '/cart' THEN wpv.website_session_id ELSE NULL END) AS rev_per_cart_session -- cálculo do preço médio dos produtos por carrinho
FROM website_pageviews AS wpv -- escolha da tabela de visualizações de páginas (alias wpv)
LEFT JOIN orders AS o -- junção da tabela de compras (alias o) usando a coluna website_session_id
USING(website_session_id)
LEFT JOIN order_items AS oi -- junção da tabela de itens por compra (alias oi) usando a coluna order_id
USING(order_id)
WHERE
DATE(wpv.created_at) BETWEEN '2013-08-25' AND '2013-10-24' -- filtro de dados para data da análise período: 25/08/2013 até 24/10/2013
GROUP BY time_period -- agrupamento por período de tempo (pré ou pós mudança)
Out[30]:
A implementação de venda cruzada gerou por compra um aumento de 4,5% em produtos e US$2,84.
Ampliação do Portifólio de Produtos¶
Na data 12/12/2013 um novo produto foi inserido no portifólio. A seguir, analisaremos o impacto financeiro utilizando as seguintes métricas:
conv_rate- a taxa de conversão;AVG_order_valeu- média de preço por pedido;products_per_order- média de produtos por pedido;revenue_per_session- média da receita por sessão.
In [31]:
%%sql
SELECT
CASE WHEN ws.created_at < '2013-12-12' THEN 'A. Pre Birthday Bear' ELSE 'B. Post Birthday Bear' END AS time_period, -- função para agrupamento de dados por período de tempo
COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id) AS conv_rate, -- cálculo da taxa de conversão de sessões em vendas
AVG(o.price_usd) AS AVG_order_value, -- função de agregação para média de preço por compra
AVG(o.items_purchased) AS products_per_order, -- função de agregação para média do número de produtos por compra
SUM(o.price_usd) / COUNT(DISTINCT ws.website_session_id) AS revenue_per_session -- cálculo de receita por sessão
FROM website_sessions AS ws -- escolha da tabela de sessões do site (alias ws)
LEFT JOIN orders AS o -- junção da tabela de compras (alias o) usando a coluna website_session_id
USING(website_session_id)
WHERE ws.created_at BETWEEN '2013-11-12' AND '2014-01-12' -- filtro de dados para data da análise período: 12/11/2013 até 12/01/2014
GROUP BY time_period -- agrupamento por período de tempo
Out[31]:
Foi um sucesso! Os resultados mostram um aumento de todas as métricas analisadas, ou seja, estamos vendendo mais.
Análise de Devoluções¶
As devoluções podem ter grande impacto no fluxo de caixa da empresa. As causas podem ser, por exemplo, falta de bom controle de qualidade ou problemas na apresentação do produto como a falta de informações claras e imagens realistas.
Faremos a análise da taxa de devolução de cada um de nossos produtos.
In [32]:
%%sql
SELECT
YEAR(oi.created_at) AS year, -- função para exibição do ano
MONTH(oi.created_at) AS month, -- função para exibição do mês
SUM(CASE WHEN oi.product_id = 1 THEN 1 ELSE NULL END) AS p1_orders, -- funções para cálculo da contagem das compras do produto ID 1
SUM(CASE WHEN oi.product_id = 1 AND oir.order_item_refund_id IS NOT NULL THEN 1 ELSE NULL END) / SUM(CASE WHEN oi.product_id = 1 THEN 1 ELSE NULL END) AS p1_refund_rt, -- funções para cálculo da taxa de devolução das compras do produto ID 1
SUM(CASE WHEN oi.product_id = 2 THEN 1 ELSE NULL END) AS p2_orders, -- funções para cálculo da contagem das compras do produto ID 2
SUM(CASE WHEN oi.product_id = 2 AND oir.order_item_refund_id IS NOT NULL THEN 1 ELSE NULL END) / SUM(CASE WHEN oi.product_id = 2 THEN 1 ELSE NULL END) AS p2_refund_rt, -- funções para cálculo da taxa de devolução das compras do produto ID 2
SUM(CASE WHEN oi.product_id = 3 THEN 1 ELSE NULL END) AS p3_orders, -- funções para cálculo da contagem das compras do produto ID 3
SUM(CASE WHEN oi.product_id = 3 AND oir.order_item_refund_id IS NOT NULL THEN 1 ELSE NULL END) / SUM(CASE WHEN oi.product_id = 3 THEN 1 ELSE NULL END) AS p3_refund_rt, -- funções para cálculo da taxa de devolução das compras do produto ID 3
SUM(CASE WHEN oi.product_id = 4 THEN 1 ELSE NULL END) AS p4_orders, -- funções para cálculo da contagem das compras do produto ID 4
SUM(CASE WHEN oi.product_id = 4 AND oir.order_item_refund_id IS NOT NULL THEN 1 ELSE NULL END) / SUM(CASE WHEN oi.product_id = 4 THEN 1 ELSE NULL END) AS p4_refund_rt -- funções para cálculo da taxa de devolução das compras do produto ID 4
FROM order_items AS oi -- escolha da tabela de itens por compra (alias oi)
LEFT JOIN order_item_refunds AS oir -- junção da tabela de devolução de itens comprados (alias oir) usando a coluna order_item_id
USING (order_item_id)
WHERE oi.created_at <= '2015-02-28' -- filtro de dados para data da análise período: início até 28/02/2015 (última data com dados completos)
GROUP BY
YEAR(created_at), MONTH(created_at) -- agrupamento por meses do ano
Out[32]:
In [33]:
#extração e tratamento dos dados para um dataframe
output = %sql SELECT DATE(MIN(oi.created_at)) AS date, SUM(CASE WHEN oi.product_id = 1 AND oir.order_item_refund_id IS NOT NULL THEN 1 ELSE NULL END) / SUM(CASE WHEN oi.product_id = 1 THEN 1 ELSE NULL END) AS 'The Original Mr. Fuzzy', SUM(CASE WHEN oi.product_id = 2 AND oir.order_item_refund_id IS NOT NULL THEN 1 ELSE NULL END) / SUM(CASE WHEN oi.product_id = 2 THEN 1 ELSE NULL END) AS 'The Forever Love Bear', SUM(CASE WHEN oi.product_id = 3 AND oir.order_item_refund_id IS NOT NULL THEN 1 ELSE NULL END) / SUM(CASE WHEN oi.product_id = 3 THEN 1 ELSE NULL END) AS 'The Birthday Sugar Panda', SUM(CASE WHEN oi.product_id = 4 AND oir.order_item_refund_id IS NOT NULL THEN 1 ELSE NULL END) / SUM(CASE WHEN oi.product_id = 4 THEN 1 ELSE NULL END) AS 'The Hudson River Mini bear' FROM order_items AS oi LEFT JOIN order_item_refunds AS oir USING (order_item_id) GROUP BY YEAR(oi.created_at), MONTH(oi.created_at)
refund_data = output.DataFrame()
refund_data = refund_data.melt(id_vars='date', var_name='Produto', value_name='Taxa de devolução')
refund_data['Taxa de devolução'] = refund_data['Taxa de devolução']*100
refund_data['date'] = pd.to_datetime(refund_data['date']).dt.strftime('%m/%y')
month_lab = [ '', '', '', 'Jun-12', '', '', '', '', '', 'Dez-12', '', '', '', '', '', 'Jun-13', '', '', '', '', '', 'Dez-13', '', '', '', '', '', 'Jun-14', '', '', '', '', '', 'Dez-14', '', '', '']
#configuração e exibição dos gráficos
sns.set_theme(context='notebook', font_scale=1.5, style='ticks', palette='colorblind', color_codes=True, rc={'lines.linewidth': 3, 'axes.edgecolor':'dimgrey', 'axes.titlecolor':'dimgrey','axes.titlelocation':'left', 'axes.titlepad':'30' , 'xtick.color':'dimgrey','ytick.color':'dimgrey','text.color':'dimgrey', 'axes.labelcolor':'dimgrey', 'xaxis.labellocation': 'left', 'yaxis.labellocation': 'top'})
fg = sns.relplot(data=refund_data, x='date', y='Taxa de devolução', row='Produto', kind='line', height=6, aspect=2.2, color='C5')
plt.figtext(x=.020, y=1, s='Taxa de devolução por produto ao longo do tempo', color='dimgrey', fontdict={'fontsize':'large'})
fg.map(plt.xlabel, xlabel='Mês-Ano', loc='left', color='dimgrey', labelpad=15)
fg.map(plt.xticks, ticks=range(0,37), labels=month_lab, color='dimgrey')
fg.map(plt.yticks, ticks=range(0,15,2), labels=list(map("{}%".format, range(0,15,2))), color='dimgrey')
fg.map(plt.tick_params, axis='x', labelbottom=True, color='dimgrey', rotation=80)
fg.map(plt.axhline, y=refund_data['Taxa de devolução'].mean(), color='black', linestyle =':', linewidth=1.5)
fg.map(plt.text, x=0.5, y=refund_data['Taxa de devolução'].mean(), s='Média', ha='right', va='bottom', color='dimgrey', fontsize='small');
ALERTA!! Os produtos The Birthday Sugar Panda e The Original Mr. Fuzzy tem alta taxa de devolução se comparados com os outros produtos do portifólio. Sabendo disso, devemos identificar as causas.
História de Crescimento¶
A seguir, recordaremos os dados financeiros da empresa na íntegra, dispondo de sub-totais por mês e ano para melhor análise.
In [34]:
%%sql
SELECT
IF(GROUPING(YEAR(o.created_at)), 'All Years', YEAR(o.created_at)) AS year, -- lógica para exibição do ano e texto personalizado para super agregados
IF(GROUPING(MONTH(o.created_at)), 'All Months', MONTH(o.created_at)) AS month, -- lógica para exibição do mês e texto personalizado para super agregados
COUNT(o.order_id) AS num_sales, -- função de agregação para contagem das compras
SUM(o.price_usd) AS total_rev, -- função de agregação para soma da receita de cada venda
SUM(o.price_usd - o.cogs_usd) AS total_profit -- função de agregação para soma do lucro de cada venda
FROM
orders AS o -- escolha da tabela de compras (alias o)
WHERE o.created_at <= '2015-02-28' -- filtro de dados para data da análise período: início até 28/02/2015 (última data com dados completos)
GROUP BY
YEAR(o.created_at) , MONTH(o.created_at) WITH ROLLUP-- agrupamento por meses do ano
Out[34]:
Gráfico interativo¶
In [35]:
#extração e tratamento dos dados para um dataframe
output = %sql SELECT DATE(min(o.created_at)) AS 'Período', ROUND(SUM(o.price_usd)) AS 'Receita', ROUND(SUM(o.price_usd - o.cogs_usd)) AS 'Lucro' FROM orders AS o WHERE o.created_at <= '2015-02-28' GROUP BY YEAR(o.created_at) , MONTH(o.created_at)
fin_alltime = output.DataFrame()
fin_alltime['Período'] = pd.to_datetime(fin_alltime['Período']).dt.strftime('%b-%y').str.title()
fin_alltime = fin_alltime.melt(id_vars='Período', var_name='Medida', value_name='Dólares')
#configuração e exibição dos gráficos
fig=px.line(data_frame=fin_alltime,x='Período',y='Dólares',color='Medida',color_discrete_sequence=['#CC78BC','#029E73'],title='Evolução financeira',markers=False)
fig.update_layout(xaxis=dict(showline=True,showgrid=True,showticklabels=True,linecolor='dimgrey',ticks='outside',tickfont=dict(family='sans-serif',size=12,color='dimgrey')),yaxis=dict(showgrid=True,zeroline=False,showline=False,linecolor='dimgrey',showticklabels=True,tickfont=dict(family='sans-serif',size=12,color='dimgrey')),plot_bgcolor='whitesmoke',legend=dict(font=dict(family='sans-serif',size=16,color='dimgrey'),title=dict(text='')),font=dict(family='sans-serif',color='dimgrey'),title=dict(font=dict(size=26),pad=dict(l=-40)))
fig.update_xaxes(title=dict(font=dict(size=20)),rangeslider=dict(visible=True,bgcolor='whitesmoke'),tickangle=80)
fig.update_yaxes(title=dict(font=dict(size=20)))
fig.update_traces(line_width=3,hoverlabel=dict(font=dict(size=14,color='black'),bgcolor='white'),hovertemplate='Medida: Receita<br>Mês-Ano: %{x}<br>Dólares: %{y}<extra></extra>')
fig.show(renderer='notebook_connected')
Através da exploração dos dados, utilizando o gráfico interativo acima, nota-se que as finanças declinam no meses próximos à virada de ano. O últimos registros apresentam resultados alarmantes, o declínio se intensificou no início do ano corrente (2015).
Será necessário aprofundar as investigações para que se estabeleça um plano de ação.