Setting up the Advvy Data Tab in Excel

---

FOR EXCEL MEDIA PLAN EXPERTS

Advvy provides a media plan mapping service to enable your current plans to be integrated with Advvy.

What Advvy provides:

1. Excel expertise to work with your agency and help map your media plan to the Advvy Data Tab
2. Senior Excel developer who will create a bespoke macro that will automate the creation of your flighting table information
   We will add a button on the sheet for planners to use to rebuild and update the flighting table
3. Advice on design improvements based on our experience working with a variety of Clients and Media Plan designs

Note Customisation of Media Plans by Advvy does incur a development charge. Contact us to discuss your Media Plan development needs.

What Advvy will not do:

• We won’t redesign your media plan or be responsible for the calculations on your Media Plan tabs
  ( unless that’s also what you want us to do- then you can ask us to design a media plan with you. )

But I already have a great Media Plan!… How do I set up my Plan for Advvy?

Setting up your media plan for use with Advvy involves adding a new Advvy Data worksheet tab into your Media Plan and configuring the 4 data tables with the information from your media plan.

DIY Media Plan Setup: Advice for successful setup

If you want to DIY set up your Excel Media Plan for Advvy there are a number of requirements to ensure success:

• Ensure you are licensed for Advvy Media Plans (usually included in Advvy licensing for most users)
• Ensure your IT Department has granted permission for the Advvy Add-in app in your organisation’s Azure Portal.
• Once you have permission to access the Add-in you should be able to see the Advvy Menu in Excel.
• Ensure you have a Master Media Plan Template ready to be configured.
  This ideally is a template that will be used for all clients in your agency.
  If more than one Media Plan template design exists then each of these templates will need to be configured individually.

 

To get started:

• Create the Advvy Data Tab and Tables – see below
• Configure the Advvy Data Tab – see below

 

Create the Advvy Data Tab and Tables

Your Master Media Plan requires an additional tab to be added to the spreadsheet. The tab contains pre-configured tables with recognised Advvy fields that relate to the data entities found in Advvy.

This additional tab is available through the Advvy Add-in.

To create the Advvy Data Tab:

1. 1. In Excel, Select the ‘Advvy‘ menu and then click on the [Sync] button.
   2. The Media Plan Summary panel will display on the left of the screen.
   3. On the Campaigns tab, select the ‘Create Advvy Tables‘ button.
   4. This will insert a new Worksheet called “Advvy Data” and it will be pre-formatted with 4 Advvy Tables. This new tab is now ready to be configured.

If the Advvy Data tab does not appear check the following:

• • Is there already an existing ‘Advvy Data’ tab in the workbook? There can only be one Advvy Data worksheet in a plan
  • is there a worksheet where the 4 Advvy Tables may already exist ie an old Advvy Data tab was renamed, but still contains the 4 x Advvy Data Tables  – these tables are named references and can only exist in one location in your media plan.

 

Advvy Data Tab

The Advvy Data worksheet is an additional tab that is added to your Media Plan. The purpose of this tab is to summarise your media plan data into a format that Advvy can receive during a sync.

The Advvy Add-in for Excel exports the data in these tables via the Sync process.

There are four data tables that need to be configured with reference details from your excel Media Plan worksheets.
Some details are imported from the connected Advvy campaign record.

 

Campaign Details Synced	Most of these details come from the the linked Advvy Campaign record via the sync. Plan Version, Plan Date and the Total $ cells are all populated from your media plan and need to be configured.
Channel Plan	These details come from your plan and are synced TO Advvy. The Media Type names must exist in Advvy Master Data for the sync to work.
Fees and Charges	These details come from your plan and are synced TO Advvy. The Fee names must exist in Advvy Master Data for the sync to work.
Flighting	These details are created from your plan and are synced TO Advvy. Each row in this table represents a single flight from the channel plan ie a weekly block. If you have added a Digital activity for Facebook that has a 4 week duration, this would need to be divided into 4 separate flight rows where each row has the same Media type, Medium, Market etc and differ in their Start & End Dates. A custom macro should be created to split your channel plan flighting into multiple weekly rows. Advvy provides this macro creation as a service.

Configuring the Advvy Data Tables – Guidelines

Configuring the Advvy data tables can be as simple as adding a reference to a cell in the Media plan, or they could be more complex requiring a macro to populate totals or to build the flighting table. We have outlined the purpose of each table with advice on how to configure them.

Table 1: Campaign Details Synced

The majority of fields in this table are populated from the Advvy Campaign Details tab.

• Once a Media Plan is connected to a campaign the first 12 fields will be imported from the Advvy Campaign record during a sync.

The following fields need to be manually configured from your Media Plan if you wish to send this data to Advvy:

• Plan Version – Text field
• Plan Date – Date field
• Total Net Investment – $ Currency field usually a calculated formula total
• Total Gross Investment – $ Currency field usually a calculated formula total

 

These fields can be found on the Media Summary tab for your Advvy Campaign

 

Table 2: Channel Plan

• Each of the fields need to be configured from your Media Plan if you wish to send this data to Advvy
• Each row should contain a single Media Type that matches the Media Type record in Advvy (identical Name)
• Each row should have the corresponding Budget, Net Investment and Gross Investment totals for the Media Type
• Field definitions:
  • • Media Type – Text field that needs to exist in Advvy master data – Media Type eg Television
    • Sync? – Yes/No field (optional) tells the Sync process if this row should be sent to Advvy
    • Budget – $ Currency field for the Media type (optional)
    • Net Investment – $ Currency Field for the Media Type Net Spend
    • Gross Investment – $ Currency field for the Media Type Gross Spend

After a sync, these fields can be found on the Media Summary tab for your Advvy Campaign

Table 3: Fees and Charges

• Each of the fields need to be configured from your Media Plan if you wish to send this data to Advvy
• Each row should contain a single Fee Type that matches the Fee record in Advvy (identical Name)
• For example if you have added a Digital activity for Facebook that has a 4 week duration, this would need to be divided into 4 separate flight rows where each row has the same Media Type, Medium, Market etc and only differ in their Start & End Dates, values, quantities and costs.
• Field definitions:
  • • Fee Type – Text field that needs to exist in Advvy master data – Fees eg Production Fee
    • Amount – $ Currency value of the Fee

These fields can be found on the Media Summary tab for your Advvy Campaign

 

Table 4: Flighting

This Flighting table is best created using an Excel Macro. The Macro should step through each row on a channel plan, and create the necessary number of flights in the table.

• Each of the fields need to be configured from your Media Plan if you wish to send this data to Advvy.
• Not all fields are required for a successful sync. The minimum requirements are Media Type, Medium, Gross Amount or Net Amount, Start Date, End Date.
• Each Flight row should contain a ‘single weekly flight’ for a Medium and the corresponding flight data from the channel plan.
  The Available Advvy fields are outlined below – you can define how little or how much data you’d like to sync to Advvy.
• Each Field can be used to store your Media Plan values or calculations – we recommend taking the time to map your media plan fields to the Advvy data fields available.
• Note: if additional fields are required use the extra fields in the table or contact Advvy with your requirements for adding more fields.

• Field definitions:
  • • Media Type – Mandatory Text field that needs to exist in Advvy master data = Media Types
    • Market – Text field that needs to exist in Advvy master data – Markets (will be auto populated if blank)
    • Medium – Mandatory Text field that needs to exist in Advvy master data – Mediums eg publishers, radio stations, websites
    • Network – Text Field that needs to exist in Advvy master data – Networks (will be auto populated if blank)
    • Language – Text field that needs to exist in Advvy master data – Languages (will be auto populated if blank)
    • Media Sub-type – Text field that needs to exist in Advvy master data – Media Sub-Type (will be auto populated if blank)
    • Fee Type – Text Field that needs to exist in Advvy master data – Fees
    • Fees Amount – Currency Field for a fee amount
    • Gross Amount – Currency Field for Gross Amount roll-up
    • Net Amount – Currency Field for Net Amount roll-up
    • TCC Amount – Currency field for Total Cost to Client roll-up
    • Status – Text field that matches one of the available Advvy Status fields (Actual, Approved, Booked, Complete, Loaded, PCA Complete, Planned, Pre-Planned)
    • Start Date – Mandatory Date field representing the start date of the flight
    • End Date – Mandatory Date field representing the end date of the flight ie +6 days after Start Date
    • Description – Text field that needs to exist in Advvy master data –
    • Units – Free Text field  eg CPM, CPA
    • Quantity – Number field eg 1
    • Value – Currency field eg $ value of one unit
    • Text 1, Text 2, Text 3 – Extra Free Text fields available to be populated
    • Number 1, Number 2 – Extra Number fields available to be populated
    • Check 1, Check 2 – Extra yes/no fields available to be populated

These flights can be found on the Related> Campaign Flightings tab for your Advvy Campaign

 

Understanding Advvy Flighting

• In your channel plan, when mapping your bookings in the calendar, a cell usually represents a weekly flight
• The cell could contain a number representing a quantity for the week
• The cell could be coloured to represent a booking status
• Groups of cells could be merged to represent a single quantity over a number of weeks

 

To capture your flighting record correctly, you will need to discuss with your Excel Plan designer how each flight should be represented in Advvy.

In the example below, the highlighted row would be represented on the Advvy Data Flighting table like this:

In this example the highlighted row has 4 weeks of bookings, meaning it should be represented in Advvy as 4 separate flight rows.

 

---