<?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 Wilma Lapuz on Medium]]></title>
        <description><![CDATA[Stories by Wilma Lapuz on Medium]]></description>
        <link>https://medium.com/@WilmaLapuz?source=rss-8398c48e812e------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*Qj10mRzd1buyIRYIiP70EQ.jpeg</url>
            <title>Stories by Wilma Lapuz on Medium</title>
            <link>https://medium.com/@WilmaLapuz?source=rss-8398c48e812e------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 06:36:47 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@WilmaLapuz/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[Automated Entry Form and Database with Easy/Printable Invoice Using Google Sheets]]></title>
            <link>https://medium.com/@WilmaLapuz/automated-entry-form-and-database-with-easy-printable-invoice-using-google-sheets-b4438803f821?source=rss-8398c48e812e------2</link>
            <guid isPermaLink="false">https://medium.com/p/b4438803f821</guid>
            <category><![CDATA[free-database]]></category>
            <category><![CDATA[automated-form]]></category>
            <category><![CDATA[data-analyst]]></category>
            <category><![CDATA[the-real-data-analyst]]></category>
            <category><![CDATA[google-app-script]]></category>
            <dc:creator><![CDATA[Wilma Lapuz]]></dc:creator>
            <pubDate>Tue, 07 Mar 2023 08:48:45 GMT</pubDate>
            <atom:updated>2023-03-07T08:57:26.318Z</atom:updated>
            <content:encoded><![CDATA[<p>Upon finishing Data Analyst Pathway course offered by Development Academy of the Philippines, the next question is what’s next?</p><p>I am loud and proud to say that I am one of the pioneer graduate of Project SPARTA and not only that, I am one of their first interns in their Internship Program. A continuity of learning was offered to chosen graduates. Isn’t it nice?</p><p>In the SPARTA internship we were grouped into Marketing and Partnership/Events. I was assigned to the latter. My task is to track the partnership’s engagements on their way to signing MOUs, monitor partners communication strategies through their social media and create topics for challenges on Cauayan’s Open Data Hackathon.</p><p>One of the challenging parts of being a SPARTA Intern is to understand the problem that the given task is trying to achieve. As a person that is completely from a different background, I need to look into their ideas to understand where they are coming from. And I was blessed to be under Daniel Rosero who does not shush my idea away but almost and always accepts it with compliments.</p><p>As always, forever grateful <a href="https://www.linkedin.com/in/daniel-rosero-a7255a206/">Daniel</a>.</p><p>The first task given to me in my SPARTA Internship is to create a form that will showcase everything about the stakeholders. I created two forms: without a database and with an automated entry form/database for an easy and printable report sheet. Remembering that gives me a light bulb moment!</p><p>After CCBI’s seminar, I thought to myself that data entry, automation or even digitalization of businesses in the Philippines is still out of reach, it is a long-long way to pave. And it is my <em>light bulb moment</em> to recreate the form I made as SPARTA Intern for an automated entry form/database to replace the manual task of truckers in the Philippines in managing their fleets.</p><p>As a former import coordinator of one the largest/oldest haulier companies in Singapore, I am blessed that I saw how powerful it is to have a data entry system to track this kind of business.</p><p>This form will give the owners/coordinators the summary of each job and the ease to print out an invoice for their customer.</p><figure><img alt="Automated Entry Form and Database with Easy/Printable Invoice Using Google Sheets" src="https://cdn-images-1.medium.com/max/1024/1*zf8YOdzeufBS0ow3dMXWFA.png" /><figcaption>Automated Entry Form and Database with Easy/Printable Invoice</figcaption></figure><figure><img alt="Automated Entry Form and Database with Easy/Printable Invoice Using Google Sheets" src="https://cdn-images-1.medium.com/max/1024/1*D5aXKXqKXN8W7qu_yv6SGA.png" /><figcaption>Automated Entry Form and Database with Easy/Printable Invoice Using Google Sheets</figcaption></figure><p>This is FREE. If you have a google account you can run the script below using an Extension called Apps Script, copy and paste below.</p><pre>//TO CLEAR THE FORM<br>function clearForm() {<br>  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(&quot;FLEET TRACKER&quot;);<br>  const ui = SpreadsheetApp.getUi();<br>  const response = ui.alert(&quot;Reset Confirmation&quot;, &#39;Do you want to reset this form?&#39;, ui.ButtonSet.YES_NO);<br>  <br>  if (response == ui.Button.YES) {<br>    sheet.getRangeList([<br>      &quot;C4&quot;, &quot;C7&quot;, &quot;C9&quot;, &quot;C11&quot;, &quot;C13&quot;, &quot;C15&quot;, &quot;C17&quot;, &quot;C19&quot;, &quot;C21&quot;, &quot;C23&quot;, &quot;C25&quot;, &quot;C28&quot;, &quot;C29&quot;,<br>      &quot;C30&quot;, &quot;C31&quot;, &quot;C33&quot;, &quot;C34&quot;, &quot;C35&quot;, &quot;C36&quot;, &quot;C37&quot;<br>    ]).clearContent().setBackground(&#39;#FFFFFF&#39;).setBorder(true, true, true, true, false, false);<br>  }<br>}<br><br>//TO DATA VALIDATION FOR DATA ENTRY<br>function validateEntry(){<br>var myGooglSheet = SpreadsheetApp.getActiveSpreadsheet();<br>var WMLUserForm = myGooglSheet.getSheetByName(&quot;FLEET TRACKER&quot;);<br>var ui = SpreadsheetApp.getUi();<br>var cellsToCheck = [&quot;C7&quot;, &quot;C9&quot;, &quot;C11&quot;, &quot;C13&quot;, &quot;C15&quot;, &quot;C17&quot;, &quot;C19&quot;, &quot;C21&quot;, &quot;C23&quot;, &quot;C25&quot;, &quot;C28&quot;, &quot;C29&quot;, &quot;C30&quot;, &quot;C31&quot;, &quot;C33&quot;, &quot;C34&quot;, &quot;C35&quot;, &quot;C36&quot;, &quot;C37&quot;];<br><br>for (var i = 0; i &lt; cellsToCheck.length; i++) {<br>var currentCell = WMLUserForm.getRange(cellsToCheck[i]);<br>currentCell.setBackground(&#39;#FFFFFF&#39;);<br>if (currentCell.isBlank()) {<br>var errorMessage;<br>switch (cellsToCheck[i]) {<br>case &quot;C7&quot;:<br>errorMessage = &quot;Please enter BL Number / Booking Number.&quot;;<br>break;<br>case &quot;C9&quot;:<br>errorMessage = &quot;Select Import/Export.&quot;;<br>break;<br>case &quot;C11&quot;:<br>errorMessage = &quot;Please enter Client Name.&quot;;<br>break;<br>case &quot;C13&quot;:<br>errorMessage = &quot;Please enter the Contact Person.&quot;;<br>break;<br>case &quot;C15&quot;:<br>errorMessage = &quot;Please enter the Mobile Number / Email Address.&quot;;<br>break;<br>case &quot;C17&quot;:<br>errorMessage = &quot;Please enter Vessel Name / Voyage Number.&quot;;<br>break;<br>case &quot;C19&quot;:<br>errorMessage = &quot;Please enter Focal Person&#39;s Name.&quot;;<br>break;<br>case &quot;C21&quot;:<br>errorMessage = &quot;Please enter Pick-Up Address (including unit number and postal code), Contact Person and Mobile Number.&quot;;<br>break;<br>case &quot;C23&quot;:<br>errorMessage = &quot;Please enter Delivery Address (including unit number and postal code), Contact Person and Mobile Number.&quot;;<br>break;<br>case &quot;C25&quot;:<br>errorMessage = &quot;Please key-in Mobile Number&quot;;<br>break;<br>case &quot;C28&quot;:<br>errorMessage = &quot;Please select...&quot;;<br>break;<br>default:<br>errorMessage = &quot;Please fill out all required fields.&quot;;<br>break;<br>}<br>ui.alert(errorMessage);<br>currentCell.activate();<br>currentCell.setBackground(&#39;#FF0000&#39;);<br>return false;<br>}<br>}<br>return true;<br>}<br><br>//SAVING THE DATA INTO THE DATABASE<br>function submitData() {<br>  const ss = SpreadsheetApp.getActiveSpreadsheet();<br>  const formSheet = ss.getSheetByName(&quot;FLEET TRACKER&quot;);<br>  const dataSheet = ss.getSheetByName(&quot;Database&quot;);<br>  const ui = SpreadsheetApp.getUi();<br>  <br>  const response = ui.alert(&quot;Submit&quot;, &quot;Do you want to save this data?&quot;, ui.ButtonSet.YES_NO);<br>  if (response == ui.Button.NO) return;<br>  <br>  if (!validateEntry()) return;<br>  <br>  const data = [<br>    formSheet.getRange(&quot;C7&quot;).getValue(),<br>    formSheet.getRange(&quot;C9&quot;).getValue(),<br>    formSheet.getRange(&quot;C11&quot;).getValue(),<br>    formSheet.getRange(&quot;C13&quot;).getValue(),<br>    formSheet.getRange(&quot;C15&quot;).getValue(),<br>    formSheet.getRange(&quot;C17&quot;).getValue(),<br>    formSheet.getRange(&quot;C19&quot;).getValue(),<br>    formSheet.getRange(&quot;C21&quot;).getValue(),<br>    formSheet.getRange(&quot;C23&quot;).getValue(),<br>    formSheet.getRange(&quot;C25&quot;).getValue(),<br>    formSheet.getRange(&quot;C28&quot;).getValue(),<br>    formSheet.getRange(&quot;C29&quot;).getValue(),<br>    formSheet.getRange(&quot;C30&quot;).getValue(),<br>    formSheet.getRange(&quot;C31&quot;).getValue(),<br>    formSheet.getRange(&quot;C33&quot;).getValue(),<br>    formSheet.getRange(&quot;C34&quot;).getValue(),<br>    formSheet.getRange(&quot;C35&quot;).getValue(),<br>    formSheet.getRange(&quot;C36&quot;).getValue(),<br>    formSheet.getRange(&quot;C37&quot;).getValue(),<br>    new Date(),<br>    Session.getActiveUser().getEmail()<br>  ];<br>  dataSheet.appendRow(data);<br><br> formSheet.getRangeList([<br>      &quot;C4&quot;, &quot;C7&quot;, &quot;C9&quot;, &quot;C11&quot;, &quot;C13&quot;, &quot;C15&quot;, &quot;C17&quot;, &quot;C19&quot;, &quot;C21&quot;, &quot;C23&quot;, &quot;C25&quot;, &quot;C28&quot;, &quot;C29&quot;,<br>      &quot;C30&quot;, &quot;C31&quot;, &quot;C33&quot;, &quot;C34&quot;, &quot;C35&quot;, &quot;C36&quot;, &quot;C37&quot;<br>    ]).clearContent().setBackground(&#39;#FFFFFF&#39;).setBorder(true, true, true, true, false, false);<br>}<br><br><br><br> //RETRIEVING DATA FROM THE DATABASE<br>function searchRecord() {<br>  <br>  var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); <br>  var WMLUserForm= myGooglSheet.getSheetByName(&quot;FLEET TRACKER&quot;); <br>  var datasheet = myGooglSheet.getSheetByName(&quot;Database&quot;); <br>    <br>  var str       = WMLUserForm.getRange(&quot;C4&quot;).getValue();<br>  var values    = datasheet.getDataRange().getValues(); <br>  var valuesFound=false; <br>  <br>  for (var i = 0; i&lt;values.length; i++) <br>    {<br>    var rowValue = values[i]; <br>   <br>    if (rowValue[0] == str) {<br>           <br>      WMLUserForm.getRange(&quot;C7&quot;).setValue(rowValue[0]);<br>      WMLUserForm.getRange(&quot;C9&quot;).setValue(rowValue[1]);<br>      WMLUserForm.getRange(&quot;C11&quot;).setValue(rowValue[2]);<br>      WMLUserForm.getRange(&quot;C13&quot;).setValue(rowValue[3]);<br>      WMLUserForm.getRange(&quot;C15&quot;).setValue(rowValue[4]);<br>      WMLUserForm.getRange(&quot;C17&quot;).setValue(rowValue[5]);<br>      WMLUserForm.getRange(&quot;C19&quot;).setValue(rowValue[6]);<br>      WMLUserForm.getRange(&quot;C21&quot;).setValue(rowValue[7]);<br>      WMLUserForm.getRange(&quot;C23&quot;).setValue(rowValue[8]);<br>      WMLUserForm.getRange(&quot;C25&quot;).setValue(rowValue[9]);<br>      WMLUserForm.getRange(&quot;C28&quot;).setValue(rowValue[10]);<br>      WMLUserForm.getRange(&quot;C29&quot;).setValue(rowValue[11]);<br>      WMLUserForm.getRange(&quot;C30&quot;).setValue(rowValue[12]);<br>      WMLUserForm.getRange(&quot;C31&quot;).setValue(rowValue[13]);<br>      WMLUserForm.getRange(&quot;C33&quot;).setValue(rowValue[14]);<br>      WMLUserForm.getRange(&quot;C34&quot;).setValue(rowValue[15]);<br>      WMLUserForm.getRange(&quot;C35&quot;).setValue(rowValue[16]);<br>      WMLUserForm.getRange(&quot;C36&quot;).setValue(rowValue[17]);<br>      WMLUserForm.getRange(&quot;C37&quot;).setValue(rowValue[18]);<br>      return; //come out from the search function<br>      <br>      }<br>  }<br> <br>if(valuesFound==false){<br>  var ui = SpreadsheetApp.getUi();<br>  ui.alert(&quot;No record found!&quot;);<br> }<br> <br>}<br><br>//EDITING RECORD <br>function editRecord() {<br>  <br>  var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); <br>  var WMLUserForm= myGooglSheet.getSheetByName(&quot;FLEET TRACKER&quot;); <br>  var datasheet = myGooglSheet.getSheetByName(&quot;Database&quot;); <br> <br>  var ui = SpreadsheetApp.getUi();<br>  <br>  var response = ui.alert(&quot;Submit&quot;, &#39;Do you want to update the data?&#39;,ui.ButtonSet.YES_NO);<br> <br> if (response == ui.Button.NO) <br> {return;<br> } <br>    <br>  var str       = WMLUserForm.getRange(&quot;C4&quot;).getValue();<br>  var values    = datasheet.getDataRange().getValues(); <br>  <br>  var valuesFound=false; <br>  <br>  for (var i = 0; i&lt;values.length; i++) <br>    {<br>    var rowValue = values[i]; <br>   <br>    if (rowValue[0] == str) {<br>      <br>      var  iRow = i+1; //identify the row number<br> <br>      datasheet.getRange(iRow,1).setValue(WMLUserForm.getRange(&quot;C7&quot;).getValue()); <br>      datasheet.getRange(iRow,2).setValue(WMLUserForm.getRange(&quot;C9&quot;).getValue()); <br>      datasheet.getRange(iRow,3).setValue(WMLUserForm.getRange(&quot;C11&quot;).getValue()); <br>      datasheet.getRange(iRow,4).setValue(WMLUserForm.getRange(&quot;C13&quot;).getValue()); <br>      datasheet.getRange(iRow,5).setValue(WMLUserForm.getRange(&quot;C15&quot;).getValue()); <br>      datasheet.getRange(iRow,6).setValue(WMLUserForm.getRange(&quot;C17&quot;).getValue()); <br>      datasheet.getRange(iRow,7).setValue(WMLUserForm.getRange(&quot;C19&quot;).getValue()); <br>      datasheet.getRange(iRow,8).setValue(WMLUserForm.getRange(&quot;C21&quot;).getValue()); <br>      datasheet.getRange(iRow,9).setValue(WMLUserForm.getRange(&quot;C23&quot;).getValue()); <br>      datasheet.getRange(iRow,10).setValue(WMLUserForm.getRange(&quot;C25&quot;).getValue()); <br>      datasheet.getRange(iRow,11).setValue(WMLUserForm.getRange(&quot;C28&quot;).getValue()); <br>      datasheet.getRange(iRow,12).setValue(WMLUserForm.getRange(&quot;C29&quot;).getValue()); <br>      datasheet.getRange(iRow,13).setValue(WMLUserForm.getRange(&quot;C30&quot;).getValue()); <br>      datasheet.getRange(iRow,14).setValue(WMLUserForm.getRange(&quot;C31&quot;).getValue()); <br>      datasheet.getRange(iRow,15).setValue(WMLUserForm.getRange(&quot;C33&quot;).getValue()); <br>      datasheet.getRange(iRow,16).setValue(WMLUserForm.getRange(&quot;C34&quot;).getValue()); <br>      datasheet.getRange(iRow,17).setValue(WMLUserForm.getRange(&quot;C35&quot;).getValue()); <br>      datasheet.getRange(iRow,18).setValue(WMLUserForm.getRange(&quot;C36&quot;).getValue()); <br>      datasheet.getRange(iRow,19).setValue(WMLUserForm.getRange(&quot;C37&quot;).getValue()); <br>   <br>      datasheet.getRange(iRow, 22).setValue(new Date()).setNumberFormat(&#39;yyyy-mm-dd h:mm&#39;); <br>      datasheet.getRange(iRow, 23).setValue(Session.getActiveUser().getEmail()); <br>      ui.alert(&#39; &quot;Data updated for - Emp #&#39; + WMLUserForm.getRange(&quot;C7&quot;).getValue() +&#39; &quot;&#39;);<br>  <br>    <br>  WMLUserForm.getRange(&quot;C4&quot;).clear();<br>  WMLUserForm.getRange(&quot;C7&quot;).clear();<br>  WMLUserForm.getRange(&quot;C9&quot;).clear();<br>  WMLUserForm.getRange(&quot;C11&quot;).clear();<br>  WMLUserForm.getRange(&quot;C13&quot;).clear();<br>  WMLUserForm.getRange(&quot;C15&quot;).clear();<br>  WMLUserForm.getRange(&quot;C17&quot;).clear();<br>  WMLUserForm.getRange(&quot;C19&quot;).clear();<br>  WMLUserForm.getRange(&quot;C21&quot;).clear();<br>  WMLUserForm.getRange(&quot;C23&quot;).clear();<br>  WMLUserForm.getRange(&quot;C25&quot;).clear();<br>  WMLUserForm.getRange(&quot;C28&quot;).clear();<br>  WMLUserForm.getRange(&quot;C29&quot;).clear();<br>  WMLUserForm.getRange(&quot;C30&quot;).clear();<br>  WMLUserForm.getRange(&quot;C31&quot;).clear();<br>  WMLUserForm.getRange(&quot;C33&quot;).clear();<br>  WMLUserForm.getRange(&quot;C34&quot;).clear();<br>  WMLUserForm.getRange(&quot;C35&quot;).clear();<br>  WMLUserForm.getRange(&quot;C36&quot;).clear();<br>  WMLUserForm.getRange(&quot;C37&quot;).clear();<br><br><br>  WMLUserForm.getRange(&quot;C4&quot;).setBorder(true, true, true, true, false, false);<br>  WMLUserForm.getRange(&quot;C7&quot;).setBorder(true, true, true, true, false, false);<br>  WMLUserForm.getRange(&quot;C9&quot;).setBorder(true, true, true, true, false, false);<br>  WMLUserForm.getRange(&quot;C11&quot;).setBorder(true, true, true, true, false, false);<br>  WMLUserForm.getRange(&quot;C13&quot;).setBorder(true, true, true, true, false, false);<br>  WMLUserForm.getRange(&quot;C15&quot;).setBorder(true, true, true, true, false, false);<br>  WMLUserForm.getRange(&quot;C17&quot;).setBorder(true, true, true, true, false, false);<br>  WMLUserForm.getRange(&quot;C19&quot;).setBorder(true, true, true, true, false, false);<br>  WMLUserForm.getRange(&quot;C21&quot;).setBorder(true, true, true, true, false, false);<br>  WMLUserForm.getRange(&quot;C23&quot;).setBorder(true, true, true, true, false, false);<br>  WMLUserForm.getRange(&quot;C25&quot;).setBorder(true, true, true, true, false, false);<br>  WMLUserForm.getRange(&quot;C28&quot;).setBorder(true, true, true, true, false, false);<br>  WMLUserForm.getRange(&quot;C29&quot;).setBorder(true, true, true, true, false, false);<br>  WMLUserForm.getRange(&quot;C30&quot;).setBorder(true, true, true, true, false, false);<br>  WMLUserForm.getRange(&quot;C31&quot;).setBorder(true, true, true, true, false, false);<br>  WMLUserForm.getRange(&quot;C33&quot;).setBorder(true, true, true, true, false, false);<br>  WMLUserForm.getRange(&quot;C34&quot;).setBorder(true, true, true, true, false, false);<br>  WMLUserForm.getRange(&quot;C35&quot;).setBorder(true, true, true, true, false, false);<br>  WMLUserForm.getRange(&quot;C36&quot;).setBorder(true, true, true, true, false, false);<br>  WMLUserForm.getRange(&quot;C37&quot;).setBorder(true, true, true, true, false, false);<br><br> <br>      valuesFound=true;<br>      return; <br>      }<br>  }<br> <br>if(valuesFound==false){<br>  var ui = SpreadsheetApp.getUi();<br>  ui.alert(&quot;No record found!&quot;);<br> }<br> <br>}</pre><p>You can comment here or email me to send you a copy of this form or if you want me to customize some parts just for you.</p><p>This is what being an analyst is, analysts are not just all about data wrangling, or creating a dashboard or detecting trends and patterns. Analysts are here to help solve real-life problems using available (free or paid) tools for the betterment of the businesses that we can pass to next generations.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b4438803f821" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[One Love: As a Customs Broker]]></title>
            <link>https://medium.com/@WilmaLapuz/one-love-d87660b62205?source=rss-8398c48e812e------2</link>
            <guid isPermaLink="false">https://medium.com/p/d87660b62205</guid>
            <category><![CDATA[customs-brokers]]></category>
            <category><![CDATA[customs-brokers-ph]]></category>
            <category><![CDATA[freight-analyst]]></category>
            <category><![CDATA[logistics-analytics]]></category>
            <category><![CDATA[ccbi-sg-chapter]]></category>
            <dc:creator><![CDATA[Wilma Lapuz]]></dc:creator>
            <pubDate>Mon, 27 Feb 2023 09:05:49 GMT</pubDate>
            <atom:updated>2023-02-28T02:29:42.654Z</atom:updated>
            <content:encoded><![CDATA[<p>Rekindling my CHILDHOOD DREAM… TO BE A CUSTOMS BROKER</p><h4>WILMA</h4><p>What do you want to become when you grow up? The typical answer would be a teacher, a doctor or an engineer. But as early as 10 years old, I knew I wanted to be a Customs Broker. My childhood dream was pursued by taking a Bachelor’s Degree in Customs Administration. When I was in my college days I fell in love learning Tariff and Customs Law and Computation. My ONE love! Have I told you that I got 91% in Computation in the customs broker board exam? Naks! Tooting my horn!</p><h4>CCBI</h4><p>I am delighted to announce that after many years living abroad, the Chamber of Customs Broker (Accredited Professional Organization of Philippine’s Professional Regulation Commission) created its Singapore Chapter. And my name was inked in history as one of its founding officers.</p><p><strong><em>I am blessed to be appointed as PRO</em></strong> and as one of Singapore Chapter officers, we will do our best to help and be a contributor in developing and enhancing Customs Broker as a reputable profession in and out of the Philippines.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lzWE6dMjkN0sMGAai_fPFg.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9xdGze0QkkbEgYdLq_a7Nw.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MBHlVQv2zxCv3YSQNwJ2kQ.jpeg" /><figcaption>CCBI SINGAPORE CHAPTER —APPOINTED OFFICERS</figcaption></figure><h3>FREIGHT/LOGISTICS ANALYST</h3><p>As professionals we need to upgrade our skills and being an analyst is an outstanding way to develop our brokerage expertise.</p><p>Below is the career map shown by Mr. Roger Lee in CCBI Goes International:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PThuMNpKKPGVFsL1jhMqyQ.jpeg" /></figure><p>Most of the companies here in Singapore are looking not only for a Customer Service or Coordinator but they are eyeing people that possess knowledge on how to handle tools like pivot table for reporting or data handling/tracking.</p><h4>CUSTOMS BROKERS OF THE FUTURE</h4><p>As I have said in this post titled <a href="https://medium.com/@WilmaLapuz/evolution-of-logistics-analyst-22b70b7a3320"><em>Evolution of Logistics Analyst</em></a>, analyst is the new and best thing not only for career but also for businesses involved in logistics/warehousing/freight forwarding businesses.</p><p>And I do hope that in the near future, logistics, supply chain, brokerage companies will be aware of the power of data they own right now and how to harness it to solve their problems and how to take advantage to increase a firm’s profit.</p><p>For Customs Brokers in the Philippines that want to kickstart their upskilling or retooling, you can check-out <a href="https://www.facebook.com/datasenseph"><em>DataSense Analytics</em></a> or <a href="https://www.facebook.com/projectSPARTAPH"><em>Project SPARTA</em></a> for free courses with certificates and for those who have the budget to learn, you can look for <a href="https://www.facebook.com/Eskwelabs">Eskwelabs</a>!</p><p>That’s all for now! See you in the FUTURE FELLOW CUSTOMS BROKERS!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d87660b62205" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Power BI Dashboard that Summarises HR Records]]></title>
            <link>https://medium.com/@WilmaLapuz/power-bi-dashboard-that-summarises-hr-records-ed7c8095f331?source=rss-8398c48e812e------2</link>
            <guid isPermaLink="false">https://medium.com/p/ed7c8095f331</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[power-bi-dashboard]]></category>
            <category><![CDATA[hr-dashboard]]></category>
            <category><![CDATA[power-bi-essentials]]></category>
            <category><![CDATA[visualization]]></category>
            <dc:creator><![CDATA[Wilma Lapuz]]></dc:creator>
            <pubDate>Thu, 16 Feb 2023 10:15:52 GMT</pubDate>
            <atom:updated>2023-02-16T10:15:52.281Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Power BI Essentials by DataSense Analytics</strong></p><p>There’s a lot of FREE webinars/training online and FREE tools to explore and that is the main reason why a huge number of people were lost and didn’t know where to begin their DATA journey. I am a bit picky now with training/webinars. Let us just say that there are numerous cr*p contents that will waste your time. Some free training is after your data, you are just a number to flaunt to their partners or sponsors.</p><p>Well, Power BI Essentials got me hooked! I am glued to our screen for more than three hours. Mr. Abraham Avila Jr was the trainer for that day and he taught us from installing Microsoft Power BI up to building a dashboard solution for the HR team.</p><p>After the training this is the dashboard created by Sir Abs:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6tKHArrRHKD6lh03bnTpVA.png" /><figcaption><strong>DataSense Analytics Sample Dashboard</strong></figcaption></figure><p>For us to receive a certificate we need to submit an enhanced dashboard to fulfil further the task and objectives given below:</p><figure><img alt="PowerBI HR Dataset" src="https://cdn-images-1.medium.com/max/1024/1*szwBUQCh129AiQRyNjCMuQ.png" /><figcaption>Power BI Desktop Essentials</figcaption></figure><p>For Apple users like me, you can access the free Power BI tool through VMware.</p><h4>Dataset can be found here: <a href="https://drive.google.com/drive/folders/1_QBcWGjJSrtnzQZ7Zy_4byaHtyHsBSTv?usp=share_link">Power BI Desktop Essentials</a></h4><h4>Data Cleaning:</h4><p>Always start with the first column.</p><p>— LastName: Trimmed, Capitalize Each Word</p><p>— FirstName: Trimmed, Capitalize Each Word</p><p>— MiddleInitial: Trimmed, Capitalize Each Word</p><p>— EmployeeID: Changed to Text</p><p>— PositionID / DepartmentID: No changes</p><p>— Manager: No Changes</p><p>— DateofHire: Changed to Date Format</p><p>— Salary: Changed to currency</p><p>— DOB: Changed to Date Format</p><h4>Data Modeling:</h4><p>Power BI is intelligent enough to know the primary keys in each table you uploaded. Here the primary key for the Department Table that connects HR_Data is DepartmentID and Position to HR_Data is PositionID.</p><p>Just always double check those connections.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZCDoWwHdjKFApGl3yXS0Cg.png" /><figcaption>Power BI Data Modeling</figcaption></figure><h4>Data Manipulation:</h4><p>— Full Name: Added new column to merge LastName, FirstName, MiddleInitial and dropping the three separate columns</p><p>— Age and Employee RetentionAdded New column using age menu in the Power Query Editor</p><h4>Data Analysis:</h4><p>We use a high level dashboard that will identify employee segmentation required by the HR Manager.</p><p><strong><em>Filters were created for users to drill down data as per their need.</em></strong></p><p>— Hire Date</p><p>— Department</p><p>— Manager</p><p>— Employment Status</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FHU_E2o-vpl8mGtgrMdL9g.png" /><figcaption>Power BI Dashboard that Summarises HR Records</figcaption></figure><ul><li>We created a number of employees and marital status segmented by gender.</li><li>We used median age instead of average because of the widespread age in our data.</li><li>Created a ribbon chart that will show not only the trend of hiring but also the average salary for each position.</li><li>Table will magnify the best recruitment source for HR</li><li>Meters will give a color coded goal for employees average satisfaction and absences.</li><li>The Employee Table will give a high level overview for each person.</li><li>Mapping out the employees location will be useful in determining where to publish vacancies for a certain department/position.</li></ul><p>Have I told you that this is my first Power BI dashboard? If you found this useful, kindly clap/like my post or you have some suggestions for me to enhance my data viz, please comment on this post.</p><p>I have read an online debate regarding free courses vs paid bootcamps. Some will definitely standstill in their opinion that you can’t be a data analyst by just staring at your screen and answering a few multiple choice exams. But I have seen SPARTA and Google Graduates who are now working full time in their dream jobs. Well, in the end, it is not the course, it is not your professors in your school… IT IS YOU who will determine your next step..your future in your OWN DATA JOURNEY!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4OJG1UCC0ireRXzBDjJerQ.png" /><figcaption>PowerBI Desktop Essentials Certificate</figcaption></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ed7c8095f331" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Exploring the Impact of COVID-19 on the Coaching Industry through Data and Analytics]]></title>
            <link>https://medium.com/@WilmaLapuz/exploring-the-impact-of-covid-19-on-the-coaching-industry-through-data-and-analytics-1af517ab5fac?source=rss-8398c48e812e------2</link>
            <guid isPermaLink="false">https://medium.com/p/1af517ab5fac</guid>
            <category><![CDATA[data-portfolio-projects]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[coaching]]></category>
            <category><![CDATA[covid-19-crisis]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Wilma Lapuz]]></dc:creator>
            <pubDate>Wed, 25 Jan 2023 05:17:24 GMT</pubDate>
            <atom:updated>2023-01-25T05:19:34.993Z</atom:updated>
            <content:encoded><![CDATA[<p>Coaching Industry: COVID-19 and Beyond</p><p>I am pleased to showcase one of my reports from my internship at <a href="https://peak-resonance.com/">Peak Resonance</a>. I am truly grateful to <a href="https://www.linkedin.com/in/jesus-jovan-medrano/">Mr. Jovan Medrano</a> for permitting me to feature this work in my Medium portfolio.</p><h4><strong>Introduction</strong></h4><p>Coaching industry was challenged by COVID-19. Face-to-face interaction that is the usual and primary way in delivering coaching services was restricted by most of the government. Thus, coaching hours declined that commonly will result in decrease of income for coaches.</p><p>Most coaching organizations lead their members for upskilling and promoting services offered. On that, coaches have pivoted, innovative ways and continue to lend their helping hands for those in need of guidance. This small report will identify coaching pre, during and beyond the crisis of COVID-19.</p><h4><strong>Coach and Coaching</strong></h4><p>Many people were confused with the differences of coaches between mentors, or coaches between counselors or psychologists and to begin this paper, we need to distinguish what a coach really is and what they provide.</p><p>Coach helps the client to achieve their personal best and to produce the results they want in their personal and professional lives (International Coaching Community). As per International Coaching Federation, coaches do provide objective assessment and observations that foster individual’s or team’s self-awareness and awareness of others, listen closely to fully understand the individual’s or team’s circumstances, act as a sounding board in exploring possibilities and implementing thoughtful planning and decision making, champion opportunities and potential, encouraging stretch and challenge commensurate with personal strengths and aspirations, foster shifts in thinking that reveal fresh perspectives, challenge blind spots to illuminate new possibilities and support the creation of alternative scenarios and maintain professional boundaries in the coaching relationship, including confidentiality, and adheres to the coaching profession’s code of ethics.</p><p>The word coaching has been used as a defined procedure only a few decades back (Wildflower, 2013). Coaching is a process focused on working with a person’s needs, wants, goals, or vision for where they want to go, and then designing steps for getting there (Stober &amp; Grant, 2006). On that premise aroused different coaches in different areas of life with diverse approaches.</p><h4><strong>Coaching Pre-pandemic</strong></h4><p>Due to the steep expectations from boss, to partner, to health; coaching became popular not only because of recommendations but due to results and goals achieved by the coachee and companies.</p><p>Results indicate that individual coaching and group training were effective in reducing procrastination and facilitating goal attainment. Individual coaching created a high degree of satisfaction and was superior in helping participants attain their goals, whereas group training successfully promoted the acquisition of relevant knowledge (Traut-Mattausch, Mühlberger, Jonas, &amp; Losch, 2016).</p><p>Thus the global growth and wide spread of coaching profession was evident in 2020 ICF Global Coaching Study:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4e1C-W_dCT8IjLi6d33psQ.png" /><figcaption>The coaching continuum: Estimates by world region</figcaption></figure><h4><strong>Crisis Hit</strong></h4><p>Everyone was affected when COVID-19 hit and when countries were shut down one by one not only their international borders but also the strict implementation of face-to-face meetings; coaching career deeply felt the impact. Coach practitioners have adjusted their methods used to coach clients. Reflecting the nature of the pandemic, there has been a sharp decrease in coaching in person (80%). Mainly, coach practitioners have increased their use of audio-video platforms (74%) (International Coaching Federation, 2020) and even though digital pivoting was applied, the decrease of numbers of clients were not avoided.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vamPcttK4PJ4bWX2nSvxkw.png" /><figcaption>Data Source: (International Coaching Federation, 2020)</figcaption></figure><p>In the chart above, 1 of 4 coaches in North America reported no impact neither on income nor employment but Eastern Europe has the greatest financial shock of 85%.</p><p>Unlike the 2008 Market Crash where coaching explores in driving financial leaders into transparency and social responsibility to rebuild the brand of their financial organizations (Davis, 2009), 2020 COVID-19 Pandemic is calling for Crisis Leaders in solving cash flow and liquidity, family and friends relationships, employees well-being, managing multiple changes and to prepare for uncertainty (Asia Pacific Alliances of Coaches, 2021).</p><p>With that demand, the 2020 survey of the International Coaching Federation gave a 41% average of coaches that has a somewhat positive outlook that their profession will bounce back in 6-months.</p><h4><strong>Future of Coaching</strong></h4><p>Blackbyrn blogged that coaching clients are becoming sophisticated and they are willing to pay for coaches with great coaching skills and profound results. In the latest study by Jarosz (2021), the participants of the experiment group noticed positive shifts occurring in their thinking patterns as well, especially in how they evaluate and think about themselves and suggest that the application of coaching in the workplace improves employee well-being and performance.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9RnI1TyP-a_5uey535Egzg.png" /><figcaption>Data Source: (Asia Pacific Alliance of Coaches, 2020)</figcaption></figure><p><em>Based on 5th Coaching Survey an Asia Coaching Benchmark 2019</em>; Singapore has a relatively more mature coaching market yet some companies find coaching very expensive and not a ‘powerful tool’. Philippines’ growth of coaching services could be attributed to the deeper penetration in existing markets as well as expansion into new markets and industry sectors. In China, coaching is a relatively young concept. One out of three firms planned to use coaching services but the others hindered by lack of awareness, high cost and support from top management. Recent growth in Indonesia’s coaching market reflects expansion opportunities and 5% indicated to adopt coaching in the next 12 months.</p><p>Clearly, in every crisis coaches are ready to explore and build new resilient models/tools in helping their clients to face vulnerability, unsafe and uncertain paths. As forecasted, the coaching industry is setting a mark not only in companies but also in different aspects of life.</p><h4>References:</h4><p>International Coaching Community. (n.d.). What is Coaching? Retrieved 07 23, 2021, from International Coaching Community: <a href="https://internationalcoachingcommunity.com/what-is-coaching/">https://internationalcoachingcommunity.com/what-is-coaching/</a></p><p>International Coaching Federation. (n.d.). FREQUENTLY ASKED QUESTIONS. Retrieved 07 23, 2021, from International Coaching Federation: <a href="https://coachingfederation.org/faqs">https://coachingfederation.org/faqs</a></p><p>Wildflower, L. (2013). The Hidden History of Coaching. Great Britain: CPI Antony Rowe, Chippenham, Wiltshire .</p><p>Stober, D. R., &amp; Grant, A. M. (2006). Evidence Based Coaching Handbook. New Jersey: John Wiley &amp; Sons, Inc.</p><p>Traut-Mattausch, E., Mühlberger, M. D., Jonas, E., &amp; Losch, S. (2016, May 03). Comparing the Effectiveness of Individual Coaching, Self-Coaching, and Group Training: How Leadership Makes the Difference.</p><p>International Coaching Federation. (2020). COVID-19 and the Coaching Industry. COVID-19 and the Coaching Industry .</p><p>Davis, A. A. (2009). Coaching: Navigating the Emerging Trends in Financial Services. IJCO The International Journal of Coaching Organizations .</p><p>Asia Pacific Alliances of Coaches. (2021, April). Newsletter. Retrieved Jul 28, 2021, from Asia Pacific Alliance of Coaches — The Coaching Voice of Asia Pacific : <a href="http://www.apacoaches.org/news/newsletter/">http://www.apacoaches.org/news/newsletter/</a></p><p>Blackbyrn, S. (2021, June 11). Blog. Retrieved July 28, 2021, from Coach Foundation: <a href="https://coachfoundation.com/blog/30-coaching-trends/">https://coachfoundation.com/blog/30-coaching-trends/</a></p><p>Jarosz, J. (2021, February 01). The impact of coaching on well-being and performance of managers and their teams during pandemic. International Journal of Evidence Based Coaching and Mentoring .</p><p>Asia Pacific Alliance of Coaches. (2020). 5th Coaching Survey an Asia Coaching Benchmark, 2019. Asia Pacific Alliance of Coaches.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1af517ab5fac" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Development Academy of the Philippines-Data Analyst Capstone: CONCEPT PAPER for Covid-19 Business…]]></title>
            <link>https://medium.com/@WilmaLapuz/development-academy-of-the-philippines-data-analyst-capstone-course-covid-19-business-impact-64b5f58acd4?source=rss-8398c48e812e------2</link>
            <guid isPermaLink="false">https://medium.com/p/64b5f58acd4</guid>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[real-data]]></category>
            <category><![CDATA[portfolio]]></category>
            <category><![CDATA[sparta]]></category>
            <category><![CDATA[capstone-project]]></category>
            <dc:creator><![CDATA[Wilma Lapuz]]></dc:creator>
            <pubDate>Wed, 18 Jan 2023 02:10:45 GMT</pubDate>
            <atom:updated>2023-01-18T03:02:57.813Z</atom:updated>
            <content:encoded><![CDATA[<h3>Development Academy of the Philippines-Data Analyst Capstone: CONCEPT PAPER for Covid-19 Business Impact</h3><p>TRUSTING IN DIGITAL PIVOT FOR BUSINESS RECOVERY</p><h4>INTRODUCTION</h4><p><strong><em>Background</em></strong></p><p>Doctors are more concerned with COVID-19. Unlike Spanish Flu, there are asymptomatic carriers of the disease. That one detail makes it harder to mitigate the spread of the virus by simply taking temperatures, (Amenabar, 2020). On that note, it is hard to foretell when we can live without the fear of exposing ourselves to COVID-19 virus and its variants. This concept paper builds upon insights from various firms who commenced surveys in the businesses and to strategically focus on the economic problem caused by this pandemic.</p><p><strong><em>Need for this Study</em></strong></p><p>Time is a luxury that all country leaders don’t have. Making the right decisions not only to health factors but also the economic risks will be vital in the recovery of the Philippines and its people. The concept will identify the relationship between demand and supply factor in job security, effective rollout of the vaccine and digital solutions across businesses.</p><p><strong><em>Problem Statement</em></strong></p><p>Does pivoting digitally alone can help to recover business during pandemic?</p><p><strong><em>Objective</em></strong></p><p>The research paper seeks to verify the relationship of economic shock and consumer’s behavior in spending. The second objective is to determine the influence of upskilling and digitalization of firms in gaining competitiveness in this time of pandemic.</p><h4>LITERATURE REVIEW</h4><p><strong><em>COVID-19 and Its Unprecedented Crisis</em></strong></p><p>From 1918’s Spanish Flu pandemic, the American Public Health Association (APHA) stated that mixing of bodies and sharing of breath in crowded rooms, was dangerous (Billings, 1997). The lockdown aims to reduce the transmission of the virus by limiting contact between people. Thus, officials all over the world strictly implement different kinds of confinement and mitigation measures like social distancing. However, this measure implicated a profound impact not only socially but also created an unprecedented economic impact.</p><p>GDP plunged to negative 9.5 which is the lowest since 1980 and unemployment rate doubled from 5.1 of 2019 to 10.4%.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4hdyjKrKtpp3eT7YbSolBQ.png" /><figcaption>Figure 1 — Source: International Monetray Fund, 2021</figcaption></figure><p>The main contributors to the decline were: Construction: -24.2 percent<br> Other Services: -38.0 percent<br> Real Estate and Ownership of Dwellings: -13.2 percent</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Iya9Jh4fRwo6lV2hFftnaw.png" /><figcaption>Figure 2 — Source: Philippine Statistics Authority, 2021</figcaption></figure><p><strong><em>Economic Shock of Lockdown</em></strong></p><p>Economy works when money moves and during lockdown people stopped moving and so is the money. Philippine Government borrows, allotted and spend trillions of pesos to mitigate the supply- demand shocks of COVID-19. A supply shock is when public health authorities and employers prevent service workers from doing their jobs and a demand shock, on the other hand, is something that reduces consumers’ ability or willingness to purchases goods and services at given prices (Brinca, Duarte, &amp; e Castro, 2020).</p><p>More than Php200-billion were allotted by the government to cover, sustain and fund the declines as seen above in figure 2, procurement, trainings and projects in response of the COVID-19 pandemic under Bayanihan II project.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IBZosvBvG1aGFFxzON6Dpg.png" /><figcaption>Figure 3 — Source: Department of Budget and Management, 2021</figcaption></figure><p><strong><em>Vaccine. Job. New Reality.</em></strong></p><p>Vaccine hesitancy is not only an arising problem in Philippines but also declared one of the ten threats to global health by WHO (World Health Organization, 2019). Spreading correct information such as efficacy, side effects and the benefit of the vaccine to face the new normal helped the Philippine Government in doubling the people vaccinated on May 2021.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*quhqA6B4A5qphM9mrxGmQg.png" /><figcaption>Figure 4 — Source: World Health Organization, 2021</figcaption></figure><p>Successfully educating the citizens that vaccine is not the 100% solution in fight against actively mutating COVID virus and due to uncertainty when this pandemic will end, Philippine consumers became very conservative with their buying habits despite government injected funds for citizens to spend (Figure 2).</p><p>Cautious spending resulted a demand and supply shock which causes job loss and insecurity. Based on Inter-Agency Task Force for the Management of Emerging Infectious Diseases, 2020 report, 50% of private companies’ workers experienced decline in income and 80% of which is due to job loss.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yIJpnmMaozKE5DAQiVhFIg.png" /><figcaption>Figure 5 — Source: Inter-Agency Task Force for the Management of Emerging Infectious Disease, 2020</figcaption></figure><p>Due to left and right retrenchment, Filipinos are looking for ways to acquire extra income and/or serve their clients contactless. And the outcome is digitalization. This is the latest competition now in the upcoming new reality of the world. Consumers are already aware of food delivery services and shopping online since pre-pandemic but how about the telecommuting or working from home?</p><p>ASIAN DEVELOPMENT BANK SURVEY, 2020 resulted that 57.3% of micro, 12% of small and 11.1% of medium firms’ workers are not possible to work from home. The same survey shows that financial assistance and tax incentives may help their businesses to adopt the new normal.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qj0wK08Ts22X4I5HylyvfA.png" /><figcaption>Figure 6 — Source: Asian Development Bank, 2020</figcaption></figure><p><strong><em>Digitalisation to Solve the Problem</em></strong></p><p>Consumer confidence is expected to remain low even after the ECQ is lifted. Expectation of a worse family income situation is especially pronounced among the low-income group (Inter-Agency Task Force for the Management of Emerging Infectious Diseases, 2020). And that is a significant economic problem.</p><p>Digitalisation for businesses will not only attract consumers to buy their goods or services but it will generate a well of data that can help them dive and perform the analytics. Analytics will solve in managing of inventories and will also help to know more about erratic decision changes of Filipinos due to this never-ending battle cause of pandemic.</p><h4>METHODOLOGY</h4><p><strong>Quantitative</strong></p><p>In cross-sectional design, we will use descriptive and categorical analysis and prescriptive analysis will be utilized to determine the next policies to be developed by the government.</p><p><strong>Qualitative</strong></p><p>For qualitative data it will undergo transcriptions and reported on themes.</p><h4>REFERENCES</h4><p>1. Amenabar, T. (4 September, 2020). The Washington Post. Retrieved from The Washington Post:</p><p><a href="https://www.washingtonpost.com/history/2020/09/01/1918-flu-pandemic-end/">https://www.washingtonpost.com/history/2020/09/01/1918-flu-pandemic-end/</a></p><p>2. Billings, M. (June, 1997). The Influenza Pandemic of 1918. Retrieved from Stanford University: <a href="https://virus.stanford.edu/uda/fluresponse.html">https://virus.stanford.edu/uda/fluresponse.html</a></p><p>3. Brinca, B., Duarte, J. B., &amp; e Castro, M. (17 June, 2020). Decomposing demand and supply shocks during COVID-19. Retrieved from VoxEU.org : <a href="https://voxeu.org/article/decomposing-demand-and-supply-shocks-">https://voxeu.org/article/decomposing-demand-and-supply-shocks-</a> during-covid-19</p><p>4. Department of Budget and Management. (31 May, 2021). COVID-19 Budget Utilization Reports as of May 31, 2021. Retrieved from Department of Budget and Management: <a href="https://www.dbm.gov.ph/index.php/programs-projects/status-of-covid-19-releases#bayanihan-2">https://www.dbm.gov.ph/index.php/programs-projects/status-of-covid-19-releases#bayanihan-2</a></p><p>5. International Monetary Fund. (April, 2021). International Monetary Fund. Retrieved from World Economic Outlook (April 2021): <a href="https://www.imf.org/external/datamapper/datasets">https://www.imf.org/external/datamapper/datasets</a></p><p>6. Inter-Agency Task Force for the Management of Emerging Infectious Diseases. (2020). WE RECOVER AS ONE. Manila: National Economic and Development Authority (NEDA).</p><p>7. World Health Organization. (2019). Top Ten Threats to Global Health in 2019. Retrieved from World Health Organization: <a href="https://www.who.int/news-room/spotlight/ten-threats-to-global-health-in-2019">https://www.who.int/news-room/spotlight/ten-threats-to-global-health-in-2019</a></p><p>8. World Health Organization. (2021). COVID-19 Explorer. Retrieved from World Health Organization: <a href="https://worldhealthorg.shinyapps.io/covid/">https://worldhealthorg.shinyapps.io/covid/</a></p><p>9. ASIAN DEVELOPMENT BANK. (17 July, 2020). ADB Philippine Enterprise Survey on COVID-19 Impact. Retrieved from ADB Data Library: <a href="https://data.adb.org/dataset/adb-philippine-enterprise-survey-covid-19-">https://data.adb.org/dataset/adb-philippine-enterprise-survey-covid-19-</a> impact</p><p>10. Philippine Statistics Authority. (11 May, 2021). National Accounts. Retrieved from Republic of the Philippines — Philippine Statistics Authority: <a href="https://psa.gov.ph/national-accounts/base-2018/estimates">https://psa.gov.ph/national-accounts/base-2018/estimates</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=64b5f58acd4" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Development Academy of the Philippines-SP601 Capstone: THE DOOR-TO-DOOR CARAVAN SAMPLING]]></title>
            <link>https://medium.com/@WilmaLapuz/spartas-sp601-capstone-the-door-to-door-caravan-sampling-ea3700fd34c0?source=rss-8398c48e812e------2</link>
            <guid isPermaLink="false">https://medium.com/p/ea3700fd34c0</guid>
            <category><![CDATA[data-driven-research]]></category>
            <category><![CDATA[capstone]]></category>
            <category><![CDATA[project-sparta]]></category>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[real-data]]></category>
            <dc:creator><![CDATA[Wilma Lapuz]]></dc:creator>
            <pubDate>Tue, 17 Jan 2023 05:55:35 GMT</pubDate>
            <atom:updated>2023-01-17T06:27:22.095Z</atom:updated>
            <content:encoded><![CDATA[<p>ANALYTICS SOLUTION FOR DOOR-TO-DOOR SIM CARD DISTRIBUTION</p><h4><strong>CHAPTER 1 : INTRODUCTION</strong></h4><p><strong><em>Background</em></strong></p><p>The growing population of Philippines with regards to mobile phone usage as primary source of communication is a good sign of business for telecommunication companies but it comes with a massive competition not only with each other but also with the noise of other products.</p><p>Internet has pivoted many accustomed activities, and marketing is no exception. As more and more people are attached to their phones, companies develop a new era of advertising, the digital marketing. This study aims to solve the problems of marketing in the narrowing attention span of consumers and highlight the effectiveness of door-to-door marketing.</p><p>Smarta, a local telecommunications company, is considering to hire a marketing agency to do a door-to-door sampling caravan project to give out free prepaid SIM cards to households. The objective is to increase the subscriber base of the company while obtaining a net profit from this project by hoping that the free SIM card samples will eventually be activated in the future and revenue will flow from the activated SIMs.</p><p><strong><em>Research Question</em></strong></p><p>From the above background, the following question can be drawn the following main question.</p><p>- Is the door-to-door SIM card distribution effective to attract new customers?</p><p><strong><em>Scope and Limitation</em></strong></p><p>-The scope of this research is related to the business process proposal of sampling system given by the marketing agency to SMARTA.</p><p>-The study is limited to the population report published at the Philippine Statistics Authority website dated 2015.</p><h4><strong>CHAPTER 2 : </strong>LITERATURE REVIEW</h4><p><strong><em>Marketing and Its Growing Problem</em></strong></p><p>Marketing refers to activities a company undertakes to promote the buying or selling of a product or service, (TWIN, 2020). Marketing evolves hand-in-hand with technology and thus produced digital marketing. But a problem arises on that, fierce competition on consumer attention (A new study in Nature Communications finds that our collective attention span is indeed narrowing, and that this effect occurs not only on social media but also across diverse domains including books, web searches, movie popularity, and more, (Technical University of Denmark, 2019)) and developing trust (Digital enables them to voice their concerns or dissatisfaction in an amplified way. In fact, we found that 40% of global consumers posted negative comments about an experience with a company via social media. Their digital distrust can easily influence thousands of potential customers to stay away (BENDER, BARTON, &amp; MARQUE, 2017)).</p><p><strong><em>Marketers Backtracked Towards Sampling</em></strong></p><p>Marketers came up with different styles of product sampling and below are the two with direct access to household (University of Missouri, 2015):</p><p><em>Table 1: Product Sampling Method</em></p><figure><img alt="SPARTA’s SP601 Capstone: THE DOOR-TO-DOOR CARAVAN SAMPLING: Product Sampling Method" src="https://cdn-images-1.medium.com/max/900/1*Rs2IQcQOcbGzMFB8I_OCTw.png" /><figcaption><em>Product Sampling Method</em></figcaption></figure><p>Product sampling continues to rank among the most effective tactics in the history of marketing because of its ability to do what no other medium can. Put the product directly in the customer’s hands.</p><p><strong><em>Improved Door-to-Door Sampling</em></strong></p><p>As above table portrays, available data and results are limited for marketers to implement the direct mail sampling. It also leaves a negative impact on potential clients due to the feeling of intrusiveness. Thus on reports of DMA UK Ltd (2014), door-to-door sampling is an extremely effective and impactful method of reaching and communicating with your target audience — which is why some 80% of the UK’s top advertisers use the medium.</p><p><strong><em>Gap Analysis</em></strong></p><p>Aim to further explore the relationships and effects in implementing the two marketing style (Door-to-door Sampling and Direct Mail Sampling) in the same adequate sample household.</p><h4><strong>CHAPTER 3: </strong>PROPOSED RESEARCH DESIGN</h4><p><strong><em>Research Design</em></strong></p><p>This research applied quantitative and qualitative methods. The quantitative method was used to meet a reasonable cost for marketing with a time bound study of 100 days. Qualitative method used to explore the pertinent data to their conceptual framework that quantitative data could not facilitate (Crick, 2020).</p><p><strong><em>Population and Sample</em></strong></p><p>The door-to-door caravan used a non-probability quota sampling that was conducted around the City of Taguig and nearby city with a distance not more than 10-Kilometer and with approximate travel period that is not more than one hour.</p><p><strong><em>Instruments</em></strong></p><p>The data for this door-to-door caravan were collected by giving a standardized form for recipients to affix their signature to prove the acceptance of the SIM card.</p><p><strong><em>Data Gathering</em></strong></p><p>The instrument of this door-to-door caravan was administered and personally collected by the samplers. The SIM card distribution took 100 days.</p><p><strong>PROPOSED DATA ANALYSIS METHOD</strong></p><p><strong><em>Quantitative</em></strong></p><p>In cross-sectional design, we will use the descriptive and categorical analysis to illustrate the mean and deviation, percentage and frequency. In this study, prescriptive analysis will be utilized to determine the next decision to be taken by SMARTA.</p><p><strong><em>Qualitative</em></strong></p><p>On qualitative data, content analysis will be used to analyse the data from the door-to-door caravan marketing sampling conducted by 10 samplers.</p><h4><strong>CHAPTER 4: </strong>IMPLEMENTATION</h4><p><strong><em>Proposed Sampling System by Marketing Agency</em></strong></p><p>The marketing agency proposed a Php5,000,000.00 contract (SIM card exclude SIM card costs) for SMARTA’s 100 day SIM card distribution. The marketing agency proposes the below sampling system.</p><p><em>Table 2: Marketing Agency Proposed Activity</em></p><figure><img alt="SPARTA’s SP601 Capstone: THE DOOR-TO-DOOR CARAVAN SAMPLING: Marketing Agency Proposed Activity" src="https://cdn-images-1.medium.com/max/898/1*oXAlgstHC0NkKfcKNBLHvQ.png" /><figcaption>Marketing Agency Proposed Activity</figcaption></figure><p>The above marketing agency’s proposed sampling system shows that the 10 samplers only have 6 hours per day to distribute SIM cards. Assuming that all the barangay that marketing agency will visit a population of 26,213 (average household size of 4 (Lungsod ng Makati, 2019)) that covers a lot area of 0.88km2 with approximately 5,698 of household like Barangay Bangkal of Makati (Lungsod ng Makati, 2018), samplers approximately 70.809 kilometers to cover per day.</p><p><em>Equation 1: Household Lot Length</em></p><figure><img alt="SPARTA’s SP601 Capstone: THE DOOR-TO-DOOR CARAVAN SAMPLING Equation 1: Household Lot Length" src="https://cdn-images-1.medium.com/max/628/1*AgQ0ZptUgFn5mX8VsCC4gQ.png" /></figure><p><em>Equation 2: Kilometers To Cover Per Barangay</em></p><figure><img alt="SPARTA’s SP601 Capstone: THE DOOR-TO-DOOR CARAVAN SAMPLING Equation 2: Kilometers To Cover Per Barangay" src="https://cdn-images-1.medium.com/max/676/1*0IYAzuZ-4BZbVd23LQo7Ng.png" /></figure><p>In order to estimate the number of household that a sampler can visit in an hour, the marketing agency provided a 40km/hour as the velocity of the one sampler. Estimated of forty-five seconds spiel talk time (accepts/declines SIM card) and 30 seconds for household that do not answered the door was also provided by the marketing agency will total an average of 37.50 seconds. With the help Household Lot Length equation, the number of household per hour can be solved.</p><p><em>Equation 3: Number of Households Visited Per Minute</em></p><figure><img alt="SPARTA’s SP601 Capstone: THE DOOR-TO-DOOR CARAVAN SAMPLING Equation 3: Number of Households Visited Per Minute" src="https://cdn-images-1.medium.com/max/884/1*ZVGa4MeXYTXhnoAo-GXbdQ.png" /></figure><p><em>Equation 4: Walk Time Per Minute Per Household</em></p><figure><img alt="SPARTA’s SP601 Capstone: THE DOOR-TO-DOOR CARAVAN SAMPLING Walk Time Per Minute Per Household" src="https://cdn-images-1.medium.com/max/756/1*_rBc1m2EQlsV_zRh_-J4MA.png" /></figure><p><em>Equation 5: Target Household Visits Per Hour</em></p><figure><img alt="SPARTA’s SP601 Capstone: THE DOOR-TO-DOOR CARAVAN SAMPLING Target Household Visits Per Hour" src="https://cdn-images-1.medium.com/max/972/1*SDnEk4kgNuLnfWokGC_GMw.png" /></figure><p>A target of sixty five (65) households visiting per hour with a six (6) working hours each for ten (10) samplers for one hundred days (100) days, there are 390,000 households could get in touch for the given sampling period.</p><p>Below chart is the expected hit and estimated conversion by the marketing agency:</p><figure><img alt="SPARTA’s SP601 Capstone: THE DOOR-TO-DOOR CARAVAN SAMPLING Number of Expected Hit and Estimated Conversion" src="https://cdn-images-1.medium.com/max/1024/1*WybXH3LXK_3jzE1yEJ_auQ.png" /><figcaption>Number of Expected Hit and Estimated Conversion</figcaption></figure><p>Exclusion of SIM card Php40.00 manufacture and Php50.00 credit with total cost of Php35,100,000.00, the marketing agency estimated an average return of revenue of Php13,162,600 yearly for SMARTA’s newly acquired customers.</p><p><strong><em>SMARTA Implementation Plan</em></strong></p><p>The objective of this SMARTA implementation plan is to increase the estimated new subscribers advised by the marketing agency. With the current situation, home-based learning was on effect to minimize and stop the spread of the virus. Unlike the marketing agency’s proposal, this plan targets consumers not only the close proximity barangays but students with demographics of 5 to 24 years that currently attending online school.</p><p>As one of the parents told (REUTERS, 2021) “Sometimes we change the SIM card to a different provider so he doesn’t have to study on the roof, but there’s rarely enough money to spare for that”. Consumers are now resulting to be more engaged on benefits offered by different telecommunication providers (Pew Research Center, 2019). Based on a study (Capistrano, 2013), the overall data shows that promotions and giveaways rated the highest level of involvement. This implies that generally speaking, mobile phone users exert more effort in learning about the promotions and giveaways being offered to them by the service providers more than the other service offerings. Thus, the SMARTA’s is on the right time to pursue door-to-door sampling caravan and target the students that are definitely at home.</p><p>The sampling will a benefit student and parents on budget and a contribution to the government for its campaign to promulgate online classes.</p><p>With the above socio-economic objective, the target barangays that has a 25% and above students on their population and the visit should not be per barangay per day but to visit the number households as reported from PSA (Philippine Statistics Authority, 2017). Below are the list of barangay and corresponding number of target students and days need to spend in a barangay given the rates from marketing agency.</p><figure><img alt="SPARTA’s SP601 Capstone: THE DOOR-TO-DOOR CARAVAN SAMPLING City of Pateros" src="https://cdn-images-1.medium.com/max/1024/0*Hc-mO2bdlFqmW9_S" /></figure><figure><img alt="SPARTA’s SP601 Capstone: THE DOOR-TO-DOOR CARAVAN SAMPLING City of Manila" src="https://cdn-images-1.medium.com/max/1024/0*3rZo8fST-PdGyAlf" /></figure><p>The above barangays are projected to get in touch on a total of 502,190 students in 100 days. On such case, it is not needed to push outside of NCR for a door-to-door caravan.</p><p>If SMARTA pushes to reach 500,000 hits with a hit rate of 75% (answered the door) we will need 19 samplers to knock 740,741 doors in 100 days or 10 samplers conducting the door-to-door in 190 days.</p><p><em>Equation 6 : Household Visits</em></p><figure><img alt="SPARTA’s SP601 Capstone: THE DOOR-TO-DOOR CARAVAN SAMPLING Equation 6 Household Visits" src="https://cdn-images-1.medium.com/max/1024/1*E6ty8xyoPftPLUbYaVdGuA.png" /></figure><p><em>Equation 7: Household Visits Per Hour Per Sampler</em></p><figure><img alt="Household Visits Per Hour Per Sampler" src="https://cdn-images-1.medium.com/max/812/1*JP9lGiD00Kp6IqwRP9kvlA.png" /></figure><p><em>Equation 8: Calculating How Many Samplers Needed for 100 Days</em></p><figure><img alt="Calculating How Many Samplers Needed for 100 Days" src="https://cdn-images-1.medium.com/max/884/1*VZjfp-K8VkBC67CaJ0qMvg.png" /></figure><p><em>Equation 9: Calculate How Many Days Needed for 10 Samplers</em></p><figure><img alt="Calculate How Many Days Needed for 10 Samplers" src="https://cdn-images-1.medium.com/max/1024/1*atbF4tUcquwGn5jzCkbbtQ.png" /></figure><p><em>Table 4: SMARTA Strategic Sampling with Cost</em></p><figure><img alt="Strategic Sampling with Cost" src="https://cdn-images-1.medium.com/max/884/1*sq06dS5zL8joFCchjQ04kA.png" /><figcaption><em>Strategic Sampling with Cost</em></figcaption></figure><p>Inaccuracy and incompleteness of sourced out data from Philippine Statistics Office is not enough to analyse it. This is one of the foreseeing reasons that may change the result of this study. Weather changes, samplers’ attitude towards the public, problems over the van’s travel time, which may lead to a bigger problem of under coverage that will take an effect on the feasibility of this project.</p><p>To avoid these problems and yet reach the target hit of 500,000, this study strongly recommend that the SMARTA or marketing agency will based on the number of acceptance target number rather than the per day in to pursue this door-to-door sampling.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*zeOnBqK6GVybeW9b" /></figure><p>Clearly, with a 53% ROI (marketing agency 46%) it will be cost efficient for SMARTA to take this study on their own. Sensitive variables will as mentioned above will be fully under control.</p><h4><strong>CHAPTER 5: </strong>BIBLIOGRAPHY</h4><p>1. Technical University of Denmark. (2019, April 15). <em>Abundance of information narrows our collective attention span</em>. Retrieved from ScienceDaily: <a href="http://www.sciencedaily.com/releases/2019/04/190415081959.htm">www.sciencedaily.com/releases/2019/04/190415081959.htm</a></p><p>2. TWIN, A. (2020, Aug 17). <em>BUSINESS ESSENTIALS</em>. Retrieved from Investopedia: <a href="https://www.investopedia.com/terms/m/marketing.asp">https://www.investopedia.com/terms/m/marketing.asp</a></p><p>3. BENDER, M., BARTON, R., &amp; MARQUE, F. (2017). <em>Trust in the Digital Age .</em> ACCENTURE. ACCENTURE.</p><p>4. University of Missouri. (2015, April 8). <em>Course Hero</em>. Retrieved from Course Hero: <a href="https://www.coursehero.com/file/p6o95um/Door-to-door-sampling-a-type-of-sampling-in-which-samples-are-brought-directly/">https://www.coursehero.com/file/p6o95um/Door-to-door-sampling-a-type-of-sampling-in-which-samples-are-brought-directly/</a></p><p>5. Frost &amp; Sullivan. (2012). <em>Research into the Door-to- Door Sales Industry in Australia.</em> Frost &amp; Sullivan for the Australian Competition and Consumer Commission (ACCC).</p><p>6. Lewis, J., &amp; Ling, P. (2016). <em>“Gone Are the Days of Mass-media Marketing Plans and Short Term Customer Relationships”: Tobacco Industry Direct Mail and Database Marketing Strategies.</em> Tobacco Control.</p><p>7. Morimoto, M., &amp; Chang, S. (2013, Jul 01). Consumers’ Attitudes Toward Unsolicited Commercial E-mail and Postal Direct Mail Marketing Methods. <em>Journal of Interactive Advertising</em> .</p><p>8. DMA UK Ltd. (2014). Door drop guide.</p><p>9. Crick, J. M. (2020, March). Qualitative research in marketing: what can academics do better? <em>Journal of Strategic Marketing</em> .</p><p>10. Lungsod ng Makati. (2018). <em>Barangays</em>. Retrieved January 2021, from Makati Portal: <a href="https://www.makati.gov.ph/assets/uploads/staticmenu/docs/1579227221918.pdf">https://www.makati.gov.ph/assets/uploads/staticmenu/docs/1579227221918.pdf</a></p><p>11. Lungsod ng Makati. (2019). <em>Facts and Figures</em>. Retrieved January 2021, from Makati Portal: <a href="https://www.makati.gov.ph/cms/content/facts%20and%20figures/3241">https://www.makati.gov.ph/cms/content/facts%20and%20figures/3241</a></p><p>12. REUTERS. (2021, Jan 7). <em>ASIA</em>. Retrieved from AsiaONe: <a href="https://www.asiaone.com/asia/scaling-roofs-and-mountains-philippine-students-battle-take-online-classes">https://www.asiaone.com/asia/scaling-roofs-and-mountains-philippine-students-battle-take-online-classes</a></p><p>13. Pew Research Center. (2019, November 20). <em>Internet and Technology</em>. Retrieved from Pew Research Center: <a href="https://www.pewresearch.org/internet/2019/11/20/mobile-owners-what-they-struggle-to-do/">https://www.pewresearch.org/internet/2019/11/20/mobile-owners-what-they-struggle-to-do/</a></p><p>14. Capistrano, E. (2013). Investigating Product Involvement Issues for Mobile Phone Services: A Study of Filipino Mobile Phone Users. <em>Philippine Management Review 2013</em> .</p><p>15. Philippine Statistics Authority. (2017, June 30). <em>Philippine Population Surpassed the 100 Million Mark (Results from the 2015 Census of Population)</em>. Retrieved from Philippine Statistics Authority: <a href="https://psa.gov.ph/population-and-housing/statistical-tables">https://psa.gov.ph/population-and-housing/statistical-tables</a></p><p>16. Marikina City. (n.d.). <em>The Shoe Capital of the Philippines</em>. Retrieved from Marikina City: <a href="https://www.marikina.gov.ph/Our-City.html">https://www.marikina.gov.ph/Our-City.html</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ea3700fd34c0" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Google Data Analytics Capstone: Cyclistic Case Study Using pgAdmin4, Tableau & Anaconda]]></title>
            <link>https://medium.com/@WilmaLapuz/google-google-data-analytics-capstone-cyclistic-case-study-using-pgadmin4-tableau-anaconda-b089afd0665?source=rss-8398c48e812e------2</link>
            <guid isPermaLink="false">https://medium.com/p/b089afd0665</guid>
            <dc:creator><![CDATA[Wilma Lapuz]]></dc:creator>
            <pubDate>Tue, 03 Jan 2023 07:09:53 GMT</pubDate>
            <atom:updated>2023-01-09T04:13:50.697Z</atom:updated>
            <content:encoded><![CDATA[<h3>Google Data Analytics Capstone: Cyclistic Case Study Using PostgreSQL pgAdmin4, Tableau &amp; Anaconda</h3><p><strong>Members’ peak on August with Classic Bikes while Casual Riders enjoys July with an Electric Bike</strong></p><h4><strong>INTRODUCTION</strong></h4><p>In 2016, Cyclistic launched a successful bike-share offering. Since then, the program has grown to a fleet of 5,824 bicycles that are geotracked and locked into a network of 692 stations across Chicago. The bikes can be unlocked from one station and returned to any other station in the system anytime.</p><p>Cyclistic categorized the riders as Casual who purchased single-ride or full-day passes and Members who purchased annual memberships.</p><p>Cyclistic’s marketing strategy relied heavily on building general awareness and appealing to broad consumer segments. Marketing team created flexibility on pricing plans like single-ride passes, full-day passes, and annual memberships. But there is no effort for an upselling plan to convert the casual riders into members.</p><p>Cyclistic’s finance analysts have concluded that annual members are much more profitable than casual riders.</p><p>Ms. Moreno, Director of Marketing Team, has a clear goal: Design marketing strategies aimed at converting casual riders into annual members. As a Junior Analyst in the Marketing Team, I was assigned to answer: How do annual members and casual riders use Cyclistic bikes differently?</p><h4><strong>BUSINESS TASK</strong></h4><p>Identify, analyze, and assess the bike usage trends and patterns of casual riders/members for the Cyclistic future marketing program.</p><h4><strong>DATA SOURCES</strong></h4><p>Internal data of Cyclistics was used in this case study under Motivate International Inc, under <a href="https://ride.divvybikes.com/data-license-agreement">license</a>. It is the original data from the source. A 12-month (November 2021 to October 2022) historical dataset was downloaded and stored in <a href="https://drive.google.com/drive/folders/1nfy5m1z2IzNWOtFsFPk7M1Xh54ZPbtiN?usp=sharing">Google Drive</a> and organized in a YYYYMM_CompanyName manner. To avoid sampling bias we will use the dataset that will represent the population and anonymization was used to implement data privacy.</p><p><em>Reliable</em> — it is first-party data. It is owned by Cyclistic.</p><p><em>Original</em> — Dataset is straight from the source. Although already anonymized the other data is still intact.</p><p><em>Comprehensive</em> — There is some nulls but the importance of it can be checked in “Process” stage.</p><p><em>Current</em> — Data is up to date and has the current information of the cyclers’ usage.</p><p><em>Cited</em> — The City of Chicago owns all right, title, and interest in the Data. Thus, this is a reliable data source.</p><p>Since the finance analysts already concluded the annual memberships are much more profitable, anonymized data provided will help to focus on the main task which is to define the different usage of the bike renters.</p><h4><strong>CLEANING OR MANIPULATION OF DATA</strong></h4><p>Since the data exceeds the allowable complimentary BigQuery sandbox, I loaded the dataset in pgAdmin4 to use SQL. Tableau Public was used to create an interactive visualization or a dashboard and for an easier way to check the relations between columns, I used Anaconda.</p><p><em>STEP 1: Creating a table named bike.</em></p><p>Since all of our downloaded files have similar features, it will be logical to create only one table. Using this kind command, you can easily manipulate the features into correct data types.</p><pre>CREATE TABLE IF NOT EXISTS public.bike<br>(<br>    ride_id character varying COLLATE pg_catalog.&quot;default&quot; NOT NULL,<br>    rideable_type character varying COLLATE pg_catalog.&quot;default&quot;,<br>    started_at timestamp without time zone,<br>    ended_at timestamp without time zone,<br>    start_station_name character varying COLLATE pg_catalog.&quot;default&quot;,<br>    start_station_id character varying COLLATE pg_catalog.&quot;default&quot;,<br>    end_station_name character varying COLLATE pg_catalog.&quot;default&quot;,<br>    end_station_id character varying COLLATE pg_catalog.&quot;default&quot;,<br>    start_lat numeric COLLATE pg_catalog.&quot;default&quot;,<br>    start_lng numeric COLLATE pg_catalog.&quot;default&quot;,<br>    end_lat numeric COLLATE pg_catalog.&quot;default&quot;,<br>    end_lng numeric COLLATE pg_catalog.&quot;default&quot;,<br>    member_casual character varying COLLATE pg_catalog.&quot;default&quot;,<br>    CONSTRAINT ride_pkey PRIMARY KEY (ride_id)<br>)</pre><p>Ensure to use the correct data type for future data aggregation/manipulation and Tableau visualization.</p><p><em>STEP 2: Copying and merging data.</em></p><p>We will not use any JOIN or UNION command because we created only one table. Stacking the 12-month in the ride table created.</p><pre>COPY bike<br>--FROM &#39;/Users/wilmalapuz/Downloads/202111_Cyclistic.csv&#39; <br>--FROM &#39;/Users/wilmalapuz/Downloads/202112_Cyclistic.csv&#39; <br>--FROM &#39;/Users/wilmalapuz/Downloads/202201_Cyclistic.csv&#39; <br>--FROM &#39;/Users/wilmalapuz/Downloads/202202_Cyclistic.csv&#39; <br>--FROM &#39;/Users/wilmalapuz/Downloads/202203_Cyclistic.csv&#39; <br>--FROM &#39;/Users/wilmalapuz/Downloads/202204_Cyclistic.csv&#39; <br>--FROM &#39;/Users/wilmalapuz/Downloads/202205_Cyclistic.csv&#39; <br>--FROM &#39;/Users/wilmalapuz/Downloads/202206_Cyclistic.csv&#39; <br>--FROM &#39;/Users/wilmalapuz/Downloads/202207_Cyclistic.csv&#39; <br>--FROM &#39;/Users/wilmalapuz/Downloads/202208_Cyclistic.csv&#39; <br>--FROM &#39;/Users/wilmalapuz/Downloads/202209_Cyclistic.csv&#39; <br>FROM &#39;/Users/wilmalapuz/Downloads/202210_Cyclistic.csv&#39;<br>DELIMITER &#39;,&#39;<br>CSV Header;</pre><p><em>STEP 3: Data Familiarization.</em></p><p>Schema of the table created.</p><pre>SELECT *<br>FROM information_schema.columns<br>WHERE table_name = &#39;bike&#39;;</pre><p>Checking the total rows of the table</p><pre>SELECT COUNT(*)<br>FROM bike</pre><p>Getting the head of the table</p><pre>SELECT *<br>FROM bike <br>LIMIT 10</pre><p>We need to know how many distinct observation per column</p><pre>SELECT <br>    COUNT(DISTINCT ride_id) AS rideid<br>    ,COUNT(DISTINCT rideable_type) AS ridetype <br>    ,COUNT(DISTINCT started_at) AS startat<br>    ,COUNT(DISTINCT ended_at) AS endedat<br>    ,COUNT(DISTINCT start_station_name) AS startstation<br>    ,COUNT(DISTINCT start_station_id) AS startid<br>    ,COUNT(DISTINCT end_station_name) AS endtstation<br>    ,COUNT(DISTINCT end_station_id) AS endid<br>    ,COUNT(DISTINCT start_lat) AS startlat<br>    ,COUNT(DISTINCT start_lng) AS startlng <br>    ,COUNT(DISTINCT end_lat) AS endlat<br>    ,COUNT(DISTINCT end_lng) AS endlng       <br> ,COUNT(DISTINCT member_casual) AS membercasual   <br>FROM bike</pre><p>Since the count and count distinct of ride_id is the same, we will not use any delete duplicate function for this dataset.</p><p>How many nulls per column</p><pre>select <br>  sum(case when ride_id is null then 1 else 0 end) rideid<br>  ,sum(case when rideable_type is null then 1 else 0 end) ridetype<br>  ,sum(case when started_at is null then 1 else 0 end) startat<br>  ,sum(case when ended_at is null then 1 else 0 end) endat<br>  ,sum(case when start_station_name is null then 1 else 0 end) startstation<br>  ,sum(case when start_station_id is null then 1 else 0 end) startid<br>  ,sum(case when end_station_name is null then 1 else 0 end) endtstation<br>  ,sum(case when end_station_id is null then 1 else 0 end) endid<br>  ,sum(case when start_lat is null then 1 else 0 end) startlat<br>  ,sum(case when start_lng is null then 1 else 0 end) startlng<br>  ,sum(case when end_lat is null then 1 else 0 end) endlat<br>  ,sum(case when end_lng is null then 1 else 0 end) endlng<br>  ,sum(case when member_casual is null then 1 else 0 end) membercasual<br>from bike</pre><p>We will go on to the next dataset feature which is the rideable_type.</p><pre>SELECT rideable_type, COUNT(rideable_type)<br>FROM bike<br>GROUP BY rideable_type<br>ORDER BY COUNT(rideable_type) ASC</pre><p>There are 3 classes of types in this data; docked, classic and electric.</p><pre>SELECT started_at, ended_at<br>FROM bike<br>ORDER BY started_at ASC</pre><p><em>STEP 4: Adding/Dropping columns for data analysis.</em></p><p>Going back to our business question, which is to analyze the bike usage of members and casual riders, we will now list all the underlying queries that we can formulate to answer the business task.</p><p>-What is the popular start time of the cyclers?</p><p>-What is the average cycling time?</p><p>-What is the busiest and slowest day/month?</p><p>-What is the average distance per cycle?</p><p>-Where is the most active station/dormant station?</p><p>By those questions above, we will need to drop the end_station_names and station_id column which consists of almost 1 million NULLS per column. We will add columns like starttime (extracted from started_at), cycletime (elapsed time between ended_at and started_at), month/day/year (convert the timestamp to their designated month/day/year), and distance traveled (using point command).</p><pre>SELECT ride_id, rideable_type, started_at, ended_at, start_station_name AS station_name, start_lat, start_lng, end_lat, end_lng, member_casual<br>    ,started_at::time AS starttime<br>    ,extract<br>     (<br>             epoch from ((ended_at - started_at)/60)::interval<br>        )<br>         as cycletime<br> ,TO_CHAR(started_at, &#39;fmDay&#39;) AS &quot;day&quot;<br> ,TO_CHAR(started_at, &#39;fmMonth&#39;) AS &quot;month&quot;<br>    ,TO_CHAR(started_at, &#39;YYYY&#39;) AS &quot;year&quot;<br> ,((point(start_lng, start_lat) &lt;@&gt; point(end_lng, end_lat)) * 1.609)as distance<br>FROM bike <br>WHERE start_lat != end_lat AND start_lng != end_lng</pre><p>We will not stop there, we want to clean it further by using the ORDER BY command on our newly added column. We found out that there are negative cycle times and less than 5 meters cycling distance in some recorded rides. We will exclude those using the command below:</p><pre>SELECT *<br>from (SELECT ride_id, rideable_type, started_at, ended_at, start_station_name AS station_name, start_lat, start_lng, end_lat, end_lng, member_casual<br>    ,started_at::time AS starttime<br>    ,extract<br>     (<br>             epoch from ((ended_at - started_at)/60)::interval<br>        )<br>         as cycletime<br> ,TO_CHAR(started_at, &#39;fmDay&#39;) AS &quot;day&quot;<br> ,TO_CHAR(started_at, &#39;fmMonth&#39;) AS &quot;month&quot;<br>    ,TO_CHAR(started_at, &#39;YYYY&#39;) AS &quot;year&quot;<br> ,((point(start_lng, start_lat) &lt;@&gt; point(end_lng, end_lat)) * 1.609)as distance<br>FROM bike <br>WHERE start_lat != end_lat AND start_lng != end_lng) as Q <br>WHERE Q.cycletime &gt;2 AND Q.distance &gt; 0.005</pre><p>With that, we only now have 5,091,767 rows for us to upload in Tableau.</p><h4>ANALYSIS SUMMARY</h4><p>We will use Tableau to answer our questions that will help us to derive our recommendations.</p><figure><img alt="Google Capstone Case Study by Wilma Lapuz" src="https://cdn-images-1.medium.com/max/1024/1*3KJd4afMS3C8Yz8RmAMRYw.png" /><figcaption><a href="https://public.tableau.com/views/GoogleCapstoneCaseStudy/Dashboard1?:language=en-US&amp;:display_count=n&amp;:origin=viz_share_link">Google Capstone Case Study by Wilma Lapuz</a></figcaption></figure><p><em>What is the median Travel Time?</em></p><figure><img alt="Google Capstone Case Study by Wilma Lapuz" src="https://cdn-images-1.medium.com/max/294/1*tiT78-RsweJT0XfcTZe5Sw.png" /><figcaption>Casual Riders strolled more than the Members</figcaption></figure><p><em>What is the median distance per bike rented?</em></p><figure><img alt="Google Capstone Case Study by Wilma Lapuz" src="https://cdn-images-1.medium.com/max/298/1*fwYoJKnOHwn_Uxbw56BcVw.png" /><figcaption>Casual Riders had covers more distance than the Members</figcaption></figure><p><em>What is the busiest and slowest day/month?</em></p><figure><img alt="Casual Riders: Busiest July | Slowest January | Busiest Saturday | Slowest Tuesday WHILE Member Busiest August | Slowest January | Busiest Thursday | Slowest Sunday" src="https://cdn-images-1.medium.com/max/1024/1*96V47m9Jap9-jiC9e-kK7A.png" /><figcaption>Casual Riders: Busiest July | Slowest January | Busiest Saturday | Slowest Tuesday WHILE Member Busiest August | Slowest January | Busiest Thursday | Slowest Sunday</figcaption></figure><p><em>What is the popular start time of the cyclers?</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/964/1*utk9neNdYPKH1dmtf5tK5w.png" /><figcaption>Casual — Saturdays around 1500Hrs WHILE Members — Tuesday by 1700Hrs</figcaption></figure><p><em>Where is the most active station/dormant station?</em></p><figure><img alt="Most Active for Casual Riders: Streeter Dr &amp; Grand Ave" src="https://cdn-images-1.medium.com/max/1022/1*KD3dzjacJIzdUCt9cRJ3_A.png" /><figcaption>Most Active for Casual Riders: Streeter Dr &amp; Grand Ave</figcaption></figure><p><em>Streeter Dr &amp; Grand Ave </em>is located near the most popular tourist attractions/spots in Chicago, The Navy Pier.</p><figure><img alt="Most Active for Casual Riders: Wells St &amp; Concord Ln" src="https://cdn-images-1.medium.com/max/1018/1*uqxgcBQci8GljqQP8guBLA.png" /><figcaption>Most Active for Casual Riders: Wells St &amp; Concord Ln</figcaption></figure><p><em>Wells St &amp; Concord Ln </em>station is surrounded by communities, schools and a church.</p><p><em>Is there a trend or patterns in renters usage?</em></p><figure><img alt="Google Data Analytics Capstone: Cyclistic Case Study Using pgAdmin4, Tableau &amp; Anaconda" src="https://cdn-images-1.medium.com/max/1024/1*zxBhMfHj7UsXmGt0nIBrjw.png" /><figcaption>Winter Season</figcaption></figure><figure><img alt="Google Data Analytics Capstone: Cyclistic Case Study Using pgAdmin4, Tableau &amp; Anaconda" src="https://cdn-images-1.medium.com/max/1024/1*ob9jwk0_Tz8JZOkNDSrjiw.png" /><figcaption>Spring Season</figcaption></figure><figure><img alt="Google Data Analytics Capstone: Cyclistic Case Study Using pgAdmin4, Tableau &amp; Anaconda" src="https://cdn-images-1.medium.com/max/1024/1*UMemzF7jBXpvMxuOdUvbZg.png" /><figcaption>Summer Season</figcaption></figure><figure><img alt="Google Data Analytics Capstone: Cyclistic Case Study Using pgAdmin4, Tableau &amp; Anaconda" src="https://cdn-images-1.medium.com/max/1024/1*KfHPwBmJaiG9P-26FuxD6Q.png" /><figcaption>Autumn Season</figcaption></figure><p>The Spring Season figure shows that there is a positive upward trend of renters at the end of season. Then there is a steady trend of bike rentals n the Summer season and negative down slope when the winter is nearing.</p><p>Classic bikes are opted to be rented in the Summer and Winter season too while electric bikes are the popular choice for Spring and Autumn season.</p><p><em>Is there a correlation?</em></p><p>To prove if there is correlation between number of rented bikes and season, I downloaded the <a href="https://www.weather.gov/wrh/climate?wfo=lot">average temperature history</a> and loaded it in Anaconda.</p><pre>import pandas as pd<br>tempcalc = pd.read_csv(&#39;temp_calc.csv&#39;)<br>tempcalc.corr()</pre><figure><img alt="Google Capstone Case Study by Wilma Lapuz — Correlation" src="https://cdn-images-1.medium.com/max/792/1*aVwLGgyQdAl8jk-KX4KSEw.png" /><figcaption>Correlation</figcaption></figure><p>A correlation coefficient of is too far from 1, meaning there is no linear relationship.</p><h4>KEY FINDINGS</h4><ul><li>Members rented 60% of the total rides.</li><li>53% of Casual Riders prefer electric bikes while 54% of Members chose classic bikes.</li><li>Casual Riders travel longer and farther than the Member.</li><li>The usual starting station of Members are from the community area while Casual Riders are from Chicago’s Bay Area.</li><li>Season affects the bike rentals.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/290/1*QK6XadXyT-mW1SyHnJxQGg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/720/1*D0zPjWXrZ2N9KeWduK5VLA.png" /></figure><h4><strong>RECOMMENDATIONS</strong></h4><p>Since demographics of bike users were anonymized, the recommendations will only evolve in the three variables: kind of bike rented, stations and time.</p><p><strong><em>Create an Exclusive Membership Reward Points System:</em></strong></p><ul><li>Friday, Saturday, and Sunday by 3PM is the peak period for casual riders, thus an exclusive membership reward program will offer their leisure rides <em>extra reward points </em>while using bikes on the said peak periods from those popular stations.</li><li>Casual riders also be offered a <em>discounted rate per minute</em> for the electric bike for the first three months.</li><li>For members, they will receive <em>reward points </em>for every rider that will purchase an annual membership using their invite code.</li></ul><p><strong><em>Voucher Codes</em></strong></p><ul><li>Casual riders may increase their annual membership discount based on their past bike rentals to and from the popular stations.</li><li>Voucher codes for annual membership can be offered at the start of Spring Season in preparation for the upcoming peak rental season.</li></ul><p><strong><em>Exclusive for Health Enthusiast Members</em></strong></p><ul><li>Since casual riders were fond of renting an electric bike, Cyclistic may promote the use of classic bike and entice them by challenge badges that can be shared in social media.</li><li>Casual riders may have the option to save their cycled routes and to be featured on next popular/new discovered exploration routes in Cyclistic website.</li><li>Promote an extended free cycling time for members and accompanying health benefits if they cycle on weekends’ off-peak times from 5AM — 9AM.</li></ul><h4>FOR FURTHER EXPLORATION</h4><ul><li>Check the age brackets and financial capacity of casual riders and then create a focus group to discuss their takes on current membership strategy of Cyclistic.</li></ul><h4>REFERENCES</h4><ul><li><strong><em>DATA SOURCE:</em></strong> <a href="https://divvy-tripdata.s3.amazonaws.com/index.html">https://divvy-tripdata.s3.amazonaws.com/index.html</a></li><li><strong><em>DASHBOARD:</em></strong> <a href="https://public.tableau.com/views/GoogleCapstoneCaseStudy/Dashboard1?:language=en-US&amp;:display_count=n&amp;:origin=viz_share_link">WML CYCLISTIC CASE STUDY 2022</a></li><li><strong><em>PRESENTATION:</em></strong> <a href="https://docs.google.com/presentation/d/1DTYJ_42ZFwjNYNU3R2iLnOuzXfxrknTyDAvyvFVvd18/edit?usp=sharing">UNDERSTANDING THE RIDE!</a></li><li><strong><em>LINKEDIN:</em></strong><em> </em><a href="https://www.linkedin.com/in/wilmamlapuz/">WILMA LAPUZ</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b089afd0665" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Community for Data Career Shifters]]></title>
            <link>https://medium.com/@WilmaLapuz/community-for-data-career-shifters-d7dcb1c42ed6?source=rss-8398c48e812e------2</link>
            <guid isPermaLink="false">https://medium.com/p/d7dcb1c42ed6</guid>
            <category><![CDATA[she-loves-data]]></category>
            <category><![CDATA[data-analyst]]></category>
            <category><![CDATA[community-data]]></category>
            <category><![CDATA[data-analyst-course]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Wilma Lapuz]]></dc:creator>
            <pubDate>Sat, 13 Aug 2022 13:21:17 GMT</pubDate>
            <atom:updated>2022-08-13T13:21:17.111Z</atom:updated>
            <content:encoded><![CDATA[<h3>I needed a network. A network that can understand me.</h3><p>Who can understand a woman at the age of 40 who will suddenly pivot to an unknown world of data? There are many questions lingering in my head. Is this a wise decision? Will it bring joy and excitement to my career? Will it sustain me financially? But before I can answer those questions for myself I realised that attending courses will not suffice for me to be a Data Analyst. I needed someone to teach me and make me comfortable in every step that I need to take. With that, I looked for a community that has this culture who believes to the capacity of a late career shifter like me. Then, I found<a href="https://www.shelovesdata.com/"> SHE LOVES DATA</a>.</p><p>2021 I was assigned to be on a group under Singapore Chapter but Richa, Head of Global Operations, reached out and let me to be a part of her team. Inch by inch with guidance from Venetia and trust of Richa I am now managing some of She Loves Data Signature Program. I can know first hand the dates of trainings I can attend and somewhat a free brain picking from top notch instructors of She Loves Data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sD6v2bipkGp8hbOJGd_Z7A.png" /><figcaption><a href="https://www.shelovesdata.com/">SHE LOVES DATA SINGAPORE BASED VOLUNTEERS</a></figcaption></figure><p>Last Thursday (11 August 2022), it is a big in-person event for She Loves Data! The very first Singapore event for 2022. The vibe is wonderful! Quinn and Aimee removed my doubts and gave comfort to alleviate my anxiety. Ohhh.. I want to cry now. They have told me stuffs that makes me think and push away the imposter syndrome out of my head.</p><p>The event is CELEBRATE YOU and it clearly stands on its name. I know and I feel that every woman and man in that room have picked some nuggets of wisdom from each other. That we all felt the warmth of community and someone is behind our back to achieve not only our data career goals but also developing ourselves for betterment.</p><p>Thank you Jana for building this kind of network.. a network of women that can understand what a career shifter like me is going through. A camaraderie indeed! <br>Thank you for the free workshops that enhances and challenges our skills. Thank you so much.</p><p>With all that, I can say that I am confident to join the data workforce and let’s crunch those data! *wink-wink*</p><p>May ALMIGHTY always bless SHE LOVES DATA and all the volunteers behind it! Onward Sisters!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d7dcb1c42ed6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The Yelp Dataset using SQLite]]></title>
            <link>https://medium.com/@WilmaLapuz/the-yelp-dataset-using-sqlite-14f06ca87224?source=rss-8398c48e812e------2</link>
            <guid isPermaLink="false">https://medium.com/p/14f06ca87224</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[yelp-dataset]]></category>
            <category><![CDATA[data-analyst]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[sqlite]]></category>
            <dc:creator><![CDATA[Wilma Lapuz]]></dc:creator>
            <pubDate>Fri, 14 Jan 2022 08:46:50 GMT</pubDate>
            <atom:updated>2022-01-14T08:46:50.371Z</atom:updated>
            <content:encoded><![CDATA[<p><strong><em>Whats is Yelp?</em></strong> It aims to provide a one-stop platform for local businesses. Like other social media, it creates community. And that community discovers, transacts, and reviews local businesses around them.</p><p>For this post we will use Yelp dataset and will formulate ideas for analysis.</p><p><em>First, we need to explore the dataset.</em> Below is the structure data in table form.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/822/1*FCi842EUpc1Fgfgmwdkrsw.png" /><figcaption>The Yelp Dataset</figcaption></figure><p>And for me to understand the above data and the entirety of its contents, I have visited the YELP website and marked the table names and column names against its pages.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0ZIUhIyob9kqN5CLYGaduw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/916/1*YawRZh8jhaaidbUTsISs-A.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/968/1*JhFYKNUDMIdwPB5YJPE6AA.png" /><figcaption>The Yelp Dataset— Business, Review, &amp; User Page</figcaption></figure><p>Armed with understanding, profiling and establishing relationship among multiple tables, you can now look onto subjects that can be useful for different stakeholders.</p><h4>Some of the business questions lingered on my mind upon seeing the above data were:</h4><ul><li>What cities could be the best choice in setting up a business?</li><li>Are there weight on the reviews coming from elite year users?</li><li>What are the main words used in text reviews for sentiment analysis?</li><li>Finding the effect of review_count and check-in on the life of a business.</li></ul><p><em>I focused on the latter due to the limitations of SQLite.</em></p><pre>SELECT business.name<br>, business.stars<br>, business.review_count<br>, business.is_open<br>, SUM(checkin.count)<br>FROM business INNER JOIN checkin ON business.id =checkin.business_id<br>GROUP BY business.name<br>ORDER BY SUM(checkin.count) DESC</pre><p>Two (2) tables were needed to get the answer for the above analysis. I used INNER JOIN to avoid getting the ‘None’ value.</p><p>The below selected data will prove that even a company received only 1 star it is still open for business. That businesses that received highest checkin count do not necessarily have 5 stars and so on. Thus, this data means that there is no direct correlation on stars, review_count and checkin.</p><p>It would be good for YELP to indicate the current population for the neighbourhood on that business.</p><figure><img alt="The Yelp Dataset — Correlation of Review Count, Checkin, and Stars" src="https://cdn-images-1.medium.com/max/1024/1*CORJBvhY0vUeT9O4fm8UJw.png" /><figcaption><a href="https://github.com/WilmaLapuz/Portfolio/blob/main/The%20Yelp%20Dataset%20using%C2%A0SQLite">The Yelp Dataset — Correlation of Review Count, Checkin, and Stars</a></figcaption></figure><p>That’s all for now! Be curious y’all! Don’t forget to click the clap button! *wink-wink*</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=14f06ca87224" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Exploring Supply Chain Dataset]]></title>
            <link>https://medium.com/@WilmaLapuz/exploring-supply-chain-dataset-a83e5994227f?source=rss-8398c48e812e------2</link>
            <guid isPermaLink="false">https://medium.com/p/a83e5994227f</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[logistics-analytics]]></category>
            <category><![CDATA[supply-chain-analytics]]></category>
            <category><![CDATA[supply-chain]]></category>
            <category><![CDATA[data-visualization]]></category>
            <dc:creator><![CDATA[Wilma Lapuz]]></dc:creator>
            <pubDate>Mon, 20 Dec 2021 08:58:15 GMT</pubDate>
            <atom:updated>2021-12-20T08:58:15.741Z</atom:updated>
            <content:encoded><![CDATA[<p>As I have written on my past posts, supply chain / logistics companies are one of the wells of data and most of them are not aware of it.</p><p>For this post, I will scratch some parts of data cleaning and apply some descriptive analysis on the data I have found in Kaggle.</p><p>Here are the usual steps I am taking in cleaning the data.</p><p>As practised, these are the main libraries that I usually use in Python and let’s import these now.</p><pre><strong>import</strong> pandas <strong>as</strong> pd<br><strong>import</strong> numpy <strong>as</strong> np<br><strong>import</strong> matplotlib.pyplot <strong>as</strong> plt<br><strong>import</strong> seaborn <strong>as</strong> sns</pre><p>Now, let’s call in my downloaded dataset titled: DataCoSupplyChainDataset.csv. It is very important to know too the description of every column name we have in the csv and it is very helpful that we found a dataset that also has this feature.</p><pre>df <strong>=</strong> pd<strong>.</strong>read_csv(&#39;DataCoSupplyChainDataset.csv&#39;)<br>info <strong>=</strong> pd<strong>.</strong>read_csv(&#39;DescriptionDataCoSupplyChain.csv&#39;)<br>pd<strong>.</strong>set_option(&#39;max_colwidth&#39;, 1)<br>info</pre><p>Now let’s explore our data.</p><pre>pd<strong>.</strong>set_option(&#39;display.max_columns&#39;, <strong>None</strong>)<br>df<strong>.</strong>head()<br>df<strong>.</strong>info()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/970/1*e6KFrk6Wnuf4h3q4kWveqQ.png" /><figcaption><a href="https://github.com/WilmaLapuz/Portfolio/blob/main/SUPPLYCHAIN.ipynb">https://github.com/WilmaLapuz/Portfolio/blob/main/SUPPLYCHAIN.ipynb</a></figcaption></figure><p>Have you noticed what is wrong on the datatype on above column? Shipping date (DateOrders) is in object. Let’s convert it to a proper data type.</p><pre>df[&#39;shipping date (DateOrders)&#39;] <strong>=</strong> pd<strong>.</strong>to_datetime(df[&#39;shipping date (DateOrders)&#39;], format<strong>=</strong>&#39;%m/%d/%Y %H:%M&#39;)<br>df[&#39;order date (DateOrders)&#39;] <strong>=</strong> pd<strong>.</strong>to_datetime(df[&#39;shipping date (DateOrders)&#39;], format<strong>=</strong>&#39;%m/%d/%Y %H:%M&#39;)<br>df<strong>.</strong>info()</pre><h4>Let’s further simplify this data.</h4><p>If you will check the values on each row, you will find that there are somewhat duplicates of columns. To see what other columns we need to drop, let’s check what columns consist equal values.</p><pre>df[&#39;Customer Id&#39;]<strong>.</strong>equals(df[&#39;Order Customer Id&#39;])<br>df[&#39;Benefit per order&#39;]<strong>.</strong>equals(df[&#39;Order Profit Per Order&#39;])<br>df[&#39;Order Item Cardprod Id&#39;]<strong>.</strong>equals(df[&#39;Product Card Id&#39;])</pre><p>Drop all sensitive and duplicate/redundant variables to clean our data.</p><pre>df<strong>.</strong>drop([<br>    &#39;Benefit per order&#39;,<br>    &#39;Customer Email&#39;, <br>    &#39;Customer Password&#39;,<br>    &#39;Product Image&#39;,<br>    &#39;Order Zipcode&#39;,<br>    &#39;Product Description&#39;,<br>    &#39;Order Item Cardprod Id&#39;,<br>    &#39;Order Customer Id&#39;<br>], axis <strong>=</strong> <strong>True</strong>, inplace <strong>=</strong> <strong>True</strong>)<br><br>df<strong>.</strong>head()</pre><blockquote><em>These are the few variables that caught my eyes:</em></blockquote><ol><li>Type</li><li>Late_delivery_risk</li><li>Customer State</li><li>Order Country</li><li>Order Region</li><li>Order Status</li><li>Product Name</li><li>Shipping Mode</li></ol><p>Using the above list of variables and describe function, here are the questions that may a supply chain want to be known.</p><ol><li>What type of payment that will be likely to be fraud? From what country? What product?</li></ol><pre>fraud<strong>=</strong>df[df[&#39;Order Status&#39;]<strong>==</strong>&#39;SUSPECTED_FRAUD&#39;]<br>fraud_payment<strong>=</strong>fraud[&#39;Type&#39;]<strong>.</strong>value_counts()<strong>.</strong>nlargest()<strong>.</strong>plot<strong>.</strong>bar(figsize<strong>=</strong>(20,8), title<strong>=</strong>&quot;Payment Type With Suspected Fraud Cases&quot;)</pre><pre>fraud_ordercountry<strong>=</strong>fraud[&#39;Order Country&#39;]<strong>.</strong>value_counts()<strong>.</strong>nlargest()<strong>.</strong>sort_values(ascending<strong>=False</strong>)<strong>.</strong>plot<strong>.</strong>bar(figsize<strong>=</strong>(20,8), title<strong>=</strong>&quot;Top 5 Countries With Suspected Fraud Case&quot;)</pre><pre>fraud_ordercountry<strong>=</strong>fraud[&#39;Product Name&#39;]<strong>.</strong>value_counts()<strong>.</strong>nlargest()<strong>.</strong>sort_values(ascending<strong>=True</strong>)<strong>.</strong>plot<strong>.</strong>barh(figsize<strong>=</strong>(20,8), title<strong>=</strong>&quot;Top 5 Products With Suspected Fraud Case&quot;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gpv0axs0PtHOL9anIjUQSQ.png" /><figcaption><a href="https://github.com/WilmaLapuz/Portfolio/blob/main/SUPPLYCHAIN.ipynb">https://github.com/WilmaLapuz/Portfolio/blob/main/SUPPLYCHAIN.ipynb</a></figcaption></figure><p>2. What year has the most oder shipment from the state of Illinois?</p><pre>df[&#39;year&#39;] <strong>=</strong> pd<strong>.</strong>DatetimeIndex(df[&#39;order date (DateOrders)&#39;])<strong>.</strong>year<br>IL<strong>=</strong>df[df[&#39;Customer State&#39;]<strong>==</strong>&#39;IL&#39;]<br>IL[&#39;year&#39;]<strong>.</strong>value_counts()<strong>.</strong>plot<strong>.</strong>bar(figsize<strong>=</strong>(20,8), title<strong>=</strong>&quot;Illinois Record of Shipments&quot;)</pre><p>3. What shipping mode and region that has a higher delivery risk?</p><pre>LATE<strong>=</strong>df[df[&#39;Delivery Status&#39;] <strong>==</strong> &#39;Late delivery&#39;]<br>LATE[&#39;Shipping Mode&#39;]<strong>.</strong>value_counts()<strong>.</strong>plot<strong>.</strong>bar(figsize<strong>=</strong>(20,8), title<strong>=</strong>&quot;Shiping Mode with Risk of Late Delivery&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JQu61ylsKn1wbNVdZDv5VA.png" /><figcaption><a href="https://github.com/WilmaLapuz/Portfolio/blob/main/SUPPLYCHAIN.ipynb">https://github.com/WilmaLapuz/Portfolio/blob/main/SUPPLYCHAIN.ipynb</a></figcaption></figure><p>This is only the start of many things what we can uncover using this supply chain dataset. I will do my best to use this dataset for my other upcoming projects.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a83e5994227f" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>