Expert Editing
Year Over Year Business Analysis
Disclaimer:
The data used for this analysis was obtained from a still-operating business. Information such as the company name and client names has been modified for confidentiality.
Introduction:
Expert Editing is an online business that provides photo editing services within the real estate niche.
Expert Editing offers five unique photo editing services and a rush delivery add-on when clients need a same-day turnaround.
The image enhancement service is Expert Editing’s standard editing service. It includes image straightening, color correction, white balance correction, small-item removal, and HDR blending.
Previous research that Expert Editing conducted showed that staged houses tend to sell faster and for more money than vacant houses, but the cost of having a home staged professionally can be cost-prohibitive. Expert Editing created their virtual staging service to be a low-cost solution to this problem. The virtual staging service transforms empty spaces into digitally staged rooms.
The virtual twilight service transforms daytime photos into breathtaking sunset photos. Daytime skies are replaced with twilight skies, and windows are given a warm glow.
The virtual decluttering service clears up cluttered spaces making rooms appear larger and more aesthetically pleasing.
The virtual landscaping service is primarily marketed towards homes that are in the process of being built and don’t have proper landscaping in place yet. Grass, trees, plants, and shrubs are all digitally “planted” to improve the curb appeal of exterior images.
The vast majority of sales are from a repeat client base. The owners are not engaged in any advertising at the moment, so any new clients have come from word-of-mouth or through the website via organic search.
Business task:
Expert Editing underwent a change of leadership in March of 2021. The primary purpose of this project is to analyze data from the twelve months before and twelve months after the leadership change to identify if any trend changes have occurred. The secondary purpose of this project is to report on the product sales and revision requests over the past twelve months.
Questions for the analysis:
- Has there been a significant change to the company’s revenue since the leadership transition?
- Has there been a substantial change to the company’s profitability since the leadership transition?
- Since the leadership transition, has there been a considerable change to sales on a per-client basis?
- Are there any recommendations we can make to Expert Editing based on the analyzed data?
Key Stakeholders:
Madison Imerson – Co-Owner and operations manager of Expert Editing.
Mark Imerson – Co-Owner and financial manager of Expert Editing.
Data Preparation:
Expert Editing utilizes a very robust online ordering system that captures all the relevant data we should need for our analysis. The dataset includes details from each order, including timestamp, client name, product(s) purchased, revenue generated, the profit realized, etc.
Microsoft Excel and Tableau were used to complete this analysis since the dataset is small and manageable.
My first step was to open the data in Excel for inspection. There were 15 individual tabs with data, but all of the data we needed for this analysis is in one sheet labeled as “Manifest”.
The Manifest tab pulls data from other sheets using VLOOKUP and IMPORTRANGE functions. Every cell within this sheet utilized a formula, so the first step was to copy the entire Manifest sheet and paste it as values to remove all of the formulas. I identified the columns I needed for my analysis and deleted any that I did not need to make the data more manageable. I then imported the data into Tableau for analysis and visualization.
Data Analyzation:
“Has there been a significant change to the company’s revenue since the leadership transition?”. I created a new Worksheet within Tableau and set the column as timestamp to find this answer. I formatted the timestamp column to show monthly data, and then I filtered the data to the twelve months before the leadership change, March 1st, 2020, to February 28th, 2021. Then I set the row to the sum of the order total column. Doing so gave me a vertical bar chart with the monthly revenue. I then added the client column as a color mark so that each bar would be further broken down by client. I noticed that a vast majority of the revenue came from six clients, so I created a custom set that showed the top six clients by the sum of the order total column. Then I made a custom field that lumped clients that weren’t in the top six set into a unique value I labeled as “Other”. I plotted an average line using that Analytics tab in Tableau to wrap up this visualization and then gave my chart a heading title. I also wanted to show the yearly total clearly, so I added that as text below the heading.
I repeated the same steps for the March 1st, 2021, to February 28th, 2022 data. I noticed that a particular client was new to these twelve months and that they only placed orders for about four weeks. I approached the stakeholders about this, and they informed me that this particular client had hired Expert Editing for a temporary project. We decided that this data was not helpful to the analysis, so I excluded this client from all further analysis. I included a disclaimer on any visualization made with this custom data.
Once I had both charts, I created a dashboard so that we could see the two charts side by side. I sorted the clients based on their order total values so that the more prominent clients appeared at the top of the bars. I also set the same range for the y axis on both charts so that the comparison would be more precise.
Here is our first dashboard:
With this visualization, we were able to see that the average monthly revenue and the overall yearly revenue went down about 16% since the leadership change occurred.
“Has there been a substantial change to the company’s profitability since the leadership transition?”. I created a new Worksheet within Tableau and set the column as timestamp again to find this answer. I formatted the timestamp column to show monthly data, and then I filtered the data to the twelve months before the leadership change, March 1st, 2020, to February 28th, 2021. The dataset already had a column that provided the net profit on a per order basis, so I set the row to the sum of the profit total column. Doing so gave me a vertical bar chart with the monthly profits.
I repeated the same steps for the March 1st, 2021, to February 28th, 2022 data. As previously noted, I again excluded the temporary project from this date range and added a disclaimer to the chart’s sub-heading.
Once I had both charts, I created a dashboard so that we could see the two charts side by side. I set the same range for the y axis on both charts so that the comparison would be more precise.
Here is our second dashboard:
With this visualization, we were able to see that the average monthly revenue and the overall yearly revenue went down about 10% since the leadership change occurred.
“Since the leadership transition, has there been a considerable change to sales on a per-client basis?”. I created a new Worksheet within Tableau and constructed a pie chart using the order total sum and our custom set of top six clients as the color mark to find this answer. I filtered the data to only show orders from March 1st, 2020, to February 28th, 2021. I included labels for each client and their percentage of the overall pie. Then I sorted the color mark by the sum of the order total field so that the slices are in descending order as you go around the chart clockwise.
I followed the same steps to create a pie chart for orders placed between March 1st, 2021, and February 28th, 2022. I again excluded the temporary project and put a disclaimer on the chart.
After finishing both pie charts, I created a dashboard to showcase them side-by-side for comparison.
Here is the third dashboard:
With this visualization, we saw an increase for the top client, but the second, third, and fourth clients from the first analyzed year all decreased after the leadership change. The fifth client had a slight increase, the sixth client had a significant increase, and the “Other” category had a significant decrease.
The stakeholders were also interested in how individual products performed over the past twelve months. To visualize this data, I created a new Worksheet within Tableau and added the image count as the column and the image type and our custom set of top six clients to the rows. I set our top clients as the color mark and the image count as the label mark. Doing so gave me a horizontal bar chart with a per-client breakdown of each product type.
The stakeholders wanted to see a breakdown of revision requests per client for the final visualization. I created another horizontal bar chart for this visualization and used the image count as the column again and our top six clients set and image type as the rows. Since 94% of the orders placed are for the Image Enhancement product, we decided to show revision requests compared to that product.
After I finished both of those charts, I created another dashboard.
Here is the fourth dashboard:
With this visualization, we were able to see that the most popular product, accounting for 94% of all orders, is the Image Enhancement product. About 1% of all orders request that the delivery be rushed back in 12 hours or less. We see that virtual decluttering hasn’t had any orders in the past twelve months. The other two virtual services, virtual twilight and virtual staging, are rarely used compared to the image enhancement product.
The revision percentage by client chart shows us that most clients request revisions on less than 5% of their images. The overall revision rate is 3.62%, indicating that clients are pleased with their initial deliverables. The “Other” category shows a revision request rate of 12%, which is comparatively high. Most of the “Other” clients are one-off projects. Photo editing can be a very subjective field, so it makes sense that regular clients (our top six) are pleased with the editing style of the company while some of the one-off project clients are not.
Recommendations Based on Analysis:
“Are there any recommendations we can make to Expert Editing based on the analyzed data?”.
We have seen that revenue and profitability have decreased since the leadership transition, and many top clients are sending fewer orders to Expert Editing. Some of the drop-offs occurred right around the leadership transition, so it seems very plausible that there is a connection between the leadership change and the drop-off. All six of the top clients are still placing orders with Expert Editing, so a simple survey of those clients could reveal some important insights. Are some clients experiencing a slow-down in their business resulting in fewer editing requests? What do you feel has changed (good and bad) since the leadership transition? How could Expert Editing help them grow their photography businesses?
Six clients have placed over 99% of all orders in the past twelve months. Expert Editing hasn’t engaged in any advertising since the leadership transition. Neither word-of-mouth nor organic search are producing new clients. Engaging in social media marketing and paid search ads could help grow the client base.
94% of all orders in the past twelve months were for the image enhancement product. A survey of the current clients could provide helpful feedback on why the other products aren’t selling. Are the other products priced too high? Do they feel there is value in the products? Have they tried the products before? Are there any other products that we could add?