<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Egemolu Goodnews on Medium]]></title>
        <description><![CDATA[Stories by Egemolu Goodnews on Medium]]></description>
        <link>https://medium.com/@webmaster406?source=rss-6f59600fa4be------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*WSHmias-H-Z8jZ9999sQBw.jpeg</url>
            <title>Stories by Egemolu Goodnews on Medium</title>
            <link>https://medium.com/@webmaster406?source=rss-6f59600fa4be------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 07:43:17 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@webmaster406/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[I built a customer ledger for my distribution business on Google Sheets, with no server and no…]]></title>
            <link>https://medium.com/@webmaster406/i-built-a-customer-ledger-for-my-distribution-business-on-google-sheets-with-no-server-and-no-c9997f152517?source=rss-6f59600fa4be------2</link>
            <guid isPermaLink="false">https://medium.com/p/c9997f152517</guid>
            <dc:creator><![CDATA[Egemolu Goodnews]]></dc:creator>
            <pubDate>Wed, 07 Oct 2026 09:58:40 GMT</pubDate>
            <atom:updated>2026-10-07T09:58:40.663Z</atom:updated>
            <content:encoded><![CDATA[<h3>I built a customer ledger for my distribution business on Google Sheets, with no server and no monthly fee</h3><p><em>How I went from “we lose track of who owes us what” to a working app my staff open on their phones. Every step I did myself, and the screenshots show where it got stuck.</em></p><h3>The Problem</h3><p>I work as a Key Account Manager — Modern Trade for a leading FMCG and manage Nondios Nigeria Enterprises, a distribution business supplying supermarkets. We had an issue that made me decide to build the system. A customer requested for invoice of supply for a period of two years showing their receiving stamp (ideally when my reps supply, the customers confirm their supply and stamp it showing that they have received it, they then snap and send to me. But what happens when anyof us loses our phone or data in the phone? This was the problem with this customer, we couldnt provide record of the invoices of over a year also we could give them account statement of what they bought and what they paid.</p><p>We already had an invoicing system. What we didn’t have was a clean answer to three questions:</p><ul><li>Who owes us, how much, and for how long?</li><li>Which invoice did that payment actually clear?</li><li>Where is the invoice photo when a supermarket disputes a figure?</li></ul><p>Supermarkets pay in parts. Staff write figures in notebooks. When a customer says “I paid you last month”, finding the proof meant scrolling through WhatsApp.</p><p>I wanted one place where each customer has an account: invoices in, payments and discounts recorded against them, and a statement or receipt one click away.</p><h3>What I wanted it to do</h3><p>I wrote my requirements down before touching anything:</p><ol><li>Staff record a sale by typing the invoice figures and attaching a photo of the invoice.</li><li>The app checks whether the photo is readable and asks for a re-snap if it isn’t.</li><li>Only I, as admin, can record payments, and I can record them against a specific invoice. Part payments show the balance and the payment date on the statement.</li><li>Different staff get different permissions.</li><li>It works on a phone and on a laptop.</li><li>Statements and receipts download as PDF.</li><li>It flags overdue accounts (my terms are 30 days) and lets me send a WhatsApp reminder.</li></ol><h3>Why Google Sheets</h3><p>I first looked at a proper database backend. It’s a good option, but it means another account, another dashboard and another thing to keep alive. I already live in Google Sheets. A Google Sheet plus Apps Script gave me:</p><ul><li>a free database I can open and read myself</li><li>free hosting for the web app</li><li>photo storage in my own Google Drive</li><li>nothing for staff to install; they open a link</li></ul><p>The trade-off is that it’s slower than a real database and it’s not built for hundreds of people at once. For a team of a few staff, that’s fine.</p><h3>How I built it</h3><p>I described the system to Claude and it wrote the code: the app’s pages and the server script that sits behind the Sheet. What follows is the part nobody can do for you, which is getting it running on your own Google account.</p><h3>Step 1: Create the Google Sheet</h3><p>I made a blank Google Sheet and named it “Nondios Ledger”. Everything lives inside this one file.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*m4BUDHGR8RRRdYyo5rw-mA.png" /></figure><h3>Step 2: Open Apps Script and paste the code</h3><p>From the Sheet, select<strong>Extensions → Apps Script</strong>. This opens the editor attached to the Sheet. I replaced the default Code.gs with the server code, then added a new HTML file called Index for the app&#39;s pages.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kmRVL7wrZuGzJGJWFDidvQ.png" /></figure><h3>Step 3: Run the one-time setup</h3><p>I picked the setup function from the dropdown and clicked Run. Google asked for permission, and since this is my own script it shows a scary &quot;unverified app&quot; warning. I went through <strong>Advanced → Go to project → Allow</strong>.</p><p>Setup creates the data tabs, creates a Drive folder for invoice photos, and prints a 6-digit code in the execution log. That code is how the first admin account gets created, so nobody else can claim it.</p><h3>Step 4: Deploy it as a web app</h3><p><strong>Deploy → New deployment → Web app.</strong> I set it to run as me, and gave access to “Anyone”, because staff aren’t on my Google account. The app has its own username-and-password login, so “anyone with the link” can open the page but can’t see data without signing in.</p><p>Google gives you a long link. That link is the app.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*f5qRIieOxj7Xknk8xANPKw.png" /></figure><h3>Step 5: Create the admin login</h3><p>Opening the link the first time asks for the setup code, then lets me choose my name, username and password. From then on that screen is gone and everyone gets a normal sign-in.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TtOs5NM1QLcxDEhadEoCTw.png" /></figure><h3>Step 6: Add staff</h3><p>In the Staff tab I created a login for each person with one of three roles: admin, staff or viewer. Staff can add customers and invoices. They can’t record payments, change prices or delete anything. Viewers can only look. These rules are enforced on the server, not just hidden in the interface, so a staff member can’t get around them by being clever.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TtOs5NM1QLcxDEhadEoCTw.png" /></figure><h3>What it looks like in daily use</h3><ul><li><strong>Accounts:</strong> every customer with their balance and how overdue they are, sorted so the worst debtors are on top.</li><li><strong>New invoice:</strong> pick the customer, attach the invoice photo, type the figures. The app checks the photo for blur and poor lighting before saving.</li><li><strong>Payments (admin only):</strong> pick the customer, pick the invoice, enter the amount. Part payments leave the balance open and show on the statement with the date.</li><li><strong>Statements and receipts:</strong> one click, PDF.</li><li><strong>Reminders:</strong> a WhatsApp message pre-filled with the customer’s balance. I choose the tone: friendly, firm or final.</li><li><strong>Reports:</strong> monthly sales per customer, top debtors, collection rate, and a zip of every debtor’s statement at month-end.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JLUsZqf4cxnbZnCvekqn4g.png" /><figcaption>Whatsapp Reminder</figcaption></figure><h3>Where it broke, and what I learned</h3><p>This is the part I’d have wanted to read before I started, because the first deployment did not work.</p><p><strong>1. A white page that wouldn’t click.</strong> The page loaded but just sat on “Loading”. The cause was that the app was waiting to download three helper libraries (for PDF and Excel) before starting. On a slow or filtered connection that download never finished, so the app never started. The fix was to load those libraries in the background so the app starts regardless.</p><p><strong>2. “Invalid argument: id”.</strong> I had deployed without running setup first, so the script didn&#39;t know which Sheet to use. A small diagnostic page that printed what the server was saying showed this in seconds. If you build anything on Apps Script, make yourself a diagnostic page early. It saves hours of staring at a blank screen.</p><p><strong>3. A column count error during setup.</strong> A one-line mistake in the code: the header row had 7 columns but the code asked for 8. It was a typo-level bug, found only because I ran the real thing in my own Google account instead of just trusting that it worked.</p><p><strong>4. Forgetting to deploy a new version.</strong> Every time you change the code you must go to <strong>Deploy → Manage deployments → New version</strong>. Saving the code isn’t enough; the link keeps serving the old version until you redeploy.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GKrX6z5VO_mXNLBCVDkbmA.png" /></figure><h3>Limits I’m honest about</h3><ul><li><strong>The photo check is basic.</strong> It catches blurry or dark photos. It can’t tell if the photo is taken from too far away. Staff type the figures themselves; nothing reads the invoice automatically.</li><li><strong>Backups are on you.</strong> Google Sheets has version history, but I turned on the automatic weekly backup in the menu anyway.</li><li><strong>It’s built for a small team.</strong> A handful of staff is fine. It’s not meant for a large company.</li><li><strong>Never share the Sheet itself with staff,</strong> only the app link. Anyone with access to the Sheet can see everything.</li></ul><p>Let me walk you thought with screenshots</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HJN0mA6WrwB85YZ9Nj-zPQ.png" /><figcaption>Login Page</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*onWrqNmBBNTIzhBa-qeJVw.png" /><figcaption>Dashboard</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Wfu4XoQYckv5tp2q7kwXpg.png" /><figcaption>Adding an Invoice</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0zphaG6I_4LoBCh544dPig.png" /><figcaption>Customer Account</figcaption></figure><h3>What I took from it</h3><p>The code was the smaller part of the work. The real work was knowing exactly what my business needed: who can record payments, how part payments should show on a statement, what “overdue” means for us. Once those rules were clear, building the tool was straightforward.</p><p>If you run a small business that chases payments from a notebook, you don’t need expensive software. You need your rules written down, a Google account, and an afternoon of patience.</p><p><em>If you want to talk about building something like this for your own business, reach out in the comments.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c9997f152517" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Analyzing HR Attrition With Power BI]]></title>
            <link>https://medium.com/@webmaster406/analyzing-hr-attrition-with-power-bi-bb6337019bf0?source=rss-6f59600fa4be------2</link>
            <guid isPermaLink="false">https://medium.com/p/bb6337019bf0</guid>
            <dc:creator><![CDATA[Egemolu Goodnews]]></dc:creator>
            <pubDate>Sun, 21 Jan 2024 20:50:06 GMT</pubDate>
            <atom:updated>2024-01-21T20:50:06.355Z</atom:updated>
            <content:encoded><![CDATA[<p>In Today’s analysis, I will be analyzing HR Data to see the rate of attrition, and what is causing it. Later, we will advise HR on strategies to manage attrition in the organization. First, let us look at our dataset.</p><p>Our dataset contains data from different sources as you can see in the diagram below. I have an Employee survey data sheet, general data sheet, managers survey, etc.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/803/1*1Hgykg0WGZZRrDOvWEly3Q.png" /></figure><p>I imported these datasets individually into Power BI. Since the data are in 3 different sheets, I created a star schema for this dataset to trade some redundancy (duplication of data) in the data model for increased query speed, by avoiding computationally expensive join operations. Since there is a common factor in all the datasets i.e. Employee ID, I used it to create a 1-to-many relationship for the start schema.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/752/1*7IMkgmhq2j-zGnOy0s_3MA.png" /></figure><p>Next, i get the total of all employees in the company using the count row DAX function.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/892/1*lIa10UeONaX28IFdYyPcUA.png" /></figure><p>I also calculated the Total attrition, using a combination of the Calculate and filter function.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/966/1*z0-o8d4RuCUIcxwYIgtJOw.png" /></figure><p>Did the same for the total number of active staff.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nt0uuWyTxyOLLDcMtf65qw.png" /></figure><p>I also calculated the attrition rate by dividing Total Attrition by Total Employees.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/737/1*fugTB4lbg4Mfy9sEs0i-Kg.png" /></figure><p>Since the employee satisfaction rating is recorded in numbers 1–5. To make sense of the data and analyze it properly, I used a conditional column.</p><p>In the picture below, you can see how I used the “if” statement to show</p><p>Very Dissatisfied if the satisfaction rate is 1, Dissatisfied if its 2, Satisfied if 3, and Very Satisfied if it’s 4.</p><p>You can see this in the picture below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ag4ro8ij8Mne-3XfyhyFdw.png" /></figure><p>I did same for job satisfaction rating</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/979/1*OvtZndp52OMd_eSyhkyAEg.png" /></figure><p>Also for distance. Where I put less than 15 as Near, 15 -30 as far 30–50 as very far etc.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/994/1*WrgNg3Pb9vQSw0q0Qg1YZw.png" /></figure><p>Then I started to build the Dashboard.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/804/1*b_0VvOqbpNb-Sj1wjTZl3w.png" /></figure><p>Finally the Dashboard</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1U4FMmVJb8mWY9E3_LLIkA.jpeg" /></figure><p>Now let’s Analyze the Data and Dashboard.</p><p>This Analysis aims to see why people are leaving the organization.</p><ol><li>The Job role with the highest number of attrition is Sales Executive (165) as seen in the diagram below. The Sales Executive has a higher attrition rate of 17% than the general attrition rate of 16%. In summary, we have more Sales Executives leaving the company than any other role. On a closer look, we found out that the number of sales executives who are leaving the company are the married ones.</li></ol><p>Solution: It appears that the Sales Executive role has the highest attrition rate among all job roles, standing at 17%, which is higher than the overall company attrition rate of 16%. This indicates that Sales Executives are leaving the company at a higher rate compared to other roles.</p><p>Upon closer examination, it has been identified that the marital status of the Sales Executives leaving the company is a significant factor. Specifically, a notable trend has been observed — a higher proportion of the departing Sales Executives are married.</p><p>This insight adds a layer to the attrition analysis, suggesting a potential correlation between marital status and attrition within the Sales Executive role. Further investigation into the reasons behind the higher attrition rate among married Sales Executives could provide valuable insights for the company to address specific challenges or concerns within this demographic.</p><p>Understanding the specific factors contributing to the attrition of married Sales Executives will be crucial for implementing targeted retention strategies. This information can guide the development of policies or initiatives aimed at improving employee satisfaction, engagement, and work-life balance for Sales Executives, ultimately working towards reducing attrition within this critical role</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/463/1*rzvkKnotZHrRNg6xqZAiqQ.png" /></figure><p>The Business can address this issue By:</p><ul><li><strong>Employee Surveys and Feedback:</strong></li><li>Conduct surveys or interviews to gather feedback from Sales Executives, especially those who are married. Understand their concerns, reasons for leaving, and expectations from the organization.</li><li><strong>Flexible Work Arrangements:</strong></li><li>Offer flexible work schedules or remote work options to accommodate the needs of married Sales Executives. Providing a better work-life balance can positively impact retention.</li><li><strong>Employee Assistance Programs (EAPs):</strong></li><li>Implement Employee Assistance Programs to provide support for personal and family-related challenges. This can include counseling services, stress management, and resources for maintaining a healthy work-life integration.</li></ul><p>2. The Research and Development Department is the department with the highest number of staff leaving the company. If the Research and Development (R&amp;D) department is experiencing the highest attrition rate within the company, it’s crucial to investigate and implement strategies to address this issue. After further analysis, we found out that the majority of the staff from the R&amp;D department who are leaving the country are leaving because of poor work-life balance.</p><p>Solution: Addressing the concern of poor work-life balance in the Research and Development (R&amp;D) department is critical to retaining talent and creating a healthier workplace environment. Here are targeted strategies to improve work-life balance in the R&amp;D department:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/316/1*i5K6UdyiiyOKg8j1KkBbmw.png" /></figure><ul><li><strong>Flexible Work Hours:</strong></li><li>Introduce flexible work hours or alternative work arrangements to allow R&amp;D employees to better balance their professional and personal commitments. This could include options like flextime or compressed workweeks.</li><li><strong>Remote Work Opportunities:</strong></li><li>Explore the possibility of remote work, allowing R&amp;D staff to work from home on designated days. This can reduce commuting time and provide a more flexible work environment.</li></ul><p>3. The age group with the highest attrition is 30–39 and their job satisfaction rating is Very Dissatisfied</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/610/1*0adFszeXB-VDq5uWd8aoxg.png" /></figure><p>Solution: Identifying a correlation between the age group of 30–39 and a Very Dissatisfied job satisfaction rating is crucial for implementing effective retention strategies. Here are some targeted actions to address the high attrition in this age group with a focus on improving job satisfaction:</p><ul><li><strong>Conduct Detailed Surveys:</strong></li><li>Administer in-depth surveys specifically targeting employees in the 30–39 age group. Collect detailed feedback on aspects contributing to dissatisfaction, such as workload, career growth opportunities, work-life balance, and organizational culture.</li><li><strong>Individual Interviews:</strong></li><li>Conduct one-on-one interviews with employees in this age group to gain deeper insights into their concerns and aspirations. Personalized discussions can unveil unique challenges and expectations.</li><li><strong>Career Development Programs:</strong></li><li>Implement career development programs that provide a clear path for advancement. Offer mentorship opportunities, training programs, and skill development initiatives to enhance professional growth for employees in their 30s.</li><li><strong>Flexible Work Arrangements:</strong></li><li>Introduce flexible work arrangements to accommodate the diverse needs of employees in this age group. Options such as flexible hours or remote work can contribute to a better work-life balance.</li></ul><p>4. Not being promoted is not the reason staff are leaving the organization. The majority of those who left were recently. While the least number of attrition came from those who haven’t been promoted in the last 15 years. But why so, I mean one will expect to have the highest number of attrition from those who haven’t been promoted for a long time.</p><p>5. Finally, staff are leaving because they no longer get satisfaction. From the data below, we can see that very dissatisfied came second just slightly below satisfied. The business needs to implement the suggestions made above in other to increase the satisfaction rate of their business and make it a better place to work.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/338/1*ldDgJVLa_7DR4rfXwwEyKg.png" /></figure><p>Ciao</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bb6337019bf0" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Analyzing Global Terrorism Trend With Power BI]]></title>
            <link>https://medium.com/@webmaster406/analyzing-global-terrorism-trend-with-power-bi-2c4758795fe2?source=rss-6f59600fa4be------2</link>
            <guid isPermaLink="false">https://medium.com/p/2c4758795fe2</guid>
            <dc:creator><![CDATA[Egemolu Goodnews]]></dc:creator>
            <pubDate>Thu, 21 Dec 2023 09:06:34 GMT</pubDate>
            <atom:updated>2024-03-25T07:32:40.580Z</atom:updated>
            <content:encoded><![CDATA[<p>Hi Data Fam, long time no analysis. So I had a little time and I decided to delve into a project I had been procrastinating on. Below is the project brief.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/946/1*DMaW63zXoyaQaafbNkHIeQ.png" /></figure><p>I wanted to use Tableau for this analysis but Tableau won’t give me what I want, so I opted for Power BI. So I will be using Power BI both for cleaning and analysis. Let’s dig in…</p><p>The first thing I did, which I usually do is to open the data set in Excel to take a look at it so I will know what it looks like and how much cleaning it needs.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mCQrQCbOsGq8tvsCQm0hXQ.png" /></figure><p>The data set aint that bad, so I jump straight into Power Query for cleaning first.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/895/1*kIBON4nJDogHGwVjLodSqg.png" /></figure><p>Now, let the cleaning begin</p><p>You can see below that the data set titles are not named correctly and their data type is incorrect too. The first thing I did was to rename it. I also deleted the columns that might not be relevant to my analysis.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/987/1*A-yNg98S8N6DDM1sX1yR9A.png" /></figure><p>Done with the renaming. Now, notice in the data set (as shown below) that we have Year, Month, and Day in separate columns. This is not good for my analysis as I won’t be able to change the data type like this. So the next thing I do is to merge the 3 columns into 1.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/537/1*wMlASrIXtB9YFhFxPDVdDA.png" /></figure><p>To do this, I selected the three columns and then right clicked.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aV_Si4eyrLoG8OGLe6ZJOA.png" /></figure><p>This will open another window where I selected the separator as custom then use / as my separator and name the new column name as ‘Date’</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/890/1*oldEf4YQB-rd-Mov1h6LVw.png" /></figure><p>“Notice in the data set, that the Year came first, followed by the month, and then day. When I merged the three columns, the new column ‘Date’ came out as 1970/12/1 i.e. the YYYY/MM/DD format. When I changed the data type to date it threw an error. I think it’s probably because the power query uses the DD/MM/YYY format. So to do this, I will arrange the column to have day first, then month and year so when I merge it will come out as DD/MM/YYYY”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/578/1*PGUyHwFa7iRaAHfjotlPGw.png" /></figure><p>Notice that in the day and month column, we have ‘0’. There is no day or month as 0. So I will unselect it.</p><p>Now I changed the data type and it showed an error. I have done all I think I should but it keeps showing errors. Then I did this.</p><p>Step 1: I right-clicked on the Date column then hovered around the change type and selected ‘Using Locale’ as shown below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6gD2FZcxemCBhhLLIyxJ2w.png" /></figure><p>Step 2: I selected the data type as ‘Date’ and the Locale as English United Kingdom which has the same format as my date DD/MM/YYYY. If the format of your date is MM/DD/YYYY please select English United States. If the format you selected is not in alignment with that of your data, it will throw an error. When you are done click on okay and you see the error will disappear.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/998/1*nXyj1DpCoyab00yw4ocs0g.png" /></figure><p>This is the last part of cleaning the data and I will hit the close and apply button.</p><p>The Next stage is the visualization</p><p>Now in my Data set, I have a column that contains status where 1 represents a successful attack and 0 represents a failed account. I want to write a command that will count the successful attack</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/771/1*Xz19EIQwUq3v4zhsbScuIg.png" /></figure><p>I will do the same to get the failed attack</p><p>I displayed all of them using the card visualization</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/891/1*lZyy21zQHJs-1vrvK7rSRQ.png" /></figure><p>This means in the year in review, we have had a total of 182,000 attacks of which 162,000 were successful and 20000 failed.</p><p>Next, I used map , bar chart and graph to show some visualization</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/747/1*Nu0vqzOYegJF0QB9SHlIpA.png" /></figure><p>I also added more visualization to get the attack group involved and total casualties</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/722/1*gkSrqHoWou_Wau_ThAghCw.png" /></figure><p>Now I want my dashboard to be finer, I want to do some tweaks but Power BI won’t give me that freedom so I will go to PowerPoint.</p><p>In PowerPoint, the first thing I will do is to make sure the PowerPoint slide size is the same as that of Power BI. To do this go to Design &gt; Slide size&gt; custom slide size. Select the size as 1000x1200 which is the same size we used in BI.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FpeIJnBo8sBS_ET4i5iAYw.png" /></figure><p>The next is to fill the background. I want a gradient fill so I will choose it</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/498/1*Bn9INLbEIaun3lsqpqKlqw.png" /></figure><p>After playing around with gradient for sometime, i got what i was looking for</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*U3FLtVv202BhORl60uiCYw.png" /></figure><p>Next, I applied this to my Power BI</p><p>PS: If you will do this, export your design from PowerPoint design as SVG</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/582/1*7UlGoNn1zSlgek12hKGpjA.png" /></figure><p>This is what my dashboard looks like. At first glance, my dashboard looks ugly, but wait till I work on it.</p><p>After a few touches, this is how the dashboard came out</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/819/1*WlELDi865Pct4Ng2R0xcng.png" /></figure><p>I think I am done here</p><p>Next is to add slicers</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/866/1*nd-N3xjqsnpVeIWrhMD-Gw.png" /></figure><p>My Final Dashboard</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5YMV_KC06BwprUBP8wpcWA.jpeg" /></figure><p>I want to tweak the visual a little, i want to have a button that will hide so part of the visual and another one that will show it. To do this i use bookmark.</p><p>First thing I did was to download the icon i need (a plus and minus icon)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/426/1*V_ODrBWwrwD8_OBQssdzNA.png" /></figure><p>Next I inserted this into power BI (Insert &gt; Buttons&gt; Blank),</p><p>When you insert the button, go to style&gt;fill then input either the minus of plus picture. The background might not be transparent, to make it transparient, use a conditional function.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/259/1*VAzUYMDNEEBAAbVH4WK6YQ.png" /></figure><p>Whe inserting the button picture, ensure transperency is at zero and image fit is in fit or fill.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/245/1*EZJ7qbPAXtAuZDlAcqYOxg.png" /></figure><p>Next, go to view&gt;book mark&gt; open bookmark</p><p>Ensure only the plus sign is showing (hide the minus sign ). Click on Add and name it open.</p><p>Next hide the charts you dont want to show.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/892/1*0frCtqD9O_sx0B8WGbdqcA.png" /></figure><p>Also make sure only the plus sign is showing this time</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/936/1*5X3uKzW5snkbZb7-G9rFDw.png" /></figure><p>Notice I dragged the bar chart up</p><p>Next I link it to the button</p><p>Go to + button&gt;action&gt; (turn action on) &gt; type &gt; bookmark&gt; Open</p><p>Go to -button&gt;action&gt; (turn action on) &gt; type &gt; bookmark&gt;Open</p><p>Also I changed visualization from doughnut to treemap</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/945/1*rVBJiZzQ02Ow8LRX3ki64g.png" /></figure><p>And that will be done. I will use a video to demonstrate what I did</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FFi_JyBlPbV0%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DFi_JyBlPbV0&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FFi_JyBlPbV0%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="640" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/47a3edab1603c79415e6e601e8fb5594/href">https://medium.com/media/47a3edab1603c79415e6e601e8fb5594/href</a></iframe><h3>Summary:</h3><p>After conducting a comprehensive analysis of the Terrorism Database, several key trends and patterns have emerged, shedding light on the global landscape of terrorist activities. Here are the primary findings:</p><ol><li>Global Trends:</li></ol><ul><li>The time series line chart illustrates a noticeable trend in the frequency of terrorist attacks over the years. The attacks increased from 400+ in 2010 to about 15,000 in 2014 causing over 1 million casualties.</li></ul><p>2.Attack Type Distribution:</p><ul><li>The bar chart and pie chart visually represent the distribution of attack types. The most prominent attack was bombing, which was used in over 77,000 attacks causing over 1M deaths. It was followed by armed assault on 44,000 in attack.</li></ul><p>3. Successful Attacks:</p><ul><li>The count of successful attacks indicates Iraq has the most attacks followed by Pakistan and India. This information is essential for risk assessment and strategic planning.</li></ul><h3>Recommendations:</h3><p>Based on the analysis, the following recommendations are proposed to enhance understanding and response to terrorist activities:</p><ol><li>Enhanced Security Measures:</li></ol><p>Given the observed trends, consider reinforcing security measures during periods or in regions where there is a historical uptick in terrorist activities.</p><p>2. Targeted Training and Preparedness:</p><p>Provide targeted training and preparedness programs for regions facing a higher prevalence of specific attack types. This can empower local communities and authorities to respond effectively.</p><p>3. Collaboration and Information Sharing:</p><p>Strengthen international collaboration and information-sharing mechanisms to facilitate a more coordinated response to global terrorism. Timely sharing of intelligence can aid in preventing and mitigating potential threats.</p><p>4. Continuous Monitoring and Analysis:</p><p>Establish a continuous monitoring and analysis framework to stay abreast of emerging trends. Regularly update the analysis to adapt strategies in response to evolving patterns of terrorist activities.</p><p>5. Public Awareness Campaigns:</p><p>Implement public awareness campaigns to educate communities about potential threats, fostering a sense of vigilance and promoting the reporting of suspicious activities.</p><p>By implementing these recommendations, it is anticipated that the organization or relevant authorities will be better equipped to navigate and respond to the dynamic challenges posed by global terrorism</p><p>Ciao</p><h3>About the Author</h3><p>Hello, I’m Goodnews, a passionate data enthusiast with a knack for turning numbers into insights. With a background in Sales, Product, and now Data, I’m dedicated to harnessing the power of data to drive informed decisions. Connect with me let’s embark on data-driven adventures together!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2c4758795fe2" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Analyzing Booking Data for Splendor Hotel Groups Using Excel and Power BI]]></title>
            <link>https://medium.com/microsoft-power-bi/analyzing-booking-data-for-splendor-hotel-groups-using-excel-and-power-bi-8505741028bb?source=rss-6f59600fa4be------2</link>
            <guid isPermaLink="false">https://medium.com/p/8505741028bb</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[power-bi-tutorials]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Egemolu Goodnews]]></dc:creator>
            <pubDate>Sun, 26 Nov 2023 10:07:29 GMT</pubDate>
            <atom:updated>2024-03-25T07:33:24.877Z</atom:updated>
            <content:encoded><![CDATA[<p>Hi, welcome to another data analysis task. There is this data hackathon that I am participating in on Twitter. We were given the project brief below.</p><p><strong>Project Brief: Analyzing Booking Data for Splendor Hotel Groups (SHG)</strong></p><p>Greetings from Splendor Hotel Groups (SHG). As a recently recruited Business Intelligence Analyst and Data Analyst, you will be essential to our efforts to solve the puzzles buried in our booking data. Renowned hospitality company SHG aims to improve visitor experiences and streamline corporate processes by utilizing data-driven insights. Your task is to thoroughly examine one of our best resorts’ past booking data in order to identify trends, comprehend consumer behavior, and offer useful suggestions for tactical decision-making.</p><p><strong>Project Overview:</strong></p><p>Your task involves a thorough analysis of a comprehensive dataset, featuring intricate details of bookings, guest demographics, distribution channels, and financial metrics. By applying your analytical prowess, we aim to extract meaningful insights that will not only inform operational improvements but also contribute to the overall success of SHG in delivering unparalleled hospitality.</p><p><strong>Objectives of the Analysis:</strong></p><p>Booking Patterns:</p><ul><li>What is the trend in booking patterns over time, and are there specific seasons or months with increased booking activity?</li><li>How does lead time vary across different booking channels, and is there a correlation between lead time and customer type?</li></ul><p>Customer Behavior Analysis:</p><ul><li>Which distribution channels contribute the most to bookings, and how does the average daily rate (ADR) differ across these channels?</li><li>Can we identify any patterns in the distribution of guests based on their country of origin, and how does this impact revenue?</li></ul><p>Cancellation Analysis:</p><ul><li>What factors are most strongly correlated with cancellations, and can we predict potential cancellations based on certain variables?</li><li>How does the revenue loss from cancellations compare across different customer segments and distribution channel</li></ul><p>Revenue Optimization:</p><ul><li>What is the overall revenue trend, and are there specific customer segments or countries contributing significantly to revenue?</li><li>Can we identify optimal pricing strategies based on the Average Daily Rate (ADR) for different customer types and distribution channels?</li></ul><p>Geographical Analysis:</p><ul><li>How does the distribution of guests vary across different countries, and are there specific countries that should be targeted for marketing efforts?</li><li>Is there a correlation between the country of origin and the likelihood of cancellations or extended stays?</li></ul><p>Operational Efficiency:</p><ul><li>What is the average length of stay for guests, and how does it differ based on booking channels or customer types?</li><li>Are there patterns in check-out dates that can inform staffing and resource allocation strategies?</li></ul><p>Impact of Deposit Types:</p><ul><li>How does the presence or absence of a deposit impact the likelihood of cancellations and revenue generation?</li><li>Can we identify any patterns in the use of deposit types across different customer segments?</li></ul><p>Analysis of Corporate Bookings:</p><ul><li>What is the proportion of corporate bookings, and how does their Average Daily Rate (ADR) compare to other customer types?</li><li>Are there specific trends or patterns related to corporate bookings that can inform business strategies?</li></ul><p>Time-to-Event Analysis:</p><ul><li>How does the time between booking and arrival date (lead time) affect revenue and the likelihood of cancellations?</li><li>Are there specific lead time ranges that are associated with higher customer satisfaction or revenue?</li></ul><p>Comparison of Online and Offline Travel Agents:</p><ul><li>What is the revenue contribution of online travel agents compared to offline travel agents?</li><li>How do cancellation rates and revenue vary between bookings made through online and offline travel agents?</li></ul><p><strong>Tools and Technologies:</strong></p><ul><li>Participants are encouraged to use tools of their choice for data analysis and visualization.</li></ul><p><strong>Deliverables:</strong></p><ul><li>Interactive and Responsive Dashboard:</li><li>A dynamic dashboard displaying essential metrics and KPIs.</li><li>Real-time updates and data filtering capabilities.</li><li>Documentation:</li><li>Comprehensive documentation detailing data sources, methodologies, and actionable insights.</li><li>Also, a Report will be okay if you can’t build a dashboard</li></ul><p><strong>Timeline:</strong></p><ul><li>Deadline: 1 week from project initiation.</li></ul><p><strong>Data Dictionary:</strong></p><ul><li>Booking ID: Unique identifier for each booking.</li><li>Hotel: Type or name of the hotel within the Splendor Hotel Group.</li><li>Booking Date: Date when the booking was made.</li><li>Arrival Date: Date when the guests are scheduled to arrive.</li><li>Lead Time: Number of days between the booking date and arrival date.</li><li>Nights: Number of nights the guests are booked to stay.</li><li>Guests: Number of guests included in the booking.</li><li>Distribution Channel: The channel through which the booking was made (e.g., Direct, Online Travel Agent, Offline Travel Agent).</li><li>Customer Type: Type of customer making the booking (e.g., Transient, Corporate).</li><li>Country: Country of origin of the guests.</li><li>Deposit Type: Whether a deposit was made for the booking (e.g., No Deposit, Deposit).</li><li>Avg Daily Rate: Average daily rate for the booking.</li><li>Status: Status of the booking (e.g., Check-Out, Canceled).</li><li>Status Update: Date of the last status update for the booking.</li><li>Canceled (0/1): Binary indicator of whether the booking was canceled (1 if canceled, 0 if not canceled).</li><li>Revenue: Revenue generated from the booking.</li><li>Revenue Loss: Loss in revenue if the booking was canceled (negative value if the booking wasn’t canceled).</li></ul><p>Good luck guys, I can’t wait to see your lovely analysis and work!!!</p><p>I will try as much as I can to do justice to the project objective. For this task, I will be using Excel and Tableau.</p><p>The first thing I did was to open the dataset in Excel to take a look at it and try to understand it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*79zPlhmZfZJtiK3-EwVWuw.png" /></figure><p>Next, we move over to cleaning, to clean the dataset, I will make use of Power Query.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ORhqncZP70DVNMSm6YrmVA.png" /></figure><p>The first cleaning I did was to inspect the data type for each column to ensure they had the correct classification, I checked for inconsistency and dirty data and made sure of no errors.</p><p>After doing some cleaning and preparation in power query, the data was saved and loaded into power bi.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Y3welqmVgGs-r8pkw_7l0Q.png" /></figure><p>At this point, data modeling was done in order to do some time intelligent calculations by creating a calendar table that consists of information such as day, month, and year. But before that i want to get a total number of successful booking.</p><p>I used the sum function to get the total revenue then i created a model to calculate the total booking, this i did by using the distinct count function</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FZsYv0ZARh4hn2RjYF1hVg.png" /></figure><p>Now, out of the total booking, i want to get the successful one. In the dataset, i have a coulumn called cancelled, where 0 represent successful booking and 1 represents unsuccessful bookings, i want to create a fuction that will count only the 0. To do this, i will use the countrow dax function.</p><p>Successful Bookings = CALCULATE(COUNTROWS(‘Cleaned Data’),’Cleaned Data’[Cancelled (0/1)] = 0)</p><p>This will filter and count the rows where the ‘Cancelled (0/1)’ column has a value of 0. So, the entire formula is essentially saying: “Count the rows in the ‘Cleaned Data’ table where the ‘Cancelled (0/1)’ column has a value of 0.”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/952/1*c4HUzhGcFuvD-f-NvufQ8g.png" /></figure><p>I also calculated for the Cancelled booking</p><p>Cancelled Bookings = CALCULATE(COUNTROWS(‘Cleaned Data’),’Cleaned Data’[Cancelled (0/1)] = 1)</p><p>I then used the card visualization to get</p><ol><li>Total sum of guests per room</li><li>Total revenue lost due to cancelled booking</li><li>Average night each guest spent</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/791/1*bGEnQG_izAV7uw73X_e9tg.png" /></figure><p>I used the sum and average function for the did</p><p>The next thing I did was to use visualization to address some of the objectives</p><p>First, i got the 5 best-performing countries by revenue, i also got the worst-performing countries by revenue</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/425/1*fMj4jAC7uTOPA-mDFz-Icg.png" /></figure><p>After sometime, this is how my dashboard looks</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/894/1*vxfSDbZWJirMCqFH-CGcCQ.png" /></figure><p>I have solved all the objectives using visualization but I want to make my Dashboard more visually appealing. This I will do later so let’s get to answering the objectives at the earlier part of this post.</p><p><strong>Addressing Objective, Analysis, and Recommendation</strong></p><p><strong>Booking Patterns:</strong></p><ul><li>What is the trend in booking patterns over time, and are there specific seasons or months with increased booking activity?</li><li>How does lead time vary across different booking channels, and is there a correlation between lead time and customer type?</li></ul><p><strong><em>Answer and Recommendation</em></strong></p><p>In terms of booking patterns, Splendor Hotels had more bookings in 2016 compared to 2015, 2014, and 2017. While it did well in 2016, there was a drop in booking in 2017. This is because of a decrease in bookings from online travel agents. This customer segment (Online Travel Agent) contributes to 80% of splendor hotel bookings. <strong>Therefore, <em>Splendor Hotels should prioritize strengthening their relationship with online booking agents. Splendor Hotels</em></strong></p><p>Q1 is the quarter with the highest number of bookings with January being the peak. These guest who book by this time check in between March to October. <strong><em>Since most booking comes in Q1, the business should increase its marking effort around Q3 of the previous year.</em></strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/982/1*LLmL_eIGdQaNoeaLfJ2mIA.png" /></figure><p><strong>Customer Behavior Analysis:</strong></p><ul><li>Which distribution channels contribute the most to bookings, and how does the average daily rate (ADR) differ across these channels?</li><li>Can we identify any patterns in the distribution of guests based on their country of origin, and how does this impact revenue?</li></ul><p><strong><em>Answer and Recommendation</em></strong></p><p>The distribution channel with the most bookings is the Online Travel Agent, it also has the highest average daily rate. <strong><em>As suggested earlier, the business should improve its relationship with online travel agents, if possible offer them incentives on bookings.</em></strong></p><p>The country with the most booking and revenue is Portugal. Portugal brings in 31% of total revenue (9 million dollars) to the hotel. The least performing country is Madagascar which brings in a mere 44 dollars. <strong><em>Splendor hotels should focus its marketing effort and in Portugal, UK, France, Spain and Germany but more effort on Portugal.</em></strong></p><p>Cancellation Analysis:</p><ul><li>What factors are most strongly correlated with cancellations, and can we predict potential cancellations based on certain variables?</li><li>How does the revenue loss from cancellations compare across different customer segments and distribution channels?</li></ul><p><strong><em>Answer and Recommendation</em></strong></p><p>In terms of cancellation, we have a total of 44,000 cancelled bookings agaisnt 75000 successful ones. The canceled booking is half that of successful ones. This is not good at all. On a closer look, i found out that majority of the cancelled bookings about 30,000 came from customers who made no deposit and more are in Portugal. <em>I recommend that the business request all guest to pat at least 50% or the room rate at the point of booking this will bring down the rate of cancelled bookings.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/322/1*MI5x9rttpty_g_CR4D2ZAw.png" /></figure><p>The customer group with the most revenue lost resulting to cancellation is the Transient group with a 12M loss out of a total of 13M</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/311/1*ZYWXk5wHVcXZuh0wTaXhJw.png" /></figure><p><strong>Revenue Optimization:</strong></p><ul><li>What is the overall revenue trend, and are there specific customer segments or countries contributing significantly to revenue?</li><li>Can we identify optimal pricing strategies based on the Average Daily Rate (ADR) for different customer types and distribution channels?</li></ul><p><strong><em>Answer and Recommendation</em></strong></p><p>Portugal, UK, France, Spain, and Germany is the country contributing the highest revenue, this revenue is coming from transient customers. So our splendor hotels marketing team should target mostly transient customers in these countries.</p><p><strong>Operational Efficiency:</strong></p><ul><li>What is the average length of stay for guests, and how does it differ based on booking channels or customer types?</li><li>Are there patterns in check-out dates that can inform staffing and resource allocation strategies?</li></ul><p><strong><em>Answer and Recommendation</em></strong></p><p>Splendor hotels have a total of 235,000 guests spending an average on 3 nights, since more nights mean more money, the hotel can run a promo thus; ‘’Get one night free when you book for 4 nights’’. Customers who booked from offline travel agents spend more nights (4) on average compared to other customers (3) except corporate customers who spend the least number of nights (2).</p><p>There is no check-out date in the data provided but check-in at Splendor Hotelis peaks between October and November hence the business should provide more resources and staffing during these periods</p><p>Comparison of Online and Offline Travel Agents:</p><ul><li>What is the revenue contribution of online travel agents compared to offline travel agents?</li><li>How do cancellation rates and revenue vary between bookings made through online and offline travel agents?</li></ul><p><strong><em>Answer and Recommendation</em></strong></p><p>Online travel agent brings in more revenue (17M) compared to offline travel agent that brings in just 7M.</p><p>Even though Online travel agent has more revenue, it still has more canceled bookings. Imagine if Splendor Hotel strengthens its relationship with online travel agents and implements the 50% deposit on booking, it will close the gap in canceled bookings and revenue loss.</p><p>The City hotel has 79K total bookings with 46K successful bookings and does 17M in revenue with an average of 3 nights. The resort has 40K bookings, 30k successful, an average of 4 nights, and 12M in revenue just 5M short of the city hotel in revenue which has more bookings. I think the resort has untapped potential, the business should market it more as people love spending more time there compared to the city hotel.</p><p>Finally My Dashboard (Pardon me, today’s dashboard looks so busy as I hope to capture a lot of insight)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sXKFbNxK03TiJpKZgOkvqw.png" /></figure><p>You can download the dashboard and data set here <br>Dashboard: <a href="https://drive.google.com/file/d/1wwnkrFe8WGdy7OiGrbtIVOBOFMNYmcsc/view?usp=sharing">https://drive.google.com/file/d/10_J2WPeoQaDREvRqU2Y_bhJz4qdJT_xl/view?usp=drive_link</a></p><p>Data Set: <a href="https://drive.google.com/drive/folders/1fVXLsP4nvgJJ4kxiUx92Dk8KUJtEgJTQ?usp=drive_link">https://drive.google.com/drive/folders/1fVXLsP4nvgJJ4kxiUx92Dk8KUJtEgJTQ?usp=drive_link</a></p><p>Pheww!</p><p>Did I do justice to the objective?</p><p>What would you have done differently?</p><p><em>Visualization isn’t just about numbers; it’s about stories. And in the world of Business, these stories empower us to see what’s happening in our business.</em></p><p><em>Follow my journey for more data-driven narratives.</em></p><p>Thank you</p><h3>About the Author</h3><p>Hello, I’m Goodnews, a passionate data enthusiast with a knack for turning numbers into insights. With a background in Sales, Product, and now Data, I’m dedicated to harnessing the power of data to drive informed decisions. Connect with me let’s embark on data-driven adventures together!</p><blockquote>Don’t forget to subscribe to</blockquote><blockquote>👉 <a href="https://medium.com/microsoft-power-bi">Power BI Publication</a></blockquote><blockquote>👉 <a href="https://medium.com/microsoft-power-bi/newsletters/microsoft-power-bi-weekly">Power BI Newsletter</a></blockquote><blockquote>and join our Power BI community</blockquote><blockquote>👉 <a href="https://linktr.ee/powerbi.masterclass">Power BI Masterclass</a></blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8505741028bb" width="1" height="1" alt=""><hr><p><a href="https://medium.com/microsoft-power-bi/analyzing-booking-data-for-splendor-hotel-groups-using-excel-and-power-bi-8505741028bb">Analyzing Booking Data for Splendor Hotel Groups Using Excel and Power BI</a> was originally published in <a href="https://medium.com/microsoft-power-bi">Power BI</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Analyzing HR And Sales Data Set With Excel + Dashboard]]></title>
            <link>https://medium.com/@webmaster406/analyzing-hr-and-sales-data-set-with-excel-dashboard-cdf95e632ec4?source=rss-6f59600fa4be------2</link>
            <guid isPermaLink="false">https://medium.com/p/cdf95e632ec4</guid>
            <dc:creator><![CDATA[Egemolu Goodnews]]></dc:creator>
            <pubDate>Thu, 23 Nov 2023 17:33:29 GMT</pubDate>
            <atom:updated>2024-03-25T07:34:34.403Z</atom:updated>
            <content:encoded><![CDATA[<p>Recently my Excel and data analysis skill was put to the test with a series of Data set. In this post, I will show you how I was able to conquer.</p><p>Let’s go</p><p>The data set you gave me has IF, Nested IFS, Round, Look UP, SumIF, Pivot, Bonus</p><p>I will start with the IF Table</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*kaOGNS8SHHyNq0Pm" /></figure><p>There are two tables here,</p><p>In the first column, I was asked to show High for the staff whose salary is greater than 100,000.</p><p>For this I used the following function; =IF(C5&gt;100000, “High”, “Low”)</p><p>NB: C5 is the cell that contains the salary.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*O0xhTwSayQq4qwnh" /></figure><p>The second cell asked that I show “Accountants who earn more than 25,000 =&gt; Check’’</p><p>For this I used the function; =IF(AND(B5=”Accountant”,C5&gt;25000),”Check”,”Not Checked”)</p><p>Then Third cell, i was asked to show check for Either Accountants or those who earn more than 50,000 . Here i used the the IF OR Function thus; =IF(OR(B5=”Accountant”,C5&gt;50000),”Check”,”Not Checked”)</p><p>Next, I was asked to get the average salary, this is the easiest as I used the average function thus; =AVERAGE(C5:C15)</p><p>NB: C5:C5 is the salary cell</p><p>In the second table, I was asked to show high salaries that are more than the Average Salary</p><p>I used the function. =IF(C23&gt;$D$17, “High”,”Low”).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*5-n6OgGO8UysknYH" /></figure><p>The next is the Nested IF function.</p><p>Next IF is an IF inside and IF for multiple condition</p><p>I was given a threshold of 49,000 and asked to show the difference if salary is greater than the threshold, show frowning Unicode if its less than thethreshold and happy unicode if its equal to the threshold.</p><p>For that i used the function =IF(C8&gt;$D$3,C8-$D$3,IF(C8&lt;$D$3,”😟”,”😊”)) for IF and =IFS(C8&gt;$D$3, C8-$D$3, C8&lt;$D$3, “😟”, C8=$D$3, “😊”) for IFS. I was alos asked to display the formula i used, for this, i used the formulatext function, =FORMULATEXT(D8)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*iLFSHLUQWVkqPEFI" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*VZ6gwejJEkve8qOH" /></figure><p>Next is the Round sheet</p><p>This is easy as I used the round-up and Round down function</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*b0vQwNH_D1HMFD72" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*noNnRv-NslOuBr_Z" /></figure><p>Next is LOOKUP</p><p>Here i used VLookUp and HLookup to get the percentage and compensation</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*QeJWXyyprHHVvz47" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*_H3nk3i9WtKcG4qR" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*lvSoywDnSqZKoQCA" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*X-WNdjP-kGUzKein" /></figure><p>Next is the SUMIFS</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Q4bCpxPKd5pwlEB8" /></figure><p>Here, I was asked to Summarize by criteria, by unique Department, and show the formulas</p><p>To get a unique department simply means making the departments appear once. For this, I used the unique formula. =UNIQUE(B5:B46,FALSE,FALSE)</p><p>With this, the Departments appeared once instead of appearing multiple times.</p><p>After that, I did the SUMIF and Average and count of employees for each of the departments.</p><p>Another request was made thus; For the combination of Department &amp; Position below calculate the following the below(make a dropdown list):</p><p>To get the drop down, i used the data validation function</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*bOttXIbyk-_a0snQ" /></figure><p>To be fair, there are many things I did here which cannot be explained in this post.</p><p>I also answered some bonus questions, pretty much mathematics and calculation.</p><p>Pivot Table</p><p>The Next thing I did was to create a pivot table using the data below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*6x_VDPSMz80lkW4F" /></figure><p>The first thing I did was to bring the table into power query for cleaning</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*sbilpN17RVkQjjPt" /></figure><p>Here I did a lot of job cleaning it to ensure there is no error in my data set. The picture below is cleaned data. If you look at the far right-hand side, you will see the cleaning activities I performed.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*uk9by45LDLt_XS15" /></figure><p>Next is the Pivot Table itself</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*PqMCeUWCIzbsV_M-" /></figure><p>The first thing I did was to get the profit from each country to see which country makes the most profit.</p><p>I also added a pivot chart which will be needed for my dashboard</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*_k_6oi6K_bW2icF9" /></figure><p>Next I moved to find out which product is more profitable, this will help the business know which product to invest in</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*8FR0qFil_K7Sblzb" /></figure><p>Next we move to get profit by segment</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*-VmMpBzmQRWWxzYf" /></figure><p>Dashboard</p><p>The Next thing is to put them into a Dashboard</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*dLQeDyC-fJK9TRc2" /></figure><p>This is what my Completed Dashboard looks like . NB: I zoomed out here</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*8SD4UIi8RKtsNj4m" /></figure><p>Next is to add my slicers to make this responsive and that will be all</p><p>Finally the dashboard</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*747MWgf0dxzgj0Gs" /></figure><p>Pheww</p><p><em>Follow my journey for more data-driven narratives.</em></p><p>Thank you</p><h3>About the Author</h3><p>Hello, I’m Goodnews, a passionate data enthusiast with a knack for turning numbers into insights. With a background in Sales, Product, and now Data, I’m dedicated to harnessing the power of data to drive informed decisions. Connect with me let’s embark on data-driven adventures together!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cdf95e632ec4" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Analyzing A Train Data Set To Draw Insight On Cancelled Departures using Power BI]]></title>
            <link>https://medium.com/microsoft-power-bi/analyzing-a-train-data-set-to-draw-insight-on-cancelled-departures-using-power-bi-59d4c1e4fb9f?source=rss-6f59600fa4be------2</link>
            <guid isPermaLink="false">https://medium.com/p/59d4c1e4fb9f</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[power-bi-tutorials]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-analytics]]></category>
            <dc:creator><![CDATA[Egemolu Goodnews]]></dc:creator>
            <pubDate>Sat, 11 Nov 2023 06:37:46 GMT</pubDate>
            <atom:updated>2024-03-25T07:35:01.830Z</atom:updated>
            <content:encoded><![CDATA[<p>Recently, I had the opportunity to analyze the dataset of one the busiest train light railways in London to draw insight into the reason for canceled departures and how to mitigate it. The task is as follows;</p><p>The “data” sheet contains an extract from the Docklands Light Railway’s KESTREL database. This data lists operational incidents that occurred on the Docklands Light Railway, their cause, and their impact on the train service. The impact is measured by the number of Departures (i.e. train journeys) that are canceled</p><p>Each incident is assigned to a Planner Group.</p><p>The Docklands Light Railway operates two fleets of trains — B92 stock and B2007 stock Your task is to produce a summary chart and table illustrating the number of service-affecting incidents caused by rolling stock faults on the B92 fleet only, by Period and problem code group. A service-affecting incident is one which causes at least one train to be canceled.</p><p>Now let’s get to work</p><p>The First thing I did was to import the data set into Power BI as it is my preferred Data Analysis tool.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ksKCSdhVq_880w0S" /></figure><p>Then i did some cleaning. I removed columns i feel wont be needed for my objective</p><p>I also filtered the vehicle type so i can have B92 only since that is my focus</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*fGBDxUupGqzzAFG8" /></figure><p>I then changed the Data type of each column to ensure accuracy</p><p>The next thing I did was to create a measure to count the non-service-affecting incident, i.e. the incident that did not lead to Departure loss. Since the incident that did not lead to departure loss is 0 on the departure loss column, I used the countax function.</p><p>Non Service Affecting = COUNTAX(FILTER(data, data[Departure Losses] = 0), 1)</p><p>Then I used the Sum function to get the total number of departures that were cancelled because of one incident or the other.</p><p>I expressed them using the ‘card’ visualization</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*P6_tRwuZC9HVxkXs" /></figure><p>The next thing I did was to visualize the incident by their planner group, period, and problems</p><p>Since the focus of this analysis is on incidents caused by rolling stock faults, here is my observation and analysis.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*THAI8v24kfxT7atR" /></figure><ol><li>Rolling Stock Faults and Departure Cancellations:</li></ol><p>Observation: Rolling stock faults caused the highest number of departure cancellations (1,850).</p><p>Analysis: This directly addresses the task by highlighting the impact of rolling stock faults on departure cancellations.</p><p>Most Occurring Problem Codes:</p><p>Observation: ATO and Doors issues are the most occurring problem codes.</p><p>Analysis: This provides additional insights into the specific problem codes contributing to departure cancellations, supporting the understanding of the nature of incidents.</p><p>Comparison of Departure Cancellations in 2014 and 2015:</p><p>Observation: More departure cancellations due to rolling stock issues in 2015 compared to 2014.</p><p>Analysis: This comparison adds a temporal perspective, indicating a trend shift between the two years.</p><p>Monthly Breakdown of Departure Losses:</p><p>Observation (2014): Highest departure losses in November and December.</p><p>Observation (2015): Highest departure losses in April and May.</p><p>Analysis: This provides a detailed breakdown by month, giving context to the seasonality of departure losses.</p><p>Recommendations</p><p>Based on my analysis, here are some recommendations that i think can help address the issues identified and potentially mitigate the impact of rolling stock faults on departure cancellations:</p><p>1. Rolling Stock Maintenance and Upgrades:</p><p>- DLR should Prioritize regular maintenance and upgrades for the rolling stock to minimize the occurrence of faults leading to departure cancellations.</p><p>2. ATO and Doors Issue Resolution:</p><p>- DLR should Focus on resolving issues related to ATO and doors, as they are identified as the most occurring problem codes. This could involve targeted maintenance, technological enhancements, or specific training for staff.</p><p>3. Enhanced Monitoring and Early Detection:</p><p>- An enhanced monitoring system to detect rolling stock faults early should be installed. This can enable proactive maintenance and prevent incidents from escalating to the point of departure cancellations.</p><p>4. Yearly Trend Analysis:</p><p>- Continue to conduct yearly trend analyses to identify any emerging patterns or changes in the nature and frequency of rolling stock faults. This can aid in adjusting maintenance schedules or implementing preventive measures.</p><p>5. Seasonal Preparedness:</p><p>- We should be prepare for the heightened risk of departure cancellations during festive months, such as November and December. Consider additional resources or contingency plans during these periods.</p><p>6. Investigate 2015 Peaks in April and May:</p><p>- Investigate the specific reasons behind the higher departure cancellations in April and May of 2015. Determine if there were specific events, conditions, or issues contributing to this trend.</p><p>10. Regular Review of Incident Data:</p><p>- Regularly review incident data and adjust strategies accordingly. The analysis should be an ongoing process to adapt to changing circumstances and continuously improve operational efficiency.</p><p>These recommendations aim to address the identified challenges and contribute to the overall improvement of service reliability, with a specific focus on reducing departure cancellations caused by rolling stock faults on the B92 fleet.</p><p>Here is the full result of my dashboard</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*15BQ8n-hfqWm8EKrS0LbUw.png" /></figure><p>I do hope I am able to address the objective to your satisfaction.</p><p>Pheww</p><p><em>Visualization isn’t just about numbers; it’s about stories. And in the world of Business, these stories empower us to see what’s happening in our business.</em></p><p><em>Follow my journey for more data-driven narratives.</em></p><p>Thank you</p><h3>About the Author</h3><p>Hello, I’m Goodnews, a passionate data enthusiast with a knack for turning numbers into insights. With a background in Sales, Product, and now Data, I’m dedicated to harnessing the power of data to drive informed decisions. Connect with me let’s embark on data-driven adventures together!</p><blockquote>Don’t forget to subscribe to</blockquote><blockquote>👉 <a href="https://medium.com/microsoft-power-bi">Power BI Publication</a></blockquote><blockquote>👉 <a href="https://medium.com/microsoft-power-bi/newsletters/microsoft-power-bi-weekly">Power BI Newsletter</a></blockquote><blockquote>and join our Power BI community</blockquote><blockquote>👉 <a href="https://linktr.ee/powerbi.masterclass">Power BI Masterclass</a></blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=59d4c1e4fb9f" width="1" height="1" alt=""><hr><p><a href="https://medium.com/microsoft-power-bi/analyzing-a-train-data-set-to-draw-insight-on-cancelled-departures-using-power-bi-59d4c1e4fb9f">Analyzing A Train Data Set To Draw Insight On Cancelled Departures using Power BI</a> was originally published in <a href="https://medium.com/microsoft-power-bi">Power BI</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Analyzing An SME Account Using Power BI]]></title>
            <link>https://medium.com/@webmaster406/analyzing-an-sme-account-using-power-bi-1e9bdde9da96?source=rss-6f59600fa4be------2</link>
            <guid isPermaLink="false">https://medium.com/p/1e9bdde9da96</guid>
            <dc:creator><![CDATA[Egemolu Goodnews]]></dc:creator>
            <pubDate>Fri, 03 Nov 2023 08:53:50 GMT</pubDate>
            <atom:updated>2024-03-25T07:35:28.329Z</atom:updated>
            <content:encoded><![CDATA[<p>Hi People, long time no analysis</p><p>So a friend reached out to me to help her analyze her accounts for the month of October to help her draw insights from her business. I told her to send the Excel sheet and got to work. It’s a pretty fast one, did not take me much time as the data set where almost clean.</p><p>First, I opened the data set in Excel to see what and how it looked like</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-eW09GFv-P6rKoh516LUZQ.png" /></figure><p>Well, seems it needs some cleaning</p><p>Next i brought it in into power BI for cleaning and transformation</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Co7aaqGYEnYhrLCbcJFrtQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ixWD4Xw_z2bjql3-SVCjAA.png" /></figure><p>Here I did the following</p><ol><li>Trimmed the data set</li><li>removed some stuff I don’t want</li><li>renamed the columns to what I want</li><li>changed data type</li><li>Replaced the null values with zero so I won’t lose any data</li></ol><p>Next, I loaded it into the Power BI Dashboard for visualization</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HuYtKPaaYd1mzihELfCSVg.png" /></figure><p>I created some measures to get</p><ol><li>Total customers</li><li>Total Jobs</li></ol><p>I did this, using the distinct function</p><p>Then I started designing my dashboard</p><p>After a few hours, my result came out. Isn’t it nice</p><p>Analysis result</p><ol><li>She had 55 Customers last month which gave her a total of 37 Jobs to do<br>2. She made over N600,000 in total jobs and spent just 83,0000<br>3. Investor, Yugo, student registration and Uchem Alvan were her top customer/revenue sources as they brought in 50,000, 50,000, 45,000, and 34,000 respectively<br>4. She spent most of your money last month (25,000) on training and upgrading<br>5. She made the most money on 17th October. You made 144,000 that day<br>6. The service that brought in the most money is frontal revamping</li></ol><h3>About the Author</h3><p>Hello, I’m Goodnews, a passionate data enthusiast with a knack for turning numbers into insights. With a background in Sales, Product and now Data, I’m dedicated to harnessing the power of data to drive informed decisions. Connect with me let’s embark on data-driven adventures together!</p><p><em>Visualization isn’t just about numbers; it’s about stories. And in the world of Business, these stories empower us to see whats hapenning in our business.</em></p><p><em>Follow my journey for more data-driven narratives.</em></p><p>Thank you</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1e9bdde9da96" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Unraveling HR Data: A Journey Through Visualization with Tableau]]></title>
            <link>https://medium.com/@webmaster406/unraveling-hr-data-a-journey-through-visualization-with-tableau-72305ca65679?source=rss-6f59600fa4be------2</link>
            <guid isPermaLink="false">https://medium.com/p/72305ca65679</guid>
            <dc:creator><![CDATA[Egemolu Goodnews]]></dc:creator>
            <pubDate>Thu, 19 Oct 2023 12:01:53 GMT</pubDate>
            <atom:updated>2024-03-25T07:36:04.908Z</atom:updated>
            <content:encoded><![CDATA[<p>I embarked on an enlightening journey to unravel HR data using the powerful tool, Tableau. In this post, I’ll share my experience and insights gained from analyzing HR data, showcasing how visualization transformed raw numbers into meaningful narratives.</p><p>For this task, the objective is simple</p><ol><li>How many people are in each job?</li><li>What is the Gender distribution?</li><li>What is the Age distribution?</li><li>What Jobs pay more?</li><li>Who are the top earners in each Job?</li></ol><p>Lets go</p><p>The first thing i did was to open the data in excel to take a peep at it and know if it needs cleaning.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*M93feT044nQKiYzoenLYlA.png" /></figure><p>The Data looks pretty clean to me</p><p>Next, I import my data into Tableau</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EWLIUjKWbuTng4Ff6XbDMA.png" /></figure><p>Above is what my imported Data looks like</p><p>Then I begin to answer the objective questions using Tableau</p><ol><li>How many people are in each job.</li></ol><p>Below, you can see the number of people in each job from Chocolatier which is 17 to Sales Rep which is 18.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LPWRLjFsbT-ro7FmIh3vFw.png" /></figure><p>I want to put this into a bar chart so it can be visually appealling</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*oR-Y9T1uHMK3kl5mGWIXmA.png" /></figure><p>It’s looking finer here</p><p>2. What is the gender distribution</p><p>For this, I will select the gender column and use the data count function to get the total number of staff for each gender.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*S2nm9F4tVfXuHehCZnfzVA.png" /></figure><p>We have 88 Females and 73 male</p><p>Let’s put it in a pie chart</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qvfc2lkttCb2KPpkBAXnEQ.png" /></figure><p>3. What is the age distribution</p><p>To get the age distribution, we will be using the ‘bin’ function</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_dAbCk_eUdrbCTP30Ta_SQ.png" /></figure><p>4. What Job Pays More</p><p>The highest-paid job is Product Manager</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*38jU_7qCBDGLVtdFc_VLcQ.png" /></figure><p>5. Who are the top earners in each job</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*K1NXKOySHdU0u4coOwQs4w.png" /></figure><p>From the Data we can see the top earners in each Job role</p><ol><li>Chocolatiers has Anaya Argawal as the top earner</li><li>Marketing Managers is Krish Rawat</li><li>Mrketing Specialist is Pari Gupta</li><li>Packaging Associate is Simon Kimberly</li><li>Product Manager is Zara Verma</li><li>Production Operators is Avani Iyer</li><li>Quality Control is Reyansh Rana</li><li>Research Analyst is Ryan sha</li><li>Research Scientist IS Anyah shah</li><li>Sales Rep is Kiara Batsha</li></ol><p>Finally the dashboard</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2UFu3UkzLl2QGs72378Xyg.png" /></figure><p>As I conclude this exploration, I am left with a profound realization: data, when visualized thoughtfully, has the power to transform organizations, one graph at a time. Tableau not only opened the doors to understanding my company’s HR landscape but also equipped me with a lens to envision a more informed and insightful future.</p><p><em>Visualization isn’t just about numbers; it’s about stories. And in the world of HR, these stories empower us to create workplaces where every individual counts and every voice is heard</em></p><p><em>Follow my journey for more data-driven narratives.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=72305ca65679" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Analyzing Sales Data Using Tableau]]></title>
            <link>https://medium.com/@webmaster406/analyzing-sales-data-using-tableau-9abaef86470b?source=rss-6f59600fa4be------2</link>
            <guid isPermaLink="false">https://medium.com/p/9abaef86470b</guid>
            <dc:creator><![CDATA[Egemolu Goodnews]]></dc:creator>
            <pubDate>Wed, 18 Oct 2023 07:20:48 GMT</pubDate>
            <atom:updated>2023-10-18T07:20:48.188Z</atom:updated>
            <content:encoded><![CDATA[<p>I decided to learn a business intelligence tool as part of my effort toward becoming a Data/Business Analyst. In this project, I used Tableau business intelligence software to analyze sales data.</p><p>The objective is to find</p><ol><li>The highest-selling country</li><li>The highest-selling staff</li><li>The highest-selling product</li></ol><p>The first thing I did was to get my dataset into Tableau</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*91U0utE--i-PsC8EAN5ZoQ.png" /></figure><p>Select Microsoft Excel from the left-hand side of the screen</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*trXkrRSWixIMXeQkFVhIlQ.png" /></figure><p>Tableau is actually easier than Power BI. Getting every one of my objectives is just a simple drag and drop.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GkeZFs-psVfGo-55LUoK5Q.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dQ7RT4qSw737wf9i_QZFPg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_UYTS-9yeS0HqVnUtYLwTA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GLob_WBVmON5yDY7Go37Sw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7ZmrF_wm2jyzjC-wMsNwWw.png" /></figure><p>Finally here is our dashboard</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Zljx7LUIgRoM-lqjPTR_-Q.png" /></figure><p>To Answer the objective questions</p><ol><li>The highest-selling country is Australia with a total of $1.1M in sales</li><li>The highest selling staff is Ches Bonnel with a total of $320,901 in sales</li><li>The highest-selling product is Smooth Slicky Salty which sold the most in India and the UK.</li></ol><p>Thank You</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9abaef86470b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A Journey in Customer Success Management: Analyzing Customer Success Data With Excel & Power BI]]></title>
            <link>https://medium.com/@webmaster406/a-journey-in-customer-success-management-analyzing-customer-success-data-with-excel-power-bi-6c9a2cd59839?source=rss-6f59600fa4be------2</link>
            <guid isPermaLink="false">https://medium.com/p/6c9a2cd59839</guid>
            <dc:creator><![CDATA[Egemolu Goodnews]]></dc:creator>
            <pubDate>Sun, 15 Oct 2023 15:23:30 GMT</pubDate>
            <atom:updated>2023-10-15T15:23:30.723Z</atom:updated>
            <content:encoded><![CDATA[<p>I was recently given a set of customer success data with the following instructions to analyze</p><p>Background</p><p>You are a Customer Success Manager for a luxury hotel chain. Your primary responsibility is to ensure that guests have exceptional experiences during their stay. This involves assisting with bookings, addressing any concerns or special requests, and ensuring that guests leave with a positive impression of the hotel.</p><ol><li>Given the provided customer feedback dataset for your hotel chain, calculate the average customer satisfaction score. Explain how this score reflects the overall guest experience.</li></ol><p>2. Write an email to the HR department recommending the termination of the worst-performing agent Provide specific reasons and examples to support your recommendation.</p><p>3. Evaluate the performance of each Customer Success Agent based on the provided customer feedback and performance metrics. Provide a brief summary for each agent, highlighting their strengths and areas for improvement. Consider how their performance contributes to the overall guest experience.</p><p>4. Identify two strategies that could be implemented to improve customer satisfaction and recommendation rates within the hotelchain. Explain how each strategy could positively impact the customer experience, and provide specific steps for implementation.</p><p>Let’s get to work</p><p>The first thing I did was to open the Data set and see what it looked like to know if it would need cleaning.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gGj3bRMISVXOITVlwjJSzw.png" /></figure><p>There is little or no cleaning to do but I still have to import it into power query to be sure</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WhunmGBLs3f3NDOtKRsRgg.png" /></figure><p>I added new columns, Merged two columns together, and removed null columns.</p><p>The next thing is to calculate the average customer satisfaction score. Now The Average Customer Satisfaction Score CSAT Score is given as the sum of 4&amp;5 ratings divided by the total sum of ratings X 100. To get the 4 &amp; 5 ratings, I used the Countif function to select the total number of 4-star and 5-star reviews</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6iHeskF_8W7NQkCzu2Ofmw.png" /></figure><p>This means that the number of ratings with 2 stars is 7, 3 stars is 9, 4 stars is 9, and 5 stars is 5.</p><p>Below is the CSAT Score</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YJi5zzkudSMw62N-344a8g.png" /></figure><p>Explanation: A good customer satisfaction score is between 40– 100 percent. For our analysis, the CSAT score is 46 which is a little above 40. Though it is not bad but its poor. The Hotel Should Improve its customer experience to Increase the CSAT score.</p><p>After, I imported the Data Into Power BI for analysis.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*64-0xxgNH5CroM4D1cTUEQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9MxUaOj01Gnt677xDyzodA.png" /></figure><p>2. Write an email to the HR department recommending the termination of the worst-performing agent Provide specific reasons and examples to support your recommendation. From our Analysis, the worst performig Rep is Sunday.</p><p>My Email will go thus;</p><p>Subject: Recommendation for Termination of Employee: [Employee Name]</p><p>Dear HR,</p><p>I hope this email finds you well. I am writing to formally recommend the termination of Sunday, who is currently employed as a Customer Success Agent at our hotel. This recommendation is based on a thorough evaluation of their performance, and I believe this decision is essential to maintain the high service standards we uphold at our establishment.</p><p><strong>Reasons for Recommendation:</strong></p><ol><li><strong>Consistently Low Customer Satisfaction Scores</strong></li></ol><p>Despite training and feedback, Sunday has consistently received low customer satisfaction scores. In fact, out of a Total of 9 dissatisfied customers we had, 7 were attended to by Sunday while all 7 Very Dissatisfied customers we had were attended to by Sunday. Several guests have reported dissatisfaction with their interactions, citing unhelpful responses and incompetence.</p><p><strong>2. Failure to Address Guests’ Concerns Adequately:</strong></p><p>On multiple occasions, Sunday has failed to address guests’ concerns effectively, leading to unresolved issues. This has resulted in guest dissatisfaction and negative online reviews, tarnishing our hotel’s reputation.</p><p><strong>3. Lack of Improvement Despite Training:</strong></p><p>Despite additional training and guidance provided to enhance communication and problem-solving skills, Sunday has shown minimal improvement in their performance. The lack of initiative to apply the training demonstrates a lack of commitment to their role.</p><p><strong>Specific Example:</strong></p><p>On 1st May 2023, a guest reported an Incident in her room where Sunday failed to resolve the issue satisfactorily, resulting in the guest’s dissatisfaction and request for compensation. She said and I quote ‘‘The agent’s responses were slow and sometimes confusing’’</p><p>Considering the consistent pattern of poor performance, lack of improvement despite training, and negative impact on guest satisfaction, I strongly recommend terminating Sunday’s employment with our Hotel. I believe this decision is necessary to preserve the reputation of our hotel and uphold the high-quality service our guests expect.</p><p>Please let me know if you require any additional information or documentation to support this recommendation. I appreciate your attention to this matter and trust that you will take appropriate action to ensure the continued excellence of our hotel’s services.</p><p>Thank you for your understanding and cooperation.</p><p>Warm regards,</p><p>Egemolu Goodnews</p><p>Customer Success Analyst</p><p>3. Evaluate the performance of each Customer Success Agent based on the provided customer feedback and performance metrics. Provide a brief summary for each agent, highlighting their strengths and areas for improvement. Consider how their performance contributes to the overall guestexperience.</p><ol><li>Sunday is the worst-performing Agent with a total of 12 Negative reviews and 0 positive reviews. He should be trained on the company’s product and how to communicate effectively and clearly.</li><li>UJU is the Best-performing Agent, all our Positive ratings came from here.</li><li>The remaining 2 were Average at best.</li></ol><p>4. Identify two strategies that could be implemented to improve customer satisfaction and recommendation rates within the hotel chain. Explain how each strategy could positively impact customer experience, and provide specific steps for implementation</p><p><strong>Strategy 1: Personalized Guest Experience Program</strong></p><p><em>Explanation:</em> Implementing a personalized guest experience program can significantly enhance customer satisfaction and recommendation rates. By tailoring services to individual guest preferences, we create memorable experiences that lead to positive reviews and repeat business.</p><p><em>Implementation Steps:</em></p><ol><li><strong>Guest Preference Collection:</strong></li></ol><ul><li>Develop a digital survey sent to guests before their arrival, asking about preferences such as room temperature, amenities, dietary restrictions, and special occasions.</li><li>Train staff to engage with guests upon arrival, asking about preferences in person and taking notes for future reference.</li></ul><ol><li><strong>Data Analysis and Segmentation:</strong></li></ol><ul><li>Utilize CRM software to store and analyze guest preferences systematically.</li><li>Segment guests based on their preferences, allowing for targeted service customization.</li></ul><ol><li><strong>Personalized Services:</strong></li></ol><ul><li>Assign a dedicated staff member (Personal Guest Experience Coordinator) to each guest, responsible for ensuring personalized experiences throughout their stay.</li><li>Offer personalized amenities, such as preferred beverages, room décor, or spa treatments, based on guest preferences.</li></ul><ol><li><strong>Post-Stay Follow-Up:</strong></li></ol><ul><li>Send a personalized thank-you email post-stay, referencing specific preferences and experiences.</li><li>Encourage guests to leave reviews by expressing gratitude for their feedback and explaining how their input helps in further personalizing services.</li></ul><p><em>Positive Impact:</em> Guests feel valued when their preferences are acknowledged and catered to, leading to increased satisfaction, positive online reviews, and higher chances of recommending the hotel to others.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ETPtRBnNa_YkfTFFQTqvKg.png" /></figure><p><em>Thank you for being a part of my journey toward excellence in guest satisfaction</em></p><h3>About the Author</h3><p>Hello, I’m Goodnews, a passionate data enthusiast with a knack for turning numbers into insights. With a background in Sales, Product and now Data, I’m dedicated to harnessing the power of data to drive informed decisions. Connect with me let’s embark on data-driven adventures together!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6c9a2cd59839" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>