Area Statistiche
Da Webmobili Wiki.
| Area Statistiche | |
|---|---|
| http://statistics.designbest.com | |
| Ambiente di test | http://statistics.dbdemo47.com (altri dettagli) |
| Sistema di versionamento | |
| Tipo | SVN |
| URL del trunk | https://svn.office.webmobili.it/svn/WM4/DB_AreaStatistiche |
| URL base dei branch | https://svn.office.webmobili.it/svn/WM4/branches/ |
Area Statistiche è l'interfaccia che permette di controllare l'andamento di produttori e rivenditori all'interno del portale Designbest e Trovaprodotti.
Origine dei dati
Il tracciamento dei dati si basa sullo script di Google Analytics.
Ad ogni tipologia di pagina corrisponde un tracciamento (gaTrack). A loro volta i gaTrack sono divisi per i portali Designbest e Trovaprodotti.
Nella tabella sottostante c'è uno schema riassuntivo. Cliccando sul link gaTrack si ottiene l'estrazione esatta della metrica richiesta per un campione esemplare.
Query ad Analytics
Per quanto riguarda Designbest:
| URL/Tracciamento | gaTrack | Descrizione |
|---|---|---|
| /{lang}/{catalogo(lang)}/{manufacturer} | DB-catalog | Schede catalogo |
| /{lang}/{novita(lang)}/{azienda(lang)}/{manufacturer} | DB-catalog-news | Listing novità |
| /{lang}/brand/{manufacturer} | DB-brand_channel | Scheda Brand-Channel |
| Pagine dentro all'XML dei post del magazine | DB-magazineposts | Magazine Post del Produttore |
| /{lang}/{catalogo(lang)}/{manufacturer}/{ambient}/{type}/{productname}/{productid} | DB-product | Schede prodotto - Comprensive delle schede prodotto da negozio (DB-product_shop) |
| /{lang}/{catalogo(lang)}/{manufacturer}/{ambient}/{type}/{productname}/{productid}/{shopname}/{shopid} | DB-product_shop | Schede prodotto da negozio - Sottoinsieme di DB-product |
| /{lang}/{pro}/{negozi(lang)}...manufacturerFilter=... | DB-res_shop_by_catalog | Listing negozi da catalogo |
| /{lang}/pro/{showroom(lang)}/{shopname}/{shopid} | DB-shop | Schede negozio |
| /{lang}/pro/showroom/{shopname}/{shopid}/{manufacturer}/{ambient}/{type}/{productname}/{productid} | DB-shop_from_product | Scheda negozio da prodotto |
| /{lang}/pro/{showroom(lang)}/{shopname}/{shopid}/{catalogo(lang)}/{ambient} | DB-catalog_shop_amb | Ricerca negozi per ambiente |
| /{lang}/pro/{showroom(lang)}/{shopname}/{shopid}/{catalogo(lang)}/{ambient}/{type} | DB-catalog_shop_typ | Ricerca negozi per ambiente/tipologia |
| /{lang}/pro/{showroom(lang)}/{shopname}/{shopid}/{manufacturer} | DB-catalog_shop_man | Ricerca negozi per manufacturer |
| /{lang}/pro/{showroom(lang)}/{shopname}/{shopid}/{manufacturer}/{ambient} | DB-catalog_shop_man_amb | Ricerca negozi per manufacturer/ambiente |
| /{lang}/pro/{showroom(lang)}/{shopname}/{shopid}/{manufacturer}/{ambient}/{type} | DB-catalog_shop_man_typ | Ricerca negozi per manufacturer/ambiente/tipologia |
| /{lang}/pro/{showroom(lang)}/{shopname}/{shopid}/{catalogo(lang)} | DB-catalog_shop | Ricerca negozi generica (no filtri), no catalog |
| /{lang}/pro/{showroom(lang)}/{shopname}/{shopid}/{altro...} | DB-shop_listing | Ricerca negozi totale (DB-catalog_shop , DB-catalog_shop_amb , DB-catalog_shop_typ , DB-catalog_shop_man , DB-catalog_shop_man_amb , DB-catalog_shop_man_typ) |
| /{lang}/outlet/{negozi(lang)}/{shopname}/{shopid} | DB-res_offers | Ricerca Occasioni Negozio |
| /{lang}/outlet/{occasione(lang)}/{occasionename}/{occasioneid}/{shopid} | DB-offer | Scheda occasione negozio |
| /{lang}/{prodotti(lang)}/{ambient}/{type} | DB-result | Ricerca prodotti per ambiente/tipologia in questo momento non è usata dall'applicazione |
| tr: http://sitoesternoproduttore... | DB-catalog_websitevisits | Visite al sito del produttore tramite i pulsanti su Designbest |
| tr: mailtoproduttore: | DB-catalog_receivedmail | Email ricevute su Designbest dal produttore |
| tr: produttore_bottone_impression: | DB-catalog_bottoneimpression_ | Impression ricevute su Designbest dal bottone del produttore |
| tr: http://sitoesternorivenditore... | DB-shop_websitevisits | Visite al sito del rivenditore tramite i pulsanti su Designbest |
| tr: mailtonegozio: | DB-shop_receivedmail | Email ricevute su Designbest dal rivenditore |
| tr: pdfdownload: | DB-catalog_downloadedpdfs | Download dei PDF del produttore da Designbest |
| /{lang}/{catalogo(lang)}/{manufacturer} | DB-catalog_userSessions | Utenti e sessioni di un produttore su Designbest |
| /{lang}/{pro(lang)}/{showroom(lang)}/{shopname}/{shopid}/{altro...} | DB-shop_userSessions | Utenti e sessioni di un rivenditore su Designbest |
| /{lang}/{catalogo(lang)}/{manufacturer} | DB-catalog_graphAge | Età media del visitatore della scheda catalogo su Designbest |
| /{lang}/{catalogo(lang)}/{manufacturer} | DB-catalog_graphGender | Sesso medio del visitatore della scheda catalogo su Designbest |
| tr: geolocalization: | DB-catalog_graphGeolocalization | Geolocalizzazione del catalogo su Designbest |
| segment =~ /outlet | DB-outlet-sessions | Sessioni totali dell'oultet |
Per quanto riguarda Trovaprodotti:
| URL/Tracciamento | gaTrack | Descrizione |
|---|---|---|
| it/Home/Index/[IDshop] | TP-hp | Homepage |
| it/ResultAmbient/Shop_[IDshop]_Ambient_[IDAmbient]/… | TP-amb | Ricerca per ambiente |
| it/Result/Shop_[IDshop]_Ambient_[IDAmbient]_... | TP-result | Ricerca per tipologia |
| it/Product/Shop_[IDshop]_Product_[IDProduct] | TP-product | Scheda prodotto |
| it/Product/Selections/Shop_[IDshop]_Product_[IDProduct] | TP-product_selection | Scheda prodotto da selezione |
| it/ResultNews/Shop_[IDshop]_... | TP-news | Ricerca novità |
| it/Result/Offers/Shop_[IDshop]_... | TP-res_offers | Ricerca occasioni |
| it/Product/Offers/Shop_[IDshop]_Offer_[IDOffer] | TP-offer | Scheda occasione |
| it/Result/ManufacturersList/[IDshop] | TP-res_catalogs | Ricerca cataloghi |
| it/Result/Shop_[IDshop]_Manufacturer_[Idmanufacturer]/… | TP-catalog | Scheda catalogo |
| it/ResultSelection/Shop_[IDshop]_Selection_[IDSelection]/… | TP-res_selections | Ricerca selezioni |
| it/Product/Selections/Shop_[IDshop]_Product_[IDOffer] | TP-selection | Scheda prodotto di selezione |
| segment =~ /\w{2}/\w+/Offers | TP-totoffers-sessions | Sessioni totali del Trovaoccasioni |
Da Analytics al Database tramite Intruder
I seguenti dati mese per mese vengono recuperati da Analytics ed aggregati nel nostro database tramite il programma Designbest Statistics Intruder.
Intruder , mese per mese, permette di eseguire l'inserimento dei dati tramite i seguenti task modulari indipendenti:
- A visite, impression - manufacturer
- B utenze, sessioni - manufacturer
- C grafici genere, età, geoloc - manufacturer
- D visite ai post magazine - manufacturer
- E schede negozio - negozi
- F utenze, sessioni - negozi
- G schede occasione - occasioni
- H prodotti, cataloghi visti - trovaprodotti
- I ambienti visti - trovaprodotti
- L schede negozio - trovaprodotti
- M schede occasione - trovaprodotti
- N schede prodotto - prodotti
- O schede prodotto da negozio - prodotti
- P applicazione moltiplicatore
all'interno di questa nostra struttura dati:
| STS_ManufacturerByDevice | ||
|---|---|---|
| K | ManufacturerID | |
| K | ZoneID | |
| K | DeviceType | |
| K | Data | |
| B | Users | DB-catalog_userSessions |
| B | Sessions | DB-catalog_userSessions |
| A | Impressions | fonte: DFP + DB-catalog_bottoneimpression |
| A | WebsiteVisits | DB-catalog_websitevisits + Click al banner DFP |
| A | DownloadedPDFs | DB-catalog_downloadedpdfs |
| A | ReceivedEmails | DB-catalog_receivedmail |
| A | CatalogPageViews | DB-catalog +DB-catalog-news + DB-brand_channel + DB-res_shop_by_catalog + DB-catalog_shop_man |
| STS_ManufacturerOtherImpressions | ||
|---|---|---|
| K | ManufacturerID | |
| K | ZoneID | |
| K | DeviceType | |
| K | Contract | |
| K | Data | |
| A | Impressions | fonte: DFP |
| A | WebsiteVisits | fonte: DFP |
| STS_ManufacturerByAgeBracket | ||
|---|---|---|
| K | ManufacturerID | |
| K | AgeBracket | |
| K | Data | |
| C | PageViews | DB-catalog_graphAge |
| STS_ManufacturerByGender | ||
|---|---|---|
| K | ManufacturerID | |
| K | Gender | |
| K | Data | |
| C | PageViews | DB-catalog_graphGender |
| STS_ManufacturerByGEO | ||
|---|---|---|
| K | ManufacturerID | |
| K | Geolocalization | |
| K | Data | |
| C | PageViews | DB-catalog_graphGeolocalization |
| STS_MagazinePostsByManufacturer | ||
|---|---|---|
| K | ArticleID | |
| K | ManufacturerID | |
| K | ZoneID | |
| K | DeviceType | |
| K | Data | |
| D | PageViews | DB-magazineposts |
| STS_ShopByDevice | ||
|---|---|---|
| K | ShopID | |
| K | ZoneID | |
| K | DeviceType | |
| K | Data | |
| Impressions | non tracciato | |
| E | WebsiteVisits | DB-shop_websitevisits |
| E | ReceivedEmails | DB-shop_receivedmail |
| E | CatalogPageViews | DB-shop + DB-shop_listing |
| E | CatalogOffersPageViews | DB-res_offers |
| STS_ShopBySessions | ||
|---|---|---|
| K | ShopID | |
| K | DeviceType | |
| K | Data | |
| F | Users | DB-shop_userSessions |
| F | Sessions | DB-shop_userSessions |
| STS_TpOfferByDevice | ||
|---|---|---|
| K | OfferID | |
| K | ShopID | |
| K | ZoneID | |
| K | DeviceType | |
| K | Data | |
| G | PageViews | TP-offer |
| STS_ProductByTpByDevice | ||
|---|---|---|
| K | ProductID | |
| K | TpID | |
| K | ZoneID | |
| K | DeviceType | |
| K | Data | |
| H | PageViews | TP-product + TP-product_selection |
| STS_ManufacturerByTP | ||
|---|---|---|
| K | ManufacturerID | |
| K | TpID | |
| K | ZoneID | |
| K | DeviceType | |
| K | Data | |
| H | PageViews | TP-catalog |
| STS_TPbyAmbient | ||
|---|---|---|
| K | TpID | |
| K | AmbientID | |
| K | ZoneID | |
| K | DeviceType | |
| K | Data | |
| I | PageViews | TP-amb + TP-result |
| STS_TpByDevice | ||
|---|---|---|
| K | TpID | |
| K | ZoneID | |
| K | DeviceType | |
| K | Data | |
| L | ReceivedEmails | fonte: tabella STS_Email di designbest |
| L | CatalogPageViews | TP-hp + TP-news + TP-res_catalogs + TP-res_selections |
| L | CatalogOffersPageViews | TP-res_offers |
| L | Entrances | TP-shop_entrances |
| STS_TpOfferByDevice | ||
|---|---|---|
| K | OfferID | |
| K | ShopID | |
| K | DeviceType | |
| K | Data | |
| M | PageViews | TP-offer |
| STS_OutletOfferByDevice | ||
|---|---|---|
| K | OfferID | |
| K | ShopID | |
| K | ZoneID | |
| K | DeviceType | |
| K | Data | |
| M | PageViews | DB-offer |
| STS_ProductByDevice | ||
|---|---|---|
| K | ProductID | |
| K | ZoneID | |
| K | DeviceType | |
| K | Data | |
| N, P | PageViews | DB-product |
| STS_ProductByShopByDevice | ||
|---|---|---|
| K | ProductID | |
| K | ShopID | |
| K | ZoneID | |
| K | DeviceType | |
| K | Data | |
| O, P | PageViews | DB-product_shop |
| STS_OutletShopSessions | ||
|---|---|---|
| K | ShopID | |
| K | ZoneID | |
| K | Data | |
| G | Sessions | algoritmo Cydrolous |
| STS_TpTotalOffersSessions | ||
|---|---|---|
| K | ShopID | |
| K | ZoneID | |
| K | Data | |
| M | Sessions | algoritmo Cydrolous |
Algoritmo Cydrolous
Il calcolo delle sessioni di Trovaoccasioni e di Outlet Design avviene tramite un algoritmo detto Cydrolous.
In breve i passaggi
Sessioni su Outlet Design
outletTotalSessions=SOMMA DB-outlet-sessions
La somma dei valori restituiti dalla query analytics fatta su tutto l'outlet (e tutti i negozi) nel periodo specificato
outletTotalPageViews=SOMMA Pagine Viste Totali Outlet
Un'estrazione del tipo SELECT SUM(PageViews) FROM ... su tutto l'outlet (e tutti i negozi) nel periodo specificato
totalPageViewsPerSession=outletTotalPageViews/outletTotalSessions
Parziale intermedio
shopOutletSessions=shopOutletTotalPageViews/totalPageViewsPerSession
Le sessioni di un singolo negozio sono le sue pagine viste totali diviso il parziale
Sessioni su Trovaoccasioni
offersTotalSessions=SOMMA TP-totoffers-sessions
La somma dei valori restituiti dalla query analytics fatta su tutto il Trovaprodotti (e tutti i negozi) nel periodo specificato
offersTotalPageViews=SOMMA Pagine Viste Totali Occasioni Trovaprodotti
Un'estrazione del tipo SELECT SUM(PageViews) FROM ... su tutto il Trovaprodotti (e tutti i negozi) nel periodo specificato
totalPageViewsPerSession=offersTotalPageViews/offersTotalSessions
Parziale intermedio
shopOffersSessions=shopOffersTotalPageViews/totalPageViewsPerSession
Le sessioni di un singolo negozio sono le sue pagine viste totali diviso il parziale
Area Produttori
Sintesi
Tabellino riassuntivo
I dati vengono recuperati tramite la stored procedure MAN_Recap
| Utenti unici | STS_ManufacturerByDevice.Users |
| Visite | STS_ManufacturerByDevice.Sessions |
| Pagine viste | STS_ManufacturerByDevice.CatalogPageViews + STS_ManufacturerByTP.PageViews + STS_ProductByDevice.PageViews + STS_ProductByTpByDevice.PageViews |
| Impression ADV | STS_ManufacturerByDevice.Impressions |
| Visite al sito | STS_ManufacturerByDevice.WebsiteVisits |
| Download cataloghi pdf | STS_ManufacturerByDevice.DownloadedPDFs |
| Email ricevute | STS_ManufacturerByDevice.ReceivedEmails |
Grafici
| Fasce d'età | STS_ManufacturerByAgeBracket.PageViews |
| Sesso | STS_ManufacturerByGender.PageViews |
| Dispositivi | STS_ManufacturerByDevice.CatalogPageViews |
| Geolocalizzazione (nascosto) | STS_ManufacturerByGEO.PageViews |
Prodotti
Tabellino riassuntivo
I dati sono restituiti dalla stored procedure MAN_TOTs
| Pagine catalogo viste | STS_ManufacturerByDevice.CatalogPageViews + STS_ManufacturerByTP.PageViews |
| Schede prodotto viste | STS_ProductByDevice.PageViews + STS_ProductByTpByDevice.PageViews |
Elenco top 50 prodotti
I dati sono restituiti dalla stored procedure MAN_Products
| Prodotto pagine viste | STS_ProductByDevice.PageViews + STS_ProductByTpByDevice.PageViews |
Distribuzione
Si tratta dell'elenco dei rivenditori in ordine alfabetico associati alla rete di produttori.
Advertising
Impression da contratto
| Impression Banner | STS_ManufacturerByDevice.Impressions |
| Visite al sito | STS_ManufacturerByDevice.WebsiteVisits |
Impression extra contratto
| Impression Banner | STS_ManufacturerOtherImpressions.Impressions |
| Visite al sito | STS_ManufacturerOtherImpressions.WebsiteVisits |
Magazine (step 2)
I dati vengono recuperati tramite la stored procedure MAN_MagazinePosts
| Articolo pageViews | STS_MagazinePostsByManufacturer.PageViews |
Social (step 2)
Elenco totale dei post su Facebook in ordine dal più recente del produttore.
Newsletter (step 2)
Area in disuso.
Area Negozi
Sintesi
Tabellino riassuntivo
I dati vengono recuperati tramite la stored procedure SHOP_Recap
Traffico per canale
Grafico
| Dispositivi | STS_ShopByDevice.CatalogPageViews + STS_ShopByDevice.CatalogOffersPageViews + STS_TpByDevice.CatalogPageViews + STS_TpByDevice.CatalogOffersPageViews |
Prodotti
Tabellino riassuntivo *
| Pagine catalogo viste | STS_ShopByDevice.CatalogPageViews + STS_TpByDevice.CatalogPageViews |
| Schede prodotto viste | STS_ProductByShopByDevice.PageViews + STS_ProductByTpByDevice.PageViews + STS_TPbyAmbient.PageViews + STS_ManufacturerByTP.PageViews |
* ATTENZIONE
Il valore Totale pagine viste è privo di Outlet Design e di Trovaoccasioni.
In pratica mancano STS_ShopByDevice.CatalogOffersPageViews + STS_OutletOfferByDevice.PageViews + STS_TpByDevice.CatalogOffersPageViews + STS_TpOfferByDevice.PageViews
Prodotti
I dati sono restituiti dalla stored procedure SHOP_NET_Products
| Prodotto pagine viste | STS_ProductByShopByDevice.PageViews + STS_ProductByTpByDevice.PageViews |
Marche
I dati sono restituiti dalla stored procedure SHOP_NET_Manufacturers
| Produttore pagine viste | STS_ProductByShopByDevice.PageViews + STS_ProductByTpByDevice.PageViews |
Aree Casa
I dati sono restituiti dalla stored procedure SHO_NET_Ambients
| Ambiente pagine viste | STS_ProductByShopByDevice.PageViews + STS_ProductByTpByDevice.PageViews |
Outlet design (step 2)
Tabellino riassuntivo
| Pagine catalogo viste | STS_ShopByDevice.CatalogOffersPageViews + STS_TpByDevice.CatalogOffersPageViews |
| Schede prodotto viste | STS_OutletOfferByDevice.PageViews + STS_TpOfferByDevice.PageViews |
Occasioni
I dati sono restituiti dalla stored procedure SHOP_NET_Offers
| Occasione pagine viste | STS_OutletOfferByDevice.PageViews + STS_TpOfferByDevice.PageViews |
Contatti (step 2)
Elenco di tutte le email ricevute (query al database alla STS_Email)
Sottoprogetti
Maialate
I numeri a partire da agosto 2016 sono alterati da moltiplicatori definiti nella tabella STS_AlterStats che vanno ad applicarsi, durante la generazione, a tutti i numeri riferiti alle schede prodotto viste sul portale Designbest (no Trovaprodotti) mese per mese.
I campi coinvolti da questa alterazione sono STS_ProductByDevice.PageViews e STS_ProductByShopByDevice.PageViews.
L'unico sistema per aggiungere nuovi moltiplicatori è l'inserimento manuale del valore all'interno del database nella suddetta tabella.
Qui un riassunto dei valori presenti ad oggi:
| MESE | MOLTIPLICATORE |
|---|---|
| Agosto 2016 | 2.4 |
| Settembre 2016 | 3.0 |
| Ottobre 2016 | 2.0 |
| Novembre 2016 | 1.8 |
| Dicembre 2016 | 1.8 |
| Gennaio 2017 | 2.1 |
| Febbraio 2017 | 1.6 |
| Marzo 2017 | 1.6 |
| Aprile 2017 | 1.4 |
| Maggio 2017 | 1.4 |
| Giugno 2017 | 1.4 |
| Luglio 2017 | 1.4 |
| Agosto 2017 | 1.4 |
| Settembre 2017 | 1.6 |
| Ottobre 2017 | 1.6 |
| Novembre 2017 | 1.8 |
| Dicembre 2017 | 1.6 |
| Gennaio 2018 | 1.8 |
| Febbraio 2018 | 1.8 |
| Marzo 2018 | 1.9 |
| Aprile 2018 | 2.1 |
| Maggio 2018 | 1.9 |
| Giugno 2018 | 1.4 |
| Luglio 2018 | 1.4 |
| Agosto 2018 | 1.4 |
| Settembre 2018 | 1.6 |
| Ottobre 2018 | 1.6 |
| Novembre 2018 | 1.5 |
| Dicembre 2018 | 1.7 |
| Gennaio 2019 | 1.6 |
| Febbraio 2019 | 1.6 |
| Marzo 2019 | 1.6 |
| Aprile 2019 | 1.9 |
| Maggio 2019 | 1.8 |
| Giugno 2019 | 1.7 |
| Luglio 2019 | 1.5 |
| Agosto 2019 | 1.6 |
| Settembre 2019 | 1.6 |
| Ottobre 2019 | 1.8 |
| Novembre 2019 | 1.8 |
| Dicembre 2019 | 1.9 |
| Gennaio 2020 | 1.9 |
| Febbraio 2020 | 1.8 |
| Marzo 2020 | 1.6 |
| Aprile 2020 | 1.9 |
| Maggio 2020 | 1.6 |
| Giugno 2020 | / |
| Luglio 2020 | 1.3 |
| Agosto 2020 | 1.3 |
| Settembre 2020 | 1.5 |
| Ottobre 2020 | 1.3 |
| Novembre 2020 | 1.3 |
| Dicembre 2020 | 1.5 |
| Gennaio 2021 | 1.7 |
| Febbraio 2021 | 1.7 |
Nella parte del Trovaprodotti i valori etichettati come sessioni in realtà sono gli accessi.
Sono stati alterati manualmente manualmente i mesi di ottobre, novembre, dicembre 2016 in zona 1 e device: desktop
incrementando il campo STS_ManufacturerByDevice.WebsiteVisits di alcuni produttori elitari a seguito di una campagna Facebook.
Lo storico si trova qui https://cloud.designbest.com/index.php/s/kAyRQh8R7iDFfDW
Nel mese di Aprile 2017 sono stati fatti casini con DFP mobile del produttore Geberit. Le Impression a zero non si potevano lasciare, così sono stati inseriti manualmente dei valori di media con questa query:
UPDATE STS_ManufacturerByDevice
SET Impressions = 3201, WebsiteVisits = 13
WHERE ManufacturerID = 4095 AND MONTH(Data) = 4 AND YEAR(Data) = 2017 AND ZoneID = 1 AND DeviceType = 'mobile'
Nel mese di Giugno 2017 sono stati fatti casini con DFP dei produttori Eclisse, Geberit e Oluce. Sono stati inseriti manualmente i seguenti click al sito:
-- Eclisse
UPDATE STS_ManufacturerByDevice
SET WebsiteVisits += 317
WHERE ManufacturerID = 2216 AND DeviceType = 'desktop'
AND ZoneID = 1 AND Data = '2017-06-01'
UPDATE STS_ManufacturerByDevice
SET WebsiteVisits += 205
WHERE ManufacturerID = 2216 AND DeviceType = 'mobile'
AND ZoneID = 1 AND Data = '2017-06-01'
-- Geberit
UPDATE STS_ManufacturerByDevice
SET WebsiteVisits += 177
WHERE ManufacturerID = 4095 AND DeviceType = 'desktop'
AND ZoneID = 1 AND Data = '2017-06-01'
UPDATE STS_ManufacturerByDevice
SET WebsiteVisits += 78
WHERE ManufacturerID = 4095 AND DeviceType = 'mobile'
AND ZoneID = 1 AND Data = '2017-06-01'
-- Oluce
UPDATE STS_ManufacturerByDevice
SET WebsiteVisits += 59
WHERE ManufacturerID = 1481 AND DeviceType = 'desktop'
AND ZoneID = 1 AND Data = '2017-06-01'
UPDATE STS_ManufacturerByDevice
SET WebsiteVisits += 32
WHERE ManufacturerID = 1481 AND DeviceType = 'mobile'
AND ZoneID = 1 AND Data = '2017-06-01'
Nel mese di febbraio 2018 ci siamo resi conto che la parte commerciale in passato ha spedito dei report ulteriormente alterati a 2 aziende in particolare: Unopiù e Martinelli Luce. E' emerso che se per caso le aziende entrassero nella propria area statistiche vedrebbero l'incoerenza dei dati fornita precedentemente e di conseguenza potrebbero disdire il contratto. Per rimediare non ci resta che alterare i dati di tutti i loro prodotti calcolando un coefficiente moltiplicativo
/**
Moltiplichiamo per un certo coefficiente tutti i dati delle statistiche di uno sponsor per far
quadrare i conti con i report "gonfiati" che precedentemente sono stati consegnati a loro.
*/
USE [_Test_DBStats]
-- UNOPIU
DECLARE @ProdMultiplier DECIMAL(5,3) = 1.51
DECLARE @CatMultiplier DECIMAL(5,3) = 1.2
DECLARE @ManufacturerID INT = 4114 -- Unopiù
DECLARE @StartDate DATE = '2016-12-01'
DECLARE @EndDate DATE = '2018-01-01'
BEGIN TRANSACTION
EXEC MAN_Recap @ManufacturerID, @StartDate, @EndDate,0
UPDATE STS_ProductByDevice SET STS_ProductByDevice.PageViews = ROUND(STS_ProductByDevice.PageViews*@ProdMultiplier,0)
--SELECT STS_ProductByDevice.PageViews, CEILING(STS_ProductByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByDevice
INNER JOIN ProductsFlatData ON STS_ProductByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ProductByShopByDevice SET STS_ProductByShopByDevice.PageViews = ROUND(STS_ProductByShopByDevice.PageViews*@ProdMultiplier,0)
--SELECT STS_ProductByShopByDevice.PageViews, CEILING(STS_ProductByShopByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByShopByDevice
INNER JOIN ProductsFlatData ON STS_ProductByShopByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByShopByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ManufacturerByDevice SET CatalogPageViews = ROUND(CatalogPageViews*@CatMultiplier,0)
--SELECT CatalogPageViews, CEILING(CatalogPageViews*@CatMultiplier) AS SuperCatalogPageViews
FROM STS_ManufacturerByDevice
WHERE STS_ManufacturerByDevice.ManufacturerID = @ManufacturerID AND STS_ManufacturerByDevice.Data BETWEEN @StartDate AND @EndDate
--------------------------------------------------------------------------------------------------------------
---------------------------------- TROVAPRODOTTI -------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
UPDATE STS_ProductByTpByDevice SET STS_ProductByTpByDevice.PageViews = ROUND(STS_ProductByTpByDevice.PageViews*@ProdMultiplier,0)
--SELECT STS_ProductByTpByDevice.PageViews, CEILING(STS_ProductByTpByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByTpByDevice
INNER JOIN ProductsFlatData ON STS_ProductByTpByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByTpByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ManufacturerByTP SET PageViews = ROUND(PageViews*@CatMultiplier,0)
--SELECT PageViews, CEILING(PageViews*@CatMultiplier) AS SuperCatalogPageViews
FROM STS_ManufacturerByTP
WHERE STS_ManufacturerByTP.ManufacturerID = @ManufacturerID AND STS_ManufacturerByTP.Data BETWEEN @StartDate AND @EndDate
EXEC MAN_Recap @ManufacturerID, @StartDate, @EndDate,0
COMMIT TRANSACTION
-- MARTINELLI LUCE
SET @ProdMultiplier = 1.28
SET @CatMultiplier = 1
SET @ManufacturerID = 1456 -- Martinelli Luce
BEGIN TRANSACTION
EXEC MAN_Recap @ManufacturerID, @StartDate, @EndDate,0
UPDATE STS_ProductByDevice SET STS_ProductByDevice.PageViews = CEILING(STS_ProductByDevice.PageViews*@ProdMultiplier)
--SELECT STS_ProductByDevice.PageViews, CEILING(STS_ProductByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByDevice
INNER JOIN ProductsFlatData ON STS_ProductByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ProductByShopByDevice SET STS_ProductByShopByDevice.PageViews = CEILING(STS_ProductByShopByDevice.PageViews*@ProdMultiplier)
--SELECT STS_ProductByShopByDevice.PageViews, CEILING(STS_ProductByShopByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByShopByDevice
INNER JOIN ProductsFlatData ON STS_ProductByShopByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByShopByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ManufacturerByDevice SET CatalogPageViews = CEILING(CatalogPageViews*@CatMultiplier)
--SELECT CatalogPageViews, CEILING(CatalogPageViews*@CatMultiplier) AS SuperCatalogPageViews
FROM STS_ManufacturerByDevice
WHERE STS_ManufacturerByDevice.ManufacturerID = @ManufacturerID AND STS_ManufacturerByDevice.Data BETWEEN @StartDate AND @EndDate
--------------------------------------------------------------------------------------------------------------
---------------------------------- TROVAPRODOTTI -------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
UPDATE STS_ProductByTpByDevice SET STS_ProductByTpByDevice.PageViews = CEILING(STS_ProductByTpByDevice.PageViews*@ProdMultiplier)
--SELECT STS_ProductByTpByDevice.PageViews, CEILING(STS_ProductByTpByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByTpByDevice
INNER JOIN ProductsFlatData ON STS_ProductByTpByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByTpByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ManufacturerByTP SET PageViews = CEILING(PageViews*@CatMultiplier)
--SELECT PageViews, CEILING(PageViews*@CatMultiplier) AS SuperCatalogPageViews
FROM STS_ManufacturerByTP
WHERE STS_ManufacturerByTP.ManufacturerID = @ManufacturerID AND STS_ManufacturerByTP.Data BETWEEN @StartDate AND @EndDate
EXEC MAN_Recap @ManufacturerID, @StartDate, @EndDate,0
COMMIT TRANSACTION
Per lo stesso problema del mese scorso anche Agape ha dovuto subire il taroccamento perché i report fossero coerenti. Questo nel mese di Marzo 2018
/**
Moltiplichiamo per un certo coefficiente tutti i dati delle statistiche di uno sponsor per far
quadrare i conti con i report "gonfiati" che precedentemente sono stati consegnati a loro.
*/
USE [_Test_DBStats]
-- AGAPE
DECLARE @ProdMultiplier DECIMAL(5,3) = 1.65
DECLARE @CatMultiplier DECIMAL(5,3) = 1.65
DECLARE @ManufacturerID INT = 28 -- Agape
DECLARE @StartDate DATE = '2016-01-01'
DECLARE @EndDate DATE = '2018-02-01'
BEGIN TRANSACTION
EXEC MAN_Recap @ManufacturerID, @StartDate, @EndDate,0
UPDATE STS_ProductByDevice SET STS_ProductByDevice.PageViews = ROUND(STS_ProductByDevice.PageViews*@ProdMultiplier,0)
--SELECT STS_ProductByDevice.PageViews, CEILING(STS_ProductByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByDevice
INNER JOIN ProductsFlatData ON STS_ProductByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ProductByShopByDevice SET STS_ProductByShopByDevice.PageViews = ROUND(STS_ProductByShopByDevice.PageViews*@ProdMultiplier,0)
--SELECT STS_ProductByShopByDevice.PageViews, CEILING(STS_ProductByShopByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByShopByDevice
INNER JOIN ProductsFlatData ON STS_ProductByShopByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByShopByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ManufacturerByDevice SET CatalogPageViews = ROUND(CatalogPageViews*@CatMultiplier,0)
--SELECT CatalogPageViews, CEILING(CatalogPageViews*@CatMultiplier) AS SuperCatalogPageViews
FROM STS_ManufacturerByDevice
WHERE STS_ManufacturerByDevice.ManufacturerID = @ManufacturerID AND STS_ManufacturerByDevice.Data BETWEEN @StartDate AND @EndDate
--------------------------------------------------------------------------------------------------------------
---------------------------------- TROVAPRODOTTI -------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
UPDATE STS_ProductByTpByDevice SET STS_ProductByTpByDevice.PageViews = ROUND(STS_ProductByTpByDevice.PageViews*@ProdMultiplier,0)
--SELECT STS_ProductByTpByDevice.PageViews, CEILING(STS_ProductByTpByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByTpByDevice
INNER JOIN ProductsFlatData ON STS_ProductByTpByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByTpByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ManufacturerByTP SET PageViews = ROUND(PageViews*@CatMultiplier,0)
--SELECT PageViews, CEILING(PageViews*@CatMultiplier) AS SuperCatalogPageViews
FROM STS_ManufacturerByTP
WHERE STS_ManufacturerByTP.ManufacturerID = @ManufacturerID AND STS_ManufacturerByTP.Data BETWEEN @StartDate AND @EndDate
EXEC MAN_Recap @ManufacturerID, @StartDate, @EndDate,0
COMMIT TRANSACTION
Per lo stesso problema del mese scorso anche Alias e Magis hanno dovuto subire il taroccamento perché i report fossero coerenti. Questo nel mese di Aprile 2018
/**
Moltiplichiamo per un certo coefficiente tutti i dati delle statistiche di uno sponsor per far
quadrare i conti con i report "gonfiati" che precedentemente sono stati consegnati a loro.
*/
USE [_Test_DBStats]
-- ALIAS
DECLARE @ProdMultiplier DECIMAL(5,3) = 1.8
DECLARE @CatMultiplier DECIMAL(5,3) = 1.8
DECLARE @ManufacturerID INT = 34 -- ALIAS
DECLARE @StartDate DATE = '2010-01-01'
DECLARE @EndDate DATE = '2018-03-31'
BEGIN TRANSACTION
EXEC MAN_Recap @ManufacturerID, @StartDate, @EndDate,0
UPDATE STS_ProductByDevice SET STS_ProductByDevice.PageViews = ROUND(STS_ProductByDevice.PageViews*@ProdMultiplier,0)
--SELECT STS_ProductByDevice.PageViews, CEILING(STS_ProductByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByDevice
INNER JOIN ProductsFlatData ON STS_ProductByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ProductByShopByDevice SET STS_ProductByShopByDevice.PageViews = ROUND(STS_ProductByShopByDevice.PageViews*@ProdMultiplier,0)
--SELECT STS_ProductByShopByDevice.PageViews, CEILING(STS_ProductByShopByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByShopByDevice
INNER JOIN ProductsFlatData ON STS_ProductByShopByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByShopByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ManufacturerByDevice SET CatalogPageViews = ROUND(CatalogPageViews*@CatMultiplier,0)
--SELECT CatalogPageViews, CEILING(CatalogPageViews*@CatMultiplier) AS SuperCatalogPageViews
FROM STS_ManufacturerByDevice
WHERE STS_ManufacturerByDevice.ManufacturerID = @ManufacturerID AND STS_ManufacturerByDevice.Data BETWEEN @StartDate AND @EndDate
--------------------------------------------------------------------------------------------------------------
---------------------------------- TROVAPRODOTTI -------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
UPDATE STS_ProductByTpByDevice SET STS_ProductByTpByDevice.PageViews = ROUND(STS_ProductByTpByDevice.PageViews*@ProdMultiplier,0)
--SELECT STS_ProductByTpByDevice.PageViews, CEILING(STS_ProductByTpByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByTpByDevice
INNER JOIN ProductsFlatData ON STS_ProductByTpByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByTpByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ManufacturerByTP SET PageViews = ROUND(PageViews*@CatMultiplier,0)
--SELECT PageViews, CEILING(PageViews*@CatMultiplier) AS SuperCatalogPageViews
FROM STS_ManufacturerByTP
WHERE STS_ManufacturerByTP.ManufacturerID = @ManufacturerID AND STS_ManufacturerByTP.Data BETWEEN @StartDate AND @EndDate
EXEC MAN_Recap @ManufacturerID, @StartDate, @EndDate,0
COMMIT TRANSACTION
-- MAGIS
SET @ProdMultiplier = 2.1
SET @CatMultiplier = 2.1
SET @ManufacturerID = 313 -- MAGIS
BEGIN TRANSACTION
EXEC MAN_Recap @ManufacturerID, @StartDate, @EndDate,0
UPDATE STS_ProductByDevice SET STS_ProductByDevice.PageViews = CEILING(STS_ProductByDevice.PageViews*@ProdMultiplier)
--SELECT STS_ProductByDevice.PageViews, CEILING(STS_ProductByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByDevice
INNER JOIN ProductsFlatData ON STS_ProductByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ProductByShopByDevice SET STS_ProductByShopByDevice.PageViews = CEILING(STS_ProductByShopByDevice.PageViews*@ProdMultiplier)
--SELECT STS_ProductByShopByDevice.PageViews, CEILING(STS_ProductByShopByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByShopByDevice
INNER JOIN ProductsFlatData ON STS_ProductByShopByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByShopByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ManufacturerByDevice SET CatalogPageViews = CEILING(CatalogPageViews*@CatMultiplier)
--SELECT CatalogPageViews, CEILING(CatalogPageViews*@CatMultiplier) AS SuperCatalogPageViews
FROM STS_ManufacturerByDevice
WHERE STS_ManufacturerByDevice.ManufacturerID = @ManufacturerID AND STS_ManufacturerByDevice.Data BETWEEN @StartDate AND @EndDate
--------------------------------------------------------------------------------------------------------------
---------------------------------- TROVAPRODOTTI -------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
UPDATE STS_ProductByTpByDevice SET STS_ProductByTpByDevice.PageViews = CEILING(STS_ProductByTpByDevice.PageViews*@ProdMultiplier)
--SELECT STS_ProductByTpByDevice.PageViews, CEILING(STS_ProductByTpByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByTpByDevice
INNER JOIN ProductsFlatData ON STS_ProductByTpByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByTpByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ManufacturerByTP SET PageViews = CEILING(PageViews*@CatMultiplier)
--SELECT PageViews, CEILING(PageViews*@CatMultiplier) AS SuperCatalogPageViews
FROM STS_ManufacturerByTP
WHERE STS_ManufacturerByTP.ManufacturerID = @ManufacturerID AND STS_ManufacturerByTP.Data BETWEEN @StartDate AND @EndDate
EXEC MAN_Recap @ManufacturerID, @StartDate, @EndDate,0
COMMIT TRANSACTION
Nell'ottobre del 2018 è stato deciso di raddoppiare i valori di Candy
USE [_Test_DBStats]
-- ALIAS
DECLARE @ProdMultiplier DECIMAL(5,3) = 2
DECLARE @CatMultiplier DECIMAL(5,3) = 2
DECLARE @ManufacturerID INT = 4119 -- CANDY
DECLARE @StartDate DATE = '2018-01-01'
DECLARE @EndDate DATE = '2018-09-30'
BEGIN TRANSACTION
EXEC MAN_Recap @ManufacturerID, @StartDate, @EndDate,0
UPDATE STS_ProductByDevice SET STS_ProductByDevice.PageViews = ROUND(STS_ProductByDevice.PageViews*@ProdMultiplier,0)
--SELECT STS_ProductByDevice.PageViews, CEILING(STS_ProductByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByDevice
INNER JOIN ProductsFlatData ON STS_ProductByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ProductByShopByDevice SET STS_ProductByShopByDevice.PageViews = ROUND(STS_ProductByShopByDevice.PageViews*@ProdMultiplier,0)
--SELECT STS_ProductByShopByDevice.PageViews, CEILING(STS_ProductByShopByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByShopByDevice
INNER JOIN ProductsFlatData ON STS_ProductByShopByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByShopByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ManufacturerByDevice SET CatalogPageViews = ROUND(CatalogPageViews*@CatMultiplier,0)
--SELECT CatalogPageViews, CEILING(CatalogPageViews*@CatMultiplier) AS SuperCatalogPageViews
FROM STS_ManufacturerByDevice
WHERE STS_ManufacturerByDevice.ManufacturerID = @ManufacturerID AND STS_ManufacturerByDevice.Data BETWEEN @StartDate AND @EndDate
--------------------------------------------------------------------------------------------------------------
---------------------------------- TROVAPRODOTTI -------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
UPDATE STS_ProductByTpByDevice SET STS_ProductByTpByDevice.PageViews = ROUND(STS_ProductByTpByDevice.PageViews*@ProdMultiplier,0)
--SELECT STS_ProductByTpByDevice.PageViews, CEILING(STS_ProductByTpByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByTpByDevice
INNER JOIN ProductsFlatData ON STS_ProductByTpByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByTpByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ManufacturerByTP SET PageViews = ROUND(PageViews*@CatMultiplier,0)
--SELECT PageViews, CEILING(PageViews*@CatMultiplier) AS SuperCatalogPageViews
FROM STS_ManufacturerByTP
WHERE STS_ManufacturerByTP.ManufacturerID = @ManufacturerID AND STS_ManufacturerByTP.Data BETWEEN @StartDate AND @EndDate
EXEC MAN_Recap @ManufacturerID, @StartDate, @EndDate,0
COMMIT TRANSACTION
Candy non era ancora abbastanza pompato. Nuovo moltiplicatore di 3,5 che sommandosi all'altro praticamente fa un 7x . Moltiplicate anche le impression per 2,2
USE [_Test_DBStats]
-- ALIAS
DECLARE @ProdMultiplier DECIMAL(5,3) = 3.5
DECLARE @CatMultiplier DECIMAL(5,3) = 3.5
DECLARE @ImpMultiplier DECIMAL(5,3) = 2.2 -- Moltiplico anche le impression
DECLARE @ManufacturerID INT = 4119 -- CANDY
DECLARE @StartDate DATE = '2018-01-01'
DECLARE @EndDate DATE = '2018-09-30'
BEGIN TRANSACTION
EXEC MAN_Recap @ManufacturerID, @StartDate, @EndDate,0
UPDATE STS_ProductByDevice SET STS_ProductByDevice.PageViews = ROUND(STS_ProductByDevice.PageViews*@ProdMultiplier,0)
--SELECT STS_ProductByDevice.PageViews, CEILING(STS_ProductByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByDevice
INNER JOIN ProductsFlatData ON STS_ProductByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ProductByShopByDevice SET STS_ProductByShopByDevice.PageViews = ROUND(STS_ProductByShopByDevice.PageViews*@ProdMultiplier,0)
--SELECT STS_ProductByShopByDevice.PageViews, CEILING(STS_ProductByShopByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByShopByDevice
INNER JOIN ProductsFlatData ON STS_ProductByShopByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByShopByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ManufacturerByDevice SET CatalogPageViews = ROUND(CatalogPageViews*@CatMultiplier,0)
, Impressions = ROUND(Impressions*@ImpMultiplier,0)
--SELECT CatalogPageViews, CEILING(CatalogPageViews*@CatMultiplier) AS SuperCatalogPageViews
FROM STS_ManufacturerByDevice
WHERE STS_ManufacturerByDevice.ManufacturerID = @ManufacturerID AND STS_ManufacturerByDevice.Data BETWEEN @StartDate AND @EndDate
--------------------------------------------------------------------------------------------------------------
---------------------------------- TROVAPRODOTTI -------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
UPDATE STS_ProductByTpByDevice SET STS_ProductByTpByDevice.PageViews = ROUND(STS_ProductByTpByDevice.PageViews*@ProdMultiplier,0)
--SELECT STS_ProductByTpByDevice.PageViews, CEILING(STS_ProductByTpByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByTpByDevice
INNER JOIN ProductsFlatData ON STS_ProductByTpByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByTpByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ManufacturerByTP SET PageViews = ROUND(PageViews*@CatMultiplier,0)
--SELECT PageViews, CEILING(PageViews*@CatMultiplier) AS SuperCatalogPageViews
FROM STS_ManufacturerByTP
WHERE STS_ManufacturerByTP.ManufacturerID = @ManufacturerID AND STS_ManufacturerByTP.Data BETWEEN @StartDate AND @EndDate
EXEC MAN_Recap @ManufacturerID, @StartDate, @EndDate,0
COMMIT TRANSACTION
Faber x2 (ottobre 2018)
USE [_Test_DBStats]
-- ALIAS
DECLARE @ProdMultiplier DECIMAL(5,3) = 2
DECLARE @CatMultiplier DECIMAL(5,3) = 2
--DECLARE @ImpMultiplier DECIMAL(5,3) = 2.2 -- Moltiplico anche le impression
DECLARE @ManufacturerID INT = 207 -- FABER
DECLARE @StartDate DATE = '2017-09-01'
DECLARE @EndDate DATE = '2018-09-30'
BEGIN TRANSACTION
EXEC MAN_Recap @ManufacturerID, @StartDate, @EndDate,0
UPDATE STS_ProductByDevice SET STS_ProductByDevice.PageViews = ROUND(STS_ProductByDevice.PageViews*@ProdMultiplier,0)
--SELECT STS_ProductByDevice.PageViews, CEILING(STS_ProductByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByDevice
INNER JOIN ProductsFlatData ON STS_ProductByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ProductByShopByDevice SET STS_ProductByShopByDevice.PageViews = ROUND(STS_ProductByShopByDevice.PageViews*@ProdMultiplier,0)
--SELECT STS_ProductByShopByDevice.PageViews, CEILING(STS_ProductByShopByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByShopByDevice
INNER JOIN ProductsFlatData ON STS_ProductByShopByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByShopByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ManufacturerByDevice SET CatalogPageViews = ROUND(CatalogPageViews*@CatMultiplier,0)
-- , Impressions = ROUND(Impressions*@ImpMultiplier,0)
--SELECT CatalogPageViews, CEILING(CatalogPageViews*@CatMultiplier) AS SuperCatalogPageViews
FROM STS_ManufacturerByDevice
WHERE STS_ManufacturerByDevice.ManufacturerID = @ManufacturerID AND STS_ManufacturerByDevice.Data BETWEEN @StartDate AND @EndDate
--------------------------------------------------------------------------------------------------------------
---------------------------------- TROVAPRODOTTI -------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
UPDATE STS_ProductByTpByDevice SET STS_ProductByTpByDevice.PageViews = ROUND(STS_ProductByTpByDevice.PageViews*@ProdMultiplier,0)
--SELECT STS_ProductByTpByDevice.PageViews, CEILING(STS_ProductByTpByDevice.PageViews*@ProdMultiplier) AS SuperPageViews
FROM STS_ProductByTpByDevice
INNER JOIN ProductsFlatData ON STS_ProductByTpByDevice.ProductID = ProductsFlatData.ProductID
WHERE ProductsFlatData.ManufacturerID = @ManufacturerID AND STS_ProductByTpByDevice.Data BETWEEN @StartDate AND @EndDate
UPDATE STS_ManufacturerByTP SET PageViews = ROUND(PageViews*@CatMultiplier,0)
--SELECT PageViews, CEILING(PageViews*@CatMultiplier) AS SuperCatalogPageViews
FROM STS_ManufacturerByTP
WHERE STS_ManufacturerByTP.ManufacturerID = @ManufacturerID AND STS_ManufacturerByTP.Data BETWEEN @StartDate AND @EndDate
EXEC MAN_Recap @ManufacturerID, @StartDate, @EndDate,0
COMMIT TRANSACTION
Nel novembre del 2018 sono state fatte altre 19 operazioni per moltiplicare i valori statistici di altri produttori. File:201811-query moltiplicatore PROD.zip
Nel dicembre del 2018 sono state fatte altre operazioni per moltiplicare i valori statistici di altri produttori. File:201812-query moltiplicatore PROD.zip
Nel dicembre del 2018 abbiamo dovuto abbassare le impression di Valcucine per far coincidere calcoli vari
USE [DBStats]
-- VALCUCINE
-- Primo caso in cui vengono ABBASSATE le stas (solo IMPRESSION)
DECLARE @ImpMultiplier DECIMAL(5,3) = 0.8 -- Moltiplico le impression
DECLARE @ManufacturerID INT = 510 -- VALCUCINE
-- primo giorno del mese iniziale
DECLARE @StartDate DATE = '2018-01-01'
-- ultimo giorno del mese finale
DECLARE @EndDate DATE = '2018-11-30'
BEGIN TRANSACTION
EXEC MAN_Recap @ManufacturerID, @StartDate, @EndDate,0
UPDATE STS_ManufacturerByDevice SET Impressions = ROUND(Impressions*@ImpMultiplier,0)
--SELECT CatalogPageViews, CEILING(CatalogPageViews*@CatMultiplier) AS SuperCatalogPageViews
FROM STS_ManufacturerByDevice
WHERE STS_ManufacturerByDevice.ManufacturerID = @ManufacturerID AND STS_ManufacturerByDevice.Data BETWEEN @StartDate AND @EndDate
EXEC MAN_Recap @ManufacturerID, @StartDate, @EndDate,0
ROLLBACK TRANSACTION
Nel gennaio del 2019 sono state fatte altre operazioni per moltiplicare i valori statistici di altri produttori.
File:201901-query moltiplicatore PROD.zip
E ancora queste:
File:20190116-query moltiplicatore PROD.zip
Nel febbraio del 2019 abbiamo moltiplicato questi: File:20190205-query moltiplicatore PROD.zip
Nel marzo del 2019 abbiamo moltiplicato questi: File:20190305-query moltiplicatore PROD.zip
Nell'aprile del 2019 abbiamo moltiplicato questi: File:20190402-query moltiplicatore PROD.zip
Nel maggio del 2019 abbiamo moltiplicato questi: File:20190507-query moltiplicatore PROD.zip
Nel giugno del 2019 abbiamo moltiplicato questi: File:20190611 query moltiplicatore PROD.zip
Nel luglio del 2019 abbiamo moltiplicato questi: File:20190703 query moltiplicatore PROD.zip
Nel settembre del 2019 abbiamo moltiplicato questi: File:20190903 query moltiplicatore PROD.zip per i dati di LUGLIO
Nel settembre del 2019 abbiamo moltiplicato questi: File:20190920 query moltiplicatore PROD.zip per i dati di AGOSTO
Nell'ottobre del 2019 abbiamo moltiplicato questi: File:2011010 query moltiplicatore PROD.zip per i dati di SETTEMBRE
Nel novembre del 2019 abbiamo moltiplicato questi: File:20191106 query moltiplicatore PROD.zip per i dati di OTTOBRE
Nel novembre del 2019 abbiamo diminuito le impression di Fantini perché nel 2018 risultano esagerate
USE Statistiche
-- Moltiplicatore di impression
DECLARE @ImpMultiplier DECIMAL(5,3) = 0.744
-- Manufacturer ID
DECLARE @ManufacturerID INT = 1286 -- FANTINI
-- primo giorno del mese iniziale
DECLARE @StartDate DATE = '2018-01-01'
-- ultimo giorno del mese finale
DECLARE @EndDate DATE = '2018-12-31'
BEGIN TRANSACTION
EXEC MAN_Recap @ManufacturerID, @StartDate, @EndDate,0
UPDATE STS_ManufacturerByDevice SET Impressions = ROUND(Impressions*@ImpMultiplier,0)
FROM STS_ManufacturerByDevice
WHERE STS_ManufacturerByDevice.ManufacturerID = @ManufacturerID AND STS_ManufacturerByDevice.Data BETWEEN @StartDate AND @EndDate
EXEC MAN_Recap @ManufacturerID, @StartDate, @EndDate,0
ROLLBACK TRANSACTION
Nel dicembre del 2019 abbiamo moltiplicato questi: File:20191203 query moltiplicatore PROD.zip per i dati di NOVEMBRE
Nel dicembre del 2019 abbiamo sistemato ancora questi: File:20191218 query moltiplicatore PROD.zip
Nel gennaio del 2020 abbiamo moltiplicato questi: File:20200109 query moltiplicatore PROD.zip
Nel febbraio del 2020 abbiamo moltiplicato questi: File:20200210 query moltiplicatore PROD.zip
Nel marzo del 2020 abbiamo moltiplicato questi: File:20200305 query moltiplicatore PROD.zip
Nell'aprile del 2020 abbiamo moltiplicato questi: File:20200403 query moltiplicatore PROD.zip
Nel maggio del 2020 abbiamo moltiplicato questi: File:20200507 query moltiplicatore PROD.zip
Nel giugno del 2020 abbiamo moltiplicato questi: File:20200611 query moltiplicatore PROD.zip
Nel luglio del 2020 abbiamo moltiplicato questi: File:20200702 query moltiplicatore PROD.zip
Nel'agosto del 2020 abbiamo moltiplicato questi: File:20200825 query moltiplicatore PROD.zip
Nel settembre del 2020 abbiamo moltiplicato questi: File:20200902 query moltiplicatore PROD.zip
Nell'ottobre del 2020 abbiamo moltiplicato questi: File:20201002 query moltiplicatore PROD.zip
Nel novembre del 2020 abbiamo moltiplicato questi: File:20201103 query moltiplicatore PROD.zip
Nel dicembre del 2020 abbiamo moltiplicato questi: File:20201216 query moltiplicatore PROD.zip
Nel gennaio del 2021 abbiamo moltiplicato questi: File:20200108 query moltiplicatore PROD.zip
Nel febbraiodel 2021 abbiamo moltiplicato questi: File:20200203 query moltiplicatore PROD.zip
Campagne
Questo paragrafo riassume tutte le campagne Facebook che sono state fatte nei mesi. I numeri sono stati inseriti manualmente nel database mese per mese.
LUGLIO 2017 - Campagna Facebook
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 142 WHERE
ManufacturerID = 313 AND Data = '2017-07-01' AND DeviceType = 'desktop'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 186 WHERE
ManufacturerID = 4108 AND Data = '2017-07-01' AND DeviceType = 'desktop'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 84 WHERE ManufacturerID
= 4107 AND Data = '2017-07-01' AND DeviceType = 'desktop'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 96 WHERE ManufacturerID
= 1322 AND Data = '2017-07-01' AND DeviceType = 'desktop'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 79 WHERE ManufacturerID
= 1938 AND Data = '2017-07-01' AND DeviceType = 'desktop'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 246 WHERE
ManufacturerID = 308 AND Data = '2017-07-01' AND DeviceType = 'desktop'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 58 WHERE ManufacturerID
= 4114 AND Data = '2017-07-01' AND DeviceType = 'desktop'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 56 WHERE ManufacturerID
= 3981 AND Data = '2017-07-01' AND DeviceType = 'desktop'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 56 WHERE ManufacturerID
= 2070 AND Data = '2017-07-01' AND DeviceType = 'desktop'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 178 WHERE
ManufacturerID = 373 AND Data = '2017-07-01' AND DeviceType = 'desktop'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 80 WHERE ManufacturerID
= 207 AND Data = '2017-07-01' AND DeviceType = 'desktop'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 79 WHERE ManufacturerID
= 3940 AND Data = '2017-07-01' AND DeviceType = 'desktop'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 56 WHERE ManufacturerID
= 4021 AND Data = '2017-07-01' AND DeviceType = 'desktop'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 108 WHERE
ManufacturerID = 4117 AND Data = '2017-07-01' AND DeviceType = 'desktop'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 81 WHERE ManufacturerID
= 2216 AND Data = '2017-07-01' AND DeviceType = 'desktop'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 174 WHERE
ManufacturerID = 1481 AND Data = '2017-07-01' AND DeviceType = 'desktop'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 66 WHERE ManufacturerID
= 4095 AND Data = '2017-07-01' AND DeviceType = 'desktop'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 60 WHERE ManufacturerID
= 313 AND Data = '2017-07-01' AND DeviceType = 'mobile'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 79 WHERE ManufacturerID
= 4108 AND Data = '2017-07-01' AND DeviceType = 'mobile'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 35 WHERE ManufacturerID
= 4107 AND Data = '2017-07-01' AND DeviceType = 'mobile'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 41 WHERE ManufacturerID
= 1322 AND Data = '2017-07-01' AND DeviceType = 'mobile'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 33 WHERE ManufacturerID
= 1938 AND Data = '2017-07-01' AND DeviceType = 'mobile'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 105 WHERE
ManufacturerID = 308 AND Data = '2017-07-01' AND DeviceType = 'mobile'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 24 WHERE ManufacturerID
= 4114 AND Data = '2017-07-01' AND DeviceType = 'mobile'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 24 WHERE ManufacturerID
= 3981 AND Data = '2017-07-01' AND DeviceType = 'mobile'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 24 WHERE ManufacturerID
= 2070 AND Data = '2017-07-01' AND DeviceType = 'mobile'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 76 WHERE ManufacturerID
= 373 AND Data = '2017-07-01' AND DeviceType = 'mobile'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 33 WHERE ManufacturerID
= 207 AND Data = '2017-07-01' AND DeviceType = 'mobile'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 33 WHERE ManufacturerID
= 3940 AND Data = '2017-07-01' AND DeviceType = 'mobile'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 23 WHERE ManufacturerID
= 4021 AND Data = '2017-07-01' AND DeviceType = 'mobile'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 46 WHERE ManufacturerID
= 4117 AND Data = '2017-07-01' AND DeviceType = 'mobile'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 34 WHERE ManufacturerID
= 2216 AND Data = '2017-07-01' AND DeviceType = 'mobile'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 74 WHERE ManufacturerID
= 1481 AND Data = '2017-07-01' AND DeviceType = 'mobile'
AND ZoneID = 1
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 27 WHERE ManufacturerID
= 4095 AND Data = '2017-07-01' AND DeviceType = 'mobile'
AND ZoneID = 1
SETTEMBRE 2017 - Campagna Facebook
-- Campagna Facebook settembre 2017 desktop
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 53 WHERE ManufacturerID = 4095 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2017-09-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 57 WHERE ManufacturerID = 1481 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2017-09-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 221 WHERE ManufacturerID = 3940 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2017-09-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 501 WHERE ManufacturerID = 2216 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2017-09-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 41 WHERE ManufacturerID = 207 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2017-09-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 49 WHERE ManufacturerID = 4021 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2017-09-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 185 WHERE ManufacturerID = 4117 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2017-09-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 74 WHERE ManufacturerID = 308 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2017-09-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 23 WHERE ManufacturerID = 1322 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2017-09-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 144 WHERE ManufacturerID = 3981 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2017-09-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 123 WHERE ManufacturerID = 4114 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2017-09-01'
-- Campagna Facebook settembre 2017 mobile
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 21 WHERE ManufacturerID = 4095 AND DeviceType = 'mobile' AND ZoneID = 1 AND Data = '2017-09-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 31 WHERE ManufacturerID = 1481 AND DeviceType = 'mobile' AND ZoneID = 1 AND Data = '2017-09-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 157 WHERE ManufacturerID = 3940 AND DeviceType = 'mobile' AND ZoneID = 1 AND Data = '2017-09-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 268 WHERE ManufacturerID = 2216 AND DeviceType = 'mobile' AND ZoneID = 1 AND Data = '2017-09-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 22 WHERE ManufacturerID = 207 AND DeviceType = 'mobile' AND ZoneID = 1 AND Data = '2017-09-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 21 WHERE ManufacturerID = 4021 AND DeviceType = 'mobile' AND ZoneID = 1 AND Data = '2017-09-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 146 WHERE ManufacturerID = 4117 AND DeviceType = 'mobile' AND ZoneID = 1 AND Data = '2017-09-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 42 WHERE ManufacturerID = 308 AND DeviceType = 'mobile' AND ZoneID = 1 AND Data = '2017-09-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 34 WHERE ManufacturerID = 1322 AND DeviceType = 'mobile' AND ZoneID = 1 AND Data = '2017-09-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 104 WHERE ManufacturerID = 3981 AND DeviceType = 'mobile' AND ZoneID = 1 AND Data = '2017-09-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits =+ 129 WHERE ManufacturerID = 4114 AND DeviceType = 'mobile' AND ZoneID = 1 AND Data = '2017-09-01'
OTTOBRE 2017 - Campagna Facebook
-- Campagna Facebook ottobre 2017 mobile
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 31 WHERE ManufacturerID = 4095 AND DeviceType = 'mobile' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 4 WHERE ManufacturerID = 1481 AND DeviceType = 'mobile' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 21 WHERE ManufacturerID = 3940 AND DeviceType = 'mobile' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 59 WHERE ManufacturerID = 2216 AND DeviceType = 'mobile' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 26 WHERE ManufacturerID = 207 AND DeviceType = 'mobile' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 22 WHERE ManufacturerID = 4021 AND DeviceType = 'mobile' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 56 WHERE ManufacturerID = 4117 AND DeviceType = 'mobile' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 23 WHERE ManufacturerID = 3981 AND DeviceType = 'mobile' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 12 WHERE ManufacturerID = 4114 AND DeviceType = 'mobile' AND ZoneID = 1 AND Data = '2017-10-01'
-- Campagna Facebook ottobre 2017 tablet
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 16 WHERE ManufacturerID = 4095 AND DeviceType = 'tablet' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 2 WHERE ManufacturerID = 1481 AND DeviceType = 'tablet' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 11 WHERE ManufacturerID = 3940 AND DeviceType = 'tablet' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 27 WHERE ManufacturerID = 2216 AND DeviceType = 'tablet' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 13 WHERE ManufacturerID = 207 AND DeviceType = 'tablet' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 11 WHERE ManufacturerID = 4021 AND DeviceType = 'tablet' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 28 WHERE ManufacturerID = 4117 AND DeviceType = 'tablet' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 12 WHERE ManufacturerID = 3981 AND DeviceType = 'tablet' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 6 WHERE ManufacturerID = 4114 AND DeviceType = 'tablet' AND ZoneID = 1 AND Data = '2017-10-01'
-- Campagna Facebook ottobre 2017 desktop
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 108 WHERE ManufacturerID = 4095 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 15 WHERE ManufacturerID = 1481 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 74 WHERE ManufacturerID = 3940 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 188 WHERE ManufacturerID = 2216 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 90 WHERE ManufacturerID = 207 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 76 WHERE ManufacturerID = 4021 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 197 WHERE ManufacturerID = 4117 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 81 WHERE ManufacturerID = 3981 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2017-10-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 41 WHERE ManufacturerID = 4114 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2017-10-01'
OTTOBRE 2018 - Campagna Facebook
USE DBStats
BEGIN TRANSACTION
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 279 WHERE ManufacturerID = 4145 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2018-11-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 122 WHERE ManufacturerID = 1481 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2018-11-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 129 WHERE ManufacturerID = 1286 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2018-11-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 263 WHERE ManufacturerID = 1322 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2018-11-01'
UPDATE STS_ManufacturerByDevice SET WebsiteVisits += 1 WHERE ManufacturerID = 4119 AND DeviceType = 'desktop' AND ZoneID = 1 AND Data = '2018-11-01'
COMMIT TRANSACTION
