# Rapport de stage

## Remerciements<a id="merci"></a>

Je tiens tout d’abord à remercier Angeline Hourlier ma tutrice de stage pour m’avoir accompagné pendant ces six mois en entreprise et pour m’avoir guidé à chaque étape. Je souhaite également remercier l’ensemble de l’équipe SOFRECOM et en particulier Rym Ayari, Amira Abbessi et Bilel Tarhouni pour toute l’aide technique et méthodologique qu’ils m’ont apportée.

Je souhaite aussi remercier Sandrine Merle et l’ensemble de l'équipe d’administration des ventes pour m’avoir accueillie en physique dès mon premier jour sur le plateau d’orange et pour avoir veillé à mon bien-être tout au long du stage.

Je remercie également Mayouran Varathalingam et Yoann Ayoub pour m’avoir aidée à prendre les outils en main lors de mon arrivée. Je remercie enfin l’ensemble de la régie d’orange et en particulier les autres stagiaires qui ont fait de ce stage une très bonne expérience autant sur le plan humain que professionnel.

## Sommaire <a id="sommaire"></a>

### [Remerciements](#merci)
### [Sommaire](#sommaire)
### [Introduction](#Intro)
### [Résumé Technique](#résumé)
### [ I Présentation de l’entreprise](#prez)
* [I.1 Introduction](#introprez)
* [I.2 Présentation de la régie](#régie)
* [I.3 Les concurrents](#laconcu)
* [I.4 Les différents pôles](#lafamille)
* [I.5 L'équipe de travail](#lateam)
* [I.6 Conclusion](#concluprez)
### [II La mission](#miss)
* [II.1 Le sujet](#suj)
* [II.2 Le planning](#planning)
* [II.3 Les contributions](#contributions)
* [II.4 Outils et Technologies](#outils)
* [II.5 Prise de recul](#recul)
### [III Réalisation](#real)
* [III.1 Introduction](#introreal)
* [III.2 Le sujet sous l'angle BI](#sujetbi)
* [III.3 Le développement](#dev)
* [III.4 Conclusion sur les réalisations](#conclureal)
### [Conclusion](#conclu)
### [Glossaire](#gloglo)
### [Source](#source)
### [Annexe](#annexe)

## Introduction <a id="Intro"></a>

Le stage d’assistant ingénieur décrit dans ce rapport a été effectué au sein de l’entreprise Orange, l’un des leaders français de la télécommunication. Le stage s’est déroulé plus précisément chez Orange Advertising, la régie publicitaire intégrée du groupe Orange. Elle a pour objectif de diffuser de la publicité sur les portails et applications de la firme, ainsi que sur ceux des ses partenaires Mappy et ViaMichelin.

Avec l’essor d’internet, la publicité en ligne est devenue l’une des clés d’une campagne marketing. Avec plus de 31.2 millions d’internautes et 7 millions de foyers possédant Orange TV, la régie du groupe a accès à un énorme public et à ses données grâce à l’intermédiaire de [cookies](#miamiam).

Pour gérer toutes ses données et pour les rendre accessibles aux différents services, la régie publicitaire a mis en place une base de données Bird. La mission principale de ce stage était d’améliorer l’expérience utilisateur des acteurs de la régie sur cette base de données, ainsi que sur les autres outils [BI](#businessI).

Après un court résumé technique, nous allons faire une présentation de l’entreprise et de l’équipe, puis détailler la mission de ce stage et enfin discuter des réalisations finales.

## Résumé Technique <a id="résumé"></a>

L’objectif principal du stage était de faciliter le travail des différents acteurs de la régie en améliorant les outils de [Business Intelligence](#businessI). Cela comprend l’optimisation de l’ensemble de base de données Bird. Pour cela j’ai utilisé principalement [SSMS](#sms) (SQL Server Management Studio). Concrètement, le travail consistait en la modification de tables (ajout de champs, de contraintes, suppression d’écarts), l’analyse d’anomalies sur la base ou encore en des modifications des [procédures stockées](#PS) (modification du mode d’alimentation d’une base, automatisation de la suppression ou l’archivage des données etc…).

L’autre grande partie du stage consistait en l’automatisation de l’intégration des données issues de [GAM](#gam) (Google Ad Manager) dans Bird pour faciliter le travail de l’administration des ventes.

## I. Présentation de l'entreprise <a id="prez"></a>
### I.I Introduction <a id="Intro"></a>

Orange est une entreprise française de télécommunications. Originellement filiale de France Télécom ancien monopole français, elle prend progressivement la place de ce dernier. Avec ses [266 millions de clients dans le monde](https://fr.wikipedia.org/wiki/Orange_(entreprise)), elle est l’une des leadeuses de la télécommunication, notamment en Europe, aux Antilles et en Afrique. En France, l’entreprise est numéro 1 dans les télécommunications avec un chiffre d'affaires de 42 milliards d'euros, malgré la présence de concurrents importants tels que Bouygues Telecom, SFR ou encore Free.

Orange employait 142000 personnes dans le monde en 2020, dont 75 000 en France. Son ancien siège se situait à Arcueil à Orange Village, à l’emplacement où s’est déroulé mon stage. Récemment la firme a acquis des bureaux à Issy-Les-Moulineaux. L’immeuble, appelé Bridge, est devenu le nouveau siège social de la boîte.

Avec le temps l’entreprise a su varier ses services et produits commercialisés. Ainsi Orange propose notamment une banque mobile ([Orange bank](https://fr.wikipedia.org/wiki/Orange_Bank)), ou des services de domotiques et de téléassistance (entre autres). Nous allons présenter dans cette partie la régie publicitaire d’Orange, ses services, ses concurrents et ses différents pôles, ainsi que découvrir l’équipe de travail, ses méthodes et ses outils.


Figure 1 : Pays d’implantation d’Orange
### I.II Présentation de la régie <a id="régie"></a>
Orange possède un site web très visité (31.2 millions d’internautes), ainsi qu’une webmail et une offre TV. Grâce à cela, Orange peut se permettre de mettre en vente les espaces publicitaires présents sur ses sites. Pour mettre en place cette activité la régie d’Orange : Orange advertising a été créée. Une régie est une entreprise dont l’objectif est de commercialiser des espaces publicitaires auprès d’annonceurs (Renault, Carrefour, La Redoute…). Elles peuvent soit être en relation directement avec ces annonceurs, soit passer par l’intermédiaire d'agences média (Havas, Dentsu …).

Aujourd’hui son action s’étend aussi à la TV où la marque est pionnière en télévision adressé. C'est-à-dire que les publicités télévisées des clients d’Orange sont segmentées en fonction du profil de l’utilisateur. La firme possède aussi 25 smart stores dans lesquels elle peut mettre en place des opérations spéciales pour des campagnes publicitaires (par exemple des jeux, des tests de voiture etc…). La publicité se fait aussi par SMS. Enfin, la régie gère aussi la commercialisation des espaces publicitaires des sites internet de Mappy et Viamichelin.

Figure 2 : Les champs d’action de la régie

Sur la figure ci-dessus, une [impression](#impr) correspond à une fois où un utilisateur a vu apparaître la publicité sur son écran.

### I.III Les concurrents <a id="laconcu"></a>

Les principaux concurrents de l’entreprise sont les autres régies publicitaires françaises telles que Les Echos ou M6 Publicité. Cependant la régie est aussi en concurrence avec les GAFAM (Google, Amazon, Facebook, Apple et Microsoft), qui ont un grand nombre d’espaces publicitaires disponible sur leurs plateformes. Enfin, Orange revend aussi les [cookies](#miamiam), de ses abonnés (cookies logués) et des internautes visitant ses sites web. Par conséquent, elle est en concurrence avec les autres Data Provider (entreprise vendant des données), telles que Skyline ou LeBonCoin.


Figure 3 : Les concurrents d’Orange Advertising

### I.IV Les différents pôles <a id="lafamille"></a>

#### I.IV.1 Le service commercial

Le service commercial est divisé en trois entités, les opérations spéciales (OPS), le gré à gré, et le programmatique. Ce qui diffère principalement entre ces trois pôles est le mode de vente. Le service OPS va se concentrer sur des campagnes de publicités uniques en utilisant les smart store ou des jeux concours en ligne. Le gré à gré est le mode de vente classique qui se fait directement auprès des annonceurs ou des agences médias. Enfin, le programmatique est un mode de vente moderne qui se développe de plus en plus dans le monde de la publicité en ligne. Lorsque quelqu’un va cliquer sur un site web, l’espace publicitaire va être mis en vente automatiquement. Alors des algorithmes appartenant à différents traders vont proposer un prix et l’offre la plus haute gagne les enchères et l’espace publicitaire.

#### I.IV.2 Le service commercial

Ce pôle est divisé en deux parties, le service Data et le service Marketing et Communication. Le service Data a pour objectif d’utiliser les données d’Orange pour permettre aux clients de la régie de faire des campagnes publicitaires plus efficaces, ainsi que de nouer des partenariats externes pour vendre et acquérir de la donnée auprès d’autres acteurs du marché.

Le service Marketing et Communication s’occupe de créer l’image de la régie en fournissant des supports aux commerciaux, en gérant les réseaux sociaux ou encore en préparant des cadeaux pour les clients.

#### I.IV.3 Le service Opérations

Le service Opérations est le dernier et le plus grand pôle de la régie. Il regroupe l’administration des ventes, le traffic management, le service Ad Tech et Projets BI, l’account Management et le Yield Management. L’administration des ventes surveille le bon déroulement de la facturation. Le traffic management s’occupe de la mise en ligne des publicités et s'assure du bon déroulement des campagnes. Le service Ad Tech et projet BI est le service dans lequel j’ai effectué mon stage. Il gère le développement de nouvelles technologies dans le but d’optimiser le CA (chiffre d'affaires), d’assurer la sécurité, ou encore de répondre aux problématiques des réglementations quant aux données personnelles.

L’équipe d’account management assure la contractualisation, le suivi et l’optimisation des campagnes publicitaires. Elle participe aussi à la programmation des campagnes. Enfin le service Yield Management a pour objectif d’optimiser les stratégies de la régie et d’en développer des nouvelles en trouvant les zones d’audience dans l’environnement Orange, et de créer du CA sur ces zones, via la publicité.


Figure 4 <a id="pole"></a>: Les différents pôles et services d’Orange Advertising

### I.V L'équipe de travail <a id="lateam"></a>

Ainsi mon stage s’est déroulé dans l’équipe Ad Tech et Projets BI. Plus précisément, le stage s’est déroulé auprès de la responsable projet BI Angéline Hourlier. L’équipe en interne à Orange ne comporte que la responsable et deux ou trois stagiaires. Cependant, environ cinq développeurs de l’entreprise SOFRECOM travaillent étroitement avec l’équipe BI pour aider à la réalisation des projets. L’équipe a pour rôle de faire de l’optimisation et d’innover sur les outils BI afin de d’aider au bon fonctionnement de la régie. L’outil principal développé par l’équipe est Bird, la base de données de la régie.

La communication au sein de l’équipe et avec les autres pôles se fait principalement par skype ou via la boîte mail, chaque employé ayant une adresse mail Orange. Dans l’équipe, les documents sont partagés grâce à un SharePoint avec une organisation bien précise.

Le pôle fonctionne avec une méthode agile. C'est-à-dire que l’équipe travaille sur un sprint d’une ou deux semaines. Puis l’équipe fait trois réunions : une review pour voir ce qui a été et ce qui n’a pas été fait pendant le sprint. Une rétrospective, réunion commençant par un IceBreaker. Cela peut-être un jeu, des questions que l’on pose aux différents membres de l’équipe ou n’importe quelle autre activité qui permet de détendre l’atmosphère. Puis, chacun peut exprimer son ressentiment par rapport au sprint, s’il était plutôt positif ou négatif, ce qui l’a bloqué et ce qui l’a motivé etc… Cette réunion est animé grâce Klaxoon.


Figure 5: Exemple d’un tableau de rétrospective sur klaxoon

Enfin, l’équipe se réunit pour définir le planning du prochain sprint. Le programme du sprint est divisé en différents objectifs appelés story, elles mêmes subdivisées en tâches. Ce planning est disponible pour toute l’équipe sur l’outil Octane.


Figure 6: Capture d’écran de l’outil Octane

Chaque story doit être organisée d’une façon bien particulière. La description doit être remplie avant le début des développements. En cas d’anomalies, l’auteur doit décrire ce qui a été observé, puis ce qui est ressorti de l’analyse et comment corriger l’anomalie.

En plus de cela, pour tous les développements un cahier de tests A et B doit être rédigé, ainsi qu’un cahier de spécifications techniques. Les tests A sont effectués par le développeur, ce sont des petits tests rapides pour s’assurer du bon fonctionnement de la fonction développée. Les tests B sont effectués par quelqu’un n’ayant pas travaillé sur la story et sont plus poussés que les tests A. Ils permettent de voir si les modifications ont un impact sur d’autres tables que celles que l’on souhaitait modifier. Le cahier de spécifications techniques est là pour permettre à un autre développeur de comprendre le code et le travail effectué rapidement. Il faut réexpliquer le besoin initial, puis expliquer comment le développeur y a répondu en commentant la structure du projet et en apportant des précisions sur les parties du code les plus importantes.

L’équipe maîtrise SSMS, application grâce à laquelle ils gèrent la base de données de la régie. Elle utilise le langage java pour les développements externes à [SSMS](#sms), sur l’[IDE](#ide) netbeans. L’équipe sait aussi utiliser les technologies liées à l’API google afin de pouvoir récupérer les données de [GAM](#GAM). Les autres outils utilisés dans la régie et par conséquent sur lesquels travaillent le pôle [BI](#businessI) sont le [cube](#cube) BIRD, MDS pour entrer des données manuellement facilement, le Sharepoint et l’espace documentaire BIRD pour le partage de documents, et enfin visual studio pour la création et la modification de rapports.

### I.VI Conclusion <a id="concluprez"></a>

Orange Advertising est divisé en trois grands pôles : commercial, marketing data et communications et Opérations. C’est dans ce dernier et plus précisément dans l’équipe [BI](#businessI) que j’ai effectué mon stage.

L’équipe de travail se compose de l’équipe [BI](#businessI) d’Orange, ainsi que des développeurs externes appartenant à l’entreprise SOFRECOM. Ils travaillent tous ensemble avec la méthode agile en faisant des réunions à distance à chaque fin de sprint, et en organisant la charge de travail sur Octane.

Le monde de la publicité en ligne est en pleine expansion. Il est donc normal qu’une entreprise comme Orange, ayant une aussi grande audience sur internet, mette en place une régie publicitaire afin mettre à profit cette audience. Nous allons à présent rentrer dans le détail de ma mission au sein de cette régie.

## II. La mission <a id="miss"></a>
### II.I Le sujet <a id="suj"></a>

#### II.I.1 Le sujet présenté initialement :

Le sujet tel que présenté sur l’offre de stage était d’accompagner les différents services de la régie publicitaire afin d’améliorer l’expérience utilisateur des outils [BI](#businessI). Le travail se fait en lien avec le responsable de projets BI et le responsable de l’ADV (administration des ventes). Concrètement, cela signifie : s’assurer du bon fonctionnement des outils [BI](#businessI) en soutenant les utilisateurs, en prenant en charge les anomalies, en vérifiant la qualité de la donnée ou encore en suivant les migrations techniques. Cela consiste aussi en l’automatisation des tâches récurrentes, participer aux migrations de données, développer de nouveaux rapports, et enfin être garant de la documentation des outils Business Intelligence. Le but du stage est donc globalement d’assister le Responsable de Projet [BI](#businessI).

#### II.I.2 Les évolutions du sujet

Le sujet présenté correspond à la réalité du stage. Cependant, certaines tâches ont pris le dessus sur d’autres en termes de temps de travail. Au début du stage, j’ai beaucoup travaillé sur la base de données afin de corriger des anomalies, d'analyser la qualité des données ou d'automatiser des processus d’archivage. J’ai assez peu participé à la migration des données ou à la réalisation de rapports. Vers le milieu de mon stage, j’ai commencé à travailler sur l’automatisation de l’intégration des fichiers DELIVERY, sujet sur lequel je me suis attelée un certain temps. Il s’agit de préparer et mettre en page des fichiers construits à partir de données récupérées de l’API [GAM](#GAM) et de la base de données BIRD afin de les intégrer dans la base ou de récupérer des statistiques manquantes. Ce sujet sera détaillé plus amplement dans la suite du rapport.

#### II.I. 3 Contexte

La base de données BIRD est utilisée par tous les services de la régie pour récupérer ou archiver des données. Elle permet d’avoir un suivi sur les campagnes, par exemple récupérer un CA (chiffre d' affaire) ou un nombre d’[impressions](#impr). Cela permet de s’assurer que ce qui a été diffusé correspond à ce qui a été vendu mais aussi de faire des bilans sur l’état de la régie. Nous avons vu précédemment que l’équipe commerciale possède un sous pôle programmatique. Les campagnes vendues par ce pôle tournent essentiellement grâce à l’API [GAM](#GAM). L'administration des ventes devait récupérer les données de [GAM](#GAM) et les mettre en forme pour les préparer à l’intégration. L’automatisation de cette tâche permet de faciliter le travail de ce service et de le rendre moins répétitif. En outre, lorsque des statistiques sont manquantes pour un [IOP](#iop), lors d’une période, l’ADV devait aussi corriger ces données à la main. C’est pourquoi un système pour récupérer les données de l’API en fonction de paramètres entrés manuellement ([IOP](#iop), date de début, date de fin, identifiant de l’utilisateur), a été construit.

Sur certaines story, il a fallu que que reprendre un travail qui avait déjà fait pour une table pour l’adapter et le faire sur une autre. Cependant la majorité du travail n’avait pas été commencé. En ce qui concerne l’intégration des fichiers , le stagiaire avec qui je travaillais (Yoann Ayoub) avait déjà commencé le sujet.

Celui-ci ne comportait pas de cahier des charges précis. Le besoin a été défini initialement par le stagiaire qui était avec moi sur la mission, puis il s’est étendu au fur et à mesure des demandes du responsable [BI](#businessI) et des discussions avec l’administration des ventes.

### II.II Le planning

Vous trouverez ci-dessous le planning de mon stage. L’équipe de travail fonctionne avec une méthode agile. Ainsi, une fois toutes les deux semaines ou trois semaines des objectifs étaient définis. Il n’y avait pas de précis pour l’ensemble du stage. Le planning est donc divisé sprint par sprint.

Figure 7<a id="planning"></a> : Planning du stage

Les tâches qui n’ont pas pu être effectuées dans les temps étaient reportées au sprint suivant, ou dans un sprint dans lequel la charge de travail était plus faible. Par exemple, la story concernant l’alimentation de la table THIRD_PARTY a commencé quelques sprints plus tôt mais les tests ont été effectués un peu plus tard. Certaines story demandaient un travail supplémentaire ultérieurement. Par exemple, l’ajout du champ INIT_LOAD_DT sur le serveur de tests a été fait très tôt. Cependant, afin de corriger des erreurs, une copie du serveur de production a été faite sur les environnements de tests. Les modifications ont dû être réécrites, puis elles ont été déployées sur l’environnement de production.

Lors de mon arrivée dans l’entreprise, le sprint 35 était en cours. Je n’avais donc pas de tâches précises à faire. J’en ai profité pour installer les différents outils [BI](#businessI) et pour prendre en main l’environnement de travail. J’ai dû apprendre le fonctionnement de la base de données BIRD ainsi que toutes ses spécificitées (data warehouse, ODS etc…). Lors du premier sprint (sprint 36), les tâches qui m’ont été confiées étaient plutôt simples et redondantes. Cela m’a permis de me familiariser avec les différentes tables. À cette période, il y a aussi eu un certain nombre de formations avec les différents pôles de la régie (voir [figure 4](#pole)) ce qui m’a permis d’avoir une vision plus large sur mon travail, ainsi que sur l’utilité des différentes tables. Cela m’a aussi permis de mieux comprendre les termes utilisés car la publicité en ligne est un monde à part avec son propre vocabulaire.

Les stories sont devenues plus intéressantes à partir du sprint 37 qui a débuté fin septembre. Travailler sur la migration technique et sur les procédures stockées impliquait une véritable réflexion car je n’avais aucun travail préexistant sur lequel m’appuyer, j’ai dû construire une solution seule. En outre, le travail allait au-delà des exercices sur les bases de données que j’ai pu effectuer lors de ma scolarité à l'UTC.

À partir du sprint 38 (environ mi-octobre), j’ai véritablement commencé à travailler sur le sujet “Intégration du DELIVERY Programmatique issu de l'API [GAM](#GAM)”. Ce fut pour moi la partie la plus intéressante de mon stage car plutôt que de travailler sur des tâches isolées, il fallait construire une véritable application afin de simplifier l’utilisation des outils [BI](#businessI). Par conséquent, nous avions une vue plus globales du sujet et étions plus indépendants dessus. Cette story impliquait aussi un changement d’environnement de travail, de [SSMS](#sms) vers NetBeans, et de langage de SQL vers java.

Enfin à partir de décembre, j’ai commencé à travailler sur la story :“Intégration du DELIVERY IOP issu de l'API [GAM](#GAM)”. C’était la story sur laquelle j’étais la plus indépendante. Globalement, si on devait résumer le planning, le début du stage a été consacré à un travail [BI](#businessI) pur alors que la seconde partie comporte plus de développement.
Nous allons maintenant nous intéresser aux contributions.

### II.III Les contributions

En ce qui concerne le travail d’optimisation, d’innovation et du traitements d’anomalies sur les tables, c’est-à-dire toutes les storys qui ne concernent pas l’intégration du DELIVERY dans le [planning](#planning) ci-dessus, j’écrivais les scripts et je les testais avec l’aide de l’équipe de Tunisie SOFRECOM, puis ils s’occupaient du déploiement sur l’environnement de production. Pour ces tâches, il fallait reprendre le code déjà présent dans les bases et ajouter des modifications. La liste exacte des contributions est indiquée dans le planning.

Pour ce qui concerne l’intégration des fichiers DELIVERY, j’ai travaillé en étroite collaboration avec Yoann Ayoub. Nous avons développé un système permettant de créer des fichiers DELIVERY prêt à être intégré dans la base, à partir des données de l’API [GAM](#GAM) pour différentes instances partenaires : EBDA, PG (programmatique garantie), PD (Preferred Deals), Ad_ex (Ad Exchange), HB (Header Biding), et en manuel en fonction d’un [IOP](#iop).

Figure 8 : Liste des classes développées

Quand j’ai commencé à travailler sur le sujet, Yoann avait déjà construit une base pour EBDA. J’ai repris cette base et l’ai adapté pour générer les fichiers issus de PG, PD et Ad_Ex. J’ai aussi mis en place une classe pour permettre la suppression des lignes en doubles dans les fichiers DELIVERY générés. Puis avec l’aide de l’équipe de SOFRECOM, ce projet a été automatisé pour qu’il n’y ai plus besoin d’intervention humaine pour l’intégration de ces données venant de[GAM](#GAM), dans BIRD.

En ce qui concerne la génération manuelle des fichiers, le projet permet de récupérer des statistiques manquantes à la demande l’ADV en vue d’une réintégration. En fonction d’un [IOP](#iop), d’une date de début et d’une date de fin, on génère un fichier complet. Yoann a plus travaillé sur le sujet précédent et moi sur celui-ci. Pour cette partie, il fallait calculer le CA pour chaque ligne inscrite dans le fichier d’une façon différente en fonction du mode de vente. Yoann s’est occupé du calcul dans le cas d’une vente par [CPM](#cpm) et je me suis occupée du calcul dans le cas d’une vente au forfait et par [CPC](#cpc). Je me suis aussi occupée du plafonnement des valeurs afin que le fichier DELIVERY prenne en compte la différence entre le diffusé et le vendu. Afin de finaliser ce projet, il faudrait mettre en place une interface qui permette à l’équipe ADV de l’utiliser facilement.

### II.IV Outils et technologies

L’outil principal sur lequel j’ai travaillé est [SSMS](#sms). Il est divisé en trois serveurs : QUALIF, PPD (Pré-production) et PRD (Production). Les deux premières parties sont destinées aux tests. Les stagiaires y ont accès peuvent y effectuer des modifications et accéder aux procédures stockées. Sur QUALIF, il y a moins de données, qu’en PPD, ce qui permet d’effectuer des tests plus rapidement. Le dernier environnement est celui qui est directement utilisable par les autres services de la régie. Les mises à jour y sont déployées seulement quand tous les tests ont été fait sur les autres environnements. Les stagiaires n’y ont accès qu’en lecture.

Ainsi, lorsqu’on souhaite effectuer une modification sur BIRD, on commence par écrire les scripts. On fait ensuite les tests A et B sur QUALIF comme on l’a expliqué précédemment, puis on déploie ces modifications en PPD et en PRD.

Figure 9 : Liste des classes développées

Une fois que le choix du serveur a été fait, on a accès à une liste de base de données ([PBRDODS](#ODS), [PBRDDWH](#dwh)..) et de tables (T_FAC_DELIVERY, T_DIM_THIRD_PARTY…), ainsi qu’aux procédures stockées qui y sont liées.

Pour bien comprendre notre mission, voici un schéma simplifié détaillant le cheminements des données sur BIRD. L’alimentation des tables se fait principalement grâce aux procédures stockées.

Figure 10 : Présentation du fonctionnement de BIRD (non exhaustif)
Les données issues de BIRD vont ensuite en partie enrichir le cube qui permet des analyses plus poussées.

Par la suite, ce qui impute à l’intégration des fichiers DELIVERY était développé sur l’environnement de développement NetBeans en
java. J’ai aussi brièvement utilisé visual studio pour les rapports, et MDS et [GAM](#GAM), pour récupérer des informations. Pour la communication au sein de l'équipe, skype et teams sont utilisés lors des appels et les réunions, Outlook pour les mails. Enfin, pour partager des documents tels que les cahiers de tests ou les spécifications techniques, l'équipe utilisait Sharepoint.

### II.V Prise de recul

La partie purement [BI](#businessI) de mon travail, l’intérêt a pour intérêt de maintenir l’intégrité de BIRD, tout en y apportant des améliorations et des innovations. La majorité de mes réalisations a déjà été déployée en PRD ce qui veut dire qu’elle est déjà utile aux membres de la régie. Elle s’intègre dans le code global de la base de données. Des maintenances devront être effectuées régulièrement en cas de modifications sur une table (ajout de champ, modification du mode d’alimentation etc..).

En ce qui concerne la partie développement, la réalisation aide surtout le travail de l’administration des ventes. Grâce à ce dispositif, l’équipe n’aura plus à mettre en page les fichiers pour les préparer à l’intégration ce qui pouvait être long et répétitif.

En outre, en cas de statistiques manquantes, l’équipe aura un programme disponible qui leur permettra de générer le fichier complet en fonction de l’[IOP](#iop), d’une date de début, d’une date de fin et de l’identifiant d’un utilisateur. Une amélioration nécessaire aurait été de mettre en place une interface pour que des utilisateurs non expérimentés puissent générer ces fichiers sans avoir à modifier le code. En effet, pour le moment il faut rentrer directement les paramètres dans netbeans.

Une autre amélioration possible aurait été d’étendre les modes de ventes. Pour l’instant, la génération n’est possible que pour les ventes par forfait par[CPM](#cpm) et par [CPC](#cpc). En ce qui concerne la maintenance, pour la génération des fichiers DELIVERY, il faudra s’assurer de modifier le programme en cas de modification sur les tables DELIVERY. Il faudra aussi mettre à jour le projet en cas de modifications au niveau des partenaires ou des modes de vente. Enfin, pour le moment le projet permet la récupération des données concernant le web et le mobile séparément. À l’avenir, les données récupérées sur la session mobile (7734) devraient disparaître progressivement. Il faudra donc modifier le projet en conséquence et faire attention à toutes les autres migrations techniques qui vont arriver.

L’objectif principal du projet est l’automatisation d’un travail qui était déjà effectué par l’administration des ventes. Comme expliqué précédemment, on peut argumenter qu’il s’agissait d’un travail long et répétitif. Cependant, avec la baisse de la quantité de travail dans l’ADV, vient logiquement une baisse d’effectif. On peut alors se poser des questions légitimes sur l’impact négatif de l’automatisation pour les travailleurs. Il faudrait s’assurer d’accompagner les personnes qui travaillaient sur des tâches à présent automatisée dans leur reconversion professionnelle.

## III. Réalisations <a id="real"></a>
### III.I Introduction <a id="introreal"></a>
Comme je l’ai expliqué précédemment, mes réalisations peuvent se diviser en deux grandes parties. Le sujet sous l’angle [BI](#businessI) correspond à tout le travail que j’ai pu effectuer directement sur les bases de données afin de maintenir leur intégrité ou de les améliorer. Au départ, afin de bien prendre en main BIRD, on m’a confié des tâches d'analyse qui m’ont permis de bien comprendre l’outil et de comprendre le fonctionnement des tables et leurs interdépendances. Par la suite, j’ai pu travailler sur des storys plus concrètes d’ajout et d’améliorations. Les missions pour cette partie étant plus courtes et plus ciblées, elles ne seront pas toutes présentées pour éviter d’encombrer le rapport.

En parallèle de ces missions de [BI](#businessI), j’ai travaillé au développement de programmes venant en aide à l'administration des ventes. Dans un premier temps, j’ai travaillé sur un programme qui permettrait de faire une intégration de données automatiquement, puis sur un programme qui permettrait de remonter des statistiques manquantes.

### III.II Le sujet sous l'angle BI <a id="sujetbi"></a>

#### III.II.1 Maintien de l'intégrité de la base

##### III.II.1.1 L’analyse des contraintes manquantes au niveau du datawarehouse

Les tables du [DWH](#dwh) sont interdépendantes. Par exemple, les tables de faits possèdent des champs qui correspondent à des clés primaires de table de référentiel. Pour maintenir l’intégrité de la base de données, il aurait fallu ajouter une contrainte sur ces champs et en faire des [clés étrangères](#cléspr). Cependant c’était rarement le cas. Pour pallier à ce problème, j’ai dû rechercher ces contraintes manquantes :
Figure 11 : Exemple de recherche des contraintes manquantes

Puis faire un script afin de générer les contraintes nécessaires :

Figure 12 : Exemple de script pour l’ajout des contraintes manquantes

##### III.II.1.2 Nettoyage de la table THIRD_PARTY

Les tables `T_REF_THIRD_PARTY` et `T_DIM_THIRD_PARTY` sauvegardent les informations sur les partenaires de la régie dans[DWH](#dwh) et [DTM](#dtm) respectivement. Il s’agit donc de deux tables assez importantes dont les [clés primaires](#clés) (`THRD_PRT_ID` et `THRD_PRT_SURROGATE_KEY`) sont utilisées dans de nombreuses tables en tant que [clés étrangères](#clés). Par exemple dans une table regroupant des annonceurs (`T_REF_ANX_ADVERTISER` dans l’exemple ci-dessous), le champ qui correspond à l’ID de l’agence `(AGNC_THRD_PRT_ID` ici ) est une [clés étrangères](#clés) de `T_REF_THIRD_PARTY`.


Figure 13 : Exemple d’une table reliée à `THIRD_PARTY`

Des écarts ont été remarqués entre les valeurs présentes dans les tables `THIRD_PARTY `et les autres tables. L’objectif de cette story était de procéder à un nettoyage de ces deux tables.

Pour cela, il fallait repérer les lignes de `THIRD_PARTY `qui n’étaient dans aucune table essentielle (c’est à dire que si les valeurs de `THIRD_PARTY` ne sont pas dans au moins l’une de ces tables, elles n’ont pas d’intérêt à être dans la base). Puis de les supprimer.

Afin de repérer ces lignes, une requête SQL `SELECT` a été construite. Au départ, la requête à été construite avec la clause `NOT IN`. Cependant, la requête construite ainsi pouvait mettre plus d’une dizaine de minutes à s’exécuter. Pour pallier à ce problème, une requête avec des jointures (`LEFT JOIN`) entre les tables et la clause `WHERE` “nom de la clé étrangère” `IS NULL` a été construite. Nous avons donc construit deux requêtes comme ceci : une pour le [DWH](#dwh) et la table `T_REF_THIRD_PARTY` et une pour le [DTM](#dtm) et la table `T_DIM_THIRD_PARTY`. Ainsi, nous avons pu récupérer tous les lignes de`THIRD_PARTY` à supprimer. Par la suite, il suffisait de faire la suppression de ses partenaires dans les tables liées non essentielles, puis dans les tables `THIRD_PARTY` :

Figure 14 : Exemple de suppression des écarts dans une table liée à `THIRD_PARTY`

#### III.II.2 Ajout et amélioration dans la base

##### III.II.2.1 Amélioration de l’archivage

L’objectif de cette mission était d'automatiser la création et la suppression de tables de backup (ici il s’agit de `T_ARCH_BACK_FEES_PUBLISHER`). Pour cela, nous allons modifier la [procédure stockée](#PS) qui s’occupe de l’alimentation de cette table. Ainsi, à chaque fois que cette procédure sera lancée, l’archivage des données se fera automatiquement.


Figure 15: [procédure stockée](#PS) à modifier sur [SSMS](#sms)

Pour commencer il faut s’assurer que la création des tables de secours se fasse automatiquement. En effet, à cause des erreurs possibles lors de la modification des données d’une table, il vaut mieux garder une table de backup pendant quelques mois. Cette création de table sera mise à la fin de la [procédure stockée](#PS) afin que la table de copie possède bien la dernière version des données de la table originale.

Ces tables de secours prennent de la place dans la base de données. Ainsi, il faut s’assurer qu’après un certain laps de temps, elles soient supprimées (ici 2 mois). On va donc modifier le début de la [procédure stockée](#PS) afin qu’elle supprime automatiquement ces tables.


Figure 16 : Exemple de suppression automatique de tables dans la [procédure stockée](#PS)

Les tables ont été nommées avec un suffixe indiquant leur date de création. Ainsi, on peut parcourir les tables avec un curseur et supprimer celles dont la date de création a plus de deux mois.

Enfin, il a fallu supprimer des vieilles tables de backup qui pour certaines avaient été créées à la main. L’utilisation d’un curseur qui permet de parcourir les tables une à une, ainsi que de la fonction `LIKE` qui permet de retrouver les tables grâce à certains attributs a été un véritable gain de temps.

##### III.I.2.2) Modification de l’alimentation

La table `T_DIM_THIRD_PART`Y est alimentée à partir d’une procédure stockée de la façon suivante :
* On crée une table temporaire avec les données à ajouter.
* On supprime de la table T_DIM_THIRD_PARTY les données ayant la même clé que la table temporaire
* On ajoute les données de la table temporaire dans la table THIRD_PARTY

On souhaite modifier ce mode d’alimentation afin de l'optimiser. Pour cela on va mettre en place un MERGE qui fait un test sur l’égalité des clés des deux tables (ici THRD_PRT_SURROGATE_KEY) :

MERGE INTO [dtm].[T_DIM_THIRD_PARTY] AS DIMTHRDPRT
USING (SELECT * FROM [tmp].[T_DIM_THIRD_PARTY]) AS TMPTHRD
ON  DIMTHRDPRT.THRD_PRT_SURROGATE_KEY=TMPTHRD.THRD_PRT_SURROGATE_KEY

En cas d’égalité, on `UPDATE` les lignes correspondantes, sinon on fait un `INSERT`. On va ensuite mettre en place des tests A (c’est à dire des tests simples, uniquement sur la table concernée) comme par exemple changer une donnée dans les tables qui alimentent cette dimension (`T_REF_THIRD_PARTY` ou la table MDS `THIRD_PARTY`) et vérifier que la modification se fait correctement. Une fois les tests terminés, on va produire trois documents que l’on va mettre sur le Sharepoint afin d’expliquer notre réalisation :

* Un cahier de test A : qui retrace les tests A effectués
* Les spécifications techniques : qui explique les modifications effectuées
* Le script : l'ensemble du code écrit

Finalement, on va passer la story à un autre membre de l’équipe afin qu’il effectue les tests B (tests plus poussés s’intéresse à l’impact de la modification sur BIRD globalement).

### III.III) Le développement <a id="dev"></a>

#### III.III.1) Génération des fichiers DELIVERY en vue de l’intégration

##### III.III.1.1 Présentation générale de la génération d’un fichier DELIVERY

Forme du projet :

Lorsque j’ai commencé à travailler sur le sujet, le stagiaire qui travaillait avec moi avait déjà conçu le système permettant la génération des fichiers avec le partenaire EBDA. J’ai donc dû me familiariser avec le code avant de commencer à développer. L’existant était composé de deux classes : connexion_bdd.java et EBDA_GAM.java. Il a fallu reprendre l’existant et l’étendre aux autres partenaires en l’adaptant avec les spécificités de chacun (changer des identifiants, des noms etc…). Je me suis occupée de cette adaptation pour les partenaires Ad_Ex, PG et PD.

Finalement, le projet autour de la génération d’un fichier DELIVERY est composé de ces différentes classes :
* connexion_bdd.java
* Automatisation.java
* Adex_GAM.java
* EBDA_GAM.java
* HB_GAM.java
* Placement_HB.java
* PD.java
* PG.java
* dedoublonnage.java
* Somme_3513_7734.java
* Manual.java
* Manual_7734.java

Les trois dernières classes correspondent à un besoin différent et seront détaillées par la suite. La classe dedoublonnage.java permet de retirer les doublons après la génération d’un fichier DELIVERY et sera détaillé par la suite. Les classes EBDA_GAM.java, PD.java, HB_GAM.java (ainsi que Placement_HB.java), PG.java et Adex_GAM.java correspondent à la génération des fichiers DELIVERY pour les différents partenaires. Automatisation.java contient les fonctions nécessaires à la génération automatique des fichiers DELIVERY.
Enfin connexion_bdd.java permet la connexion à la base de données BIRD. Dans celle-ci, il y avait déjà deux connexions : une pour la PRD et une pour la PPD. Pour rappel, l’accès à la PRD nous permet de lire des données uniquement et nous effectuons les modifications en PPD.


Figure 17 : Extrait de la classe connexion_bdd.java, connexion en PRD

Par la suite, la classe connexion_bdd.java a été améliorée pour contenir toutes les fonctions généralisables à toutes les classes.

Forme générale de la génération d’un fichier DELIVERY :

Chaque classe est composée d’une fonction `main()` qui permet notamment la connexion à la session (web ou mobile) et d’une fonction `run()` qui s’occupe de la génération des fichiers avec les spécificités propres à chaque partenaire.
Voici la forme générale d’une ligne d’un fichier DELIVERY

Figure 18: Forme d’une ligne d’un fichier DELIVERY

Pour commencer la génération d’un fichier DELIVERY, on commence par faire un appel pour à la PRD pour aller chercher l’[IOP](#iop) du partenaire avec un test sur la période dans la table `T_FAC_IOP`. Puis on fait un premier appel à l’API afin d’aller chercher l’unit (clé de la table `T_REF_ANX_PLACEMENT`) et la size (taille de l’espace vendu). Grâce à ces deux valeurs on va pouvoir aller chercher src_sit_id, src_pag_id et src_pag_id. Grâce à une requête sql dans BIRD :


Figure 19: Requête permettant la création de correspondance

On va stocker les lignes ainsi crée dans un fichier correspondance comme ci-dessous :


Figure 20: Présentation des lignes du fichiers correspondances

En cas d’absence de correspondance, un mail est envoyé automatiquement à l’équipe ADV contenant la liste des couples manquants via une commande SQL :


Figure 21: Requête pour l’envoie de mail automatique

On va ensuite faire une seconde requête à l’API afin d’aller chercher cette fois-ci toutes les autres colonnes, de la date, aux clicks diffusés. À chaque requête à l’API, on doit spécifier les lignes et les colonnes que l’on souhaite récupérer :


Figure 22 : Sélection des dimensions à récupérer sur GAM

Chaque requête à l’API génère ensuite un fichier excel dans lequel on peut aller piocher les informations :


Figure 23: Fichier excel généré par la requête à l’API

Finalement voici à quoi ressemble un fichier DELIVERY classique :


Figure 24 : Exemple de fichier DELIVERY

Il possède une en-tête indiquant la date et l’heure à laquelle il a été généré, ainsi que le nombre de lignes du fichier. On peut remarquer que certaines lignes ont les mêmes valeurs, à l’exception du CA et du nombre d’[impressions](#impr). Ce sont des doublons qu’il faudra éliminer par la suite. Finalement, des tables de recap ont été mises en place afin de reprendre toutes les informations des fichiers générés. Une première table `T_RECAP_FILE` contient toutes les informations sur le fichier généré avec par exemple le nombre de lignes, le filtre ayant été appliqué pour la requête à l’API `etc.. Une seconde table de recap T_RECAP_IOP` contient les informations par [IOP](#iop) avec par exemple le CA total, le nombre d’[impressions](#impr) total etc…


Figure 25 : Extrait de la table `T_RECAP_IOP`

Afin de mettre à jour ces tables on va mettre en place un `MERGE`. Pour cela on crée une table temporaire (par exemple `[PBRDODS].[tmp].[T_RECAP_IOP_TEMP]`) dans laquelle on insert les valeurs à ajouter. Puis on compare les clés de la table temporaire et de la table recap. Si la clé existe déjà, on `UPDATE` les valeurs existantes. Sinon, on fait un `INSERT`.
Le fichier DELIVERY généré ira dans le dossier a_dedoublonner. Les dossiers liés à la génération des fichiers DELIVERY sont organisés comme ceci :


Figure 26: Organisation des dossiers du projet

Après avoir été dédoublonné, les fichiers vont dans le dossier a_dedoublonner_archive. Les fichiers générés iront dans le dossier dedoublonné. Cependant, ce n’est qu’un dossier temporaire pour les tests. Dans le cadre de l’intégration automatique, ils devront être rangés dans un autre dossier auquel les stagiaires n’ont pas accès. Le dossier Correspondance contient les correspondances dont nous avons parlé précédemment. Inexistant et Null permettent de mettre en lumière les lignes pour lesquelles les correspondances sont nulles ou inexistantes pendant les tests. Cela permet de repérer les erreurs ou les correspondances manquantes qu’il faut créer.

##### III.III.1.2 Gestion des doublons et préparation à l’intégration

Comme nous l’avons vu précédemment, le fichier généré ainsi comporte des doublons, ce qui posera des problèmes au moment de l’intégration. On considère comme doublons toutes les lignes dont toutes les premières colonnes de l’[IOP](#iop) à la date sont identiques. On souhaite donc mettre en place une classe de dédoublonnage qui fonctionnerait ainsi :


Figure 27 : Représentation du fonctionnement de la classe dedoublonnage.java

Les colonnes contenant le CA, le nombre d’[impressions](#impr) et le nombre de clicks doivent être sommées.

Afin de répondre à ce besoin, il faut commencer par parcourir les fichiers DELIVERY un à un dans le dossier a_dedoublonner. On va aller chercher l’identifiant du fichier qu’on crée en tant que `INCREMENTAL_ID` dans la table `T_RECAP_FILE` et on va nommer le fichier final avec cet identifiant et le préfixe DELIVERY_ADF_. Cela est nécessaire pour l’intégration automatique.

Comparer toutes les lignes deux à deux n’était pas envisageable pour le dédoublonnage. En effet, pour certains partenaires (Ad_exchange notamment), les fichiers peuvent avoir une centaine de milliers de lignes. De plus, les lignes en doublons ne se suivent pas forcément. Pour éviter de devoir faire jusqu’à 10 milliards de comparaison, on va donc se baser sur la date. On récupère dans un tableau toutes les lignes ayant la même date. On compare la première ligne avec les autres. Pour chaque doublon, on somme les valeurs nécessaires, et on marque le doublon. Puis on écrit la ligne dans le fichier final. On passe ensuite à la ligne suivante non marquée et on la compare à toutes les lignes non marquées.

Une fois que le fichier dédoublonné a été généré, il faut mettre à jour la table `T_RECAP_FILE` afin de remplir les champs liés au fichier dédoublonné (le nom et le nombre de lignes).

Par la suite, le reste de l’équipe a mis en place un protocole pour que l’intégration des fichiers se fasse automatiquement. Le fichier sera stocké dans un dossier RCP, un proxy a été mis en place dans chaque classe. Enfin, une application fera un test tous les jours et si des nouvelles données sont disponibles, l’intégration se fera automatiquement.

#### III.III.2 Génération des statistiques manquantes

##### III.III.2.1 Objectif et généralités

Nous avons vu précédemment que la génération des fichiers DELIVERY permettait de faire une intégration automatique dans le cadre du programmatique. Au-delà du programmatique, les fichiers DELIVERY permettent aussi de récupérer des données manquantes. Habituellement, lorsque les données ne remontent pas correctement , l’équipe ADV doit récupérer les données manuellement. Grâce à ce projet, l’ADV peut récupérer ces fichiers DELIVERY en indiquant simplement : une date de début, une date de fin, l’[IOP](#iop) et enfin l’Id_user permettant d’identifier la personne ayant intégré les valeurs dans la base.


Figure 28: Partie dynamique de la classe manual

À terme, l’objectif est que cette saisie se fasse dans un site internet plutôt que dans la classe.
Comme il ne s’agit pas d’une saisie automatique mais plutôt d’une réintégration, il est important d’aller annuler toutes les lignes qui sont déjà présentes dans la base afin d’éviter les doublons. Avant toute annulation, on s’assure d’archiver toutes les lignes de `T_FAC_DELIVERY` qui ne l’ont pas encore été. On va donc faire un INSERT dans la table `T_DEL_DELIVERY` avec un test sur le dernier mois d’archivage qu’on trouve dans la table `T_ARCH_BACK_FEES_PUBLISHER`.
Par la suite, on peut annuler les lignes :


Figure 29: Annulation des lignes dans la table `T_FAC_DELIVERY`

La génération de ces statistiques manquantes apporte de nouvelles problématiques. Par exemple le CA devra être calculé différemment en fonction des différents modes de vente : forfait, [CPC](#cpc) ou [CPM](#cpm) . On va donc aller chercher le mode de vente dans la base au début de la classe. Pour cela, il suffit de faire une requête SELECT sur la table `T_FAC_IOP` en testant sur l’[IOP](#iop) et de récupérer la colonnes correspondantes. Si le mode ne correspond pas à l’un des trois cités précédemment, on envoie un mail à l’ADV pour les prévenir.
Il a aussi été nécessaire de plafonner les valeurs en fonction des dates de ventes pour le forfait, et de la quantité vendue pour le [CPC](#cpc) et le[CPM](#cpm). Pour récupérer les dates de ventes on fait une requête similaire à celle qui permet de récupérer le mode de vente et on peut ainsi récupérer aisément le résultat :


Figure 30: Récupération des données de la base

Le filtre lors de l’appel à l’API sera différent, pour les fichiers précédent on filtrait par rapport au partenaire, dans celui-ci on filtre par rapport à l’[IOP](#iop) :


Figure 31: Mise en place du filtre sur l’[IOP](#iop)

De plus, on ne souhaite récupérer qu’une certaine période, on va donc aussi mettre en place un filtre sur la date :


Figure 32: Mise en place du filtre sur les dates

Enfin, ce projet nécessite de travailler simultanément avec les données web et mobile afin que les chiffres du CA soient exacts. Hors, on ne récupère pas le web et le mobile sur la même session. En effet le web est branché sur le 3513 et le mobile sur le 7734 :


Figure 33: Branchement au mobile

En outre, le mode de récupération des données diffère entre le web et le mobile. Pour le mobile, il faut par exemple aller chercher l’information au 5ème niveau de GAM.
Il a donc été nécessaire de créer plusieurs classes qui communiquent entre elles les informations en fonction de la session. La classe Manual.java génère le fichier en web et la classe Manual_7734.java s’occupe de la génération en mobile.

##### III.III.2.2 Le calcul par forfait

###### Le calcul :

Afin de déterminer le CA journalier lors d’une vente par forfait il faut faire le calcul suivant :

CA = (ImpressionsJour * CAMois) / ImpressionsMois

Les variables ImpressionsJour et ImpressionsMois indiquent respectivement le nombre d’[impressions](#impr) du jour et du mois en train d’être analysé. CAMois indique le CA du mois en cours et est calculé comme ceci :

CAMois =NombreJourMois *(CAVendu/NombreJourVendu)

NombreJourMois indique le nombre de jours vendus dans le mois en train d’être parcouru. Le CAVendu indique le CA total vendu pour cet [IOP](#iop) et enfin le NombreJourVendu indique le nombre total de jours vendus. Par exemple, pour l’[IOP](#iop) 163636, on a vendu 365 jours pour 12000 euros. On souhaite calculer le CA du 20 octobre. Sachant qu’en octobre on vend du 20 au 31, on obtient :

CAMois =12 *(12000/365)
CAMois = 394.5205

Si le 20, le nombre d’[impressions](#impr) diffusées est de 35 et le nombre d’[impressions](#impr) livrées sur octobre est 603, on obtient :

CA = (35*394.5205)/603
CA= 22.8992 euros

###### Récupération des données :

La première donnée à récupérer afin de faire le calcul du CA journalier est le nombre de jours vendus par mois. Pour cela, on va utiliser la classe Calendar que l’on initialise au début de la vente :


Figure 34: Initialisation du calendrier

Pour chaque mois jusqu’à la date de fin de vente, on va stocker dans une liste le nombre de jours, le mois et l’année.
Ensuite, on va faire de nouveau une requête `SELECT` avec un test sur l’[IOP](#iop) mais cette fois-ci sur la table `T_FAC_IOP_SD` afin de récupérer le champ `NET_NET_AMNT` qui correspond au CA total (CAVendu). Pour calculer le nombre de jours vendu au total, il suffit de faire la différence entre la date de début et la date de fin de la vente que l’on a récupéré précédemment. On a donc toutes les données nécessaires pour calculer le CA du mois (CAMois). Les [impressions](#impr) journalières seront récupérées ligne par ligne lors de l’appel à l’API.

La dernière donnée à récupérer est donc le nombre d’[impressions](#impr) par mois. Cette donnée est plus difficile à récupérer pour deux raisons :
- Le calcul du CA se fait en simultané avec la lecture du résultat de la requête à l’API, par conséquent on ne connaîtra pas immédiatement le nombre d’[impressions](#impr) totale du mois lors du calcul. Pour pallier à ce problème on pourrait :
1. Stocker les données de la requête API et faire le calcul après avoir récupéré le total pour chaque mois
2. Faire une nouvelle requête à l’API pour récupérer le total d’[impressions](#impr) par mois

On va plutôt se diriger vers la deuxième méthode car elle évite le stockage d’un grand nombre de données et qu’elle demande moins de tests et de modifications sur le code. Par exemple, on peut ne vouloir récupérer que les données du début du mois de mars, et donc si on ne souhaite faire qu’une seule requête à l’API, il aurait fallu faire le tri entre les données à intégrer et celles qui servent simplement pour la somme.

- La seconde difficulté vient du fait qu’on ait besoin des données du mobile afin de récupérer la somme totale des [impressions](#impr) du mois. C’est pour cela que la classe Somme_3513_7734.java a été mise en place. Il suffit ensuite de sommer les deux listes et on peut ainsi récupérer la somme totale des [impressions](#impr) par mois.

Une fois que toutes les données sont réunies, on peut commencer la génération du fichier DELIVERY.

###### Cas particuliers :

Il peut arriver qu’on récupère des valeurs pour des jours qui ne font pas partie des jours vendus, dans ce cas on met le CA et les [impressions](#impr) facturables à 0 pour ce jour-là.
Si on récupère des données pour le mois courant, on ne va pas prendre en compte ces données, ni les écrire dans le fichier DELIVERY. En effet, le nombre d’[impressions](#impr) du mois courant sera sûrement amené à augmenter et il faudra reprendre le calcul plus tard. La seule exception est dans le cas où la vente est déjà finie.

##### II.2.3 Le calcul par CPM

###### Le calcul

Le CA journalier lors d’une vente par [CPM](#cpm) se calcule de la façon suivante :

CA = CoutUnité * ImpressionsJour

La variable ImpressionsJour désigne comme précédemment le nombre d’[impressions](#impr) diffusées pour la ligne en train d’être traitée. Le coût par unité (CoutUnité) se calcule comme ceci :

CoutUnité = AMNT/QNT

Ici QNT désigne le nombre d’[impressions](#impr) vendues et AMNT le prix pour lequel elles ont été vendues.

###### Récupération des données :

La variable ImpressionsJour est récupéré lors de la lecture des données récupérées par l’API comme nous l’avons déjà vu :


Figure 35: Récupération du nombre d’[impressions](#impr)

Les données QNT et AMNT se trouvent dans la base en PRD, on peut aller les récupérer avec une requête SELECT et un test sur l’[IOP](#iop) dans la table T_FAC_IOP_SD


Figure 36: Résultat de la requête SELECT dans T_FAC_IOP_SD

Dans la photo ci-dessus `SOL_TOT_QNT` correspond à la variable QNT et `NET_NET_AMNT` à AMNT.

###### Cas particulier :

Il peut arriver que le nombre d’[impressions](#impr) diffusées surpasse le nombre vendu. C’est pour cela que dans ce cas précis on va faire la différence entre les [impressions](#impr) diffusées et les[impressions](#impr) facturables (voir figure forme d’une ligne d’un fichier DELIVERY). De même, on va plafonner le CA à ce qui a été vendu. Le vendu concerne le web et le mobile. Il faudra donc harmoniser les deux instances. La génération du fichier mobile se fait grâce un appel de la classe manual.java à la classe manual_7734.java via une fonction qui prend pour arguments :
* l’[IOP](#iop)
* La date de début du fichier généré
* La date de fin du fichier généré
* Le mode de calcul
* Le nombre d’[impressions](#impr) du web
* Le CA du web

Ainsi, on va pouvoir gérer les dépassements de la façon suivante :


Figure 37 : Harmonisation du plafonnement entre le mobile et le web

Le plafonnement fonctionne de la même façon en cas de dépassement du CA.

##### II.2.3 Le calcul par CPC

Le calcul du CA par [CPC](#cpc) fonctionne globalement de la même façon que le calcul par CPM. La principale différence est que l’on parle du coût pour un clic plutôt que du coût pour mille [impressions](#impr). On a donc

CA = CoutUnité * ClicJour

La variable ClicJour désigne le nombre de clics pour la ligne en train d’être traitée. Le coût par unité (CoutUnité) se calcule comme ceci :

CoutUnité = AMNT/QNT

Cette fois-ci QNT désigne le nombre de clics vendus et AMNT le prix pour lequel ils ont été vendus. La récupération des données est identique au calcul par CPM, on va simplement s'intéresser à la colonne `AD_SERVER_CLICKS` à la place de `AD_SERVER_IMPRESSIONS` lors de la récupération des données de l’API :


Figure 38: Récupération des [impressions](#impr) et des clics

Le plafonnement au vendu fonctionne de la même façon que pour le CPM.

### III.IV Conclusion sur les réalisations <a id="conclureal"></a>

Au travers de ces réalisations, j’ai pu travailler sur des sujets variés propre à l’univers de la BI et ainsi développer des compétences diverses. Grâce à mon travail sur les bases de données. J’ai pu comprendre en profondeur le fonctionnement d’un entrepôt de données et des outils décisionnels qui y sont liés. J’ai aussi pu participer à sa gestion : ajout de contraintes pour maintenir l’intégrité de la base, suppression d’écarts, amélioration de l’alimentation et du mode d’archivage afin d’optimiser son fonctionnement et ajout de champ dans les tables. Pour chaque modification, il ne suffisait pas de modifier la table en elle-même, il fallait aussi aller fouiller dans les [procédures stockées](#PS) qui lui sont liées afin de faire les modifications nécessaires. Cela m’a permis de réaliser la complexité d’une véritable base de données relationnelle.

En outre, mon travail concernant la génération de fichier DELIVERY m’a permis de faire du développement en parallèle de mes activités [BI](#businessI). La génération automatique des fichiers en fonction du partenaire m’a permis de consolider mes compétences en orienté objet. Enfin, étant plus indépendante sur la partie concernant la génération des statistiques manquantes. J’ai dû travailler mon autonomie afin de développer avec des concepts du monde de la publicité que je ne connaissais pas forcément comme la vente par [CPC](#cpc) ou par [CPM](#cpm). La pluralité de mes tâches m’a permis de rester intéressé de continuer d’apprendre tout au long du stage.

## Conclusion <a id="conclu"></a>

Grâce à ce stage chez Orange Advertising, j’ai pu découvrir les dynamiques de travail au sein d’une grande entreprise. Des syndicats, aux événements ou aux présentations permettant de découvrir ses différentes activités, Orange est une entreprise très active en interne. Au sein d’Orange Advertising, j’ai découvert le monde de la publicité en ligne, univers en pleine expansion dont je ne connaissais que la surface. L’informatique y est de plus en plus prépondérante : par exemple que le mode de vente va tendre de plus en plus vers le programmatique et délaisser le gré à gré. La donnée est aussi très importante dans cet univers que ce soit pour l’achat ou la revente. L’équipe de travail fonctionne avec la méthode agile et des sprint de deux ou trois semaines. À la fin de ce sprint, trois réunions étaient organisées : la review, la rétrospective et le planning du sprint suivant.

La mission au sein de l’équipe se découpe en deux parties distinctes : la [BI](#businessI) et la génération de fichiers DELIVERY. Les deux sujets ont été traités en parallèle même si la première partie du stage était plus consacrée à la [BI](#businessI) et la seconde à la génération de fichiers. La [BI](#businessI) concerne les modifications dans les bases de données afin de maintenir l’intégrité de BIRD et d’optimiser l’outil. La génération de fichier DELIVERY vient en aide à l’administration des ventes, en automatisant une intégration ou en permettant la génération de statistiques manquantes.

La partie [BI](#businessI) du stage concerne un ensemble de tâches variées : ajout de contraintes, suppression d’écarts, amélioration de l’archivage, ajout de champ ou encore modification du mode d'alimentation. Le développement en vue de la génération automatique de fichiers DELIVERY permet de récupérer des données en fonctions d’un partenaire pour la partie automatique, ou d’un [IOP](#iop) pour la partie manuelle. Cette dernière varie en fonction du mode de vente : [CPC](#cpc), [CPM](#cpm) ou forfait.

À travers ce stage, j’ai pu développer des compétences variées, allant de la découverte de la [BI](#businessI) et de l’amélioration de mes compétences en SQL, à la découverte du java et l’amélioration de mes compétences en développement orienté objet. L’équipe de travail était très compréhensive et essayait d’être disponible au maximum même si ce n’était pas toujours facile avec le travail à distance. J'ai pu découvrir l'organisation du travail dans un cadre professionnel grâce à la méthodologie agile et aux documents à placer sur le Sharepoint.

Cependant, j’ai trouvé que le cadre n’était pas toujours propice au travail collaboratif. J’ai ressenti notamment un manque car il n’y avait pas de git d’équipe ce qui rendait mon travail avec l’autre stagiaire Yoann Ayoub un peu plus compliqué. En outre, certains aspects de la publicité sur internet sont plus dérangeants. Par exemple, la quantité d’informations qu’un site peut avoir sur une personne connectée, ou simplement passant sur un site internet, est assez effrayante. Même si cela évolue notamment grâce aux lois, il faut tout de même une certaine patience pour désactiver tous les [cookies](#miamiam).

Globalement, je trouve que ce stage s’inscrit très bien dans mon parcours à l’UTC. Je souhaite continuer dans la filière intelligence artificielle et science des données et la [BI](#businessI) est très liée à cette spécialité. De plus, peu d’enseignements s’intéressent à la [BI](#businessI) à l’UTC, ce qui rend ce stage complémentaire avec mon parcours.

## Glossaire <a id="gloglo"></a>

#### [Cookies](https://www.cnil.fr/fr/definition/cookie)<a id="miamiam"></a> :
Fichier stocké sur le disque dur d'un ordinateur associé à un domaine. Il sera utilisé lors d'une connexion ultérieure à ce même domaine.

#### [BI](https://www.oracle.com/fr/database/business-intelligence-definition.html#:~:text=La%20Business%20Intelligence%20(BI)%20est,prendre%20des%20d%C3%A9cisions%20business%20%C3%A9clair%C3%A9es.) (Business Intelligence)<a id="businessI"></a> :
La Business Intelligence est l'ensemble des outils, technologies et méthodologies permettant l'aide à la décision.

#### Procédures stockées <a id="PS"></a> :
Code SQL présent dans la base qui s'exécute notamment lors de launch global et contenant toutes les instructions nécessaires au bon fonctionnement de la base de donnée : alimentation des tables, archivages etc..

#### GAM (google Ad Manager) <a id="GAM"></a> :
Plateforme d'échange d'annonce.

#### [SSMS](https://docs.microsoft.com/fr-fr/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15) (SQL Server Management Studio) <a id="sms"></a> :
Environnement intégré pour la gestion des infrastructures SQL

#### IDE (Integrated development environment) <a id="ide"></a> :
Environnement de développement facilitant la programmation

#### Cube <a id="cube"></a> :
Zone contenant des données destinée à l'analyse. Les utilisateurs peuvent y accéder via excel ou des rapports sur le Sharepoint.

#### Impression <a id="impr"></a> :
Une impression correspond à chaque fois qu'une publicité s'affiche sur un écran

#### IOP (Insertion Order Placement) <a id="iop"></a> :
Numéro concernant un type de publicité

#### CPM (Coût pour mille) <a id="cpm"></a> :
Vente en fonction du coût pour mille impressions

#### CPC (Coût par click) <a id="cpc"></a> :
Vente en fonction du coût pour un click

#### ODS <a id="ods"></a> :
Zone de traitement des données : intégration puis validation ou rejet. Les données ne sont pas persistantes.

#### DWH <a id="dwh"></a> :
Zone de stockage des données, elles y sont persistantes.

#### DTM <a id="dtm"></a> :
Contient une partie des données du DWH souvent spécialisé sur un sujet ou une fonction.

#### Clés primaire <a id="cléspr"></a> :
Données permettant d'identifier de manière unique les clés d'une table

#### Clés étrangère <a id="clés"></a> :
Identifie une colonne d'une table comme référençant une colonne d'une autre table

#### BIRD <a id="bird"></a> :
Ensemble d’outils et de bases de données utilisés par la régie

#### Story <a id="story"></a>:
Ensemble de tâches concernant un même objectif global

#### Octane <a id="octane"></a>:
Outil comportant le planning et l’ensemble des storys ainsi que les tâches qui leurs sont liées sprint par sprint

#### MERGE <a id="merge"></a>:
Fusion

#### API (Application Programming Interface) <a id="api"></a>:
Permet de connecter deux logiciels entre eux afin de faciliter l’échange de données et de fonctionnalités

#### Proxy <a id="proxy"></a>:
Intermédiaire entre le réseau privé de l’entreprise et internet (ici)

## Source <a id="source"></a>

* https://www.cnil.fr/fr/definition/cookie
visité le 27/01/2022

* https://www.oracle.com/fr/database/business-intelligence-definition.html#:~:text=La%20Business%20Intelligence%20(BI)%20est,prendre%20des%20d%C3%A9cisions%20business%20%C3%A9clair%C3%A9es
visité le 27/01/2022

* https://docs.microsoft.com/fr-fr/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15
visité le 28/01/2022

* https://fr.wikipedia.org/wiki/Orange_(entreprise)
visité le 12/12/2021

## Annexe <a id="annexe"></a>

* Annexe 1: Liste des base de données sur SSMS en PPD


* Annexe 2 : Requête pour la modification de l’alimentation de la table THIRD_PARTY

MERGE INTO [dtm].[T_DIM_THIRD_PARTY] AS DIMTHRDPRT
USING (SELECT * FROM [tmp].[T_DIM_THIRD_PARTY]) AS TMPTHRD
 
ON  DIMTHRDPRT.THRD_PRT_SURROGATE_KEY=TMPTHRD.THRD_PRT_SURROGATE_KEY
 
WHEN MATCHED THEN UPDATE SET
     
   	DIMTHRDPRT.THRD_PRT_INT_ID = TMPTHRD.THRD_PRT_INT_ID
   	   ,DIMTHRDPRT.THRD_PRT_ID = TMPTHRD.THRD_PRT_ID
   	   ,DIMTHRDPRT.COMRC_NM = TMPTHRD.COMRC_NM
   	   ,DIMTHRDPRT.LEG_NM = TMPTHRD.LEG_NM
   	   ,DIMTHRDPRT.AGNC_FLG = TMPTHRD.AGNC_FLG
   	   ,DIMTHRDPRT.ADV_FLG = TMPTHRD.ADV_FLG
   	   ,DIMTHRDPRT.PUB_FLG = TMPTHRD.PUB_FLG
   	   ,DIMTHRDPRT.CTNT_PUB_FLG = TMPTHRD.CTNT_PUB_FLG
   	   ,DIMTHRDPRT.BRK_FLG = TMPTHRD.BRK_FLG
   	   ,DIMTHRDPRT.GROUP_FLG = TMPTHRD.GROUP_FLG
   	   ,DIMTHRDPRT.FRA_TEL_GRP_FLG = TMPTHRD.FRA_TEL_GRP_FLG
   	   ,DIMTHRDPRT.OPF_FLG = TMPTHRD.OPF_FLG
   	   ,DIMTHRDPRT.LITIG_FLG = TMPTHRD.LITIG_FLG
   	   ,DIMTHRDPRT.LNK_STRT_DT = TMPTHRD.LNK_STRT_DT
   	   ,DIMTHRDPRT.LNK_END_DT = TMPTHRD.LNK_END_DT
   	   ,DIMTHRDPRT.LNK_CURRENT = TMPTHRD.LNK_CURRENT
   	   ,DIMTHRDPRT.THRD_PRT_INT_ID_NIV_1 = TMPTHRD.THRD_PRT_INT_ID_NIV_1
   	   ,DIMTHRDPRT.THRD_PRT_NM_NIV_1 = TMPTHRD.THRD_PRT_NM_NIV_1
   	   ,DIMTHRDPRT.CRE_TRT_ID = TMPTHRD.CRE_TRT_ID
   	   ,DIMTHRDPRT.CRE_DT = TMPTHRD.CRE_DT
 
 
 WHEN NOT MATCHED THEN
 INSERT
 (     THRD_PRT_SURROGATE_KEY
,THRD_PRT_INT_ID
      ,THRD_PRT_ID
      ,COMRC_NM
      ,LEG_NM
      ,AGNC_FLG
      ,ADV_FLG
      ,PUB_FLG
      ,CTNT_PUB_FLG
      ,BRK_FLG
      ,GROUP_FLG
      ,FRA_TEL_GRP_FLG
      ,OPF_FLG
      ,LITIG_FLG
      ,LNK_STRT_DT
      ,LNK_END_DT
      ,LNK_CURRENT
      ,THRD_PRT_INT_ID_NIV_1
      ,CRE_TRT_ID
      ,CRE_DT
     )
 
VALUES  (
   	TMPTHRD.THRD_PRT_SURROGATE_KEY
,TMPTHRD.THRD_PRT_INT_ID
      ,TMPTHRD.THRD_PRT_ID
      ,TMPTHRD.COMRC_NM
      ,TMPTHRD.LEG_NM
      ,TMPTHRD.AGNC_FLG
      ,TMPTHRD.ADV_FLG
      ,TMPTHRD.PUB_FLG
      ,TMPTHRD.CTNT_PUB_FLG
      ,TMPTHRD.BRK_FLG
      ,TMPTHRD.GROUP_FLG
      ,TMPTHRD.FRA_TEL_GRP_FLG
      ,TMPTHRD.OPF_FLG
      ,TMPTHRD.LITIG_FLG
      ,TMPTHRD.LNK_STRT_DT
      ,TMPTHRD.LNK_END_DT
      ,TMPTHRD.LNK_CURRENT
      ,TMPTHRD.THRD_PRT_INT_ID_NIV_1
      ,TMPTHRD.CRE_TRT_ID
      ,TMPTHRD.CRE_DT
   );