<?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 Andrei Kushniarou on Medium]]></title>
        <description><![CDATA[Stories by Andrei Kushniarou on Medium]]></description>
        <link>https://medium.com/@andrewkushnerov?source=rss-27f59dbe18f4------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/2*Wx0VPbfMllduhz7mzSbHFQ.jpeg</url>
            <title>Stories by Andrei Kushniarou on Medium</title>
            <link>https://medium.com/@andrewkushnerov?source=rss-27f59dbe18f4------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 04:26:31 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@andrewkushnerov/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[Cutting LLM tokens on big spreadsheets]]></title>
            <link>https://andrewkushnerov.medium.com/cutting-llm-tokens-on-big-spreadsheets-b633c33fafea?source=rss-27f59dbe18f4------2</link>
            <guid isPermaLink="false">https://medium.com/p/b633c33fafea</guid>
            <category><![CDATA[claude]]></category>
            <category><![CDATA[llm]]></category>
            <category><![CDATA[mcp-server]]></category>
            <category><![CDATA[google-sheets]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Andrei Kushniarou]]></dc:creator>
            <pubDate>Fri, 25 Sep 2026 00:00:48 GMT</pubDate>
            <atom:updated>2026-09-28T11:26:50.233Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*kIxJRx4tTVADLdTv.png" /><figcaption>Cutting LLM tokens on big spreadsheets. Image by the author</figcaption></figure><p>I am a data engineer in e-commerce. I often need to work with big Google Sheets tables: orders, customers, reports, financial data. Tens and hundreds of thousands of rows are normal.</p><p>I wanted Claude to work with these tables: read, write, calculate. And I wanted the data to go nowhere except Google. I did not find anything ready like this, and third-party MCP servers were not an option because of data privacy. So I made my own, <a href="https://github.com/andrewkushnerov/gsheets-mcp">gsheets-mcp</a>. It is a local MCP server. It works directly with the Google Sheets API and does not send data anywhere else. I built it for myself and made it public.</p><p>For testing I use a synthetic Amazon Settlement report with 50 thousand rows. It has the same structure as a real one, but the products and IDs are made up. <a href="https://docs.google.com/spreadsheets/d/1NkN_3IV_KIlHmruuNs-8BdKvStCs-LqTRJ-6FemapTI/edit">The sheet is public</a>, so you can repeat the question. The question is: “Where did the money go? Break it down by amount type.” This is a common task in e-commerce: to understand where the money went between the sales and the payout.</p><p>Below are the steps I went through and what each of them gave in tokens.</p><h3>Why you can’t just give the table to the model</h3><p>The test file has 50,009 rows and 24 columns. In the most compact form (TSV) it is 5.8M tokens, in “pretty” JSON almost 19M. The context window of Opus 5.5 is 1M. But even if it fit, there are three more problems:</p><blockquote><em>Error: result (965,076 characters across 5,003 lines) exceeds maximum allowed tokens. Output has been saved to …</em></blockquote><ol><li>Money. Input tokens for Opus 5.5 cost $4 per million. Reading the tab once costs about $23, and an agent re-reads its context at every step.</li><li>Arithmetic. Even if everything fit, adding up 50 thousand amounts “in its head” is not something I would trust with accounting.</li></ol><p>One detail surprises almost everyone: in tables like this, one token is about 1.7 characters, not 4 like in normal text. Order IDs, dates and amounts are cut into many small tokens.</p><blockquote><strong><em>How I measured.</em></strong><em> One file, one question, every release of the server from v0.1 to v0.4.4. Each release ran against a copy of the sheet in memory, so the output is byte for byte what the model gets. Tokens were counted with the Claude Opus 5.5 tokenizer. Everything can be reproduced with one command from the repo: </em><em>python bench/tokens.py.</em></blockquote><h3>Starting point: v0.1</h3><p>The first version (August 10) returned the sheet as JSON with indentation. To answer the question, the model needs two columns, the amount type and the amount, for all rows. With the most careful approach this is 11 calls and <strong>1.83M tokens</strong>. And if the model just “reads the sheet”, it gets 995K tokens, and only the first 5,000 rows of 50,009. That is 10% of the data.</p><h3>1. Less data: format and pages</h3><h3>Format</h3><p>The same 200 rows in different formats, tokens per row:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_MxghcGY-JwHnm2SHSolTw.png" /></figure><p>What this shows:</p><ol><li>Repeating keys costs the most. JSON records carry 24 column names in every row: 303 tokens against 125 for the same data as an array.</li><li>Indentation is second. indent=2 puts every cell on its own line: 199 against 125. The fix is one argument when you serialize.</li><li>Between flat formats the difference is small. CSV, TSV and a compact JSON array are within 8% of each other. The “JSON or CSV” argument is almost about nothing. What matters is to not repeat keys and not indent.</li></ol><h3>Pages</h3><p>v0.1 downloaded the whole sheet from Google (13 MB) and gave the model the first 5,000 rows, with a note that the rest did not fit. v0.2 asks Google only for the needed page (1.3 MB, 0.7 seconds) and tells the model which offset to continue from.</p><p>But to be honest, pages do not reduce the total. It is still the same 11 calls. And rows are the wrong unit. A page of 5,000 rows of this sheet is 579K tokens, 23 times more than the Claude Code limit. About 216 rows fit under the limit.</p><p>Result of the step: a row got cheaper from 199 to 116 tokens (−42%), and the whole question went from 1.83M to 1.03M tokens, <strong>1.8 times smaller</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*oNMWXMukHoCRkyZK.png" /><figcaption>Tokens per question: v0.1 - 1.83M, v0.2 - 1.03M</figcaption></figure><p>Takeaway: do not underestimate basic and simple optimizations, they noticeably reduce the volume. But it is still only 1.8 times. The sheet is still almost 6M tokens. Format does not solve the volume problem.</p><h3>2. Preview and reading by columns</h3><h3>Preview</h3><p>Before v0.4, gsheets_list_sheets returned only tab names and grid sizes: 100-130 tokens. Grid size is not data size: an empty tab &quot;has&quot; 1,000 rows. To see the columns, the model had to read: 1,381 tokens if it thought to take a piece like A1:X11, or 579K if it read the whole sheet.</p><p>Since v0.4.0, one call describes every tab: the letter, name and type of each column, three sample rows, the real number of rows, and a flag if there is a totals row under the table. For our sheet it is <strong>633 tokens</strong> (340 without sample rows). It is three requests to Google for the whole document, no matter how many tabs it has.</p><pre>amazon_settlement_test_50k	gid=517145915	rows~50010<br>  A	settlement-id	number<br>  …<br>  G	transaction-type	text<br>  H	order-id	text<br>  …<br>  M	amount-type	text<br>  N	amount-description	text<br>  O	amount	number<br>  …</pre><p>To be honest about the limits: types are detected from the first 10 rows. Here adjustment-id and promotion-id are shown as empty, because the first rows are all orders and refunds come later.</p><p>For the full question the preview alone changes nothing: 1.03M =&gt; 1.03M. Its value is different. The model knows the column letters and the scale. It is a map, and the next steps do not work without it.</p><h3>Only the columns you need</h3><p>columns: [&quot;M&quot;, &quot;O&quot;] is one request to Google, and the columns are joined back into rows on the server. A row costs 11.6 tokens instead of 116, 10 times less. The whole question: 1.03M =&gt; 580K, <strong>another 1.8 times smaller</strong>.</p><p>But it is still 11 calls of about 58K tokens each, every one bigger than the Claude Code limit. And the model still has to add up 50,009 numbers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*yWUQsObhFpwM5gGT.png" /><figcaption>Tokens per question: v0.1 to v0.4.1, down to 580K</figcaption></figure><p>Takeaway: giving the model an overview of the data is very important, the next requests become much more efficient. The first look at the document is 633 tokens instead of 579K blind, one call instead of two. But the saving is not the main thing, the map is. Without exact column names the model cannot ask for columns or for an aggregate.</p><h3>3. Aggregation on the server: the answer instead of rows</h3><p>gsheets_aggregate does grouping, metrics (count, count_distinct, sum, min, max, avg), a where filter and a limit. Only the named columns are read from Google (all rows), and only the groups go back to the model. One call:</p><pre>{&quot;sheet_name&quot;: &quot;amazon_settlement_test_50k&quot;,<br> &quot;group_by&quot;: [&quot;amount-type&quot;],<br> &quot;metrics&quot;: [{&quot;column&quot;: &quot;amount&quot;, &quot;fn&quot;: &quot;sum&quot;}]}</pre><pre>scanned: 50009 rows<br>groups: 9, shown: 9, sorted by sum(amount) desc<br><br>amount-type	sum(amount)<br>ItemPrice	246220.28<br>…<br>ItemFees	-87170.27</pre><p><strong>154 tokens</strong> instead of 580K, about 3,760 times fewer. It takes about a second: 2 requests to Google, the server read 100 thousand cells, and the model read none of them.</p><p>What is inside:</p><ul><li>It counts like a person. Empty cells do not count, and a totals row with an empty order id is not an order.</li><li>It reads numbers the way the sheet shows them: $1,240.50, (340), 1.234,56 €, 12%.</li><li>A bug story. In 0.4.4 I found that -$87 (the minus goes before the dollar sign) was read as text, and the sums silently lost refunds. The total still looked believable. That is the most dangerous kind of bug.</li><li>Mixed percentages. If one group mixes 12% and 0.12, you get a warning instead of a quietly wrong sum.</li><li>No HAVING, OR or nested groups, on purpose. A model fills in a small schema correctly.</li></ul><p>A live run (Opus 5.5 in Claude Code, the public sheet): the preview and 5 aggregates, 1,878 tokens of results, $0.39. Here is what the model answered (shortened):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8M7b3DKVpsZz0S2nNfSWtQ.png" /></figure><p>The rows add up to the payout in the report, to the cent.</p><p>Where the tokens go now, for the whole conversation:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*t3feEPU7kMMWeTAZxw4rAg.png" /></figure><p>When the data is out of the way, you pay for what you want to pay for: the model’s thinking.</p><p>And the result of all steps: <strong>1.83M =&gt; 787 tokens, 2,331 times fewer.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*pVz5vArSJxxzyjMM.png" /><figcaption>Tokens per question: v0.1 to v0.4.2, down to 787</figcaption></figure><p>Takeaway: tools for processing table data give the biggest saving. The main thing is to not send the data at all, instead of compressing it. The model needs the answer, not the rows. And it is better to calculate next to the data: an LLM is an expensive and unreliable calculator.</p><h3>The hidden cost: tools in every conversation</h3><p>Tool descriptions sit in every conversation, even if there are no tables in it: 3,708 tokens in v0.1, 6,473 in v0.4.4. Aggregation alone added about 1,000. In read-only mode it is 3,310. It is a good deal: plus a thousand tokens per conversation against millions per question. But descriptions are a prompt, so keep them short.</p><h3>Why not pandas or Google SQL</h3><p>I had ideas to make a wrapper around pandas, or to let the model run SQL directly in Google Sheets. I decided to keep everything as simple as possible.</p><p>Google’s query language, the one behind =QUERY(), says in its own documentation: &quot;In case of mixed data types in a single column, the majority data type determines the data type of the column for query purposes. Minority data types are considered null values.&quot; So in a &quot;dirty&quot; column some values silently become null, and the total is quietly wrong. It is the same kind of bug as with -$87.</p><p>pandas inside the server is a heavy dependency. And to make it flexible, I would have to let the model run code next to cells from someone else’s sheet. That is a direct road to prompt injection.</p><p>So the server does grouping and filtering. For complex analysis you export a CSV and work in pandas or DuckDB, where code already runs.</p><h3>What’s next</h3><p>Charts are already there ( gsheets_add_chart): the model can draw the result in the sheet right away. Next in the plans:</p><ol><li>Search in the data: a filter when reading, the same where as in the aggregate. &quot;All rows of order X&quot; is 28 rows and 3,409 tokens instead of 5.8M.</li><li>Reconcile two tabs by a key. For example, shop orders against the settlement: the server returns only the differences to the model. A classic e-commerce task.</li><li>Pages by tokens, not by rows. About 216 full rows of this sheet fit under the Claude Code limit.</li><li>Do not repeat constants. In this report settlement-id is the same in all rows, 6 columns are empty, and merchant-order-id repeats order-id in 98.6% of rows. Without them a row is 83 tokens instead of 116 (−29%).</li><li>Compress or mark data that means nothing for the analysis: emails, phone numbers, addresses, long IDs. To count and group, the model does not need the address itself. A short stable label like c_0412 is enough: one address, one label. Fewer tokens, and personal data does not go to the model.</li><li>A smarter preview. Look not only at the start of the sheet but also at the middle and the end, and show the values of small columns so filters hit exactly.</li><li>Aggregates by time: by day, week and month.</li><li>Median and percentiles in the aggregate. The average order value lies, p50 and p90 do not.</li><li>Write the aggregate result straight into a new tab. The report is built on the server, the data never goes through the model, and the model only sees “done”.</li><li>Export to a temporary file, for agents that can run code.</li><li>A Python package, finally. So you can install it with one command, without cloning the repo and putting three paths into the config.</li><li>Something else? Send ideas to <a href="https://github.com/andrewkushnerov/gsheets-mcp/issues">GitHub issues</a></li></ol><p><em>Originally published at </em><a href="https://kushniarou.com/articles/cutting-llm-tokens-on-big-spreadsheets"><em>https://kushniarou.com</em></a><em> on September 25, 2026.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b633c33fafea" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to send notifications to Slack with Python]]></title>
            <link>https://andrewkushnerov.medium.com/how-to-send-notifications-to-slack-with-python-c6c27c2eef8f?source=rss-27f59dbe18f4------2</link>
            <guid isPermaLink="false">https://medium.com/p/c6c27c2eef8f</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[api]]></category>
            <category><![CDATA[bots]]></category>
            <category><![CDATA[slack]]></category>
            <category><![CDATA[automation]]></category>
            <dc:creator><![CDATA[Andrei Kushniarou]]></dc:creator>
            <pubDate>Wed, 13 Aug 2025 12:59:59 GMT</pubDate>
            <atom:updated>2025-08-13T12:59:59.386Z</atom:updated>
            <content:encoded><![CDATA[<p>Sending messages about various events from Python to Slack can be very useful. My Python Scripts sending every day messages about various anomalies, competitor activity, price changes, and daily reports on advertising and sales statistics to Slack.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2XHXPoY23Pto8rWitOzEgw.png" /><figcaption>How to send notifications to Slack with Python. Collage by the Author</figcaption></figure><p>Also it’s very convenient to create a group in Slack and add only the necessary employees to it. Let’s figure out how to set up the Slack App so that you can easily send messages.</p><h3>Table of Contents:</h3><p>1. Create a Slack App and Get Credentials<br>2. Slack App customization<br>3. Set the scope to send a message to the Slack<br>4. Installing the application in your Slack Workspace<br>5. Check the app in Slack and add it to an open or closed Channel<br>6. Create a Python script and send messages to Slack<br>7. Preparing the Slack App to add to another Slack Workspace.<br>8. Receiving an OAuth token for a Slack app in another Workspace</p><h3><strong>Create a Slack App and Get Credentials</strong></h3><p>Regardless of whether you will send messages only to your own or to another workspace, first you need to create your own Slack workspace, where you will be the administrator (if you are already an administrator in some workspace, then skip this step).</p><p>Creating a Workspace is free and can be done via this link: <a href="https://slack.com/get-started?entry_point=help_center#/createnew">https://slack.com/get-started?entry_point=help_center#/createnew</a></p><p>Next, we need to create a Slack App and get credentials. Got to <a href="https://api.slack.com/apps">https://api.slack.com/apps</a> and click <strong>Create New App</strong>. Then click to create the <strong>From Scratch application</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7nolVQalbvvKp5p7IhE4SQ.png" /><figcaption>Create a new Slack App. Screenshot by the author.</figcaption></figure><p>Set the App Name, for example, <strong>Notification Slack Bot</strong> and select the base workspace. The base workspace should be selected in any case, even if you are going to send messages to another workspace (the authorization process is just a little more complicated, especially if you are not an administrator there).</p><p>Now we are immediately redirected to the <strong>App Credentials</strong> page where we have all the basic data we need: App ID, Client ID and Client Secret.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*k8A6qbNxMQ2K038UgkLl4Q.png" /><figcaption>Basic information of Slack App. Screenshot by the author.</figcaption></figure><h3><strong>Slack App customization</strong></h3><p>On the <strong>Basic Information</strong> page, scroll down a bit and find the <strong>Display Information</strong> block. You can write a description and upload a picture (you need a PNG max 512 by 512 pixels) and change the color. Don’t forget to save the changes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*R6gr35AmaO2sNJs04L-YCQ.png" /><figcaption>Slack App customization. Screenshot by the author.</figcaption></figure><h3>Set the scope to send a message to the Slack</h3><p>Go to the <strong>OAuth &amp; Permissions</strong> page, find the <strong>Bot Token Scopes</strong> block, click Add an <strong>OAuth Scope</strong> and select <strong>chat:write</strong>. This is necessary so that the bot has the rights to send messages.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4O7RaeZQ587VuknuvZ_DTg.png" /><figcaption>Slack App scopes. Screenshot by the author.</figcaption></figure><p>The added scope should appear in the list.</p><h3>Installing the application in your Slack Workspace</h3><p>Now we can install the Slack App in the Workspace. This method can only be used to install in the Workspace that was specified when creating the Slack App and where you are the administrator! (How to add to another workspace, please read below).</p><p>To do this, go to the <strong>Install App</strong> section and simply click <strong>Install to &lt;Your workspace&gt;.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Js_eEoBIqWi0KVoZRU20ag.png" /><figcaption>Installing the application in your Slack Workspace. Screenshot by the author.</figcaption></figure><p>And then click <strong>Allow.</strong></p><p>After permission, we will be redirected to the <strong>Installed App Settings </strong>page and we will be able to get an <strong>OAuth Token</strong>. Copy it, we will need it in the next step.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6eekaxnkCJ1uFUQtV9T7uA.png" /><figcaption>Installed Slack App Settings. Screenshot by the author.</figcaption></figure><h3><strong>Check the app in Slack and add it to an open or closed Channel</strong></h3><p>If we open Slack, we will see our bot in the Apps list. Great, then everything is ok and it has been added.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E81EI35SqHDHjKXnuHwo_A.png" /><figcaption>Installed Slack App in Slack workspace. Screenshot by the author.</figcaption></figure><p>Now we need to create a group and add the app there so that it can write messages there.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pX7LVnQUfHAOpXWJz1hrsA.png" /><figcaption>Creation a new channel (group) in Slack workspace. Screenshot by the author.</figcaption></figure><p>When you create a group, there are two options for the group, open and closed. Open starts with # and anyone on Slack can join, while closed starts with a lock and can only be invited to.</p><p>After creating a group, you need to click on its name and in the window that opens, go to the <strong>Integrations tab.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SYVc7GVaYxPj4z6r8_J03Q.png" /><figcaption>Integration tab of Slack channel. Screenshot by the author.</figcaption></figure><p>There is an <strong>Apps block</strong>, click <strong>Add an App</strong> and select our Slack application.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*K6d2iwI_d6WB8Ugk3nmmyg.png" /><figcaption>Add a new app to Slack channel. Screenshot by the author.</figcaption></figure><p>Now the app appears in the Apps list in the group. Great.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*v-lqzX8Slt2AgAG7cgKWDw.png" /><figcaption>Slack apps list added in Slack channel. Screenshot by the author.</figcaption></figure><p>Now an important point, if the group is closed. You also need to click on the group name to open a pop-up and in the first tab <strong>About</strong> scroll to the very bottom and find the<strong> Channel ID</strong> (it is displayed in gray).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7TnpDxjb3Rdnufum1kF9hA.png" /><figcaption>Channel ID of Slack channel. Screenshot by the author.</figcaption></figure><p>For example, in my case, Channel ID: C099UJZEL83. Copy it.</p><h3>Create a Python script and send messages to Slack</h3><p>To send messages to Slack, we only need python and the standard library requests. I’ll make a universal function that receives a token, channel identifier and a message to send.</p><pre>import requests<br><br>def send_slack_message(token, channel, message):<br>    &quot;&quot;&quot;<br>    Send message to Slack channel using token and channel<br>    &quot;&quot;&quot;<br><br>    #Define header w auth<br>    headers = {<br>        &#39;Authorization&#39;: f&#39;Bearer {token}&#39;,<br>        &#39;Content-Type&#39;: &#39;application/json&#39;<br>    }<br>    <br>    #Define data - channel and message<br>    data = {<br>        &#39;channel&#39;: channel,<br>        &#39;text&#39;: message<br>    }<br>    <br>    #Send query to Slack API<br>    response = requests.post(<br>        &#39;https://slack.com/api/chat.postMessage&#39;,<br>        headers=headers,<br>        json=data<br>    )<br>    <br>    # Check response<br>    result = response.json()<br>    if result.get(&#39;ok&#39;):<br>        print(&#39;Message sent successfully&#39;)<br>    else:<br>        print(&#39;Failed to send message:&#39;, result.get(&#39;error&#39;))</pre><p>The token is inserted into the header using Bearer authorization, and the channel and message into the data. The request is sent to the Slack API. It’s that simple.</p><p>To send the message itself using the function, first define the OAuth token and the message. (Paste the OAuth token that you copied from the <strong>Installed App Settings</strong> page).</p><pre>#Define access token and message<br>access_token = &#39;xoxb-8649375527331-....&#39; <br>message = &#39;Hello from Python! I am a Slack notification bot!&#39;</pre><p>Now let’s send a message to our bot in an open group. As I wrote above, if the group is open, we simply write its name starting with #. The main thing is that our Slack App is added to this group.</p><pre>#Send message to open channel<br>channel = &#39;#notification-group-open&#39; <br>send_slack_message(access_token, channel, message) </pre><p>The message came to the group! It works!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zFik_GyMeqfJMmPnWYPj5A.png" /><figcaption>A new message from the Slack bot. Screenshot by the author.</figcaption></figure><p>To send to a closed group, everything is similar, only you need to specify not the name of the group, but its ID, which we found in the previous step.</p><pre>#Send message to closed channel<br>channel = &#39;C099UJZEL83&#39; <br>send_slack_message(access_token, channel, message) </pre><h3>Preparing the Slack App to add to another Workspace.</h3><p>To send messages to another Slack Workspace, everything is similar, but it will take a few more steps to get an OAuth Access token.</p><p>Go to the <strong>Manage Distribution</strong> item in your Slack App settings and just below in this section there is a block called <strong>Share Your App with Other Workspaces</strong>. You need to have green checkmarks in all four items.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XWZA9qiev83WH_E74kqdMQ.png" /><figcaption>Preparing the Slack App to add to another Workspace. Screenshot by the author.</figcaption></figure><p>Immediately decide with <strong>Remove Hard Coded Information</strong>, to do this, click on this item and check the box I<strong>’ve reviewed and removed any hard-coded information</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AUe4HaNabfe71Z8W6LknZw.png" /><figcaption>Remove Hard Coded Information option. Screenshot by the author.</figcaption></figure><p>Now we need to decide on <strong>Add OAuth Redirect URLs</strong>. Click <strong>Set Up Redirect URLs.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*k_z9fzGGvH530bR9MFZFbA.png" /><figcaption>Add OAuth Redirect URLs. Screenshot by the author.</figcaption></figure><p>Go to the <strong>Settings page</strong> and find the <strong>Redirect URLs</strong> block and click <strong>Add New Redirect URL</strong>.</p><p>What is this URL? This is the address to which the workspace administrator will be redirected, who will add your Slack APP to their workspace. The address to which it will be redirected will contain the code you need in the line.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*liUHE6xiVC3iFrWSqcv0ZA.png" /><figcaption>Redirect URLs list. Screenshot by the author.</figcaption></figure><p>If you are making an application that will be distributed and work automatically, you will need to make a controller that will catch this parameter and receive the OAuth Token.</p><p>But here I will show a simple way, if you have a connection with the administrator, In fact, you can enter any URL there and ask the administrator to copy and send you the link immediately after adding.</p><p>I will enter the address of my corporate site with a non-existent page.</p><p>I will ask the slack workspace admin who will add my Slack APP to himself, to copy and send me the link where he was redirected. But in the future I can make a controller there that will receive oauth automatically.</p><p>Now we return to the <strong>Manage Distribution</strong> page and find the <strong>Sharable URL</strong> there.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aud-rj167KZuMGZBxNn_Eg.png" /><figcaption>Manage Distribution page of Slack App. Screenshot by the author.</figcaption></figure><p>This is the link we send to the Slack administrator, who should add your Slack App. In my case, it looks something like this: <a href="https://slack.com/oauth/v2/authorize?client_id=864937552%E2%80%A6.&amp;scope=chat:write&amp;user_scope=">https://slack.com/oauth/v2/authorize?client_id=864937552….&amp;scope=chat:write&amp;user_scope=</a></p><p>Next, he should send you a link to which the administrator was redirected after adding your Slack App, it looks something like this: <a href="https://sellerflux.com/slack_auth?code=3695031%E2%80%A6">https://sellerflux.com/slack_auth?code=3695031…</a></p><p>You need this code, copy it.</p><h3>Receiving an OAuth token for a Slack app in another Workspace</h3><p>Next, we write a script that will use the data of our Slack App from the Basic Information page and the received code to make a request to the Slack API and receive an OAuth Token.</p><p>We make a Python Script</p><pre><br>import requests <br><br>#Insert your cliend ID / Client secret from Basic Information Slack APP Page<br>client_id = &#39;&lt;INSERT CLIENT ID&gt;&#39;<br>client_secret = &#39;&lt;INSERT CLIENT SECRET&gt;&#39;<br><br>#Insert your redirect URL <br>redirect_uri = &#39;https://sellerflux.com/slack_auth&#39;<br><br>#Insert code, that you received from workspace admin. The code is valid for only a few minutes! <br>code = &#39;&lt;INSERT CODE&gt;&#39;<br><br>response = requests.post(<br>    &#39;https://slack.com/api/oauth.v2.access&#39;,<br>    data={<br>        &#39;client_id&#39;: client_id,<br>        &#39;client_secret&#39;: client_secret,<br>        &#39;code&#39;: code,<br>        &#39;grant_type&#39;: &#39;authorization_code&#39;,<br>        &#39;redirect_uri&#39;: redirect_uri<br>    }<br>)<br><br>#Uncomment to see all response<br>#print(response.text)<br><br># Check response from Slack<br>data = response.json()<br>if data.get(&#39;ok&#39;):<br>    print(data)<br>    access_token = data[&#39;access_token&#39;]<br>    print(&#39;Got your access token:&#39;, access_token)<br>else:<br>    print(data)<br>    print(&#39;Something went wrong, check:&#39;, data[&#39;error&#39;])<br></pre><p>If everything is ok, you will receive an Oauth Token</p><pre>Get your access token: xoxb-573414...</pre><p>Use it to send messages to Slack as well as to your Workspace. Don’t forget to ask the workspace admin to add your Slack App to a public or private group and tell them the group name (if the group is public) or the Slack Group ID (if the group is private).</p><h3><strong>Useful Links</strong></h3><ol><li><strong>Slack, create a new workspace: </strong><a href="https://slack.com/get-started?entry_point=help_center#/createnew">https://slack.com/get-started?entry_point=help_center#/createnew</a></li><li><strong>Slack, create a new Slack App:</strong> <a href="https://api.slack.com/apps/">https://api.slack.com/apps/</a></li><li><strong>My Article: How to send notifications to Telegram with Python. </strong><a href="https://medium.com/@andrewkushnerov/how-to-send-notifications-to-telegram-with-python-9ea9b8657bfb">https://medium.com/@andrewkushnerov/how-to-send-notifications-to-telegram-with-python-9ea9b8657bfb</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c6c27c2eef8f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Install N8N on Your Server for $5/mo]]></title>
            <link>https://andrewkushnerov.medium.com/how-to-install-n8n-on-your-server-for-5-mo-7b57c2f575b1?source=rss-27f59dbe18f4------2</link>
            <guid isPermaLink="false">https://medium.com/p/7b57c2f575b1</guid>
            <category><![CDATA[n8n]]></category>
            <category><![CDATA[workflow-automation]]></category>
            <category><![CDATA[self-hosting]]></category>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[no-code]]></category>
            <dc:creator><![CDATA[Andrei Kushniarou]]></dc:creator>
            <pubDate>Sat, 09 Aug 2025 18:09:00 GMT</pubDate>
            <atom:updated>2025-08-09T22:13:24.601Z</atom:updated>
            <content:encoded><![CDATA[<p>Today I’ll show how to install and set up your own fully functional N8N for just $5 per month. N8N is very popular now because it lets you build decent no-code automations and has a very rich library of free automation templates. With the rise of AI tools, N8N has become especially widespread.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aCLv6F34pZSgUbntXAviOQ.png" /><figcaption>How to Install your own N8N on your server for $5 a month. Collage by the Author</figcaption></figure><p>You can use the official N8N Cloud, but the price starts at $20 per month (<a href="https://n8n.io/pricing/">https://n8n.io/pricing/</a>). The smallest plan is also limited to only five active workflows and 2.5k executions per month, which is not much at all.</p><p>Fortunately, N8N is an open-source project that allows free use for your own purposes (<a href="https://github.com/n8n-io/n8n">https://github.com/n8n-io/n8n</a>). You can run the Community version locally on your computer (<a href="https://medium.com/@andrewkushnerov/installing-your-own-local-n8n-on-macos-ddc9dbc85cd3">how to install N8N locally on Mac, please read my article</a>) or install it on a server.</p><p>Moreover, a self-hosted setup does not have the limits that exist in N8N Cloud. You can run any number of workflows and steps inside them. You only pay for your hosting.</p><p>So by installing N8N using my guide, you will save minimum from $150 per year (Starter plan) up to $450 per year (Pro plan).</p><p>As a true programmer, I prefer pure Python, but I’ve already built a number of fairly complex workflows in N8N for my clients and was impressed by its features and how it can pass execution along a chain. Now I use it for complex automation chains, especially for creating LLM agents.</p><h3><strong>Table Of Contents</strong></h3><ol><li>Choose server specs and pick a hosting provider.</li><li>Buy a server</li><li>Link your domain</li><li>Continue to configure the server</li><li>Install and run Docker and N8N</li><li>Check our N8N in the browser and start using it</li></ol><h3>1. Choose server specs and pick a hosting provider.</h3><p>The server requirements for N8N are not high. 2–4 GB of RAM, 2 CPU cores, and a few dozen gigabytes of free disk space are enough.</p><p>We need a cheap and reliable hosting provider. For several years I’ve used <a href="https://www.hetzner.com/">Hetzner</a>. It’s one of the largest hosting providers in the world, with data centers in the US and Europe.</p><p>Compared to Amazon Web Services, Hetzner’s price is shocking.</p><blockquote>At the time of writing, the smallest server on Hetzner:<strong>(</strong><a href="https://www.hetzner.com/cloud">https://www.hetzner.com/cloud</a>) <br><strong>Cloud Server CAX11 in Germany (ARM)<br></strong>* 2 cores<br>* 4 GB RAM<br>* 40 GB disk<br>* 20 TB traffic<br>=<strong> $4.59 per month (or €4.66 with VAT for Portugal)<br></strong>+ IP address</blockquote><p>A similar Cloud CPX11 in the US will cost $5.59.</p><blockquote>Let’s compare with a similar AWS server:<br>Cloud Server t4g.medium (<a href="https://aws.amazon.com/ec2/pricing/on-demand/">https://aws.amazon.com/ec2/pricing/on-demand/</a>)<br>* 2 cores<br>* 4 GB RAM<br>=<strong>$24.192 per month<br></strong>+ disk for extra money (about $1 per 10 GB)<br>+ traffic for extra money<br>+ IP address</blockquote><p>In the end, the same kind of server on AWS will cost about $30 per month, which is <strong>SIX times more expensive.</strong> Yes, you won’t get a 99.99% SLA, and if you don’t need all the AWS infrastructure features and all that “enterprise,” Hetzner is a great choice.</p><p>In my opinion, big clouds with their server pricing have gone off track. Once AWS was a good alternative; now for small projects it’s easier to run on an alternative host (like Hetzner) for much less money (several times less!).</p><h3><strong>2. Buy a Server</strong></h3><p>Create an account on Hetzner and click <strong>Create Server</strong>.</p><p>First, choose a region.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0VQqgtNAnrmypmEBFoQqOw.png" /><figcaption>Create a new server on Hetzner. Screenshot by the author.</figcaption></figure><p>I live in Portugal, so I pick Germany. Choose what’s closest to your region.</p><p>Next, choose <strong>Ubuntu 24.04</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*O1rpQun8sVbw9P1VWk7-EQ.png" /><figcaption>Choosing a image os on Hetzner. Screenshot by the author.</figcaption></figure><p>Next we need to add an SSH key.</p><p>To generate an SSH key on Mac, open Terminal and run:</p><pre>ssh-keygen -t ed25519 -f ~/.ssh/my_n8n_key -C &quot;n8n root key&quot;<br>chmod 700 ~/.ssh/my_n8n_key</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GoCPJHYwhA8AnbzqMZZxpw.png" /><figcaption>Generate a ssh key on mac console. Screenshot by the author.</figcaption></figure><p>Now after generation we need to copy the public key to specify it for access to the server (the private key remains on our computer, do not show it to anyone and do not send it anywhere)</p><p>To copy the public key, execute in the console</p><pre>cat ~/.ssh/my_n8n_key.pub</pre><p>Copy the whole line, for example:</p><pre>ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIAQtsQKH7VzwAZ2eSPnwa1UjQ6DjQXQ4trBRqYqwBAD2 n8n root key</pre><p>Go back to Hetzner, click <strong>Add SSH Key</strong>, and paste the full public key line.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*z7yc_Cj0YFrsM1MxJOr4CA.png" /><figcaption>Adding publuc ssh key on Hetzner. Screenshot by the author.</figcaption></figure><p>The <strong>Backup</strong> option is up to you, but I always enable it. You can restore the server from a daily backup. It costs about +20% to the server price.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KRZEyXKMkQbey3vAWcGqdA.png" /><figcaption>Enable server backup option on Hetzner. Screenshot by the author.</figcaption></figure><p>As a last step, you can change the server name. I used <strong>my-n8n-server</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aBGNrtlwwJuMTpIuEZqDaw.png" /><figcaption>Set a server name on Hetzner. Screenshot by the author.</figcaption></figure><p>After creation, the server appears in the list. It’s already up and running.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wCuM0glsfnxmjLFH5wX8dA.png" /><figcaption>List of servers on Hetzner. Screenshot by the author.</figcaption></figure><p>I also recommend setting up a provider-level Firewall. This improves security a lot. Only the ports you allow will be reachable. Open your server in Hetzner, go to <strong>Firewall</strong>, click <strong>Create Firewall</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aUjcblW1LU0EpmYyXyEGUg.png" /><figcaption>Set up server firewall on Hetzner. Screenshot by the author.</figcaption></figure><p>In the Firewall, allow port <strong>22</strong> (SSH), and also <strong>80</strong> (HTTP) and <strong>443</strong> (HTTPS) so the server can act as a web server.</p><p>You can rename the firewall to<strong> firewall-n8n-server.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*64czWjkTz9TsFXiTlGQ6EA.png" /><figcaption>Set firewall name on Hetzner. Screenshot by the author.</figcaption></figure><p>After that, the firewall will appear in the list.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*opet4jpyM4GdHA5De9aNXA.png" /><figcaption>Server firewall page on Hetzner. Screenshot by the author.</figcaption></figure><h3>3. Link your domain</h3><p>You can run N8N by IP only, but then it will be HTTP without encryption. I don’t recommend this. On an unsafe network (like airport Wi-Fi), someone can intercept traffic, including passwords.</p><p>Now everyone uses <strong>https</strong>, when all traffic is fully encrypted, this is the de facto security standard, I will do everything correctly as it should be. The certificate can be obtained automatically during installation, you only need to bind any domain to the server.</p><p>If you already have any domain, you don’t have to buy a new one, you can create a subdomain. If you buy a domain, you can buy the cheapest zone (for example, “.<strong>bid” </strong>zone<strong> </strong>for 4 dollars with a tail, see prices here: <a href="https://cfdomainpricing.com/">https://cfdomainpricing.com/</a>)</p><p>Next, we need to bind the <strong>A record</strong> to the <strong>IP address</strong> that we received after creating the server. I keep my domains on <a href="https://cloudflare.com/">cloudflare.com</a>, this is what adding an A record for a domain looks like. You need to create a new record of type A and for the n8n subdomain, specify the IP address of the new server.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*g1xUXhVDU0NZFYdewWPwYQ.png" /><figcaption>Set up a new “A” DNS record for domain. Screenshot by the author.</figcaption></figure><p>Now if you open n8n.&lt;yourdomain&gt;.com it will point to the new server.</p><p>Overall, I highly recommend <a href="https://www.cloudflare.com/">Cloudflare</a>, it’s not only a fast DNS but also excellent protection against DOS attacks and bots on your server even on a free account.</p><h3>4. Continue to configure the server</h3><p>Now we can log into our server via SSH protocol (port 22).</p><p>In the console, type</p><pre>ssh -i ~/.ssh/my_n8n_key root@&lt;server IP address&gt;.</pre><p>We will automatically connect to the server</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-UNYajDXv2gFjYY1uOCBOA.png" /><figcaption>Connecting to server from console with SSH. Screenshot by the author.</figcaption></figure><p>Now let’s update the packages on the server</p><pre>apt update &amp;&amp; apt upgrade -y</pre><p>And install new packages</p><pre>apt install -y mc htop fail2ban ca-certificates curl git</pre><p>Next, we need to reboot our server</p><pre>reboot</pre><p>After sending the reboot command, the connection will be lost, now you need to wait a minute and reconnect to the server</p><pre>ssh -i ~/.ssh/my_n8n_key root@&lt;server IP address&gt;</pre><p>Install Docker, first add the official Docker GPG key</p><pre>install -m 0755 -d /etc/apt/keyrings<br>curl -fsSL https://download.docker.com/linux/ubuntu/gpg -o /etc/apt/keyrings/docker.asc<br>chmod a+r /etc/apt/keyrings/docker.asc</pre><p>Now we add the Docker repository to the APT sources (Ubuntu package manager for installing packages)</p><pre>echo \<br>&quot;deb [arch=$(dpkg - print-architecture) signed-by=/etc/apt/keyrings/docker.asc] https://download.docker.com/linux/ubuntu \<br>$(. /etc/os-release &amp;&amp; echo &quot;${UBUNTU_CODENAME:-$VERSION_CODENAME}&quot;) stable&quot; | \<br>sudo tee /etc/apt/sources.list.d/docker.list &gt; /dev/null<br>sudo apt-get update -y</pre><p>Install Docker and related packages, including the compose plugin</p><pre>apt install -y docker-ce docker-ce-cli containerd.io docker-buildx-plugin docker-compose-plugin</pre><p>You can check that docker is installed</p><pre>docker compose version</pre><p>The version should be displayed. For example, I have Docker Compose version v2.39.1</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kK8xtSa1ipczuZz16zqSDA.png" /><figcaption>Docker Compose version, after docker installation. Screenshot by the author.</figcaption></figure><h3>5. Install and run Docker and N8N</h3><p>Clone the Docker repository and go to the folder</p><pre>git clone https://github.com/n8n-io/n8n-docker-caddy.git<br>cd n8n-docker-caddy</pre><p>Create Docker volumes</p><pre>docker volume create caddy_data<br>docker volume create n8n_data</pre><p>Set up config</p><pre>nano .env</pre><p>If you haven’t launched nano yet, Ubuntu will ask you which editor to use, press 1 for nano, this is the simplest editor.</p><p>The standard config will open.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0X5Krhi1K2oT5Qlcrs8nYA.png" /><figcaption>Docker caddy config by default in Nano editor. Screenshot by the author.</figcaption></figure><p>You need to change the path to the folder, as well as the main domain (you need to enter it without a subdomain, for example mydomain.com and your email, and press Control + X and then Y to save the data</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KT0jOo-4CUnl_88S37KrnA.png" /><figcaption>Docker caddy config after changes. Screenshot by the author.</figcaption></figure><p>Next, we register the domain in the caddyfile</p><pre>nano caddy_config/Caddyfile</pre><p>You need to enter the current subdomain and press Control + X and then Y to save the data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*n9tE2OoYmZRt6ZY4UpVWwA.png" /><figcaption>Caddyfile config in Nano editor. Screenshot by the author.</figcaption></figure><p>Open ports 80/443 on the server</p><pre>ufw allow 80,443/tcp</pre><p>Now run n8n image with Docker Compose:</p><pre>docker compose up -d</pre><p>If you want to make sure the containers are running, run the command</p><pre>docker compose ps</pre><h3>6. Check our N8N in the browser and start using it</h3><p>Now just open the domain in your browser and make sure N8N works!</p><p>https://n8n.&lt;your domain&gt;.com</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pofDKg2AJEXl3kNwDi1REQ.png" /><figcaption>N8N main page with set up owner account after installation. Screenshot by the author.</figcaption></figure><p>After registering and setting up your main account, you will be able to log in and create your workflows.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HC3pf0OQpB2m-POrRj7Tiw.png" /><figcaption>N8N main page after installation. Screenshot by the author.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*r9wwVoZIxudh5lfBFvhPeA.png" /><figcaption>Workflow N8N page. Screenshot by the author.</figcaption></figure><h3>Question: How to update N8N?</h3><p>Very simple<br>Go to our folder with n8n</p><pre>cd ~/n8n-docker-caddy</pre><p>Download fresh docker images</p><pre>docker compose pull</pre><p>Stop the current version</p><pre>docker compose down</pre><p>Rebuild and start containers</p><pre>docker compose up -d</pre><h3>Optimizing SQLite database</h3><p>N8N uses SQLite database by default and if you have large workflows with multiple steps, logs can greatly inflate the SQLite database. I recommend reducing the number of logs that will be stored in the database</p><p>Add to /home/ubuntu/n8n-docker-caddy/.env new block to limit logs (last 500 instead of 10000).</p><pre># - - n8n pruning &amp; vacuum options  - –<br>EXECUTIONS_DATA_PRUNE=true<br>EXECUTIONS_DATA_MAX_AGE=72<br>EXECUTIONS_DATA_PRUNE_MAX_COUNT=500<br>EXECUTIONS_DATA_SAVE_ON_SUCCESS=none<br>EXECUTIONS_DATA_SAVE_ON_ERROR=all<br>EXECUTIONS_DATA_SAVE_ON_PROGRESS=false<br>EXECUTIONS_DATA_SAVE_MANUAL_EXECUTIONS=false<br>#DB_SQLITE_VACUUM_ON_STARTUP=true</pre><p>and restart the docker image</p><pre>docker compose up -d</pre><p><strong>Note:</strong> for heavy loads it is better to install Postgres instead of SQLite</p><h3>Useful Links</h3><ol><li><strong>My Article, how to install Your Own Local N8N on macOS:</strong> <a href="https://andrewkushnerov.medium.com/installing-your-own-local-n8n-on-macos-ddc9dbc85cd3">https://andrewkushnerov.medium.com/installing-your-own-local-n8n-on-macos-ddc9dbc85cd3</a></li><li><strong>N8N official repository: </strong><a href="https://github.com/n8n-io/n8n">https://github.com/n8n-io/n8n</a></li><li><strong>N8N plans and pricing:</strong> <a href="https://n8n.io/pricing/">https://n8n.io/pricing/</a></li><li><strong>Hetzner hosting:</strong> <a href="https://www.hetzner.com/">https://www.hetzner.com/</a></li><li><strong>Cloudflare (buy domains, control DNS and protect your server against DOS-atacks and bots): </strong><a href="https://www.cloudflare.com/">https://www.cloudflare.com/</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7b57c2f575b1" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Installing Your Own Local N8N on macOS]]></title>
            <link>https://andrewkushnerov.medium.com/installing-your-own-local-n8n-on-macos-ddc9dbc85cd3?source=rss-27f59dbe18f4------2</link>
            <guid isPermaLink="false">https://medium.com/p/ddc9dbc85cd3</guid>
            <category><![CDATA[n8n]]></category>
            <category><![CDATA[workflow-automation]]></category>
            <category><![CDATA[mac]]></category>
            <dc:creator><![CDATA[Andrei Kushniarou]]></dc:creator>
            <pubDate>Mon, 04 Aug 2025 17:01:27 GMT</pubDate>
            <atom:updated>2025-10-01T16:07:49.111Z</atom:updated>
            <content:encoded><![CDATA[<p>Today I’ll show you how to install and configure a fully functional N8N instance on your Mac. Sure, you could just use the official cloud-hosted N8N, but the cheapest plan starts at $20 per month (billed annually). If you want to play around with N8N’s features and run workflows only occasionally, installing it locally on your own computer is an excellent solution.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Mcc_pGwD4AmoYnB0xc3Ciw.png" /><figcaption>Installing Your Own Local N8N on macOS. Collage by the author</figcaption></figure><p>Fortunately, N8N is open source (<a href="https://github.com/n8n-io/n8n">https://github.com/n8n-io/n8n</a>) and free to use for personal and non-commercial purpose, including internal business use. The paid cloud plan limits you to just five active workflows and 2.5K executions per month, it’s not exactly generous. A self-hosted install has none of those caps.</p><h3>1. Install Homebrew</h3><p>Homebrew is the go-to package manager for macOS, think of it like apt or yum for Linux. It makes installing and managing development tools from the command line a breeze.</p><ol><li>Go to <a href="https://brew.sh/">https://brew.sh/</a></li><li>Copy the install command and paste it into Terminal:</li></ol><pre>/bin/bash -c &quot;$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)&quot;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cav7DSOeMKzR1CkpYECIBg.png" /><figcaption><em>Homebrew installation in Terminal. Screenshot by the author.</em></figcaption></figure><h3>2. Install Node.js</h3><p>N8N runs on Node.js, so let’s install it via Homebrew. Brew will pull in all dependencies and install the latest Node.js release:</p><pre>brew install node</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HzUealmjjXOOEPx7ebQDoQ.png" /><figcaption><em>Node installation in Terminal using brew. Screenshot by the author.</em></figcaption></figure><p>When it’s done, verify your install with:</p><pre>node -v<br>npm -v</pre><h3>3. Create Your N8N Project Folder &amp; Install N8N</h3><p>Make and navigate into a new folder:</p><pre>mkdir ~/my_n8n<br>cd ~/my_n8n</pre><p>Next, Initialize an npm project:</p><pre>npm init -y</pre><p>To install N8N, run the command. Now the nodeJs package manager (npm) will install the latest current version of N8N on your Mac.</p><pre>npm install n8n</pre><p>Now you need to add a block to package.json so that npm can run n8n. Open package.json in the nano editor (it is in the current my_n8n folder)</p><pre>nano package.json </pre><p>Replace the “scripts” block with:</p><pre>&quot;scripts&quot;: {<br>  &quot;start&quot;: &quot;n8n&quot;<br>},</pre><p>It should look like this</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aw-TTIFX-Vk8m7lCjH8A5Q.png" /><figcaption>Editing package.json to run n8n. Screenshot by the author.</figcaption></figure><p>Next, press Ctrl +X and Y to save the changes.</p><h3><strong>4. Launch N8N</strong></h3><p>Now you can start N8N right from your project folder:</p><pre>npm run start</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Z3_Rwvo8RI0ngu9SrmgAbA.png" /><figcaption><em>Screenshot of N8N starting in the console. Screenshot by the author.</em></figcaption></figure><p>As we can see, our N8N is available at the local address <a href="http://localhost:5678/">http://localhost:5678</a>. To open the interface, you need to enter the address in the browser, or you can press “o”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WkrF_ZWlXi_rLkPf0FSTkg.png" /><figcaption><em>Local N8N login page. Screenshot by the author.</em></figcaption></figure><p>We register, log in, and we have a real working N8N!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AlyvZq7ei2qzIATSZYPmnQ.png" /><figcaption><em>Local N8N main page. Screenshot by the author.</em></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rqTkJnmv29Y3mvstH8tWYw.png" /><figcaption>N8N, workflow. <em>Screenshot by the author.</em></figcaption></figure><p>This is a fully functional N8N that will run as long as the terminal window is open.</p><h4><strong>I need to change/add something to the N8N config, how do I do this?</strong></h4><pre>nano ~/.n8n/config</pre><h4>Is it possible to install N8N on my server so that it runs continuously?</h4><p>Yes, it’s possible! You can read about it in my article.<strong> </strong><a href="https://andrewkushnerov.medium.com/how-to-install-n8n-on-your-server-for-5-mo-7b57c2f575b1">How to Install N8N on Your Server for $5/mo</a></p><h3><strong>Useful links:</strong></h3><ol><li>Brew, a package manager for Mac: <a href="https://brew.sh/">https://brew.sh/</a></li><li>N8N official repository: <a href="https://github.com/n8n-io/n8n">https://github.com/n8n-io/n8n</a></li><li>My article: <a href="https://andrewkushnerov.medium.com/how-to-install-n8n-on-your-server-for-5-mo-7b57c2f575b1">How to Install N8N on Your Server for $5/mo</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ddc9dbc85cd3" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[AI-powered Calorie Tracker. How to use ChatGPT and Python to Analyze Your Meals]]></title>
            <link>https://andrewkushnerov.medium.com/ai-powered-calorie-tracker-how-to-use-chatgpt-and-python-to-analyze-your-meals-e6880a0db4ac?source=rss-27f59dbe18f4------2</link>
            <guid isPermaLink="false">https://medium.com/p/e6880a0db4ac</guid>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[chatgpt]]></category>
            <category><![CDATA[openai]]></category>
            <dc:creator><![CDATA[Andrei Kushniarou]]></dc:creator>
            <pubDate>Tue, 15 Oct 2024 22:43:02 GMT</pubDate>
            <atom:updated>2024-10-19T20:59:16.883Z</atom:updated>
            <content:encoded><![CDATA[<p>A few weeks ago, I decided to track my daily calories, protein, carbs, fat, and fiber intake to make sure I was eating right (getting enough protein and fiber and not gaining more fat and calories than I needed). To do this, I created a Google Spreadsheet and painstakingly and meticulously entered all the calories and macronutrients for a total after each meal and snack.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*K557jDdY4F-EuF0rvSlVJA.png" /><figcaption>AI-powered Calorie Tracker. How to use ChatGPT and Python to Analyze Your Meals. Image by the Author.</figcaption></figure><p>The situation got complicated during lunch, when I was brought a plate of food where I definitely couldn’t weigh the ingredients individually. Luckily, I had the idea to take a photo of my lunch and send it to ChatGPT asking how many calories and nutrients are in each ingredient. To my surprise, ChatGPT 4o was quite accurate in describing what was on my plate, correctly identifying the ingredients and estimating the total calorie count. Yes, the accuracy was plus or minus 50 grams, but it is quite enough to assess the correctness of my diet.</p><p>At that moment, I thought that it is in such small things that AI penetrates our lives faster than you expect.</p><p>When I returned from lunch to the office, I thought that I could ask ChatGPT to return the result not as text, but as structured JSON, and then it could be processed into a data frame for output and calculation of calories, proteins, carbohydrates, fats and fiber for each product separately and in general. It will turn out to be a simple application for independent use, and maybe even a small prototype for a potential startup. Right now I will try to do this.</p><h3>Table of Contents</h3><ul><li>Plan for building the app</li><li>Creating and testing the prompt</li><li>Connecting to the OpenAI API and making a request</li><li>Processing the received data</li><li>Testing with multiple photos</li><li>Creating a Flask app</li><li>Cost of an OpenAI Request</li><li>Ideas for Future Development</li></ul><h3>Plan for building the app</h3><p>The idea is simple:</p><ol><li>Send a meal photo to ChatGPT, requesting a structured JSON response (a list of ingredients and their nutritional details).</li><li>Process the JSON into a dataframe, displaying the results for each ingredient and the meal as a whole.</li><li>Wrap this functionality in a simple web app that can be deployed on a server.</li></ol><h3>Creating and Testing the Prompt</h3><p>Since it’s known that large language models (LLMs) aren’t great with precise math, I’ll separately ask for the ingredient weight in grams and use known data — calories, proteins, carbs, fats, and fiber per 100 grams. I’ll then calculate the exact values myself in the script.</p><p>Here’s the prompt I came up with:</p><pre>You are a very useful assistant. Help me with determining the caloric content of my meal<br><br>The photo shows food products for a meal. Determine which products are shown in the photo and return them ONLY as a JSON list, <br>where each list element should contain:<br>  * &quot;title&quot; - the name of the product, <br>  * &quot;weight&quot; - weight in grams, <br>  * &quot;kilocalories_per100g&quot; - how many calories are contained in this product in 100 grams, <br>  * &quot;proteins_per100g&quot; - the amount of proteins of this product per 100 grams, <br>  * &quot;fats_per100g&quot; - the amount of fat per 100 grams of this product, <br>  * &quot;carbohydrates_per100g&quot; - the amount of carbohydrates per 100 grams of this product, <br>  * &quot;fiber_per100g&quot; - the amount of fiber per 100 grams of this product</pre><p>Let’s test this prompt with a photo of my delicious Portuguese lunch:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Sj1DKXGnztp3yB69a3u0dw.jpeg" /><figcaption><em>Photo of the Meal with Beef Stew, Roasted Potatoes, and Green Peas by the Author of the Article.</em></figcaption></figure><p>And try to test in web-version of ChatGPT…</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*caYiiPJicOSDCwi9wMju1A.png" /></figure><p>Not bad! It looks like it’s working — ChatGPT returned a JSON in the exact format I requested. It did add some unnecessary text, but that’s not an issue.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*02w10dIXRjqOsj3ChfKnAA.png" /></figure><h3>Connecting to the OpenAI API and Making a Request</h3><p>Now let’s do the same thing using the OpenAI API. To access the API, you need to register at <a href="https://platform.openai.com/">OpenAI Platform</a>, add a small balance (I added $5), and generate an API key.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fJp7hh-S9Lk8sZhhNvjgSA.png" /></figure><p>Now let’s write a Python script to send the request to ChatGPT via the API.</p><p>Installing the required libraries:</p><pre>pip install openai Pillow pandas</pre><p>We then import the necessary libraries, API key, and the model we’ll use:</p><pre>import os, base64, json<br>from openai import OpenAI<br>from PIL import Image, ImageOps<br>from io import BytesIO<br>import pandas as pd<br><br>#Define OPEN API Key and model<br>OPENAI_API_KEY = &quot;[INSERT YOUR OPEN AI API KEY HERE]&quot;<br>MODEL=&quot;gpt-4o&quot; #or &quot;gpt-4o-mini&quot;</pre><p>I used gpt-4, but I also tested gpt-4o-mini, and while it worked decently, its accuracy was slightly lower.</p><p>Next, we’ll declare two functions to process our lunch photo. The first corrects the image orientation if needed, and the second resizes it to reduce token usage (since ChatGPT doesn’t need a 12-megapixel photo). We’ll also convert the image to a base64 string to send it.</p><pre><br># Function to resize the image and return the resized PIL image<br>def resize_image(image_path, target_width):<br><br>    #Open image<br>    with Image.open(image_path) as img:<br><br>        #Correcting image orientation if there is EXIF data<br>        img = ImageOps.exif_transpose(img)<br>        original_width, original_height = img.size #get original size<br><br>        target_height = int((target_width / original_width) * original_height) #calc proportion<br>        resized_img = img.resize((target_width, target_height), Image.Resampling.LANCZOS) #resize image<br><br>        # return image object<br>        return resized_img<br><br># Function to convert a PIL image to a base64 string<br>def pil_image_to_base64(img):<br><br>    buffered = BytesIO() #create buffer<br>    img.save(buffered, format=&quot;JPEG&quot;) #Save the image to the buffer in JPEG format<br>    base64_str = base64.b64encode(buffered.getvalue()).decode(&#39;utf-8&#39;) #convert buffer to base64 format<br><br>    return base64_str</pre><p>Next, we define the image path and process the image:</p><pre># Path to your image<br>image_path = &quot;./data/meal1.jpg&quot;<br><br>#Read and resize image, and convert to base64<br>resized_image = resize_image(image_path, 1000)<br>base64_image = pil_image_to_base64(resized_image)</pre><p>We’ll set up the system and user prompts:</p><pre>system_prompt = &quot;&quot;&quot;<br>  You are a very useful assistant. Help me with determining the caloric content of products<br>&quot;&quot;&quot;<br><br>user_prompt = &quot;&quot;&quot;<br>  The photo shows food products for a meal. Determine approximately which products are shown in the photo and return them ONLY as a json list, <br>  where each list element should contain:<br>    * &quot;title&quot; - the name of the product, <br>    * &quot;weight&quot; - weight in grams, <br>    * &quot;kilocalories_per100g&quot; - how many calories are contained in this product in 100 grams, <br>    * &quot;proteins_per100g&quot; - the amount of proteins of this product per 100 grams, <br>    * &quot;fats_per100g&quot; - the amount of fat per 100 grams of this product, <br>    * &quot;carbohydrates_per100g&quot; - the amount of carbohydrates per 100 grams of this product, <br>    * &quot;fiber_per100g&quot; - the amount of fiber per 100 grams of this product, <br>&quot;&quot;&quot;</pre><p>Let’s initialize the OpenAI client and send the data:</p><pre>client = OpenAI(api_key=os.environ.get(&quot;OPENAI_API_KEY&quot;, OPENAI_API_KEY))<br><br>completion = client.chat.completions.create(<br>  model=MODEL,<br>  messages=[<br>    {<br>      &quot;role&quot;: &quot;system&quot;,<br>      &quot;content&quot;: [<br>        {&quot;type&quot;: &quot;text&quot;, &quot;text&quot;: system_prompt},<br>      ]<br>    },<br>    {<br>      &quot;role&quot;: &quot;user&quot;,<br>      &quot;content&quot;: [<br>        {&quot;type&quot;: &quot;text&quot;, &quot;text&quot;: user_prompt},<br>        {&quot;type&quot;: &quot;image_url&quot;,&quot;image_url&quot;: {&quot;url&quot;: f&quot;data:image/jpeg;base64,{base64_image}&quot;}}<br>      ]<br>    }<br>  ]<br>)</pre><p>What did we do above? We “created” a chat using client.chat.completions, specified our model with the MODEL variable, defined the system prompt with role=system, and the user prompt consisting of text and the photo in base64 format separately.</p><p>Finally, we’ll get and print the response:</p><pre>response_content = completion.choices[0].message.content<br>print(&quot;Response: &quot; + response_content)</pre><p>Result: We received both text and JSON. It works!</p><pre>Response: Based on a description of typical meals, I can provide approximate nutritional information. Here&#39;s a possible breakdown for what you&#39;ve described:<br><br>```json<br>[<br>    {<br>        &quot;title&quot;: &quot;Beef Stew&quot;,<br>        &quot;weight&quot;: 150,<br>        &quot;kilocalories_per100g&quot;: 150,<br>        &quot;proteins_per100g&quot;: 20,<br>        &quot;fats_per100g&quot;: 7,<br>        &quot;carbohydrates_per100g&quot;: 5,<br>        &quot;fiber_per100g&quot;: 0<br>    },<br>    {<br>        &quot;title&quot;: &quot;Roasted Potatoes&quot;,<br>        &quot;weight&quot;: 100,<br>        &quot;kilocalories_per100g&quot;: 150,<br>        &quot;proteins_per100g&quot;: 2,<br>        &quot;fats_per100g&quot;: 6,<br>        &quot;carbohydrates_per100g&quot;: 28,<br>        &quot;fiber_per100g&quot;: 2<br>    },<br>    {<br>        &quot;title&quot;: &quot;Green Peas&quot;,<br>        &quot;weight&quot;: 100,<br>        &quot;kilocalories_per100g&quot;: 81,<br>        &quot;proteins_per100g&quot;: 5,<br>        &quot;fats_per100g&quot;: 0,<br>        &quot;carbohydrates_per100g&quot;: 14,<br>        &quot;fiber_per100g&quot;: 5<br>    }<br>]<br>```<br><br>Adjust the weights and content as needed based on actual portion sizes and ingredients.</pre><h3>Processing the Received Data</h3><p>Now we can extract the JSON part of the response:</p><pre># Try to extract and clean the JSON part from the response<br>try:<br>    start_index = response_content.find(&#39;[&#39;)  # JSON starts with [<br>    end_index = response_content.rfind(&#39;]&#39;) + 1  # JSON ends with ]<br><br>    # Extract the JSON string<br>    json_str = response_content[start_index:end_index]<br><br>    # Parse the JSON string<br>    json_data = json.loads(json_str)<br>    <br>    # Test pring Result<br>    #print(json.dumps(json_data, indent=4, ensure_ascii=False)) <br><br>except json.JSONDecodeError as e:<br>    print(&quot;Failed to parse JSON:&quot;, e)<br>except Exception as ex:<br>    print(&quot;An error occurred:&quot;, ex)</pre><p>Then, we’ll iterate over each product in the JSON to calculate the exact value of calories, proteins, fats, carbohydrates, and fiber based on the weight, gather this into a new frame_data list, and then create a dataframe from it:</p><pre>frame_data = []<br>for row in json_data:<br><br>    frame_data.append(dict(<br>        title =             row[&#39;title&#39;],<br>        weight =            row[&#39;weight&#39;],<br>        kilocalories =      round(row[&#39;weight&#39;]/100 * row[&#39;kilocalories_per100g&#39;]),<br>        proteins =          round(row[&#39;weight&#39;]/100 * row[&#39;proteins_per100g&#39;]),<br>        fat =               round(row[&#39;weight&#39;]/100 * row[&#39;fats_per100g&#39;]),<br>        carbohydrates =     round(row[&#39;weight&#39;]/100 * row[&#39;carbohydrates_per100g&#39;]),<br>        fiber =             round(row[&#39;weight&#39;]/100 * row[&#39;fiber_per100g&#39;]),<br>    ))<br><br><br>frame = pd.DataFrame(frame_data)<br>print(frame)</pre><p>The result:</p><pre>              title   weight  kilocalories  proteins  fat  carbohydrates  fiber<br>0         Beef Stew     150           225        30   10              8      0<br>1  Roasted Potatoes     100           150         2    6             28      2<br>2        Green Peas     100            81         5    0             14      5</pre><p>Now let’s calculate the total values for the meal:</p><pre>#Сalculate overall<br>kilocalories = frame[&#39;kilocalories&#39;].sum()<br>proteins = frame[&#39;proteins&#39;].sum()<br>fat = frame[&#39;fat&#39;].sum()<br>carbohydrates = frame[&#39;carbohydrates&#39;].sum()<br>fiber = frame[&#39;fiber&#39;].sum()<br><br>print()<br>print(&#39;== OVERALL ==&#39;)<br>print(f&#39;Kilocalories: {kilocalories} kcal&#39;)<br>print(f&#39;Proteins: {proteins} g&#39;)<br>print(f&#39;Fat: {fat} g&#39;)<br>print(f&#39;Carbohydrates: {carbohydrates} g&#39;)<br>print(f&#39;Fiber: {fiber} g&#39;)</pre><p>The result</p><pre>== OVERALL ==<br>Kilocalories: 456 kcal<br>Proteins: 37 g<br>Fat: 16 g<br>Carbohydrates: 50 g<br>Fiber: 7 g</pre><p>I think it looks pretty good.</p><h3>Tests on Several Photos</h3><p>Let’s send some more photos of my meals to ensure the script works properly.</p><h4>Meal #2 — Salmon + vegetables</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Fg2h0UP3cE9yZjv9xkC8TQ.jpeg" /><figcaption>Photo of the Meal with Salmon by the Author of the Article</figcaption></figure><h4>Result #2</h4><pre>              title  weight  kilocalories  proteins  fat  carbohydrates  fiber<br>0           Salmon     200           412        44   26              0      0<br>1  Boiled Potatoes     150           130         3    0             30      3<br>2          Carrots     100            41         1    0             10      3<br>3      Green Beans     100            31         2    0              7      3<br><br>== OVERALL ==<br>Kilocalories: 614 kcal<br>Proteins: 50 g<br>Fat: 26 g<br>Carbohydrates: 47 g<br>Fiber: 9 g</pre><h4>Meal #3 — Lasagna</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sTcBb2WM59SNK0ciUQlmsw.jpeg" /><figcaption>Photo of the Lasagna by the Author of the Article</figcaption></figure><h4>Result #3</h4><p>It did, however, count my son’s pizza next to it, but it is in the picture, right? Ideally, you could ask in the prompt to recognize the main dish in the center or just take a more careful photo.</p><pre>         title    weight  kilocalories  proteins  fat  carbohydrates  fiber<br>0      Lasagna     300           450        24   30             36      6<br>1  Pizza slice     150           375        18   15             50      4<br><br>== OVERALL ==<br>Kilocalories: 825 kcal<br>Proteins: 42 g<br>Fat: 45 g<br>Carbohydrates: 86 g<br>Fiber: 10 g</pre><h4>Meal #4 — Sushi</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MZlz6CWv6Imix2ZdctaCEQ.jpeg" /><figcaption>Photo of the Sushi by the Author of the Article</figcaption></figure><h4>Result #4</h4><p>You could nitpick that it didn’t recognize all the nigiri, but it did a fairly decent job counting the rolls.</p><pre>             title  weight  kilocalories  proteins  fat  carbohydrates  fiber<br>0    Salmon Nigiri      40           100         5    3             14      0<br>1      Tuna Nigiri      40            52         8    2              4      0<br>2  California Roll     150           360         9   12             52      3<br>3     Avocado Roll     150           210         4   10             27      4<br><br>== OVERALL ==<br>Kilocalories: 722 kcal<br>Proteins: 26 g<br>Fat: 27 g<br>Carbohydrates: 97 g<br>Fiber: 7 g</pre><h4>Meal #5 — Complicated Breakfast</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*C0kISSY8GQ5ZKfSnElOtIQ.jpeg" /><figcaption>Photo of the Breakfast by the Author of the Article</figcaption></figure><h4>Result #5</h4><p>Here, I purposely created a complicated breakfast where I piled a lot of different things onto the plate, and to ChatGPT’s credit, it recognized almost everything.</p><pre>                title  weight  kilocalories  proteins  fat  carbohydrates  fiber<br>0          fried egg      50            75         6    6              0      0<br>1    cherry tomatoes     100            18         1    0              4      1<br>2             salmon      50           103        11    6              0      0<br>3        feta cheese      30            79         4    6              1      0<br>4        bread slice      40           100         4    2             19      1<br>5  pickled mushrooms      50            11         1    0              2      1<br><br>== OVERALL ==<br>Kilocalories: 386 kcal<br>Proteins: 27 g<br>Fat: 20 g<br>Carbohydrates: 26 g<br>Fiber: 3 g</pre><p>Wow! It even recognized the mushrooms. Excellent work! Yes, the script occasionally makes mistakes, but the result is much better than I expected, especially for a prototype put together on the fly.</p><h3>Building a Flask app</h3><p>Now we can build a separate Flask app that can be deployed on a web server or packaged into an Android/iPhone app.</p><p>First, install Flask:</p><pre>pip install flask</pre><p>Let’s create the controller:</p><pre>import os, base64, json<br>from flask import Flask, request, render_template<br>from PIL import Image, ImageOps, UnidentifiedImageError<br>import pandas as pd<br>from io import BytesIO<br>from openai import OpenAI<br><br>app = Flask(__name__)<br><br>#Define OPEN API Key and model<br>OPENAI_API_KEY = &quot;[INSERT YOUR OPEN AI API KEY HERE]&quot;<br>MODEL=&quot;gpt-4o&quot;<br><br>client = OpenAI(api_key=os.environ.get(&quot;OPENAI_API_KEY&quot;, OPENAI_API_KEY))<br><br>system_prompt = &quot;You are a very useful assistant. Help me with determining the caloric content of my meal&quot;<br><br>user_prompt = &quot;&quot;&quot;<br>The photo shows food products for a meal. Determine which products are shown in the photo and return them ONLY as a JSON list, <br>where each list element should contain:<br>  * &quot;title&quot; - the name of the product, <br>  * &quot;weight&quot; - weight in grams, <br>  * &quot;kilocalories_per100g&quot; - how many calories are contained in this product in 100 grams, <br>  * &quot;proteins_per100g&quot; - the amount of proteins of this product per 100 grams, <br>  * &quot;fats_per100g&quot; - the amount of fat per 100 grams of this product, <br>  * &quot;carbohydrates_per100g&quot; - the amount of carbohydrates per 100 grams of this product, <br>  * &quot;fiber_per100g&quot; - the amount of fiber per 100 grams of this product<br>&quot;&quot;&quot;<br><br># Function to resize the image and return the resized PIL image<br>def resize_image(img, target_width):<br><br>    img = ImageOps.exif_transpose(img)<br>    original_width, original_height = img.size #get original size<br>    target_height = int((target_width / original_width) * original_height) #calc proportion<br>    resized_img = img.resize((target_width, target_height), Image.Resampling.LANCZOS) #resize image<br>    return resized_img<br><br># Function to convert a PIL image to a base64 string<br>def pil_image_to_base64(img):<br><br>    buffered = BytesIO() #create buffer<br>    img.save(buffered, format=&quot;JPEG&quot;) #Save the image to the buffer in JPEG format<br>    base64_str = base64.b64encode(buffered.getvalue()).decode(&#39;utf-8&#39;) #convert buffer to base64 format<br>    return base64_str<br><br><br>@app.route(&quot;/&quot;, methods=[&quot;GET&quot;, &quot;POST&quot;])<br>def index():<br><br>    if request.method == &quot;POST&quot;:<br><br>        #Get uploaded file<br>        file = request.files[&quot;file&quot;]<br>        if file:<br><br>            try:<br>                image = Image.open(file)<br>            except UnidentifiedImageError:<br>                return render_template(&quot;index.html&quot;, error=&quot;Incorrect Image!&quot;)<br><br>            resized_image = resize_image(image, 1000)<br>            base64_image = pil_image_to_base64(resized_image)<br><br>            completion = client.chat.completions.create(<br>                model=MODEL,<br>                messages=[<br>                    {<br>                        &quot;role&quot;: &quot;system&quot;,<br>                        &quot;content&quot;: [<br>                            {&quot;type&quot;: &quot;text&quot;, &quot;text&quot;: system_prompt},<br>                        ]<br>                    },<br>                    {<br>                        &quot;role&quot;: &quot;user&quot;,<br>                        &quot;content&quot;: [<br>                            {&quot;type&quot;: &quot;text&quot;, &quot;text&quot;: user_prompt},<br>                            {&quot;type&quot;: &quot;image_url&quot;, &quot;image_url&quot;: {&quot;url&quot;: f&quot;data:image/jpeg;base64,{base64_image}&quot;}}<br>                        ]<br>                    }<br>                ]<br>            )<br><br>            #Get and parse result<br>            response_content = completion.choices[0].message.content<br><br>            start_index = response_content.find(&#39;[&#39;)<br>            end_index = response_content.rfind(&#39;]&#39;) + 1<br>            json_str = response_content[start_index:end_index]<br><br>            try:<br><br>                data = json.loads(json_str)<br><br>                frame_data = []<br>                for row in data:<br>                    frame_data.append(dict(<br>                        title = row[&#39;title&#39;],<br>                        weight = row[&#39;weight&#39;],<br>                        kilocalories = round(row[&#39;weight&#39;] / 100 * row[&#39;kilocalories_per100g&#39;]),<br>                        proteins = round(row[&#39;weight&#39;] / 100 * row[&#39;proteins_per100g&#39;]),<br>                        fat = round(row[&#39;weight&#39;] / 100 * row[&#39;fats_per100g&#39;]),<br>                        carbohydrates = round(row[&#39;weight&#39;] / 100 * row[&#39;carbohydrates_per100g&#39;]),<br>                        fiber = round(row[&#39;weight&#39;] / 100 * row[&#39;fiber_per100g&#39;]),<br>                    ))<br><br>                frame = pd.DataFrame(frame_data)<br>                totals = {<br>                    &#39;kilocalories&#39;: frame[&#39;kilocalories&#39;].sum(),<br>                    &#39;proteins&#39;: frame[&#39;proteins&#39;].sum(),<br>                    &#39;fat&#39;: frame[&#39;fat&#39;].sum(),<br>                    &#39;carbohydrates&#39;: frame[&#39;carbohydrates&#39;].sum(),<br>                    &#39;fiber&#39;: frame[&#39;fiber&#39;].sum()<br>                }<br><br>                return render_template(&quot;index.html&quot;,<br>                                       frame=frame.to_dict(orient=&quot;records&quot;),<br>                                       totals=totals,<br>                                       base64_image=f&quot;data:image/jpeg;base64,{base64_image}&quot;)<br><br>            except json.JSONDecodeError as e:<br>                return render_template(&quot;index.html&quot;, error=&quot;No Meal found!&quot;)<br><br>    return render_template(&quot;index.html&quot;)<br><br><br>if __name__ == &quot;__main__&quot;:<br>    app.run(debug=True)<br></pre><p>Let’s create a template for displaying the form and result data.</p><pre>&lt;!doctype html&gt;<br>&lt;html lang=&quot;en&quot;&gt;<br>&lt;head&gt;<br>  &lt;meta charset=&quot;utf-8&quot;&gt;<br>  &lt;meta name=&quot;viewport&quot; content=&quot;width=device-width, initial-scale=1, shrink-to-fit=no&quot;&gt;<br>  &lt;title&gt;Calorie Detector&lt;/title&gt;<br>  &lt;link href=&quot;https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css&quot; rel=&quot;stylesheet&quot; integrity=&quot;sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH&quot; crossorigin=&quot;anonymous&quot;&gt;<br>&lt;/head&gt;<br>&lt;body&gt;<br><br>  &lt;div class=&quot;container mt-3&quot;&gt;<br>    &lt;div class=&quot;row&quot;&gt;<br>      &lt;div class=&quot;col&quot;&gt;<br><br>        &lt;h2&gt;Upload photo of your Meal!&lt;/h2&gt;<br>        &lt;form method=&quot;POST&quot; enctype=&quot;multipart/form-data&quot;&gt;<br>          &lt;div class=&quot;row&quot;&gt;<br>            &lt;div class=&quot;col&quot;&gt;<br>              &lt;input type=&quot;file&quot; name=&quot;file&quot; class=&quot;form-control&quot; accept=&quot;image/*&quot; required&gt;<br>            &lt;/div&gt;<br>            &lt;div class=&quot;col&quot;&gt;<br>              &lt;button type=&quot;submit&quot; class=&quot;btn btn-primary&quot;&gt;Upload a Photo!&lt;/button&gt;<br>            &lt;/div&gt;<br>          &lt;/div&gt;<br>        &lt;/form&gt;<br><br>        &lt;hr&gt;<br><br>        {% if error %}<br>          &lt;div class=&quot;alert alert-danger&quot; role=&quot;alert&quot;&gt;<br>            {{ error }}<br>          &lt;/div&gt;<br>        {% endif %}<br><br>        {% if frame %}<br><br>        &lt;h2&gt;Meal Ingredients&lt;/h2&gt;<br><br>          &lt;div class=&quot;row&quot;&gt;<br>            &lt;div class=&quot;col-md-4&quot;&gt;<br>              &lt;img src=&quot;{{ base64_image }}&quot; class=&quot;img-thumbnail mb-3&quot; alt=&quot;Uploaded Meal&quot; /&gt;<br>            &lt;/div&gt;<br><br>            &lt;div class=&quot;col-md-8&quot;&gt;<br><br>              &lt;h2&gt;Meal Overall Result:&lt;/h2&gt;<br><br>              &lt;h5&gt;Kilocalories: {{ totals.kilocalories }} kcal&lt;/h5&gt;<br>              &lt;h5&gt;Proteins: {{ totals.proteins }} g&lt;/h5&gt;<br>              &lt;h5&gt;Fat: {{ totals.fat }} g&lt;/h5&gt;<br>              &lt;h5&gt;Carbohydrates: {{ totals.carbohydrates }} g&lt;/h5&gt;<br>              &lt;h5&gt;Fiber: {{ totals.fiber }} g&lt;/h5&gt;<br><br>            &lt;/div&gt;<br>          &lt;/div&gt;<br><br>          &lt;table class=&quot;table table-striped&quot;&gt;<br>            &lt;thead&gt;<br>              &lt;tr&gt;<br>                &lt;th&gt;Product&lt;/th&gt;<br>                &lt;th&gt;Weight (g)&lt;/th&gt;<br>                &lt;th&gt;Kilocalories (kcal)&lt;/th&gt;<br>                &lt;th&gt;Proteins (g)&lt;/th&gt;<br>                &lt;th&gt;Fat (g)&lt;/th&gt;<br>                &lt;th&gt;Carbohydrates (g)&lt;/th&gt;<br>                &lt;th&gt;Fiber (g)&lt;/th&gt;<br>              &lt;/tr&gt;<br>            &lt;/thead&gt;<br>            &lt;tbody&gt;<br>              {% for item in frame %}<br>              &lt;tr&gt;<br>                &lt;td&gt;{{ item.title }}&lt;/td&gt;<br>                &lt;td&gt;{{ item.weight }}&lt;/td&gt;<br>                &lt;td&gt;{{ item.kilocalories }}&lt;/td&gt;<br>                &lt;td&gt;{{ item.proteins }}&lt;/td&gt;<br>                &lt;td&gt;{{ item.fat }}&lt;/td&gt;<br>                &lt;td&gt;{{ item.carbohydrates }}&lt;/td&gt;<br>                &lt;td&gt;{{ item.fiber }}&lt;/td&gt;<br>              &lt;/tr&gt;<br>              {% endfor %}<br>            &lt;/tbody&gt;<br>          &lt;/table&gt;<br><br>        {% endif %}<br><br>      &lt;/div&gt;<br>    &lt;/div&gt;<br>  &lt;/div&gt;<br><br>    &lt;script src=&quot;https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js&quot; integrity=&quot;sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz&quot; crossorigin=&quot;anonymous&quot;&gt;&lt;/script&gt;<br>&lt;/body&gt;<br>&lt;/html&gt;</pre><p>Result of the Flask Application with Data Output</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6uvVhtZrypsISTZB-Yf9_Q.png" /><figcaption>Result of Flask application</figcaption></figure><h3>Calculating the Cost of an OpenAI Request</h3><p>It’s interesting to calculate the cost of an OpenAI request. Pricing information is available here: <a href="https://openai.com/api/pricing/">OpenAI Pricing</a>. The cost of Vision will depend on the size of the images. For example, for an image of 1000x1000 pixels, it will be approximately $0.002 just for Vision. Therefore, for production, it’s essential to choose an image size that’s not too large but still has enough detail for recognition.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1016/1*YT6cCxWm1qBrWCWC1rGKcw.png" /><figcaption>Vision pricing calculator result</figcaption></figure><p>Here’s how to get the input/output tokens from the completion structure and calculate the cost:</p><pre>#Get tokens from usage<br>prompt_tokens = completion.usage.prompt_tokens<br>completion_tokens = completion.usage.completion_tokens<br>total_tokens = completion.usage.total_tokens<br><br>#Show tokens<br>print(f&quot;Prompt Tokens: {prompt_tokens}&quot;)<br>print(f&quot;Completion Tokens: {completion_tokens}&quot;)<br>print(f&quot;Total Tokens: {total_tokens}&quot;)<br><br># https://openai.com/api/pricing/<br>cost_per_1m_input_tokens = 2.50  # price for input tokens<br>cost_per_1m_output_tokens = 10.00  # price for output tokens<br><br>#Calc cost<br>cost_input = (prompt_tokens / 1_000_000) * cost_per_1m_input_tokens<br>cost_output = (completion_tokens / 1_000_000) * cost_per_1m_output_tokens<br>total_cost = round((cost_input + cost_output),6)<br><br>print(f&quot;Total cost of the request: ${total_cost}&quot;)</pre><p>The result. Overall cost is approximate $0.004 per query.</p><pre>Prompt Tokens: 974<br>Completion Tokens: 128<br>Total Tokens: 1102<br>Total cost of the request: $0.003715</pre><h3>Ideas for Future Development</h3><h4>Ideas for Technical Development</h4><p>I naively ask the model for JSON in text format. This works because ChatGPT-4 is smart enough, but it’s better to define the format for returning data more explicitly.</p><p>It’s also worth experimenting with the prompt, image size, and parameters to optimize the detection of meal components and reduce token consumption. If I were building this app more “seriously,” I’d try fine-tuning the model with data from global cuisines so that the model could identify portion sizes and ingredients more accurately.</p><h4>Ideas for Developing the App into a Product</h4><p>First, I’d add the ability to detect sugar and saturated fats. These parameters are also important for healthy eating. It’s not difficult to add these parameters to the prompt and then save them into the dataframe.</p><p>Next, I’d implement meal tracking to calculate the total intake for a day. For example, after photographing your breakfast, lunch, and dinner, you could see how much you’ve eaten in total for the day. Data could be displayed in a calendar format.</p><p>Additionally, recommendations and goals could be added! If you’ve eaten too much fat, for example, the app could suggest cutting down on fats. Or, if you’re into sports, it could recommend increasing protein intake.</p><p>If we add user parameters (weight, height, basic physical activity), we could calculate recommended calorie intake and provide personalized suggestions based on that!</p><p>Maybe one of my readers will use this idea and implementation to launch a startup? Don’t forget to give me a share! :)</p><h3>About the Author</h3><p>The author of this article is an engineer with 20 years of experience as a developer and data engineer, with expertise in e-commerce, machine learning (including AI), and blockchain. Always eager to experiment and test new things, opening to project-based collaborations.</p><h3>Links</h3><ol><li>Quickstart with OpenAI: <a href="https://platform.openai.com/docs/quickstart">https://platform.openai.com/docs/quickstart</a></li><li>OpenAI Vision — works with Images: <a href="https://platform.openai.com/docs/guides/vision">https://platform.openai.com/docs/guides/vision</a></li><li>OpenAI— Structured Json Output <a href="https://platform.openai.com/docs/guides/structured-outputs/json-mode#expander-0">https://platform.openai.com/docs/guides/structured-outputs/json-mode#expander-0</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e6880a0db4ac" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Amazon SP-API: Measure Sales Performance with Order Metrics]]></title>
            <link>https://andrewkushnerov.medium.com/amazon-sp-api-measure-sales-performance-with-order-metrics-52fddfc98254?source=rss-27f59dbe18f4------2</link>
            <guid isPermaLink="false">https://medium.com/p/52fddfc98254</guid>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-integration]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Andrei Kushniarou]]></dc:creator>
            <pubDate>Thu, 03 Oct 2024 11:03:06 GMT</pubDate>
            <atom:updated>2024-10-03T18:13:18.431Z</atom:updated>
            <content:encoded><![CDATA[<p>Amazon SP-API provides a simple and efficient way to evaluate Sales Performance — using Order Metrics [<a href="https://developer-docs.amazon.com/sp-api/docs/sales-api-v1-reference">1</a>]. The Order Metrics request returns detailed, basic sales data over specified time intervals, similar to what you’d see in the Amazon Seller interface.</p><p>A few weeks ago, I wrote a detailed guide on how to query Orders [<a href="https://medium.com/@andrewkushnerov/amazon-sp-api-get-orders-with-python-7b7e913d87ea">2</a>] and Order Items [<a href="https://medium.com/@andrewkushnerov/amazon-sp-api-get-order-items-with-python-68506bbde58a">3</a>] using the Amazon SP API. With detailed data on orders and items, you can build highly accurate statistics. However, fetching Orders and their Items is a lengthy process, as you need to make separate requests for each order to retrieve its items.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5qAdUjfY7sN5ckvEw2YnSA.png" /><figcaption>Amazon SP-API: Measure Sales Performance with Order Metrics. Image by the Author.</figcaption></figure><p>If you don’t need such granular data (e.g., item statuses, promotion discounts, geographical information, etc.), you can use Order Metrics.</p><p>Let’s explore how to request this data and set up the entire process.</p><h4>Table of Contents</h4><ul><li>Overview of getOrderMetrics method</li><li>Making a General Request</li><li>Parsing the Result into a DataFrame</li><li>Plotting Sales Graphs</li><li>Advanced Version: ASIN-Specific Data</li><li>Processing Results for the Advanced Version</li><li>Plotting Graphs for ASIN-Specific Data</li><li>Further Development</li></ul><h4>Overview of getOrderMetrics method</h4><p>The SP-API getOrderMetrics [<a href="https://developer-docs.amazon.com/sp-api/docs/sales-api-v1-reference">1</a>] method allows you to request sales performance data within a chosen time range and time granularity (hourly, daily, weekly, monthly, yearly, or total). The key feature here is that you can request data for the past two years.</p><p>You can also request data for individual units (filtered by SKU or ASIN), by buyer type (B2B/B2C), by marketplace, and by fulfillment network. This allows you to build a basic high-level statistic that updates in near real-time.</p><h4>Making a General Request</h4><p>Let’s first create a script to make a general request and retrieve sales data grouped by days.</p><p>Connecting Libraries</p><pre>import os, time, pytz<br>import requests, urllib.parse, json<br>from sp_api_functions import get_lwa_access_token<br>from datetime import datetime, timedelta, timezone<br>import pandas as pd</pre><p>We define the endpoint and marketplace ID (for the US). You can find the endpoints for other regions <a href="https://developer-docs.amazon.com/sp-api/docs/sp-api-endpoints">here</a>, and the marketplace IDs <a href="https://developer-docs.amazon.com/sp-api/docs/marketplace-ids">here</a>.</p><pre>#Define endpoint and marketplace ID<br>ENDPOINT = &quot;https://sellingpartnerapi-na.amazon.com&quot;<br>MARKETPLACE_ID = &#39;ATVPDKIKX0DER&#39; #US</pre><p>Define the time range for the data request. Since I’m requesting data for the US and I want the times to align with the Amazon Seller Central interface, I’ll use the US/Pacific timezone. I set the end of the time range to today and go back 180 days. For SP API, time needs to be converted to ISO format.</p><pre>today = datetime.now().astimezone(pytz.timezone(&#39;US/Pacific&#39;))<br><br>start_time = today.replace(hour=23, minute=59, second=59, microsecond=59)<br>start_time_iso = start_time.isoformat(timespec=&#39;milliseconds&#39;).replace(&quot;+00:00&quot;, &quot;Z&quot;)<br><br>end_time = today.replace(hour=0, minute=0, second=0, microsecond=0) - timedelta(days=180)<br>end_time_iso = end_time.isoformat(timespec=&#39;milliseconds&#39;).replace(&quot;+00:00&quot;, &quot;Z&quot;)<br><br>interval = f&#39;{end_time_iso}--{start_time_iso}&#39;<br>print(&#39;interval=&#39;, interval)</pre><p>We set the request parameters. I choose granularity = &quot;Day&quot; to group data by days and granularityTimeZone = &quot;US/Pacific&quot; to match the Pacific time zone (like in Amazon Seller Central for US).</p><pre># Set Request params<br>request_params = {<br>    &quot;granularity&quot;: &quot;Day&quot;,<br>    &quot;interval&quot;: interval,<br>    &quot;marketplaceIds&quot;: MARKETPLACE_ID,<br>    &#39;granularityTimeZone&#39;: &#39;US/Pacific&#39;,<br>    &quot;buyerType&quot;: &quot;All&quot;,<br>    &quot;firstDayOfWeek&quot;: &quot;Monday&quot;,  # if granularity=Week selected<br>}</pre><p>Fetch the access token and make the request to SP API. You can find the get_lwa_access_token function in my article <a href="https://medium.com/r?url=https%3A%2F%2Fandrewkushnerov.medium.com%2Famazon-sp-api-get-inventory-and-prices-with-python-3226b980bd79"><em>Amazon SP-API: Get Inventory and Prices with Python</em></a>.</p><pre>#Get access token<br>access_token = get_lwa_access_token()<br><br>#Call orderMetrics<br>response = requests.get(<br>    ENDPOINT<br>    + &quot;/sales/v1/orderMetrics&quot;<br>    + &quot;?&quot;<br>    + urllib.parse.urlencode(request_params),<br>    headers = {<br>        &quot;x-amz-access-token&quot;: access_token<br>    }<br>)<br>print(f&#39;Response code={response.status_code}&#39;)<br><br><br>if(response.status_code!=200):<br><br>    print(f&#39;-- Wrong response code -- exit!&#39;)<br><br>    # Show error<br>    data = json.loads(response.content)<br>    print(data)<br><br>else:<br><br>    #process response content as json<br>    data = json.loads(response.content)<br><br>    #print formatted json just for test<br>    pretty_json = json.dumps(data, indent=4, ensure_ascii=False)<br>    print(pretty_json)</pre><p>If you run the script, you’ll get a result like this:</p><pre>interval= 2024-04-05T00:00:00.000-07:00--2024-10-02T23:59:59.000-07:00<br>Load access_token from file...<br>Response code=200<br>{<br>    &quot;payload&quot;: [<br>        {<br>            &quot;interval&quot;: &quot;2024-04-05T00:00-07:00--2024-04-06T00:00-07:00&quot;,<br>            &quot;unitCount&quot;: 716,<br>            &quot;orderItemCount&quot;: 688,<br>            &quot;orderCount&quot;: 695,<br>            &quot;averageUnitPrice&quot;: {<br>                &quot;amount&quot;: 32.83,<br>                &quot;currencyCode&quot;: &quot;USD&quot;<br>            },<br>            &quot;totalSales&quot;: {<br>                &quot;amount&quot;: 23505.44,<br>                &quot;currencyCode&quot;: &quot;USD&quot;<br>            }<br>        },<br>        {<br>            &quot;interval&quot;: &quot;2024-04-06T00:00-07:00--2024-04-07T00:00-07:00&quot;,<br>            &quot;unitCount&quot;: 643,<br>            &quot;orderItemCount&quot;: 615,<br>            &quot;orderCount&quot;: 613,<br>            &quot;averageUnitPrice&quot;: {<br>                &quot;amount&quot;: 33.60,<br>                &quot;currencyCode&quot;: &quot;USD&quot;<br>            },<br>            &quot;totalSales&quot;: {<br>                &quot;amount&quot;: 21601.67,<br>                &quot;currencyCode&quot;: &quot;USD&quot;<br>            }<br>        },<br>...</pre><h4>Parsing the Result into a DataFrame</h4><p>To process the result, we will iterate over all items in data[&#39;payload&#39;], convert the time, and extract unitCount, orderCount, and totalSales.</p><pre>#define list with processed data for dataframe<br>METRICS = []<br><br>#Iterate all items in payload section<br>for item in data[&#39;payload&#39;]:<br><br>    #get first date and part before first T<br>    date_str = item[&#39;interval&#39;].split(&quot;--&quot;)[0].split(&quot;T&quot;)[0]<br>    date_obj = datetime.strptime(date_str, &quot;%Y-%m-%d&quot;) #convert to datetime object<br>    <br>    #get all variables<br>    date =                  date_obj<br>    unitCount =             item[&#39;unitCount&#39;]<br>    orderCount =            item[&#39;orderCount&#39;]<br>    averageUnitPrice =      item[&#39;averageUnitPrice&#39;][&#39;amount&#39;]<br>    totalSales =            item[&#39;totalSales&#39;][&#39;amount&#39;]<br>    currencyCode =          item[&#39;totalSales&#39;][&#39;currencyCode&#39;]<br>    <br>    METRICS.append(dict(<br>        date =              date,<br>        unitCount =         unitCount,<br>        orderCount =        orderCount,<br>        averageUnitPrice =  averageUnitPrice,<br>        totalSales =        totalSales,<br>        currencyCode =      currencyCode,<br>    ))<br><br>print(&#39;/// Creating dataframe...&#39;)<br>frame = pd.DataFrame(METRICS)<br>print(frame)</pre><p>The result will look like this:</p><pre>          date  unitCount  orderCount  averageUnitPrice  totalSales currencyCode<br>0   2024-04-05        716         688             32.83    23505.44          USD<br>1   2024-04-06        643         615             33.60    21601.67          USD<br>2   2024-04-07        770         739             33.88    26084.27          USD<br>3   2024-04-08        595         575             33.53    19949.94          USD<br>4   2024-04-09        670         639             33.66    22549.75          USD<br>..         ...        ...         ...               ...         ...          ...<br>176 2024-09-28       1213        1160             33.07    40110.32          USD<br>177 2024-09-29       1480        1442             33.17    49087.84          USD<br>178 2024-09-30       1447        1399             33.47    48435.10          USD<br>179 2024-10-01       1313        1271             33.60    44123.27          USD<br>180 2024-10-02         57          54             31.53     1797.26          USD</pre><h4>Plotting Sales Graphs</h4><p>Now, we can plot a sales graph by day. It should match the graph from the Amazon Seller Central account.</p><p>We will use the seaborn library and make two graphs one under the other.</p><pre>import seaborn as sns<br>import matplotlib.pyplot as plt<br><br>#Remove last day (to remove partial stat for current day)<br>frame = frame.iloc[:-1]<br><br># Create a figure with two graphs located vertically (one below the other)<br>fig, (ax1, ax2) = plt.subplots(2, 1, figsize=(10, 10))<br><br># First Graph - for sales<br>sns.lineplot(x=&#39;date&#39;, y=&#39;totalSales&#39;, data=frame, marker=&#39;o&#39;, ax=ax1, color=&quot;red&quot;)<br>ax1.set_title(&#39;Total Sales Over Time&#39;)<br>ax1.set_xlabel(&#39;Date&#39;)<br>ax1.set_ylabel(&#39;Total Sales&#39;)<br>ax1.tick_params(axis=&#39;x&#39;, rotation=45)<br>ax1.grid(True, which=&#39;both&#39;, linestyle=&#39;--&#39;, linewidth=0.5)<br><br># Second Graph - для unitCount<br>sns.lineplot(x=&#39;date&#39;, y=&#39;unitCount&#39;, data=frame, marker=&#39;o&#39;, ax=ax2, color=&quot;green&quot;)<br>ax2.set_title(&#39;Unit Count Over Time&#39;)<br>ax2.set_xlabel(&#39;Date&#39;)<br>ax2.set_ylabel(&#39;Unit Count&#39;)<br>ax2.tick_params(axis=&#39;x&#39;, rotation=45)<br>ax2.grid(True, which=&#39;both&#39;, linestyle=&#39;--&#39;, linewidth=0.5)<br><br># Show graphs<br>plt.tight_layout()<br>plt.show()</pre><p>The result:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/1*2ojFZv71nVG4Q-_KYxdghg.png" /><figcaption>Sales Performance with Order Metrics. Total Sales and Unit Count over time Graph built using Seaborn</figcaption></figure><h4>Advanced Version: ASIN-Specific Data</h4><p>To build statistics not only for overall sales but also for each ASIN (Amazon product ID), we will substitute the ASIN in the filter and make a separate request for each product. Since the SP API rate limit for this method is 0.5 requests per second (allowing two requests per second), iterating over several hundred products won’t be an issue (we can handle 150 products in about five minutes).</p><p>You can define the product list as a static list:</p><pre>asin_list = [&#39;ASIN1&#39;, &#39;ASIN2&#39;, &#39;ASIN3&#39;.... ]</pre><p>Or, preferably, load it from the results of an inventory query [<a href="https://andrewkushnerov.medium.com/amazon-sp-api-get-inventory-and-prices-with-python-3226b980bd79">4</a>]. We load processed inventory from csv file.</p><pre># Load asins from inventory dataframe<br>inventory_path = os.path.join(os.getcwd(), &#39;data&#39;, &#39;processed_inventory.csv&#39;)<br>inventory_frame = pd.read_csv(inventory_path)<br>asin_list = inventory_frame[&#39;asin&#39;].unique().tolist()</pre><p>Now, let’s write a script to load data for all products in a loop:</p><pre>import os, time, pytz<br>import requests, urllib.parse, json<br>from sp_api_functions import get_lwa_access_token<br>from datetime import datetime, timedelta, timezone<br>import pandas as pd<br><br>#Define endpoint and marketplace ID<br>ENDPOINT = &quot;https://sellingpartnerapi-na.amazon.com&quot;<br>MARKETPLACE_ID = &#39;ATVPDKIKX0DER&#39; #US<br><br>#Set time interval<br>today = datetime.now().astimezone(pytz.timezone(&#39;US/Pacific&#39;))<br><br>start_time = today.replace(hour=23, minute=59, second=59, microsecond=59)<br>start_time_iso = start_time.isoformat(timespec=&#39;milliseconds&#39;).replace(&quot;+00:00&quot;, &quot;Z&quot;)<br><br>end_time = today.replace(hour=0, minute=0, second=0, microsecond=0) - timedelta(days=180)<br>end_time_iso = end_time.isoformat(timespec=&#39;milliseconds&#39;).replace(&quot;+00:00&quot;, &quot;Z&quot;)<br><br>interval = f&#39;{end_time_iso}--{start_time_iso}&#39;<br>print(&#39;interval=&#39;, interval)<br><br><br># Load asins from inventory dataframe<br>inventory_path = os.path.join(os.getcwd(), &#39;data&#39;, &#39;processed_inventory.csv&#39;)<br>inventory_frame = pd.read_csv(inventory_path)<br><br>asin_list = inventory_frame.sort_values(by=&#39;fulfillableQuantity&#39;, ascending=False)[&#39;asin&#39;].unique().tolist()<br>print(&#39;asin_list=&#39;, asin_list)<br><br><br>#Step 1 - load data from SP API<br>DATA = {} #define dict with loaded data (asin=&gt;json)<br><br>i=0<br>for asin in asin_list:<br><br>    print(f&#39;LOAD ASIN {asin} | {i} from {len(asin_list)}&#39;)<br><br>    # Set Request params<br>    request_params = {<br>        &quot;granularity&quot;: &quot;Day&quot;,<br>        &quot;interval&quot;: interval,<br>        &quot;marketplaceIds&quot;: MARKETPLACE_ID,<br>        &quot;asin&quot;: asin,<br>        &#39;granularityTimeZone&#39;: &#39;US/Pacific&#39;,<br>        &quot;buyerType&quot;: &quot;All&quot;,<br>        &quot;firstDayOfWeek&quot;: &quot;Monday&quot;,  # if granularity=Week selected<br>    }<br><br>    # Get access token<br>    access_token = get_lwa_access_token()<br><br>    # Call orderMetrics<br>    response = requests.get(<br>        ENDPOINT<br>        + &quot;/sales/v1/orderMetrics&quot;<br>        + &quot;?&quot;<br>        + urllib.parse.urlencode(request_params),<br>        headers={<br>            &quot;x-amz-access-token&quot;: access_token<br>        }<br>    )<br>    time.sleep(2) # Add a 2-second delay to comply with rate limits<br>    print(f&#39;Response code={response.status_code}&#39;)<br><br>    if (response.status_code != 200):<br><br>        print(f&#39;-- Wrong response code -- exit!&#39;)<br><br>        # Show error<br>        data = json.loads(response.content)<br>        print(data)<br><br>    else:<br><br>        data = json.loads(response.content)<br>        DATA[asin] = data<br><br>    i+=1</pre><p>In the second step, we’ll process the DATA structure where the retrieved JSON data for each ASIN is stored, converting it into a DataFrame.</p><pre>#Step 2 - Prepare Data for dataframe<br>METRICS = []<br>for asin, data in DATA.items():<br><br>    # Iterate all items in payload section<br>    for item in data[&#39;payload&#39;]:<br><br>        # get first date and part before first T<br>        date_str = item[&#39;interval&#39;].split(&quot;--&quot;)[0].split(&quot;T&quot;)[0]<br>        date_obj = datetime.strptime(date_str, &quot;%Y-%m-%d&quot;)  # convert to datetime object<br><br>        date = date_obj<br>        unitCount = item[&#39;unitCount&#39;]<br>        orderCount = item[&#39;orderCount&#39;]<br>        totalSales = item[&#39;totalSales&#39;][&#39;amount&#39;]<br>        currencyCode = item[&#39;totalSales&#39;][&#39;currencyCode&#39;]<br><br>        METRICS.append(dict(<br>            date=date,<br>            asin=asin,<br>            unitCount=unitCount,<br>            orderCount=orderCount,<br>            totalSales=totalSales,<br>            currencyCode=currencyCode,<br>        ))</pre><p>Now, we’ll create the DataFrame:</p><pre>#Step 3 - Create dataframe<br>print(&#39;/// Creating dataframe...&#39;)<br>frame = pd.DataFrame(METRICS)<br>print(frame)</pre><p>The result will look something like this:</p><pre>          date     asin  unitCount  orderCount  totalSales currencyCode<br>0   2024-04-05  ASIN001         30          29      679.56          USD<br>1   2024-04-06  ASIN001         49          46     1099.15          USD<br>2   2024-04-07  ASIN001         41          37      919.44          USD<br>3   2024-04-08  ASIN001         42          37      939.33          USD<br>4   2024-04-09  ASIN001         40          35      899.45          USD<br>..         ...      ...        ...         ...         ...          ...<br>900 2024-09-28  ASIN005         11          10      259.87          USD<br>901 2024-09-29  ASIN005         13          13      299.85          USD<br>902 2024-09-30  ASIN005         22          20      499.75          USD<br>903 2024-10-01  ASIN005         24          24      539.73          USD<br>904 2024-10-02  ASIN005          4           4       99.95          USD</pre><p>I’ve omitted the averageUnitPrice field because it’s a calculated value that cannot be aggregated, and it’s easily derived as totalSales / unitCount.</p><h4>Plotting Graphs for ASIN-Specific Data</h4><p>Now, let’s create sales graphs for each ASIN over time, focusing on totalSales and unitCount by day.</p><p>First, select top 5 high-sales asins over time (you can choose another count)</p><pre>#Define the number of top asins<br>number_of_top_asins = 5<br><br># Group by asin and sum of totalSales over time<br>total_sales_by_asin = frame.groupby(&#39;asin&#39;)[&#39;totalSales&#39;].sum().reset_index()<br><br># Get top asins<br>top_asins = total_sales_by_asin.sort_values(by=&#39;totalSales&#39;, ascending=False).head(number_of_top_asins)<br><br># Filter the original dataframe to include only rows with top asins<br>top_asins_frame = frame[frame[&#39;asin&#39;].isin(top_asins[&#39;asin&#39;].tolist())]</pre><p>Second, let’s write code to plot a graph</p><pre>import seaborn as sns<br>import matplotlib.pyplot as plt<br><br># Find the latest date and filter the frame to remove last date<br>latest_date = top_asins_frame[&#39;date&#39;].max()<br>top_asins_frame = top_asins_frame[top_asins_frame[&#39;date&#39;] != latest_date]<br><br># Create the plot with two subplots: one for totalSales and one for unitCount<br>fig, (ax1, ax2) = plt.subplots(2, 1, figsize=(10, 8), sharex=True)<br><br># Plot for totalSales<br>sns.lineplot(data=top_asins_frame, x=&#39;date&#39;, y=&#39;totalSales&#39;, hue=&#39;asin&#39;, ax=ax1)<br>ax1.set_title(&#39;Total Sales over Time by ASIN&#39;)<br>ax1.set_ylabel(&#39;Total Sales (USD)&#39;)<br>ax1.grid(True, which=&#39;both&#39;, linestyle=&#39;--&#39;, linewidth=0.5)<br>ax1.legend(loc=&#39;center left&#39;, bbox_to_anchor=(1, 0.5))<br><br># Plot for unitCount<br>sns.lineplot(data=top_asins_frame, x=&#39;date&#39;, y=&#39;unitCount&#39;, hue=&#39;asin&#39;, ax=ax2)<br>ax2.set_title(&#39;Unit Count over Time by ASIN&#39;)<br>ax2.set_ylabel(&#39;Units Sold&#39;)<br>ax2.set_xlabel(&#39;Date&#39;)<br>ax2.grid(True, which=&#39;both&#39;, linestyle=&#39;--&#39;, linewidth=0.5)<br>ax2.legend(loc=&#39;center left&#39;, bbox_to_anchor=(1, 0.5))<br><br># Display the plot<br>plt.tight_layout()<br>plt.show()</pre><p>The resulting graph will show sales and units sold by top ASIN over time.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/1*REMbkZyVd162vy7MxPmLsg.png" /><figcaption>Sales Performance with Order Metrics. Total Sales and Unit Count for selected sku’s over time Graph built using Seaborn.</figcaption></figure><h4>Further Development</h4><p>Beyond individual ASIN data, the Order Metrics method offers filters for buyerType (B2B/B2C), fulfillment network (AFN/MFN), and marketplaces. By iterating over these values and collecting the data in a detailed DataFrame, you can build more sophisticated statistics.</p><h4>About Author</h4><p>The author of this article is the Founder and CTO of <a href="https://sellerflux.com/">SellerFlux</a>. We are <strong>Empowering E-commerce with Data-Driven Solutions</strong> and specialize in providing software solutions specifically designed for e-commerce businesses.</p><h4>Links:</h4><ol><li>Amazon SP-API Sales API — getOrderMetrics: <a href="https://developer-docs.amazon.com/sp-api/docs/sales-api-v1-reference">https://developer-docs.amazon.com/sp-api/docs/sales-api-v1-reference</a></li><li>Amazon SP-API: Get Orders with Python: <a href="https://medium.com/@andrewkushnerov/amazon-sp-api-get-orders-with-python-7b7e913d87ea">https://medium.com/@andrewkushnerov/amazon-sp-api-get-orders-with-python-7b7e913d87ea</a></li><li>Amazon SP-API: Get Order Items with Python: <a href="https://medium.com/@andrewkushnerov/amazon-sp-api-get-order-items-with-python-68506bbde58a">https://medium.com/@andrewkushnerov/amazon-sp-api-get-order-items-with-python-68506bbde58a</a></li><li>Amazon SP-API: Get Inventory and Prices with Python: <a href="https://andrewkushnerov.medium.com/amazon-sp-api-get-inventory-and-prices-with-python-3226b980bd79">https://andrewkushnerov.medium.com/amazon-sp-api-get-inventory-and-prices-with-python-3226b980bd79</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=52fddfc98254" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Automating Analytics with Michaels Retail SKU Reports]]></title>
            <link>https://andrewkushnerov.medium.com/automating-analytics-with-michaels-retail-sku-reports-d7d4df4e49bb?source=rss-27f59dbe18f4------2</link>
            <guid isPermaLink="false">https://medium.com/p/d7d4df4e49bb</guid>
            <category><![CDATA[retail]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[automation]]></category>
            <dc:creator><![CDATA[Andrei Kushniarou]]></dc:creator>
            <pubDate>Tue, 01 Oct 2024 19:31:45 GMT</pubDate>
            <atom:updated>2024-10-01T19:31:45.706Z</atom:updated>
            <content:encoded><![CDATA[<p>Michaels Stores, one of the largest Art &amp; Crafts hypermarkets in the US and Canada. Recently, as part of building Retail Data Collection and Weekly Sales Reporting, I had the task of extracting sales statistics from Michaels and processing it in a Data Warehouse.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qCpJErKMSoe8qTyeUOWcOg.png" /><figcaption>Automating Analytics with Michaels Retail SKU Reports. Image by the Author.</figcaption></figure><h4>Table of Contents</h4><ul><li>Michaels Vendor SKU Detail Reports</li><li>Report file processing script</li><li>Visualization</li><li>Further automation</li></ul><h4>Michaels Vendor SKU Detail Reports</h4><p>The report containing weekly retail sales data is available in the Vendor SKU Detail Report, which can be downloaded from the slightly outdated website <a href="https://vendorconnect.com/">https://vendorconnect.com/</a>. The site suggests using the now-defunct Internet Explorer to download the reports.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ok5QDAieysx23e_Ev9oZfA.png" /></figure><p>Let’s take a look at what the weekly report looks like — it’s an XLS file that is actually a CSV with a non-standard tab separator and an XLS extension. It contains around 50 columns.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2-fnNQX5zObRxc5Y24pJGQ.png" /><figcaption>File format of Michaels Vendor SKU Detail Report</figcaption></figure><p>The report provides SKU-based data on sales, units, and the number of stores where each SKU is available. Besides the latest week, the data includes the previous three weeks. However, this data is spread across columns rather than rows, making it somewhat inconvenient.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*47DQcrtqyuNwZWLhDUDyDg.png" /><figcaption>Sales data for the last 4 weeks in Michaels Vendor SKU Detail Report</figcaption></figure><p>Additionally, there is no date reference in the columns or fields, so we need to extract the date from the filename.</p><h4>Processing Script</h4><p>I will use pandas to read all XLS (actually CSV) files in the folder, then transform the date and extract data from all columns to organize everything in one dataframe.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/638/1*s6uyiA5oSSdNZNte1CslOA.png" /><figcaption>Directory structure to store sources files</figcaption></figure><p>Let’s import the necessary libraries:</p><pre>import os, re<br>import pandas as pd<br>from datetime import datetime, timedelta, date</pre><p>We’ll iterate over the files in the “sources” folder:</p><pre>#define source dir with all files<br>source_dir = os.path.join(os.getcwd(), &#39;data&#39;, &#39;michaels&#39;, &#39;sources&#39;)<br><br>#Result Array with sku - date and units/sales info<br>data = {}<br><br>#Define pattern for XLS file<br>pattern = &#39;^\d+_SKUwe\d{6}\.xls$&#39;<br><br>#Iterate files<br>for fname in os.listdir(source_dir):<br><br>    print(f&#39;/// Process {fname} file...&#39;)<br><br>    if not re.search(pattern, fname):<br>        print(&quot;  -- Filename not matched to pattern&quot;)<br>    else:<br>        print(f&quot; -- Filename {fname} matched&quot;)<br><br>        #Parse file name and get month, day and year from filename<br>        pattern = r&#39;(\d{2})(\d{2})(\d{2})\.xls$&#39;<br>        m = re.search(pattern, fname)<br>        mo = int(m[1])<br>        d = int(m[2])<br>        y = int(&#39;20&#39;+m[3])<br><br>        report_date = datetime(day=d, month=mo, year=y)<br>        print(f&#39; -- Got day={d} month={mo} year={y} from filename | report_date={report_date\}&#39;)<br></pre><p>If we run the script now, it should recognize the files in the folder and parse the dates:</p><pre>/// Process 12345_SKUwe020324.xls file...<br>  -- Filename 12345_SKUwe020324.xls matched<br>  -- Got day=3 month=2 year=2024 from filename | report_date=2024-02-03 00:00:00<br>/// Process 12345_SKUwe010624.xls file...<br>  -- Filename 12345_SKUwe010624.xls matched<br>  -- Got day=6 month=1 year=2024 from filename | report_date=2024-01-06 00:00:00<br>...<br></pre><p>Now, we’ll read each file using pandas, iterate through the rows and columns to extract data for the current and previous weeks. The data will be grouped by SKU and date into the data structure.</p><pre>        ...<br>        <br>        # This is tab separated csv, not xls originally<br>        # so we can use pandas to read file<br>        report_frame = pd.read_csv(os.path.join(source_dir, fname), sep=&quot;\t&quot;)<br>        <br>        #Iterate all rows<br>        for row in report_frame.to_dict(orient=&quot;records&quot;):<br>        <br>            sku = row[&#39;vendor product number&#39;].replace(&#39;=&#39;, &#39;&#39;).replace(&#39;&quot;&#39;, &#39;&#39;)<br>            #print(sku) #show sku<br>        <br>            #So we need for every sku process current column and all column with historical data<br>        <br>            #array with..<br>            #1. number of weeks minus report date,<br>            #2. name of variable with units<br>            #3. name of variable with sales<br>            #4. name of variable with store qty<br>            vars = [<br>                (0, &#39;lw total units&#39;, &#39;lw total pos $&#39;, &#39;pog stores&#39;),<br>                (1, &#39;-2 weeks ago total units&#39;, &#39;-2 weeks ago total pos $&#39;, &#39;pog stores&#39;),<br>                (2, &#39;-3 weeks ago total units&#39;, &#39;-3 weeks ago total pos $&#39;, &#39;pog stores&#39;),<br>                (3, &#39;-4 weeks ago total units&#39;, &#39;-4 weeks ago total pos $&#39;, &#39;pog stores&#39;)<br>            ]<br>        <br>            for (minus_week, units_field, sales_field, stores_field) in vars:<br>        <br>                _date = report_date<br>                if minus_week&gt;0:<br>                    _date = _date - timedelta(weeks=minus_week)<br>        <br>                units = row[units_field]<br>                sales = row[sales_field]<br>                stores_qty = row[stores_field]<br>        <br>                if sku not in data:<br>                    data[sku] = {}<br>        <br>                if _date.strftime(&quot;%Y-%m-%d&quot;) not in data:<br>                    data[sku][_date.strftime(&quot;%Y-%m-%d&quot;)] = {<br>                        &#39;date&#39;: _date,<br>                        &#39;units&#39;:units,<br>                        &#39;sales&#39;:sales,<br>                        &#39;stores_qty&#39;:stores_qty<br>                    }</pre><p>Next, we’ll iterate over the resulting structure and create a flat list of data for the dataframe.</p><pre>#Step2 - iterate data and create rows for dataframe<br><br>rows = []<br>for sku,lines in data.items():<br>    for _,line in lines.items():<br><br>        #print(line)<br><br>        rows.append({<br>            &#39;date&#39;:         line[&#39;date&#39;],<br>            &#39;year&#39;:         line[&#39;date&#39;].strftime(&quot;%Y&quot;),<br>            &#39;week&#39;:         line[&#39;date&#39;].strftime(&quot;%V&quot;),<br>            &#39;sku&#39;:          sku,<br>            &#39;sales&#39;:        line[&#39;sales&#39;],<br>            &#39;units&#39;:        line[&#39;units&#39;],<br>            &#39;stores_qty&#39;:   line[&#39;stores_qty&#39;],<br>        })<br><br>#Create and show dataframe<br>frame = pd.DataFrame(rows)<br>print(frame)<br><br>#Save to file if needed<br>frame.to_csv(&#39;./data/michaels/michaels-processed.csv&#39;)</pre><p>Script result and dataframe output:</p><pre>    date       year   week sku      sales    units      stores_qty<br>0  2024-02-03  2024   05  SKU-5626  1233.82   55.0      1099.0<br>1  2024-01-27  2024   04  SKU-5626  1431.81   68.0      1099.0<br>2  2024-01-20  2024   03  SKU-5626  1432.06   74.0      1099.0<br>3  2024-01-13  2024   02  SKU-5626  1739.34   86.0      1099.0<br>4  2024-01-06  2024   01  SKU-5626  2199.67  121.0      1099.0<br>..        ...   ...  ...       ...      ...    ...         ...<br>67 2023-12-16  2023   50  SKU-5844  8897.59  339.0      1178.0<br>68 2024-02-24  2024   08  SKU-5844  1969.26   71.0      1187.0<br>69 2024-02-17  2024   07  SKU-5844  1835.07   70.0      1186.0<br>70 2024-02-10  2024   06  SKU-5844  2132.86   81.0      1184.0<br>71 2024-03-02  2024   09  SKU-5844  1469.82   65.0      1187.0</pre><h4>Visualization</h4><p>Let’s build a simple visualization using good old seaborn. We’ll plot Sales and Units:</p><pre>import seaborn as sns<br>import matplotlib.pyplot as plt<br><br># Create a figure with two graphs located vertically<br>fig, (ax1, ax2) = plt.subplots(2, 1, figsize=(10, 10))<br><br># First graph for sales<br>sns.lineplot(x=&#39;date&#39;, y=&#39;sales&#39;, hue=&#39;sku&#39;, data=frame, marker=&#39;o&#39;, linestyle=&#39;-&#39;, ax=ax1)<br>ax1.set_title(&#39;Sales Over Time by SKU&#39;)<br>ax1.set_xlabel(&#39;Date&#39;)<br>ax1.set_ylabel(&#39;Sales&#39;)<br>ax1.tick_params(axis=&#39;x&#39;, rotation=45)<br><br># Second graph for units<br>sns.lineplot(x=&#39;date&#39;, y=&#39;units&#39;, hue=&#39;sku&#39;, data=frame, marker=&#39;x&#39;, linestyle=&#39;--&#39;, ax=ax2)<br>ax2.set_title(&#39;Units Over Time by SKU&#39;)<br>ax2.set_xlabel(&#39;Date&#39;)<br>ax2.set_ylabel(&#39;Units&#39;)<br>ax2.tick_params(axis=&#39;x&#39;, rotation=45)<br><br># Show graphs<br>plt.tight_layout()<br>plt.show()</pre><p>The result graph</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/1*ns-C2-gHvP7bMT5yH3b-Fg.png" /><figcaption>The result graph with Sales and Units over Time from Michaels Retail Vendor SKU Detail Reports</figcaption></figure><h3>Further Automation</h3><p>Daily report downloading can be automated using Python’s requests module since logging into such a site presents no difficulty. All reports can be stored in a separate folder and processed together.</p><h4>Author</h4><p>The author of this article is the Founder and CTO of <a href="https://sellerflux.com/">SellerFlux</a>. We Empower E-commerce with Data-Driven Solutions and specialize in providing software solutions specifically designed for e-commerce businesses.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d7d4df4e49bb" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Tracking Negative Reviews on Amazon with Python]]></title>
            <link>https://andrewkushnerov.medium.com/tracking-negative-reviews-on-amazon-with-python-61808d7e494c?source=rss-27f59dbe18f4------2</link>
            <guid isPermaLink="false">https://medium.com/p/61808d7e494c</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[web-scraping]]></category>
            <category><![CDATA[e-commerce-business]]></category>
            <category><![CDATA[amazon]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Andrei Kushniarou]]></dc:creator>
            <pubDate>Mon, 16 Sep 2024 17:21:32 GMT</pubDate>
            <atom:updated>2024-09-26T17:10:54.063Z</atom:updated>
            <content:encoded><![CDATA[<p>Keeping track of negative comments on Amazon is crucial, as they can significantly impact sales. I’ve seen cases where clients only found out about negative reviews after noticing a drop in sales weeks later.</p><p>To prevent such situations, we’ll develop a Python tracker script that scrapes Customer Reviews and promptly notifies you when a negative comment (customer review rating less than or equal to three) appears on one of your products. The script will visit the product page, fetch all new Customer Reviews, identify which reviews are new, and send notifications for negative ones.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AONjYgfy6M49ABGaQwjaIQ.png" /><figcaption>Tracking Negative Reviews on Amazon with Python. Image by the Author</figcaption></figure><p>Notifications about new negative Customer Reviews will be sent via a Telegram bot. Telegram is convenient for receiving notifications, and you can add the bot to any private group.</p><h4>Table of Contents</h4><ul><li>How and Where to Fetch Customer Reviews</li><li>Script for Collecting Fresh Reviews</li><li>Identifying New and Negative Reviews for Notification</li><li>Sending Notifications via Telegram Bot</li><li>Notes on Bulk Scraping</li></ul><h4>How to Fetch Customer Reviews from an Amazon Page</h4><p>A few years ago, Amazon closed access to Customer Reviews through its API, so the only way to collect comments is via scraping.</p><p>On the product’s main page, reviews are not displayed in the order they were added, so you need to go to the reviews page, e.g., <a href="https://www.amazon.com/product-reviews/B0CPG6JRXL">https://www.amazon.com/product-reviews/B0CPG6JRXL</a>, and select “Sort By: Most Recent.”</p><p>The complete link will look like this: <a href="https://www.amazon.com/product-reviews/B0CPG6JRXL/ref=cm_cr_arp_d_viewopt_srt?ie=UTF8&amp;reviewerType=all_reviews&amp;sortBy=recent&amp;pageNumber=1">https://www.amazon.com/product-reviews/B0CPG6JRXL/ref=cm_cr_arp_d_viewopt_srt?ie=UTF8&amp;reviewerType=all_reviews&amp;sortBy=recent&amp;pageNumber=1</a>.</p><p>This page will always display the last ten Customer Reviews, which is sufficient for tracking new negative comments.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5IV_R09DYH_B0XdD7FZN1Q.png" /><figcaption>Page with last Customer Reviews for Product on Amazon.com</figcaption></figure><p>I’ve been into rock climbing for the past few months, so I was tried to select for this article amazon products for climbers. When I checked out carabiners, I noticed there were hardly any negative reviews. Why? Well, those who could leave a bad review usually end up… unable to. You get the idea! 😅</p><h4>Script for Collecting Fresh Reviews</h4><p>A few days ago, <a href="https://andrewkushnerov.medium.com/scraping-customer-reviews-from-amazon-product-page-with-python-4593fdbe00da">I wrote a detailed article on how to collect Customer Reviews from Amazon product pages using Python</a>, requests, and lxml libraries.</p><p>Here, we’ll use a slightly simplified version, as we only need to collect the freshest Customer Reviews, so gathering just the first page of comments will significantly speed up the process.</p><p>First, let’s import the necessary libraries, define a list of ASINs (Amazon Product IDs) to search for negative comments, and set up a session and headers:</p><pre>import pandas as pd<br>import requests<br>from lxml import html<br>import re, time, os<br>from datetime import datetime<br><br>#Define list of asins<br>asins = [&#39;B0CPG6JRXL&#39;, &#39;B01MXORFUD&#39;, &#39;B09M82M78M&#39;]<br><br>#Create session<br>session = requests.Session()<br><br>#Define headers<br>headers = {<br>    &#39;User-Agent&#39;: &#39;Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36&#39;,<br>    &#39;Accept&#39;: &#39;text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8&#39;,<br>    &#39;Accept-Encoding&#39;: &#39;gzip, deflate, br&#39;,<br>    &#39;Accept-Language&#39;: &#39;en-US,en;q=0.9&#39;,<br>    &#39;Connection&#39;: &#39;keep-alive&#39;,<br>    &#39;DNT&#39;: &#39;1&#39;,  # Do Not Track request header<br>    &#39;Upgrade-Insecure-Requests&#39;: &#39;1&#39;,<br>    &#39;Referer&#39;: &#39;https://amazon.com/&#39;,<br>}</pre><p>We then iterate over each ASIN, making two requests: one to the product page and one to the comments page. This simulates a user who first opens the product page and then the comments section. We parse all fields and save the data in a structure called data.</p><pre>data = [] #List for save reviews data for dataframe<br><br>#Iterate asins<br>for asin in asins:<br><br>    print(f&#39;/// ASIN {asin}&#39;)<br><br>    #Load asin page<br>    url = f&#39;https://www.amazon.com/dp/{asin}&#39;<br>    print(f&#39;Load {url}...&#39;)<br>    response = session.get(url, headers=headers, verify=False)<br>    headers[&#39;Referer&#39;] = url #save url as referrer for next query<br>    print(response)<br>    time.sleep(2)<br><br>    #Load comments page with paging<br>    url = f&#39;https://www.amazon.com/product-reviews/{asin}/ref=cm_cr_arp_d_viewopt_srt?ie=UTF8&amp;reviewerType=all_reviews&amp;sortBy=recent&amp;pageNumber=1&#39;<br>    print(f&#39;Load {url}...&#39;)<br>    response = session.get(url, headers=headers, verify=False)<br>    headers[&#39;Referer&#39;] = url  # save url as referrer for next query<br>    print(response)<br>    time.sleep(2)<br><br>    #Get page tree and get reviews<br>    tree = html.fromstring(response.content)<br>    reviews = tree.xpath(&#39;//div[@data-hook=&quot;review&quot;]&#39;)<br><br>    print(f&#39;Reviews found on the page: {len(reviews)}&#39;)<br>    if(len(reviews)==0):<br>        print(response.content)<br><br>    # Iterating reviews<br>    for review in reviews:<br><br>        #Get review id<br>        review_id = review.xpath(&#39;./@id&#39;)<br>        review_id = review_id[0] if review_id else None<br><br>        # Get Author name<br>        name = review.xpath(&#39;.//span[@class=&quot;a-profile-name&quot;]/text()&#39;)<br>        name = name[0] if name else None<br><br>        # Get Title<br>        title = review.xpath(&#39;.//a[@data-hook=&quot;review-title&quot;]/span/text()&#39;)<br>        title = title[0] if title else None<br><br>        # Try get Title by alternative way<br>        if not title:<br>            title = review.xpath(&#39;.//span[@data-hook=&quot;review-title&quot;]/span[@class=&quot;cr-original-review-content&quot;]/text()&#39;)<br>            title = title[0] if title else None<br><br>        # Get Rating<br>        rating_str = review.xpath(&#39;.//i[@data-hook=&quot;review-star-rating&quot;]/span/text()&#39;)<br>        rating_str = rating_str[0] if rating_str else None<br><br>        # Try Rating by alternative way<br>        if not rating_str:<br>            rating_str = review.xpath(&#39;.//i[@data-hook=&quot;cmps-review-star-rating&quot;]/span[@class=&quot;a-icon-alt&quot;]/text()&#39;)<br>            rating_str = rating_str[0] if rating_str else None<br><br>        # Get Author name<br>        date_str = review.xpath(&#39;.//span[@data-hook=&quot;review-date&quot;]/text()&#39;)<br>        date_str = date_str[0] if date_str else None<br><br>        # Get Review Text<br>        review_text = review.xpath(&#39;.//span[@data-hook=&quot;review-body&quot;]//span/text()&#39;)<br>        review_text = review_text[0] if review_text else None<br><br>        # Get Country and comment date from<br>        country = None<br>        date = None<br><br>        if date_str:<br>            match = re.search(r&#39;Reviewed in ([A-Za-z\s]+) on ([A-Za-z]+\s\d{1,2},\s\d{4})&#39;, date_str)<br>            if match:<br><br>                # Get country<br>                country = match.group(1).strip()<br><br>                # Get date and convert to datetime<br>                date_strip = match.group(2).strip()<br>                date = datetime.strptime(date_strip, &#39;%B %d, %Y&#39;)<br><br><br>        # Get rating value from rating str<br>        rating = None<br><br>        if rating_str:<br>            match = re.search(r&#39;(\d+(\.\d+)?)&#39;, rating_str)<br>            if match:<br>                rating = float(match.group(1))<br><br>        #Print data<br>        print(f&quot;Id: {review_id}&quot;)<br>        print(f&quot;Name: {name}&quot;)<br>        print(f&quot;Title: {title}&quot;)<br>        print(f&quot;Rating: {rating}&quot;)<br>        print(f&quot;Country: {country}&quot;)<br>        print(f&quot;Date: {date}&quot;)<br>        print(f&quot;Text: {review_text}&quot;)<br>        print(&quot;-&quot; * 40)<br><br>        data.append(dict(<br>            asin = asin,<br>            review_id = review_id,<br>            name = name,<br>            title = title,<br>            rating = rating,<br>            country = country,<br>            date = date,<br>            review_text = review_text<br>        ))</pre><p>Now we can save the data into a DataFrame:</p><pre>#Create result dataframe<br>frame = pd.DataFrame(data)<br>print(frame)<br><br>#Save dataframe if needed<br>frame.to_csv(&#39;data/amazon_reviews_tracker_data.csv&#39;, index=False)</pre><p>After running the script, we get a neat DataFrame with the data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nW15w5WfciMs_gc3YW_22g.png" /><figcaption>Result Dataframe with Last Customer Reviews from selected Amazon Product Pages</figcaption></figure><h4>Identifying New and Negative Reviews for Notification</h4><p>Next, we’ll refine the review-saving process by loading previously downloaded reviews and identifying those Customer Reviews not present in the existing file, i.e., the new ones.</p><pre>#Created dataframe with new data<br>new_data = pd.DataFrame(data)<br>print(f&#39;New reviews data loaded, rows={new_data.shape[0]}&#39;)<br><br>#Define path for existing data<br>file_path = &#39;data/amazon_reviews_tracker_data.csv&#39;<br><br>#Check is file exist<br>if os.path.exists(file_path):<br><br>    #Read existing dataframe from file<br>    existing_data = pd.read_csv(file_path)<br>    print(f&#39;Existing data loaded, rows={existing_data.shape[0]}&#39;)<br><br>    #Add a new data<br>    updated_data = pd.concat([existing_data, new_data], ignore_index=True).drop_duplicates(subset=[&#39;review_id&#39;])<br>else:<br><br>    #Set existing as empty dataframe<br>    existing_data = pd.DataFrame(columns=new_data.columns)<br><br>    # If no file, copy all data from new<br>    updated_data = new_data.copy()<br><br>#Save updated data frame to file<br>updated_data.to_csv(file_path, index=False)</pre><p>Now, let’s extract Customer Reviews with a rating of 3 or less, which are the negative comments that need to be sent as notifications. We’ll gather a list of messages to send:</p><pre>#Identify new entries that are in new_data but not in existing_data<br>new_entries = new_data[~new_data[&#39;review_id&#39;].isin(existing_data[&#39;review_id&#39;])]<br>new_negative_entries = new_entries[new_entries[&#39;rating&#39;] &lt;= 3]<br><br>#Iterate all new negative reviews and create list of messages<br>messages = []<br>for index, row in new_negative_entries.iterrows():<br><br>    messages.append(f&quot;New Negative Customer Review for ASIN {row[&#39;asin&#39;]} found! &quot;<br>                    f&quot;Review_id={row[&#39;review_id&#39;]} | Date={row[&#39;date&#39;]} | &quot;<br>                    f&quot;Name={row[&#39;name&#39;]} | Title={row[&#39;title&#39;]}&quot;)<br><br><br>print(messages)</pre><p>Example output of this part of script that found two negative new Customer Reviews:</p><pre>New reviews data loaded, rows=30<br>Existing data loaded, rows=28<br>[&#39;New Negative Customer Review for ASIN B0CPG6JRXL found! Review_id=R9T1VTMV8PFHT | Date=2024-09-08 00:00:00 | Name=Kevin | Title=Got a return.&#39;, &#39;New Negative Customer Review for ASIN B0CPG6JRXL found! Review_id=R6L5SXRD09YCL | Date=2024-06-20 00:00:00 | Name=DANIEL A FUENMAYOR | Title=Casco Black Diamond&#39;]</pre><h4>Sending Notifications via Telegram Bot</h4><p>Great, now all that’s left is to send our prepared messages about negative Customer Reviews to the Telegram bot.</p><p>I’ve detailed <a href="https://andrewkushnerov.medium.com/how-to-send-notifications-to-telegram-with-python-9ea9b8657bfb">how to create a bot and send messages to Telegram</a> in my article. <a href="https://andrewkushnerov.medium.com/how-to-send-notifications-to-telegram-with-python-9ea9b8657bfb">Check it out</a> to learn how to get a Bot Token and Chat ID.</p><pre>#pip install python-telegram-bot for install package<br>from telegram import Bot<br>import asyncio<br><br>TELEGRAM_BOT_TOKEN = &#39;[DEFINE YOUR BOT_TOKEN HERE]&#39;<br>CHAT_ID = &#39;[DEFINE YOUR CHAT_ID HERE]&#39;<br><br>#Define bot<br>bot = Bot(token=TELEGRAM_BOT_TOKEN)<br><br>async def send_message(text, chat_id):<br>    async with bot:<br>        await bot.send_message(text=text, chat_id=chat_id)<br><br>async def run_bot(messages, chat_id):<br>    text = &#39;\n&#39;.join(messages)<br>    await send_message(text, chat_id)<br><br>if messages:<br>    asyncio.run(run_bot(messages, CHAT_ID))</pre><p>Result of execution in my test group through my bot</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sneCI83YgAOJvaO4AY9Pxw.png" /><figcaption>A new message with negative customer reviews in my test Telegram group</figcaption></figure><p>Of course, using other APIs we can send messages not only to Telegram, but for example to Email, Whatsapp or Slack.</p><p>Now it is enough to set the script to auto-update several times a day to receive timely messages about new Negative Customer Reviews. Automated updates can be implemented using cron jobs on Unix-based systems, Task Scheduler on Windows, or a cloud-based solution like AWS Lambda. I prefer using Airflow.</p><h4>Notes on Bulk Scraping</h4><p>If you have a lot of requests, Amazon might start blocking you if it detects suspicious activity, resulting in CAPTCHAs or server errors, which you should monitor. If you have many ASINs or want to make requests too frequently, consider using proxies.</p><p>I also covered using registration cookies in <a href="https://medium.com/@andrewkushnerov/scraping-customer-reviews-from-amazon-product-page-with-python-4593fdbe00da">my article on scraping Customer Reviews</a>, which reduces the likelihood of errors.</p><pre>#Add auth cookies<br>session.cookies.set(&#39;session-id&#39;, &#39;[INSERT YOUR VALUE HERE]&#39;, domain=&#39;.amazon.com&#39;)<br>session.cookies.set(&#39;ubid-main&#39;, &#39;[INSERT YOUR VALUE HERE]&#39;, domain=&#39;.amazon.com&#39;)<br>session.cookies.set(&#39;x-main&#39;, &#39;[INSERT YOUR VALUE HERE]&#39;, domain=&#39;.amazon.com&#39;)</pre><h4><strong>Author</strong></h4><p>The author of this article is the founder and CTO at <a href="https://sellerflux.com/">sellerflux.com</a>. We empower e-commerce with data-driven solutions and provide <a href="https://sellerflux.com/services">Multiple Services</a> including <a href="https://sellerflux.com/services/data_extraction/">Data Extraction &amp; Integration</a>, and <a href="https://sellerflux.com/services/tracking_notifications/">Tracking and Notifications</a>.</p><h4>Useful Links</h4><ol><li><strong>Scraping Customer Reviews from Amazon Product Page: </strong><a href="https://andrewkushnerov.medium.com/scraping-customer-reviews-from-amazon-product-page-with-python-4593fdbe00da">https://andrewkushnerov.medium.com/scraping-customer-reviews-from-amazon-product-page-with-python-4593fdbe00da</a></li><li><strong>How to send Notifications to Telegram with Python: </strong><a href="https://andrewkushnerov.medium.com/how-to-send-notifications-to-telegram-with-python-9ea9b8657bfb">https://andrewkushnerov.medium.com/how-to-send-notifications-to-telegram-with-python-9ea9b8657bfb</a></li><li><strong>XPATH expression language:</strong> <a href="https://en.wikipedia.org/wiki/XPath">https://en.wikipedia.org/wiki/XPath</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=61808d7e494c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Scraping Customer Reviews from Amazon Product Page with Python]]></title>
            <link>https://andrewkushnerov.medium.com/scraping-customer-reviews-from-amazon-product-page-with-python-4593fdbe00da?source=rss-27f59dbe18f4------2</link>
            <guid isPermaLink="false">https://medium.com/p/4593fdbe00da</guid>
            <category><![CDATA[automation]]></category>
            <category><![CDATA[web-scraping]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[amazon]]></category>
            <category><![CDATA[data-extraction]]></category>
            <dc:creator><![CDATA[Andrei Kushniarou]]></dc:creator>
            <pubDate>Sat, 14 Sep 2024 23:35:41 GMT</pubDate>
            <atom:updated>2024-09-26T16:37:59.316Z</atom:updated>
            <content:encoded><![CDATA[<p>When selling on Amazon, timely tracking of customer reviews for products is a crucial task. Customer reviews are vital for product promotion and successful sales. Gathering these reviews allows for analysis to understand any shortcomings of your product, identify areas for improvement, and most importantly, to promptly address any negative feedback.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7UnIBHHuEyV5AvIl2Ye0ug.png" /><figcaption>Scraping Customer Reviews from Amazon Product Page. Image by the Author.</figcaption></figure><p>A few years ago, Amazon removed direct access to reviews via API, so the only way to obtain them is by scraping the pages. Fortunately, with Python, this isn’t too difficult, and in this article, I’ll show you how to do it.</p><h4>Table of Contents</h4><ul><li>Where to Collect Reviews from Amazon Pages</li><li>Getting Reviews from Amazon — Minimal Script</li><li>Parsing Date, Country, and Rating</li><li>Iterating through Pages</li><li>Authentication via Cookies</li><li>Saving Data to a DataFrame</li><li>Final Script</li><li>Tips for Large-Scale Scraping</li></ul><h4>Where to Collect Reviews from Amazon Pages</h4><p>Let’s start by figuring out how to collect reviews from an Amazon product page. I chose ASIN (it’s Amazon Product Id) B01MXORFUD, which is for the PETZL GRIGRI, a belay device for climbing, something I’ve gotten into over the last six months.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_YbzxGVlxTlAQbhVqwExeQ.png" /><figcaption>Amazon Product Page</figcaption></figure><p>You can open the product page directly via this link: <a href="https://www.amazon.com/dp/B01MXORFUD">https://www.amazon.com/dp/B01MXORFUD</a>. The reviews on the page will be displayed below, and you can collect them directly from there. However, they are sorted by popularity rather than date, and it’s difficult to gather all reviews from this view.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*x7pafn0eYfMNVACo1Ye8Pw.png" /><figcaption>Customer Reviews on Amazon Product Page</figcaption></figure><p>If you look closely, there is a link to a separate page with all the reviews. You can access it via this link: <a href="https://www.amazon.com/product-reviews/B01MXORFUD">https://www.amazon.com/product-reviews/B01MXORFUD</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*25npSfPhN447yTv10FT8xA.png" /><figcaption>Customer Reviews Page</figcaption></figure><p>Just below, there’s a “Sort By” filter that can be set to “Most Recent” to collect the latest reviews first. You may notice that the link changes to <a href="https://www.amazon.com/product-reviews/B01MXORFUD/ref=cm_cr_arp_d_viewopt_srt?sortBy=recent&amp;pageNumber=1">https://www.amazon.com/product-reviews/B01MXORFUD/ref=cm_cr_arp_d_viewopt_srt?sortBy=recent&amp;pageNumber=1</a>, where the pageNumber parameter determines the page number. Therefore, we need to iterate through the pages starting from 1 until there are no more reviews, or, more reliably, until the &quot;Next Page&quot; button becomes inactive.</p><p>It’s important to note that the filter and viewing of pages beyond the first one on the review page are only available to registered users. So, we need to somehow provide the registration data to gather all the reviews.</p><h4>Getting Reviews from Amazon — Minimal Script</h4><p>Fortunately, Amazon.com is a fairly conservative site where reviews are still displayed statically in the HTML code without using JS, so we can easily request the reviews by loading the HTML pages via the requests library.</p><p>Let’s start by writing a minimal script. To prevent Amazon from detecting that we’re using a Python bot, we will create a session to save and pass back all cookies, and also send headers to make the server think we’re using a real browser.</p><pre>import requests<br>from lxml import html<br>import re, time<br><br>#Define ASIN<br>asin = &#39;B01MXORFUD&#39;<br><br>#Create a session object<br>session = requests.Session()<br><br>#Define headers<br>headers = {<br>    &#39;User-Agent&#39;: &#39;Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36&#39;,<br>    &#39;Accept&#39;: &#39;text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8&#39;,<br>    &#39;Accept-Encoding&#39;: &#39;gzip, deflate, br&#39;,<br>    &#39;Accept-Language&#39;: &#39;en-US,en;q=0.9&#39;,<br>    &#39;Connection&#39;: &#39;keep-alive&#39;,<br>    &#39;DNT&#39;: &#39;1&#39;,  # Do Not Track request header<br>    &#39;Upgrade-Insecure-Requests&#39;: &#39;1&#39;,<br>    &#39;Referer&#39;: &#39;https://amazon.com/&#39;,<br>}</pre><p>First, let’s open the product page and wait a couple of seconds. After all, how else would you reach the reviews page? It’s better to emulate a user’s browsing behavior on the site.</p><p>We will use the get function with headers for the request. We use verify=False because we can ignore HTTPS protocol verification to avoid dealing with certificates.</p><pre>#Load asin page<br>url = f&#39;https://www.amazon.com/dp/{asin}&#39;<br>print(f&#39;Load {url}...&#39;)<br>response = session.get(url, headers=headers, verify=False)<br>headers[&#39;Referer&#39;] = url # save url as referrer for next query<br>print(response)<br>time.sleep(2)</pre><p>After each request, save the request URL in the headers as a new referrer so that it looks to the server like we came from the previous page. Then, open the reviews page with the parameters defined earlier:</p><p>Next, we open the reviews page using the parameters we defined earlier.</p><pre>#Load comments page<br>url = f&#39;https://www.amazon.com/product-reviews/{asin}/ref=cm_cr_arp_d_viewopt_srt?ie=UTF8&amp;reviewerType=all_reviews&amp;sortBy=recent&amp;pageNumber=1&#39;<br>print(f&#39;Load {url}...&#39;)<br>response = session.get(url, headers=headers, verify=False)<br>headers[&#39;Referer&#39;] = url # save url as referrer for next query<br>print(response)<br>time.sleep(2)</pre><p>To parse the reviews, I use the lxml library, which supports XPath. XPath is a powerful tool in skilled hands, allowing you to navigate and select elements within XML and HTML documents with very concise queries. I used to rely on regular expressions, but with XPath, the code is more flexible, simpler, and easier to maintain.</p><p>We construct a tree from the HTML content of the page and request all our blocks of reviews (these are all the div elements that have a data-hook attribute set to review).</p><pre>tree = html.fromstring(response.content)<br>reviews = tree.xpath(&#39;//div[@data-hook=&quot;review&quot;]&#39;)</pre><p>Now we iterate over all the reviews and extract the fields we need: name, title, rating, date, and text.</p><pre># Iterating reviews<br>for review in reviews:<br><br>    # Get Author name<br>    name = review.xpath(&#39;.//span[@class=&quot;a-profile-name&quot;]/text()&#39;)<br>    name = name[0] if name else None<br><br>    # Get Title<br>    title = review.xpath(&#39;.//a[@data-hook=&quot;review-title&quot;]/span/text()&#39;)<br>    title = title[0] if title else None<br><br>    # Get Rating<br>    rating_str = review.xpath(&#39;.//i[@data-hook=&quot;review-star-rating&quot;]/span/text()&#39;)<br>    rating_str = rating_str[0] if rating_str else None<br><br>    # Get Date<br>    date_str = review.xpath(&#39;.//span[@data-hook=&quot;review-date&quot;]/text()&#39;)<br>    date_str = date_str[0] if date_str else None<br><br>    # Get Review Text<br>    review_text = review.xpath(&#39;.//span[@data-hook=&quot;review-body&quot;]//span/text()&#39;)<br>    review_text = review_text[0] if review_text else None<br><br>    #Print data<br>    print(f&quot;Name: {name}&quot;)<br>    print(f&quot;Title: {title}&quot;)<br>    print(f&quot;Rating: {rating_str}&quot;)<br>    print(f&quot;Date: {date_str}&quot;)<br>    print(f&quot;Text: {review_text}&quot;)<br>    print(&quot;-&quot; * 40)</pre><p>Let’s run our script:</p><pre>Load https://www.amazon.com/dp/B01MXORFUD...<br>&lt;Response [200]&gt;<br><br>Load https://www.amazon.com/product-reviews/B01MXORFUD/ref=cm_cr_arp_d_viewopt_srt?ie=UTF8&amp;reviewerType=all_reviews&amp;sortBy=recent&amp;pageNumber=1...<br>&lt;Response [200]&gt;<br><br>Name: Maker Guy<br>Title: Great.. if you like lowering your partner very slowly<br>Rating: 1.0 out of 5 stars<br>Date: Reviewed in the United States on August 16, 2024<br>Text: I hate this thing.. I used a Grigri 2 today at the gym. SO much easier to lower people.. I’m shocked this has so many 5 star ratings. When I started climbing I read this was the one for beginners.. It is good for people who panic easily while belaying..<br>----------------------------------------<br>Name: jennifer k walker<br>Title: Top rated product<br>Rating: 5.0 out of 5 stars<br>Date: Reviewed in the United States on August 12, 2024<br>Text: I recommend this for it&#39;s performance, performance varies by rope size. Weight is light, orange is a nice color, quality is known to be great, comfortable to use vs an ATC, for the value....it your life it&#39;s definitely worth the price you pay for it!<br>----------------------------------------<br><br>...<br><br>Name: PizzaPuff<br>Title: Exactly as described<br>Rating: 5.0 out of 5 stars<br>Date: Reviewed in the United States on March 2, 2023<br>Text: Son is very happy with this, Petzl quality<br>----------------------------------------</pre><p>Great! We now have a list of reviews from the Amazon product page.</p><h4>Parsing Date, Country, and Rating</h4><p>In the data we’ve obtained, note that the rating and date strings are textual, so it’s best to convert them into data types we can work with. We’ll parse the rating string to extract the numerical value, and from the date string, we’ll extract the exact date in datetime format.</p><p>You’ll also notice that the date string contains information about the country from which the review was posted, which could be useful for further analysis. We should extract the country as well.</p><pre>Rating: 5.0 out of 5 stars<br>Date: Reviewed in the United States on March 2, 2023</pre><p>Let’s add regular expressions:</p><pre>    ...<br><br>    # Get Country and date<br>    country = None<br>    date = None<br><br>    if date_str:<br>        match = re.search(r&#39;Reviewed in ([A-Za-z\s]+) on ([A-Za-z]+\s\d{1,2},\s\d{4})&#39;, date_str)<br>        if match:<br><br>            # Get country<br>            country = match.group(1).strip()<br><br>            # Get date and convert to datetime<br>            date_strip = match.group(2).strip()<br>            date = datetime.strptime(date_strip, &#39;%B %d, %Y&#39;)<br>    <br>    <br>    # Get rating value from rating str<br>    rating = None<br><br>    if rating_str:<br>        match = re.search(r&#39;(\d+(\.\d+)?)&#39;, rating_str)<br>        if match:<br>            rating = float(match.group(1))<br><br>    #Print data<br>    print(f&quot;Name: {name}&quot;)<br>    print(f&quot;Title: {title}&quot;)<br>    print(f&quot;Rating: {rating}&quot;)<br>    print(f&quot;Country: {country}&quot;)<br>    print(f&quot;Date: {date}&quot;)<br>    print(f&quot;Text: {review_text}&quot;)<br>    print(&quot;-&quot; * 40)</pre><p>Now the output is more structured into separate fields:</p><pre>Name: Maker Guy<br>Title: Great.. if you like lowering your partner very slowly<br>Rating: 1.0<br>Country: United States<br>Date: 2024-08-16 00:00:00<br>Text: I hate this thing.. I used a Grigri 2 today at the gym. SO much easier to lower people.. I’m shocked this has so many 5 star ratings. When I started climbing I read this was the one for beginners.. It is good for people who panic easily while belaying..<br>----------------------------------------<br>Name: jennifer k walker<br>Title: Top rated product<br>Rating: 5.0<br>Country: United States<br>Date: 2024-08-12 00:00:00<br>Text: I recommend this for it&#39;s performance, performance varies by rope size. Weight is light, orange is a nice color, quality is known to be great, comfortable to use vs an ATC, for the value....it your life it&#39;s definitely worth the price you pay for it!<br>----------------------------------------<br><br>...<br><br>Name: PizzaPuff<br>Title: Exactly as described<br>Rating: 5.0<br>Country: United States<br>Date: 2023-03-02 00:00:00<br>Text: Son is very happy with this, Petzl quality<br>----------------------------------------</pre><h4>Iterating through Pages</h4><p>To iterate through pages and get all reviews, you just need to change the pageNumber in the request. To avoid infinite loops, we’ll check if the next page is available in each iteration. I&#39;ll check for the presence of the &quot;Next Page&quot; link by its XPath //ul[@class=&#39;a-pagination&#39;]/li[@class=&#39;a-last&#39;]/a.</p><pre>#Load comments page with paging<br>page = 1<br><br>while True:<br><br>    url = f&#39;https://www.amazon.com/product-reviews/{asin}/ref=cm_cr_arp_d_viewopt_srt?ie=UTF8&amp;reviewerType=all_reviews&amp;sortBy=recent&amp;pageNumber={page}&#39;<br>    print(f&#39;Page {page} | Load {url}...&#39;)<br>    response = session.get(url, headers=headers, verify=False)<br>    headers[&#39;Referer&#39;] = url  # save url as referrer for next query<br>    print(response)<br>    time.sleep(2)<br><br>    tree = html.fromstring(response.content)<br>    reviews = tree.xpath(&#39;//div[@data-hook=&quot;review&quot;]&#39;)<br><br>    # Iterating reviews<br>    for review in reviews:<br>      ...<br><br>    next_page = tree.xpath(&quot;//ul[@class=&#39;a-pagination&#39;]/li[@class=&#39;a-last&#39;]/a[@href]&quot;)<br>    print(&#39;next_page=&#39;, next_page)<br>    if next_page:<br>        print(&#39;Next page found - go to the next page!&#39;)<br>        page += 1<br>    else:<br>        print(&#39;Next page not found! - Exit from the loop!&#39;)<br>        break</pre><h4>Authentication via Cookies</h4><p>As mentioned earlier, to iterate through reviews starting from the second page, authentication is required. Implementing authentication (entering login and password) is possible but comes with challenges, as CAPTCHAs often appear. A very simple and elegant solution is to pass authentication cookies to the script, which can be taken from a logged-in browser session.</p><p>To view the cookies, open a browser with your Amazon.com logged-in account, open the Chrome Inspector and go to the “Application” tab. Then, in the left menu, select Storage &gt; Cookies &gt; https://www.amazon.com to view all cookies that Amazon has saved in your browser.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BjfFyy7jlak4BBqgu1MUOQ.png" /><figcaption>Chrome inspector with cookies from amazon.com</figcaption></figure><p>Through trial and error, I found that the cookies involved in authentication are session-id, ubid-main, and x-main. Copy their values. As long as your account is authenticated in the browser, your Python script will work.</p><p>To set the cookies in the script, simply insert them into the session after declaring the headers; they will then be sent automatically with each page request via session.get.</p><pre>session = requests.Session() # Create session<br><br>headers = {<br>    &#39;User-Agent&#39;: &#39;Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36&#39;,<br>    &#39;Accept&#39;: &#39;text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8&#39;,<br>    &#39;Accept-Encoding&#39;: &#39;gzip, deflate, br&#39;,<br>    &#39;Accept-Language&#39;: &#39;en-US,en;q=0.9&#39;,<br>    &#39;Connection&#39;: &#39;keep-alive&#39;,<br>    &#39;DNT&#39;: &#39;1&#39;,  # Do Not Track request header<br>    &#39;Upgrade-Insecure-Requests&#39;: &#39;1&#39;,<br>    &#39;Referer&#39;: &#39;https://amazon.com/&#39;,<br>}<br><br>#Add auth cookies<br>session.cookies.set(&#39;session-id&#39;, &#39;[INSERT YOUR VALUE HERE]&#39;, domain=&#39;.amazon.com&#39;)<br>session.cookies.set(&#39;ubid-main&#39;, &#39;[INSERT YOUR VALUE HERE]&#39;, domain=&#39;.amazon.com&#39;)<br>session.cookies.set(&#39;x-main&#39;, &#39;[INSERT YOUR VALUE HERE]&#39;, domain=&#39;.amazon.com&#39;)</pre><p>After testing page transitions, I found that the Title and Rating tags differ slightly if the review comes from another country, so I expanded the parsing. I also added collection of each review’s identifier, which exists in the HTML code and can be useful for some scenarios (such as tracking which reviews have been added).</p><pre><br>        ...<br><br>        #Get review id<br>        review_id = review.xpath(&#39;./@id&#39;)<br>        review_id = review_id[0] if review_id else None<br><br>        ...<br><br>        # Get Title<br>        title = review.xpath(&#39;.//a[@data-hook=&quot;review-title&quot;]/span/text()&#39;)<br>        title = title[0] if title else None<br><br>        # Try get Title by alternative way<br>        if not title:<br>            title = review.xpath(&#39;.//span[@data-hook=&quot;review-title&quot;]/span[@class=&quot;cr-original-review-content&quot;]/text()&#39;)<br>            title = title[0] if title else None<br><br>        ...<br><br>        # Get Rating<br>        rating_str = review.xpath(&#39;.//i[@data-hook=&quot;review-star-rating&quot;]/span/text()&#39;)<br>        rating_str = rating_str[0] if rating_str else None<br><br>        # Try Rating by alternative way<br>        if not rating_str:<br>            rating_str = review.xpath(&#39;.//i[@data-hook=&quot;cmps-review-star-rating&quot;]/span[@class=&quot;a-icon-alt&quot;]/text()&#39;)<br>            rating_str = rating_str[0] if rating_str else None<br><br>        ...</pre><h4>Saving Data to a DataFrame</h4><p>To save data to a DataFrame, during the processing of each review, besides outputting, we will save the review data into a list of dicts with the fields we need, and at the end of the script, we’ll insert the data into a DataFrame.</p><p>The DataFrame can be saved to CSV and used in various ways, such as sending it to a spreadsheet or a database.</p><pre>comments_data = []<br><br>....<br>page = 1<br><br>while True:<br><br>        ...<br><br>        # Iterating reviews<br>        for review in reviews:<br><br>            ....<br><br>            comments_data.append(dict(<br>                asin = asin,<br>                review_id = review_id,<br>                name = name,<br>                title = title,<br>                rating = rating,<br>                country = country,<br>                date = date,<br>                review_text = review_text<br>            ))<br><br>            ....<br><br>frame = pd.DataFrame(comments_data)<br>print(frame)<br><br>frame.to_csv(&#39;data/amazon_reviews.csv&#39;, index=False)</pre><h4>Final Script</h4><p>Let’s put everything together into a final script. I’ve also added support for a list of ASINs, so the script can collect reviews for multiple products instead of just one.</p><pre>import pandas as pd<br>import requests<br>from lxml import html<br>import re, time<br>from datetime import datetime<br><br>#Define list of asins<br>asins = [&#39;B01MXORFUD&#39;, &#39;B015XIXMKS&#39;, &#39;B09M82M78M&#39;]<br><br>#Create session<br>session = requests.Session()<br><br>#Define headers<br>headers = {<br>    &#39;User-Agent&#39;: &#39;Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36&#39;,<br>    &#39;Accept&#39;: &#39;text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8&#39;,<br>    &#39;Accept-Encoding&#39;: &#39;gzip, deflate, br&#39;,<br>    &#39;Accept-Language&#39;: &#39;en-US,en;q=0.9&#39;,<br>    &#39;Connection&#39;: &#39;keep-alive&#39;,<br>    &#39;DNT&#39;: &#39;1&#39;,  # Do Not Track request header<br>    &#39;Upgrade-Insecure-Requests&#39;: &#39;1&#39;,<br>    &#39;Referer&#39;: &#39;https://amazon.com/&#39;,<br>}<br><br>#Set auth cookies<br>session.cookies.set(&#39;session-id&#39;, &#39;[INSERT YOUR VALUE HERE]&#39;, domain=&#39;.amazon.com&#39;)<br>session.cookies.set(&#39;ubid-main&#39;, &#39;[INSERT YOUR VALUE HERE]&#39;, domain=&#39;.amazon.com&#39;)<br>session.cookies.set(&#39;x-main&#39;, &#39;[INSERT YOUR VALUE HERE]&#39;, domain=&#39;.amazon.com&#39;)<br><br><br>comments_data = [] #Comments data for result dataframe<br><br>#Iterate asins<br>for asin in asins:<br><br>    print(f&#39;/// ASIN {asin}&#39;)<br><br>    #Load asin page<br>    url = f&#39;https://www.amazon.com/dp/{asin}&#39;<br>    print(f&#39;Load {url}...&#39;)<br>    response = session.get(url, headers=headers, verify=False)<br>    headers[&#39;Referer&#39;] = url #save url as referrer for next query<br>    print(response)<br>    time.sleep(2)<br><br>    #Load comments page with paging<br>    page = 1<br><br>    while True:<br><br>        url = f&#39;https://www.amazon.com/product-reviews/{asin}/ref=cm_cr_arp_d_viewopt_srt?ie=UTF8&amp;reviewerType=all_reviews&amp;sortBy=recent&amp;pageNumber={page}&#39;<br>        print(f&#39;Page {page} | Load {url}...&#39;)<br>        response = session.get(url, headers=headers, verify=False)<br>        headers[&#39;Referer&#39;] = url  # save url as referrer for next query<br>        print(response)<br>        time.sleep(2)<br><br>        #Get page tree and get reviews<br>        tree = html.fromstring(response.content)<br>        reviews = tree.xpath(&#39;//div[@data-hook=&quot;review&quot;]&#39;)<br><br>        print(f&#39;Reviews found on the page: {len(reviews)}&#39;)<br>        if(len(reviews)==0):<br>            print(response.content)<br><br>        # Iterating reviews<br>        for review in reviews:<br><br>            #Get review id<br>            review_id = review.xpath(&#39;./@id&#39;)<br>            review_id = review_id[0] if review_id else None<br><br>            # Get Author name<br>            name = review.xpath(&#39;.//span[@class=&quot;a-profile-name&quot;]/text()&#39;)<br>            name = name[0] if name else None<br><br>            # Get Title<br>            title = review.xpath(&#39;.//a[@data-hook=&quot;review-title&quot;]/span/text()&#39;)<br>            title = title[0] if title else None<br><br>            # Try get Title by alternative way<br>            if not title:<br>                title = review.xpath(&#39;.//span[@data-hook=&quot;review-title&quot;]/span[@class=&quot;cr-original-review-content&quot;]/text()&#39;)<br>                title = title[0] if title else None<br><br>            # Get Rating<br>            rating_str = review.xpath(&#39;.//i[@data-hook=&quot;review-star-rating&quot;]/span/text()&#39;)<br>            rating_str = rating_str[0] if rating_str else None<br><br>            # Try Rating by alternative way<br>            if not rating_str:<br>                rating_str = review.xpath(&#39;.//i[@data-hook=&quot;cmps-review-star-rating&quot;]/span[@class=&quot;a-icon-alt&quot;]/text()&#39;)<br>                rating_str = rating_str[0] if rating_str else None<br><br>            # Get Author name<br>            date_str = review.xpath(&#39;.//span[@data-hook=&quot;review-date&quot;]/text()&#39;)<br>            date_str = date_str[0] if date_str else None<br><br>            # Get Review Text<br>            review_text = review.xpath(&#39;.//span[@data-hook=&quot;review-body&quot;]//span/text()&#39;)<br>            review_text = review_text[0] if review_text else None<br><br>            # Get Country and comment date from<br>            country = None<br>            date = None<br><br>            if date_str:<br>                match = re.search(r&#39;Reviewed in ([A-Za-z\s]+) on ([A-Za-z]+\s\d{1,2},\s\d{4})&#39;, date_str)<br>                if match:<br><br>                    # Get country<br>                    country = match.group(1).strip()<br><br>                    # Get date and convert to datetime<br>                    date_strip = match.group(2).strip()<br>                    date = datetime.strptime(date_strip, &#39;%B %d, %Y&#39;)<br><br><br>            # Get rating value from rating str<br>            rating = None<br><br>            if rating_str:<br>                match = re.search(r&#39;(\d+(\.\d+)?)&#39;, rating_str)<br>                if match:<br>                    rating = float(match.group(1))<br><br>            #Print data<br>            print(f&quot;Id: {review_id}&quot;)<br>            print(f&quot;Name: {name}&quot;)<br>            print(f&quot;Title: {title}&quot;)<br>            print(f&quot;Rating: {rating}&quot;)<br>            print(f&quot;Country: {country}&quot;)<br>            print(f&quot;Date: {date}&quot;)<br>            print(f&quot;Text: {review_text}&quot;)<br>            print(&quot;-&quot; * 40)<br><br>            comments_data.append(dict(<br>                asin = asin,<br>                review_id = review_id,<br>                name = name,<br>                title = title,<br>                rating = rating,<br>                country = country,<br>                date = date,<br>                review_text = review_text<br>            ))<br><br>        next_page = tree.xpath(&quot;//ul[@class=&#39;a-pagination&#39;]/li[@class=&#39;a-last&#39;]/a[@href]&quot;)<br>        print(&#39;next_page=&#39;, next_page)<br>        if next_page:<br>            print(&#39;Next page found - go to the next page!&#39;)<br>            page += 1<br>        else:<br>            print(&#39;Next page not found! - Exit from the loop!&#39;)<br>            break<br><br>#Create result dataframe<br>frame = pd.DataFrame(comments_data)<br>print(frame)<br><br>#Save dataframe<br>frame.to_csv(&#39;data/amazon_reviews.csv&#39;, index=False)</pre><p>The resulting CSV file with reviews can be used for analysis or opened in Excel.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MjMm0nmRdHZrKWo09XBxFg.png" /><figcaption>Result CSV opened in Excel</figcaption></figure><h4>Tips for Large-Scale Scraping</h4><p>If you need to scrape a lot of products frequently, you may face significant challenges. Websites usually don’t like being scraped en masse. After a while, you might get a 500 error page or a CAPTCHA.</p><p>Don’t make too many requests too often. Load pages one after the other and take random pauses after each page request using time.sleep. Emulate a slow person browsing page after page.</p><p>If you encounter blocks, the simplest solution is to use proxies. Use good paid proxies and change proxies from time to time. When changing proxies, remember to reset the session and all cookies.</p><p>Some sites use JS for data display or bot detection; in this case, you need to emulate the browser and scrape through Selenium. It’s more complex, but it provides full browser emulation. In this case, there are almost no limits to the data you can scrape.</p><p><strong>Author:</strong> The author of this article is the founder and CTO at <a href="https://sellerflux.com/">sellerflux.com</a>. We empower e-commerce with data-driven solutions. Our services include <a href="https://sellerflux.com/services/data_extraction/">Data Extraction &amp; Integration</a>, and <a href="https://sellerflux.com/services/tracking_notifications/">Tracking and Notifications</a>.</p><h4>Useful Links</h4><ol><li>XPATH expression language: <a href="https://en.wikipedia.org/wiki/XPath">https://en.wikipedia.org/wiki/XPath</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4593fdbe00da" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Amazon SP-API: Get Order Items with Python]]></title>
            <link>https://andrewkushnerov.medium.com/amazon-sp-api-get-order-items-with-python-68506bbde58a?source=rss-27f59dbe18f4------2</link>
            <guid isPermaLink="false">https://medium.com/p/68506bbde58a</guid>
            <category><![CDATA[amazon-sp-api]]></category>
            <category><![CDATA[order-processing]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Andrei Kushniarou]]></dc:creator>
            <pubDate>Thu, 05 Sep 2024 21:26:19 GMT</pubDate>
            <atom:updated>2024-09-05T21:26:19.966Z</atom:updated>
            <content:encoded><![CDATA[<p>Getting Orders and Order Items from Amazon SP-API is fundamental for building high-quality sales analytics. It’s not only important to create a script that fetches data, but also to build a robust architecture and flow that works continuously, reliably fetching and updating data while preparing structured data for quality analytics.</p><p>In my previous article, I detailed how to <a href="https://medium.com/@andrewkushnerov/amazon-sp-api-get-orders-with-python-7b7e913d87ea">build a solid flow for retrieving Orders from SP API</a>. In this article, I will explain how to extend the process to include fetching Order Items from SP-API. We will save the raw data and create a script to transform raw data into structured data, and finally, we’ll perform basic data analytics.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*UtQwSTSWsUi-9C-3" /><figcaption>Image from DALL-E for this article</figcaption></figure><p>You might wonder why Items are not retrieved along with Orders. Unfortunately, this is due to how the Amazon SP-API operates. You can fetch Orders in bulk with a single request, and the API method returns up to 100 records at a time, but the results do not include the items purchased in the orders. To get Order Items, you need to make a separate request for each order.</p><h4><strong>Table of Contents:</strong></h4><ol><li>Overview of Amazon SP-API Function for Retrieving Order Items</li><li>Fetching Order Items — A Minimal Example</li><li>Overview and Data Structure</li><li>Should You Fetch Order Items Once or Continuously Update as Order Status Changes?</li><li>Data Storage Structure for Order Items</li><li>Full Working Script for Fetching Order Items</li><li>Processing Raw Data into Structured Data (DataFrame) and Combining Orders with Order Items for Comprehensive Order Data</li><li>Performing Basic Analytics</li></ol><h4><strong>Overview of the Amazon SP API Function for Retrieving Order Items</strong></h4><p>To retrieve order items from the Amazon SP-API, we need to use the /orders/v0/orders/{orderId}/orderItems method from the Orders API v0 Reference [<a href="https://developer-docs.amazon.com/sp-api/docs/orders-api-v0-reference#get-ordersv0ordersorderid">2</a>]. All we need is the orderIdto get the Items data.</p><h4>Fetching Order Items — A Minimal Example</h4><p>Let’s start with a minimal example and fetch the data. Import the necessary libraries and define the Amazon SP-API endpoint (for US)</p><pre>import sys<br>import requests, json<br>from sp_api_functions import get_lwa_access_token<br><br>ENDPOINT = &quot;https://sellingpartnerapi-na.amazon.com&quot;</pre><p>Fetch the access token and set the orderId for testing. You can find the access token retrieval function in my article on fetching orders [<a href="https://medium.com/@andrewkushnerov/amazon-sp-api-get-orders-with-python-7b7e913d87ea">link</a>]. It should be located in the file sp_api_functions.py. The function requests an access token and caches it for 50 minutes (the access token is valid for one hour).</p><pre>#Get Access token<br>access_token = get_lwa_access_token()<br><br>orderId = &#39;111-222222-333333&#39;</pre><p>Make a request and display the result:</p><pre>#Make a request to Amazon SP-API<br>response = requests.get(<br>    ENDPOINT<br>    + f&quot;/orders/v0/orders/{orderId}/orderItems&quot;,<br>    headers={<br>        &quot;x-amz-access-token&quot;: access_token<br>    }<br>)<br>print(response)<br>print(f&#39;Response code={response.status_code}&#39;)<br><br>#Process result<br>if(response.status_code!=200):<br><br>    print(f&#39;-- Wrong response code -- exit!&#39;)<br>    data = json.loads(response.content)<br>    print(data)<br>    sys.exit()<br><br>else:<br><br>    print(response.headers)<br>    data = json.loads(response.content)<br>    print(data)<br></pre><p>If everything is OK (the order exists for this marketplace and belongs to the seller), you will get a response with a status of 200.</p><pre>Load access_token from file...<br>&lt;Response [200]&gt;<br>Response code=200<br>{&#39;Server&#39;: &#39;Server&#39;, &#39;Date&#39;: &#39;Thu, 29 Aug 2024...<br>{&#39;payload&#39;: {&#39;OrderItems&#39;: [{&#39;TaxCollection&#39;: {&#39;Model&#39;: &#39;MarketplaceFacilitator&#39;, ...</pre><p>If the order does not exist, you will receive an error with a status of 400.</p><pre>Load access_token from file...<br>&lt;Response [400]&gt;<br>Response code=400<br>-- Wrong response code -- exit!<br>{&#39;errors&#39;: [{&#39;code&#39;: &#39;InvalidInput&#39;, &#39;message&#39;: &#39;The input you have submitted is not valid. Please check your input and try again.&#39;, &#39;details&#39;: &#39;&#39;}]}</pre><h4>Overview and Data Structure</h4><p>Let’s print the JSON with indents for better readability:</p><pre>print(json.dumps(data, indent=4)) #show json with indent</pre><p>And look to the result:</p><pre>{<br>    &quot;payload&quot;: {<br>        &quot;OrderItems&quot;: [<br>            {<br>                &quot;TaxCollection&quot;: {<br>                    &quot;Model&quot;: &quot;MarketplaceFacilitator&quot;,<br>                    &quot;ResponsibleParty&quot;: &quot;Amazon Services, Inc.&quot;<br>                },<br>                &quot;ProductInfo&quot;: {<br>                    &quot;NumberOfItems&quot;: &quot;1&quot;<br>                },<br>                &quot;BuyerInfo&quot;: {},<br>                &quot;ItemTax&quot;: {<br>                    &quot;CurrencyCode&quot;: &quot;USD&quot;,<br>                    &quot;Amount&quot;: &quot;0.00&quot;<br>                },<br>                &quot;QuantityShipped&quot;: 1,<br>                &quot;ItemPrice&quot;: {<br>                    &quot;CurrencyCode&quot;: &quot;USD&quot;,<br>                    &quot;Amount&quot;: &quot;26.39&quot;<br>                },<br>                &quot;ASIN&quot;: &quot;XXYYYZZ1&quot;,<br>                &quot;SellerSKU&quot;: &quot;XXX-YYY-ZZZ-1&quot;,<br>                &quot;Title&quot;: &quot;Item Title 1...&quot;,<br>                &quot;IsGift&quot;: &quot;false&quot;,<br>                &quot;IsTransparency&quot;: false,<br>                &quot;QuantityOrdered&quot;: 1,<br>                &quot;PromotionDiscountTax&quot;: {<br>                    &quot;CurrencyCode&quot;: &quot;USD&quot;,<br>                    &quot;Amount&quot;: &quot;0.00&quot;<br>                },<br>                &quot;PromotionDiscount&quot;: {<br>                    &quot;CurrencyCode&quot;: &quot;USD&quot;,<br>                    &quot;Amount&quot;: &quot;0.00&quot;<br>                },<br>                &quot;OrderItemId&quot;: &quot;....&quot;<br>            },<br>            {<br>                &quot;TaxCollection&quot;: {<br>                    &quot;Model&quot;: &quot;MarketplaceFacilitator&quot;,<br>                    &quot;ResponsibleParty&quot;: &quot;Amazon Services, Inc.&quot;<br>                },<br>                &quot;ProductInfo&quot;: {<br>                    &quot;NumberOfItems&quot;: &quot;1&quot;<br>                },<br>                &quot;BuyerInfo&quot;: {},<br>                &quot;ItemTax&quot;: {<br>                    &quot;CurrencyCode&quot;: &quot;USD&quot;,<br>                    &quot;Amount&quot;: &quot;0.00&quot;<br>                },<br>                &quot;QuantityShipped&quot;: 2,<br>                &quot;ItemPrice&quot;: {<br>                    &quot;CurrencyCode&quot;: &quot;USD&quot;,<br>                    &quot;Amount&quot;: &quot;46.18&quot;<br>                },<br>                &quot;ASIN&quot;: &quot;XXYYYZZ1&quot;,<br>                &quot;SellerSKU&quot;: &quot;XXX-YYY-ZZZ-2&quot;,<br>                &quot;Title&quot;: &quot;Item Title 2...&quot;,<br>                &quot;IsGift&quot;: &quot;false&quot;,<br>                &quot;IsTransparency&quot;: false,<br>                &quot;QuantityOrdered&quot;: 2,<br>                &quot;PromotionDiscountTax&quot;: {<br>                    &quot;CurrencyCode&quot;: &quot;USD&quot;,<br>                    &quot;Amount&quot;: &quot;0.00&quot;<br>                },<br>                &quot;PromotionDiscount&quot;: {<br>                    &quot;CurrencyCode&quot;: &quot;USD&quot;,<br>                    &quot;Amount&quot;: &quot;0.00&quot;<br>                },<br>                &quot;OrderItemId&quot;: &quot;...&quot;<br>            }<br>        ],<br>        &quot;AmazonOrderId&quot;: &quot;111-2222222-3333333&quot;<br>    }<br>}</pre><p>The items are found in data[&#39;payload&#39;][&#39;OrderItems&#39;]. Besides the item ASIN/SKU, item price, and quantity, there are several other important fields. Only in the items data, you will find ItemTax and PromotionDiscount, which are important for overall statistics.</p><p>It’s also important to note that QuantityOrdered and QuantityShipped are two different fields, and if multiple items are ordered, ItemPrice is the total for all items, meaning ItemPricePerItem = ItemPrice / QuantityOrdered.</p><h4>Should You Fetch Order Items Once or Continuously Update When Order Status Changes?</h4><p>This is a good question. I prefer updating items data every time the order is updated. This provides more accurate statistics, as some items might not be shipped and may be removed from the order. If the order status changes to canceled, the item data still reflects what was in the order, which can be used, for example, to build statistics on canceled orders.</p><h4>Data Storage Structure for Order Items</h4><p>In my article on retrieving Orders [<a href="https://medium.com/@andrewkushnerov/amazon-sp-api-get-orders-with-python-7b7e913d87ea">1</a>], I detailed how I prefer to store raw data and why. We’ll store items similarly to Orders, in a separate file in the same directories.</p><p>All items for orders made on a single day will be stored in a separate file with the date. To prevent too many files in one directory, files will be divided into subdirectories consisting of the year and month.</p><p>For example, all Order Items made on August 24, 2024, will be stored in the file /data/orders/2024-08/order_items_2024-08-24.json. Since I will need to update items by orderId, each file will store data as a JSON dictionary with the order number as the key {orderId1: raw data of order items1, orderId2: raw data of order items2}. When I need to add or update Order Items, I&#39;ll read the JSON, convert it to a Python dict, check for the orderId, and update the data if the orderId exists, or add a new value with the orderId key if not.</p><p>This storage model is not the most optimal, but it is fast and convenient enough for a working example, even if you have several thousand orders per day.</p><h4>Full Working Script for Fetching Order Items</h4><p>Let’s implement a full working script that will load all Order Items data from Amazon SP API and save it.</p><p>Keep in mind that we only request Order Items for new Orders or for those Orders that have been updated. In the script for retrieving Orders [<a href="https://medium.com/@andrewkushnerov/amazon-sp-api-get-orders-with-python-7b7e913d87ea">1</a>], I specifically saved all identifiers and order dates in a structure called processedOrders. So, we need to request item updates for all Orders present in processedOrders.</p><p>Import the libraries and define the endpoint:</p><pre>import sys, requests, json, time, os<br>from sp_api_functions import get_lwa_access_token<br>import pickle<br>from datetime import datetime, timedelta<br><br>#Define endpoint and marketplace ID<br>ENDPOINT = &quot;https://sellingpartnerapi-na.amazon.com&quot;</pre><p>Print how many orders we have in the queue for loading items:</p><pre>print(&#39;processedOrders length=&#39;, len(processedOrders))</pre><p>Define the ORDER_ITEMS structure for storing loaded orders items and immediately split them by year_month and day by order date, so they can be easily saved or updated in the corresponding file.</p><pre>#Dict to save all loaded Order Items<br>ORDER_ITEMS = {}<br>i = 0<br><br>for orderId, orderDate in processedOrders.items():<br><br>    print(f&#39;/// Load AmazonOrderId {orderId} Items | {i} from {len(processedOrders)}&#39;)<br><br>    #Get Access token (get from cache, or load from SP API)<br>    access_token = get_lwa_access_token()<br><br>    while True:<br><br>        #Make a request to Amazon SP-API to get order Items<br>        response = requests.get(<br>            ENDPOINT<br>            + f&quot;/orders/v0/orders/{orderId}/orderItems&quot;,<br>            headers={<br>                &quot;x-amz-access-token&quot;: access_token<br>            }<br>        )<br>        print(f&#39;Response code={response.status_code}&#39;)<br><br>        if(response.status_code==429):<br><br>            print(f&#39;-- QuotaExceed - wait!&#39;)<br>            time.sleep(10)<br>            continue<br><br>        elif(response.status_code==400):<br><br>            print(f&#39;-- Amazon Order not found!&#39;)<br>            data = json.loads(response.content)<br>            print(data)<br>            time.sleep(2)<br>            continue<br><br>        elif(response.status_code!=200):<br><br>            print(f&#39;-- Wrong response code - exit!&#39;)<br>            data = json.loads(response.content)<br>            print(data)<br>            time.sleep(10)<br>            sys.exit()<br><br>        else:<br><br>            data = json.loads(response.content)<br>            amazonOrderId = data[&#39;payload&#39;][&#39;AmazonOrderId&#39;]<br>            orderItems = data[&#39;payload&#39;][&#39;OrderItems&#39;]<br><br>            year_month = orderDate.strftime(&quot;%Y-%m&quot;)<br>            day = orderDate.strftime(&quot;%d&quot;)<br><br>            if year_month not in ORDER_ITEMS:<br>                ORDER_ITEMS[year_month] = {}<br>            if day not in ORDER_ITEMS[year_month]:<br>                ORDER_ITEMS[year_month][day] = {}<br><br>            print(f&#39;-- {amazonOrderId} items={len(orderItems)} loaded&#39;)<br><br>            ORDER_ITEMS[year_month][day][amazonOrderId] = orderItems<br><br>            time.sleep(1.5)<br><br>        i += 1<br>        break #exit from While True loop</pre><p>Now let’s process the ORDER_ITEMS structure in a loop. Since the data is initially split by year_month and day by order date, in each iteration, we need to find the corresponding file where order items are stored (if it doesn’t exist, we’ll create it) and add the items data, or update it using the Amazon order identifier amazonOrderId as the key.</p><pre>print(&#39;Process Order Items...&#39;)<br>for year_month, month_data in ORDER_ITEMS.items():<br>    for day, day_data in month_data.items():<br><br>        print(f&#39;- DAY {year_month}-{day} | Order Items to save/update {len(day_data)}&#39;)<br><br>        #Check and create year_month dir (if not exist)<br>        year_month_dir = os.path.join(orders_dir, year_month)<br>        if not os.path.isdir(year_month_dir): os.mkdir(year_month_dir)<br><br>        day_order_items_path = os.path.join(year_month_dir, f&#39;order_items_{year_month}-{day}.json&#39;)<br>        if os.path.isfile(day_order_items_path):<br>            with open(day_order_items_path, &quot;r&quot;) as f:<br>                DAY_ORDER_ITEMS = json.load(f)<br>                print(f&#39;--Loaded DAY_ORDER_ITEMS from file={len(DAY_ORDER_ITEMS)}&#39;)<br>        else:<br>            DAY_ORDER_ITEMS = {}<br>            print(f&#39;--File not found, set DAY_ORDER_ITEMS as empty&#39;)<br><br>        #Process all orders<br>        added_qty, updated_qty = 0,0<br>        for amazonOrderId, item in day_data.items():<br>            if amazonOrderId not in DAY_ORDER_ITEMS:<br>                added_qty +=1<br>            else:<br>                updated_qty +=1<br><br>            #Save or update order<br>            DAY_ORDER_ITEMS[amazonOrderId] = item<br><br>        #Save/update file with DAY_ORDER_ITEMS<br>        with open(day_order_items_path, &quot;w&quot;) as f:<br>            json.dump(DAY_ORDER_ITEMS, f, ensure_ascii=False, indent=2)<br>            print(f&#39;--{day_order_items_path} day order items saved, added={added_qty} updated_qty={updated_qty} overall_records={len(DAY_ORDER_ITEMS)}&#39;)</pre><p>Run the script:</p><pre>processedOrders loaded= 133<br>/// Load AmazonOrderId 114-7998018-... Items | 0 from 133<br>Response code=200<br>-- 114-7998018-... items=2 loaded<br>/// Load AmazonOrderId 112-5780815-... Items | 1 from 133<br>Response code=200<br>-- 112-5780815-... items=1 loaded<br><br>...<br><br>/// Load AmazonOrderId 114-8320087-... Items | 133 from 133<br>-- 114-8320087-... items=3 loaded<br>Response code=200<br><br>Process Order Items...<br>- DAY 2024-08-16 | Order Items to save/update 14<br>--Loaded DAY_ORDER_ITEMS from file=12<br>--.../data/orders/2024-08/order_items_2024-08-16.json day order items saved, added=2 updated_qty=12 overall_records=14<br>...<br>- DAY 2024-08-28 | Order Items to save/update 38<br>--File not found, set DAY_ORDER_ITEMS as empty<br>--.../data/orders/2024-08/order_items_2024-08-28.json day order items saved, added=38 updated_qty=0 overall_records=38</pre><p>We see how Orders are first sequentially loaded using the SP API, and then Order Items are saved and updated in daily files.</p><p><strong>Note 1:</strong> Sometimes my SP API was unstable, and SSL errors would occur. If this happens, wrap the request in an additional while True block with a try... except for request retries.</p><pre>while True:<br><br>    try:<br>        #Make a request to Amazon SP-API to get order Items<br>        response = requests.get(<br>            ENDPOINT<br>            + f&quot;/orders/v0/orders/{orderId}/orderItems&quot;<br>            headers={<br>                &quot;x-amz-access-token&quot;: access_token<br>            }<br>        )<br>        print(f&#39;Response code={response.status_code}&#39;)<br>        break<br><br>    except requests.exceptions.SSLError as ex:<br><br>        print(&#39;SSL ERROR Exception - wait 20 s&#39;)<br>        time.sleep(20)</pre><p><strong>Note 2:</strong> Order Items load much slower than orders because with orders, you get up to 100 records at once, while for items, each record needs to be loaded individually. If you have many orders (more than 1,000 per day) or need to load historical data for the first time, it’s better to load orders and then order items in short intervals, such as by weeks or even by individual days.</p><h4><strong>Processing Raw Data into Structured Data (DataFrame) and Combining Orders with Order Items for Complete Order Data:</strong></h4><p>After creating the script for retrieving raw Order Items data (and Orders earlier), let’s build a script to compile structured data from raw unstructured data.</p><p><strong>The difference between raw unstructured data and processed structured data:</strong></p><ul><li>Raw unstructured data: Obtained from the API as is and saved as is. Fields can change over time, be present at first, and then be absent later, and so on.</li><li>Processed structured data: Prepared for further processing and building reports, BI analytics, and so on. A fixed list of required fields, no empty and unknown data.</li></ul><p>Processed structured data is ready to be used for building reports, sending to a database, saving as a CSV file, uploading to Excel, Google Spreadsheet, or using with Pandas for statistical calculations.</p><p>When processing raw data from Order Items, we also need Orders data. Since our data is well-prepared (all in separate files split by months and days), it will be easy to scan the required range and assemble a DataFrame.</p><p>Import libraries and define days_range - for how many days we&#39;ll process the data. For the example, I&#39;ll use 14 days.</p><pre>import os, json<br>from datetime import datetime, timedelta, timezone<br>import pandas as pd<br><br>days_range = 14</pre><p>Since Orders and Order Items are stored in separate files by day, we need to get a list of days to process each day:</p><pre>start_day = datetime.now().astimezone(timezone.utc) - timedelta(days=days_range)<br>end_day = datetime.now().astimezone(timezone.utc)<br><br>#Get list of days<br>days = []<br>start_day = start_day.replace(hour=0, minute=0, second=0, microsecond=0)<br>while start_day &lt;= end_day:<br>    days.append(start_day)<br>    start_day += timedelta(days=1)</pre><p>Next, define the main loop, where for each day, we’ll load the file with Orders and Order Items (if they exist), iterate through all orders, and for each order, find all items by the Amazon order ID key. All data will be stored in the dataframe_data list.</p><pre>dataframe_data = []<br><br>#Process days<br>for day in days:<br><br>    print(f&#39;// DAY {day.strftime(&quot;%Y-%m-%d&quot;)}&#39;)<br><br>    year_month = day.strftime(&quot;%Y-%m&quot;)<br>    day = day.strftime(&quot;%d&quot;)<br><br>    day_orders_path = os.path.join(&#39;data&#39;, &#39;orders&#39;, year_month, f&#39;orders_{year_month}-{day}.json&#39;)<br>    day_items_path = os.path.join(&#39;data&#39;, &#39;orders&#39;, year_month, f&#39;order_items_{year_month}-{day}.json&#39;)<br>    if os.path.isfile(day_orders_path) and os.path.isfile(day_items_path):<br><br>        with open(day_orders_path, &quot;r&quot;) as f:<br>            day_orders_data = json.load(f)<br>        with open(day_items_path, &quot;r&quot;) as f:<br>            day_items_data = json.load(f)<br><br>        print(f&#39;--Loaded orders &amp; items | orders={len(day_orders_data)} | items={len(day_items_data)}&#39;)<br><br>        for amazonOrderId,order_data in day_orders_data.items():<br><br>            amazonOrderId = order_data[&quot;AmazonOrderId&quot;]<br>            orderStatus = order_data[&#39;OrderStatus&#39;]<br>            purchaseDate = order_data[&#39;PurchaseDate&#39;]<br><br>            if amazonOrderId in day_items_data:<br>                order_items = day_items_data[amazonOrderId]<br><br>                for item in order_items:<br>                    #print(item)<br><br>                    asin =              item[&#39;ASIN&#39;]<br>                    sellerSku =         item[&#39;SellerSKU&#39;]<br>                    title =             item[&#39;Title&#39;]<br>                    itemQuantity =      item[&#39;QuantityOrdered&#39;]<br>                    itemPrice =         item.get(&#39;ItemPrice&#39;, {}).get(&#39;Amount&#39;, &#39;&#39;)<br>                    itemCurrencyCode =  item.get(&#39;ItemPrice&#39;, {}).get(&#39;CurrencyCode&#39;, &#39;&#39;)<br>                    itemShippingPrice = item.get(&#39;ShippingPrice&#39;, {}).get(&#39;Amount&#39;, &#39;&#39;)<br>                    itemTax =           item.get(&#39;ItemTax&#39;, {}).get(&#39;Amount&#39;, &#39;&#39;)<br>                    promotionDiscount = item.get(&#39;PromotionDiscount&#39;, {}).get(&#39;Amount&#39;, &#39;&#39;)<br><br>                    dataframe_data.append(dict(<br>                        amazonOrderId =             amazonOrderId,<br>                        date =                      purchaseDate,<br>                        orderStatus =               orderStatus,<br>                        asin =                      asin,<br>                        sellerSku =                 sellerSku,<br>                        title =                     title,<br>                        itemQuantity =              itemQuantity,<br>                        itemPrice =                 itemPrice,<br>                        itemCurrencyCode =          itemCurrencyCode,<br>                        itemShippingPrice =         itemShippingPrice,<br>                        itemTax =                   itemTax,<br>                        promotionDiscountItems =    promotionDiscount,<br>                    ))</pre><p>Now we can create a DataFrame and save the resulting DataFrame with order and item data to a CSV file.</p><pre>print(&#39;/// Creating frame...&#39;)<br>frame = pd.DataFrame(dataframe_data)<br><br>#for 4 columns convert to numeric and replace NA values to zeros<br>for col in [&#39;itemPrice&#39;, &#39;itemShippingPrice&#39;, &#39;itemTax&#39;, &#39;promotionDiscountItems&#39;]:<br>    frame[col] = pd.to_numeric(frame[col], errors=&#39;coerce&#39;)<br>    frame[col] = frame[col].fillna(0)<br><br>print(frame)<br><br>print(&#39;/// Saving frame...&#39;)<br>processed_file_path = os.path.join(os.getcwd(), &#39;data&#39;, &#39;processed_order_items.csv&#39;)<br>frame.to_csv(processed_file_path, index=False)<br>print(&#39;-- saved!&#39;)</pre><p>The script output looks something like this:</p><pre>// DAY 2024-08-22<br>--Loaded orders &amp; items | orders=1154 | items=1190<br>// DAY 2024-08-23<br>--Loaded orders &amp; items | orders=1035 | items=1085<br>...<br>// DAY 2024-08-29<br>--Loaded orders &amp; items | orders=964 | items=988<br><br>/// Creating frame...<br>            amazonOrderId  ... promotionDiscountItems<br>0     114-8313437-1034437  ...                    0.0<br>1     113-8312128-1547680  ...                    0.0<br>2     113-4774685-2750394  ...                    0.0<br>3     111-7776087-9667357  ...                    0.0<br>4     114-7658306-5744469  ...                    0.0<br>...                   ...  ...                    ...<br>8505  112-6818067-9840260  ...                    0.0<br>8506  114-4983025-4845398  ...                    0.0<br>8507  114-5584699-2254768  ...                    0.0<br>8508  111-8269030-5060163  ...                    0.0<br>8509  114-3595331-4454175  ...                    0.0<br><br>[8510 rows x 12 columns]</pre><p>With processed data, you can do anything. The resulting CSV file can be opened in Excel, Google Spreadsheet, sent to Amazon S3, or uploaded to a database.</p><h4>Performing Basic Analytics</h4><p>Let’s perform a simple grouping of the resulting DataFrame by sellerSkuover the selected range. For each item, we’ll display the number of orders, total sales, number of ordered items, items per order, and also calculate the percentage of canceled orders that include this item. We’ll sort the result by sales.</p><pre>#Choose data range for example<br>frame = frame[(frame.date&gt;=&#39;2024-08-22&#39;) &amp; (frame.date&lt;=&#39;2024-08-30&#39;)]<br><br>#Group by sku<br>grouped = frame.groupby(&#39;sellerSku&#39;).agg(<br>    order_count=(&#39;amazonOrderId&#39;, &#39;nunique&#39;),<br>    total_sales=(&#39;itemPrice&#39;, &#39;sum&#39;),<br>    total_items=(&#39;itemQuantity&#39;, &#39;sum&#39;),<br>    cancelled_orders_percentage=(&#39;orderStatus&#39;, lambda x: (x == &#39;Canceled&#39;).mean() * 100)<br>)<br>#Add items per order<br>grouped[&#39;items_per_order&#39;] = grouped[&#39;total_items&#39;] / grouped[&#39;order_count&#39;]<br><br>#Sort by sales<br>grouped = grouped.sort_values(by=&#39;total_sales&#39;, ascending=False)<br><br>print(grouped)</pre><p>Output</p><pre>order_count  total_sales  total_items  cancelled_orders_percentage  items_per_order<br>sellerSku                                                                                        <br>MXC-222-AKJB         2357     61704.01       2511                     6.703437         1.065337<br>TVF-760-UNBC         1043     31496.96       1090                     6.321839         1.045062<br>ZZM-065-POJE          816     25984.61        856                     3.186275         1.049020<br>XKA-382-XQLW          468     13220.67        483                     7.692308         1.032051<br>TJN-820-IZBB          411     12164.92        421                     4.379562         1.024331<br>NUW-470-CUFK          335     10799.51        352                     5.970149         1.050746<br>...</pre><p>The author of this article is the Founder and CTO of <a href="https://sellerflux.com/">SellerFlux</a>. We are <strong>Empowering E-commerce with Data-Driven Solutions</strong> and specialize in providing software solutions specifically designed for e-commerce businesses. If you need expert help in building, optimizing, or automating your e-commerce analytics and data processes, don’t hesitate to <a href="https://sellerflux.com/about"><strong>Сontact us</strong></a><strong>.</strong></p><h4><strong>Useful Links</strong></h4><ol><li><strong>Amazon SP-API: Get Orders with Python: </strong><a href="https://medium.com/@andrewkushnerov/amazon-sp-api-get-orders-with-python-7b7e913d87ea">https://medium.com/@andrewkushnerov/amazon-sp-api-get-orders-with-python-7b7e913d87ea</a></li><li><strong>Amazon getOrderItems API function:</strong> <a href="https://developer-docs.amazon.com/sp-api/docs/orders-api-v0-reference#get-ordersv0ordersorderidorderitems">https://developer-docs.amazon.com/sp-api/docs/orders-api-v0-reference#get-ordersv0ordersorderid</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=68506bbde58a" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>