Financial Data
Load Financial Product Stat
df_financial_product_stat <- read_csv("C:/Users/DELL/Documents/R/Projects/KPI DATA RAW/Financial-Product-Stats.csv")
## Rows: 80 Columns: 4
## -- Column specification --------------------------------------------------------
## Delimiter: ","
## chr (2): PERIOD, PRODUCT
## dbl (2): EST, ACT
##
## i Use `spec()` to retrieve the full column specification for this data.
## i Specify the column types or set `show_col_types = FALSE` to quiet this message.
#View(df_financial_product_stat)
df_fin_prod_stat <- rename_with(df_financial_product_stat, tolower)
##split period into year and quarter
df_fin_prod_stat <- separate(df_fin_prod_stat, period, into = c('year', 'quarter'), sep = ' ')
##filter by year 2016
df_fin_prod_stat_2016 <- df_fin_prod_stat %>%
filter(year == 2016) %>%
group_by(product)
##create a pivot table from columns 'est' and 'act'
df_fin_prod_2016_pivot <- pivot_longer(df_fin_prod_stat_2016, cols = c('est', 'act'), names_to = "variable", values_to = "value")
Plots for Finacial Products
Financial Region Stat
##load financial regional stat
df_financial_region_stat <- read_csv("C:/Users/DELL/Documents/R/Projects/KPI DATA RAW/Financial-Region-Stats.csv")
## Rows: 80 Columns: 4
## -- Column specification --------------------------------------------------------
## Delimiter: ","
## chr (2): PERIOD, REGION
## dbl (2): EST, ACT
##
## i Use `spec()` to retrieve the full column specification for this data.
## i Specify the column types or set `show_col_types = FALSE` to quiet this message.
#View(df_financial_region_stat)
df_fin_region_stat <- rename_with(df_financial_region_stat, tolower)
##split period into year and quarter
df_fin_region_stat <- separate(df_fin_region_stat, period, into = c('year', 'quarter'), sep = ' ')
df_fin_region_stat_2016 <- df_fin_region_stat %>%
filter(year == 2016) %>%
group_by(region)
Plot for finacial regional Stat
Financial Sales Rep Stat
##load financial sales rep stat
df_financial_salesrep_stat <- read_csv("C:/Users/DELL/Documents/R/Projects/KPI DATA RAW/Financial-Sales-Rep-Stats.csv")
## Rows: 80 Columns: 4
## -- Column specification --------------------------------------------------------
## Delimiter: ","
## chr (2): PERIOD, SALESREP
## dbl (2): EST, ACT
##
## i Use `spec()` to retrieve the full column specification for this data.
## i Specify the column types or set `show_col_types = FALSE` to quiet this message.
#View(df_financial_salesrep_stat)
df_fin_salesrep_stat <- rename_with(df_financial_salesrep_stat, tolower)
#remove space in column heading 'sales rep'
#df_fin_salesrep_stat <- rename(df_fin_salesrep_stat, "salesrep" = "sales rep")
##split period into year and quarter
df_fin_salesrep_stat <- separate(df_fin_salesrep_stat, period, into = c('year', 'quarter'), sep = ' ')
colnames(df_fin_salesrep_stat)
## [1] "year" "quarter" "est" "act" "salesrep"
df_fin_salesrep_stat_2016 <- df_fin_salesrep_stat %>%
filter(year == 2016) %>%
group_by(salesrep)
##create a pivot table from columns 'est' and 'act'
df_fin_salesrep_2016_pivot <- pivot_longer(df_fin_salesrep_stat_2016, cols = c('est', 'act'),
names_to = "variable", values_to = "value")
Plot of Financial Sales Rep
Retail Inventory
##load retail inventory data
df_retail_inventory <- read_csv("C:/Users/DELL/Documents/R/Projects/KPI DATA RAW/Retail-Inventory.csv")
## Rows: 8 Columns: 13
## -- Column specification --------------------------------------------------------
## Delimiter: ","
## chr (1): PRODUCT NAME
## dbl (12): JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC
##
## i Use `spec()` to retrieve the full column specification for this data.
## i Specify the column types or set `show_col_types = FALSE` to quiet this message.
#View(df_retail_inventory)
df_retail_inventory <- rename_with(df_retail_inventory, tolower)
#df_retail_inventory <- rename(df_retail_inventory, "product" = "productname")
colnames(df_retail_inventory)
## [1] "product name" "jan" "feb" "mar" "apr"
## [6] "may" "jun" "jul" "aug" "sep"
## [11] "oct" "nov" "dec"
##pivot the inventory data
df_retail_inv_pivot <- pivot_longer(df_retail_inventory, cols = c('jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov', 'dec'), names_to = "month", values_to = "value")
##make month column ordered by date
df_retail_inv_pivot <- df_retail_inv_pivot %>%
mutate(month = factor(month, levels = c('jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov', 'dec')))
df_retail_inv_pivot <- rename(df_retail_inv_pivot, "productname" = "product name")
Retail Inventory Plots
Retail Win/Loss
##load retail win-loss data
df_retail_win_loss <- read_csv("C:/Users/DELL/Documents/R/Projects/KPI DATA RAW/Retail-Win-Loss.csv")
## Rows: 2 Columns: 10
## -- Column specification --------------------------------------------------------
## Delimiter: ","
## chr (1): WIN OR LOSS
## dbl (9): ITEM 1, ITEM 2, ITEM 3, ITEM 4, ITEM 5, ITEM 6, ITEM 7, ITEM 8, ALL
##
## i Use `spec()` to retrieve the full column specification for this data.
## i Specify the column types or set `show_col_types = FALSE` to quiet this message.
#View(df_retail_win_loss)
df_retail_win_loss <- rename_with(df_retail_win_loss, tolower)
df_retail_win_loss <- rename(df_retail_win_loss, "win_loss" = "win or loss")
colnames(df_retail_win_loss)
## [1] "win_loss" "item 1" "item 2" "item 3" "item 4" "item 5"
## [7] "item 6" "item 7" "item 8" "all"
##pivot items
df_retail_winloss_pivot <- pivot_longer(df_retail_win_loss, cols = c('item 1', 'item 2', 'item 3', 'item 4', 'item 5', 'item 6', 'item 7', 'item 8'), names_to = "item", values_to = "percent")
#df_retail_winloss_pivot <- mutate()
Retail Win/Loss Plot
Retail Delivery Scope
#load delivery scope data
df_retail_delscope <- read_csv("C:/Users/DELL/Documents/R/Projects/KPI DATA RAW/Retail-Delivery-Scope.csv")
## Rows: 2 Columns: 3
## -- Column specification --------------------------------------------------------
## Delimiter: ","
## chr (1): SCOPE
## dbl (2): NEW, EXISTING
##
## i Use `spec()` to retrieve the full column specification for this data.
## i Specify the column types or set `show_col_types = FALSE` to quiet this message.
#View(df_retail_delscope)
df_retail_delscope <- rename_with(df_retail_delscope, tolower)
df_retail_delscope_pivot <- pivot_longer(df_retail_delscope, cols = c('new', 'existing'), names_to = "category", values_to = "value")
Plot of Retail Delivery Scope
ggplot(df_retail_delscope_pivot, aes(x= category, y = value, fill = scope)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Delivery Scope") +
coord_flip()
Retail Sale By Quarter
#load retail sale by quarter data
df_retail_quarter_sale <- read_csv("C:/Users/DELL/Documents/R/Projects/KPI DATA RAW/Retail-Sale-By-Quarter.csv")
## Rows: 4 Columns: 2
## -- Column specification --------------------------------------------------------
## Delimiter: ","
## chr (1): quarter
## dbl (1): sale
##
## i Use `spec()` to retrieve the full column specification for this data.
## i Specify the column types or set `show_col_types = FALSE` to quiet this message.
#View(df_retail_quarter_sale)
##Plot of Retail Sale By Quarter
ggplot(df_retail_quarter_sale, aes(x= quarter, y = sale)) +
geom_bar(stat = "identity", fill = "steelblue") +
labs(title = "Total Units Sold By Quarter") +
geom_text(aes(label = sale), hjust=1.5, color="white", size=3.5) +
coord_flip()+
scale_y_continuous(labels = comma)
Retail Unit Sold By Sales Rep
##load retail unit sold by rep data
df_retail_rep_sale <- read_csv("C:/Users/DELL/Documents/R/Projects/KPI DATA RAW/Retail-Units-Sold-By-Rep.csv")
## Rows: 8 Columns: 5
## -- Column specification --------------------------------------------------------
## Delimiter: ","
## chr (1): SALES REP
## dbl (4): Q1, Q2, Q3, Q4
##
## i Use `spec()` to retrieve the full column specification for this data.
## i Specify the column types or set `show_col_types = FALSE` to quiet this message.
#View(df_retail_rep_sale)
#df_retail_rep_sale <- rename_with(df_retail_rep_sale, tolower)
df_retail_rep_sale <- rename(df_retail_rep_sale, "salesrep" = "SALES REP")
df_retail_repsale_pivot <- pivot_longer(df_retail_rep_sale, cols = c('Q1', 'Q2', 'Q3', 'Q4'), names_to = "quarter", values_to = "sale")
#df_retail_repsale_pivot <- rename_with(df_retail_repsale_pivot, tolower)
Plot of Retail Units Sold By SalesRep
Marketing Stat
Marketing Lead Source Data
##load marketing lead source data
df_market_leadsource <- read_csv("C:/Users/DELL/Documents/R/Projects/KPI DATA RAW/Marketing-Lead-Source.csv")
## Rows: 30 Columns: 7
## -- Column specification --------------------------------------------------------
## Delimiter: ","
## dbl (7): DATE, PLUS, OPP, SALE ACCEPTED, ACTIONABLE LEAD, CAPTURED LEAD, WEB...
##
## i Use `spec()` to retrieve the full column specification for this data.
## i Specify the column types or set `show_col_types = FALSE` to quiet this message.
df_market_leadsource <- rename_with(df_market_leadsource, tolower)
#View(df_market_leadsource)
df_market_leadsource_pivot <- pivot_longer(df_market_leadsource, cols = c('plus', 'opp', 'sale accepted', 'actionable lead', 'captured lead', 'web visit'), names_to = "leadsource", values_to = "value")
Plot of Marketing Lead Source
Marketing Traffic Source By Month
##load traffic source by month data
df_market_leads_month <- read_csv("C:/Users/DELL/Documents/R/Projects/KPI DATA RAW/Marketing-Lead-Source-By-Month.csv")
## Rows: 3 Columns: 13
## -- Column specification --------------------------------------------------------
## Delimiter: ","
## chr (1): SOURCE
## dbl (12): JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC
##
## i Use `spec()` to retrieve the full column specification for this data.
## i Specify the column types or set `show_col_types = FALSE` to quiet this message.
#View(df_market_leads_month)
df_market_leadsmon_pivot <- pivot_longer(df_market_leads_month, cols = c('JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC'), names_to = "month", values_to = "value")
df_market_leadsmon_pivot <- rename_with(df_market_leadsmon_pivot, tolower)
##convert month to factor so its ordered by month
df_market_leadsmon_pivot <- df_market_leadsmon_pivot %>%
mutate(month = factor(month, levels = c('JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC')))
View(df_market_leadsmon_pivot)
Plot of Traffic Source By Month
##plot chart of traffic source by month
ggplot(df_market_leadsmon_pivot, aes(x = month, y = value, fill = source)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Traffic Source By Month")+
scale_y_continuous(labels = comma)
Marketing Leads In A 30-Day Month
##load marketing leads by date data
df_lead_day <- read_csv("C:/Users/DELL/Documents/R/Projects/KPI DATA RAW/Marketing-Leads-By-Day.csv")
## Rows: 30 Columns: 2
## -- Column specification --------------------------------------------------------
## Delimiter: ","
## dbl (2): Day, Leads
##
## i Use `spec()` to retrieve the full column specification for this data.
## i Specify the column types or set `show_col_types = FALSE` to quiet this message.
View(df_lead_day)
##plot of marketing leads by date
ggplot(df_lead_day, aes(x = Day, y = Leads)) +
geom_point(size = 3, color = "steelblue") +
geom_line(linetype = "dashed", color = "steelblue") +
geom_hline(yintercept = 3300, linetype = "dotted", color = "steelblue", alpha = 0.6) +
labs(title = "Leads Per Day")
Sales Data
##load sales data
df_sales <- read_csv("C:/Users/DELL/Documents/R/Projects/KPI DATA RAW/Sales-Data.csv")
## Rows: 3456 Columns: 6
## -- Column specification --------------------------------------------------------
## Delimiter: ","
## chr (5): Region, Product, Outlet, Sales Person, Date
## dbl (1): Sales
##
## i Use `spec()` to retrieve the full column specification for this data.
## i Specify the column types or set `show_col_types = FALSE` to quiet this message.
View(df_sales)
df_sales_new <- read_csv("C:/Users/DELL/Documents/R/Projects/KPI DATA RAW/Sales-Data-New.csv")
## Rows: 3456 Columns: 6
## -- Column specification --------------------------------------------------------
## Delimiter: ","
## chr (5): Location, Product, Payment, Sales Person, Date
## dbl (1): Sales
##
## i Use `spec()` to retrieve the full column specification for this data.
## i Specify the column types or set `show_col_types = FALSE` to quiet this message.
View(df_sales_new)
df_sales_new <- rename(df_sales_new, "SalesPerson" = "Sales Person")
##convert column 'Date' to Date format
df_sales$Date <- as.Date(df_sales$Date, "%m/%d/%Y")
df_sales_new$Date <- as.Date(df_sales_new$Date, "%m/%d/%Y")
library(lubridate)
##
## Attaching package: 'lubridate'
## The following objects are masked from 'package:base':
##
## date, intersect, setdiff, union
df_sales$Month <- month(ymd(df_sales$Date))
df_sales$Year <- year(ymd(df_sales$Date))
df_sales_new$Month <- month(ymd(df_sales_new$Date))
df_sales_new$Year <- year(ymd(df_sales_new$Date))
KPI Data
##load kpi data
df_kpi <- read_csv("C:/Users/DELL/Documents/R/Projects/KPI DATA RAW/KPI-Data.csv")
## Rows: 10 Columns: 11
## -- Column specification --------------------------------------------------------
## Delimiter: ","
## chr (1): NAME
## dbl (10): BUDGET GOAL, BUDGET ACTUAL, BUDGET REMAINDER, NET EXP ADDITIONAL, ...
##
## i Use `spec()` to retrieve the full column specification for this data.
## i Specify the column types or set `show_col_types = FALSE` to quiet this message.
View(df_kpi)
df_kpi <- rename_with(df_kpi, tolower)
df_kpi <- rename(df_kpi, "budgetgoal" = "budget goal", "budgetactual" = "budget actual")
colnames(df_kpi)
## [1] "name" "budgetgoal" "budgetactual"
## [4] "budget remainder" "net exp additional" "net exp total"
## [7] "revenue goal" "revenue actual" "revenue remainder"
## [10] "profit margingross" "profit margin net"
##rename column names
df_kpi <- rename(df_kpi, "budgetremainder" = "budget remainder", "netexp_additional" = "net exp additional",
"netexp_total" = "net exp total", "revenuegoal" = "revenue goal",
"revenueactual" = "revenue actual", "revenualremainder" = "revenue remainder",
"profitmargin_gross" = "profit margingross", "profitmargin_net" = "profit margin net")
##pivot budget values
df_kpi_budget <- pivot_longer(df_kpi, cols = c("budgetgoal", "budgetactual"),
names_to = "budget", values_to = "budgetvalue")
##pivot revenue values
df_kpi_revenue <- pivot_longer(df_kpi, cols = c("revenuegoal", "revenueactual"),
names_to = "revenue", values_to = "revenuevalue")
##pivot profit margin
df_kpi_profitmargin <- pivot_longer(df_kpi, cols = c("profitmargin_gross", "profitmargin_net"),
names_to = "profitmargin", values_to = "profitvalue")
KPI Debt/Equity
##load kpi debt to equity data
df_kpi_debt_equity <- read_csv("C:/Users/DELL/Documents/R/Projects/KPI DATA RAW/KPI-Debt-To-Equity-Ratio.csv")
## Rows: 10 Columns: 3
## -- Column specification --------------------------------------------------------
## Delimiter: ","
## dbl (3): CALENDAR, DEBT, EQUITY
##
## i Use `spec()` to retrieve the full column specification for this data.
## i Specify the column types or set `show_col_types = FALSE` to quiet this message.
View(df_kpi_debt_equity)
##pivot debt_equity table
df_kpi_debt_equity_pivot <- pivot_longer(df_kpi_debt_equity, cols = c("DEBT", "EQUITY"),
names_to = "debt_equity", values_to = "value")
df_kpi_debt_equity_pivot <- rename_with(df_kpi_debt_equity_pivot, tolower)
View(df_kpi_debt_equity_pivot)
Plot of KPI Debt/Equity Ratio
