$3.2\mathrm{.}1\mathrm{.}1$

Understanding Trends

The following is a findings summary of elicitation conducted by Analyst Billy Bob; interview conducted with project stakeholders Sherina Amarat, Director of Marketing and Ashley Prover, VP of Sales & Marketing.

Sherina Amarat: $$Through running a promotional giveaway of our goods to local restaurants we hope to increase exposure for CBR coffee and raise brand awareness.$$

Ashley Prover: $$Each restaurant that has agreed to participate will be receiving enough coffee grinds to make $1,500$ cups per restaurant at a sunk cost of $0\mathrm{.}07$ cents per cup. The goal is to demonstrate to these restaurants how popular our coffee is in order to entice them to switch to CBR brand coffee. We have put forth this promotion with the challenge that any restaurant carrying $1,500$ units of our coffee will run out$-$of$-$ stock before the end of the month to prove our products superior taste and market demand.$$

$*$You are permitted to incorporate new columns for calculated data. You may not remove rows of data, you can implement only filter to remove unnecessary data on the map level.$*$

Business Objective:

$1)$ To maximize the likelihood of success for the promotion, the Project Stakeholders will require a visual comparison that illustrates which neighborhood $[$referenced as $$city$$ in location data for excel $3$DMaps$]$ would be the optimal area to generate brand awareness. The optimal area will be decided based on the total monthly number of all customers in a particular neighborhood among only the participating restaurants.

$2)$ Using the neighborhood identified in the previous question, create a new visual $($or visual layer$)$ which shows the three $(3)$ postal codes $[$zip codes$]$ that would be the most optimal choices to execute our promotion. $[$Postal Code Segmentation Analysis$]$

$3)$ Calculate total sunk cost for the promotional giveaway campaign based on all participating restaurants identified in business objective #$2.[$Feasibility Study$]$

$4)$ Identify all ComBoard areas that would meet promotional time frame constraints, $1,500$ cups of coffee sold within $30$ days. Show the ComBoard and cups of coffee projected to be sold, only include results that are in excess of $1,499.$

Project Plan Summary:

Stage $1$: Data

Status: Complete. Data set $$NYC $-$ Restaurant Location Data Table.xlsx$$

Hire an analytics firm to collect market data for the target cities. Required data includes the following items;

$[$BusinessID$]$; Unique registered business number

$[$ResName$]$; Legally registered name of a restaurant

$[$Neighborhood$]$; Governmentally defined geographic areas

$[$BuildingNum$]$; Street number

$[$StreetName$]$; Registered street address for restaurant

$[$ZipCode$]$; Government set unique identifier for geographic area

$[$PhoneNum$]$; Registered phone number

$[$Cuisine$]$; Primary type of food type$($s$)$ sold at location

$[$Participating$]$; Agreed to participate in promotional partnership

$[$FitScore$]$; Metric based on demographic fit of clientele linked to a specific community board $($sub$-$area of neighborhood$)$ cross$-$referenced by restaurant

$[$DailyCustAvg$]$; The average number of customers whom patronize a restaurant on a daily basis

$[$Latitude$]$; X$-$axis coordinates on globe

$[$Longitude$]$; Y$-$axis coordinates on globe

$[$ComBoard$]$; Community identifier, breakdown for areas within neighborhoods

$[$CouncilDist$]$; Political council distrcting

$234235$

The analytics firm had contacted every restaurant and conducted a short informal survey to determine the appropriateness of each restaurant for our product$($s$).$ This elicitation was based on data and parameters that we have provided to them to assist in narrowing down potential restaurants.

As part of the analytics firm$$s work, a fitness score $[$fitscore$]$ was calculated and included on the provided dataset. The fitness score identifies the percentage of individuals in a given area would fall within the target market parameters, ie$,.$ ideal demographic.

Stage $2$: $[$Postal Code Segmentation Analysis$]$ Status: Not Started

Address Business Objectives $1,2.$

Stage $3$: Feasibility Study Status: Not Started

Address Business Objectives $3,4.$

The following formula is utilized for forecasting the number of cups sold within a one$-$month $(30$ day$)$ span for a restaurant.

Probability of Purchase $=3\mathrm{.}5$ Step $1$:

For each restaurant in a ComBoard:

Per restaurant monthly sales $=((($FitScore$/100)*$DailyCustAverage$)/$ Probabilty of purchase$)*30$ days$)$

Step $2$:

Average the monthly sales of all restaurants within the three postal codes $[$zipcodes$]$ grouped by ComBoard.

Step $3$:

Create a table showing the results of all targetted ComBoards, identify which one would be the optimal choice for the campaign.

$*$Probability of purchase was calculated based on $28\mathrm{.}57\%$ of target demographics will opt to purchase a coffee from CBR$.$ A factor of $3\mathrm{.}5.$