Update Hotel Gross-Bookings

Primary Language Only

OVERVIEW:
Download and import the latest Hotel Gross Bookings (Hotel GB) data into the SWXL DB for use in URL and Keyword Generation processes.

– update GBV (gross bookings)
– update Transactions
– update Room Nights

You can find the latest Hotel GB for each POS under:
https://drive.google.com/open?id=0B2nosImlsF9HU1pvY05KdGRMODQ

Otherwise, we will have to download it from Expedia through their VPN or request it from Expedia.

DOWNLOADING & IMPORTING:

1. Enable your Expedia VPN. Instructions for setting that up can be found here:
   https://docs.google.com/document/d/1w18zj4B49KUxuFshyYYMTM-cbPWYZsCqYTcy5Ixc9wo/edit?usp=sharing
2. Go to http://hue/beeswax/. Log in as jmihalik / jmihalik.
3. In the big query window, run the attached query.
   (Adjust the point of sale and date to be about one year ago)

4. SELECT
    s.site_name,
    a.EXPE_LODG_PROPERTY_ID,
    a.LODG_PROPERTY_ID,
    a.LODG_PROPERTY_NAME,
    SUM(b.RM_CNT) AS ROOM_NIGHTS,
    SUM(b.RM_NIGHT_CNT) AS ROOM_NIGHT_CNT,
    SUM(b.GROSS_BKG_AMT_USD) as GBV
   FROM
    DM.LODG_RM_TRANS_FACT b
    INNER JOIN DM.LODG_PROPERTY_DIM a ON (b.LODG_PROPERTY_KEY=a.LODG_PROPERTY_KEY)
    INNER JOIN DM.site_dim s ON (s.tpid=b.tpid)
   WHERE
    b.trans_date_key >= '2016-08-23' -- about a year ago from when you are running the query to get 12 months of data
    and s.site_name = 'EXPEDIA.CA' -- site name selected from list below
   GROUP BY
    s.site_name,
    a.EXPE_LODG_PROPERTY_ID,
    a.LODG_PROPERTY_ID,
    a.LODG_PROPERTY_NAME

   Query should only take a few minutes.

5. Save results as csv.
6. Run the appropriate query from: https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/KWGeneration/trunk/KWExplosion/preprocess/06a-Hotel_GB.pl