:  
 


            ? :             .

     Excel,   SQL-,    Python    .    - ,   ,   ,         .

   ,    ,     .      ,  ,     .





 

:  





:



 



 




.    



   -  :   ,      .    ,    .      .    .    ,      .        .

   .    ,    ,      ,    . Excel     . SQL     . Python       .      ,     ,   .

     .        ,   .     2025 .           .    CSV,   SQLite,   ,     .        .

  :             ?  ,    .     ,   ,  ,      .     :  , ,  ,     .




  


   ,      .        .      .       .         ,       .

     .            .            .          ;     .          .

   ,  , Python   SQL.  SQL  SQLite:     ,    .      .      PostgreSQL, ClickHouse   ;    .     DataLens     BI-,    .         .

    .       ,    .     ,      .     ,  ,  ,    .    ,       .        .

      .       ,     .      .          SQL  Python:    ,       .




   


       ,          .     ,  ,  ,  , SQL-,  ,     .   :   ,       .

   .       :   , , ,       .         .         .         ,    .

     .    .   ,        ,     .      ,   .          .




 1.     





   


     . ,      ,      ,     ,     .          .     ,    .

     .    ?    ?     ?      ?  ,   ?     ,   .       .           .

    . BI-      ,   .     ,   . -     . Data Scientist    .   ,         ,      .

      :   ,    ,   .       .     ,     ,   ,         .




   


     .            .   ,       .        .       .      ,        .

         .    ,    ,      .    .  ,    ;      .

       . ,  ,        .    ,    .   ,   ,    ,          .




   


   hh.ru   14  2026       95 700113 100 ,         37 500   65 000  [1].    :     ,         .          .

        .  , ,  ,  , ,    .      ,    .      :   ,    .      .

         ,       .        .     ,  ,       .     ,       ;       .




  1.   


   .   ,    ,       . :        ,   .  ,       .    ,      .

         .   :  SQL-,  ,   .   ,    .      ,     .

  :     ,    ,          .        .           .




 2.     





  


       ,    . ,             .      , ,    ,      .      .

      .   ,         .        1   30  2025  .           .    ,        .    ,      .

     .         ,      .        .  ,        .

   :      ,            ;        .  ,     :   ,           .




 


   ,    .  orders  .  customers  .  marketing    .  traffic     ,    ,   .         ,   .

    .      ,    ,    .    300     .         ,    900 .  ,  ,    -  .

     .           .    /        .    ,        .        100%.




  


        .    gross     , discount  , refund    .       gross ? discount ? refund.     net_revenue.       .

     net_revenue ? cost ? shipping.  cost    , shipping     .           :      .       .           .

        spend.    ,     .       .   ,           after_marketing  .

       .      ,     .        .    .        ,    .




 


      .  ,  , ,  , , ,     . ,         refund > 0,     .   ,           .

     .    ,   .       .   ,      ,    .      :         .

       .      1 000    500,    9 000   900.     30%,     1 400 / 10 000 = 14%.   - .         .




  2.  


   .   , ,    ,       . ,      .   :  SQL  Python ,   ,     ,   .

   .    ?     ?       ?            ?     ,      .      ,    .




 3.     





   


    .  orders_raw.csv  4 827 :            . orders_clean.csv  4 815       .     ,   .  customers.csv  products.csv  .  marketing.csv  traffic.csv  24 :     .

 shop.sqlite     orders, customers, products, marketing  traffic.  analyze.py   ,  mart_channel_month   monthly.  SQL-    orders;     .    :  Excel  Python   ,   SQL    .    CSV ,      .

 build_project.py     .        .         ,     .           .       .




  


 orders  order_id  , order_date       --. customer_id    , product_id   . channel  organic, search, social  email. status   completed  cancelled. quantity      .  gross, discount, refund, cost  shipping        .

 customers     customer_id; city  .  products  product_id,  category,   unit_price    unit_cost.       ,     .         .       -   .

 marketing  spend    month  channel.  month   2025-01.  traffic  sessions       .     ,      .     ,      ,      .

     . ,       cost      .  - ,    .        .           .




    


    .  ,  , ,    .     .       ,   ,  .          .

CSV   ,     .     , ,     .       .          ,       .       .

 CSV   UTF-8   BOM  -.  Excel      CSV   .      .       .       .




   


   .          ?    .     .      .    :       ,            .

    .      .    ,    .     .    ,   .   ,    ,      .

     .            .        .      ,            .        .




    


      CSV   .         .              .      order_id.   ,      .

  order_id     ,      .         .       :     .        .

         4 815  order_id.       ,     .       .              ,     .




  3.  


     , , , .  orders_raw  4 827  ,   , 4 815      .     .  marketing  traffic    -    24 .

    .     :   ,   ,   .    , , .              .

 :   ,  channel     partner      .        .       ,   .   ,    .



 4. 

Excel

    




  


    ,  , ,   .     ,     .      .        Power Query.         .

    Excel     Orders.     ,       K2:K4816.      ,     CSV  .     .     :   ,    .

  . order_id  quantity   ,    , order_date  .     ,      .        ,        .         .




   


  net_revenue.   Excel      :

=[@gross]-[@discount]-[@refund]



  contribution    :

=[@net_revenue]-[@cost]-[@shipping]



   ,  completed_flag:

=IF([@status]="completed",1,0)



   IF  ,        .     :

=([@status]="completed";1;0)



          .     ,       .    ,      .

 month          -.       .        marketing,  YEAR  MONTH  Power Query.   TEXT   ,      .    2025-01   01.01.2025    .




    


        .  gross = 2 000, discount = 100, refund = 0, cost = 1 100, shipping = 180,  net_revenue = 1 900, contribution = 620.    .    ,       .

          refund = 1 900.    ,     1 280 .           .      , -      .

  .      , completed_flag   .        .     ,        .




   


            Orders [10].  month  , channel   , net_revenue  contribution   . ,      ,   .  Excel  ,     .       .

        completed_flag.       4 815,    4 428.      9 453 295 ,     1 970 070.     expected.json.      : ,  ,    .

  spend    .          ,       . ,  97 000   social       .     175   ,  16 975 000 .   ,    .

            .                .        .             .




  


   ,   product_id  .    Excel  XLOOKUP,     X;     .         INDEX  MATCH.     Products:

=INDEX(Products[category],

MATCH([@product_id],Products[product_id],0))



  MATCH   .         .      product_id,   ,   .       .

    IFERROR   .      ,      .        .  ,     .         ,     .




  4.  


      net_revenue.   contribution.          ;         .        .

  .   ,  ,    ,    . :           ,    .  ,           .       .       .

   ,         .     ,        .      ,      .



 5. 

Power



Query

  




  


   ,     .     ,   ,      . Power Query          .     ,      .

 orders_raw.csv   .      .   ,       ,  channel        .      ,     ,     .

    order_id        .       ,      order_id.    ,     .       .




  


    .  03/04/2025        .   ISO- --  ,       .              .

       .            .     1 250,50,        .        CSV    .

     .         .    ,      .    ,    .        .




  


        .        .         .       customers.     ,     .

    customers   :     .        .    .     .   ,    ;  , ,        .

      month  channel.  net_revenue, contribution  completed_flag,  .     marketing   .       .    channel        .




   


    ,        .           .   ,    .             .

     ,    .         .     ,     .       ,      .

   .   ,   ,     ,      .          .   Excel      .




  5.  


   CSV      .  .       4 815,          .     ,       .

 :       .        .       ,      .   ,         .

      .    ,     .       SQL:      ,     .



 6. 

SQL

    




  


 shop.sqlite  ,     SQLite.         .    ,     .   orders, customers, products, marketing  traffic.      :        .

   ,     Python,   sqlite3    .       ;  Python   .      run_sql.py:

from pathlib import Path

import sqlite3



root = Path(__file__).resolve().parent

database = root / 'shop.sqlite'

if not database.exists():

raise FileNotFoundError(database)

with sqlite3.connect(database) as con:

rows = con.execute(

'SELECT COUNT(*) FROM orders'

).fetchall()

print(rows)



   4815.        ,    .    no such table,  : SQLite         .   ,   ,     .

SQL ,     .  SELECT   ,  FROM  ,  WHERE    .    ORDER BY  .        ,     [2].




  


  ,        :

SELECT

order_id,

order_date,

channel,

status,

gross,

discount,

refund

FROM orders

ORDER BY order_id

LIMIT 10;



   .    FROM   .     .       ;    .      : SELECT *    ,        .

        AS:

SELECT

order_id,

gross - discount - refund AS net_revenue,

gross - discount - refund

- cost - shipping AS contribution

FROM orders

ORDER BY order_id

LIMIT 10;



           .  ,   net_revenue        SELECT   .          .




   


    :

SELECT

order_id,

order_date,

channel,

gross - discount - refund AS net_revenue

FROM orders

WHERE status = 'completed'

AND order_date >= '2025-01-01'

AND order_date < '2025-04-01'

ORDER BY order_date, order_id;



       .     ,    .   31  00:00         .      ISO-,       .      .

   ,  IN.   AND  OR,     .      social-  :

SELECT order_id, channel, order_date

FROM orders

WHERE channel IN ('search', 'social')

AND order_date >= '2025-04-01'

AND order_date < '2025-07-01'

ORDER BY order_id;



     .   SQL    =.     IS NULL,   = NULL. NULL          ,      .




 


  ,     :

SELECT

COUNT(*) AS orders,

SUM(CASE WHEN status = 'completed'

THEN 1 ELSE 0 END) AS completed,

SUM(gross - discount - refund) AS net_revenue,

SUM(gross - discount - refund

- cost - shipping) AS contribution

FROM orders;



  4 815, 4 428, 9 453 295  1 970 070 . CASE     . SUM   . COUNT(*)  ,  COUNT(column)  ,      NULL.      ,       .

SUM     SQLite  NULL.      ,  COALESCE(SUM(...), 0),  ,       ,    .          .

    :

SELECT

channel,

COUNT(*) AS orders,

SUM(gross - discount - refund) AS net_revenue,

SUM(gross - discount - refund

- cost - shipping) AS contribution

FROM orders

GROUP BY channel

ORDER BY net_revenue DESC, channel;



 GROUP BY     .        ,    . SQLite     ,        :             .


WHERE

 

HAVING

WHERE   , HAVING    .        ,       HAVING:

SELECT

channel,

SUM(gross - discount - refund) AS net_revenue

FROM orders

WHERE status = 'completed'

GROUP BY channel

HAVING SUM(gross - discount - refund) > 2000000

ORDER BY net_revenue DESC;



      .     ,        ,    .           ,       .




  6.   


     ,       .         .     order_id    ,    .

      .  SQLite  substr(order_date, 1, 7).    SELECT  order_date    .  : 1 435 795 .    ,  ,     .        ,      ,     .




 7.    





  


JOIN    .   INNER JOIN   ,  LEFT JOIN        .   ,     NULL.   ,     .        .

    :

SELECT

o.order_id,

o.customer_id,

c.city,

o.gross - o.discount - o.refund AS net_revenue

FROM orders AS o

LEFT JOIN customers AS c

ON o.customer_id = c.customer_id

ORDER BY o.order_id

LIMIT 20;



 o  c    .     .      customer_id  .       4 815.    ,     .

SELECT customer_id, COUNT(*) AS copies

FROM customers

GROUP BY customer_id

HAVING COUNT(*) > 1;



    .    ,    :

SELECT o.order_id, o.customer_id

FROM orders AS o

LEFT JOIN customers AS c

ON o.customer_id = c.customer_id

WHERE c.customer_id IS NULL;



    .    ,      ,      .  INNER JOIN         .


 

LEFT



JOIN

  

,    ,  ,  .  c.city = ''  WHERE      ,     NULL    .           ,     ON.       , WHERE .    ,      .

   .     ?      ?    ?         JOIN  .




 ,   


       .      WITH.    CTE;       :

WITH sales AS (

SELECT

substr(order_date, 1, 7) AS month,

channel,

COUNT(*) AS orders,

SUM(CASE WHEN status = 'completed'

THEN 1 ELSE 0 END) AS completed,

SUM(gross - discount - refund) AS net_revenue,

SUM(gross - discount - refund

- cost - shipping) AS contribution

FROM orders

GROUP BY substr(order_date, 1, 7), channel

)

SELECT

s.month,

s.channel,

s.orders,

s.completed,

s.net_revenue,

s.contribution,

m.spend,

s.contribution - m.spend AS after_marketing

FROM sales AS s

LEFT JOIN marketing AS m

ON s.month = m.month

AND s.channel = m.channel

ORDER BY s.month, s.channel;



    24 .  spend  1 614 000 ,  after_marketing  356 070.   marketing     ,     ,     .       .                ,     .

   spend    .    ,     .  organic      .    JOIN       .    .




  


   ,     .   :    300    ,    200   .    500.     ,   ,     800.    ,     .

       ,     .         . ,   .         ,        .

     SUM(DISTINCT shipping).        300 , DISTINCT      .      .    ,  .




  .


   .

   ,     (https://www.litres.ru/book/anton-zolotavin-34104812/professiia-analitik-dannykh-74621103/)  .

      Visa, MasterCard, Maestro,    ,   ,     ,  PayPal, WebMoney, ., QIWI ,       .


