<aside>
💻
Project: Lewis County Business Advertising Insights (ReferenceUSA Data)
Tools: Excel (data cleaning, pivot tables), Tableau (interactive dashboards) | Charts at bottom of page.
This page is part of an ongoing effort to practice data storytelling and explore how public data can support local decision-making.
</aside>
Summary:
I created this project to document my analysis of the local business landscape in Lewis County, NY. Using public data from ReferenceUSA, I reviewed 735 businesses to identify market trends, industry leaders, and advertising activity within the area. Used Excel and Tableau to clean and transform the dataset, create visual summaries, and uncover patterns across several focus areas. These included top-performing companies by sales, industry revenue distribution, average advertising spend by sector, and overall spend by company.
Insights:
This project helped me gain a clearer understanding of the local economic environment and allowed me to share insights with my community through Facebook. Should I decide to offer independent insight work in the future, this serves as both a proof of concept and a strong starting point.
What I Did:
- Used ReferenceUSA to identify businesses in Lewis County, NY, who on file have over $15,000 in sales.
- Disclaimer: the data offered by ReferenceUSA did not have any data toward profits, COG, or other worthy considerations.
The limited data draws a limited picture, thought still useful.
- Notable data toward a companies financial situation was limited to reported localized sales and varied expense ranges.
- Downloaded filed as CSV, converted to Excel file.
- Cleaned and formatted a dataset of 735 Lewis County businesses from ReferenceUSA in Excel.
- Names - Flash Fill used to combine first and last
- Blanks - Identified and removed blank rows and columns
- Dates - incorrectly transferred as ‘yyyymm’, used Right(x,2)&”/”&LEFT(x,4) to clean dates column to a ‘mm/yyyy/’ format
- Needed to add day, introduced a “1/” into the data - using a helper column and pasting the corrected data as values into the original column.
- Expenses - Converted expense ranges into median point estimates for analysis.
- Originally listed as ranges - “x to y”, “y to z”, “Over z”, “Less than a”, etc.
- Could have used ifs with left and right but due to the varied format, decided 20 find and replaces was faster than IFS function with variable left and right character numbers. Could have also used xlookup, however with a range of 10+ columns, F&R seemed faster. Open to suggestions!
- Number - Once all data was formatted and cleaned, formatted fields to correct number type
- Built PivotTables and Charts in Excel to quickly identify top-sales companies, average overall expenses by industry, average advertising/overall expenses by industry and company, and more.
- Used various functions within PivotTable Analyzer to adjust design, add secondary axis, adjust field value settings, adjust field number format, create calculated expenses, and more.
- Exported dataset to Tableau
- Imported xlsx dataset to Tableau Public Desktop Version.
- Manipulated dataset to ensure compatibility
- Date settings were in English (UK) format - changed to US to fit dd/mm/yyyy format
- A number of fields needed to be converted to measures/dimensions and changed from discrete/continuous
- Created a Treemap with filtered zip codes, filtering out distant towns/cities from the county based on my knowledge of my target audience (facebook friends in the area)
- Filtered out companies whose sales were outliers in range (ex. Farmers Co-op in Lowville with ~56M in sales) & whose industry was government (Fire Departments)
- Used tool tip to have useful information from fields: Last updated on, SIC and NAICS description, and more.
- Created a Bar Chart to see companies, grouped by city/town, with sales between $0-$10M, and were located in cities/towns of interest.
- Filtered out companies whose sales were outliers in range (ex. Farmers Co-op in Lowville with ~56M in sales) & whose industry was government (Fire Departments)
- Used tool tip to have useful information from fields: Last updated on, SIC and NAICS description, and more.
- Designed and published interactive Tableau dashboards for public exploration on Facebook.
Key Insights:
- Identified local businesses and SIC industries/descriptions with advertising spend exceeding 5% of overall expenses - notably R&H Creations, Roggie’s Flooring Center, and Riverside Motors.