<?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 Umi Nabilah  on Medium]]></title>
        <description><![CDATA[Stories by Umi Nabilah  on Medium]]></description>
        <link>https://medium.com/@uminabilah?source=rss-4920223f146c------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*Nrj4ZtIoUsZZbc31</url>
            <title>Stories by Umi Nabilah  on Medium</title>
            <link>https://medium.com/@uminabilah?source=rss-4920223f146c------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 17:14:38 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@uminabilah/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[Sales Performance Analysis]]></title>
            <link>https://medium.com/@uminabilah/sales-performance-analysis-fd014834f713?source=rss-4920223f146c------2</link>
            <guid isPermaLink="false">https://medium.com/p/fd014834f713</guid>
            <category><![CDATA[exploratory-data-analysis]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-analyst]]></category>
            <dc:creator><![CDATA[Umi Nabilah ]]></dc:creator>
            <pubDate>Sat, 14 Oct 2023 07:37:31 GMT</pubDate>
            <atom:updated>2023-10-14T07:37:31.759Z</atom:updated>
            <content:encoded><![CDATA[<p>For this project, I will use SQL and Python to analyze the data. The data was collected from 10 different shopping malls between 2021 and 2023, encompassing various age groups and genders in Istanbul. This comprehensive dataset aims to offer insights into shopping habits and provide a valuable understanding of shopping patterns.</p><p>The primary objective of this data analysis is to comprehend and identify trends in customer purchasing behavior. It will involve analyzing the most frequently used product categories and payment methods by customers, identifying the mall with the highest sales, and examining how sales evolve month by month. In essence, this data analysis will aid in identifying the factors that influence customer behavior in Istanbul.</p><h4>How much is the Mall’s revenue by product category?</h4><p>To obtain the results, I will use an SQL query similar to the one below:</p><pre>WITH t1 AS (<br>SELECT <br>  DISTINCT category,<br>  SUM(quantity * price) AS r1,<br>  REGEXP_REPLACE(REVERSE(CAST(ROUND(SUM(quantity * price), 0) AS STRING )), &#39;(\\d{3})&#39;, &#39;\\1.&#39;) AS revenue<br>FROM `projectbyumi.customer_shopping.transaction`<br>GROUP BY 1)<br><br>SELECT <br>  category,<br>  REVERSE(CASE WHEN RIGHT(revenue, 1) = &#39;.&#39; THEN LEFT(revenue, LENGTH(revenue) - 1) ELSE revenue END) AS total_revenue<br>FROM t1<br>ORDER BY r1 DESC</pre><p>And here is the result,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/459/1*C5xZJmHveJf9ZtRPbBfVYg.png" /></figure><p>From the result above, 45.3% of the revenue is generated from the sales of products in the Clothing category, with a total of 103,558 units sold. Meanwhile, the Souvenir category has the lowest sales with 14,871 units. Souvenirs have the second-lowest price after Food &amp; Beverages, which is 34.9.</p><p>A suggestion to increase souvenir sales is to create bundling packages between souvenirs and high-selling products such as clothing and shoes. These bundling packages can be sold at a lower price, which will attract consumers to make a purchase.</p><h4>How much is the Mall’s revenue based on customer age?</h4><p>If previously I looked for income based on category, this time I am searching for income based on customer age to identify the age range of customers with the largest nominal transactions.</p><pre>WITH t1 AS(<br>SELECT <br>  CASE WHEN age &lt;= 30 THEN &#39;&lt;= 30&#39;<br>    WHEN age BETWEEN 31 AND 40 THEN &#39;31 to 40&#39;<br>    WHEN age BETWEEN 41 AND 50 THEN &#39;41 to 50&#39;<br>    WHEN age BETWEEN 51 AND 60 THEN &#39;51 to 60&#39;<br>    WHEN age &gt;60 THEN &#39;&gt;60&#39;<br>    ELSE &#39;Unknown&#39; END AS age_group,<br>  REGEXP_REPLACE(REVERSE(CAST(ROUND(SUM(quantity * price), 0) AS STRING )), &#39;(\\d{3})&#39;, &#39;\\1.&#39;) AS revenue<br>FROM `projectbyumi.customer_shopping.transaction`<br>GROUP BY 1)<br><br>SELECT <br>  age_group,<br>  REVERSE(CASE WHEN RIGHT(revenue, 1) = &#39;.&#39; THEN LEFT(revenue, LENGTH(revenue) - 1) ELSE revenue END) AS total_revenue<br>FROM t1</pre><p>With the query above, here are the results.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/462/1*A4to8-lnhN9PeK5aZyeo9w.png" /></figure><p>The majority of consumers are individuals under the age of 30, contributing 63,047,402 Turkish Lira, which is equivalent to 25.06% of the total. In contrast, other age groups share a similar proportion.</p><p>Citing the findings of Kredivo and Katadata Insights Center (KIC) research, it is evident that the millennial generation, aged 26–35, accounts for 48% of total e-commerce transactions. Therefore, increasing the contribution of buyers aged 31 to 40 can be achieved through targeted marketing efforts aimed at this age group.</p><h4>What payment methods are often used?</h4><p>I will create a table showing the percentage of each payment method based on the shopping range to determine if there are differences in patterns between small and large transaction amounts.</p><pre># Creating conditions to categorize income ranges<br>def range_revenue(x):<br>  if x &lt;= 500:<br>    return &#39;&lt;500&#39;<br>  elif x &lt;= 5000:<br>    return &#39;501 to 5000&#39; <br>  elif x &lt;= 10000:<br>    return &#39;5001 to 10000&#39;<br>  elif x &lt;= 15000:<br>    return &#39;10001 to 15000&#39;<br>  elif x &lt;= 20000:<br>    return &#39;15001 to 20000&#39;<br>  elif x &lt;= 25000:<br>    return &#39;20001 to 25000&#39;<br>  else:<br>    return &#39;&gt;25001&#39;<br><br># Creating an income range column<br>df[&#39;range_revenue&#39;] = df[&#39;revenue&#39;].apply(range_revenue)<br><br># Calculating payment methods for each income range<br>df_method_by_revenue = df.groupby([&#39;range_revenue&#39;, &#39;payment_method&#39;]).size().reset_index(name=&#39;total_payment_method&#39;)<br><br># Calculating total orders for each income range<br>df_total_by_revenue = df.groupby([&#39;range_revenue&#39;])[&#39;invoice_no&#39;].size().reset_index(name=&#39;total_order&#39;)<br><br># Merging the two dataframes above<br>df_method = pd.merge(df_method_by_revenue, df_total_by_revenue, on=&#39;range_revenue&#39;)<br><br># Calculating the percentage of payment methods within each income range<br>df_method[&#39;percent&#39;] = df_method[&#39;total_payment_method&#39;] / df_method[&#39;total_order&#39;] * 100<br><br># Creating a pivot table<br>pivot2 = pd.pivot_table(df_method, index=&#39;payment_method&#39;, values=&#39;percent&#39;, columns=&#39;range_revenue&#39;)<br>pivot2</pre><p>And here is the result,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/627/1*9cqAf9nWe8nrsdBjUmlLaQ.png" /></figure><p>Approximately 45% of consumers prefer to use cash as their payment method for shopping, while 20% opt for debit cards. These percentages remain consistent across various income ranges.</p><p>On the other hand, non-cash payments offer advantages, such as convenience, avoidance of counterfeit money, and the elimination of the need for change. Suggestions for enhancing non-cash payment methods include offering rewards, such as discounts, or providing additional payment service options, such as e-wallets.</p><h4><strong>Is there a difference in the buying behavior among customer age groups for various products?</strong></h4><p>First, I will classify customers by age and then calculate the income for each group based on categories.</p><pre># Creating conditions to group customer ages<br>def age_group(x):<br>  if x &lt;= 30:<br>    return &#39;&lt;= 30&#39;<br>  elif x &lt;= 40:<br>    return &#39;31 to 40&#39;<br>  if x &lt;= 50:<br>    return &#39;41 to 50&#39;<br>  elif x &lt;= 60:<br>    return &#39;51 to 60&#39;<br>  else:<br>    return &#39;&gt;60&#39;<br><br># Creating an age group column<br>df[&#39;age_group&#39;] = df[&#39;age&#39;].apply(age_group)<br><br># Calculating total revenue for each category and age group<br>df_total = df.groupby([&#39;category&#39;, &#39;age_group&#39;])[&#39;revenue&#39;].sum().reset_index()<br><br># Calculating total revenue for each age group<br>df_age_group_total = df.groupby([&#39;age_group&#39;])[&#39;revenue&#39;].sum().reset_index()<br><br># Merging the two dataframes above<br>df_merge = pd.merge(df_total, df_age_group_total, on=&#39;age_group&#39;, suffixes=(&#39;_cat&#39;, &#39;_total&#39;))<br><br># Calculating the percentage of total for each category in a specific age group<br>df_merge[&#39;percent&#39;] = df_merge[&#39;revenue_cat&#39;] / df_merge[&#39;revenue_total&#39;] * 100<br><br># Creating a pivot table<br>pivot3 = pd.pivot_table(df_merge, index=&#39;age_group&#39;, columns=&#39;category&#39;, values=&#39;percent&#39;)<br>pivot3</pre><p>And here is the result,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/785/1*Ij6LK_Ej_LyAAwKb9UMsAg.png" /></figure><p>All age groups tend to purchase the same products, with 45% opting for clothing. However, a significant imbalance exists in sales per category, where clothing, shoes, and technology each account for more than 20% of the total. Meanwhile, each of the other categories represents less than 5% of the total.</p><p>To boost sales, marketing media optimization can be done, quality improvement can be undertaken, and targeting more specific age groups can be pursued for maximum results.</p><h4>How much revenue by shopping mall per quarter?</h4><pre># Converting the format to datetime<br>df[&#39;invoice_date&#39;] = pd.to_datetime(df[&#39;invoice_date&#39;], format=&#39;%d/%m/%Y&#39;)<br><br># Creating a new column for the quarter period<br>df[&#39;quarter&#39;] = df[&#39;invoice_date&#39;].dt.to_period(&#39;Q&#39;)<br><br># Creating a pivot table for mall revenue per quarter<br>pivot4 = df.pivot_table(index=&#39;quarter&#39;, values=&#39;revenue&#39;, columns=&#39;shopping_mall&#39;, aggfunc=&#39;sum&#39;)<br><br># Creating a line chart<br>ax = pivot4.plot(kind=&#39;line&#39;, figsize=(10, 5))<br><br># Setting the title and axis labels<br>ax.set_title(&#39;Quarterly Revenue by Shopping Mall&#39;)<br>ax.set_xlabel(&#39;Quarter&#39;)<br>ax.set_ylabel(&#39;Revenue&#39;)<br><br># Placing the legend outside the chart box<br>ax.legend(loc=&#39;center left&#39;, bbox_to_anchor=(1, 0.5))<br><br># Displaying the chart<br>plt.show()</pre><p>From the syntax above, the graph below is obtained.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1021/1*VeR_PYWq7_mCJg7oHDLPdA.png" /></figure><p>Mall of Istanbul and Kanyon consistently lead in revenue from period to period. While they are not the largest malls in Turkey, they attract a substantial number of visitors due to their diverse range of shops, restaurants, and entertainment options. In contrast, the malls with the lowest income are Cevahir AVM, Forum Istanbul, Viaport Outlet, and Zorlu.</p><p>To boost sales, the focus can be placed on increasing foot traffic to these four malls, particularly during the periods when they perform the lowest. Strategies may include organizing events tailored to the interests of young people, enhancing facilities and services, and more.</p><h3>Sales Performance Dashboard</h3><p>Following the analysis using SQL and Python, I created a dashboard like the one below,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/919/1*tBUcR4wPpWvKBJRs6ZIZKw.png" /></figure><p>Insights:</p><ol><li>The monthly revenue growth does not demonstrate a positive trend.</li><li>Women make up 60% of the customer base for product purchases.</li></ol><p>Policy recommendations include conducting regular market analysis and gaining an in-depth understanding of customers, updating and improving the products or services offered, focusing on delivering satisfying customer experiences, and maintaining good customer relationships. Given that the majority of customers are women, ensure that the mall provides a diverse range of products and brands catering to their preferences and needs, such as accessories and beauty products. Additionally, consider organizing fashion shows and beauty workshops to pique the interest of female customers.</p><p>That’s my analysis this time, you can find my notebook <a href="https://colab.research.google.com/drive/1hwA7pnPWFZlD3sOwJyTlXp5rVkTw9WhK?usp=sharing">here</a> and access the dashboard <a href="https://public.tableau.com/views/SalesPerformanceDashboard_16842329250240/Dashboard1?%3Alanguage=en-US&amp;publish=yes&amp;%3Adisplay_count=n&amp;%3Aorigin=viz_share_link">here</a>.</p><p>Project by Umi Nabilah<br>E-Mail : uminabilah.2000@gmail.com</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fd014834f713" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Django E-Commerce]]></title>
            <link>https://medium.com/@uminabilah/django-e-commerce-74cbfae18116?source=rss-4920223f146c------2</link>
            <guid isPermaLink="false">https://medium.com/p/74cbfae18116</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-analyst]]></category>
            <category><![CDATA[django]]></category>
            <dc:creator><![CDATA[Umi Nabilah ]]></dc:creator>
            <pubDate>Sat, 14 Oct 2023 07:27:25 GMT</pubDate>
            <atom:updated>2023-10-14T07:27:25.631Z</atom:updated>
            <content:encoded><![CDATA[<h3>About this Project</h3><p>This project was created as a requirement for passing the Woman in Tech: Python and Cybersecurity training program. I was assigned to innovate on templates, such as completing website features starting from activating category filters, adding contact pages, to adding product recommendations. I completely changed this website into a cosmetics store called Beauberry.</p><p>Source: <a href="https://github.com/atnlie/ecommerce-django-project/tree/all_auth">Template</a><br>Templates View : <a href="https://mdbootstrap.com/snippets/standard/mdbootstrap/4809942">Home</a>, <a href="https://mdbootstrap.com/snippets/standard/mdbootstrap/4809951">Product Detail</a>, <a href="https://mdbootstrap.com/snippets/standard/mdbootstrap/4809952">Checkout</a></p><h3>Homepage</h3><p>For the main page, I changed the banner, logo, product, and color palette according to the cosmetic brand that I created.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iOjQPoTrxFgJBmM51FomFA.png" /></figure><h3>Product List</h3><p>Apart from changing the product, I also set the display of the product name by limiting it so that only the first 5 words appear so that the card size between products remains the same</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zSi4iipdyX2ycKsK9XMwYQ.png" /></figure><h3>Category Filter</h3><p>I expanded the website’s functionality by introducing a new product list page, seamlessly integrated into the navigation bar on the homepage. This addition enhances user experience, allowing visitors to browse a variety of products within specific categories effortlessly.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*P9NlOz7F1QrcknBcUQHXvg.png" /></figure><h3>Search Result</h3><p>In addition to these enhancements, I implemented a search feature, making it significantly easier for users to find their desired products swiftly. This search functionality streamlines the shopping experience, allowing visitors to quickly locate specific items or explore a wide range of options within the Beauberry cosmetics store.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hfhmEpl0pQ7J8mDe1euYKg.png" /></figure><h3>Product Detail</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1zYSEHeB68BF_xYP7W6d9Q.png" /></figure><p>The product detail page is designed to seamlessly appear when a user clicks on a product, whether they access it from the homepage, apply category filters, or retrieve it from search results. This user-friendly feature ensures that shoppers can access comprehensive information and details about their chosen products with ease.</p><h3>Product Recommendation</h3><p>At the bottom of the product details page, I thoughtfully implemented a user-centric feature by incorporating a curated list of product recommendations. These recommendations are tailored to the specific category of the product the user is viewing, ensuring a more personalized shopping experience. This intuitive addition aims to guide and assist users in discovering additional products within the same category that may pique their interest and ultimately enhance their shopping journey on the Beauberry cosmetics store.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EJcb9FQy-RMkF9ZN6JAiaQ.png" /></figure><h3>Sign In</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fHCmckMJ3cBjWx2kdKAk-Q.png" /></figure><p>The sign-in page will seamlessly and intuitively activate when a user attempts to add a product to their cart but hasn’t yet logged into their account. This user-friendly approach ensures a smooth transition, allowing customers to easily access their accounts and complete their desired actions.</p><h3>Sign Up</h3><p>The sign-up page is thoughtfully designed to cater to new users who are looking to create an account and explore the features and offerings of the Beauberry cosmetics store.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NhPlRW6CmB7WOAQ0ttDucQ.png" /></figure><h3>Order Summary</h3><p>In addition to containing products that have been added to the cart, the order summary page provides information regarding the total items to be purchased and their nominal value.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FZ5DfO5xkTjQ9su8BnrX3g.png" /></figure><h3>Checkout</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4Ec_9hyMjuegOzaGu0KXxA.png" /></figure><p>On the checkout page, users are provided with the convenience of adding multiple shipping addresses and selecting their preferred payment methods, ensuring a seamless and personalized shopping experience tailored to their preferences and needs.</p><h3>Payment</h3><p>Once the user has completed the address details and made their preferred payment method selection, they will be seamlessly directed to the chosen payment gateway for a secure and hassle-free transaction process.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*trOhxh5mGfmj8PTthKDUAg.png" /></figure><h3>Contact Us</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UNDfjNkrFku5zhOSorBSZA.png" /></figure><p>The addition of the contact page is aimed at enhancing user experience by offering a convenient and direct channel for users to get in touch with the company. This user-friendly feature facilitates communication and ensures that users can easily reach out to us with their inquiries or feedback.</p><h3>Cybersecurity</h3><p>To bolster website security, we’ve implemented a login protection mechanism that limits login attempts to three. In the event of three consecutive login failures, the user is required to wait for 60 seconds before they can make another login attempt. This security measure helps safeguard the website against potential login attacks and unauthorized access.</p><pre>ACCOUNTS_LOGIN_ATEMPTS_LIMIT = 3<br>ACCOUNTS_LOGIN_ATEMPTS_TIMEOUT = 60</pre><p>In addition to the login protection, we’ve enhanced security by implementing CSRF (Cross-Site Request Forgery) protection on the product detail page. This added layer of security ensures that users’ shopping cart changes are secure and protected from potential unauthorized actions.</p><pre>&lt;p&gt;{{ object.deskripsi }}&lt;/p&gt;<br>&lt;form action=&quot;{{ object.get_add_to_cart_url }}&quot; method=&quot;post&quot; style=&quot;display: inline;&quot;&gt;<br>    {% csrf_token %}<br>    &lt;button type=&quot;submit&quot; class=&quot;btn btn-primary ms-1&quot;&gt;<br>         Tambah ke keranjang<br>         &lt;i class=&quot;fas fa-shopping-cart ms-1&quot;&gt;&lt;/i&gt;<br>    &lt;/button&gt;<br>&lt;/form&gt;<br>                    <br>&lt;form action=&quot;{{ object.get_remove_from_cart_url }}&quot; method=&quot;post&quot; style=&quot;display: inline;&quot;&gt;<br>     {% csrf_token %}<br>     &lt;button type=&quot;submit&quot; class=&quot;btn btn-danger ms-1&quot;&gt;<br>         Kurangi keranjang<br>         &lt;i class=&quot;fas fa-shopping-cart ms-1&quot;&gt;&lt;/i&gt;<br>     &lt;/button&gt;<br>&lt;/form&gt;</pre><p>That’s is my project related to Django and cybersecurity. It was my first experience with Django, which involves a combination of Python and HTML, while also implementing cybersecurity measures. I’m quite satisfied and proud of my work, especially as a beginner.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=74cbfae18116" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Customer Behavior Analysis]]></title>
            <link>https://medium.com/@uminabilah/customer-behavior-analysis-01f468a584ac?source=rss-4920223f146c------2</link>
            <guid isPermaLink="false">https://medium.com/p/01f468a584ac</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[ecommerce-data]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[exploratory-data-analysis]]></category>
            <category><![CDATA[data-analyst]]></category>
            <dc:creator><![CDATA[Umi Nabilah ]]></dc:creator>
            <pubDate>Thu, 12 Oct 2023 08:14:28 GMT</pubDate>
            <atom:updated>2023-10-12T08:14:28.420Z</atom:updated>
            <content:encoded><![CDATA[<h3>About This Project</h3><p>This project was created as a requirement for passing the Belajar Analisis Data dengan Python class. The data is provided by Dicoding Indonesia. This e-commerce dataset contains the transaction information for a company in Brazil, the data period is from 2016 to 2020.</p><p>The intended purpose of this analysis is to understand and identify total customers per month and the average nominal issued, customer behavior by country, level of customer satisfaction, and number of active customers. In other words, this analysis aims to understand customer behavior to increase customer satisfaction and company sales performance.</p><h3>Data Wrangling</h3><p>The first step is that I have to import the data, this time I used data that I had saved on my Google Drive</p><pre>drive.mount(&#39;/content/drive&#39;)<br>file_path =&quot;/content/drive/MyDrive/E-Commerce Public Dataset/&quot;<br>customer_df = pd.read_csv(file_path + &#39;customers_dataset.csv&#39;)<br>order_item_df = pd.read_csv(file_path + &#39;order_items_dataset.csv&#39;)<br>order_payment_df = pd.read_csv(file_path + &#39;order_payments_dataset.csv&#39;)<br>order_review_df = pd.read_csv(file_path + &#39;order_reviews_dataset.csv&#39;)<br>product_df = pd.read_csv(file_path + &#39;products_dataset.csv&#39;)<br>order_df = pd.read_csv(file_path + &#39;orders_dataset.csv&#39;)</pre><p>After gathering the data, I then performed data assessment, which included checking information and data types, as well as identifying null and duplicate values. I discovered that there are 2965 rows in the ‘order_delivered_customer_date’ column with empty values, either because the orders have not yet reached the customers or because the orders were canceled. I decided to retain these values. Additionally, I changed the format of some columns from ‘object’ to ‘datetime’.</p><pre>order_df[&#39;order_purchase_timestamp&#39;]=pd.to_datetime(order_df[&#39;order_purchase_timestamp&#39;], format=&#39;%Y-%m-%d %H:%M:%S&#39;)<br>order_df[&#39;order_approved_at&#39;]=pd.to_datetime(order_df[&#39;order_approved_at&#39;], format=&#39;%Y-%m-%d %H:%M:%S&#39;)<br>order_df[&#39;order_delivered_carrier_date&#39;]=pd.to_datetime(order_df[&#39;order_delivered_carrier_date&#39;], format=&#39;%Y-%m-%d %H:%M:%S&#39;)<br>order_df[&#39;order_delivered_customer_date&#39;]=pd.to_datetime(order_df[&#39;order_delivered_customer_date&#39;], format=&#39;%Y-%m-%d %H:%M:%S&#39;)<br>order_df[&#39;order_estimated_delivery_date&#39;]=pd.to_datetime(order_df[&#39;order_estimated_delivery_date&#39;], format=&#39;%Y-%m-%d %H:%M:%S&#39;)</pre><h3>Exploratory Data Analysis</h3><h4>Number of Customer per Month</h4><p>First, I created a pivot table that includes the number of customers per month and performed some adjustments, such as sorting the months chronologically and filtering out years with many null values.</p><pre># Creating a pivot table to count the number of customers per month<br>total_customer_df = order_df.pivot_table(index=&#39;order_month&#39;,<br>                                         values=&#39;customer_id&#39;,<br>                                         columns=&#39;order_year&#39;,<br>                                         aggfunc=&#39;nunique&#39;).reset_index()<br><br># Sorting the pivot table based on the calendar month order<br>month_order = list(calendar.month_name[1:])<br>total_customer_df = total_customer_df.set_index(&#39;order_month&#39;).reindex(month_order)<br><br># Calculating the total customers per year<br>total_customer_per_year = total_customer_df.sum()<br><br># Adding a row for total customers per year to the dataframe<br>total_customer_df.loc[&#39;Total Customer&#39;] = total_customer_per_year<br><br># Removing the 2016 column as it has many missing values<br>total_customer_df = total_customer_df.drop(columns=[2016])</pre><p>After that, I then made a line graph with the matplotlib library</p><pre># Removing the &#39;Total Customer&#39; row<br>total_customer_df.drop(index=&#39;Total Customer&#39;, inplace=True)<br><br># Creating a line graph<br>ax = total_customer_df.plot(kind=&#39;line&#39;, figsize=(10, 5))<br><br># Setting the title and axis labels<br>ax.set_title(&#39;Number of Customers from 2017 to 2018&#39;)<br>ax.set_xlabel(&#39;Month&#39;)<br>ax.set_ylabel(&#39;Number of Customers&#39;)<br><br># Placing the legend outside the chart<br>ax.legend(loc=&#39;center left&#39;, bbox_to_anchor=(1, 0.5))<br><br># Displaying the graph<br>plt.show()</pre><p>And here is the result,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/951/1*3T9Y2cLFtpIIl0I8Q5EWvw.png" /></figure><p>Based on the graph above, it is evident that the number of subscribers per month continued to increase from 2017 to 2018. When examining yearly trends, the number of subscribers showed an upward trend in 2017 but a downward trend in 2018. Additionally, it’s worth noting that the number of subscribers tends to decrease in April and June, while it increases in March and May.</p><h4>Average Customer Expenditure</h4><p>Next, I wanted to determine the amount each customer spent per transaction. To achieve this, I merged the ‘order_df’ and ‘order_payment_df’ tables and created a pivot table to calculate the total customer spending.</p><pre># Creating a new dataframe<br>spending_df = pd.merge(order_df, order_payment_df, on=&#39;order_id&#39;, how=&#39;left&#39;)<br><br># Creating a pivot table for customer spending per month<br>customer_spending_df = spending_df.pivot_table(index=&#39;order_month&#39;,<br>                                               values=&#39;payment_value&#39;,<br>                                               columns=&#39;order_year&#39;,<br>                                               aggfunc=&#39;mean&#39;).reset_index()<br><br># Sorting the pivot table based on the calendar month order<br>month_order = list(calendar.month_name[1:])<br>customer_spending_df = customer_spending_df.set_index(&#39;order_month&#39;).reindex(month_order)<br><br># Removing the 2016 column as it has many missing values<br>customer_spending_df = customer_spending_df.drop(columns=[2016])<br><br># Creating a line graph<br>ax_spending = customer_spending_df.plot(kind=&#39;line&#39;, figsize=(10, 5))<br><br>ax_spending.set_title(&#39;Trend of Customer Average Spending in 2017 - 2018&#39;)<br>ax_spending.set_xlabel(&#39;Month&#39;)<br>ax_spending.set_ylabel(&#39;Spending&#39;)<br><br>ax_spending.legend(loc=&#39;center left&#39;, bbox_to anchor=(1, 0.5))<br><br># Displaying the graph<br>plt.show()</pre><p>And here is the result,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/942/1*LqhiBLkC8Ly2rpZB7cSy_Q.png" /></figure><p>From the graph, it can be seen that the average customer spending per month is below 170 Brazilian Real, but this number has almost doubled in September 2018.</p><h4>Clustering Customer and Transactions by State</h4><p>I will make a graph containing the number of customers and the nominal value of their transactions based on their respective countries to find out whether there are different patterns between countries.</p><pre># Creating a new dataframe from the order, product, and customer tables<br>country_df = pd.merge(order_df, customer_df, on=&#39;customer_id&#39;, how=&#39;left&#39;)<br><br># Creating a new dataframe from the order, product, and customer tables<br>merge2_df = pd.merge(order_df[[&#39;order_id&#39;, &#39;customer_id&#39;]], <br>                     customer_df[[&#39;customer_id&#39;, &#39;customer_state&#39;]], <br>                     on=&#39;customer_id&#39;, how=&#39;left&#39;)<br>state_df = pd.merge(order_payment_df[[&#39;order_id&#39;, &#39;payment_value&#39;]], <br>                    merge2_df, on=&#39;order_id&#39;, how=&#39;inner&#39;)<br><br>state_df.duplicated().sum() # Since there are duplicate data, they will be removed<br>state_df.drop_duplicates(inplace=True)<br><br># Creating a new pivot table<br>customer_state_df = country_df.pivot_table(index=&#39;customer_state&#39;, <br>                                           values=&#39;customer_id&#39;, <br>                                           aggfunc=&#39;nunique&#39;).sort_values(by=&#39;customer_id&#39;, <br>                                                                          ascending=False)<br>transaction_state_df = state_df.pivot_table(index=&#39;customer_state&#39;, <br>                                            values=&#39;payment_value&#39;, <br>                                            aggfunc=&#39;mean&#39;).reset_index()<br><br># Merging the tables<br>clustering_state_df = pd.merge(customer_state_df, <br>                               transaction_state_df, <br>                               on=&#39;customer_state&#39;, <br>                               how=&#39;inner&#39;)<br>clustering_state_df = clustering_state_df.set_axis([&#39;state_name&#39;, <br>                                                    &#39;number_of_customer&#39;, <br>                                                    &#39;average_of_transaction&#39;], <br>                                                   axis=1).reset_index(drop=True)<br><br># Plotting the number_of_customer data on the left axis (axis 1)<br>ax_country = clustering_state_df.plot(x=&#39;state_name&#39;, <br>                                      y=&#39;number_of_customer&#39;, <br>                                      kind=&#39;bar&#39;, figsize=(10, 5))<br><br># Activating the right axis (axis 2)<br>ax2 = ax_country.twinx()<br><br># Plotting the average_of_transaction data on the right axis (axis 2)<br>clustering_state_df.plot(x=&#39;state_name&#39;, <br>                         y=&#39;average_of_transaction&#39;, <br>                         ax=ax2, color=&#39;orange&#39;, marker=&#39;o&#39;)<br><br># Setting the title and axis labels<br>ax_country.set_title(&#39;Number of Customers and Average of Transactions by State&#39;)<br>ax_country.set_xlabel(&#39;State Name&#39;)<br>ax_country.set_ylabel(&#39;Number of Customers&#39;)<br>ax2.set_ylabel(&#39;Average of Transactions&#39;)<br><br># Placing the legend outside the chart<br>lines, labels = ax_country.get_legend_handles_labels()<br>lines2, labels2 = ax2.get_legend_handles_labels()<br>ax2.legend(lines + lines2, labels + labels2, loc=&#39;upper center&#39;, <br>           bbox_to_anchor=(0.5, -0.2), ncol=2)<br><br># Displaying the chart<br>plt.show()</pre><p>And here is the result,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/923/1*jKcPC-4FN-cUc5IBzrCGzw.png" /></figure><p>From the graph above, it’s evident that the majority of customers reside in the state of São Paulo (SP), followed by Rio de Janeiro (RJ) as the state with the second-highest number of customers. Surprisingly, São Paulo has the lowest average transaction amount, while the state of Roraima (RR) has the fewest customers but the highest average transaction nominal.</p><h4>Number of Orders Based on Customer Reviews</h4><p>Next, I want to know the customer reviews of their orders to assess the level of customer satisfaction in shopping.</p><pre># Creating a new dataframe from the order review and order item tables<br>review_df = pd.merge(order_df, order_review_df, on=&#39;order_id&#39;, how=&#39;left&#39;)<br><br># Creating a new column with 1 if the order has a review comment and 0 if there&#39;s no comment<br>review_df[&#39;review_comment&#39;] = review_df[&#39;review_comment_message&#39;].notnull().astype(int)<br>review_df[&#39;review_score&#39;].fillna(&#39;no review&#39;, inplace=True)<br><br># Creating a new pivot table<br>customer_review_df = review_df.pivot_table(index=&#39;review_score&#39;, values=&#39;order_id&#39;,<br>                                           columns=&#39;review_comment&#39;, aggfunc=&#39;nunique&#39;,<br>                                           margins=True, margins_name=&#39;total&#39;)<br><br># Renaming columns<br>customer_review_df = customer_review_df.set_axis([&#39;no_comment(%)&#39;, &#39;has_Comment(%)&#39;,<br>                                                  &#39;total&#39;], axis=1).reset_index()<br>customer_review_df[&#39;no_comment(%)&#39;].fillna(0, inplace=True)<br>customer_review_df[&#39;has_Comment(%)&#39;].fillna(0, inplace=True)<br><br># Converting column values to percentages<br>customer_review_df[&#39;has_Comment(%)&#39;] = (customer_review_df[&#39;has_Comment(%)&#39;] / 99441) * 100<br>customer_review_df[&#39;no_comment(%)&#39;] = (customer_review_df[&#39;no_comment(%)&#39;] / 99441) * 100<br><br># Rounding values to two decimal places<br>customer_review_df[&#39;has_Comment(%)&#39;] = customer_review_df[&#39;has_Comment(%)&#39;].round(2)<br>customer_review_df[&#39;no_comment(%)&#39;] = customer_review_df[&#39;no_comment(%)&#39;].round(2)<br><br># Removing the total row and column<br>customer_review_df.drop(&#39;total&#39;, axis=1, inplace=True)<br>total_row_index = customer_review_df[customer_review_df[&#39;review_score&#39;] == &#39;total&#39;].index<br>customer_review_df.drop(index=total_row_index, inplace=True)<br><br># Creating a bar chart<br>ax_review = customer_review_df.plot(x=&#39;review_score&#39;, kind=&#39;bar&#39;, figsize=(10, 5))<br><br># Setting x-axis label rotation<br>ax_review.set_xticklabels(ax_review.get_xticklabels(), rotation=0)<br><br># Setting titles and labels<br>plt.suptitle(&#39;Customer Review Based on Their Order&#39;, fontsize=15, fontweight=&#39;bold&#39;)<br>plt.title &#39;Percentage of Total&#39;, fontsize=12)<br>ax_review.set_xlabel(&#39;Review Score&#39;)<br>ax_review.set_ylabel(&#39;Total Order&#39;)<br><br># Displaying the chart<br>plt.show()</pre><p>The code above will generate the following graph,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/841/1*Tcu0xlo4H3cLNNpzifqAEw.png" /></figure><p>From the graph above, it can be observed that over 50% of customers rated their orders with a score of 5. However, there were still 22,701 orders, which is equivalent to 22% of the total orders, that received a poor rating, specifically a score below 4. Furthermore, customers who rated their orders with a score of 1 were more likely to leave comments related to their ratings, whereas customers who rated their orders with a score of 5 were less inclined to provide feedback messages.</p><h3>Conclusion</h3><p>Based on the results of the analysis, several conclusions and suggestions can be made, including:</p><ol><li>The number of customers in 2018 exhibited a declining trend. Nearly 50% of customers who conducted transactions in 2016 and 2017 did not return for shopping in 2018. To address this, companies can consider offering promotions to entice returning customers and boost transactions.</li><li>In light of the clustering results by country, it’s noteworthy that Sao Paulo, with the highest number of customers, has the smallest average transaction amount, while Roraima shows the opposite trend. This observation might be linked to the geographical distance of customers from the central distribution points, where Roraima, possibly the farthest, has larger transactions carried out by bigger companies. Consequently, the company could strategize from a logistics perspective to reduce shipping costs, potentially attracting more customers from other regions.</li><li>A significant number of orders, specifically 22,701 orders or about 22% of the total, received ratings below 4, indicating poor customer satisfaction. This figure underscores the importance for companies and sellers to enhance both product and service quality to elevate customer satisfaction.</li></ol><p>To access a more comprehensive analysis, you can view my notebook by clicking <a href="https://colab.research.google.com/drive/1WZG6bApMAijNBTXU967MajymGSMQ0CiG?usp=sharing">here</a>.</p><p>Project by Umi Nabilah<br>E-Mail : uminabilah.2000@gmail.com</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=01f468a584ac" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>