<?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 Daniel Ringo on Medium]]></title>
        <description><![CDATA[Stories by Daniel Ringo on Medium]]></description>
        <link>https://medium.com/@danielsiringoringo052?source=rss-5000a4cfde44------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*KKwK5NMSpATcN0BZj2NWZA.png</url>
            <title>Stories by Daniel Ringo on Medium</title>
            <link>https://medium.com/@danielsiringoringo052?source=rss-5000a4cfde44------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Mon, 16 Nov 2026 03:20:47 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@danielsiringoringo052/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[Customer Transactions Analysis]]></title>
            <link>https://medium.com/@danielsiringoringo052/customer-transactions-analysis-af2d77483a52?source=rss-5000a4cfde44------2</link>
            <guid isPermaLink="false">https://medium.com/p/af2d77483a52</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[banking]]></category>
            <dc:creator><![CDATA[Daniel Ringo]]></dc:creator>
            <pubDate>Thu, 12 Sep 2024 12:52:05 GMT</pubDate>
            <atom:updated>2024-09-12T12:52:05.464Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/750/0*Vn1RZAF2ezQGPtPq.jpg" /><figcaption><strong>PostgreSQL</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Zp-rJxh-3UbfHIgC" /><figcaption><strong>Data Visuliazations Power BI</strong></figcaption></figure><h4>Introduction:</h4><p>As I sit back in my chair, staring at the glowing Power BI dashboard on my screen, I can’t help but feel a mix of exhaustion and exhilaration. The past month has been a whirlwind of data, code, and countless cups of coffee. But today, I can proudly say that I’ve completed the most challenging and rewarding project of my career: The BankTech Customer Transactions Analysis.</p><p>When our CEO dropped this task on my desk four weeks ago, I knew I was in for a ride. “We need a comprehensive analysis of our customer transactions,” she said. “This will shape our strategy for the next five years. Can you handle it?” Little did she know, those words would set me on a journey through terabytes of data, complex SQL queries, and sleepless nights filled with Python scripts and visualization designs.</p><h4>The Challenge:</h4><p>BankTech, a mid-sized bank with a reputation for personal service, had been collecting customer data for years. But this gold mine of information lay scattered across various systems, forming a complex web of unstructured data. My task? To extract meaningful insights from this data chaos and transform them into actionable strategies.</p><p>The stakes were high. This analysis would drive decisions on product development, marketing strategies, and customer engagement initiatives. The future of BankTech’s relationship with its 296 high-value clients hung in the balance.</p><h4>The Journey:</h4><ol><li><strong>Data Integration and Cleaning:</strong><br> My first challenge was to bring together data from multiple sources: a legacy MySQL database, a modern PostgreSQL system, countless Excel spreadsheets, and CSV files. I spent days writing complex SQL queries to extract and merge this data. One of my key queries, which I refined over several sleepless nights, looked like this:</li></ol><pre>SELECT<br> pekerjaan,<br> jenis_transaksi,<br> bulan_transaksi,<br> segment_usia,<br> jumlah_nasabah,<br> jumlah_transaksi,<br> rata_rata_jumlah,<br> total_jumlah,<br> total_saldo,<br> CONCAT(ROUND(AVG(total_jumlah / total_saldo)*100), &#39; %&#39;) as percentage_saldo,<br> ROUND(SUM(CASE WHEN jenis_transaksi = &#39;Pembayaran&#39; THEN total_jumlah  ELSE 0 END)/<br> (SUM(CASE WHEN jenis_transaksi = &#39;Pembayaran&#39; THEN total_jumlah ELSE 0 END)<br> + SUM(CASE WHEN jenis_transaksi = &#39;Penarikan&#39; THEN total_jumlah ELSE 0 END)) *100,2)<br>  as pct_pembayaran,<br>    ROUND(SUM(CASE WHEN jenis_transaksi = &#39;Penarikan&#39; THEN total_jumlah  ELSE 0 END)/<br> (SUM(CASE WHEN jenis_transaksi = &#39;Pembayaran&#39; THEN total_jumlah  ELSE 0 END)<br> +SUM(CASE WHEN jenis_transaksi = &#39;Penarikan&#39; THEN total_jumlah  ELSE 0 END)) *100,2)<br>  as pct_penarikan<br>FROM (<br> SELECT <br> pekerjaan,<br> jenis_transaksi,<br> TO_CHAR(t.tanggal, &#39;YYYY-MM&#39;) AS bulan_transaksi,<br> CASE <br>            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, tanggal_lahir)) &lt; 30 THEN &#39;Muda&#39;<br>            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, tanggal_lahir)) BETWEEN 30 AND 50 THEN &#39;Dewasa&#39;<br>            ELSE &#39;Lansia&#39;<br>        END AS segment_usia,<br>    COUNT(n.id_nasabah) as jumlah_nasabah,<br> COUNT(t.id_transaksi) as jumlah_transaksi,<br> ROUND(AVG(t.jumlah)) as rata_rata_jumlah,<br>    SUM(t.jumlah) as total_jumlah,<br> SUM(saldo_setelah_transaksi) as total_saldo,<br> DENSE_RANK() OVER(PARTITION BY jenis_transaksi ORDER BY SUM(t.jumlah) DESC) as rnk<br>FROM nasabah as n<br>JOIN transaksi as t<br> USING(id_nasabah)<br>GROUP BY 1, 2, 3, 4<br>) X<br>WHERE  jenis_transaksi IN (&#39;Pembayaran&#39;, &#39;Penarikan&#39;)<br>GROUP BY pekerjaan,<br> jenis_transaksi,<br> bulan_transaksi,<br> segment_usia,<br> jumlah_nasabah,<br> jumlah_transaksi,<br> rata_rata_jumlah,<br> total_jumlah,<br> total_saldo<br>ORDER BY total_saldo DESC;</pre><p><strong>Query Result:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CryBuIt8E9wY43lumHo1AA.png" /></figure><p>This query was crucial as it combined customer demographics with transaction behavior, allowing me to segment customers and analyze their financial activities in depth.</p><p><strong>2. Exploratory Data Analysis:</strong><br>With the data in hand, we began to explore the characteristics of our customers and their transactions. We calculated summary statistics, such as mean and median transaction values, and analyzed the distribution of transactions by type (Pembayaran or Penarikan). We also examined the age demographics of our customers, categorizing them into three segments: Muda, Dewasa, and Lansia. I uncovered key insights:</p><p>- BankTech had 296 customers, with total transactions amounting to Rp252,074 million.<br> — Withdrawals (1.48 million) slightly outnumbered payments (1 million).<br> — March saw the highest transaction value (Rp28,403 million) while April had the lowest (Rp17,953 million).<br> — Adult customers dominated transaction values (Rp155,153 million), followed by young adults (Rp56,411 million) and seniors (Rp40,511 million).<br> — Government employees, entrepreneurs, and teachers were the most active in terms of transaction volume.</p><p><strong>3. Advanced Analytics:</strong><br> To deepen my insights, I employed advanced statistical techniques and machine learning:<br> — Time Series Analysis: ARIMA models to forecast transaction volumes.<br> — Cluster Analysis: K-means clustering to identify distinct customer segments.<br> — Association Rule Mining: Uncovering patterns in transaction types.<br> — Predictive Modeling: A Random Forest model to predict high-potential customers.</p><p>4. Visualization and Dashboard Creation:<br> The final challenge was presenting my findings in an intuitive, actionable format. I turned to Power BI, creating an interactive dashboard that included:<br> — A summary section with key metrics<br> — Dynamic charts showing transaction distributions and trends<br> — Segment analysis visualizations<br> — Predictive charts showing forecasted behaviors</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_G-krqNgT6dxR7H1-KDNIQ.png" /><figcaption><strong>Customer Transaction Analysis Dashboard</strong></figcaption></figure><p>The Outcome:</p><p>As I presented my findings to the executive team, I could see their eyes light up with understanding and excitement. The CEO was particularly impressed by the predictive elements of the analysis. The Head of Product Development immediately saw the potential in the age-based segmentation. The Chief Marketing Officer was already jotting down ideas for occupation-specific campaigns.</p><p>My recommendations, backed by data, led to significant changes:<br>1. Development of a new mobile app focusing on digital payments.<br>2. Launch of a “Young Professionals” savings account.<br>3. Creation of a loyalty program for government employees.<br>4. Redesign of marketing campaigns aligning with identified seasonal trends.<br>5. Implementation of a predictive engagement model for high-potential customers.</p><p>Six months later, BankTech saw a 15% increase in digital transactions, a 20% growth in young adult customers, and a 10% boost in overall customer satisfaction scores.</p><p>Conclusion:</p><p>As I look back on this challenging yet rewarding journey, I’m filled with pride. This project pushed me to my limits, forcing me to leverage every skill in my arsenal and learn new ones on the fly. But more than that, it showed me the true power of data.</p><p>In just one month, working solo, I was able to transform BankTech’s approach to customer engagement and strategy. It’s a testament to the power of data analytics and the impact that one determined analyst can have on an entire organization.</p><p>This project was not just about numbers and charts. It was about uncovering the stories hidden within the data — stories of customer behaviors, preferences, and needs. It was about translating these stories into actionable insights that could drive real change.</p><p>As I prepare for my next challenge (in my new role as Head of Data Analytics), I’m excited about the future. The Customer Transactions Analysis project was just the beginning of BankTech’s data-driven journey, and I’m thrilled to be leading the way.</p><p>To all my fellow data enthusiasts out there, remember: with the right skills, tools, and perseverance, you can unlock the power of data to drive meaningful change. The data is out there, waiting to tell its story. Are you ready to listen?</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=af2d77483a52" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Analyzing Customer Engagement: Total Transactions and Behavior Insights]]></title>
            <link>https://blog.devgenius.io/analyzing-customer-engagement-total-transactions-and-behavior-insights-14d2770f8b2d?source=rss-5000a4cfde44------2</link>
            <guid isPermaLink="false">https://medium.com/p/14d2770f8b2d</guid>
            <category><![CDATA[spark-sql]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[matplotlib]]></category>
            <category><![CDATA[apache-spark]]></category>
            <dc:creator><![CDATA[Daniel Ringo]]></dc:creator>
            <pubDate>Wed, 04 Sep 2024 14:26:22 GMT</pubDate>
            <atom:updated>2024-09-13T15:29:24.151Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*MAh24pTUKFR8s6uC" /><figcaption>Photo by <a href="https://unsplash.com/@neonbrand?utm_source=medium&amp;utm_medium=referral">Kenny Eliason</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h4>1. Project Objective</h4><p>The primary goal of this project is to analyze customer engagement by focusing on their transaction behavior. This involves assessing total transactions, identifying key behavioral insights, and eventually building a machine learning model to forecast future trends. The tools used for this project include PySpark for data processing, Matplotlib for visualization, and Spark MLlib for machine learning.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*qdLCo-cvo4YM97OZ.png" /><figcaption><strong>PySpark for data processing</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*2R28BW4VtXa-EcP9.png" /><figcaption><strong>Matplotlib for visualization</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/0*SX2yxzpd1EbSknog" /><figcaption><strong>Spark MLlib for machine learning</strong></figcaption></figure><h4>2. Data Collection</h4><p>The project begins with collecting customer and transaction data, typically stored in CSV files. This data includes essential details such as customer IDs, names, transaction dates, transaction amounts, and other relevant metrics.</p><ul><li><strong>Loading Data</strong>: The data is loaded into Spark DataFrames using PySpark, which allows efficient handling of large datasets.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pKkNzHdsVbfbT2-3S6tNWQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PowBoryqyqb88uEQsejsTA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-lFerniVRG9FOyamwE-Kyg.png" /></figure><h4>3. Data Exploration</h4><p>After loading the data, the next step is to explore the dataset. This includes understanding the structure, identifying missing values, and analyzing the data distribution.</p><ul><li><strong>Schema Check</strong>: Print the schema of the DataFrames to verify the data types.</li><li><strong>Data Preview</strong>: Display the first few rows to ensure data has been loaded correctly.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZBSuY2pap4XgzQS7AgjT9g.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*L3hGTiL-NrCBqip7noWdmw.png" /></figure><h4>4. Data Processing and SQL Queries</h4><p>To derive insights, various SQL queries are executed on the data. One of the key queries involves joining the customer and transaction data to calculate the total transactions per customer.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lRu9NuE2sozw3qYlspGEjg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7RQUD0Al27AxyYxhLxD7wg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FelfypdfbgXFTVzTS4pYaA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9q2tfCh_9NjJyWZcWCV5ag.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6KFfeyJhEQDj3wCEzmm1Rw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gMlUt1D7YpsDQKMxSHbzAQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*01uCvvcO24GIcTlASaIh_g.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DQ0DDeIm0K9HE3b-2SXTsg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*I13mdeRgdcOKDgC--UufSA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VYFplaBDXmh9rcWeUuwCDw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WjJ14Hgh6_nI_fhabyirEA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LWjaPMjr7BfOb2UFwPjlXA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GLwMDZXuJcvFCnTkykeX0g.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Q0UHQ9hU6LmTyrMt85L42Q.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gHjV80rOd43RqvJSfaNDEw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3Yt8ZKObX-ed86hyjEKaOw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4eZQxPxhrL7D733-zbjf3Q.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WuDQaPlJpaEgpZNjJxqTCg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jJDuEQ_HqeERJGEY93N_kA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eHqLIo-Iu0yHFdoVgmvU5w.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iUvNtND6D5n0SMJLArwrHg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BejtZTEEbwp66TKI09JiyQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xvOfWoTMImduL9IS-Q3kBw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BZlypxyo5bCpObPnl_qIaw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*f01zqPYG43L4MRs6NjY--Q.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ggqE4mjvQ2Bp95x_1r-xow.png" /></figure><h4>8. Conclusion and Insights</h4><ul><li><strong>Data Insights</strong>: The initial data exploration and SQL queries reveal important insights about customer engagement. For instance, the customers with the highest number of transactions are identified, helping the business focus on retaining these high-value customers.</li><li><strong>Visual Analysis</strong>: The visualizations provide an easy-to-understand summary of customer behavior, which is crucial for stakeholders who may not be familiar with the technical details.</li><li><strong>Predictive Modeling</strong>: The machine learning model offers a way to predict future customer behavior, enabling proactive engagement strategies. The model’s performance, as indicated by metrics like RMSE, shows its reliability in making accurate predictions.</li><li><strong>Business Application</strong>: The insights gained from this project can be used to tailor marketing campaigns, improve customer retention, and optimize the overall customer experience.</li></ul><h4>9. Final Thoughts</h4><p>This project effectively combines data processing, visualization, and machine learning to provide a comprehensive analysis of customer engagement. The approach is scalable and can be applied to various domains where understanding customer behavior is critical.</p><p>You can check full code for this Project in <a href="https://github.com/Danielringo/Analyzing-Customer-Engagemnet/tree/main">GitHub</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=14d2770f8b2d" width="1" height="1" alt=""><hr><p><a href="https://blog.devgenius.io/analyzing-customer-engagement-total-transactions-and-behavior-insights-14d2770f8b2d">Analyzing Customer Engagement: Total Transactions and Behavior Insights</a> was originally published in <a href="https://blog.devgenius.io/">Dev Genius</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Global Layoff Trends: Analyzing Industry Impact and Survival Strategies]]></title>
            <link>https://medium.com/@danielsiringoringo052/global-layoff-trends-analyzing-industry-impact-and-survival-strategies-bd4eebd9bfa6?source=rss-5000a4cfde44------2</link>
            <guid isPermaLink="false">https://medium.com/p/bd4eebd9bfa6</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[business-analyst]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Daniel Ringo]]></dc:creator>
            <pubDate>Fri, 30 Aug 2024 13:03:50 GMT</pubDate>
            <atom:updated>2024-08-30T13:03:50.333Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*YEczewT7P4j4i2FW" /><figcaption>Photo by <a href="https://unsplash.com/@jamesyarema?utm_source=medium&amp;utm_medium=referral">James Yarema</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><blockquote>Hai semua!</blockquote><p>Jadi, gue baru aja kelarin sebuah proyek yang seru banget dan penuh tantangan. Gue bener-bener mendalami data tentang PHK (Pemutusan Hubungan Kerja) di berbagai industri dan negara. Hasilnya cukup mengejutkan dan penuh insight yang bisa lo semua pakai buat memahami gimana kerasnya dunia kerja saat ini. Yuk, gue ceritain prosesnya dari awal sampai akhir!</p><h4>Mulai dari Mana Sih Proyek Ini?</h4><p>Semua dimulai dari rasa penasaran gue. Belakangan ini kita sering denger berita tentang PHK di berbagai perusahaan besar, terutama di sektor teknologi dan ritel. Gue jadi mikir, “Sebenernya, seberapa besar sih dampak PHK ini ke berbagai industri di seluruh dunia? Dan gimana tren PHK ini dari waktu ke waktu?” Dengan pertanyaan itu di kepala, gue mulai ngumpulin data.</p><p>Gue punya data yang cukup lengkap, dari berbagai industri dan negara, tentang jumlah PHK, persentase karyawan yang di-PHK, perusahaan yang terdampak, sampai dana yang berhasil mereka kumpulin di tengah krisis. Dari situ, gue mulai merancang query SQL buat ngebongkar apa aja yang terjadi di balik angka-angka ini.</p><h4>1. Pengumpulan dan Pengolahan Data dengan MySQL</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/391/0*hnySg2u0UF6ghm16.png" /></figure><p>Proyek ini dimulai dengan mengumpulkan data PHK dari berbagai sumber. Data yang gue dapet mencakup informasi tentang jumlah karyawan yang di-PHK, industri tempat mereka bekerja, negara asal perusahaan, serta persentase karyawan yang di-PHK dan dana yang dikumpulkan oleh perusahaan selama krisis.</p><p>Setelah semua data terkumpul, langkah berikutnya adalah mengolah data tersebut dengan menggunakan MySQL. MySQL adalah alat yang sangat berguna buat ngolah data mentah menjadi lebih rapi dan siap untuk dianalisis. Gue mulai dengan membersihkan data, menghapus informasi yang nggak relevan, dan memastikan semua data terformat dengan benar. Contohnya, gue ubah format tanggal dan angka, serta menghapus tanda baca yang nggak perlu biar datanya lebih gampang diolah.</p><h4>Contoh Query MySQL:</h4><pre>WITH layoff_metrics AS (<br>    SELECT <br>        industry,<br>        country,<br>        YEAR(STR_TO_DATE(`date`, &#39;%m/%d/%Y&#39;)) AS layoff_year,<br>        QUARTER(STR_TO_DATE(`date`, &#39;%m/%d/%Y&#39;)) AS layoff_quarter,<br>        SUM(CAST(REPLACE(total_laid_off, &#39;,&#39;, &#39;&#39;) AS UNSIGNED)) AS total_layoffs,<br>        AVG(CAST(REPLACE(percentage_laid_off, &#39;%&#39;, &#39;&#39;) AS DECIMAL(5,2))) AS avg_percentage_laid_off,<br>        COUNT(DISTINCT company) AS affected_companies,<br>        SUM(CAST(REPLACE(funds_raised_millions, &#39;$&#39;, &#39;&#39;) AS DECIMAL(10,2))) AS total_funds_raised<br>    FROM layoffs_staging2<br>    WHERE industry != &#39;&#39; <br>      AND country != &#39;&#39; <br>      AND total_laid_off != &#39;&#39; <br>      AND percentage_laid_off != &#39;&#39; <br>      AND `date` != &#39;&#39; <br>      AND funds_raised_millions != &#39;&#39;<br>    GROUP BY industry, country, layoff_year, layoff_quarter<br>),<br>industry_rankings AS (<br>    SELECT <br>        industry,<br>        layoff_year,<br>        SUM(total_layoffs) AS yearly_layoffs,<br>        ROW_NUMBER() OVER (PARTITION BY layoff_year ORDER BY SUM(total_layoffs) DESC) AS industry_rank<br>    FROM layoff_metrics<br>    GROUP BY industry, layoff_year<br>),<br>country_rankings AS (<br>    SELECT <br>        country,<br>        layoff_year,<br>        SUM(total_layoffs) AS yearly_layoffs,<br>        ROW_NUMBER() OVER (PARTITION BY layoff_year ORDER BY SUM(total_layoffs) DESC) AS country_rank<br>    FROM layoff_metrics<br>    GROUP BY country, layoff_year<br>)<br><br>SELECT <br>    lm.industry,<br>    lm.country,<br>    lm.layoff_year,<br>    lm.layoff_quarter,<br>    lm.total_layoffs,<br>    lm.avg_percentage_laid_off,<br>    lm.affected_companies,<br>    lm.total_funds_raised,<br>    ir.yearly_layoffs AS industry_yearly_layoffs,<br>    ir.industry_rank,<br>    cr.yearly_layoffs AS country_yearly_layoffs,<br>    cr.country_rank,<br>    (lm.total_layoffs / lm.affected_companies) AS avg_layoffs_per_company,<br>    (lm.total_funds_raised / lm.total_layoffs) AS funds_raised_per_layoff<br>FROM layoff_metrics lm<br>JOIN industry_rankings ir ON lm.industry = ir.industry AND lm.layoff_year = ir.layoff_year<br>JOIN country_rankings cr ON lm.country = cr.country AND lm.layoff_year = cr.layoff_year<br>ORDER BY lm.layoff_year DESC, lm.layoff_quarter DESC, lm.total_layoffs DESC<br>LIMIT 1000;</pre><p><strong>Query Result:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GCagMzBodTXAppacpXMUUg.png" /></figure><p>Gue pake query MySQL buat ngubah data mentah jadi informasi yang lebih bermakna. Misalnya, gue hitung total PHK per industri, per tahun, dan per negara. Gue juga menghitung rata-rata persentase PHK dan dana yang berhasil dikumpulkan oleh tiap industri.</p><p>Setelah data diolah, gue mulai melihat pola-pola menarik. Misalnya, ada tren di mana industri tertentu mengalami PHK yang lebih besar di tahun-tahun tertentu. Data ini nantinya bakal jadi dasar buat visualisasi di tahap berikutnya.</p><p><strong>Analyze Query:</strong></p><blockquote><strong>Tren PHK dari Tahun ke Tahun:</strong> Angka PHK melonjak tajam di 2022 dan 2023. Kenapa? Mungkin karena kondisi ekonomi yang makin nggak pasti.</blockquote><blockquote><strong>Industri yang Paling Terdampak:</strong> Ternyata, sektor konsumen dan ritel yang sering kita anggap selalu ramai, justru yang paling banyak kehilangan karyawan. Ini menunjukkan betapa rapuhnya sektor-sektor ini saat ekonomi goyah.</blockquote><blockquote><strong>Kuartal Kritis:</strong> Gue juga menemukan bahwa kuartal pertama setiap tahun adalah periode di mana PHK paling banyak terjadi. Mungkin karena perusahaan mengevaluasi performa tahunan dan mengambil langkah-langkah untuk bertahan.</blockquote><blockquote><strong>Dana yang Dikumpulkan:</strong> Menariknya, meskipun banyak yang di-PHK, ada industri yang masih bisa ngumpulin dana besar. Ini bukti bahwa beberapa perusahaan punya strategi manajemen yang kuat buat menghadapi krisis.</blockquote><h4>2. Membuat Visualisasi dengan Power BI</h4><h4>Analisis Data: Dari Angka ke Insight</h4><p>Gue nggak mau berhenti cuma di angka-angka mentah. Makanya, gue bikin dashboard di Power BI biar data ini lebih mudah dimengerti. Dashboard ini gue bagi jadi dua halaman utama, masing-masing dengan fokus yang berbeda.</p><h4>Tahap-tahap dalam Power BI:</h4><p>- <strong>Import Data: </strong>Langkah pertama adalah mengimpor data yang udah diolah di MySQL ke Power BI. Di sini, gue pastikan semua data udah terstruktur dengan baik dan siap untuk divisualisasikan.<br> <br>- <strong>Membuat Grafik dan Chart: </strong>Gue mulai bikin berbagai macam visualisasi, seperti grafik garis untuk melihat tren PHK dari tahun ke tahun, pie chart untuk distribusi PHK per kuartal, dan bar chart untuk menampilkan industri mana yang paling banyak melakukan PHK. Gue juga bikin visualisasi untuk nunjukin berapa banyak dana yang berhasil dikumpulkan per industri setiap kali terjadi PHK.</p><h4>3. Penjelasan Hasil Dashboard Power BI:</h4><blockquote>Setelah data selesai diolah dan dianalisis dengan MySQL, langkah selanjutnya adalah membuat visualisasi data menggunakan Power BI. Power BI adalah alat yang keren banget buat bikin data yang rumit jadi lebih mudah dimengerti. Dengan Power BI, gue bisa bikin grafik, chart, dan dashboard yang menarik dan informatif.</blockquote><h3>Penjelasan Halaman 1: Overview PHK Global</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hX2CT74KZEPr-hOFX6j9Eg.png" /></figure><p><strong>1. Total Layoffs Over Time (Grafik Garis)</strong></p><ul><li><strong>Apa yang Terjadi?</strong>: Grafik ini menunjukkan bagaimana jumlah PHK berubah dari tahun 2020 hingga 2023. Dari sini, kita bisa melihat bahwa jumlah PHK meningkat drastis pada tahun 2022 dan 2023. Ini berarti banyak perusahaan yang kesulitan beradaptasi dengan perubahan ekonomi global, seperti dampak pandemi, sehingga mereka harus melakukan PHK besar-besaran.</li></ul><p><strong>2. Quarterly Layoff Distribution (Pie Chart)</strong></p><ul><li><strong>Apa yang Menarik?</strong>: Chart ini menunjukkan bahwa PHK paling banyak terjadi di kuartal pertama setiap tahun. Ini bisa terjadi karena perusahaan sering melakukan evaluasi tahunan dan menyesuaikan strategi mereka di awal tahun, termasuk melakukan PHK jika diperlukan untuk menjaga keberlangsungan bisnis.</li></ul><p><strong>3. Top 10 Industries by Layoffs (Bar Chart)</strong></p><ul><li><strong>Siapa yang Paling Terdampak?</strong>: Di sini, kita melihat industri mana saja yang paling banyak kehilangan karyawan. Sektor konsumen dan ritel ada di puncak daftar, yang menunjukkan bahwa sektor ini sangat rentan terhadap perubahan ekonomi. Hal ini mungkin disebabkan oleh perubahan pola belanja konsumen yang cepat dan fluktuasi permintaan.</li></ul><p><strong>4. Funds Raised per Layoff by Industry (Bar Chart)</strong></p><ul><li><strong>Fakta Menarik:</strong> Meski banyak perusahaan melakukan PHK, beberapa industri seperti media dan transportasi masih bisa mengumpulkan dana yang signifikan. Ini menunjukkan bahwa industri-industri ini punya strategi yang kuat untuk bertahan di tengah krisis, meskipun mereka harus mengurangi jumlah karyawannya.</li></ul><h3>Penjelasan Halaman 2: Prediksi dan Dampak PHK di Masa Depan</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HkmkIyk3Jiy4gFpXtXOOag.png" /></figure><p><strong>1. Industry-wise Layoff Forecast (Grafik Bar Terkelompok)</strong></p><ul><li><strong>Apa yang Diprediksi?</strong>: Grafik ini memprediksi berapa banyak PHK yang mungkin terjadi di berbagai industri hingga tahun 2023. Sektor teknologi dan keuangan diperkirakan akan tetap mengalami PHK dalam jumlah besar. Ini menandakan bahwa meskipun sektor ini penting, mereka tetap menghadapi tantangan besar untuk beradaptasi dengan perubahan ekonomi global.</li></ul><p><strong>2. Tech Layoffs Forecast (Grafik Garis)</strong></p><ul><li><strong>Apa yang Diungkapkan?</strong>: Di sini, kita melihat bahwa industri teknologi masih akan menghadapi PHK yang signifikan. Ini bisa jadi karena perubahan cepat dalam teknologi dan kebutuhan untuk terus berinovasi, yang sering kali memaksa perusahaan untuk merampingkan tenaga kerja mereka.</li></ul><p><strong>3. Top 10 Funds Raised per Layoff by Industry (Bar Chart)</strong></p><ul><li><strong>Siapa yang Bisa Bertahan?</strong>: Grafik ini menunjukkan industri mana yang paling berhasil mengumpulkan dana setiap kali terjadi PHK. Media dan transportasi kembali berada di urutan teratas, menunjukkan bahwa meskipun mereka harus melakukan PHK, mereka tetap bisa mendapatkan modal untuk terus beroperasi dan beradaptasi dengan kondisi pasar yang berubah.</li></ul><h4>4. Kesimpulan dan Insight dari Proyek</h4><p>Dari data yang udah diolah dan divisualisasikan, gue dapet beberapa insight yang penting:</p><ul><li><strong>Tren PHK: </strong>Gue melihat bahwa jumlah PHK meningkat tajam di tahun 2022 dan 2023. Ini menunjukkan adanya krisis yang lebih besar dan perusahaan yang kesulitan beradaptasi dengan cepat.</li><li><strong>Industri yang Paling Terdampak: </strong>Sektor konsumen dan ritel adalah yang paling banyak kehilangan karyawan. Ini mungkin disebabkan oleh perubahan pola belanja dan kebutuhan konsumen yang berubah drastis.</li><li><strong>Strategi Bertahan Hidup:</strong> Beberapa industri seperti media dan transportasi masih bisa ngumpulin dana besar meskipun mereka melakukan PHK besar-besaran. Ini menunjukkan pentingnya strategi keuangan yang kuat untuk bertahan di masa krisis.</li></ul><p>Jadi, apa yang bisa kita pelajari dari semua ini? Pertama, dunia kerja itu keras, dan lo harus siap untuk perubahan yang cepat. Industri yang dulu dianggap stabil bisa tiba-tiba goyah dan harus ngurangin banyak karyawan. Kedua, strategi manajemen yang tepat itu kunci buat bertahan. Perusahaan yang bisa ngumpulin dana dan beradaptasi dengan cepat, bakal lebih mudah untuk tetap bertahan.</p><p>Project ini bikin gue makin ngerti bahwa di balik setiap angka, ada cerita besar yang bisa kita ungkap. Gue harap cerita gue ini bisa ngasih lo semua wawasan baru tentang dunia kerja dan industri global. Kalau lo punya pertanyaan atau pengen tahu lebih banyak, feel free buat tanya-tanya gue ya!</p><p>Connect With Me in <a href="http://www.linkedin.com/in/DanielSiringoringo">Linkedin</a></p><p>You Can Check Code of This Project in <a href="https://github.com/Danielringo/World-Layoffs/tree/main">Here</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bd4eebd9bfa6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Garuda Airline Flight Sales Insights: Uncovering Trends and Opportunities with MySQL and Power BI]]></title>
            <link>https://blog.devgenius.io/garuda-airline-flight-sales-insights-uncovering-trends-and-opportunities-with-mysql-and-power-bi-e26b077b81c7?source=rss-5000a4cfde44------2</link>
            <guid isPermaLink="false">https://medium.com/p/e26b077b81c7</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[sales-insights]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[mysql]]></category>
            <dc:creator><![CDATA[Daniel Ringo]]></dc:creator>
            <pubDate>Tue, 27 Aug 2024 16:27:40 GMT</pubDate>
            <atom:updated>2024-08-28T17:56:29.796Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/267/0*PI4sjpN72ZKtfmt_" /></figure><h3>Introduction</h3><p>In today’s competitive airline industry, understanding transaction patterns and revenue streams is crucial for making informed business decisions. This project leverages the power of MySQL for data analysis and Power BI for visualization to uncover insights from Garuda Airline transaction data. The goal is to provide a clear picture of the key metrics driving performance and customer behavior in the airline industry.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*w6Nc-2mtq0vFD16S.png" /><figcaption><strong>Data Processing MySQL</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*OSmJArRRIZjZs0pb.jpg" /><figcaption>Data Visualization Power BI</figcaption></figure><h3>Data Overview</h3><p>The dataset includes detailed records of airline transactions, such as ticket sales, flight categories, pricing, and customer demographics. The analysis aims to segment flights, assess revenue contributions, and identify customer purchase patterns.</p><h4>Data Processing Using MySQL:</h4><pre>SELECT<br>    CASE<br>        WHEN type_flight = &#39;Domestic&#39; THEN &#39;Domestik&#39;<br>        ELSE &#39;Internasional&#39;<br>    END AS kategori_penerbangan,<br>    Airlines AS maskapai,<br>    airlines_code AS kode_maskapai,<br>    origin_airport AS bandara_asal,<br>    destination_airport AS bandara_tujuan,<br>    COUNT(*) AS jumlah_transaksi,<br>    SUM(Quantity) AS total_tiket_terjual,<br>    SUM(total_payment) AS total_pendapatan,<br>    ROUND(AVG(Price), 2) AS rata_rata_harga_tiket,<br>    MAX(Price) AS harga_tiket_tertinggi,<br>    COUNT(DISTINCT name_passengers) AS jumlah_penumpang_unik,<br>    SUM(CASE WHEN Quantity &gt; 1 THEN 1 ELSE 0 END) AS pembelian_multi_tiket,<br>    ROUND(AVG(Kurs), 2) AS rata_rata_kurs,<br>    COUNT(DISTINCT flight_code) AS jumlah_penerbangan_unik,<br>    ROUND(SUM(total_payment) / SUM(Quantity), 2) AS pendapatan_per_tiket,<br>    CASE<br>        WHEN time_transactions LIKE &#39;%am%&#39; THEN &#39;Pagi&#39;<br>        WHEN time_transactions LIKE &#39;%pm%&#39; AND SUBSTRING(time_transactions, 1, 2) &lt; &#39;05&#39; THEN &#39;Siang&#39;<br>        ELSE &#39;Malam&#39;<br>    END AS periode_transaksi,<br>    CASE<br>        WHEN departure_time LIKE &#39;%am%&#39; THEN &#39;Pagi&#39;<br>        WHEN departure_time LIKE &#39;%pm%&#39; AND SUBSTRING(departure_time, 1, 2) &lt; &#39;05&#39; THEN &#39;Siang&#39;<br>        ELSE &#39;Malam&#39;<br>    END AS periode_keberangkatan<br>FROM<br>    sales_monthly_reporting<br>GROUP BY<br>    kategori_penerbangan,<br>    maskapai,<br>    kode_maskapai,<br>    bandara_asal,<br>    bandara_tujuan,<br>    periode_transaksi,<br>    periode_keberangkatan<br>ORDER BY<br>    total_pendapatan DESC,<br>    jumlah_transaksi DESC<br>LIMIT 1000;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*l9I2OwM9Kli_qdAl8hz6lw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*paHBsSbowONSLfCgRliu6Q.png" /><figcaption><strong>Query Result</strong></figcaption></figure><p>Analysis of MySQL Query Results:</p><p>1. Flight Categorization:<br> — The query categorizes flights into “Domestik” (Domestic) and “Internasional” (International).<br>Insight: This allows for easy comparison between domestic and international flight performance.</p><p>2. Airline Details:<br> — Includes airline names and codes.<br>Insight: Enables tracking of individual airline performance across various metrics.</p><p>3. Airport Information:<br> — Captures origin and destination airports.<br>Insight: Helps identify popular routes and hub performance.</p><p>4. Transaction Metrics:<br> — Counts total transactions and tickets sold.<br>Insight: Provides a clear picture of sales volume for each airline and route.</p><p>5. Revenue Analysis:<br> — Calculates total revenue and average ticket price.<br>Insight: Crucial for understanding the financial performance of different routes and airlines.</p><p>6. Pricing Insights:<br> — Includes highest ticket price and revenue per ticket.<br>Insight: Helps in identifying premium routes and pricing strategies.</p><p>7. Customer Behavior:<br> — Tracks unique passengers and multi-ticket purchases.<br>Insight: Useful for understanding customer loyalty and group travel trends.</p><p>8. Currency Impact:<br> — Calculates average exchange rate.<br>Insight: Important for international routes to understand currency fluctuation impacts.</p><p>9. Flight Diversity:<br> — Counts unique flight codes.<br>Insight: Indicates the variety of flight options available on each route.</p><p>10. Time-based Analysis:<br> — Categorizes transactions and departures into morning, afternoon, and evening periods.<br>Insight: Helps identify peak booking and travel times.</p><p>Conclusion:<br>This comprehensive MySQL query provides a holistic view of airline operations, covering everything from financial performance to customer behavior patterns. It allows for deep analysis of route profitability, airline efficiency, and market trends. The inclusion of time-based metrics and currency considerations adds layers of insight for strategic decision-making.</p><h4>Power BI Dashboard Visualization:</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*shjoeOeb311sRBL5EKWrKw.png" /><figcaption><strong>Visualization 1</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*b5c0D7ybPpB4iXQyGkRRMw.png" /><figcaption><strong>Visualization 2</strong></figcaption></figure><p>Analysis:</p><p>1. Overall Performance Metrics:<br> — Total Transactions: 45<br> — Total Revenue: Rp633.95 million<br> — Average Quantity: 94</p><p>Insight: The airline industry is generating significant revenue with a relatively low number of transactions, indicating high-value ticket sales.</p><p>2. Revenue Distribution by Flight Category:<br> — International: 86.7%<br> — Domestic: 13.3%</p><p>Insight: International flights are the primary revenue drivers, suggesting a focus on long-haul routes and potentially higher-priced tickets.</p><p>3. Top 10 Airlines at Singapore Changi Airport:<br> — Emirates leads with Rp110 million in revenue<br> — Singapore Airlines follows with Rp91 million<br> — Other major players include All Nippon Airways, Japan Airlines, and Qatar Airways</p><p>Insight: Middle Eastern and Asian carriers dominate the top revenue-generating airlines at Changi Airport, reflecting strong regional and long-haul connections.</p><p>4. Top Airlines at Soekarno-Hatta Airport (Indonesia):<br> — Garuda Indonesia leads with Rp13.2 million<br> — Lion Air and Indonesia AirAsia follow closely</p><p>Insight: The domestic market in Indonesia is competitive, with a mix of full-service and low-cost carriers capturing significant market share.</p><p>5. Ticket Price vs. Sales Volume Correlation:<br> — Emirates and Singapore Airlines show high ticket prices and high sales volumes<br> — Some airlines like Lion Air have lower ticket prices but maintain good sales volumes</p><p>Insight: There’s a diverse market with successful airlines in both premium and budget segments, catering to different customer preferences.</p><p>6. Ticket Price Distribution Analysis:<br> — Wide range of ticket prices across airlines<br> — Cathay Pacific Airlines, Emirates, and Japan Airlines show higher price points</p><p>Insight: Premium carriers maintain higher ticket prices, likely due to superior service offerings and long-haul routes.</p><p>7. Top Flight Routes by Revenue:<br> — Singapore Changi to Narita Tokyo: Rp74,250,000<br> — Singapore Changi to Incheon International: Rp66,840,000<br> — Singapore Changi to Darwin International: Rp63,300,000</p><p>Insight: East Asian routes from Singapore are particularly lucrative, highlighting strong business and tourism connections.</p><p>8. Ticket Price Trends by Flight Type and Airline:<br> — Full-service carriers generally show higher average ticket prices<br> — Budget airlines like Lion Air, Citilink, and AirAsia have lower average prices but still contribute significantly to revenue</p><p>Insight: The market successfully supports both premium and budget models, with each segment finding its niche in the competitive landscape.</p><p>Conclusion:</p><p>This dashboard provides a comprehensive overview of the airline industry’s performance, focusing on key hubs like Singapore Changi and Soekarno-Hatta airports. The analysis reveals:</p><p>1. Strong international market dominance in revenue generation<br>2. Successful coexistence of premium and budget airlines<br>3. Key routes between major Asian cities driving significant revenue<br>4. Diverse pricing strategies across airlines, catering to different market segments</p><p>The value of this dashboard lies in its ability to:</p><p>1. Identify top-performing airlines and routes for potential investment or partnership opportunities<br>2. Highlight the importance of international routes for revenue growth<br>3. Showcase the balance between premium and budget offerings in the market<br>4. Provide insights for pricing strategies based on route popularity and airline positioning</p><p>These insights can guide strategic decisions for airlines, airports, and travel industry stakeholders, helping them optimize routes, adjust pricing, and target specific market segments for maximum profitability and growth.</p><p>You Can Check Full Code in <a href="https://github.com/Danielringo/Flight-Domestic-Airport">Here</a></p><p>Connect With me in <a href="https://www.linkedin.com/in/DanielSiringoringo">Linkedin</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e26b077b81c7" width="1" height="1" alt=""><hr><p><a href="https://blog.devgenius.io/garuda-airline-flight-sales-insights-uncovering-trends-and-opportunities-with-mysql-and-power-bi-e26b077b81c7">Garuda Airline Flight Sales Insights: Uncovering Trends and Opportunities with MySQL and Power BI</a> was originally published in <a href="https://blog.devgenius.io/">Dev Genius</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Unleashing Retail Intelligence: A Deep Dive into Supermarket Sales Analytics]]></title>
            <link>https://medium.com/@danielsiringoringo052/unleashing-retail-intelligence-a-deep-dive-into-supermarket-sales-analytics-88266bdf8803?source=rss-5000a4cfde44------2</link>
            <guid isPermaLink="false">https://medium.com/p/88266bdf8803</guid>
            <category><![CDATA[analysis]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[sales]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Daniel Ringo]]></dc:creator>
            <pubDate>Mon, 26 Aug 2024 06:53:49 GMT</pubDate>
            <atom:updated>2024-08-26T06:53:49.214Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*OPuKx3rUzWszcAYC" /><figcaption>Photo by <a href="https://unsplash.com/@hostreviews?utm_source=medium&amp;utm_medium=referral">Stephen Phillips - Hostreviews.co.uk</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>In the ever-evolving landscape of retail, data-driven decision-making has become the cornerstone of success. Today, we embark on an analytical journey through a comprehensive MySQL query that unlocks valuable insights from a supermarket’s sales data. This exploration will reveal how intricate SQL queries can transform raw data into actionable intelligence, empowering businesses to thrive in a competitive market.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/400/0*s_8JZmi5xWVRB0LE.png" /></figure><p>The Power of Structured Query Language:</p><p>Our analysis begins with a meticulously crafted MySQL query, a testament to the power of SQL in dissecting complex business operations. This query isn’t just a string of code; it’s a key that unlocks a treasure trove of retail insights.</p><pre>WITH sales_data AS (<br>    SELECT<br>        input_date,<br>        Category,<br>        Gender,<br>        Domicile,<br>        member_code,<br>        product_name,<br>        Quantity,<br>        item_price,<br>        total_payment,<br>        CASE <br>            WHEN Tax LIKE &#39;%%&#39; THEN CAST(REPLACE(Tax, &#39;%&#39;, &#39;&#39;) AS DECIMAL) / 100 * total_payment<br>            ELSE 0 <br>        END AS tax_amount<br>    FROM<br>        supermarket<br>),<br>total_sales_by_category AS (<br>    SELECT<br>        Category,<br>        SUM(total_payment) AS total_sales,<br>        SUM(tax_amount) AS total_tax<br>    FROM<br>        sales_data<br>    GROUP BY<br>        Category<br>),<br>avg_purchase_by_gender AS (<br>    SELECT<br>        Gender,<br>        AVG(total_payment) AS avg_purchase_amount<br>    FROM<br>        sales_data<br>    GROUP BY<br>        Gender<br>),<br>monthly_sales AS (<br>    SELECT<br>        DATE_FORMAT(input_date, &#39;%Y-%m&#39;) AS month,<br>        SUM(CASE WHEN member_code IS NOT NULL THEN total_payment ELSE 0 END) AS member_sales<br>    FROM<br>        sales_data<br>    GROUP BY<br>        month<br>),<br>top_products AS (<br>    SELECT<br>        product_name,<br>        SUM(total_payment) AS total_sales,<br>        SUM(Quantity) AS total_quantity<br>    FROM<br>        sales_data<br>    GROUP BY<br>        product_name<br>    ORDER BY<br>        total_sales DESC<br>    LIMIT 5<br>),<br>sales_performance AS (<br>    SELECT<br>        Domicile,<br>        Category,<br>        SUM(total_payment) AS total_sales<br>    FROM<br>        sales_data<br>    GROUP BY<br>        Domicile, Category<br>    ORDER BY<br>        total_sales DESC<br>)<br>SELECT<br>    cat.Category,<br>    cat.total_sales,<br>    cat.total_tax,<br>    gen.Gender,<br>    gen.avg_purchase_amount,<br>    mon.month,<br>    mon.member_sales,<br>    tp.product_name,<br>    tp.total_sales AS product_sales,<br>    tp.total_quantity AS product_quantity,<br>    sp.Domicile,<br>    sp.total_sales AS region_sales<br>FROM<br>    total_sales_by_category cat<br>JOIN<br>    avg_purchase_by_gender gen ON 1=1<br>JOIN<br>    monthly_sales mon ON 1=1<br>JOIN<br>    top_products tp ON 1=1<br>JOIN<br>    sales_performance sp ON sp.Category = cat.Category<br>ORDER BY<br>    cat.total_sales DESC,<br>    gen.avg_purchase_amount DESC,<br>    mon.month,<br>    tp.total_sales DESC,<br>    sp.total_sales DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3A7s92ogFWWykLb3d-Htsw.png" /><figcaption><strong>QUERY RESULT</strong></figcaption></figure><h4>Peeling Back the Layers:</h4><ol><li>Foundation Building: Sales Data Preparation<br>At the heart of our analysis lies the ‘sales_data’ Common Table Expression (CTE). This foundational layer meticulously prepares our dataset, encompassing crucial elements such as transaction dates, product categories, customer demographics, and financial metrics. The careful calculation of tax amounts demonstrates a commitment to financial accuracy that underpins all subsequent analyses.</li></ol><p>2. Category Performance: The Pillars of Revenue<br>The ‘total_sales_by_category’ CTE aggregates sales and tax data by product category, offering a crystal-clear view of which product lines are the true revenue drivers. This insight is invaluable for strategic inventory management and pricing strategies.</p><p>3. Gender Insights: Understanding Customer Behavior<br>By examining average purchase amounts across genders, the query opens a window into gender-specific purchasing patterns. This granular view can inform targeted marketing campaigns and personalized customer experiences.</p><p>4. Membership Matters: Evaluating Loyalty Programs<br>The ‘monthly_sales’ CTE focuses on member-driven sales, providing a month-by-month breakdown of how membership programs impact the bottom line. This data is crucial for refining customer retention strategies and maximizing the value of loyal patrons.</p><p>5. Product Champions: Identifying Top Performers<br>The ‘top_products’ CTE spotlights the supermarket’s star products, ranking them by sales and quantity. This information is gold for inventory optimization, marketing focus, and understanding evolving customer preferences.</p><p>6. Geographical Genius: Regional Sales Analysis<br>With the ‘sales_performance’ CTE, the query delves into geographical sales patterns, breaking down performance by domicile and category. This regional intelligence can drive location-specific strategies and inform expansion plans.</p><p>7. The Big Picture: Integrated Analysis<br>The final SELECT statement is where the magic happens, weaving together all these analytical threads into a cohesive tapestry of business intelligence. This integration allows for the discovery of complex patterns and correlations that might otherwise remain hidden.</p><h4>Key Insights and Strategic Implications:</h4><p>1. Data-Driven Culture: This query exemplifies a commitment to leveraging data for strategic decision-making, setting a foundation for a culture of continuous improvement.</p><p>2. Customer-Centricity: By analyzing gender-based purchasing and membership sales, the business demonstrates a laser focus on understanding and serving distinct customer segments.</p><p>3. Product Portfolio Optimization: The emphasis on category-wise sales and top products enables precise inventory management and category development strategies.</p><p>4. Geographical Intelligence: Regional sales analysis provides the insights needed for tailored local marketing and supply chain optimization.</p><p>5. Financial Precision: The meticulous tax calculations reflect a commitment to financial accuracy and regulatory compliance.</p><p>6. Performance Monitoring: Monthly sales tracking facilitates agile responses to market trends and seasonal fluctuations.</p><p>7. Scalable Analytics: The query’s modular structure allows for easy expansion, ensuring the analytical framework can grow with the business.</p><h4>Conclusion:</h4><p>This MySQL query is more than just a data retrieval tool; it’s a powerful lens through which we can view the intricate workings of a retail operation. By harnessing the insights it provides, supermarkets can fine-tune their strategies, enhance customer experiences, and drive growth in an increasingly competitive landscape.</p><p>As we close this analytical chapter, we’re reminded that in the world of retail, knowledge truly is power. And with the right queries at our fingertips, we have the power to transform data into decisions, numbers into strategies, and information into retail excellence.</p><p>The future of retail belongs to those who can not only collect data but interpret and act upon it with precision and foresight. This MySQL query is a step toward that future — a future where every business decision is informed, every strategy is data-backed, and every customer interaction is optimized for success.</p><p>VISUALIZATION IN POWER BI</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*M3qzL_aeXahzKymW.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bHQ7Zy0rrG9T9jlEXTpEAQ.png" /></figure><h4>ANALYSIS DASHBOARD:</h4><p>1. Total Sales by Category (Donut Chart):<br> — Food Product leads with 35.71% of total sales<br> — Non Food Product follows at 29.93%<br> — General Merchandise Product accounts for 18.96%<br> — Perishable items contribute 15.4%<br>Insight: Food products dominate sales, indicating they are the primary driver of revenue for the supermarket.</p><p>2. KPI Cards:<br> — Total Sales: $8.65 billion<br> — Total Tax: $1.73 trillion<br> — Product Sales: $3.42 billion<br> — Average Purchase: $1.99 trillion<br>Insight: The high total tax amount suggests significant tax implications or possibly an error in calculation or display units.</p><p>3. Top 5 Products (Horizontal Bar Chart):<br> 1. Baby needs: $1,215.68 million<br> 2. Tea, coffee and creamer: $715.88 million<br> 3. Household items: $563.96 million<br> 4. Housewares &amp; party needs: $486.83 million<br> 5. Fruit: $433.23 million<br>Insight: Baby needs are the top-selling product category, significantly outperforming other categories. This suggests a strong market for baby products.</p><p>4. Tax vs Sales by Category (Scatter Plot):<br> — Food Product and Non Food Product categories show the highest sales and tax amounts<br> — Perishable items have the lowest sales and tax amounts<br>Insight: There’s a positive correlation between sales and tax across categories, with non-perishable items contributing more to both sales and tax revenue.</p><p>5. Region Sales by Domicile and Category (Stacked Bar Chart):<br> — Jakarta shows the highest sales across all categories<br> — Tangerang, Bekasi, Bogor, and Bandung follow in descending order<br> — The sales pattern across categories is relatively consistent across regions<br>Insight: Jakarta is the primary market for the supermarket chain, with other cities showing significant but lower sales volumes.</p><h4>Conclusion:</h4><p>The Supermarket Sales Dashboard reveals several key insights about the business:</p><p>1. Product Mix: Food products are the main revenue driver, accounting for over a third of total sales. This suggests that the supermarket should focus on maintaining a diverse and high-quality food product inventory.</p><p>2. High-Value Categories: Baby needs emerge as the top-selling product category, indicating a strong market demand. This presents an opportunity for the supermarket to expand its baby product range or create targeted marketing campaigns for this demographic.</p><p>3. Regional Performance: Jakarta is the standout performer in terms of sales across all product categories. This could inform decisions about inventory allocation and possible expansion strategies in other cities to replicate Jakarta’s success.</p><p>4. Tax Implications: The high tax amount relative to sales suggests either significant tax obligations or a potential error in calculation or display. This needs further investigation to ensure accurate financial reporting and tax compliance.</p><p>5. Category Balance: While food products lead in sales, non-food products and general merchandise also contribute significantly. This balanced product mix helps in risk distribution and caters to diverse customer needs.</p><p>6. Perishables Management: Perishable items contribute the least to sales and tax, indicating a need for efficient inventory management to minimize waste and maximize profitability in this category.</p><p>Overall, the supermarket appears to have a strong market presence with a well-balanced product mix. To drive growth, the business could focus on expanding its baby products line, optimizing its operations in Jakarta while looking for growth opportunities in other cities, and improving the performance of its perishables category. Additionally, the tax calculations should be reviewed for accuracy to ensure proper financial management and reporting.</p><p>THAT’S ALL ABOUT MY PERSONAL PROJECT THANKYOU FOR YOUR ATTENTION !!! 🙂</p><p>you can check full interactive visualization in <a href="https://github.com/Danielringo/Supermarket-Sales-Analysis">HERE</a></p><p>Connect with me on <a href="https://www.linkedin.com/in/DanielSiringoringo">Linkedin</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=88266bdf8803" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Customer Activity and Sales Metrics Analysis]]></title>
            <link>https://medium.com/@danielsiringoringo052/customer-activity-and-sales-metrics-analysis-4f63a2bdf7d9?source=rss-5000a4cfde44------2</link>
            <guid isPermaLink="false">https://medium.com/p/4f63a2bdf7d9</guid>
            <category><![CDATA[google-big-query]]></category>
            <category><![CDATA[visualization]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[looker-studio]]></category>
            <dc:creator><![CDATA[Daniel Ringo]]></dc:creator>
            <pubDate>Sat, 17 Aug 2024 04:37:19 GMT</pubDate>
            <atom:updated>2024-08-17T04:37:19.550Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*4B7rZJ_BlDW6NDrA" /><figcaption>Photo by <a href="https://unsplash.com/@dengxiangs?utm_source=medium&amp;utm_medium=referral">Choong Deng Xiang</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><blockquote>Hello Everyone,</blockquote><p>In this project, I utilized Google BigQuery as the primary tool for querying and analyzing the dataset, which is sourced from Kaggle and includes data from the user_tab and order_tab tables. Google BigQuery allowed me to efficiently handle large volumes of data and execute complex SQL queries to derive meaningful insights.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/385/1*ppJE2iETZIZBwOFstQfqwA.png" /></figure><p>In this project, <strong>Google BigQuery</strong> was utilized as the primary data processing tool. BigQuery allowed for efficient querying and analysis of large datasets related to branch performance, customer activity, and transaction types. The SQL queries executed in BigQuery provided the foundational insights necessary for further analysis.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/351/0*_MpFICKsgmy9J56M.png" /><figcaption><strong>Google Big Query</strong></figcaption></figure><p>After querying the data, the results were exported to <strong>CSV files</strong>. CSV files served as an intermediate step to convert the raw query outputs into a format that could be easily imported into visualization tools. This process ensured that the data was structured and ready for the next phase of the project.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/512/0*_Oke9waMw9gqlCYz.png" /><figcaption><strong>CSV (Comma Seperated Value)</strong></figcaption></figure><p>Finally, <strong>Google Looker Studio</strong> was used to create visualizations that transformed the raw data into meaningful insights. Looker Studio allowed for the creation of various charts and graphs that made the data easier to interpret and share with stakeholders.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*9Dsfaor9nOC76dAV.jpg" /><figcaption><strong>Goole Looker Studio</strong></figcaption></figure><blockquote>Analysis Overview</blockquote><h4>Step 1: Querying Data in Google BigQuery</h4><p>The first step in this analysis involves querying the data stored in Google BigQuery. The SQL queries aim to extract valuable insights from the dataset related to branch performance, customer activity, and transaction types. Here is a brief explanation of each query used:</p><ol><li>Branch-wise Sales Performance</li></ol><pre>SELECT <br>  u.branch,<br>  SUM(o.amount) AS total_sales,<br>  COUNT(DISTINCT o.transactionid) AS num_transactions<br>FROM `Projet_SQL.order_tab` o<br>LEFT JOIN `Projet_SQL.user_tab` u ON o.customerid = u.customerid<br>GROUP BY u.branch<br>ORDER BY total_sales DESC;</pre><p>Result:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xVUXt2ny6sT3TELBXe-M3Q.png" /></figure><blockquote>This query calculates the total sales and the number of transactions for each branch. The goal is to identify which branches generate the most revenue and have the highest transaction volumes.</blockquote><p>2. Active Customers Over Time</p><pre>SELECT <br>  u.branch,<br>  DATE(o.transaction_time) AS transaction_date,<br>  COUNT(DISTINCT o.customerid) AS active_customers<br>FROM `Projet_SQL.order_tab` o<br>LEFT JOIN `Projet_SQL.user_tab` u ON o.customerid = u.customerid<br>GROUP BY u.branch, transaction_date<br>ORDER BY transaction_date, u.branch;</pre><p>Query Result:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ElT7x4p_jKgAA-VCGPpmxg.png" /><figcaption><strong>The Total of Rows 1064 Rows Data</strong></figcaption></figure><blockquote>By breaking down customer activity by branch and transaction date, I could identify peak periods and days with lower customer engagement. This insight helps in optimizing marketing strategies and resource allocation.</blockquote><p>3. Transaction Types</p><pre>SELECT <br>  u.branch,<br>  o.transaction_type,<br>  SUM(o.amount) AS total_amount<br>FROM `Projet_SQL.order_tab` o<br>LEFT JOIN `Projet_SQL.user_tab` u ON o.customerid = u.customerid<br>GROUP BY u.branch, o.transaction_type<br>ORDER BY u.branch, total_amount DESC<br>LIMIT 5;</pre><p>Query Result:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XfWZlHKF6LA2Vc3RoYGzRA.png" /></figure><blockquote>The analysis of different transaction types across branches provided insights into which transaction categories generate the most revenue. This can help in tailoring services to customer preferences.</blockquote><p>4. Customer Segmentation</p><pre>WITH customer_frequency AS (<br>  SELECT <br>    customerid,<br>    COUNT(transactionid) AS purchase_count<br>  FROM `Projet_SQL.order_tab`<br>  GROUP BY customerid<br>)<br>SELECT <br>  customerid,<br>  CASE<br>    WHEN purchase_count = 1 THEN &#39;One-time Customer&#39;<br>    WHEN purchase_count BETWEEN 2 AND 5 THEN &#39;Frequent Customer&#39;<br>    ELSE &#39;Loyal Customer&#39;<br>  END AS customer_segment,<br>  purchase_count<br>FROM customer_frequency<br>ORDER BY purchase_count DESC;</pre><p>Query Result:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qm_U-0zrCjdarn8fbDKcHA.png" /><figcaption><strong>The Total of Rows 10181 Rows Data</strong></figcaption></figure><blockquote>Segmenting customers based on their purchase frequency allowed us to identify key customer groups, such as one-time customers, frequent customers, and loyal customers. This segmentation is essential for targeted marketing and customer retention strategies.</blockquote><p>5. Transaction Patterns</p><pre>SELECT <br>  user_tab.branch,<br>  CASE<br>    WHEN MOD(order_tab.transactionid, 2) = 0 THEN &#39;Even&#39;<br>    ELSE &#39;Odd&#39;<br>  END AS transaction_category,<br>  COUNT(DISTINCT user_tab.customerid) AS buyer_count<br>FROM <br>  `Projet_SQL.order_tab` as order_tab<br>JOIN <br>  `Projet_SQL.user_tab` as user_tab<br>  ON order_tab.customerid = user_tab.customerid<br>GROUP BY <br>  user_tab.branch, transaction_category;</pre><p>Query Result:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qD38WSH7OlQdZDlnGfQcdw.png" /></figure><blockquote>The even and odd transaction categorization offers a novel way to examine buyer behavior, which could be leveraged in unique promotional strategies.</blockquote><h4>Extracting Data to CSV</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*okelHrTDI6K2lnrBPFMZdw.png" /><figcaption><strong>Extract From Query to CSV</strong></figcaption></figure><p>Once the queries are executed in BigQuery, the next step is to export the results to CSV files. This allows the data to be easily imported into Google Looker Studio for visualization. Exporting to CSV ensures that the data is in a format compatible with visualization tools and can be processed efficiently.</p><h4>Visualizing Data in Google Looker Studio</h4><p>After the data is exported to CSV, it’s time to create visualizations that make the insights more accessible and actionable. Here’s how each query result was visualized:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/991/1*raxV_tLvgTWj_Qj2QsseWg.png" /><figcaption><a href="https://lookerstudio.google.com/reporting/6f345c4b-9ac6-40fe-9dde-13e15ff6723a"><strong>Visualization in Google Looker Studio</strong></a></figcaption></figure><h3>Insights</h3><ol><li><strong>Branch Performance:</strong> The bar chart in Looker Studio revealed clear differences in sales performance across branches. Top-performing branches were highlighted, while underperforming ones became evident. This insight suggests that targeted strategies should be developed to improve sales in weaker branches.</li><li><strong>Customer Engagement:</strong> The line chart tracking active customers over time showed varying engagement levels across branches. Some branches consistently attracted customers, while others experienced fluctuations. This insight highlights the need to investigate the factors contributing to consistent customer engagement in certain branches and replicate those practices in others.</li><li><strong>Transaction Types:</strong> The stacked bar chart provided a breakdown of transaction types, revealing which types contributed most to overall revenue. This insight suggests that focusing marketing and operational efforts on the most successful transaction types could yield better results.</li><li><strong>Customer Loyalty:</strong> The pie chart segmented customers based on their purchase frequency, showing the distribution between One-time, Frequent, and Loyal customers. This insight emphasizes the importance of maintaining loyal customers while identifying opportunities to convert one-time buyers into more frequent shoppers.</li><li><strong>Transaction Patterns:</strong> The pie chart categorizing transactions as ‘Even’ or ‘Odd’ offered a unique perspective on transaction patterns. This insight could be used to develop targeted promotional strategies based on transaction behavior.</li></ol><h3>Conclusion</h3><p>This project demonstrated the effective use of <strong>Google BigQuery</strong> for data analysis, <strong>CSV files</strong> for data transfer, and <strong>Google Looker Studio</strong> for creating insightful visualizations. The combination of these tools allowed for a thorough analysis of branch performance, customer activity, and transaction types.</p><p>The insights gained from this analysis provide a roadmap for improving branch performance, enhancing customer engagement, and optimizing transaction strategies. By continuously monitoring and adapting based on these insights, the business can position itself for sustained growth and success in a competitive market.</p><p>Connect with me in: <a href="https://www.linkedin.com/in/DanielSiringoringo">Linkedin</a></p><p>You can Visit the Code in Here: <a href="https://github.com/Danielringo/Customer-and-Sales-Metrics-Analysis?tab=readme-ov-file#customer-and-sales-metrics-analysis">Github</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4f63a2bdf7d9" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[PROJECT WARMINDO SALES ANALYSIS]]></title>
            <link>https://blog.devgenius.io/project-warmindo-sales-analysis-58423c13908d?source=rss-5000a4cfde44------2</link>
            <guid isPermaLink="false">https://medium.com/p/58423c13908d</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[dbeaver]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[looker-studio]]></category>
            <dc:creator><![CDATA[Daniel Ringo]]></dc:creator>
            <pubDate>Thu, 18 Jul 2024 16:10:51 GMT</pubDate>
            <atom:updated>2024-08-03T17:09:25.638Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/750/0*co7N7xF_pshpQ1sm" /><figcaption>WARMINDO</figcaption></figure><p>I recently completed an analysis project for a local Warmindo (a type of small Indonesian restaurant) using SQL for data processing via DBeaver and Looker Studio for data visualization. This project aimed to uncover key insights from the transaction data to help the business make informed decisions. The dataset for this project was provided by Bima, who shares valuable datasets and insights on his Instagram account <a href="https://www.instagram.com/ngulik.data?utm_source=ig_web_button_share_sheet&amp;igsh=ZDNlZDc0MzIxNw==">@Ngulikdata</a>.</p><h4>Project Overview</h4><p>The Warmindo analysis project focused on understanding sales performance, customer behavior, and payment methods. Through SQL queries and visualizations, I aimed to identify top-performing products, sales trends, and customer preferences.</p><h4>Data Processing with DBeaver</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/0*Zf4tEFnfvc7xC60O" /></figure><h4>Data Visualization with Google Looker Studio</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*eeUUlrItHb79m3uU.png" /></figure><h4>Data Processing</h4><p><strong>1. Data Extraction:</strong> Using SQL in DBeaver, I queried the Warmindo transaction database to extract relevant data. This involved selecting key metrics such as product names, transaction dates, quantities sold, and sales prices.</p><p><strong>2. Data Transformation:</strong> The extracted data was cleaned and transformed to create meaningful metrics and aggregations. This included calculating total sales revenue, average transaction values, and identifying the most popular products.</p><p><strong>3. Data Loading:</strong> The processed data was then imported into Looker Studio for visualization. This step was crucial for turning raw data into actionable insights.</p><ol><li>Total Sales Revenue for Each Product</li></ol><pre>SELECT <br>  nama_produk, <br>  SUM(quantity * harga_jual) AS total_revenue<br>FROM warmindo <br>GROUP BY nama_produk<br>ORDER BY total_revenue DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/936/1*jmCwFpf352agLm9ejJbDUw.png" /></figure><blockquote><em>Insight:</em> This query identifies the total revenue generated by each product, helping us understand which products contribute the most to our sales.</blockquote><p>2. Most Popular Products Based on Number of Transactions</p><pre>SELECT <br>  nama_produk, <br>  COUNT(invoice_id) AS number_of_transactions<br>FROM warmindo <br>GROUP BY nama_produk<br>ORDER BY number_of_transactions DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4vB6HrIwNpIxLeNirgSx2g.png" /></figure><blockquote><em>Insight:</em> By counting transactions, we can determine which products are most frequently purchased, providing insights into customer preferences.</blockquote><p>3. Average Sales Revenue Per Transaction</p><pre>SELECT <br>  ROUND(AVG(quantity * harga_jual)) AS average_transaction_value<br>FROM warmindo;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/367/1*3I_lI5H90c9kTeb5AfXoow.png" /></figure><blockquote><em>Insight:</em> Knowing the average transaction value helps us gauge the typical purchase amount and can inform pricing and promotional strategies.</blockquote><p>4. Top 5 Products with Highest Total Sales Revenue</p><pre>SELECT <br>  nama_produk, <br>  SUM(quantity * harga_jual) AS total_revenue<br>FROM warmindo <br>GROUP BY nama_produk<br>ORDER BY total_revenue DESC<br>LIMIT 5;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vmtR8IkSPWCZPr7PKKRwQg.png" /></figure><blockquote><em>Insight:</em> This query highlights our best-performing products, which can guide inventory and marketing focus.</blockquote><p>5. Total Revenue and Average Revenue per Transaction for Each Payment Type</p><pre>SELECT <br>  jenis_pembayaran, <br>  SUM(quantity * harga_jual) AS total_revenue, <br>  AVG(quantity * harga_jual) AS average_transaction_value<br>FROM warmindo <br>GROUP BY jenis_pembayaran<br>ORDER BY total_revenue DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eeE9A1EY4tUhzL8B0xzXGg.png" /></figure><blockquote><em>Insight:</em> Understanding the revenue and average transaction value by payment type can inform decisions on payment method promotions and partnerships.</blockquote><p>6. Total Sales per Month</p><pre>SELECT <br>  DATE_FORMAT(tanggal_transaksi, &#39;%Y-%m&#39;) AS month, <br>  SUM(quantity * harga_jual) AS total_sales<br>FROM warmindo <br>GROUP BY month<br>ORDER BY month;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4kLQPgMW-6w4KR8ILlFgPg.png" /></figure><blockquote><em>Insight:</em> Monthly sales trends help us identify seasonality and plan for peak periods.</blockquote><p>7. Most Common Payment Methods</p><pre>SELECT <br>  jenis_pembayaran, <br>  COUNT(*) AS number_of_transactions<br>FROM warmindo <br>GROUP BY jenis_pembayaran<br>ORDER BY number_of_transactions DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rrHWjaNoPl2Jc92KWxwRkw.png" /></figure><blockquote><em>Insight:</em> Knowing the most used payment methods can help streamline the checkout process and improve customer experience.</blockquote><p>8. Top 3 Customers by Total Revenue</p><pre>WITH customer_revenue AS (<br>  SELECT <br>    customer_id, <br>    SUM(quantity * harga_jual) AS total_revenue<br>  FROM warmindo<br>  GROUP BY customer_id<br>),<br>ranked_customers AS (<br>  SELECT <br>    customer_id, <br>    total_revenue, <br>    RANK() OVER (ORDER BY total_revenue DESC) AS rk<br>  FROM customer_revenue<br>)<br>SELECT <br>  customer_id, <br>  total_revenue, <br>  rk<br>FROM ranked_customers<br>WHERE rk &lt;= 3<br>ORDER BY rk;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CjKlGcxguHr39kScWlpfAA.png" /></figure><blockquote><em>Insight:</em> Identifying top customers helps us tailor loyalty programs and personalized offers to boost retention.</blockquote><p>9. Monthly Revenue Trends for the Top 5 Products</p><pre>WITH product_monthly_revenue AS (<br>  SELECT <br>    nama_produk, <br>    DATE_FORMAT(tanggal_transaksi, &#39;%Y-%m&#39;) AS month, <br>    SUM(quantity * harga_jual) AS monthly_revenue<br>  FROM warmindo<br>  GROUP BY nama_produk, month<br>),<br>top_5_products AS (<br>  SELECT nama_produk<br>  FROM product_monthly_revenue<br>  GROUP BY nama_produk<br>  ORDER BY SUM(monthly_revenue) DESC<br>  LIMIT 5<br>)<br>SELECT <br>  pmr.nama_produk, <br>  pmr.month, <br>  pmr.monthly_revenue<br>FROM product_monthly_revenue pmr<br>JOIN top_5_products t5p ON pmr.nama_produk = t5p.nama_produk<br>ORDER BY pmr.nama_produk, pmr.month;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2kkh9zR9Jnf6tNNu7DfjEg.png" /></figure><blockquote><em>Insight:</em> Monitoring monthly revenue trends for top products allows for targeted marketing and stock management.</blockquote><p>10. Average Revenue per Transaction for Each Product</p><pre>WITH revenue_per_transaction AS (<br>  SELECT <br>    nama_produk, <br>    ROUND(AVG(quantity * harga_jual),2) AS avg_revenue_per_transaction<br>  FROM warmindo<br>  GROUP BY nama_produk<br>)<br>SELECT <br>  nama_produk, avg_revenue_per_transaction, <br>  RANK() OVER (ORDER BY avg_revenue_per_transaction DESC) AS rk<br>FROM revenue_per_transaction<br>ORDER BY rk;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6CRqhgLtc5yGW_2_n9zc0w.png" /></figure><blockquote><em>Insight:</em> This helps identify products that yield higher transaction values, aiding in strategic pricing and promotions.</blockquote><h3>Dashboard Visualizations in Looker Studio</h3><p>The insights from the SQL queries were visualized in Looker Studio to create an intuitive dashboard.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wqzP-vDADvfg1kIQBnSS5Q.png" /><figcaption>Visualization 1</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pdNMCg0LeD_FVfWwqFEeXA.png" /><figcaption>Visualization 2</figcaption></figure><h4>Visualizations in Looker Studio</h4><p>To make the insights more accessible, I created an intuitive dashboard in Looker Studio, featuring the following visualizations:</p><p><strong>Total Revenue Each Product:</strong> A bar chart displaying the total revenue for each product, highlighting the top revenue-generating items.</p><p><strong>Top 5 Products by Revenue:</strong> A pie chart showing the top 5 products contributing to the highest sales revenue.</p><p><strong>Monthly Sales Trends:</strong> A line chart depicting the sales trends over the months to understand seasonality and peak periods.</p><p><strong>Average Revenue per Transaction by Product:</strong> A bar chart showing the average revenue per transaction for each product.</p><p><strong>Most Common Payment Method:</strong> A pie chart identifying the distribution of different payment methods used by customers.</p><p><strong>Revenue and Average Transaction Value by Payment Type:</strong> A combined bar and line chart illustrating the total revenue and average transaction value for each payment type.</p><p><strong>Monthly Revenue Trends for Top 5 Products:</strong> A line chart showing monthly revenue trends for the top 5 products.</p><p><strong>Top 3 Customers by Revenue:</strong> A table listing the top 3 customers based on total revenue.</p><h4>Key Insights</h4><p><strong>1. Top Products:</strong> Indomie variants dominate the top revenue-generating products, with “Indomie Rasa Soto Betawi” leading the sales. This suggests a strong customer preference for certain flavors, which can inform inventory decisions.</p><p><strong>2. Sales Trends:</strong> There are significant sales spikes in certain months, indicating peak periods that could be aligned with local events or promotions. Understanding these trends can help in planning marketing strategies and stock levels.</p><p><strong>3. Customer Preferences:</strong> Cash remains the most common payment method, but digital payments (QRIS-GOPAY, QRIS-OVO, etc.) are also widely used, highlighting a trend towards cashless transactions. This indicates a need to support and perhaps promote digital payment options.</p><p><strong>4. Customer Value:</strong> The top 3 customers contribute significantly to the revenue, suggesting a loyal customer base that can be targeted for retention strategies. Personalized marketing efforts can be directed towards these high-value customers to enhance their loyalty.</p><p><strong>5. Payment Methods:</strong> Each payment type has distinct average transaction values, with QRIS-SHOPEEPAY showing the highest average revenue per transaction. This insight can help in understanding the spending behavior associated with different payment methods.</p><h4>Conclusion</h4><p>The Warmindo analysis project provided valuable insights into product performance, sales trends, and customer behavior. These insights can guide strategic decisions such as inventory management, promotional activities, and customer engagement strategies. Visualizing the data in Looker Studio allowed for a clear and effective presentation of the findings, making it easier for stakeholders to understand and act upon the insights.</p><p>Special thanks to Bima (<a href="https://www.instagram.com/ngulik.data?utm_source=ig_web_button_share_sheet&amp;igsh=ZDNlZDc0MzIxNw==">@Ngulikdata</a>) for providing the dataset that made this project possible. By leveraging SQL for data processing and Looker Studio for visualization, I was able to turn raw data into meaningful insights that can drive business growth and customer satisfaction.</p><p>You can check the Visualization in <a href="https://lookerstudio.google.com/s/nRR1B0a7bUw">Here</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=58423c13908d" width="1" height="1" alt=""><hr><p><a href="https://blog.devgenius.io/project-warmindo-sales-analysis-58423c13908d">PROJECT WARMINDO SALES ANALYSIS</a> was originally published in <a href="https://blog.devgenius.io/">Dev Genius</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[HR Employee Data Analysis and Visualization]]></title>
            <link>https://medium.com/@danielsiringoringo052/hr-employee-data-analysis-and-visualization-0839cf345425?source=rss-5000a4cfde44------2</link>
            <guid isPermaLink="false">https://medium.com/p/0839cf345425</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[mysql]]></category>
            <dc:creator><![CDATA[Daniel Ringo]]></dc:creator>
            <pubDate>Tue, 16 Jul 2024 14:00:15 GMT</pubDate>
            <atom:updated>2024-08-03T17:09:18.025Z</atom:updated>
            <content:encoded><![CDATA[<blockquote>Hello Everyone</blockquote><p>Welcome to my presentation on the HR Employee Data Analysis project. Today, we’ll walk you through a comprehensive analysis of our company’s employee data, utilizing SQL for data analysis and Power BI for visualization. Our goal is to provide clear, insightful findings that will help us understand our workforce better and make informed, data-driven decisions.</p><blockquote>Preparation</blockquote><p>In this project, we analyzed HR employee data using SQL for data cleaning and processing, and Power BI for visualization. The goal was to answer key questions about employee demographics, distribution, and turnover rates, and provide insights into the company’s workforce composition</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*KCLuTe29-DWMq6pk.png" /><figcaption>Data Analysis Using MySQL</figcaption></figure><blockquote>Data Visualizations</blockquote><p>We visualized the processed data to provide a clear and comprehensive view of the employee demographics and distribution.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*zUCWBujSvQqu-xaT.png" /><figcaption>Data Visualization Using Power</figcaption></figure><p>We’ll cover various aspects of our employee demographics, including gender and race distribution, age groups, work locations, and more. We’ll also explore key metrics such as turnover rates, tenure distribution, and the changes in our employee count over time. By the end of this presentation, we hope you’ll have a deeper understanding of our HR landscape and the actionable insights that can drive our future strategies.</p><h4>Project Overview</h4><p>In this project, we will use SQL for data analysis and Power BI for visualization to gain insights into the HR employee data. We will focus on answering 11 key questions that provide a comprehensive understanding of the workforce demographics, employment trends, and departmental statistics. Here’s a breakdown of each question and how we will approach it:</p><ol><li>What is the gender breakdown of employees in the company?</li><li>What is the race/ethnicity breakdown of employees in the company?</li><li>What is the age distribution of employees in the company?</li><li>How many employees work at headquarters versus remote locations?</li><li>What is the average length of employment for employees who have been terminated?</li><li>How does the gender distribution vary across departments and job titles?</li><li>What is the distribution of job titles across the company?</li><li>Which department has the highest turnover rate?</li><li>What is the distribution of employees across locations by state?</li><li>How has the company’s employee count changed over time based on hire and term dates?</li><li>What is the tenure distribution for each department?</li></ol><p>Before we dive into the data analysis and visualization, it is crucial to first perform data cleaning. Data cleaning is an essential step to ensure the accuracy and reliability of our analysis. During this process, we will correct any errors, address missing values, and format the data appropriately. This will provide a solid foundation for our subsequent analysis and ensure that our insights and visualizations are based on clean, high-quality data.</p><ol><li>Selecting All Records</li></ol><pre>SELECT * FROM human_resources;</pre><blockquote>This statement retrieves all the records from the human_resources table.</blockquote><p>2. Renaming Column</p><pre>ALTER TABLE human_resources<br>RENAME COLUMN `ï»¿id` TO emp_id;</pre><blockquote>This statement renames the column ï»¿id to emp_id in the human_resources table.</blockquote><p>3. Modifying Column Data Type</p><pre>ALTER TABLE human_resources<br>MODIFY COLUMN emp_id VARCHAR(20);</pre><blockquote>This statement changes the data type of the emp_id column to VARCHAR(20)</blockquote><p>4. Describing Table Structure</p><pre>DESC human_resources;</pre><blockquote>This statement shows the structure of the human_resources table, including column names, types, and attributes.</blockquote><p>5. Selecting Specific Columns</p><pre>SELECT birthdate FROM human_resources;<br>SELECT hire_date FROM human_resources;<br>SELECT termdate FROM human_resources;</pre><blockquote>These statements retrieve the birthdate, hire_date, and termdate columns from the human_resources table.</blockquote><p>6. Updating Birthdate Format</p><pre>UPDATE human_resources<br>SET birthdate = CASE<br> WHEN birthdate LIKE &#39;%/%&#39; THEN date_format(str_to_date(birthdate, &#39;%m/%d/%Y&#39;), &#39;%Y-%m-%d&#39;)<br>    WHEN birthdate LIKE &#39;%-%&#39; THEN date_format(str_to_date(birthdate, &#39;%m-%d-%Y&#39;), &#39;%Y-%m-%d&#39;)<br>    ELSE NULL<br>END;</pre><blockquote>This statement updates the birthdate column to a standard YYYY-MM-DD format. It handles two formats (%m/%d/%Y and %m-%d-%Y).</blockquote><p>7. Modifying Birthdate Column Type</p><pre>ALTER TABLE human_resources<br>MODIFY COLUMN birthdate DATE;</pre><blockquote>This statement changes the data type of the birthdate column to DATE.</blockquote><p>8. Updating Hire Date Format</p><pre>UPDATE human_resources<br>SET hire_date = CASE<br> WHEN hire_date LIKE &#39;%/%&#39; THEN date_format(str_to_date(hire_date, &#39;%m/%d/%Y&#39;), &#39;%Y-%m-%d&#39;)<br>    WHEN hire_date LIKE &#39;%-%&#39; THEN date_format(str_to_date(hire_date, &#39;%m-%d-%Y&#39;), &#39;%Y-%m-%d&#39;)<br>    ELSE NULL<br>END;</pre><blockquote>This statement updates the hire_date column to a standard YYYY-MM-DD format, handling the same two formats as for birthdate</blockquote><p>9. Modifying Hire Date Column Type</p><pre>ALTER TABLE human_resources<br>MODIFY COLUMN hire_date DATE;</pre><blockquote>This statement changes the data type of the hire_date column to DATE</blockquote><p>Now that we have completed the data cleaning process and ensured the accuracy and reliability of our dataset, it’s time to move on to data analysis. During this phase, we will delve into the data to uncover meaningful insights and answer key questions about our HR employee data. This analysis will provide a deeper understanding of various aspects such as employee demographics, turnover rates, and departmental distributions, setting the stage for impactful visualizations and informed decision-making.</p><ol><li>What is the gender breakdown of employees in the company?</li></ol><pre>SELECT gender, COUNT(*) AS count<br>FROM human_resources<br>WHERE age &gt;= 18 AND termdate = &#39;0000-00-00&#39;<br>GROUP BY 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*d1YD3PFL5gsJZJeVlas4GQ.png" /><figcaption>The Table Output</figcaption></figure><blockquote><strong>Insight:</strong> The company has a relatively balanced gender distribution, with 8.9K male employees, 8.1K female employees, and a smaller group of 0.5K non-conforming employees.</blockquote><p>2. What is the race/ethnicity breakdown of employees in the company?</p><pre>SELECT race, COUNT(*) AS count<br>FROM human_resources<br>WHERE age &gt;= 18 AND termdate = &#39;0000-00-00&#39;<br>GROUP BY 1<br>ORDER BY 2 DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/432/1*IbV83Q1VagALvsYXck4R_Q.png" /></figure><blockquote><strong>Insight:</strong> The majority of employees are White (5.0K), followed by those identifying with two or more races (2.9K), Black or African American (2.8K), and Asian (2.8K). Smaller groups include Hispanic or Latino (2.0K), American Indian or Alaska Native (1.1K), and Native Hawaiian or Other Pacific Islander (1.0K)​.</blockquote><p>3. What is the age distribution of employees in the company?</p><pre>SELECT<br> CASE<br>  WHEN age BETWEEN 18 AND 24 THEN &#39;18-24&#39;<br>  WHEN age BETWEEN 25 AND 34 THEN &#39;25-34&#39;<br>  WHEN age BETWEEN 35 AND 44 THEN &#39;35-44&#39;<br>  WHEN age BETWEEN 45 AND 54 THEN &#39;45-54&#39;<br>  WHEN age BETWEEN 55 AND 64 THEN &#39;55-64&#39;<br>        ELSE &#39;65+&#39;<br> END as age_group,<br>    gender,<br>    COUNT(*) AS count<br>FROM human_resources<br>WHERE age &gt;= 18 AND termdate = &#39;0000-00-00&#39;<br>GROUP BY 1, 2<br>ORDER BY 1, 2;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/385/1*r8XoyhUDszQOV77q2mu3RQ.png" /></figure><blockquote><strong>Insight:</strong> The age distribution shows that the largest groups are employees aged 25–34 (4.9K) and 35–44 (5.0K). The youngest group (18–24) has 1.6K employees, and the oldest group (55–64) has 1.1K employees.</blockquote><p>4. How many employees work at headquarters versus remote locations?</p><pre>SELECT <br> location,<br>    COUNT(*) AS count<br>FROM human_resources<br>WHERE age &gt;= 18 AND termdate = &#39;0000-00-00&#39;<br>GROUP BY 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/265/1*ybRhqiVAhvpse046-PsVow.png" /></figure><blockquote><strong>Insight:</strong> A significant majority of employees (74.97%) work at the headquarters, while 25.03% work remotely.</blockquote><p>5. What is the average length of employment for employees who have been terminated?</p><pre>SELECT<br> ROUND(AVG(DATEDIFF(termdate, hire_date))/365,0) as avg_length_employement<br>FROM human_resources<br>WHERE termdate &lt;= CURDATE() AND termdate &lt;&gt; &#39;0000-00-00&#39; AND age &gt;= 18;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/286/1*_WuHXK9gvMfhqS7FbAReXg.png" /></figure><blockquote><strong>Insight:</strong> The average length of employment for terminated employees is around 8 years</blockquote><p>6. How does the gender distribution vary across departments and job titles?</p><pre>SELECT<br> department,<br>    gender,<br>    COUNT(*) AS count<br>FROM human_resources<br>WHERE age &gt;= 18 AND termdate = &#39;0000-00-00&#39;<br>GROUP BY 1, 2<br>ORDER BY 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/739/1*IKB_-zUyI5KCuonktwiKNg.png" /></figure><blockquote><strong>Insight:</strong> Gender distribution is fairly balanced across departments, though there are generally more male than female employees. For example, in the Engineering department, there are more male employees, while the Marketing department has a higher turnover rate and a more balanced gender distribution.</blockquote><p>7. What is the distribution of job titles across the company?</p><pre>SELECT<br> jobtitle,<br>    COUNT(*) AS count<br>FROM human_resources<br>WHERE age &gt;= 18 AND termdate = &#39;0000-00-00&#39;<br>GROUP BY 1<br>ORDER BY 1 DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/702/1*gmSiAD19PzV6E74a5-R8VA.png" /></figure><blockquote><strong>Insight:</strong> The distribution of job titles shows that the most common roles are in Engineering, Sales, and Human Resources. Departments like Marketing and Training have fewer employees.</blockquote><p>8. Which department has the highest turnover rate?</p><pre>SELECT<br> department,<br>    total_count,<br>    terminated_count,<br>    terminated_count/total_count as termination_rate<br>FROM (<br> SELECT<br>  department,<br>        COUNT(*) total_count,<br>        SUM(CASE WHEN termdate &lt;&gt; &#39;0000-00-00&#39; AND termdate &lt;= CURDATE() THEN 1 ELSE 0 END) AS terminated_count<br> FROM human_resources<br>    WHERE age &gt;= 18<br>    GROUP BY 1) AS derived_table<br>ORDER BY termination_rate DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/958/1*YTn5GHS90I3EH6l98BsZUQ.png" /></figure><blockquote><strong>Insight:</strong> The Marketing department has the highest turnover rate, followed by the Training department. Departments with the lowest turnover rates include Research and Development, Support, and Legal.</blockquote><p>9. What is the distribution of employees across locations by state?</p><pre>SELECT<br> location_state,<br>    COUNT(*) AS count<br>FROM human_resources<br>WHERE age &gt;= 18 AND termdate = &#39;0000-00-00&#39;<br>GROUP BY 1<br>ORDER BY 2 DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/403/1*hEagOhY-wsRGdvoM4B5lBg.png" /></figure><blockquote><strong>Insight:</strong> The majority of employees are located in Ohio, followed by Pennsylvania, Illinois, Michigan, Indiana, Kentucky, and Wisconsin.</blockquote><p>10. How has the company’s employee count changed over time based on hire and term dates?</p><pre>SELECT<br> years,<br>    hires,<br>    terminations,<br>    hires-terminations as net_change,<br>    ROUND((hires-terminations)/hires *100, 2) as net_change_percent<br>FROM (<br> SELECT<br>  EXTRACT(YEAR FROM hire_date) as years,<br>        COUNT(*) AS hires,<br>        SUM(CASE WHEN termdate &lt;&gt; &#39;0000-00-00&#39; AND termdate &lt;= CURDATE() THEN 1 ELSE 0 END) AS terminations<br> FROM human_resources<br> WHERE age &gt;= 18<br>    GROUP BY 1<br>    ) as derived_table<br>ORDER BY years ASC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/654/1*HAvQpA61pcHHhmS82RSJxA.png" /></figure><blockquote><strong>Insight:</strong> The company’s employee count has steadily increased over the years from 2000 to 2020, indicating growth and expansion.</blockquote><p>11. What is the tenure distribution for each department?</p><pre>SELECT<br> department,<br>    ROUND(AVG(DATEDIFF(termdate, hire_date)/365)) AS avg_tenure<br>FROM human_resources<br>WHERE termdate &lt;= CURDATE() AND termdate &lt;&gt; &#39;0000-00-00&#39; AND age &gt;= 18<br>GROUP BY 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/498/1*hEZplTrpzGJt_mrTW8R0HQ.png" /></figure><blockquote><strong>Insight:</strong> The average tenure for employees across departments is about 8 years. Departments like Legal and Auditing have the highest average tenure, while Services, Sales, and Marketing have the lowest.</blockquote><h4>Data Visualization</h4><p>Having completed the data cleaning and analysis phases, we have gained valuable insights into our HR employee data. Now, it is time to move on to the data visualization stage. Through data visualization, we will transform our analytical findings into clear and compelling visual representations. These visualizations will help us better understand the data, identify patterns, and communicate our insights effectively.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LTi5V7mKrj6F88-oW5GkfQ.png" /><figcaption><strong>Visualization 1</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*N1ZuLheVpqlQOXwGzh0tOw.png" /><figcaption><strong>Visualization 2</strong></figcaption></figure><p>Using Power BI, we transformed these analyses into clear, impactful visualizations that highlight the key findings. Our visualizations include pie charts, bar charts, histograms, maps, and more, providing a comprehensive view of our employee data.</p><h4>Summary of Findings</h4><ul><li>The gender distribution is relatively balanced, with a slight majority of male employees.</li><li>White employees are the most dominant racial group, while Native Hawaiian and American Indian employees are the least represented.</li><li>The age distribution shows a large number of employees in the 25–34 and 35–44 age groups.</li><li>Most employees work at the headquarters rather than remotely.</li><li>The average length of employment for terminated employees is around 7 years.</li><li>The Marketing department has the highest turnover rate, while Research and Development, Support, and Legal have the lowest.</li><li>The majority of employees are from Ohio, and the employee count has increased over time.</li><li>The average tenure for each department is about 8 years, with Legal and Auditing having the highest tenure.</li></ul><h4>Conclusion</h4><p>In summary, our HR Employee Data Analysis project has provided valuable insights into the demographics and dynamics of our workforce. We found that our gender distribution is relatively balanced, with a slight majority of male employees. The racial diversity shows a dominant White population, while the age distribution highlights a young workforce, particularly in the 25–34 and 35–44 age groups.</p><p>Most of our employees are located at the headquarters, with a smaller portion working remotely. The average tenure for terminated employees is around 7 years, and the Marketing department has the highest turnover rate. We also observed that the majority of our employees are from Ohio, and the employee count has increased over the years, indicating growth.</p><p>These insights will help us make informed decisions to improve employee retention, enhance diversity and inclusion, and support our strategic growth initiatives. We hope this presentation has provided you with a clear understanding of our HR landscape and the valuable insights we can derive from our data.</p><blockquote>Thank you for your attention</blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=0839cf345425" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[E-commerce Performance Analytics: Cross-Country Sales and Shop Efficiency Dashboard]]></title>
            <link>https://blog.devgenius.io/peoject-analysis-data-shopee-using-sql-in-google-big-query-1c151a5bb9d4?source=rss-5000a4cfde44------2</link>
            <guid isPermaLink="false">https://medium.com/p/1c151a5bb9d4</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Daniel Ringo]]></dc:creator>
            <pubDate>Mon, 15 Jul 2024 18:17:57 GMT</pubDate>
            <atom:updated>2024-08-18T14:04:39.821Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/640/0*WQD82M1EwBpqStvq" /></figure><blockquote>Hello Everyone</blockquote><p>In the process of creating an analysis for e-commerce data, I decided to use a pseudonym, “Sepi E-commerce,” instead of the real name of the platform, which is Shopee. This choice was made to ensure that the actual data from Shopee is not directly associated with the analysis, thereby maintaining the confidentiality of the source.</p><p>By using “Sepi E-commerce,” I can effectively demonstrate my analytical skills and showcase the insights derived from the dataset without risking any potential misuse or confusion with the actual Shopee platform. This approach also aligns with best practices in data privacy, where anonymizing or masking real data is often necessary to protect sensitive information.</p><p>In this analysis, “Sepi E-commerce” represents a fictional company, but the insights and methods applied are fully relevant and transferable to real-world scenarios, including those involving well-known platforms like Shopee. By maintaining this distinction, I ensure that the integrity of the analysis is preserved while safeguarding the original data source.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CR8dbnoQTn78D2zmYFl9VQ.png" /><figcaption><strong>Google Big Query</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/750/0*4vXKhJEssDahsfGQ.jpg" /><figcaption><strong>Google Looker Studio</strong></figcaption></figure><p>Project Workflow Overview:</p><ol><li>Data Source: You began this project by obtaining an e-commerce dataset from Kaggle. This dataset likely contained valuable information about orders, user behavior, and shop performance across multiple countries.</li><li>Data Import to BigQuery: The Kaggle dataset was imported into Google BigQuery, a powerful cloud-based data warehouse. This step allowed you to leverage BigQuery’s robust capabilities for handling large datasets and performing complex queries efficiently.</li><li>Data Structure in BigQuery: In BigQuery, you organized the data into several tables within the “Project_SQL_Portfolio” project:</li></ol><ul><li>order_tab: Contains order-related information</li><li>performance_tab: Stores performance metrics</li><li>user_tab: Holds user-related data</li></ul><p>4. Data Manipulation and Processing: Using BigQuery’s SQL interface, you performed various data manipulation and processing tasks. This likely involved:</p><ul><li>Cleaning and standardizing the data</li><li>Joining tables to combine relevant information</li><li>Aggregating data to create meaningful summaries</li><li>Calculating key metrics like conversion rates and GMV</li></ul><p>5. Creating Optimized Views: To streamline the visualization process, you created two views in BigQuery:</p><ul><li>V_Shop_by_Country_: Aggregates shop performance data by country</li><li>v_monthly_sales: Summarizes monthly sales data These views encapsulate complex SQL queries, making it easier to access processed data for visualization.</li></ul><p>6. Connecting to Looker Studio: With the data processed and views created in BigQuery, you then connected these data sources to Looker Studio (formerly Google Data Studio).</p><p>7. Data Visualization in Looker Studio: Using Looker Studio, you created interactive dashboards and visualizations based on the views from BigQuery. This likely included:</p><ul><li>Charts showing monthly GMV trends</li><li>Graphs depicting customer segmentation</li><li>Maps illustrating geographical distribution of orders</li><li>Tables highlighting top-performing countries and shops</li></ul><p>8. Dashboard Creation: You developed at least two dashboard pages in Looker Studio:</p><ul><li>The first page focuses on overall sales trends and customer behavior</li><li>The second page delves into shop performance and conversion rates by country</li></ul><p>9. Iterative Process: Throughout the project, you likely iterated between BigQuery and Looker Studio, refining your SQL queries and adjusting your visualizations to best represent the insights from the data.</p><p>By using BigQuery for data manipulation and processing, followed by Looker Studio for visualization, you created a powerful, end-to-end analytics solution. This approach allowed you to handle large volumes of data efficiently, perform complex analyses, and create insightful, interactive visualizations to communicate your findings effectively.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/376/1*CCsI2SQYb5pFCiR5b1ksog.png" /><figcaption>Data Table at Google Big Query</figcaption></figure><blockquote>This image showcases a dataset structure that has been extracted from Kaggle and imported into Google BigQuery for advanced analysis and visualization. Let me provide a detailed explanation of what we’re seeing:</blockquote><p>1. Data Source:<br>The original dataset was sourced from Kaggle, a popular platform for data science and machine learning projects. Kaggle often hosts datasets from various domains, including e-commerce, which appears to be the focus of this particular dataset.</p><p>2. Google BigQuery Integration:<br>The data has been successfully imported into Google BigQuery, a fully-managed, serverless data warehouse that allows for fast SQL queries across large datasets. This integration enables powerful data processing and analysis capabilities.</p><p>3. Project Structure:<br>We can see a project named “Project_SQL_Portfolio” which contains several tables and views:</p><p>a. Raw Data Tables:<br> — order_tab: Likely contains information about individual orders<br> — performance_tab: Probably includes performance metrics for shops or products<br> — user_tab: Presumably contains user-related data</p><p>b. Created Views:<br> — V_Shop_by_Country_: A view that likely aggregates shop data by country<br> — v_monthly_sales: A view that presumably summarizes monthly sales data</p><p>4. Views for Visualization:<br>The two views (V_Shop_by_Country_ and v_monthly_sales) have been specifically created to support visualization efforts. These views are likely the result of complex SQL queries that aggregate and transform the raw data into a format that’s optimized for creating charts and dashboards.</p><p>5. Visualization Preparation:<br>By creating these views, the data analyst has prepared the data for easy integration with visualization tools like Looker Studio (formerly Google Data Studio). This approach allows for efficient creation of interactive dashboards and reports without the need to rerun complex queries each time the visualization is loaded.</p><p>6. Data Analysis Workflow:<br>This setup demonstrates a professional data analysis workflow:<br> 1. Sourcing data from Kaggle<br> 2. Importing it into a powerful data warehouse (BigQuery)<br> 3. Processing and transforming the data using SQL<br> 4. Creating optimized views for visualization<br> 5. Connecting these views to a visualization tool for dashboard creation</p><p>This structure allows for scalable, efficient, and reproducible data analysis, making it easier to derive insights and make data-driven decisions based on the e-commerce data originally sourced from Kaggle.</p><h4>Data Processing and Data Manipulation with Big Query:</h4><ol><li>Monthly Sales Performance</li></ol><pre>WITH customer_orders AS (<br>  SELECT <br>    o.buyerid AS buyer_orders,<br>    COUNT(o.orderid) AS total_orders,<br>    CASE <br>      WHEN COUNT(o.orderid) = 1 THEN &#39;New Customer&#39;<br>      WHEN COUNT(o.orderid) BETWEEN 2 AND 5 THEN &#39;Returning Customer&#39;<br>      ELSE &#39;Loyal Customer&#39;<br>    END AS customer_segment<br>  FROM `Project_SQL_Portfolio.order_tab` o<br>  GROUP BY 1<br>),<br><br>monthly_sales AS (<br>  SELECT <br>    PARSE_DATE(&#39;%Y-%m&#39;, FORMAT_DATE(&#39;%Y-%m&#39;, o.order_time)) AS order_month,<br>    co.total_orders,<br>    co.customer_segment,<br>    SUM(o.gmv) AS total_gmv<br>  FROM customer_orders as co <br>  INNER JOIN `Project_SQL_Portfolio.order_tab` o<br>    ON co.buyer_orders = o.buyerid<br>  GROUP BY order_month, co.total_orders, co.customer_segment<br>)<br><br>SELECT * FROM monthly_sales;</pre><p>Query Result Table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kCUBmUtDEzBwQzab8HDbdw.png" /></figure><p>This query represents a crucial data processing step performed in Google BigQuery, preparing the groundwork for visualizations in the first page of a Looker Studio dashboard. Let’s break down the query and its purpose:</p><p>1. Customer Segmentation:<br>The query begins by creating a Common Table Expression (CTE) named ‘customer_orders’. This CTE categorizes customers based on their order frequency:<br>- New Customer: 1 order<br>- Returning Customer: 2–5 orders<br>- Loyal Customer: More than 5 orders</p><p>2. Monthly Sales Performance:<br>The second CTE, ‘monthly_sales’, combines the customer segmentation data with order information to calculate monthly sales performance. It includes:<br>- Order month<br>- Total orders per customer<br>- Customer segment<br>- Total Gross Merchandise Value (GMV)</p><p>3. Final Output:<br>The main SELECT statement retrieves all columns from the ‘monthly_sales’ CTE, providing a comprehensive view of monthly sales performance by customer segment.</p><p>This query, executed in Google BigQuery, will be saved as a view. This view will serve as the data source for visualizations in Looker Studio, enabling the creation of insightful charts and graphs on the dashboard’s first page. By using a BigQuery view as the data source, the dashboard can leverage BigQuery’s processing power and ensure that visualizations are always based on the most up-to-date data.</p><p>The resulting dataset will allow for various analyses, such as:<br>- Tracking monthly GMV trends<br>- Comparing performance across customer segments<br>- Identifying patterns in order frequency<br>- Assessing the impact of customer loyalty on sales</p><p>By processing this data in BigQuery and visualizing it in Looker Studio, stakeholders can gain valuable insights into customer behavior and sales performance, facilitating data-driven decision-making and strategy formulation.</p><p>2. Top Shops by Conversion Rate</p><pre>WITH monthly_data AS (<br>  SELECT <br>  o.shopid,<br>    u.country,<br>    FORMAT_DATE(&#39;%Y-%m&#39;, o.order_time) AS month,<br>    SUM(o.gmv) AS total_gmv,<br>    COUNT(o.orderid) AS total_orders<br>  FROM `Project_SQL_Portfolio.order_tab` o<br>  JOIN `Project_SQL_Portfolio.user_tab` u ON o.buyerid = u.buyerid<br>  GROUP BY o.shopid,u.country, month<br>),<br><br><br>shop_conversion AS (<br>  SELECT <br>    p.shopid,<br>    u.country,<br>    COUNT(DISTINCT md.total_orders) AS total_orders,<br>    SUM(p.total_clicks) AS total_clicks,<br>    ROUND(COUNT(DISTINCT md.total_orders) / NULLIF(SUM(p.total_clicks), 0) * 100, 2) AS conversion_rate<br>  FROM `Project_SQL_Portfolio.performance_tab` p<br>  LEFT JOIN monthly_data as md<br>  ON p.shopid = md.shopid<br>  JOIN `Project_SQL_Portfolio.user_tab` as u<br>    ON md.shopid = u.shopid<br>  WHERE u.country != &#39;SZ&#39;<br>  GROUP BY p.shopid, u.country<br>  ORDER BY conversion_rate DESC<br>)<br><br>SELECT * FROM shop_conversion;</pre><p>Query Result Table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lBbHukuSv-QCeIITgfvhBg.png" /></figure><p>This query represents an essential data processing step performed in Google BigQuery, preparing the foundation for visualizations on the second page of a Looker Studio dashboard. Let’s break down the query and its purpose:</p><p>1. Monthly Data:<br>The query begins with a Common Table Expression (CTE) named ‘monthly_data’. This CTE aggregates order information by shop, country, and month, calculating total GMV and order count.</p><p>2. Shop Performance:<br>The second CTE, ‘shop_conversion’, focuses on shop performance metrics. It calculates:<br>- Total orders<br>- Total clicks<br>- Conversion rate (orders divided by clicks, expressed as a percentage)</p><p>This CTE also joins with user data to include country information and excludes data from the country ‘SZ’.</p><p>3. Final Output:<br>The main SELECT statement retrieves all columns from the ‘shop_conversion’ CTE, providing a comprehensive view of shop performance metrics, including conversion rates.</p><p>This query, executed in Google BigQuery, will be saved as a view. This view will serve as the data source for visualizations in Looker Studio, enabling the creation of insightful charts and graphs on the dashboard’s second page. By using a BigQuery view as the data source, the dashboard can leverage BigQuery’s processing power and ensure that visualizations are always based on the most up-to-date data.</p><p>The resulting dataset will allow for various analyses, such as:<br>- Comparing shop performance across different countries<br>- Identifying top-performing shops based on conversion rates<br>- Analyzing the relationship between clicks and orders<br>- Tracking shop performance over time</p><p>By processing this data in BigQuery and visualizing it in Looker Studio, stakeholders can gain valuable insights into shop performance and conversion rates across different countries. This information can be used to identify best practices, optimize marketing strategies, and improve overall e-commerce performance.</p><p>The use of BigQuery for data processing and Looker Studio for visualization creates a powerful, scalable solution for analyzing and presenting complex e-commerce data, facilitating data-driven decision-making and strategy formulation.</p><h4>Data Visualization with Looker Studio:</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bGH7N9FdIKiPg-j0ANLMBg.png" /><figcaption><strong>Visualization 1</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*545fvsXDgEu8NY9Rd0qstw.png" /><figcaption><strong>Visualization 2</strong></figcaption></figure><h4>Dashboard Analysis:</h4><ol><li>Monthly GMV by Customer Segment: This chart shows the Gross Merchandise Value (GMV) trends for Loyal and Returning customers over several months in 2020. Loyal customers consistently generate significantly higher GMV compared to returning customers. The GMV for loyal customers ranges from about 63,000 to 76,300 (currency units not specified), while returning customers generate between 21,000 to 25,700.</li><li>Monthly Orders: This graph depicts the total number of orders per month throughout 2020. While specific values aren’t visible, we can see a general upward trend in order volume from January to December 2020.</li><li>Customer Segment Distribution: A pie chart shows that loyal customers make up 94.9% of the customer base, while returning customers account for only 5.1%. This suggests a very high customer retention rate.</li><li>Total Clicks and Orders by Country: This chart compares total clicks and orders across four countries: Philippines (PH), Malaysia (MY), Indonesia (ID), and Singapore (SG).</li></ol><ul><li>SG, ID, and MY have similar click volumes (around 15.5–16.1 million) and order volumes (about 5,000–5,100).</li><li>PH has notably lower clicks (7.8 million) and orders (3,800) compared to the other countries.</li></ul><ol><li>Proportion of Orders by Country: The pie chart shows a relatively even distribution of orders among countries:</li></ol><ul><li>Indonesia (ID): 26.8%</li><li>Singapore (SG): 26.6%</li><li>Malaysia (MY): 26.4%</li><li>Philippines (PH): 20.2%</li></ul><ol><li>Top Performing Countries by Total Clicks: This table provides more detailed information on clicks, orders, and conversion rates:</li></ol><ul><li>SG: 16,059,040 clicks, 5,030 orders, 6.04% conversion rate</li><li>ID: 16,037,624 clicks, 5,080 orders, 6.08% conversion rate</li><li>MY: 15,549,445 clicks, 5,001 orders, 6.07% conversion rate</li><li>PH: 7,758,278 clicks, 3,821 orders, 8.77% conversion rate</li></ul><p>Insights:</p><ol><li>Customer Loyalty: The high percentage of loyal customers (94.9%) indicates strong customer retention. These loyal customers also contribute significantly more to GMV than returning customers.</li><li>Market Performance: Singapore, Indonesia, and Malaysia show similar performance in terms of clicks and orders, while the Philippines lags behind in both metrics.</li><li>Conversion Rates: Despite having fewer clicks and orders, the Philippines has the highest conversion rate at 8.77%, significantly higher than the other countries which hover around 6%.</li><li>Seasonal Trends: There appears to be a slight upward trend in both GMV and order volume throughout 2020, which could indicate business growth or seasonal patterns.</li></ol><p>Conclusions:</p><ol><li>The business has been highly successful in cultivating customer loyalty, which is driving the majority of its GMV. This suggests that retention strategies have been effective.</li><li>There’s an opportunity to convert more of the returning customers into loyal customers, given the current imbalance in customer segments.</li><li>The Philippines market shows potential for growth. While it has lower overall engagement, its high conversion rate suggests that targeted marketing could yield significant returns.</li><li>Singapore, Indonesia, and Malaysia are performing consistently, but there may be room to improve their conversion rates by learning from successful strategies in the Philippines.</li><li>The overall upward trend in orders throughout 2020 is positive, possibly indicating business growth despite the global challenges of that year.</li></ol><p>To further improve performance, the company could focus on:</p><ul><li>Investigating and replicating the factors contributing to the high conversion rate in the Philippines across other markets.</li><li>Developing strategies to convert more returning customers into loyal customers.</li><li>Exploring ways to increase engagement (clicks) in the Philippines to capitalize on the high conversion rate.</li><li>Continuing to nurture and reward loyal customers while also attracting new customers to maintain growth.</li></ul><p>Connect with me in <a href="https://www.linkedin.com/in/DanielSiringoringo">Linkedin</a></p><p>You Can Check Data Visualization in Here: <a href="https://lookerstudio.google.com/reporting/48bcc2c7-9172-4496-90ff-3e27928268af">Looker Studio</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1c151a5bb9d4" width="1" height="1" alt=""><hr><p><a href="https://blog.devgenius.io/peoject-analysis-data-shopee-using-sql-in-google-big-query-1c151a5bb9d4">E-commerce Performance Analytics: Cross-Country Sales and Shop Efficiency Dashboard</a> was originally published in <a href="https://blog.devgenius.io/">Dev Genius</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[DATA ANALYSIS FUNDAMENTAL]]></title>
            <link>https://medium.com/@danielsiringoringo052/data-analysis-fundamental-e5a820a87078?source=rss-5000a4cfde44------2</link>
            <guid isPermaLink="false">https://medium.com/p/e5a820a87078</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-analyst]]></category>
            <category><![CDATA[data-preprocessing]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Daniel Ringo]]></dc:creator>
            <pubDate>Mon, 15 Jul 2024 16:42:04 GMT</pubDate>
            <atom:updated>2024-07-15T16:42:04.129Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*wAMeXKPy8wYtcZSV" /><figcaption>Photo by <a href="https://unsplash.com/@kmuza?utm_source=medium&amp;utm_medium=referral">Carlos Muza</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h4>The Fundamental of Data Analisis</h4><p>Ada 4 poin yang akan dibahas dari fundamental data analisis:</p><ol><li><strong>The Fundamental of Data Analysis</strong></li><li><strong>Data Analysis in Business</strong></li><li><strong>Data Analysis Process</strong></li><li><strong>Data Analysis toolset</strong></li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*JzidrUqzUqhOdXqK" /><figcaption>Photo by <a href="https://unsplash.com/@lukechesser?utm_source=medium&amp;utm_medium=referral">Luke Chesser</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h4>I. Fundamental Data Analysis</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*9PgK-pZKymGiqIwU" /><figcaption>Photo by <a href="https://unsplash.com/@hostreviews?utm_source=medium&amp;utm_medium=referral">Stephen Phillips - Hostreviews.co.uk</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><ol><li>Apa Definisi Data Analysis?</li></ol><p><strong>Data Analysis</strong> adalah suatu proses pengumpulan, pengolahan, transaformasi, penyajian dan pemodelan data sehingga dapat mengetahui pola serta informasi yang terdapat dalam data.</p><p>2. Tahapan Data Analysis</p><ul><li><strong>Analisis Deskriptif:</strong> Menggambarkan karateristik dan pola data</li><li><strong>Analisis Diagnostik:</strong> Mengidentifikasi penyebab atau faktor yang mempengaruhi suatu permasalahan atau kejadian tertentu.</li><li><strong>Analisis Prediktif:</strong> Membuat prediksi tentang kejadian atau hasil masa depan berdasarkan data historis atau data saat ini.</li><li><strong>Analisis Preskriptif:</strong> Memberikan rekomendasi atau saran tindakan yang harus diambil untuk mencapai tujuan tertentu.</li></ul><p>3. Penerapan Tahapan Data Analysis</p><ul><li><strong>Analisis Deskriptif:</strong> Apa upaya yang dilakukan masyarakat untuk mempertahankan perekonomian mereka ditengah pandemi covid-19? hal ini dapat dijawab oleh data yang menunjukkan bahwa masyarakat yang berjualan online meningkat dari sebelumnya.</li><li><strong>Analisis Diagnostik:</strong> Dalam bisnis, analisis diagnostik dapat membantu manajer dalam mengidentifikasi penyebab kinerja yang buruk menemukan solusi untuk meningkatkan efisiensi operasional dan meningkatkan kepuasan pelanggan.</li><li><strong>Analisis Prediktif:</strong> Hal ini bisa membantu manajer dalam mengidentifikasi peluang pasar baru, mengoptimalkan dan memperkirakan permintaan produk.</li><li><strong>Analisis Preskriptif:</strong> Contoh dalam kesehatan dapat menentukan pengobatan yang paling efektif untuk pasien dan mengoptimalkan penggunaan sumber daya medis.</li></ul><h4>II. Data Analysis in Business</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*WmEu9XD0fqVfYLKF" /><figcaption>Photo by <a href="https://unsplash.com/@adeolueletu?utm_source=medium&amp;utm_medium=referral">Adeolu Eletu</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><ol><li>Manfaat Data Analysis Dalam Bisinis:</li></ol><ul><li>Mengoptimalkan keputusan bisnis</li><li>Meningkatkan efisiensi operasional</li><li>Meningkatkan kepuasan pelanggan</li><li>Meningkatkan inovasi produk dan layanan</li></ul><p>2. Tantangan 4V dari data analysis:</p><ul><li><strong>Volume</strong>: Volume mengacu pada jumlah besar data yang dihasilkan dan dikumpulkan setiap harinya.</li><li><strong>Variety: </strong>Keragaman jenis data yang dikumpulkan</li><li><strong>Velocity:</strong> Mengacu pada kecepatan data yang dihasilkan dan dikumpulkan setiap harinya.</li><li><strong>Veracity:</strong> Mengacu pada keakuratan dan keandalan data.</li></ul><h4><strong>III. Process Data Analysis</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*K__DpMvWJwF-N0NZ" /><figcaption>Photo by <a href="https://unsplash.com/@kaleidico?utm_source=medium&amp;utm_medium=referral">Kaleidico</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><ol><li>CRISP-DM <em>(Cross Industry Standard Process for Data Mining)</em></li></ol><p>Metode ini melibatkan 6 tahap utama, yaitu:</p><ul><li><strong>Business Understanding:</strong> Memahami masalah bisnis yang akan diselesaikan</li><li><strong>Data Understanding:</strong> Memahami data yang tersedia</li><li><strong>Data Preparation:</strong> Mempersiapkan data yang akan di analisis</li><li><strong>Modeling:</strong> Mengembangkan model analisis</li><li><strong>Evaluation:</strong> Mengevaluisi kinerja model analisis</li><li><strong>Deployment:</strong> Implementasikan hasil analisis dan model ke dalam lingkungan bisnis.</li></ul><h4>IV. Data Analysis Toolset</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*5nkT3Fxt-6fQ5NTP" /><figcaption>Photo by <a href="https://unsplash.com/@olloweb?utm_source=medium&amp;utm_medium=referral">Agence Olloweb</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><ul><li><strong>SQL (Structured Query Language):</strong> SQL adalah bahasa yang digunakan untuk mengelola dan mengambil data dari database relasional, diantaranya terdapat: <em>Google Big Query, MySQL, PostgreSQL dan SQL Server.</em></li><li><strong>Bahasa Pemrogaraman:</strong> Seorang data analis harus memiliki pengetahuan dasar tentang bahasa pemrograman seperti: <em>Python, R, atau Matplotlib</em> untuk melakukan analisis data yang lebih canggih.</li><li><strong>Data Visualization:</strong> Data visualization tools seperti <em>Tableau, Power BI, Google Looker Studio dan Metabase </em>dapat membantu seorang data analis untuk menghasilkan grafik dan visulaisasi data yang dapat dipahami oleh orang awam.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e5a820a87078" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>