<?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 Kingsley Okoye on Medium]]></title>
        <description><![CDATA[Stories by Kingsley Okoye on Medium]]></description>
        <link>https://medium.com/@exceldispensing?source=rss-f1bbe5fa9b1b------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*SogEEgISmIWX7OFtWkQ7vQ.png</url>
            <title>Stories by Kingsley Okoye on Medium</title>
            <link>https://medium.com/@exceldispensing?source=rss-f1bbe5fa9b1b------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Mon, 16 Nov 2026 09:49:54 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@exceldispensing/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[How to use AI to optimize your marketing agency’s performance using data. (Simple one time set up)]]></title>
            <link>https://medium.com/@exceldispensing/how-to-use-ai-to-optimize-your-marketing-agencys-performance-using-data-simple-one-time-set-up-1605585d2785?source=rss-f1bbe5fa9b1b------2</link>
            <guid isPermaLink="false">https://medium.com/p/1605585d2785</guid>
            <category><![CDATA[google-ads]]></category>
            <category><![CDATA[gen-ai-for-business]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[web-analytics]]></category>
            <category><![CDATA[ai-agent]]></category>
            <dc:creator><![CDATA[Kingsley Okoye]]></dc:creator>
            <pubDate>Mon, 08 Jun 2026 19:16:00 GMT</pubDate>
            <atom:updated>2026-06-08T19:16:00.883Z</atom:updated>
            <content:encoded><![CDATA[<p>These are the kind of beneficial business insights I get for my clients when I set up AI to investigate and provide recommendations.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rD552gcfeccgezNBzMFuBQ.png" /></figure><p><strong>Recommendations</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VKZKOsCX3Cslqd5RDnJTlw.png" /></figure><p>By the end of the write up, you will be able to go back to your project, business or agency data and discover details you have probably never discovered.</p><p>Here are the steps you’ll need to take:</p><p><strong>Step 1: Connect with your data and structure your data</strong></p><p>AI needs structured data to function with precision, the more structured your data is, the easier it is for AI to analyze and provide recommendations and this is where the challenge lies for most business owners, marketers, marketing strategists and agencies.</p><p>While this can sometimes be complicated, if overcome, you are one step away from getting highly valuable insights and recommendations.</p><p>The challenge is most businesses, marketing strategists and agencies have access to so much data, while some data is easy to access, some are difficult to access. While some are also structured, lots are also unstructured and usually need a lot of transformation.</p><p>For context, here is the data I presented to AI (Gemini specifically).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Bt_czxCz28NDKpCZr5pTDA.png" /></figure><p>This table above shows revenue, ad spend, profitability and much more about an shopify online store and I asked AI to provide me with the most important insights about this data and also provide me with recommendations.</p><p>This table looks straight forward but it cam from 4 different data sources.</p><ol><li>Shopify raw data.</li><li>Google ads data</li><li>Business product cost price and shipping data</li><li>Other business operating expense data</li></ol><p>The 4 different data sources had to be blended together to one extensive table that provides specific details to AI.</p><p>This same logic can be applied to much larger projects, many agencies deal with clients having much more data sources.</p><p>Here are the insights are I got from AI.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ic_yWJPzzm8h4wDVXN6ogA.jpeg" /></figure><p>Note that you can find out more about me by checking out &gt;&gt; <a href="http://datascienceportfol.io/kingsleyokoye218"><strong>my portfolio</strong></a>.</p><p>You can also connect with me on &gt;&gt; <a href="http://www.linkedin.com/in/ifeanyi-okoye-a857911ba"><strong>Linked in</strong></a></p><p>Back to the artcile.</p><p>Now because I just don’t like to blindly listen to AI, I did some vetting myself and found all information to be correct.</p><p>For this project I used <strong>Big Query a data warehouse</strong> platform within <strong>Google cloud platform</strong> that uses <strong>Gemini AI models</strong>.</p><p><strong>Step 1</strong>: Collect your data and get it into Big Query. I created an article explaining how this can be done. <a href="https://medium.com/@exceldispensing/how-to-load-data-into-google-big-query-using-python-in-10-minutes-1aab32c1c4e8"><strong>How to import data in Big Query.</strong></a></p><p><strong>Step 2</strong>: Structure your data</p><p>As explained earlier, AI loves structured data so we must ensure to transform our data and make it structured before any form of AI implementation.</p><p>For our example, I will be using the sample google analytics 4 data events data available for free via the Big Query interphase</p><p><strong>ga4_obfuscated_sample_ecommerce</strong></p><p>This is the original data structure before any form of transformation.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3dc6r_7_Td9dXXzvPIUGFg.png" /></figure><p>Data structure after transformation</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Y10ETUY3e2AZVonQ6Jvmkg.png" /></figure><p><strong>AI request</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/870/1*_NZaAJ0OlRA4hNtVUUVJCw.png" /></figure><p><strong>AI key insights</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/891/1*0Rrs-QBb4aNXkvfm9Y1Ehw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/814/1*_tUzXErdIAUtMxEhKt_VEg.png" /></figure><p><strong>Further AI recommendation about optimizing conversions</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/813/1*7fTKx5-V7LhoO8wDyBX-jQ.png" /></figure><p>Note that you can find out more about me by checking out &gt;&gt; <a href="http://datascienceportfol.io/kingsleyokoye218"><strong>my portfolio</strong></a>.</p><p>You can also connect with me on &gt;&gt; <a href="http://www.linkedin.com/in/ifeanyi-okoye-a857911ba"><strong>Linked in</strong></a></p><p>Let’s work on something a little more complicated</p><p>A fictional company has it’s data spread across multiple sources, internal employee data, sales data, inventory, advertisement and much more.</p><p>The goal is to enable AI provide real time recommendations as well as provide detailed insights.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/283/1*0lL72g6AU0Hj_CzgHbw9iA.png" /></figure><p>The first thing I did was combining all these data into on properly structure table. As said earlier, this is very important.</p><p>This is what the final table looks like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1002/1*gdgkoKU_ThOoMuGqeHuJow.png" /></figure><p>Here I ask AI detailed questions about the historical data and I am able to get detailed insights immediately.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/577/1*LWvM9H0WK_Dw1ZbCq53Uvw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/727/1*ibPWf4KNtgudnnEMKJPKsw.png" /><figcaption>AI response</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/745/1*Dcrys3aI2cAwMSC0MFqCvA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/647/1*PM_s8bzn3IUL4fpK9KfCGw.png" /></figure><p>I went further to ask AI how profitable the business was.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/508/1*UVoEwALZTMJ-oPu-eL1lgA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/717/1*W6ISMUXB7HZd93mLx637yQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/780/1*tontMZlbQQquIweBXM7nOQ.png" /></figure><p>Imagine applying this level of strategic analysis towards your agency and implementing recommendations provided by AI. You’d be 10x better.</p><p>That’s the power and combining structured data with AI analysis.</p><p>You can find out more about me by checking out &gt;&gt; <a href="http://datascienceportfol.io/kingsleyokoye218"><strong>my portfolio</strong></a>.</p><p>Connect with me on &gt;&gt; <a href="http://www.linkedin.com/in/ifeanyi-okoye-a857911ba">Linked in</a></p><p>Feel free to register your thoughts by commenting, if the article was helpful, please like.</p><p>God bless.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1605585d2785" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How I discovered over $15,000 waste in google ad spend for huge e commerce business.]]></title>
            <link>https://medium.com/@exceldispensing/how-i-discovered-over-15-000-waste-in-google-ad-spend-for-huge-e-commerce-business-bc3412fe1a67?source=rss-f1bbe5fa9b1b------2</link>
            <guid isPermaLink="false">https://medium.com/p/bc3412fe1a67</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[digital-marketing]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[ecommerce]]></category>
            <category><![CDATA[shopify]]></category>
            <dc:creator><![CDATA[Kingsley Okoye]]></dc:creator>
            <pubDate>Thu, 19 Mar 2026 10:51:53 GMT</pubDate>
            <atom:updated>2026-06-07T13:31:43.323Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_CDFbVHlAilMm4E3ShNJnw.jpeg" /></figure><p><strong>How I discovered over $10,000 in ad spend waste</strong></p><p>I was hired to create fully automated dashboards for an e-commerce brand, one of the most important insights I discovered was the consistent drop of ROAS year of year for 3 straight years and these were not ordinary drops.</p><p>First year: ROAS — 10.1</p><p>First year: ROAS — 8.84</p><p>Third year: ROAS — 6.4</p><p>Over the course of these 3 years there was an average of 20% in ad spend year over year.</p><p>This is a highly successful brand so this might just have gone unnoticed.</p><p>BTW, you can view my <strong>portfolio </strong>here &gt;&gt; <a href="http://datascienceportfol.io/kingsleyokoye218">My portfolio</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/822/1*5sDGlxyeiGplx5PvZsToRA.png" /></figure><p>I dug into the campaign metrics, didn’t discover much then I decided to dig into Product level metrics to see how well products were performing in terms of clicks, conversions, sales and revenue.</p><p>What I found was surprising.</p><p>Some products were getting <strong>hundreds of ad clicks</strong>.</p><p>But they had <strong>zero sales</strong></p><p>One product alone had <strong>over 400 clicks from ads, $286 in ad spend… and not a single sale conversion</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/995/1*d7MjuRd2EdUoy_LpRKFw4A.png" /></figure><p>When I summed it up, the store had paid for <strong>hundreds of clicks driving traffic to products that simply weren’t converting.</strong></p><p>I decided to dig into the product level metrics even further over a 3 year period and what I discovered was mind boggling.</p><p>By the way you can connect with me on linked in by clicking <a href="http://www.linkedin.com/in/ifeanyi-okoye-a857911ba"><strong>here</strong></a>.</p><p>See image below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/519/1*f-q3_yLD3b27IZGnUYkROw.png" /></figure><p>At the campaign level, this problem was almost invisible.</p><p>But once we broke the data down by product, the wasted ad spend became obvious.</p><p>These shortcoming wasn’t affecting the revenue, it was affecting the <strong>overall net profit and net margin (%)</strong> and this is one thing lots of e-commerce brands ignore because this isn’t the first time I have noticed something like this.</p><p>Another reason this might be happening might be because some ad platforms over amplify attribution towards themselves as well as conversion metrics, also platforms like google ads see conversions not just as sales but also events like these:</p><ul><li>Calls from ads</li><li>Form submissions</li><li>Website sign ups</li><li>Event sign ups</li><li>Store visits etc depending on how your ads are set up.</li></ul><p>Lots of times, these actions might not be generating your business any money, this is why it is important to seperate sales conversions from google ads conversions when analyzing your data.</p><p><strong>My recommendations after these discovery.</strong></p><ul><li>Pause ads for products getting clicks, getting high ad spend but no sales</li><li>Reallocate ad budget to products that actually convert</li><li>Checked product pages with low or 0 sales conversions to identify potential page, creative ir market fit issues.</li><li>Set up automated monitoring to catch this kind of bad ad spend early before it blows out of proportion.</li></ul><p>What have you done about situations like these in the past and what would you have done in this situation?</p><p>Feel free to comment below and share if you found this article insightful, remember you can connect with me on linked in <a href="http://www.linkedin.com/in/ifeanyi-okoye-a857911ba"><strong>here</strong></a>.</p><p>God bless.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bc3412fe1a67" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Set up shopify data collection and analysis automatically using windsor.ai — Part 2.]]></title>
            <link>https://medium.com/@exceldispensing/set-up-shopify-data-collection-and-analysis-automatically-using-a-no-code-tool-one-time-set-up-6fd0ad3e26c5?source=rss-f1bbe5fa9b1b------2</link>
            <guid isPermaLink="false">https://medium.com/p/6fd0ad3e26c5</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-analytics]]></category>
            <dc:creator><![CDATA[Kingsley Okoye]]></dc:creator>
            <pubDate>Sun, 15 Mar 2026 16:25:14 GMT</pubDate>
            <atom:updated>2026-06-12T02:57:33.261Z</atom:updated>
            <content:encoded><![CDATA[<p>I recently wrote an article about how you can set up automated collection and analysis of your shopify data, in that article I outlined the initial set up process from start to finish, if you are reading this article you should go read that artcicle first then come back to this.</p><p>Here is the link to part 1&gt;&gt; <a href="https://medium.com/@exceldispensing/how-to-set-up-collection-and-analysis-of-your-shopify-data-automatically-using-a-no-code-tool-one-a9a256b74630">Link to part 1</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kI9U0-utUIxornCw8bjB0Q.jpeg" /></figure><p>Now we have full access to our data but as we all know shopify has multiple tables, each table having multiple columns but as expected, only few columns are needed for analysis.</p><p>In this article, you’ll find out the columns really needed for analysis, how you can set up automated export of your data to your data warehouse and how you can even calculate advanced metrics like <strong>Revenue on ad spend (ROAS), Cost per conversion (CPC), Cost per conversion, Marketing efficiency ratio (MER), CTR, Net profit, Sale Conversion rate</strong> and much more assuming you run ads for your shopify store.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/584/1*Oi8CdCIk3yxJf_BT7Uzw_w.jpeg" /></figure><p>At this point, I assume you have followed all the instructions in the previous article and you now have access to your shopify data, on the windsor.ai website. Click shopify and then “Next (Data preview)” as seen below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E9makx7xmkl1MtR-C4Kv1Q.png" /></figure><p>You should find this next.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zhu0IPtG0PIoOSAJAHcOIA.png" /></figure><p>Click on the drop down menus as seen below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YLeTBvs2VzkrmcUFtRFnGw.png" /></figure><p>This is the filter section, for the drop down menu with “Last 7 days”, you can choose to filter for historical data, you can pick last 14, 28, 30 days, 1 year or more, you can also filter for a custom date range.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kPyb16OIyTX60bn40wFAiA.png" /></figure><p>Note that any date range above 30 days requires you to subscribe, the first month is free and after this you pay $19/month.</p><p>Next click on the blended data drop down menu and choose shopify, see image below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dy9hkhMST5UMM0EKT1-XLg.png" /></figure><p>This is my personal preference because I want to select columns from each table seperately, if you choose blended, you can pick columns from across different tables at once e.g you can pick columns from orders table and customer table and fix both columns within the same table.</p><p>I simply prefer to do this once my data has been exported to a data warehouse or database.</p><p>Next, you need to pick all the columns needed for your analysis, you do this by typing them in the search bar as seen below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*m5s7bNcepEExTfnbqBnf3A.png" /></figure><p>Here are the list of columns you really need for 90% of your analysis in the <strong>orders </strong>table.</p><p><strong>date</strong></p><p><strong>line item sku</strong></p><p><strong>line item title</strong></p><p><strong>line item variant title</strong></p><p><strong>line item price</strong></p><p><strong>order created at</strong></p><p><strong>order updated at</strong></p><p><strong>order name</strong></p><p><strong>order net sales</strong></p><p><strong>order subtotal price</strong></p><p><strong>line item quantity</strong></p><p><strong>order returns amount</strong></p><p><strong>order adjusted discounts</strong></p><p><strong>order customer last visit source</strong></p><p><strong>line item quantity</strong></p><p>Once your columns are picked, click on get data and you will find a preview of your data. See image below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3Vpoz8Fa76qo07GrcAIUdQ.png" /></figure><p>You can pick other tables and pick other columns you might need for further analysis, e.g customers table. For customers table you can pick the following columns as well.</p><p><strong>Customer state</strong></p><p><strong>Customer is returning</strong></p><p><strong>Customer total spent</strong></p><p><strong>Customer created at</strong></p><p>Do you like articles like this or are interested in more finding like this, <strong>follow</strong> me here on <strong>medium </strong>and connect with me on linked in by clicking &gt;&gt; <a href="http://www.linkedin.com/in/ifeanyi-okoye-a857911ba"><strong>here</strong></a>.</p><p><strong>Note that, I can you can reach out to me for a set up just like this as well.</strong></p><p>Now to the next part of exporting your data to your database or data warehouse of choice.</p><p>At this point, your data is available, scroll down and pick one of the data destinations available. See image below.</p><p>There are over 20 data destinations.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/821/1*5bGgaGYPCDdRqZfnxHpfSQ.png" /></figure><p>Click on “add destination”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*trRz6CtPBtFs_3sFnaZSHw.png" /></figure><p>You will be prompted to a page like this depending on the destination you picked.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0Jt_jYYkObvtMQ20iW58Cg.png" /></figure><p>You will need to fill in some details, all details needed are associated with your destination account. Simply fill them, pick a schedule and click “Test connection”, if test is successful, click “save” and your data will be automatically updated every single day based on the schedule you picked.</p><p>Also your first export will immeiately start and within few minutes you should find your data in your destination, note that this is fully dependent on how large your data is.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kPyb16OIyTX60bn40wFAiA.png" /></figure><p>If you filtered for 1 year or more, it might take more time for your data to be sent to destination. The less you need to export, the less amount of time it’ll take to export.</p><p>Once your data is available in your database, we can start analysis.</p><p>Here are the list of the metrics we plan to track:</p><ol><li>Net revenue</li><li>Net profit</li><li>Quantity of items sold</li><li>ROAS (Revenue on AD spend)</li><li>Discount</li><li>Return rate</li><li>Ad spend</li><li>Ad Cost per conversions</li><li>Ad Cost per click</li><li>Cost of goods sold</li><li>Orders</li><li>Ad Impressions</li><li>Ad Click through rate</li><li>Ad Clicks etc.</li></ol><p>I used Big Query as my data destination and here is a snippet of my orders table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4Lq_DhBgKRaSNh6O0N14Zw.png" /></figure><p>We can track calculate all metrics using SQL in Big Query</p><p>Metric 1: Net revenue — Net revenue is total sales after discount and returns. Here is the query.</p><pre>    with net_sales_duplicated   as   <br>         (<br>   select *,<br>   row_number() over (partition by order_created_at, order_name <br>   order by order_created_at, order_name) as rn<br>   from <br>   windsor_shopify_google_ads.shopify_orders_table_net_sales<br>   --replace above table name with yours<br>where 1=1<br>   and line_item__title is not null  <br>   qualify rn = 1<br>          )<br>   select date, sum(order_net_sales) as Net_sales<br>   from   <br>   net_sales_duplicated<br>   group by 1 ORDER BY 1<br><br></pre><p>Metric 2: Quantity of items sold .</p><p>Here is the query.</p><pre>               <br>     WITH qty_table AS <br>     (<br>     SELECT *,<br>     ROW_NUMBER() OVER (PARTITION BY ORDER_NAME, DATE ORDER BY ORDER_NAME, DATE) AS RN <br>     FROM collections-online-sales.windsor_shopify_google_ads.shopify_orders_returns_qty_sold<br>     QUALIFY RN = 1<br>     )<br>     SELECT date, <br>     sum(line_item__quantity) as items_sold<br>     FROM qty_table<br>     group by 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/339/1*Q3wbWPqnZa9Lk6TfNGhrBQ.png" /><figcaption>KPIs</figcaption></figure><p>3. Discount</p><pre>with correct_set_up as   <br>                    (<br> select nt.line_item__title, nt.line_item__variant_title, nt.order_created_at<br>,sum((order_net_sales/order_subtotal_price) * nt.line_item__price) as gross_revenue,<br> sum(((order_net_sales/order_subtotal_price) * nt.line_item__price) - order_adjusted_discounts)<br> as Net_sales,<br> sum(order_adjusted_discounts) as total_discount<br> from<br> collections-online-sales.windsor_shopify_google_ads.shopify_orders_table_net_sales nt <br> group by all              <br> )<br> select cast(timestamp(order_created_at) as date) as order_date, <br> sum(gross_revenue) as gross_revenue,<br> sum(Net_sales) as net_revenue,<br> sum(total_discount) as discount_amount<br> from correct_set_up<br> group by all <br> order by 1</pre><p>4. Return rate</p><pre>   WITH qty_table AS <br>   (<br>    SELECT *,<br>    ROW_NUMBER() OVER (PARTITION BY ORDER_NAME, DATE ORDER BY ORDER_NAME, DATE) AS RN <br>    FROM collections-online-sales.windsor_shopify_google_ads.shopify_orders_returns_qty_sold<br>    QUALIFY RN = 1<br>    )<br>    SELECT date,<br>    sum(case when line_item__quantity is null then 1 else 0 end) as returned,<br>    sum(line_item__quantity) + <br>    sum(case when line_item__quantity is null then 1 else 0 end) <br>    as total_qty_sold,<br>    (sum(case when line_item__quantity is null then 1 else 0 end))/<br>    (sum(line_item__quantity) + <br>     sum(case when line_item__quantity is null then 1 else 0 end))<br>     as return_rate<br>     FROM qty_table<br>     group by 1;<br>                  </pre><p>5. Total orders</p><pre>    select order_customer_last_visit_source, dt.date, <br>    count(distinct dt.order_name)<br>    as unique_orders<br>    from   <br>    windsor_shopify_google_ads.view_for_gross_profit_cogs_and_shipping_shopify_project sp<br>    group by all;<br></pre><p>The above metrics are metrics associated specifically with Shopify but what if you need to understand how your e commerce brand is doing altogether, what if you really need to understand how well the business is performing all round not just within shopify.</p><p>What if you really need to understand if your ad spend is worth it?</p><p>This is where integration of other data sources come in, if you run ads on Google or Meta, you connect to these data sources and bring them into your database.</p><p>If you have product pricing or business expense data in google sheets, excel or quickbooks as I have seen in practice, these can also be integrated.</p><p>In a nutshell, whatever metric you need, even if it cuts across multiple data sources, can be calculated.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*muBSaA-y14LQ7p1aPt34Pg.png" /></figure><p>Do you need this set up fully automated for your e commerce business, feel free to reach out to me on linked in &gt;&gt; <a href="http://www.linkedin.com/in/ifeanyi-okoye-a857911ba"><strong>here</strong></a>.</p><p>For the purpose of this example, I’ll be integrating data from Google ads to calculate ad performance metrics like <strong>ROAS, CPC, Cost/conv., CTR</strong> and much more.</p><p>To access your data, you can head back to windsor.ai and pick google as data source.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UxmUXNV0S6fo7_OXY0cURw.png" /></figure><p>Pick the columns needed for your analysis, columns needed are</p><ul><li>clicks</li><li>impressions</li><li>conversions</li><li>cost (this is ad cost)</li><li>ctr (click through rate)</li></ul><p>I wrote this basic SQL query below to analyze and understand ad performance.</p><pre> with net_sales_duplicated   as   <br>         (<br>   select *,<br>   row_number() over (partition by order_created_at, order_name <br>   order by order_created_at, order_name) as rn<br>   from <br>   windsor_shopify_google_ads.shopify_orders_table_net_sales<br>   --replace above table name with yours<br>where 1=1<br>   --and line_item__title is not null  <br>   qualify rn = 1<br>          ),<br>          Net_sales_table as   <br>          (<br>   select date, sum(order_net_sales) as Net_sales<br>   from   <br>   net_sales_duplicated<br>   group by 1<br>          ),<br>          ad_metrics_table as   <br>          (<br>    select segments_date, sum(metrics_cost_micros/1000000) as ad_spend, <br>    sum(metrics_impressions) as impressions, avg(metrics_average_cpc/1000000) as cpc,<br>    avg(metrics_average_cpm/1000000) as cpm, avg(metrics_conversions_from_interactions_rate) as interaction_rate,<br>    sum(metrics_clicks) as clicks,<br>    avg(metrics_ctr) as ctr <br>   from   <br>    collections-online-sales.windsor_shopify_google_ads.ads_Campaign_3731321554<br>    group by 1  <br>          )<br>    select * , Net_sales/ad_spend as ROAS<br>    from <br>    net_sales_table ns  <br>    inner join ad_metrics_table am    <br>    on ns.date = am.segments_date <br>    order by 1;</pre><p>Here is th result.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5L2MC8_WeBzUDTD8C1ewYw.jpeg" /></figure><p>The possibilities are endless when you have access to data needed.</p><p><strong>My major analytic discovery for this project.</strong></p><p>After completion of this project, I discovered one long term trend and the discovery was that <strong>ROAS</strong> (Revenue of AD spend) had consistently dropped year over year for about 3 years.</p><p>So I decided to dig deeper.</p><p>Instead of only looking at campaign-level metrics, I decided to dig into Product level metrics to see how well products were performing in terms of clicks, conversions, sales and revenue.</p><p>What I found was surprising.</p><p>Some products were getting <strong>hundreds of ad clicks</strong>.</p><p>But they had <strong>zero sales</strong></p><p>One product alone had <strong>over 400 clicks from ads, $286 in ad spend… and not a single conversion</strong>.</p><p>When I summed it up, the store had paid for <strong>hundreds of clicks driving traffic to products that simply weren’t converting.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/995/1*d7MjuRd2EdUoy_LpRKFw4A.png" /></figure><p>At the campaign level, this problem was almost invisible.</p><p>But once we broke the data down by product, the wasted ad spend became obvious.</p><p>What made this worse, this was all within a 30 day period, I dug deeper into a 3 year period and what I found was shocking. Below is a screenshot.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/519/1*f-q3_yLD3b27IZGnUYkROw.png" /></figure><p>These shortcoming wasn’t affecting the revenue, it was affecting the <strong>overall business profit and net margin (%)</strong> and this is one thing lots of businesses ignore because this isn’t the first time I have noticed something like this.</p><p>Lots of e commerce businesses have lots of cash flow, in fact cash flow might consistently grow year over year and this leads the business owner to believe they need to increase ad spend, unfortunately when ad spend is increased, the amount of ad spend on these poor performing products also increases.</p><p>Another reason this might be happening might be because some ad platforms over amplify ROAS and conversion metrics, also platforms like google ads see conversions as not just sales but also actions like these:</p><ul><li>Calls from ads</li><li>Form submissions</li><li>Website sign ups</li><li>Event sign ups</li><li>Store visits etc depending on how your ads are set up.</li></ul><p>Lots of times, these actions might not be generating your business any money, this is why it is important to seperate sales conversions from google ads conversions when analyzing your data.</p><p>Its also worthy to note that there were products getting $0 in ad spend but getting sales conversions. Image below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*y4CC92QEnxOGPzHxTWngoQ.png" /></figure><p><strong>My recommendations.</strong></p><ul><li>Pause ads for products getting clicks, getting high ad spend but no sales<br>• Reallocate ad budget to products that actually convert<br>• Checked product pages with low or 0 sales conversions to identify potential issues.<br>• Set up automated monitoring to catch this kind of bad ad spend early before it blows out of proportion.</li></ul><p>In all this project was another eye opener and I’m truly excited I made this discovery, if you suspect this might be happening with your e commerce brand, feel free to reach out to me via linked in &gt;&gt; <a href="http://www.linkedin.com/in/ifeanyi-okoye-a857911ba">Contact me</a>.</p><p>Interact with the final dashboard here &gt;&gt; <a href="https://lookerstudio.google.com/reporting/b98f357a-6014-445f-9b1c-5fd606aef6d0">Shopify dashboard link</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*s4jVheVPiNCT4p7kGq6IIg.jpeg" /><figcaption>Shopify dashboard</figcaption></figure><p>I hope this article has been helpful.</p><p>Kindly like and comment if you have found it helpful.</p><p>God bless.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6fd0ad3e26c5" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to set up collection and analysis of your shopify data automatically using windsor.ai.]]></title>
            <link>https://medium.com/@exceldispensing/how-to-set-up-collection-and-analysis-of-your-shopify-data-automatically-using-a-no-code-tool-one-a9a256b74630?source=rss-f1bbe5fa9b1b------2</link>
            <guid isPermaLink="false">https://medium.com/p/a9a256b74630</guid>
            <category><![CDATA[shopify]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-integration]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Kingsley Okoye]]></dc:creator>
            <pubDate>Tue, 10 Mar 2026 16:32:37 GMT</pubDate>
            <atom:updated>2026-06-12T02:57:01.741Z</atom:updated>
            <content:encoded><![CDATA[<h3>How to set up collection and analysis of your shopify data automatically using windsor.ai. (One time set up).</h3><p>You’ve started selling on shopify or you have a client selling on shopify, recently they/you decided to start tracking and analyzing shopify data to see real time performance and possibly make business decisions using this data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/210/1*3LJw7GItuu43KUuANqJXFA.png" /></figure><p>Basically you need access to your raw shopify data, I will be showing you how to set this up step by step.</p><p>Prerequisites</p><ol><li>You must either be the owner of the shopify store or the owner of the shopify store adds you as a user, another option is having a shopify partners account.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*efLM58bHzRbVUBmuvXqShw.png" /></figure><p>2. You need the shopify store url and number.</p><p>Store url would look like this &gt;&gt; mydiamondstore.myshopify.com</p><p>Next, if you need to send a request to the specific shopify store using the store url and id.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lBjtpQ2IjCum_g9hEl8dTA.png" /></figure><p>If you have a partners account simply click on “Stores” in your shopify dashboard and click on “Add store”, in the drop down menu that pops up click “Request access to collaborator store”.</p><p>On the next page, add the store url as seen in the screenshot below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TwdzOpmzlH0Y3RBYESng2w.png" /></figure><p>Scroll down and tick the following permissions as seen below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/743/1*BkjfuyOBNhx4RkDY3WKxpw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/765/1*MocGbKxejddYWdsgdRiLOA.png" /></figure><p>Next, click “Request access to store” at the bottom, once you request access you are prompted to provide store id, this is usually in digits. Provide the store id and a request is immediately sent to the client.</p><p>Once the request is approved, you have access to the shopify store in with the permissions which have been granted to you.</p><p>The aforementioned steps are steps you needed to take if you had to use a partner account but if you are the owner of the store or the owner added you as a user then you start from here.</p><p>Step 1: Go to windsor.ai, windsor.ai is a platform that allows you access data from all kinds of data sources, with windsor.ai you can access you google ads and facebook ads data, shopify or woo commerce e commerce data and much more, infact you can connect to over 325 data sources using windsor.ai.</p><p>Once on the windsor.ai page, hover over the “products” table and pick click on “data sources” as seen below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*otb_ABDuPMoZcqnUkHsUVw.png" /></figure><p>By the way, if you need to set up an analytics system just like this for your business, feel free to reach ou to me, you can connect with me on linked in using this &gt;&gt; <a href="http://www.linkedin.com/in/ifeanyi-okoye-a857911ba">link</a> .</p><p>Also check out real time data analysis projects I have worked via my portfolio &gt;&gt; <a href="http://datascienceportfol.io/kingsleyokoye218">Portfolio projects</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SogEEgISmIWX7OFtWkQ7vQ.png" /><figcaption>Me</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8oL1yg6LR8a7UAMNXV6pRQ.png" /></figure><p>Ensure to be logged in on the email attached to the data source you plan to access, this means if the email address you always use to log into your shopify store is “yourname@gmail.com”, make sure you are logged in on “yourname@gmail.com” before you initiate this process.</p><p>Next search for your specific data source in the search bar and click on it once it appears as seen below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UYqwq2hTBKuitgt3TFpdQQ.png" /></figure><p>Pick your data source and you will be redirected to the next page, see below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*P7UF4cpqXaQAo2TROMOtlQ.png" /></figure><p>Once shopify is selected, you will be provided with a list of instructions on how to set up access to your shopify data as seen below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kNzyoJvgkbubrQQlEqaABA.png" /></figure><p>I’ll guide you through each step from here.</p><p>Step 1: Click on the Dev dashboard in blue as seen below, if you are already logged into your shopify account then you will be immediately directed to your Dev dashboard environment in shopify.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/698/1*GEU6-fdOa-S0Z6TUXKs2lw.png" /></figure><p>Step 2: Click on create app and give the app you create a name.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pyaNWePsIKW_STXiXGuzJw.png" /></figure><p>!!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WgYiv0HrZ5kX-9-2AMjukA.png" /></figure><p>Add the app url to the space provided as seen below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UOi7YjyvPUSd8PHhZpTDXg.png" /></figure><p>Select scopes, scopes required are listed in the image.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HNqt8n9XGDHCauP3XsGRPA.png" /></figure><p>Type scopes in the space provided one after each other as seen in image below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wDEi2l0tsRxPTPg6FrTWMA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fxgd8AsegFdMu6kY4sYM_A.png" /><figcaption>Click done after selecting scopes</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*y6naSZxjdwKCliJiOFaT0w.png" /><figcaption>Click release after selecting scopes</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kQcLUyL0-fv5GW7ffV9Fvg.png" /><figcaption>Type version name in space provided and release</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DxbnSdeJ8sdzVy6BpGjO7Q.png" /><figcaption>Go back to home and release</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*F53m8g-X53bxNsVaYM2hsw.png" /><figcaption>Select distribution method</figcaption></figure><p>You will be redirected to a page where you are required to pick between public and custom distribution, pick “custom distribution”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Wwq_fRDilo7LV1qdA5E23g.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PVEPhu--i2PmdzNgYZ-EZg.png" /><figcaption>Type in your store url in the space provided</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RHolkWAWhAYHuPa5VFOYIQ.png" /><figcaption>Generate link after adding your store url</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wcj7-bN-GHSklh-SO1Hf6g.png" /><figcaption>Copy the generated link and paste in a new tab</figcaption></figure><p>You will be prompted to log into your shopify account again.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/949/1*aEAL99A0R16pNHsNrCmUSA.png" /><figcaption>Log into your shopify account</figcaption></figure><p>Click install in the install app page after logging in.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/913/1*7_QqmcjFBSVf4eYKi6wVLg.png" /></figure><p>On successful installation, you will find a page like this.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sdygfUaZHhEy9ZTyisnAKQ.png" /></figure><p>You might see a message saying “app refused to install” on hovering over this page, ignore this because the app has installed.</p><p>After installation go back to your Dev dashboard.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/821/1*-6lUivI0-rzVloT5g_ZxCw.png" /></figure><p>Click on the app ypu just created.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mKAl_TL4RLEAO3aQxWq-7g.png" /></figure><p>Reemember you can connect with me on linked in, just click <a href="http://www.linkedin.com/in/ifeanyi-okoye-a857911ba">here</a>.</p><p>Also check out real time data analysis projects I have worked via my portfolio &gt;&gt; <a href="http://datascienceportfol.io/kingsleyokoye218">Portfolio projects</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/973/1*M_U-cy_26sy6jAShHKHNXA.png" /><figcaption>Click on settings</figcaption></figure><p>You will be redirected to a page where you will find your client id and client secret, copy and save them somewhere on your computer.</p><p>Now go back to <a href="http://windsor.ai/">windsor.ai</a> website.</p><p>Click “connect shopify store”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*k2JcOYeYqmLJDAJntGJ08g.png" /></figure><p>Next you will need to provide the following credentials, your store url, your client id and client secret which you already have.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*gXkZqAfjRkL0UHdRPlq5lA.png" /></figure><p>Note that you might not be able to connect to your store immediately, this is a temporary error, all you need to do is restart your browser or system all together.</p><p>Once this process is completed, you should have full access to your shopify store. Your data is ready to be cooked.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ypTFYqWGdUsmprE0sQRhHw.png" /></figure><p>At this point, you are done with the set up, you now have full access to your shopify data.</p><p>In the next part of this series, we will be connecting the data provided to a data warehouse (Snowflake, Big Query etc) or BI tool (Power Bi, Looker studio, Tableau) for further analysis.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*imi9Hfzp7HqFZHHm4JZfcg.jpeg" /></figure><p>You will also be able to set up automatic flow of new data to any destination of your choice on a daily basis at anytime you prefer. Your data is basically updated every single day.</p><p>In the second part of this article, I’ll show you the exact columns to pick in order to access specific metrics in shopify like Net revenue, Gross revenue, Returns, return rate, shipping fee, Quantity of products sold and much more.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/913/1*ndsIzqMOJGKNcULJ5xFMjw.png" /></figure><p>I’ll also show you how you can integrate your shopify data with other data sources like google ads, Facebook ads, locally saved data in csv or pdf files etc. This way you can track more advanced metrics like ROAS, Net profit, Cost of goods sold, Cost per click, cost per conversion.</p><p>Do you want me to set this up for your business? Reach out to me directly via <strong>linked in</strong> &gt;&gt; <a href="http://www.linkedin.com/in/ifeanyi-okoye-a857911ba">Click here</a>.</p><p>Part 2 is up now, <a href="https://medium.com/@exceldispensing/set-up-shopify-data-collection-and-analysis-automatically-using-a-no-code-tool-one-time-set-up-6fd0ad3e26c5"><strong>click here</strong></a> to read</p><p>I hope this article has been helpful to you.</p><p>God bless!!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a9a256b74630" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[7 looker studio challenges you will face and how you can handle them.]]></title>
            <link>https://medium.com/@exceldispensing/7-looker-studio-challenges-you-will-face-and-how-you-can-handle-them-cae60005e5fb?source=rss-f1bbe5fa9b1b------2</link>
            <guid isPermaLink="false">https://medium.com/p/cae60005e5fb</guid>
            <category><![CDATA[google-analytics]]></category>
            <category><![CDATA[looker-studio]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[web-analytics]]></category>
            <dc:creator><![CDATA[Kingsley Okoye]]></dc:creator>
            <pubDate>Wed, 23 Jul 2025 18:35:49 GMT</pubDate>
            <atom:updated>2026-06-07T13:41:54.273Z</atom:updated>
            <content:encoded><![CDATA[<p>Looker Studio (formerly Google Data Studio) has become a go-to tool for building dashboards and visualizing data especially for marketers, analysts, small teams and business owners. It’s free, integrates well with other Google products, and offers an easy drag-and-drop user interface.</p><p>While it looks simple on the surface, working with Looker Studio can sometimes be frustrating.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EP4iaWeUqCKDbhwH8DkZZw.jpeg" /></figure><p>From slow-loading reports to limited blending options and timestamp errors, many users eventually hit roadblocks that aren’t so easy to solve without workarounds or deeper technical knowledge. In this article, we’ll break down some of the most common challenges users face with Looker Studio and how to navigate them without losing your cool (or your client’s patience).</p><p><strong>Challenge 1: Handling date integration</strong></p><p>Have you ever uploaded data to looker studio and discovered that the date column range presented in looker studio was different from what you saw in your source data.</p><p>Example: In your google sheet or CSV file, the maximum date is ‘2025–05–10’ but in looker studio you are seeing ‘2025–10–05’ or even something else as the maximum date.</p><p>This is because looker studio’s intuitive design automatically converts date columns from source data to date but it can sometimes be wrong.</p><p>Here is how you an fix it:</p><p>Step 1: Convert the <strong>date </strong>column to <strong>text </strong>data type.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/597/1*VkJ-6Z9AUE0976XUYvh71A.png" /></figure><p>Step 2: Add a new calculated column.</p><p>Step 3: Apply this formula in the newly created calculated column using the date your converted to text.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/504/1*nTotvzsiPRcuOUfhSLmbig.png" /></figure><p>Take note of the date structure from the source data, if the data comes as month/day/year then use “%m/%d/%Y”, if the date comes as day/month/year then use “%d/%m/%Y”.</p><p>Once this formula is successfully applied, your date should be correct.</p><p><strong>Challenge 2: Importing data from csv files</strong></p><p>If you have ever tried to upload a csv file to got “error”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/865/1*geET9Shk-V0ges-rSzAgDQ.png" /></figure><p>It might just be because looker studio is not ANSI compatible, when uploading a csv file, ensure to convert it to utf-8 character encoding before uploading.</p><p>By the way, you can find multiple looker dashboards I have created via my <a href="http://datascienceportfol.io/kingsleyokoye218"><strong>PORTFOLIO</strong></a> &lt;&lt; link</p><p>You can do this by opening the file with a TXT editor like notepad and then converting to utf-8 as seen below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/665/1*Iu-A1EzAuKHAvp6ZzmoV9A.png" /></figure><p>Simply click on the ANSI icon and pick utf-8</p><p><strong>Challenge 3: Upload error due to spaces empty rows.</strong></p><p>Sometimes uploading a csv file having blank spaces like the one seen below will lead to error during upload.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/834/1*sc09p4UDSJHQS1Wb5AmQvw.png" /></figure><p>Simply replace the empty spaces with a place holder like 0, null or a default number to prevent this error.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/709/1*FQmYu22-2jswGEC1L3ZcuA.png" /></figure><p>Table new look.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/378/1*M871g2WKSioEF922ap7U1w.png" /></figure><p>Check out other related medium articles I have created via the links below:</p><p><a href="https://medium.com/@exceldispensing/how-to-load-data-into-google-big-query-using-python-in-10-minutes-1aab32c1c4e8"><strong>How to load data into big query using python</strong></a></p><p><a href="https://medium.com/@exceldispensing/get-a-clear-understanding-of-any-websites-ga4-metrics-using-big-query-and-looker-605c90f8fe59"><strong>Get a clear understanding of your Google analytics metrics GA4 metrics using Big Query and looker studio.</strong></a></p><p><a href="https://medium.com/@exceldispensing/how-do-you-solve-this-problem-as-a-data-business-analyst-sql-challenge-9e6670e6b967"><strong>How do you solve this problem as a data analyst</strong></a></p><p><strong>Challenge 4: Blending and joining related tables</strong></p><p>Blending data from multiple tables is sometimes inevitable when creating dashboards, looker studio blends are just like blends in SQL. You must blend properly else your final results and skewed and incorrect.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/992/1*JaerXzypIatfmq3egj7dxA.png" /></figure><p>1. <strong>Common Key Requirement</strong><br> To blend two tables, they must share a common key, if you trying to blend sales table with customer table, then both tables must have a related or common column such as customer id or customer name. Even though the columns may have different column names, as long as they contain matching values (keys), they can be used to establish a relationship between the tables.</p><p>2. <strong>Matching Data Types</strong><br> The common key used to join the tables must be of the same data type in both tables. If the data types differ, Looker Studio will throw an error when you try to use the blended data in visualizations.</p><p>3.<strong> Select on data you need</strong><br>When creating blends in looker studio, you might be tempted to import all columns from all related tables into your final blended table but importing too many columns can affect the performance of your dashboard, avoid this by importing only tables need for analysis.</p><p><strong>Challenge 5: Validating your blends</strong></p><p>If your blends or joins aren’t validated in looker studio, you might be presenting the wrong data to your stake holders.</p><p>Here is how to ensure your blends are done properly:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/612/1*Ryz-PMLLdabrbua2yA_eRg.jpeg" /></figure><p>Step 1: Identify the fact table and the dimension table, the fact table is usually the table with measurable metrics and facts like order date, order amount, discount rate, quantity ordered etc while the dimension table contains attributes describing metrics present in the fact table. Example, Sales table is a fact table while Customer details or Product details table are fact tables. Once your fact table is identified, count the number of records present in your fact table. In the image presented below, the fact table has 120,000 records.</p><p>Next, also count the number of records in the dimension table, number of records present in the dimension table are usually way less than number of records present in the fact table, as far as the number of records present in the dimension table are less than in the fact, you can validate your blends using this method.</p><p>Step 2: Blend your fact table with the dimension table using the LEFT OUTER JOIN and count the number of records in the new blended table. If the new table has more records than the original fact table then your blend is wrong, this is usually because some records occur more than once in the dimension table. Example, you are joining sales table to product table using the product_id column, if there is more than one occurrence of product_id 5 in the dimension table then the blend result will be wrong, in this situation the source data needs to be checked for duplicates or wrong entry.</p><p>On the other hand, if the number records present in the original fact table and the blended table are the same then your blend is correct.</p><p>Note: This does not apply when blending two fact tables</p><p>You can learn more about this from this article: <a href="https://medium.com/@exceldispensing/before-you-write-your-next-sql-join-statement-make-sure-to-avoid-this-mistake-b420650b820f">Writing proper SQL joins</a></p><p><strong>Challenge 6: Version control</strong></p><p>You just added a feature which has affected the way your dashboard looks and you need to revert to the previous version before this change, simply use <strong>VERSION CONTROL.</strong></p><p>Here is a video showing how you can implement version control in looker studio:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/400/1*-wY3V1qtP9GOFqW1QX9nTQ.gif" /></figure><p><strong>Challenge 7: Visualizing metrics with significantly </strong>different scales</p><p>When visualizing two metrics with significantly different scales — like NUMBER OF SESSIONS and BOUNCE RATE (%) — on a single line chart in Looker Studio, the smaller values can easily appear flat.</p><p>Here is a simple fix to help make both variables clearly visible, even when their scales differ significantly.</p><p>Simply fix both metrics on different axis as seen in video below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HOgoQwQL-aZdjV_pRC1pwg.gif" /></figure><p>Thanks for reading, if you found this article insightful, remember to give it a clap and if you feel there is anything I missed, please feel free to air your opinion in the comment section below.</p><p><a href="http://datascienceportfol.io/kingsleyokoye218"><strong>My portfolio link</strong></a></p><p>Here is a link to some other medium articles I have written:</p><p><a href="https://medium.com/@exceldispensing/how-to-load-data-into-google-big-query-using-python-in-10-minutes-1aab32c1c4e8"><strong>How to load data into big query using python</strong></a></p><p><a href="https://medium.com/@exceldispensing/get-a-clear-understanding-of-any-websites-ga4-metrics-using-big-query-and-looker-605c90f8fe59"><strong>Get a clear understanding of your Google analytics metrics GA4 metrics using Big Query and looker studio.</strong></a></p><p><a href="https://medium.com/@exceldispensing/how-do-you-solve-this-problem-as-a-data-business-analyst-sql-challenge-9e6670e6b967"><strong>How do you solve this problem as a data analyst</strong></a></p><p>God bless!!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cae60005e5fb" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[GET A CLEAR UNDERSTANDING OF ANY WEBSITE’S GA4 METRICS USING BIG QUERY AND LOOKER STUDIO.]]></title>
            <link>https://medium.com/@exceldispensing/get-a-clear-understanding-of-any-websites-ga4-metrics-using-big-query-and-looker-605c90f8fe59?source=rss-f1bbe5fa9b1b------2</link>
            <guid isPermaLink="false">https://medium.com/p/605c90f8fe59</guid>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[looker-studio]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[google-analytics]]></category>
            <dc:creator><![CDATA[Kingsley Okoye]]></dc:creator>
            <pubDate>Tue, 05 Nov 2024 06:02:26 GMT</pubDate>
            <atom:updated>2026-06-07T13:45:11.379Z</atom:updated>
            <content:encoded><![CDATA[<p>With GA4, Google empowers you with the ability to track nearly every aspect of activity on your website using the extensive data it provides.</p><p>With its powerful event-based tracking model, GA4 allows you to move beyond traditional page views and delve into advanced user interactions like clicks, video views, and scroll depth. Imagine having the ability to see how visitors engage with your content in real-time, identify patterns, and make data-driven decisions to enhance user experience.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/1*MkncF4biJFzGNW1pAnvxjg.png" /></figure><p>In this article, I’ll be extracting insights from GA4 data and establishing what impact these metrics have on the success of your website.</p><p>First, its important to note that the 3 most important aspects of tracking GA4 metrics are:</p><ol><li><strong>USERS : </strong>these are website visitors.</li><li><strong>EVENTS: </strong>events taking place on the website e.g add to cart, purchase, page view etc.</li><li><strong>SESSIONS: </strong>unique user session, a session automatically starts when a someone visits the website.</li></ol><p>GA4 <strong>user activity dashboard </strong>below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_ziPxm4W3MHRrP-XzTdl7w.jpeg" /></figure><p><a href="https://lookerstudio.google.com/reporting/3e174c08-a5bc-4a49-8f5b-366b1732d64a">Click here to view dashboard</a></p><p><strong>Sessions</strong></p><p>A session represents a group of user interactions on your website (events) that take place within a specific time frame.</p><p>A session starts when a user visits your website, when the user takes specific actions, these actions are recorded as events, for example if a user on a website views a page this event is called a page_view, also, if a user visits your website for the first time this event is called first_visit. Like these many events can be tracked with google analytics, some events are tracked automatically while implementations need to be put in place for some other events to be tracked.</p><p>Here are some of the most important metrics you could track using the GA4 data made available to you in <strong>Big query</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/471/1*pPULeGmE-hY1XnLTKcIFfw.png" /></figure><ol><li><strong>UNIQUE SESSIONS</strong></li></ol><p>With this metric you are calculating the number of unique sessions on your website within a specific timeframe.</p><p>A session starts when a user visits your website your site, when the user takes specific actions</p><p>How to calculate unique sessions with <strong>BIG QUERY</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*chPOBMsA-G22Xwl33gLe3A.png" /></figure><p><strong>RESULT</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/286/1*3CicYy0DovnPoGU8l0Lqvw.png" /></figure><p>How to calculate Unique sessions in LOOKER STUDIO (formerly GOOGLE DATA STUDIO)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/1*AcDo5WD40zj8JXjzRcNK9w.png" /></figure><p>With these metric you can dig deeper and get a better understanding of where MOST visitors are coming from. Data visual below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/481/1*muk3W8C0pSJL3iadE6iZww.png" /></figure><p>BTW, you can check out my portfolio here &gt;&gt; <a href="http://datascienceportfol.io/kingsleyokoye218"><strong>My portfolio</strong></a></p><p><strong>2. AVERAGE SESSION DURATION</strong></p><p>When a user visits your website, GA4 starts to count how much time they have spent on the website (in milliseconds) until they close the tab or leave the website.</p><p>Note that if a visitor leaves the website and doesn’t return within 30 minutes, the session is closed and a new session is started the next time the user visits.</p><p><strong>Average session duration</strong> gives you a clear understanding of user engagement in your website. The higher the <strong>Average session duration </strong>metric value is the better.</p><p><strong>Formula for Average session duration = engagement time/unique sessions</strong></p><p>How to calculate average session duration using <strong>Big query.</strong></p><p>Query below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dG2-khKJn4gU47ksxyGZpA.png" /></figure><p><strong>RESULT</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/391/1*GyNJ2Mb8bt_1c3a-gPRiTw.png" /><figcaption>Average session duration in seconds</figcaption></figure><p>Average session duration in seconds.</p><p>How to calculate average session duration using <strong>Looker studio.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/628/1*L9Ra_oaBCGGYF_s52-Lg0Q.png" /></figure><p>Note that the engagement time column in GA4 is calculated in milliseconds, hereby you need to divide to 1000 to get the value in seconds as seen above.</p><p>3. <strong>Number of engaged sessions.</strong></p><p>In GA4, a session can either be <strong>engaged </strong>or <strong>not engaged</strong>.</p><p>According to GA4 support, an engaged session is a session that lasts longer than <strong>10 seconds</strong>, has a <strong>key event</strong>, or has at least <strong>2 pageviews or screen views.</strong></p><p>This means the user came to the website and either met any of the specified criteria above or didn’t.</p><p>An engaged session can be identified by checking the <strong>session engaged </strong>column in GA4.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/212/1*pRdTIskzkhWooqpi8Jogqg.png" /></figure><p>A value is 1 means the session was engaged but a value of 0 means the session wasn’t engaged.</p><p>How to calculate number of engaged sessions using B<strong>ig query</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*T_GaKHIBkdkbd_Q1E3Hzpw.png" /></figure><p>Result</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/377/1*AZY2WnMJSxBRR4Lpn7H42g.png" /></figure><p>Calculating session engaged with Looker studio.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/991/1*fI587bslKIJKHzndQKGn0A.png" /></figure><p>With this metric, you can get more insights about what device and web browser your engaged website visitors are using.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/748/1*4F6dh9zfQz_YGHVMfnklhQ.png" /></figure><p>You can also clearly see what time of the day users are actively engaging with the website.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/997/1*Iit590fC0LPHbv8F9Iym5Q.png" /></figure><p>4. <strong>Engagement rate</strong></p><p>When users visit your website, they are either engaged or not engaged, this metric reveals how percentage of website visitors which are engaged.</p><p>The higher the engagement rate, the better.</p><p>How to calculate engagement rate with <strong>Big query</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VXfRROrW9AMxeknoKpaJvg.png" /></figure><p><strong>RESULT</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/925/1*VX_Lrk6UCXNbU0Z61CkVEw.png" /></figure><p>How to calculate engagement rate with <strong>Looker studio</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/992/1*jB9KvnC1pYpzgJc6G7lc6g.png" /></figure><p>5. <strong>Retention rate</strong></p><p>This is a measure of how many people who visited your website in the past came visiting again. When a user visits your website for the first time, this is recognized and the session is assigned a session number of 1.</p><p>Remember to visit my portfolio here &gt;&gt; <a href="http://datascienceportfol.io/kingsleyokoye218"><strong>My portfolio</strong></a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/142/1*90X2NuFeezDMiUoLlmK0Rg.png" /><figcaption>ga session number</figcaption></figure><p>Note that ga session number is different from ga session id, ga session id is the unique number assigned to each session while ga session id represents the number of visits.</p><p>How to calculate retention rate with <strong>Big query</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ydzmvi3-67hX6JHBp4SJXA.png" /></figure><p><strong>RESULT</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/274/1*3Qs7HZAALrKCxJKNcNoFWw.png" /></figure><p>How to calculate <strong>retention rate</strong> with <strong>LOOKER STUDIO</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/672/1*fyyEEERLRjORUKwhQyuycw.png" /></figure><p>You can also see retention rate overtime.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/476/1*duY3IsrnXOZld5X9Qf-xhQ.png" /></figure><p>Finally, there are many more metrics that can be tracked using GA4 and I will be posting about these metrics as time goes on/</p><p>If you found this article helpful, kindly give it a clap.</p><p>God bless.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=605c90f8fe59" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[HOW TO GENERATE FAKE DATA FOR YOUR NEXT DATA PROJECT.]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://medium.com/@exceldispensing/how-to-generate-fake-data-for-your-next-data-project-05535d6b3107?source=rss-f1bbe5fa9b1b------2"><img src="https://cdn-images-1.medium.com/max/776/1*zYURz-pNWFy56gGHA28gdw.png" width="776"></a></p><p class="medium-feed-snippet">Projects are extremely important when trying to showcase your skills to potential recruiters and employers as a data professional, while&#x2026;</p><p class="medium-feed-link"><a href="https://medium.com/@exceldispensing/how-to-generate-fake-data-for-your-next-data-project-05535d6b3107?source=rss-f1bbe5fa9b1b------2">Continue reading on Medium »</a></p></div>]]></description>
            <link>https://medium.com/@exceldispensing/how-to-generate-fake-data-for-your-next-data-project-05535d6b3107?source=rss-f1bbe5fa9b1b------2</link>
            <guid isPermaLink="false">https://medium.com/p/05535d6b3107</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Kingsley Okoye]]></dc:creator>
            <pubDate>Sat, 21 Sep 2024 22:03:08 GMT</pubDate>
            <atom:updated>2024-09-21T22:03:08.702Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[BEFORE YOU WRITE YOUR NEXT SQL JOIN STATEMENT, MAKE SURE TO AVOID THIS MISTAKE.]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://medium.com/@exceldispensing/before-you-write-your-next-sql-join-statement-make-sure-to-avoid-this-mistake-b420650b820f?source=rss-f1bbe5fa9b1b------2"><img src="https://cdn-images-1.medium.com/max/612/1*fT6HfXA3CybIiwezIsu1Lg.jpeg" width="612"></a></p><p class="medium-feed-snippet">The good thing about working with real world data is that you get to learn concepts you would never have learnt working with sample&#x2026;</p><p class="medium-feed-link"><a href="https://medium.com/@exceldispensing/before-you-write-your-next-sql-join-statement-make-sure-to-avoid-this-mistake-b420650b820f?source=rss-f1bbe5fa9b1b------2">Continue reading on Medium »</a></p></div>]]></description>
            <link>https://medium.com/@exceldispensing/before-you-write-your-next-sql-join-statement-make-sure-to-avoid-this-mistake-b420650b820f?source=rss-f1bbe5fa9b1b------2</link>
            <guid isPermaLink="false">https://medium.com/p/b420650b820f</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Kingsley Okoye]]></dc:creator>
            <pubDate>Tue, 27 Aug 2024 05:26:56 GMT</pubDate>
            <atom:updated>2025-05-02T06:23:09.951Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[HOW TO MANAGE MANY TO MANY RELATIONSHIPS (MUST KNOW DATA MODELLING SKILL).]]></title>
            <link>https://medium.com/@exceldispensing/how-to-handle-many-to-many-relationships-must-know-data-modelling-skill-9a426a16a62f?source=rss-f1bbe5fa9b1b------2</link>
            <guid isPermaLink="false">https://medium.com/p/9a426a16a62f</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[business-analyst]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Kingsley Okoye]]></dc:creator>
            <pubDate>Sun, 25 Aug 2024 08:34:39 GMT</pubDate>
            <atom:updated>2026-06-07T13:54:54.198Z</atom:updated>
            <content:encoded><![CDATA[<p>Data modelling is an integral part of the data cleaning/integration process that cannot be overemphasized considering how it can make or mar any Business intelligence process.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/612/1*hI7TQr8eHFyXrJf4mGgWfg.jpeg" /></figure><p>A common obstacle when creating a model for any given database/data warehouse is when implementing a many to many relationship.</p><p>A many to many relationship is simply a relationship between two tables where multiple values in both tables relate to one another, not establishing data integrity constraints when creating a data model that involves many to many relationships can affect the quality of data being produced.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/780/1*iO3DjceK8Hd6cwpol8NE9Q.png" /></figure><p>Therefore, we need to implement the right data model to keep database integrity intact.</p><p>In this article, you will understand how to implement a many to many relationship and create constraints to protect the integrity of the data being produced.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/612/1*CW30qIpyS4XgGCvp7uNlvg.jpeg" /></figure><p>Before we get ahead, lets understand the 3 kinds of relationships present in relational databases today.</p><ol><li><strong>One to many relationship.</strong></li><li><strong>One to one relationships</strong></li><li><strong>Many to many relationships</strong></li></ol><p><strong>One to Many relationship.</strong></p><p>This is by far the most common relationship you will find in any database, in the relationship, one row in a table relates to multiple rows in another table.</p><p>Example: Customer table relating to the Customer orders table.</p><p>In customer table below, each CUSTOMER_ID is unique meaning that each CUSTOMER_ID in table below appears only once in the table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/789/1*TNVFOSIe4R91FWY8kjBMig.png" /></figure><p>A relationship can be established between this customers table and customer orders table, the customer table carries all information about customer’s personal details like NAME, EMAIL ADDRESS, DATE OF BIRTH etc while the customer orders table houses all information about customer orders.</p><p>By the way, you can connect with me on <a href="http://www.linkedin.com/in/ifeanyi-okoye-a857911ba">LINKED IN</a>.</p><p>View customer orders table below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/965/1*aCa01wSket-HYvAq_JAbhw.png" /></figure><p>Customer orders table carries all information about customer orders such quantity purchased, date of purchase, transaction date etc. Several rows can have carry information each CUSTOMER_ID because one customer can place multiple orders. This is how a one to many relationship works .</p><p>If any customer_id row in the customer table is duplicated then the data becomes invalid, this might be due to a data entry error or a system error and this can be fixed by properly validating the source data.</p><p><strong>One to one relationship</strong></p><p>This is the least common type of relationship you will find in any database, here one row in one table only refers to one row in another table.</p><p>Example</p><p>Customer and account relationship in banking system.</p><p>Each customer can only have one account number and each account number can only be associated with one customer.</p><p><strong>Many to many relationship</strong></p><p>As stated earlier, in this kind of relationship, multiple values in both tables relate to each other.</p><p>A good example is he relationship between STUDENTS and COURSES/SUBJECTS in a school database.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/209/1*26R9-8kzNBVejY2f1OGGAQ.png" /></figure><p>One student can enroll in multiple COURSES/SUBJECTS while one COURSE/SUBJECT can have multiple STUDENTS.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/187/1*y2Ec1L7a4aN-4QuVcNQU6g.png" /></figure><p>Hereby the best way to handle a many to many relationship is with a bridge table, this bridge table helps to connect both tables to each other while keeping data and data integrity intact.</p><p>The bridge table will carry all the information about each student and their respective courses, no matter what number of courses they enroll in we will be able to fit it into the bridge table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/778/1*WZnsFxOaImRbOp-130-EJw.png" /></figure><p>Don’t forget you connect with me on LINKED IN via → <a href="http://www.linkedin.com/in/ifeanyi-okoye-a857911ba">LINKED IN</a></p><p>Here is a step wise approach to handling this problem.</p><p>Step 1: Create a bridge table</p><pre>    CREATE TABLE STUDENT_COURSE_BRIDGE_TABLE<br>    (STUDENT_ID INT,<br>    COURSE_ID INT);</pre><p>Step 2: Insert necessary values into the bridge table based on the students enrollment.</p><pre>INSERT INTO STUDENT_COURSE_BRIDGE_TABLE<br>    VALUES<br>    (1, 201),<br>    (1, 202),<br>    (2, 201),<br>    (2,202),<br>    (2,203),<br>    (3,201);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/193/1*XB9TWLjfWp3AuTpEsPugBg.png" /></figure><p>Student with ID 1 enrolled in two COURSES/SUBJECTS, student with ID 2 enrolled in 3 COURSES/SUBJECTS while student with ID 3 enrolled in just 1.</p><p>Now we can relate these tables to each other and find more insights</p><p>With a simple SQL script, we can see all details of all students and courses they enrolled in.</p><pre><br>    SELECT STUDENT_NAME, COURSE_NAME<br>    FROM STUDENTS S<br>    INNER JOIN STUDENT_COURSE_BRIDGE_TABLE SC<br>    ON S.STUDENT_ID = SC.STUDENT_ID<br>    INNER JOIN COURSES C<br>    ON SC.COURSE_ID = C.COURSE_ID</pre><p>Result.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/211/1*ExNpr_KxsFWvv1Nq3y8z7g.png" /></figure><p>If you like my write ups, you can find out more about me via my portfolio.</p><p>Click here &gt;&gt; <a href="http://datascienceportfol.io/kingsleyokoye218"><strong>My portfolio</strong></a></p><p>We can go further and do a count of number of students enrolled in each COURSE/SUBJECT using query below.</p><pre><br>    SELECT COURSE_NAME, COUNT(S.STUDENT_ID) AS<br>    NUMBER_OF_STUDENT_ENROLLMENTS<br>    FROM STUDENTS S<br>    INNER JOIN STUDENT_COURSE_BRIDGE_TABLE SC<br>    ON S.STUDENT_ID = SC.STUDENT_ID<br>    INNER JOIN COURSES C<br>    ON SC.COURSE_ID = C.COURSE_ID<br>    GROUP BY COURSE_NAME</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/346/1*CydvDFyUtlcHFMUfHaE49A.png" /></figure><p>Finally, in order to keep the integrity of this table intact, you will need to add a constraint that prevents two rows with the exact same value from being inserted again. If duplicated rows are inserted into the bridge table this will create duplicates and this will affect the quality of our data so it must be prevented.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/193/1*XB9TWLjfWp3AuTpEsPugBg.png" /></figure><p>If a row containing STUDENT_ID 1 and COURSE_ID 201 has been inserted previously, it must not be inserted again. You have to create a composite primary key constraint on STUDENT_ID, COURSE_ID column in the STUDENT_COURSE_BRIDGE_TABLE.</p><p>SQL script to create data integrity constraint below.</p><pre><br>    --MAKE STUDENT_ID COLUMN UNABLE TO ACCEPT NULL VALUES<br>    ALTER TABLE STUDENT_COURSE_BRIDGE_TABLE<br>    ALTER COLUMN STUDENT_ID INT NOT NULL;<br><br>    --MAKE COURSE_ID COLUMN UNABLE TO ACCEPT NULL VALUES<br>    ALTER TABLE STUDENT_COURSE_BRIDGE_TABLE<br>    ALTER COLUMN COURSE_ID INT NOT NULL;<br><br>    --MAKE THE CONCATENATION OF STUDENT_ID AND COURSE_ID A PRIMARY KEY<br>    ALTER TABLE STUDENT_COURSE_BRIDGE_TABLE<br>    ADD PRIMARY KEY (STUDENT_ID, COURSE_ID);</pre><p>Now lets test our constraint by trying to insert values already present in the table for both rows.</p><pre>INSERT INTO STUDENT_COURSE_BRIDGE_TABLE<br> VALUES<br> (1,201);</pre><p>Result.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/995/1*2JExBNxPKjTXi0COz5jT2g.png" /></figure><p>Insert query was terminated because of constraint we created.</p><p>We have successfully implemented a many to many relationship and created a constraint to prevent the insertion of bad data.</p><p>Hopefully this has been helpful.</p><p>If you found this article insightful, kindly like or clap and follow for more.</p><p>Remember you can connect with me on linked in via this link → <a href="http://www.linkedin.com/in/ifeanyi-okoye-a857911ba">LINKED IN</a></p><p>Find out more about me via my <a href="http://datascienceportfol.io/kingsleyokoye218"><strong>portfolio</strong></a> as well.</p><p>God bless!!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9a426a16a62f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[HOW ANY BUSINESS CAN START KEEPING TRACK OF THEIR BUSINESS PROFITABILITY WITH SQL.]]></title>
            <link>https://medium.com/@exceldispensing/how-any-business-can-start-keeping-track-of-their-business-profitability-with-sql-big-query-38c7aaf34d6e?source=rss-f1bbe5fa9b1b------2</link>
            <guid isPermaLink="false">https://medium.com/p/38c7aaf34d6e</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[business-analysis]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[business-development]]></category>
            <dc:creator><![CDATA[Kingsley Okoye]]></dc:creator>
            <pubDate>Sat, 24 Aug 2024 18:00:37 GMT</pubDate>
            <atom:updated>2024-08-26T16:46:57.174Z</atom:updated>
            <content:encoded><![CDATA[<p>I am of the opinion that if some large scale business decided to do an in-depth profit analysis of their business, they would either be:</p><ol><li>Wowed</li><li>Disappointed</li><li>Discover that a part of the business has been taking a huge chunk of the expenses and has not be justifying the expenses with revenue/profit.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/612/1*k1ZypqmP55OZoQTYmepfgQ.jpeg" /></figure><p>In this write up, I’ll explaining how you can start keeping track of business profitability using data gotten from your business.</p><p>By the end of this article, you will be able to easily calculate how profitable a business is as far as you have access to the business data and you will be able to create informative visuals pertaining to your business profit like this one below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8zB5h3nHDHSQST9b80-SlA.png" /></figure><p>For this write up, I’ll be using 3 fictitious datasets:</p><ol><li>Online Sales Table</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/901/1*Lo2gH7J-BFKbF1Jks_W8kw.png" /></figure><p>This table represents sales transactions for each purchase made from the business, so this is all about revenue generated by the business.</p><p>2. Marketing Spend Table</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/306/1*66DMZ2ecP9XUoikzflx15g.png" /></figure><p>This table represents how much was spent on offline and online marketing. This is an expense table.</p><p>3. Staff Monthly Wage Table</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/268/1*0ZIZkU5s6jEOMWfDeOw_mQ.png" /></figure><p>Represents Staff monthly wage spend. This is also an expense table</p><p>GOAL: Track profitability for the business.</p><p>The first thing we need to do is aggregate total revenue and total spend based on each table.</p><p>Let’s start with the Online Sales table. Script below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JHUIefOFxFxZsxia6XqFyQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/362/1*8bYmDb0jIVGaJP85DR1c3A.png" /></figure><p>Next we need to do the same for marketing spend and monthly wage spend.</p><p>Remember the marketing spend table has online spend column and offline spend column so we must add both together for each row to get total spend. Recheck below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/306/1*66DMZ2ecP9XUoikzflx15g.png" /></figure><p>Query to calculate monthly marketing spend below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/770/1*oc_AX-eCZEINf1VjbzQhdw.png" /></figure><p>Result.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/481/1*Z9lQdL3b0FTGMLvoBUkdXQ.png" /></figure><p>Next we calculate the monthly wage spend by month.</p><p>Query below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/632/1*RgvS1JRWKqX3M1yRjkJL6A.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/280/1*F_ff3bGcCAr4S8bl4Xm-jQ.png" /></figure><p>By the way, you can connect with me on linked in via this link → <a href="http://www.linkedin.com/in/ifeanyi-okoye-a857911ba">LINKED IN</a></p><p>Let’s continue.</p><p>Now that we have gotten the aggregated amount for each table, we need to connect all created queries together using JOINS on a common field (MONTH) and the deduct all expenses from revenue.</p><p>Query below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/715/1*wKm1oiklo1isOzSJn3PJqw.png" /></figure><p>Result</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/697/1*PleYy29XSdPgbUHUmhRfmA.png" /></figure><p>Above table shows us everything already.</p><p>MONTHLY REVENUE</p><p>MONTHLY MARKETING SPEND</p><p>MONTHLY WAGE SPEND</p><p>Next we SUM the MONTHLY MARKETING SPEND AND MONTHLY WAGE SPEND and then deduct both columns from MONTHLY REVENUE to get TOTAL MONTHLY PROFIT.</p><p>Query below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rVBaiCoOQr4Za7RFDP3Hwg.png" /></figure><p>Result</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/855/1*lxNANJ5Oid1mgbZN43fBhg.png" /></figure><p>We can make a quick visualization of our monthly profit using BIGQUERY chart section.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1uBqs-YSZE8SDDrgtMY2OQ.png" /></figure><p>We can also view our MONTLY profit percentage using a line chart as seen below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1pmsAJs0WnYCeiWDIaQCrg.png" /></figure><p>Summary</p><p>We solved this problem by aggregating monthly revenue and expenses for each table and deducting all expenses from revenue to get profit.</p><p>While this can be more complicated with real world data, it can always to efficiently calculated as far as the data is available.</p><p>All that is required is to bring all required data into one environment like a data lake or data warehouse where these data can be connected to one other and then aggregated.</p><p>Thank you for reading.</p><p>If you found this article helpful, kindly give it a clap and follow for more.</p><p>Any contribution is well appreciated and can be typed in the comment section.</p><p>Remember to connect with me on LINKED IN via this link → <a href="http://www.linkedin.com/in/ifeanyi-okoye-a857911ba">LINKED IN</a></p><p>God bless!!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=38c7aaf34d6e" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>