This Excel project is focused on analyzing revenue data for a hypothetical retail company called Hyperion. The main objective of this project is to identify trends, patterns, and insights that can assist Hyperion in making informed business decisions. The data includes information about customer demographics, such as customer ID, gender, marital status, and yearly income, as well as revenue-related data such as revenue, order date, country, birth date, first name, and last name. Through the use of data modeling, cleaning, transformation, and visualization techniques, this project aims to extract valuable insights from the data set to support decision-making.
Disclamer : The data set used in this project is a dummy data set and does not represent any real company or institution.
The data used in this project is in a tabular format and contains 20 columns and 17,422 rows. The data was sourced from a hypothetical retail company "Hyperion" and includes customer information, demographics, order dates, countries, and revenue. The revenue data ranges from a minimum of £2.53 to a maximum of £5,398.35, with an average revenue of £1,249.26. The total revenue for the dataset is £21,764,628.64. The data is a dummy dataset and does not represent any real company or institution.
The initial dataset contained some inconsistencies and incomplete data that needed to be cleaned and transformed to make it suitable for analysis. The following steps were taken:
-
Merging customer's first and last name: To merge the customer's first and last name into one column, the "CONCATENATE" function was used. This function takes two or more values and combines them into one. In this case, the "LOWER" function was also used to convert the text to lowercase. The formula used was: =LOWER(CONCATENATE(N2," ",O2))
-
Creating new date columns: To extract the year from the "Order Date" column, the "LEFT" function was used. This function returns a specified number of characters from the beginning of a text string. The formula used was: =LEFT(H3,4) To extract the year and month from the "Order Date" column, a similar approach was used but with a different number of characters specified. The formula used was: =LEFT(H2,7)
-
Deriving weekday: To extract the weekday from the "Order Date" column, the "TEXT" function was used. This function takes a date or time value and converts it to a text string in a specified format. The formula used was: =TEXT(H2,"dddd")
-
Deriving customer age: To calculate the customer's age based on their birth date and the order date, the "DATEDIF" function was used. This function calculates the difference between two dates in years, months, or days. The formula used was: =DATEDIF([@[Birth Date]],[@OrderDate],"Y")
-
Deriving customer age band: To group customers into age bands based on their age, an "IF" statement was used. This statement checks if a certain condition is true or false and returns a value based on the result. The "IF" statement was nested to create multiple conditions in this case. The formula used was: =IF(M2<=19,"Teenager",IF(M2<=34,"Young Adult",IF(M2<=49,"Adult",IF(M2<=64,"Seniors","Elderly"))))
-
Deriving customer value: To categorize customers based on their spending, an "IF" statement was used. This statement checks if a certain condition is true or false and returns a value based on the result. The "IF" statement was nested to create multiple conditions in this case. The formula used was: =IF(E2<1500,"Casual Seekers",IF(E2<2500,"Brand Seekers","Convinced Seekers")) These formulas were used to clean and transform the original data to create new columns with more useful information.
Based on the analysis of the revenue data for Hyperion using various pivot tables and charts, several insights were derived regarding the revenue generated by the company. It was found that the highest revenue came from the Adult and Senior age bands, accounting for 46.7% and 35.87% respectively. The country that generated the highest revenue was Australia with 32.60%, followed by South West with 16.42%. When analyzing revenue by gender and country, it was found that 16.50% of the total revenue came from males in Australia.
Furthermore, the year 2013 accounted for the highest revenue with 11.43%, and the income bracket of £50,001-£75,000 generated the highest revenue with 11.62% in that year. Convinced seekers accounted for 23.28% of total revenue in 2013, and were the highest revenue-generating customer value category. When analyzing revenue by marital status and gender, it was found that single and adult customers accounted for 25.28% of total revenue.
In terms of customer value and country, convinced seekers in Australia generated 24.87% of total revenue. Males who were convinced seekers accounted for 35.99% of total revenue, and married customers who were convinced seekers generated 37.31% of total revenue. When looking at revenue by gender, males accounted for 53.93% of total revenue. Lastly, convinced seekers within the £50,001-£75,000 yearly income bracket accounted for 21.53% of total revenue.
Looking at the key stats, we can see that the majority of customers are male (54.49%) and adult (45.48%), while seniors (37.17%) make up the largest portion of the revenue. Married customers (54.70%) make up slightly more of the customer base than single customers (45.30%), and both groups contribute roughly equal amounts of revenue.
The largest income bracket is customers earning between £50,001 and £75,000 (30.02%), who also generate the highest revenue (£6,533,636.08). The majority of customers are casual seekers (60.93%), but the convinced seekers (29.23%) generate the most revenue (£6,361,238.04). Australia has the most customers (24.57%) and generates the most revenue (£5,348,087.20) of all the countries.
Looking at the orders, the highest revenue was generated in 2013 (46.10%), with a total revenue of £10,034,065.97, while the lowest revenue was in 2011 (4.17%), with a total revenue of £908,212.89. Overall, 17,422 customers generated a total revenue of £21,764,628.64.
Based on the analysis of the revenue data for Hyperion using various pivot tables and charts, several insights were derived regarding the revenue generated by the company.
Age: The highest revenue came from the Adult and Senior age bands, accounting for 46.7% and 35.87% respectively. This suggests that Hyperion should focus its marketing and product offerings on these age groups to maximize revenue.
Country: The country that generated the highest revenue was Australia with 32.60%, followed by South West with 16.42%. This indicates that Hyperion may want to expand its operations in Australia and South West to increase revenue.
Gender: When analyzing revenue by gender and country, it was found that 16.50% of the total revenue came from males in Australia. Additionally, males accounted for 53.93% of total revenue. This suggests that Hyperion should continue to target male customers to maintain their revenue levels.
Year: The year 2013 accounted for the highest revenue with 11.43%. This could be due to various factors such as marketing campaigns, product offerings, or economic conditions. Hyperion should analyze the reasons behind this and use it to inform their future business decisions.
Income: The income bracket of £50,001-£75,000 generated the highest revenue with 11.62% in 2013. This indicates that Hyperion may want to target customers within this income bracket with their marketing and product offerings.
Customer Value: Convinced seekers accounted for 23.28% of total revenue in 2013, and were the highest revenue-generating customer value category. This suggests that Hyperion should focus its efforts on retaining these customers and possibly offer incentives to encourage repeat business.
Marital Status: Single and adult customers accounted for 25.28% of total revenue. This indicates that Hyperion may want to target these groups with its marketing and product offerings.
Combining Factors: When looking at revenue by customer value and country, convinced seekers in Australia generated 24.87% of total revenue. Males who were convinced seekers accounted for 35.99% of total revenue, and married customers who were convinced seekers generated 37.31% of total revenue. These insights suggest that Hyperion should focus on targeting married male customers who are convinced seekers in Australia to maximize revenue.
In summary, the analysis of the revenue data for Hyperion has provided valuable insights that can be used to inform business decisions. By targeting specific age groups, countries, income brackets, customer values, and gender and marital status combinations, Hyperion can optimize its marketing and product offerings to maximize revenue.