Script 107: Inventory Bidding 2.0

Purpose:

The script determines auction boost amounts for groups in specific campaigns based on various criteria, including pageviews, PUP scores, and shoot prices.

To Elaborate

The Python script is designed to calculate and apply auction boost amounts for groups within specific marketing campaigns, namely “RBA-Pro-SEM/BSEM.” It uses data from Google Analytics (GA) for pageviews conversions and an inventory feed that provides additional metrics like Kelvin Top, PUP Score, and Shoot Price. The script also checks whether a group is opted into inventory bidding and when it was last updated. Groups are ignored if they are not modified by the script, and they are paused if the Kelvin Top value is not equal to 1. For groups with inventory bidding enabled, the script applies bid boosts based on a set of prioritized conditions: if pageviews are zero, a 50% boost is applied; if PUP Score or Shoot Price indicates “bid up” or “bid down,” additional adjustments are made.

Walking Through the Code

  1. Initialization:
    • A new column ‘Status’ is created in the output DataFrame and initialized with an empty string.
  2. Determine Group Status:
    • The script checks if the ‘Kelvin Top’ value is 1.0 to set the group status to ‘Active’; otherwise, it sets the status to ‘Paused’.
  3. Calculate Auction Boost:
    • The script calculates the ‘AuctionBoost’ value using nested operations:
      • If ‘Pageviews Conv.’ is less than 10, a boost of 50 is added.
      • If ‘PUP Score’ is not blank and contains “bid up” or “bid down,” it adjusts the boost by adding 30 for “bid up” or subtracting 50 for “bid down.”
      • If ‘PUP Score’ is blank, it considers ‘Shoot Price’ for similar adjustments.
  4. Apply Boosts:
    • The calculated ‘AuctionBoost’ is applied to active groups, while inactive groups receive a NaN value.
  5. Filter and Output:
    • The script filters and prints only the rows that have been modified, ensuring that only relevant changes are displayed.

Vitals

  • Script ID : 107
  • Client ID / Customer ID: 1306913045 / 60268001
  • Action Type: Bulk Upload
  • Item Changed: AdGroup
  • Output Columns: Account, Campaign, Group, Status, AuctionBoost
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: Jeff Sands (jsands@marinsoftware.com)
  • Created by Jeff Sands on 2023-05-17 20:38
  • Last Updated by Jeff Sands on 2024-03-13 17:24
> See it in Action

Python Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69

RPT_COL_GROUP = 'Group'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_COL_GROUP_STATUS = 'Group Status'
RPT_COL_INVENTORY_BIDDING = 'Inventory Bidding'
RPT_COL_INVENTORY_PERCENTAGE = 'Inventory Percentage'
RPT_COL_IB_LASTUPDATED = 'IB Last Updated'
RPT_COL_PAGEVIEWS_CONV = 'Pageviews Conv.'
RPT_COL_KELVIN_TOP = 'Kelvin Top'
RPT_COL_PUP_SCORE = 'PUP Score'
RPT_COL_SHOOT_PRICE = 'Shoot Price'
RPT_COL_AUCTIONBOOST = 'AuctionBoost'
BULK_COL_STATUS = 'Status'
BULK_COL_AUCTIONBOOST = 'AuctionBoost'

# Create a new column 'Status' initialized with an empty string
outputDf[BULK_COL_STATUS] = ''

try:
    # Check condition for 'Active' status using vectorized operations
    active_condition = inputDf[RPT_COL_KELVIN_TOP].astype(float) == 1.0
    outputDf.loc[active_condition, BULK_COL_STATUS] = 'Active'

    # Check condition for 'Paused' status using vectorized operations
    paused_condition = ~active_condition
    outputDf.loc[paused_condition, BULK_COL_STATUS] = 'Paused'

    try:
        # Calculate the 'AuctionBoost' column using nested operations with priority
        bidBoost = (
            # First, consider whether Pageviews Conv. is less than 10. If yes, add 50 to bidBoost
            ((inputDf[RPT_COL_PAGEVIEWS_CONV] < 10) * 50) +
            (
                # If PUP Score is not blank and contains either "bid up" or "bid down", consider PUP Score
                (~inputDf[RPT_COL_PUP_SCORE].isna()) &
                (inputDf[RPT_COL_PUP_SCORE].astype(str).str.contains('bid up|bid down'))
            ) * (
                # If PUP Score equals "bid up", add 30 to bidBoost
                (inputDf[RPT_COL_PUP_SCORE] == 'bid up') * 30 +
                # If PUP Score equals "bid down", subtract 50 from bidBoost
                (inputDf[RPT_COL_PUP_SCORE] == 'bid down') * -50
            ) +
            (
                # If PUP Score is blank, only then should we consider Shoot Price
                (inputDf[RPT_COL_PUP_SCORE].isna()) |
                (inputDf[RPT_COL_PUP_SCORE] == '')
            ) * (
                # If Shoot Price equals "bid up", add 30 to bidBoost
                (inputDf[RPT_COL_SHOOT_PRICE] == 'bid up') * 30 +
                # If Shoot Price equals "bid down", subtract 50 from bidBoost
                (inputDf[RPT_COL_SHOOT_PRICE] == 'bid down') * -50
            )
        )
        outputDf[BULK_COL_AUCTIONBOOST] = np.where(active_condition, bidBoost, np.nan)

    except Exception as e:
        print("Error occurred while calculating 'AuctionBoost':", str(e))

except Exception as e:
    print("Error occurred while checking 'Active' and 'Paused' conditions:", str(e))


# Filter rows that have been modified
modified_rows = outputDf[outputDf[BULK_COL_STATUS].ne('') | outputDf[BULK_COL_AUCTIONBOOST].notna()]

# Print the modified rows
print(tableize(modified_rows))

Post generated on 2025-03-11 01:25:51 GMT

comments powered by Disqus