Purpose

The Python script ingests media plan spend targets from a Google Sheets document and maps them to strategies for the current month.

To Elaborate

The script is designed to automate the process of ingesting monthly budget targets from a Google Sheets document and aligning them with specific strategies. It extracts budget data for the current month from a predefined Google Sheets structure, where each column represents a different month. The script then maps these budget targets to corresponding strategies in an input data frame. This process ensures that the media plan is updated with the latest budget allocations, facilitating efficient budget management and strategy alignment for the current month.

Walking Through the Code

1. Data Source Initialization
   • The script begins by defining the primary data source, which is a Google Sheets document containing budget information. It identifies the relevant columns for strategy and target data.
2. Determine Current Month’s Column
   • It calculates the column key for the current month based on the current date, aligning with the Google Sheets structure where each column corresponds to a month.
3. Load Current Month Targets
   • The script extracts budget targets for the current month from the Google Sheets data, renaming columns to standardize the data frame for further processing.
4. Prepare Output Data Frame
   • An output data frame is initialized by copying the input data frame. A placeholder value is set for the target column, which will be updated with actual budget data.
5. Map Budget Targets to Strategies
   • A dictionary is created from the Google Sheets data for quick lookup of budget targets by strategy. The script maps these targets to the input data frame, updating the target column with the corresponding values.
6. Finalize Output
   • The script removes any rows with missing data from the output data frame, ensuring only complete records are retained for further use.

Vitals

• Script ID : 1277
• Client ID / Customer ID: 309909744 / 14196
• Action Type: Bulk Upload (Preview)
• Item Changed: Strategy
• Output Columns: Strategy, Target
• Linked Datasource: M1 Report
• Reference Datasource: Google Sheets
• Owner: Grégory Pantaine (gpantaine@marinsoftware.com)
• Created by Grégory Pantaine on 2024-07-10 15:55
• Last Updated by emerryfield@marinsoftware.com on 2024-11-26 20:27

> 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
##
## name: [Demo] Ingest Media Plan
## description: Ingest Media Plan Spend targets from a G-sheets containing budgets per month per publisher, which maps to Strategies.
##  
## 
## author: M Huang saved my coding (Greg P).
## created: 2024-07-10
## 

today = datetime.datetime.now(CLIENT_TIMEZONE).date()

# primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_STRATEGY = 'Strategy'
RPT_COL_TARGET = 'Target'

# Assume column B is July, C is August, D is Sept, etc. 
# In terms of integer months, B=7, C=8, D=9, etc
column_key = chr(64 + today.month - 5)  
print(f"GSheets column key for current month: {today.strftime(('%B'))} => {column_key}")

# load current month targets
# - RPT_COL_STRATEGY is always column A
# - RPT_COL_TARGET column key for current month is determined above
current_month_budgets = dataSourceDict['2_1'] \
                        .loc[0:, ['A', column_key]] \
                        .rename(columns={ \
                            'A' : RPT_COL_STRATEGY, \
                            column_key : RPT_COL_TARGET \
                        })

print("current_month_budgets.shape", current_month_budgets.shape)
print("current_month_budgets first 10 rows", current_month_budgets.head(10))

# output columns and initial values
BULK_COL_STRATEGY = 'Strategy'
BULK_COL_TARGET = 'Target'
outputDf = inputDf.copy()
outputDf[BULK_COL_TARGET] = '7/1/2024'  # Initial value, assuming this is a placeholder

# user code start here

# Create a dictionary from the gSheetsDf for quick lookup
strategy_to_target = current_month_budgets.set_index(RPT_COL_STRATEGY)[RPT_COL_TARGET].to_dict()

# Map the target values from the gSheetsDf to the inputDf based on the Strategy column
outputDf[BULK_COL_TARGET] = inputDf[RPT_COL_STRATEGY].map(strategy_to_target)

outputDf = outputDf.dropna()

print("outputDf sample", tableize(outputDf.head()))

Post generated on 2024-11-27 06:58:46 GMT