Login
/ Register
Subscribe
Subscribe
News
| Richard Haas

By Richard Haas, last updated Jan 21 2025 (Also available here on Google Docs)

Overview of PSD structure

As new data is collected, it is added to the following sheet: ‘PSD Data entry sheet linked to SQL Queries.’

This file is the underlying source of all data which is then processed by Google BigQuery SQL queries. The queries we use can be found and edited if necessary on Google Cloud Console.

The names of the SQL queries we use are as follows:

  • Allocations-sorted
  • Assignments-sorted
  • Countries-1
  • Operators-1
  • Expiry
  • MHz-per-pop-new-reference-nr-3

These SQL queries are linked to ‘PolicyTracker Spectrum Database (PSD) Last updated: DD.MM.YYY’ (This is sent out to subscribers). They are connected using Google’s built in BigQuery connection, and visible as ‘extracts’. These can be updated when new data is added to the data entry sheet.

Data entry

Monitor any updates relating to new auctions, new assignments, spectrum trades, mergers, spectrum divestment, spectrum allocations, etc.

When you’re ready to add these to the database, insert any new data on allocations in the Allocations_static sheet, and new data on assignments in the Assignment_static sheet in the ‘PSD Data entry sheet linked to SQL Queries’ sheet.

Key points

New entries require a new licence reference number.

  • This is added in column U in Allocations_static, and AR in Licence_static.
  • Simply take the number in cell AS1 which shows the largest reference number, add 1 and insert it to your entry.
  • E.g. if it says “5755”, add “5756” to your entry and so on. Each row needs a unique reference number.

Ensure formatting is consistent

  • If you’re unsure how to enter data in a certain column, check the notes in row one (hover over the cell)
  • It is important to maintain the same formatting for columns involving numbers, and not to change the number of decimal places.
  • String (i.e. text) cells, these need to be written out in the exact same way. For example in the bands column, ‘3.3 – 3.8 GHz’ cannot be referred to as ‘3.5 GHz’ or ‘3.3–3.8 GHz’. This needs to be consistent as it is used for grouping in the SQL queries.

Adding new countries

  • If a brand new country is added which has not previously been in the database, ensure the following is present in ‘PSD Data entry sheet linked to SQL Queries’.
  • The population for this country is available in POP_DATA.
  • The exchange rate for that country’s currency is available in Exchange_rates2. Exchange rate data is needed for the year the assignment took place and the current year.
  • To make life in the future easier, I recommend adding exchange rate data from current year to 1999, so this data is available to our SQL queries.

Data synchronization

Once you have finished adding the new data into ‘PSD Data entry sheet linked to SQL Queries’, it is time to run our SQL queries and import this into our user facing Google Sheet. We will also create an Excel version which is sent out to subscribers.

How to synchronise database with the latest entries

Synchronise SQL queries

  1. Go to the ‘PolicyTracker Spectrum Database (PSD) Last updated: DD.MM.YYY’
  2. Open Allocations, hover over the refresh button in the bottom left corner.
  3. Click ‘…” and then click ‘Refresh options’ (see screenshot below)
  1. Then click ‘Refresh all’ at the bottom, and wait for the SQL queries to run. This will take a minute or so.
  2. Once this is complete, it’s time to conduct some checks.

Conducting checks, final review

For all sheets

  • In all sheets, sort the ‘Last updated’ column to view new entries. Ensure that these are present and displayed correctly.

Checking ‘MHz per pop’ sheet

  • The SQL query for this sheet is complex, so it is important to review it.
  • In the ‘MHz per pop’ sheet, do the following:
    1. Add a filter and sort column Z in a descending order (A-Z). Check the top entries are Germany, and other well known high price auctions like the UK, and some US entries too.
    2. Now, filter the last updated column (S) to display new entries only. Ensure that data is showing properly, particularly in columns W onwards.

Troubleshooting: If data is missing from some columns, check the correct Currency Code has been inserted in column D. Also check that there is currency rate data for that currency code is available for the current year and for the year the licence was assigned. Also check that the country is included in POP_DATA and that relevant population data is included.

Final review

  • Go through each sheet again, and ensure that the filter is visible but no conditions have been applied. Customers can only view the visible rows, so it is important not to leave any filters on.
  • Amend the name of the file to reflect today’s date, and do the same in the ‘Guide’ sheet.
  • Ensure that no sheets ending in ‘-source’ are visible. If they are, right click the sheet and click ‘hide sheet’.

Download and upload Excel version to website

Download Excel file

  • Click the hamburger menu (☰) in the bottom left corner, and open the hidden sheet called ‘Guide-Excel’; now update the date to reflect the latest update.
  • Click on ‘file’ -> Download -> Microsoft Excel
  • Now re-hide the sheet: Right click ‘Guide-Excel’ and click ‘Hide sheet’.

Reviewing Excel file

  • Open the downloaded Excel file and check each sheet to review if numbers are displayed properly.
  • Expand the header rows so that the entire header text is visible in all columns. Re-format to ‘wrap text’ if necessary.
  • Check the ‘notes’ in the first row of ‘Assignments’, ‘MHz per pop’. If these are too small, right click them and choose ‘edit note’. Then drag the box to be larger so the entire text is visible.

Saving and naming Excel version

  • Save the file, and amend the name to include “SRS-only-” before the start. The file name should look something like this: ‘SRS-only-PolicyTracker Spectrum Database (PSD) 20.01.2025’.
  • Upload the final file to this Google Drive folder for archival purposes. 

Uploading and securing Excel version to PolicyTracker website

  • Upload this file to the PolicyTracker website under the ‘Media’ tab.
  • Go to this page: https://www.policytracker.com/spectrum-dashboard/secured-pdfs/
  • Edit the page to add in the latest version. But when adding the hyperlink, add /sec/ before the document name. E.g. https://www.policytracker.com/wp-content/uploads/sec/SRS-only-Spam-form.pdf
  • Now try downloading the file, both logged in and in an incognito tab where it should not work. (more on securing Excel files here)

Sending out email to subscribers

Updating email template

  • Open Quarterly update template (this goes out to subs)
  • Change the first sentence to explain some countries which you have updated in the PSD. Google sheets link does not need changing as this remains the same.

Sending out email

  • Click on the profile icon on the top right -> Salesforce Classic (Doing it in SF Lightning is a pain – don’t bother)
  • Navigate to ‘contacts’ page
  • In tools, select ‘Mass email contacts’
  • Select “PSD subs all” view .
  • Click “go”
  • Make sure to check email preview to see if it’s correct