r$j!Q"ddlmZddlmZddlmZmZmZddlmZddl m Z ddl m Z m Z mZddlmZmZmZmZmZmZmZmZmZddlmZmZmZmZmZmZm Z d Z!d Z" dd?d#Z$d@d&Z%dAd*Z&dBd0Z'dCdDd3Z(dEd4Z)d5Z*dFd7Z+dGd9Z,dHd;Z-d S)I) annotations) defaultdict)datetimetimetimezone)median)Any)Sessionfuncselect) AnalyticsDailyAggregateAnalyticsImportBatchAnalyticsImportFileAnalyticsPostSnapshot ExternalPost PostDraftProviderTheme VideoAsset)UNKNOWN YOUTUBE_CHARTYOUTUBE_DAILY_TOTALSYOUTUBE_VIDEO_TABLEcompute_snapshot_metrics file_hashparse_csv_by_typec|dSt|tr|Stj|tjt jS)Ntzinfo) isinstancercombinerminrutcvalues S/home/agent/projects/AutoShorts_Dashboard/backend/app/services/analytics_service.py as_datetimer' s? }t%""  E48HL A A AAc||dS||z SN)newolds r& safe_deltar.(s {ckt 9r(youtubeNmanualFsessionr fileslist[tuple[str, bytes]]providerstr account_id str | None snapshot_atdatetime | None source_labelis_demoboolimport_batch_labelreturndict[str, Any]c t|}|ptjtj}t |||dt ||||p|} || || | g} g} d} d} d}d}g}g}|D ]\ }t|}t|}| |j z } |j r||j |jr||j|t#t$t$j |kt$j|jkt$jdk}t%| j ||j|j t1|jt1|j|rdnd}|||| ||r_|j |_|  dg|jd|_||||jt:krdd |_|j |_|  fd |jD|||N|jt>krd}|j D]}tC|"d }|'|tG| j||||"d |"d pi|dz }||z }||_$|j |z |_d|_n(|jtJkr|j&r|  d|j&"d d|j&"dd|j&"dd|j&"dd|j&"d d}|j D]}|"dpd|"dd}|t#tNtNj(|ktNj)|ktNj*|k}|stO||||"dtC|"d|"d|||p|  }tW||"d|}|r$|j|_,|j-|_-|j.|_.|||| || dz } n|"dp|j/|_/tC|"dp|j0|_0|"dp|j1|_1||_2||_3|p||_4 |_5tjtj|_6||||t#tntnj(|ktnj)|ktnj*|k8tnj9:}tw||}tod6id | jd!|d"|d|d#|j,d$|d|"ddtC|"dd|"dd |"d d|"dd|"dd|"dd|"dd%|"d%d&|"d&d'|"d'd(|"d(d)|"d)d*|"d pid+|d,|d-|p|d. }ty|||||dz }|dz }||_$|j |z |j&rdndz |_d|_nl|jtzkr\t |j |_$t}d|j t |j z |_|j dkrd/nd|_g|j|j|_g|j?|j|_?|  fd0|jD|  fd1|jD||| |r!tCt|| _ |r!tCt}|| _| r|s|sd n| s| rd2nd| _| | _A| | _| | _?|| || | | Bd34||zt}d| |z |z | | | ||d5S)7N importing)r4r6r8status total_filesr:r;r=rimportedskipped_duplicate)batch_id file_namer detected_type row_count warnings_json errors_jsonrBz: duplicate file hash skippedzDuplicate file hash skippedfailedcg|] }d| Sz: r+.0efilenames r& z,import_youtube_csv_files..ws'EEE!h--!--EEEr( metric_dateviewsraw)rFr4r6rTrUraw_jsonz: total row views=z watch_time=watch_time_hoursz subscribers=subscribers_deltaz impressions= impressionsz ctr=impression_ctr_pctexternal_post_idztitle:titleuntitled published_at duration_sec) r4r]r6r^r`rar:r;r=source_file_namerFr4r6video_asset_idr8avg_view_duration_secretention_proxy_pctviews_per_impressionsubscribers_per_1000_viewsviews_per_day_since_publishrWr:r;r=rb empty_validcg|] }d| SrNr+)rPwrRs r&rSz,import_youtube_csv_files..s'EEEH++++EEEr(cg|] }d| SrNr+rOs r&rSz,import_youtube_csv_files..s'AAAa())a))AAAr(imported_with_warningsjsonmode)batch imported_rows skipped_rowswarningserrors created_postscreated_snapshotscreated_daily_metricsr+)Crrnowrr#rlenaddcommitrefreshrrrIdate_range_startappenddate_range_endexecr rwhererHrBfirstidlistrtrursrJrextendrrowsr'getr rrr total_rowrr4r]r6match_video_assetrctopic_idseriesr^r`rar:r;r=rb updated_atrorder_byr8descr apply_deltasrmaxrKr" total_rows model_dump)!r1r2r4r6r8r:r;r= provider_enumrqrtrurrv snapshots daily_metrics date_starts date_endscontentdigestparsed duplicate import_filerDrowrT external_idpostlinkedpreviousderivedsnapshotrRs! @r&import_youtube_csv_filesr.s X&&M;hl!;!;K JJ!-=   E KK NN OOEHFJMIMKI"aa'7##"7++f&&  " 8   v6 7 7 7   4   V2 3 3 3LL & ' ' - -#-7#1V5II#*j8    %'' *X .&v//V]++*3D&&      K    $$$  '-'7K $ OOxFFF G G G(c+*C(cEb(cK % KK $ $ $ NN      7 * *!)K '-'7K $ MMEEEEv}EEE F F F KK $ $ $ NN      #7 7 7H{  )#''-*@*@AA & 3"X*) +'''** WWU^^1r A  X %M(0K %'-'7('BK $!+K    !%8 8 8 dd63C3G3G3P3Pdd^d^n^r^rtF_G_GddV\VfVjVjk~VVddNTN^NbNbcpNqNqddx~xHxLxLMaxbxbddH{Q Q !gg&899c=ccgggV`FaFa=c=c ||<((..$->$5D$/:= %''  %'!.)4#-!ggg..%01H1H%I%I%(WW^%<%<%1 '+=+M)1   D/w8H8H+VVF4.4i+(. &,m KK%%%NN$$$OOD)))!Q&MM!$!1!1!?TZDJ(3CGGN4K4K(L(L(aPTPaD%(+(?(?(T4CTD%(4D%#*DL.@.PLD+,4D)&.l8<&@&@DOKK%%%NN$$$"<<011U-6-G->+M-8JF X3?DDFFGG%''33 DD0"XX*] *z&1[  $(#6#6  !, '''***"-SWW^-D-D!E!E!E"%!8!8!8'''***&)WW-?%@%@%@'*gg.A&B&B&B!$ 6 6 6(+ww/C'D'D'D+2++6M*N*N*N )0 4I(J(J(J!"*15K)L)L)L#$07{{;W/X/X/X%&18 V[AQAQ.Q'R'RK $282Ba2G2GZK $Rk&?$R&/$R !"LK$;"Lfm"L EEEEV_EEEFFF AAAA6=AAABBB K   ?!,S-=-=!>!>;*3y>>::%OyOMO88ltQOx~QOPhPhEOEL!E"EE KK NN OOE!!v!.."]2AzI5 EFF&&!.   r(r^rVideoAsset | Nonec&|r|}|tttj|k}|r|S|tttj|k}|r$| t|j }|r|S|tt D]X}|j pi}t|} |r*|| vr&| t|j }|r|cSY|sdSt|} d} d} |tt D]0}t| t|jpd} | | kr|} | } 1| dkr| ndS)Ng?)lowerrr rrexternal_youtube_idrrr]rrcall metadata_jsonr5normalize_textjaccard working_title)r1r^rneedledirect draft_directvideodraftmeta flattened normalizedbest best_scorescores r&rr s!""$$fZ0066z7UYd7deeffllnn  M||F9$5$5$;$;Iz!normalize_text..,s- s s sTehimenenqrererDerererr(rc3jK|].}|r|ndV/dS) N)isalnumr)rPchs r& z!normalize_text..,s;$Y$YR2::<<%HRXXZZZS$Y$Y$Y$Y$Y$Yr()joinsplitr$s r&rr+s> s sRWW$Y$YSX$Y$Y$YYY__aa s s ssr(abfloatcX|r|sdSt||zt||zz S)Nrr)rrs r&rr/s5 As q1u::AE ""r(rrrAnalyticsPostSnapshot | NoneNonecb|s d|_dSt|j|j|_|j|jr|j|jz dz|_t|j|j|_t|j|j|_t|j |j |_ t|j |j |_ t|j |j |_|j}|j}|j |t$j}|j |t$j}t)d||z dz }||_|j2|j|z |_|jdkrdn|jdkrd nd |_dSdS) Nr,drg{Gz?iQ risingrXslowingstable)trendr.rU views_deltaviews_delta_pctrYwatch_time_deltar[impressions_deltarZsubscriber_delta_changer\ ctr_changereretention_proxy_changer8rreplacerr#r total_secondsdays_since_previous_snapshotviews_velocity_per_day)rr current_at previous_atdayss r&rr5s %hnhnEEH'HN'#+#7(.#H3#N *8+DhF_ ` `H!+H,@(BV!W!WH'1(2LhNh'i'iH$$X%@(B]^^H&01MxOk&l&lH#%J&K ''x|'<< !!)))>> tj;.==??%G H HD,0H)'*2*>*E'%-%9B%>%>QYQehiQiQiIIow('r( include_demolist[AnalyticsPostSnapshot]ctttj}|s#|tjdk}||}t}g}|D]E}|j |j |j f}||vr| |||F|S)NF)r rrr8rrr;rrsetr4r6r]r{r)r1rstmtrseenlatestrkeys r&latest_snapshotsrNs ' ( ( 1 12G2S2X2X2Z2Z [ [D Bzz/75@AA <<   ! ! # #D 55D F|S^S-AB $;;    c Mr(c t|}|tttj}|tttj }td|D}td|D}td|D}td|D}t|dd}t|dd} d|D} |td | d dDtd | d dD|t|d ||r|jnd| r| jndd| d|Dd|DdS)Nc3(K|] }|jpdVdSrNrUrPss r&rz%analytics_overview..bs(33qagl333333r(c3(K|] }|jpdVdSr)rYrs r&rz%analytics_overview..cs*>>!a(-A>>>>>>r(c3(K|] }|jpdVdSr)rZrs r&rz%analytics_overview..ds*DD1.3!DDDDDDr(cg|] }|j Sr+rers r&rSz&analytics_overview..esCCCqQ2CCCr(c|jpdS)Nrrs r&z$analytics_overview..fs agmr()rdefaultc|jpdS)Nrgrs r&rz$analytics_overview..gsQ-I-TWr(cxg|]7}|j|jpdd8S)r)daterU)rTr isoformatrU)rPds r&rSz&analytics_overview..hsBeeeZ[Q]//11;;== STUUeeer(c3&K|] }|dV dSrUNr+rPps r&rz%analytics_overview..ls&$K$KAQwZ$K$K$K$K$K$Kr(ic3&K|] }|dV dSrr+rs r&rz%analytics_overview..ms&%M%MQaj%M%M%M%M%M%Mr(i)total_views_latestviews_last_7_daysviews_last_28_dayssubscriber_deltatotal_watch_time_hoursaverage_retention_proxy best_video best_video_subscriber_conversionc,g|]}t|Sr+)snapshot_to_dictrs r&rSz&analytics_overview..us!7771#A&&777r(c:g|]}|dS)rnro)r)rPrs r&rSz&analytics_overview..vs&???!ALLfL--???r()kpis daily_viewsvideosimports)rrr r rrTrr imported_atrsumaveragerroundr^) r1rdailybatches total_views total_watchr  avg_retention best_by_views best_by_subs daily_pointss r&analytics_overviewr%^s g & &F LL 788AABYBeff g g k k m mEll6"677@@AUAaAfAfAhAhiijjnnppG33F33333K>>v>>>>>KDDVDDDDDCCFCCCDDM$;$;TJJJMv#T#T^bcccLee_deeeL#.!$$K$Kbcc9J$K$K$K!K!K"%%M%M,stt:L%M%M%M"M"M 0&+K&;&;'41>H---DFR0\ 0B0BX\   $77777??w???  r(c`d|D}|sdSt|t|z S)Ncg|]}||Sr*r+)rPvs r&rSzaverage..{s333aQ]]]]r()rrz)valuesfiltereds r&rrzs9336333H t x==3x== ((r(rch|d}|jdn|jdkrdnd|d<|S)Nrnrounknown-strongweakretention_label)rre)rdatas r&rrsI <.s111QAGLq111r(c*g|]}|j |jSr*rrs r&rSz&analytics_insights..s#ccc!AVAb-AbAbAbr(c g|] }|jpd Sr:)r[rs r&rSz&analytics_insights..s===!-,1===r(c*g|]}|j |jSr*)r\rs r&rSz&analytics_insights..s#[[[1!:N:Z!&:Z:Z:Zr(c*g|]}|j |jSr*rrs r&rSz&analytics_insights..s#lllA1C_Ck1/CkCkCkr(rwinnersuccesszWinner patternz: keep this topic/hook style.)r6r7r^r8r]packaging_problemwarningzPackaging opportunityzM: viewers who watch stay longer, but distribution/click packaging looks weak. hook_problemzHook/CTR problemzO: high exposure but weak click-through. Rework title/thumbnail/opening promise.retention_problemzRetention problemzE: topic attracts views, but pacing or first seconds may lose viewers.rsubscriber_converterzSubscriber converterzA: converts viewers into subscribers. Reuse a similar promise/CTA.rnegative_trendzDistribution slowingzR: distribution flattened since previous import. Consider follow-up or repackaging. ) rrr^r]rUrerr[r\rgrrtopic_opportunities)r1r views_valuesretention_valuesimpressions_values ctr_values subs_values views_medianretention_medianimpressions_median ctr_median subs_medianinsightsrr^s r&analytics_insightsrTs g & &F l f?qjkkl l11&111Lccvccc==f===[[[[[JlllllK+7>6,'''QL3CJv.///7IP 2333q'18 ###qJ)4;&%%%!K%'H  ~ ~-1- GLqL ( (a.C.HqL\-\-\ OOX9O_otmTmTmTjkj|}} ~ ~ ~ ! &Q*: : : 1Q]?]?] OO%8iZqBGVVVlml~ @ @ @ M Q"4 4 4!:N:SRSWa9a9a OO^Ugw|uNuNuNdedvww x x x GLqL ( (a.C.HqL\-\-\ OO%8iZm~C{J{J{J`a`rss t t t ( 3G{ B B OO%;]sDIALALALbcbtuu v v v 7i   OO%56TjzxTxTxTjkj|}} ~ ~ ~ OO'88999 CRC=r(rc tt}d|ttD}|D]E}||j}|r'|jr ||j |Ftd|Dpd}g}| D]{\}} td| Dpd} t| dkrH| |krB|t|} | ddd| r| jn|d |d ||S) Nci|] }|j| Sr+)r]rs r& z'topic_opportunities..s U U UqQ  U U Ur(cg|] }|j Sr+rrs r&rSz'topic_opportunities..s///1qw///r(rcg|] }|j Sr+rrs r&rSz'topic_opportunities..s333QW333r(r topic_opportunityr@zTopic opportunityzR: few videos but above-average views. Make 3 more variants before switching topic.)r6r7r^r8r)rrrr rrrr]rrritemsrzrr^) r1rgroupspostsrrall_avgoutrr topic_avgtopics r&rHrHs5@5F5FF U UGLL 9M9M,N,N,R,R,T,T U U UE ,,yy+,,  ,DM , 4= ! ( ( + + +/////005AG C ,,..GG$33d333449 t99q==Y00KKx00E JJ 3UhHMy[x}yDyDS[vovovo}EFF G G G Jr()r/NNr0FN)r1r r2r3r4r5r6r7r8r9r:r5r;r<r=r7r>r?r*)r1r r^r7rr7r>r)r%r5r>r)rrrrr>r)rrrrr>r)F)r1r rr<r>r)r1r r>r?)rrr>r?)r1r r>r2)r1r rrr>r2). __future__r collectionsrrrr statisticsrtypingr sqlmodelr r r app.models.corer rrrrrrrr"app.services.analytics_youtube_csvrrrrrrrr'r.rrrrrrr%rrrTrHr+r(r&ris""""""######----------**********                      BBB!#' %)[[[[[|00000>tttt#### xxxx2      8))) Br(