Jacob Filipp

Category: Writing (page 2 of 2)

Pardot: finding the Pardot asset when all you have is a landing page

In a situation where you only know a Pardot landing page’s public URL, and you want to open it for editing, here is what you do:

  1. Go to the landing page in your browser (works for forms, too)
  2. add the parameter ?PI_DEBUG_ENABLE=1 to the end of the URL. So
    will become
  3. Hit Enter to go to this new URL
  4. A direct link to the Pardot asset will appear on the upper right of the page. Click it to start editing your page/form right away:


You can create a button that does the above work for you, as a bookmarklet:



Credit for this goes to Cheshire Impact’s post on editing live Pardot pages. As far as I can tell, this is a completely undocumented server-side function.

Sync Outlook Calendar to Google Calendar using Microsoft Power Automate

Summary: getting Outlook events to show up in Google Calendar can be a challenge due to your workplace’s security settings. If you have access to Microsoft Power Automate (aka Flow), then you can use this .zip package to set up an integration.

This Flow is based on this Outlook to Google Calendar synch package by Alex Matulich. Thank you for laying the groundwork, Alex!

Why sync these calendars?

My personal calendar is in a Google account, and I access it with a Google app on my Android phone. My workplace calendar is in Office 365 Outlook on my work laptop.

I wanted the ability to see work events on my personal calendar/device without having to install the Outlook app on my phone. I did this because it’s a good idea to keep home & work accounts on separate devices. To keep work-time and personal-time fully apart.

Using Microsoft Flow to synch Outlook and Google Calendars

Microsoft Power Automate (aka Flow) is a service that lets you connect different applications and build connections between them. If you’ve heard of IFTTT or Zapier, Flow has the same concept.

Flow was a good option for this sync because it was free and available. The basic free version of Power Automate is already included in Office 365, and apparently allows 750 “runs” a month. This version is good enough for our purposes, although your company may have paid for a more advanced version.

Download your Power Automate .zip package below:

v2 of this flow added on Aug. 7, 2020. It no longer copies the event description to Google Calendar. Long event descriptions were cutting off the important Event ID/Master ID.

v3 of this flow was added on Nov. 17, 2020. It has 1 minute delays between repeated actions, to try and reduce rate limiting problems. Also, changed Outlook settings to look forward only 90 days, instead of 300.

Known issues: the Google Calendar steps error out because they hit rate-limiting limits (“rateLimitExceeded” message). I’ve also encountered lots of problems when making changes to recurring events. If you think of a fix, post it in the comments!

Screenshot of this Microsoft Power Automate Flow that copies Outlook calendar events into Google calendar.

This Flow will copy over new events from Outlook to Google Calendar. It also handles event updates and deletions, as well as supporting recurring events.

Installation Instructions

To get started, sign in at the Power Automate site and Import the .zip package by clicking the Import button at the top. Next, you will be prompted to connect your Office account and your Google account.

Remember, you will need to set up your own Office ID and your own Google Calendar ID within the Flow, in order to make it work with your accounts. Awesome commenter “justme” shows you how to find the Google Calendar ID in the comments below. They say:

If you go to the Settings and Sharing options of your google calendar you should see an ID, that’s a long list of random characters followed by @group.calendar.google.com. Copy that whole thing into the google calendar ID field in your flow.

You might run into an issue where your provided Google Calendar credentials are not automatically used in this Flow’s elements. The account might say the dummy value “your@googlecalendarusername.com”.

If this happens, then plase go through every instance where those credentials are used, and click on the dropdown to change the account to use your actual Google Account. There are currently 5 spots where that change has to be made. The highlighted parts of the Flow below show where to make the changes:

Credits for the MS Power Automate Flow

This flow is based on the one at Syncing Office 365 Outlook to Google calendar using Power Automate by Alex Matulich. I found it through his helpful question around special date formatting for Google Calendar in a Power Automate discussion thread.

(The date format ended up being “yyyy-MM-ddTHH:mm:ss-00:00” in your own time zone)

Thank you Alex!

My customized Flow is different from Alex’s in that it doesn’t require you to have access to an Excel Online / OneDrive account to keep track of event updates. In my organization, I was not able to make proper use of Excel Tables in the way that Alex’s Flow expected.

Other MS Flows that could be helpful:
Create new events on Google Calendar from Office 365
Copy new events in Office 365 to Google Calendar and send a notification
Update Google Calendar events previously created from Outlook.com

Trying to add Outlook Calendar to Google calendar

There are a few other documented ways to bring your Office 365 Outlook events into G Suite/Google Calendar.

One of them could work for you. It all depends on your employer’s security settings. For me, none of these methods worked. Only the Microsoft Flow method I describe above was effective.

Share calendar from Outlook 2016

You might be able to share your calendar through a “sharing invitation” email, found under the “share calendar” button.

At my organization, sharing a calendar with an external contact was blocked by the admins. A message like “Problem: Your organization’s policy does not allow you to share with this recipient.” comes up whenever you try to share your calendar with a Google account.

Publish to WebDAV server

One recommendation you’ll see for Outlook and Google calendar integration is to use the “Publish to WebDAV Server” option. This requires having an intermediary account on a server like iCal Exchange (accounts are free).

Here is a guide for setting up WebDAV publishing, from Ablebits.

Sync with Google Calendar from Outlook 365 Online

Another option for calendar sync is to go to Outlook 365 Online, then go to Settings -> Calendar -> Accounts.

There is a section there that says “To manage all your calendars in one place, connect your personal accounts with your work account. Details of personal events, like title or location, won’t be shown to other people.”.

Unfortunately, this turned out to be only a one-way sync that brings Google Calendar events into Outlook calendar (the opposite of what I wanted). As of now (July 2020) this also seems to be a feature that’s not available to everyone.

If you are looking to see your Google Calendar from within Outlook, you can try the above method or try this guide for setting up calendar sync on Outlook for desktop.

Thanks for reading and good luck with the setup!

Tool – convert HTML list to Plaintext

This tool will convert a list of HTML elements like a dropdown list of <option> elements, or <li> and <ol>, into plaintext. One item per line. This helps when you’re quickly copying a list of dropdown items (using Developer Tools) into Excel.

Limitation: the current versiont works when each element is on its own line. This isn’t necessarily always going to be the case with valid HTML, though.

How to make iFrames auto-resize to 100% height based on content

Do you need to ensure your iframe’s height is always set to 100% of the height of the content you are embedding? If so, keep on reading.

This page contains freely-usable code for responsive iFrames. iFrame height will adjust based on the height of the content in them. The code works for cross-domain iFrames and does not use any libraries like jQuery.

The problem

As a Marketing Operations professional, I often need to put marketing forms onto webpages. Normally, I would put code like this onto the page so the form appears in an iFrame:

 <iframe src="https://mydomain.com/the-form.html" style="width:1px; min-width:100%; border-width: 0px;" allowtransparency="true"></iframe> 

The problem with iFrames is that you don’t always know their full height. Also, their height can change unexpectedly.

Continue reading

How we did it: Top Tasks analysis on the LexisNexis website


Have you ever wondered if your website is truly helping your visitors accomplish their goals?

It is usually challenging to find out. Often, the opinions of your coworkers, managers, and complaining customers are the ones you hear the loudest. It is difficult to find out what the average site user thinks.

One way to find out is through a “Top Tasks” survey. This approach has been used by governments, health services, universities and software companies like Microsoft. At the core, this is a survey that reveals user’s basic needs by overwhelming their “thinking brain” with choice. The purpose of this document is to help you run your own Top Tasks survey. You will learn how to set up such a survey through a step-by-step case study of how I ran such a survey at LexisNexis Canada (a company that makes software for lawyers). You will discover surprising real-world insights that go beyond other published materials on Top Tasks.

Why is a Top Tasks survey special?

This type of survey is used to discover which end goals your website visitors value the most. It goes beyond “what information do visitors want?” towards answering “what action can I help them accomplish?”.

There is a twist: in this survey, the visitor is shown about 100 different tasks and urged to choose the top 5 quickly – in this way, we get a “gut reaction” choice rather than one that they had consciously thought about.

Continue reading

Default field mappings (Pardot and Salesforce integration)

Note: this information is from 2018 and was applicable to the v1 Pardot Connector. The new v2 connector mapping is likely different.

Here are some notes about the initial sync that occurs between Pardot and Salesforce (SFDC).

When you first enable and validate the Salesforce Connector in Pardot, a synch will begin automatically. You will not have a chance to define how fields sync between Pardot and Salesforce, and how data overrides work – you are at the mercy of the default mapping that’s been preset for you.

Here is the official document that explains how Pardot fields are mapped to Salesforce fields by default.

Don’t like how the default sync is set up? Want to have total control of the initial mapping between Pardot and Salesforce?I recommend that you do 3 things:

  1. Configure the connector so that Salesforce is prevented from creating new Pardot Prospect records (at least initially).
  2. Ensure that the Salesforce user who’s been set up as the Pardot Connector User is blind to all Leads and Contacts. Your Salesforce administrator will have to set this up prior to verifying and enabling the Pardot Connector.
  3. If you have any assigned Prospects in your Pardot instance, mark them as “Do Not Sync With CRM” using an automation rule. Anyone who’s assigned will be automatically created as a Lead in Salesforce when you verify the Connector (this will happen because step #2 ensures that Pardot will never find an existing Lead for an Assigned prospect – they’re all hidden. So it will attempt to create a new Lead).

These setting will ensure that no Prospects are synched initially. This gives you time to configure the kind of integration mapping that you desire.

Remember that you can do a “practice integration” by connecting Pardot to a Salesforce Sandbox, and even by getting a free Pardot Training Environment to protect your main Pardot during integration testing.

In addition, here is a table that shows field IDs for the default mapping for our organization. Some of these field types might depend on our specific Salesforce setup:

Pardot field namePardot field IDsalesforce.com Field NameTypeIn case of conflicting values:
Years In Businessyears_in_business Text 
WebsitewebsiteWebsiteTextIf Pardot and Salesforce values differ when data sync occurs: Use Salesforce’s value
Territoryterritory Text 
SourcesourceLeadSourceTextIf Pardot and Salesforce values differ when data sync occurs: Use Salesforce’s value
Scoring Category Last Scored Atlast_scored_atpi__Pardot_Last_Scored_At__cDateIf Pardot and Salesforce values differ when data sync occurs: Use Pardot’s value
SalutationsalutationSalutationDropdownIf Pardot and Salesforce values differ when data sync occurs: Use Salesforce’s value
ProvincestateMailingStateTextIf Pardot and Salesforce values differ when data sync occurs: Use Salesforce’s value
Postal CodezipMailingPostalCodeTextIf Pardot and Salesforce values differ when data sync occurs: Use Salesforce’s value
PhonephonePhoneTextIf Pardot and Salesforce values differ when data sync occurs: Use Salesforce’s value
Pardot Hard Bouncedpardot_hard_bouncedpi__pardot_hard_bounced__cCheckboxPardot is the master. Map this lead field to the contact field so the contact record pulls in the data. This field is hidden until a hard bounce. Even if the Do Not Email and Opted Out fields are cleared, the bounce history is retained on the prospect record and the Pardot Hard Bounced, Email Bounced Reason and Email Bounced Date fields are not cleared. If the hard bounce was the result of an invalid email address, adding a valid email address clears the Pardot Hard Bounced field.
Opted Outopted_outHasOptedOutOfEmailCheckboxMore about Opt-Out synch behaviour
Last Namelast_nameLastNameTextIf Pardot and Salesforce values differ when data sync occurs: Use Salesforce’s value
Job Titlejob_titleTitleTextIf Pardot and Salesforce values differ when data sync occurs: Use Salesforce’s value
IndustryindustryIndustryTextIf Pardot and Salesforce values differ when data sync occurs: Use Salesforce’s value
First Namefirst_nameFirstNameTextIf Pardot and Salesforce values differ when data sync occurs: Use Salesforce’s value
FaxfaxFaxTextIf Pardot and Salesforce values differ when data sync occurs: Use Salesforce’s value
EmployeesemployeesNumberOfEmployeesTextIf Pardot and Salesforce values differ when data sync occurs: Use Salesforce’s value
Email Bounced Reasonemail_bounced_reasonEmailBouncedReasonTextMore details in the official field mapping docs
Email Bounced Dateemail_bounced_dateEmailBouncedDateTextMore details in the official field mapping docs
EmailemailEmailTextIf Pardot and Salesforce values differ when data sync occurs: Use Salesforce’s value


Read more about how email address changes are synched

Do Not Emailis_do_not_email Checkbox 
Do Not Callis_do_not_call Checkbox 
DepartmentdepartmentDepartmentTextIf Pardot and Salesforce values differ when data sync occurs: Use Salesforce’s value
CountrycountryMailingCountryTextIf Pardot and Salesforce values differ when data sync occurs: Use Salesforce’s value
CompanycompanyCompanyTextIf Pardot and Salesforce values differ when data sync occurs: Use Salesforce’s value
Commentscomments Textarea 
CitycityMailingCityTextIf Pardot and Salesforce values differ when data sync occurs: Use Salesforce’s value
Annual Revenueannual_revenueAnnualRevenueTextIf Pardot and Salesforce values differ when data sync occurs: Use Salesforce’s value
Address Twoaddress_twoMailingStreetTextIf Pardot and Salesforce values differ when data sync occurs: Use Salesforce’s value
Address Oneaddress_oneStreetTextIf Pardot and Salesforce values differ when data sync occurs: Use Salesforce’s value

The Pardot documentation has more information about standard sync behaviours.

And, this document shows how to change the sync behaviour for a particular pair of fields. Keep in mind that data priorities between Pardot and Salesforce cannot be changed for certain fields (For example, the “Opted Out” field – although that field can be remapped). These special rules are explained in the official field mapping documentation.

Finally, you should be aware that – regardless of the sync behaviour you’ve set up – Pardot will never use a blank value to overwrite an existing field value. The nuances of how Pardot acts on blank values are explained in this post from The Spot for Pardot.

Search keywords n-gram analysis tool

This is an n-gram analysis tool for search keywords from Google Webmaster Console. Simply copy the query and frequency (of clicks, or impressions) into the “Input field”. What makes this tool unique is that it takes into account the weighed frequencies of a term’s appearance in your list.

For example, if you are calculating an n-gram of 2, and your query click counts are:

  • plumbers in Toronto (22 clicks)
  • plumbers in New York (11 clicks)

Then “plumbers in” will have a total weight of 33 clicks.

Your feedback is important to me – leave a comment if you run into a bug / have a killer feature request. For bugs, tell me what you did, how the tool responded, and what your ideal expected response was.

[video] Online marketing tips for small businesses

On August 31st, 2017 I was invited to present to the StartUp Here Toronto community. I spoke about online marketing tips for new businesses. A recording of the presentation is available below:

Electrical bill for 100 Wellesley St. East – Toronto, Canada

How much can a renter expect to pay for electricity in Downtown Toronto?

Back in January 2016, my pregnant wife and I moved in to a new 2 bedroom apartment at 100 Wellesley St. East in Toronto. Soon after, we got our first full electrical bill: it was a $327 surprise.

This was very different from the $36/month bill at the 1-bedroom downtown apartment we just left.

This post is about sharing our real electrical bills with you. To show what an ordinary downtown renter could expect to pay.

We wish that we had known about the size of the electrical bill ahead of renting our apartment. As renters, we have zero choice in electricity providers – the company that set up a building’s original submeter wiring gets to set whatever renter fees it wants, and it is a “take it or move elsewhere” type of arrangement.

A Sample Bill

Here is an example of the type of charges that appear on a typical electrical bill (nubmers are from Jan 1, 2017 to Feb 1, 2017).
Hover over the “Charge” item with your mouse to see the explanation Wyse’s website gives for the charge.

Type of charge Amount
Delivery for 01/01/17-02/01/17 69.78
Electric Customer Charge for 01/01/17-02/01/17 0.06
Energy Charge for 01/01/17-02/01/17 51.59
Energy Charge 2 for 01/01/17-02/01/17 173.97
Regulatory Charges for 01/01/17-02/01/17 12.56
Service Delivery Fee for 01/01/17-02/01/17 21.66
HST # 832218960 RT0001 for 01/01/17-02/01/17 41.09
SSS Admin Charge for 01/01/17-02/01/17 0.26
Wyse HST for 01/01/17-02/01/17 3.96
Electric Meter for 01/01/17-02/01/17 4.37
Electric Meter 2 for 01/01/17-02/01/17 4.38
Line Loss Adjustment for 01/01/17-02/01/17 7.81
8% Provincial Rebate for 01/01/17-02/01/17 -25.27
8% Provincial Rebate – Wyse for 01/01/17-02/01/17 -2.43
Total $363.79

When signing the rental agreement, we paid a one-time $20 account setup fee and an energy deposit of $75.

Total Monthly Electricity Bill

Here is a graph that shows the total monthly electricity bill that we received in each month. Note that electrical charges for winter and spring are much higher than for summer and fall.

Late fees, and one-time setup charges were removed from the total.

It is great that Wyse provides an online portal that lets us download detailed billing and usage data. The raw bill data, as well as daily electricity consumption data is available in this Excel file for your reference:

2017-07-24 Wyse utility readings.xlsx

Note: we moved into the apartment on Jan. 20, 2016. Before that, there was some work being done by contractors. Because of that, the first “normal” month of usage will be February – the bill for that usage will appear in the chart above under “March”.

Daily Electricity Usage

Below is a graph showing daily electricity consumption for our apartment. The data goes back only as far as part of July 2016.

Attempts at Reducing the Electrical Bill

In attempt to reduce our whopping winter bills, we tried the following:

  • Up to about April 2016, we left our heat on even when we were away from the apartment. After that time, we started turning off the heat when we left.
  • Acquiring 2 oil-based space heaters, and using them instead of the AC units connected to the thermostats.
  • On Sept. 11, 2016, we bought heat insulating curtains for the large windows in the living room. The idea was to prevent heat leakage through the glass.
  • Sometime around January 2017, I taped any cracks in all windows using insulating tape. I also taped the edges of the balcony door with insulating foam tape.

Unfortunately, despite all the extra expense and work we put into saving energy, our electrical bills were even higher in 2017 than they were in 2016.

The 2017 bills for May, June and July were higher than in the previous year. This might be due to the fact that the 2016 and 2017 bills cover different date ranges (April 15-May 16 in 2016, April 1-April 20 in 2017). It is possible to get weather data for that timespan from the government of Canada and compare downtown temperatures. A quick look at the May data shows that, indeed, days in the 2017 billing span were colder than in the one in the billing span for the previous year.

Basic Information About Our Unit

The company serving this apartment building is Wyse Meter Solutions.

Our unit is electrically heated, is submetered, and is hooked up to 2 meters. That means that we have 2 thermostats that we can use to heat/cool the unit as much as we wish. We are billed directly for our own electricity consumption.

We do not have any unusually electricity hungry appliances. Just the standard fridge, microwave and oven. The fridge is old and may be less efficient than average.

The unit is a 2 bedroom corner unit. That means that 2 sides of the unit have walls that are exposed directly to the outside, and an additional wall of the Master Bedroom is shared with the emergency stairwell – which is colder than the corridor. This kind of unit would have higher heating costs than a unit in the middle of the building. That kind of unit would have only 1 wall facing the outdoors.

Proof – electrical bill pictures

For reference, here are pictures of the electrical bills who’s data I analyzed in this post:


Now share your story…

How does your electrical bill compare to what you’ve seen above?

If you live in an apartment, use the Comment field below to share your electrical charges. Please indicate which month the charge is for, which area you live in, and the number of bedrooms.

If you have your own website and have posted about this topic, please share a link to your article!

Creating a Choropleth with Google Maps and GeoJSON

This tutorial will teach you how to create a choropleth map that can be posted on a website. A choropleth map – also known as a heatmap – allows you to divide a map into geographical areas, and give each area a color that corresponds to a measurement in that area. For example, you can visualize crime in a city by making a neighbourhood dark red if more than 40 criminal acts occured there in the past year, yellow if 20-39 criminal acts took place, and green if 19 or less crimes were committed (see here for an example). Choropleths allow you to discover links between pieces of data and their geographic distribution.

Here is an example of what a choropleth looks like:


Here is the final visualization that we will produce at the end of this tutorial.

The Technology Involved

To build our Google Maps heatmap, we will be using:

Google Maps API for generating a map and visualizing colored areas. We will be adding a Data Layer on top of a regular map – here is a tutorial that gives more information about the Data Layer.

QGIS software for processing and transforming map coordinates, and for enriching a map with custom data. QGis is an open-source tool for working with cartographic data, and you can download it at the QGis site.

GeoJSON for feeding rich data into the Google Maps API. GeoJSON is a way of representing map data in a compact way, especially when publishing maps on websites. Wikipedia has a good overview of GeoJSON.

Intended Audience

This tutorial is intended for people who need to generate a map with colored areas for publication on a website. A degree of HTML/Javascript knowledge is good, but you do not need to know anything about mapping or GIS (Geographical Information Systems) in order to follow along. This document will be especially useful if you need to create a choropleth for areas outside of the USA. If you are in the USA, it will probably be faster to do your mapping with tools like Google Fusion Tables or Choropleth.us. Finally, if you have the option of paying for a mapping tool, then CartoDB or Tableau could allow you to generate the map faster.

Feel free to skip sections that deal with areas that don’t interest you – such as the sections on acquiring a data set, processing the data in Excel, or setting up the map with Javascript.

1.Download statistics data

The data set that we’ll be visualizing comes from Statistics Canada. This health data was collected as part of a survey called the Canadian Community Health Survey (CCHS), and some other sources.

Download the data by going to CANSIM (http://www5.statcan.gc.ca/cansim/a01?lang=eng) and entering table number 105-0501 in the search box. You’ll see a summary of the data we’re about to download. Next, go to the “Download” tab at the top. You’ll have 2 options:

  • Download only the summary data that you saw on the previous screen.
  • Download the entire data set, with all columns

Choose the second option


Processing the Data

When you open the CSV file you downloaded, you’ll see that health measures are broken out according to “Health Regions” ( http://www12.statcan.gc.ca/health-sante/82-228/help-aide/Q01.cfm?Lang=E ) – these are administrative areas that are smaller than a province but bigger than a city.

Column A contains a numeric code for each region.


Let’s single out the health attribute “Sense of community belonging (83)” for visualization in our choropleth. This measure is the percentage of the population aged 12 and over who reported their sense of belonging to their local community as being “very strong” or “somewhat strong”.

We are aiming to create a very simple CSV file that contains:

  • Health Region code
  • Health Region name
  • The percentage of people who replied “very strong” or “somewhat strong” to the “Sense of community belonging (83)” question.

To capture theat last item, we need to make sure that we’re getting the data under row N – “Rate_Total” – for the question we are exploring. The file contains data that deals with specific age ranges, or greater regions – like the province of Nova Scotia – which is a summary of Health Region results. We will not be using these other fields.

Cleaning up the data

In order to build our simplified CSV, we are going to filter the data according to certain criteria. Select the range from A1 to N6262, click “Data” at the top of the Excel Ribbon, and click the funnel shaped “Filter” icon.

Next, click on the little funnel icon beside the column names and filter out the data as follows:

  • Column A: hide any codes below 1000 (these are redundant groupings of Health Regions. We want to work only with the HRs)
  • Column E: keep only the item named “Sense of community belonging (83)”


Finally, create a new spreadsheet, and paste the visible values in columns A, B and N into it. Change the row heading “Rate_Total” to “Feel Belonging”, so that we can present the numbers clearly as the percentage of people who feel community belonging in each Health Region.

Save the file as a CSV. Here is the processed CSV file that I ended up with: CCHS-community-belonging-metric.csv .

Double check your work

Your new CSV file should have 182 health regions (plus one row for the column headings). Only 159 of the health regions will have any value under the “Feel Belonging” column. The file will look something like this:


Further exploration

If you’d like to understand exactly what each item in the spreadsheet means, and how it was gathered, you should read the following documents:

2. Get QGIS and Health Region boundaries

Now that we have a neat CSV that describes how people in each Health Region feel about their community, we need to start thinking about visualizing this data on the map.

At this point, we’ve gone as far as Excel alone can take us.

In order to visualize data on a map, you need to download a GIS software package. The industry standard software is called ArcGIShttp://www.arcgis.com/ , and a search of their online store ( http://store.esri.com/esri/) reveals a price range from a $100 noncommercial license to a $2,500 online subscription.

For the purposes of this tutorial, we will use the free and open source QGIS software. Download and install the latest version from http://qgis.org/en/site/ .

The examples and screenshots you will see here come from QGIS version 2.8.1 (Wien).

Now that you have QGIS on your computer, you need to let it know what the outline of each Health Region looks like on a map. QGIS has no “out of the box” way of knowing where HR boundaries start and end, so we need to download these definitions from Canstat.

Visit the following URL: http://www.statcan.gc.ca/pub/82-402-x/2013002/reg-eng.htm

Download the topmost “Canada” file under the “MapInfo” section. These are the outlines of the HRs from October 2013.


At this point, if your aim is to do a proper analysis of the CCHS data, then you will need to return to your CSV file and remove any HRs which are present in the Excel data but are not present inside the MapInfo file.

To see the full list of attribute IDs in the MapInfo file, open the file in QGIS as a vector layer (this is explained below). Then, right click on the layer and select “Save As”. In the settings, save it as a CSV file – only the text attributes related to each HR will be saved. You can then use the Excel vlookup() function to detect regions that are present in the Excel CCHS file but not in the MapInfo file.

3. Create QGIS File + CSV mapping

Let’s review what we have so far:

We have a CSV file with a metric (“sense of community belonging”) that ties to each numbered Health Region, we have a MapInfo file that describes the shape of each HR on a map, and we have QGIS – which we’ll use to combine the two on one map.

Start by opening QGIS. Your goal is to create a Layer, which will contain the data from the MapInfo file that you downloaded from Statscan.

Go to “Layer” -> “Add Layer” -> “Add Vector Layer”. Browse for the Health Region file you downloaded, and make sure to select “Mapinfo File” from the file extension selector at the bottom right.

The official instructions are here: (http://docs.qgis.org/testing/en/docs/user_manual/working_with_vector/supported_data.html#loading-a-mapinfo-layer)

Your screen should look something like this:


Combining the geographic information with the CSV data

Just like we have loaded in a “Vector Layer” with shapes, we will load in a “Delimited Text Layer” with attribute data. Before we can do that, we need to create a special file that will tell QGIS about the type of data that’s in each column of our CSV (is it text, integer, or floating point?).

Follow the instructions here to create this file, with a “CSVT” extension: http://anitagraser.com/2011/03/07/how-to-specify-data-types-of-csv-columns-for-use-in-qgis/

The CSVT file for our basic CSV file will contain the following content:


The HR code is an integer, the English name of the HR is a string, and the percentage of people who feel a sense of community belonging is a decimal (real) number with 2 digits to the right of the decimal point.

Make sure that the CSVT file name is the same as the CSV filename, aside from the extension. QGIS needs them to be the names to match up.

Once the CSVT file exists, go back into QGIS and load up the CSV. You should do this by going to “Layer” -> “Add Layer” -> “Add Delimited Text Layer…” Choose the CSV file, and set up the window that comes up with a CSV file format as follows:

  1. Choose an encoding of CP1252. I needed to choose this encoding, on a Windows 7 machine, in order for French language names to appear properly. You can also leave it as UTF8 if you are working with English-only data.
  2. Indicate that the first row of the file contains field names, not data.
  3. Specify “No geometry” as the final option.

When you finish, a second layer should appear in the Layer view on the left sidebar:


Connect the Data and the Vectors

In order to tie in the “community belonging” metric to a specific shape, we need to join these two layers.

Follow the following steps:

  1. Right click on the vector layer (it will be the one with a colored square next to it). Click “Properties”.
  2. Click on the “Joins” option towards the bottom on the left. This is where you attach supplemental information to the geometric figures that describe Health Regions.
  3. Click the green plus sign to add a new Join.
  4. Choose the CSV data layer as the “join layer”, and use the “Code” and “HR_UID” fields as the join fields. These are the two fields, in the CSV and the Vector Layer that have the Health Region ID.
  5. Press “OK” to confirm your settings.

Let’s confirm that the join worked:

Go back to the window where you chose the “Join” icon. Above it, you will find an icon called “Fields”.

Verify that you can see a field called “CCHS-community-belonging-metric_Feel Belonging. That would indicate that our vector layer, which came from a MapInfo file, now contains data from our CSV file.


More explanations of how to join CSV and Vector data:

Ujaval Gandhi http://www.qgistutorials.com/en/docs/performing_table_joins.html

Sake Wagenaar http://www.qgis.nl/2012/07/13/koppelen-van-data-uit-csv-bestand/?lang=en

4. Simplify the map – the tools that didn’t work

Save your work as a QGIS “QGS” file.

As you’ve been working on the map, you might have noticed that it is highly detailed. This is fine on our local computer, but is too much detail for passing to the Google Maps API. The smaller you can make your map file, the faster it will load on your site.

Below are pictures of a coastline that starts out with maximum complexity, and gets gradually more simplified. That last stage of simplifications is what we want for our map’s Health Region borders.

2015-06-06_0-47-27 2015-06-06_0-47-43 2015-06-06_0-47-56

Notes on built-in QGIS simplification

There are two built-in options for simplifying geography in QGIS, and a third option which is a third-party plugin. I found all 3 options unworkable for simplifying the Health Regions, but I’d like to list them here for completeness – just in case they will work for your projects. Click here to skip and read about the tool that did work.

The “Simplify Geometries” tool

You can access this tool by going to “Vector” -> “Geometry Tools” -> “Simplify Geometries…”


My main challenge with this tool was that the simplification algorithm did not give me the ability to fine tune the degree of simplification on the fly. The edges of the Health Regions were either not simplified enough or were over-simplified, resulting in strange jagged edges like the following:



GRASS is an open-source toolset that comes bundled with QGIS. You can read more about it here: http://grass.osgeo.org/

The simplification module of GRASS is called “v.generalize”. You can access it in QGIS by going to “Processing” -> “Toolbox”. A bar will appear on the right of the screen, simply enter “v.generalize” into the search form.


My difficulty with this tool, aside from finding the “right” simplification settings, is that it works very slowly and sometimes throws up errors. In certain instances, I can’t see any difference between the original and the simplified polygons. I was using GRASS version 6 though, and you might see better results if you download the latest version 7 package.

The SimpliPy plugin

This is a 3rd party plugin built with Python. SimpliPy’s homepage is here: https://plugins.qgis.org/plugins/simplipy/

You would install it in QGIS by going to “Plugins” -> “Manage and Install Plugins”, and searching for SimpliPy.

My issue with this plugin is that, at times, it throws up errors that are unclear and uninformative. When an error comes up, the only way to re-run the plugin is to totally restart QGIS. Also, I was not able to see the layer which contains the simplified polygons that this tool generates.

5. Simplifying with Mapshaper

You are welcome to try out the 3 simplification methods listed above. They did not meet my needs, so I had to resort to a 4th method: a tool called mapshaper.org (http://www.mapshaper.org/).

Mapshaper is a web-based tool, which is also available for you to download and run locally on Node.js.

To simplify our map, we start by exporting our .QGS file (with the joined CSV attributes) as a .SHP file.

Before we can save our map as .SHP, we need to put the date into a specific “Projection” – we need to create a new Coordinate Reference System (CRS). This projection is the formula we use to take a 3 dimensional surface (the earth) and flatten it out to a 2 dimensional one (the screen).

Statistics Canada supplies us with the proper projection data that they used for the map, in the Projection Information section at http://www.statcan.gc.ca/pub/82-402-x/2015001/gui-eng.htm#a5

Unfortuately, I do not know how to set up a custom projection according to these parameters, so I ended up going to spatialreference.org, a website that records common projections and the software settings that can reproduce them.

The projection “NAD83 / Statistics Canada Lambert” seems like the standard one used by Statscan: http://spatialreference.org/ref/epsg/3347/

Now, in order to create this new projection inside QGIS, we follow some of the steps at http://gis.stackexchange.com/questions/20566/how-to-define-new-custom-projections-in-qgis/20568 to do the following:

In QGIS, go to “Settings” -> “Custom CRS”. Then, on the Spatialreference.org page, click on the “Proj4” link and copy the string you get.

Back in QGIS, name your new projection and paste in the Proj4 string:


For reference, the string is:

+proj=lcc +lat_1=49 +lat_2=77 +lat_0=63.390675 +lon_0=-91.86666666666666 +x_0=6200000 +y_0=3000000 +ellps=GRS80 +datum=NAD83 +units=m +no_defs

Hit “Ok” and you have your new CRS projection. If you look at the Proj4 string, it seems to exactly correspond to the parameters that are set out on the Statscan site.

Next, we will save the map as an ESRI .SHP file with our new projection.

Right click on the vector layer in QGIS, and click “Save As”.


In the Save menu, choose to save the file as an “ESRI Shapefile”. Choose the projection you just created, and save the file. Make sure to wait while QGIS is “thinking” during the save process – saving to the hard drive takes a while.


Next, go to Mapshaper.org. Choose “Visvalingam / weighted area” as your simplification method, and check the “Repair intersections” checkbox. Next, upload the .SHP file that you saved in the previous step.

Adjust the slider along the top to simplify the figures:


On the upper right, click the “Repair” link to repair any shape overlaps that have been introduced by the simplification process.


Export the file as a Shapefile. You will get a zip file with 3 files: .shp, .dbf and .shx.

Open the .SHP file in QGIS. You will have to select a CRS projection, and you should select the Statscan projection we created before. You will see a simplified map, but none of your “community sense of belonging” attributes will be in sight. Mapshaper has no ability to pick up on these attributes, so the map it generated is missing our community belonging data.

Our next step will be to re-add the attributes to the simplified file.

We do this by copying the .DBF file that QGIS generated when we originally saved the map as a .SHP file. The .DBF file contains all the data, while the .SHP file just contains geometric figures. (read more here: http://en.wikipedia.org/wiki/Shapefile ). Rename the .DBF file that was generated by Mapshaper to have “_mapshaper” in the filename. Rename the copied, old .DBF file to exactly match the name of your newly generated simplified .SHP file (be sure to keep the extension as .DBF).

You’re done – if you open the new .SHP file with QGIS, you will see the joined attribute data.

6. Save as GeoJSON with another projection

Open a new QGIS project and add the simplified .SHP file as a new vector layer. Now that it is open, we need to export the map and attributes as a GeoJSON file. GeoJSON is a file format that is taken in by Google Maps in order to represent our Health Regions.

Right click on the vector layer, and click “Save As”. Here, we will choose our Format as “GeoJSON” and our projection as “EPSG:4326“. This is the CRS projection for Google Maps, accordign to http://gis.stackexchange.com/a/60438 and https://developers.google.com/kml/articles/vector.

In order to find the EPSG:4326 projection, you may need to expand a menu of projections and to enter “4326” in the search box.


Next, set the Encoding to “UTF-8”. This will convert our special French language characters from the Windows-1252 encoding to an Internet-compatible encoding that will be understandable to any computer.

Save the GeoJSON file.

Here is the file that I ended up with: CCHS-geojson.geojson.

7. Creating the Google Maps page

Now that you have the areas of Health Regions, and the “sense of community belonging” attributes in one GeoJSON file, it is time to visualize this data on top of a map.

In order to create a Google Maps map, you don’t have to sign up anywhere. However, if you’d like to be able to see viewership statistics for your map, you can sign up for a Google Maps API key (https://developers.google.com/maps/signup)

Creating a “baseline” map

Start by creating a simple standalone HTML page with a <div> element that’ll contain our Google Map:


		<style type="text/css">
			 html, body, #map-canvas { height: 100%; margin: 0; padding: 0;}

		<title>CCHS Map</title>
		<div id=”map-canvas”></div>

Let’s add in the Google Maps javascript library inside the <head> tag:

<script type="text/javascript" src="https://maps.googleapis.com/maps/api/js"></script>

Next, create a basic map centered on Canada. This is a necessary step before we can overlay our data and Health Region outlines on top of it.

The different map settings are explained on the “Map Types” page in the documentation – https://developers.google.com/maps/documentation/javascript/maptypes#BasicMapTypes. The controls that overlay the map (like pan and zoom) are documented here: https://developers.google.com/maps/documentation/javascript/controls .

A brief explanation is also included in the comments that are inside the code.

<script type="text/javascript">

function initialize() {
 	var mapOptions = {
 		center: { lat: 55.293277, lng: -98.3730469},	// center on a latitude and longitude that results in Canada neatly fitting in the browser
 		zoom: 5, 										// the map’s zoom level
 		mapTypeId: google.maps.MapTypeId.ROADMAP, 		// this is a map that shows roads, not a satellite imagery map
 		panControl: false, 								// hide the pan controls that overlay the map
 		streetViewControl: false, 						// don’t allow visitors to enable street view
 		mapTypeControl: false 							// don’t allow visitors to switch to satellite view
 	var map = new google.maps.Map(document.getElementById('map-canvas'),
	mapOptions); // create a new map with the settings in “mapOptions” and then attach it to the DIV element called “map-canvas”


google.maps.event.addDomListener(window, 'load', initialize); // when our document loads, initialize the map creation

Save the file as “CCHS-HTML.html” and open this local file in a browser. You should see something like this:


Loading in the GeoJSON data

Upload your GeoJSON file to your webserver. Then, right after the HTML line where the “map” variable is set, and the Google Map is created, add in the following line:

map.data.loadGeoJson( "http://URL of your GeoJSON file.geojson");

This line will load in the GeoJSON file from your server. If your aim is to view the map on your local computer, try to put the .geojson file in the same directory as your HTML file, and provide the filename instead of a full URL. Make sure to open the HTML file in Firefox, as Chrome has strict cross-origin policy limitations (http://en.wikipedia.org/wiki/Cross-origin_resource_sharing ). If this doesn’t work, you will need to run a local server like XAMPP ( https://www.apachefriends.org/index.html) to serve up the file locally.

Setting the look of our Health Regions

The outlines and positions of our Health Regions are now inside the map – but there is no way of seeing them. In this section, we will outline each area and make it visible on the map. For a detailed explanation of how to do this, refer to the Google documentation at https://developers.google.com/maps/documentation/javascript/datalayer .

Still within the “initialize” function, and after the map.data.loadGeoJson line, we insert the following code:

google.maps.event.addDomListener(window, 'load', initialize);
 	var fcolor = "#ffffff";
 	return {
 		fillColor: fcolor, 					// the polygon fill
 		strokeWeight: 1, 					// width of the border line
 		strokeColor: '#afafaf', 	// color of the HR border
 		fillOpacity: 1, 					// how opaque is the polygon fill? 1 means totally opaque.
 		strokeOpacity: 1, 					// how opaque is the border line
 		zIndex: 0 							// a thorough explanation of what this is http://www.smashingmagazine.com/2009/09/15/the-z-index-css-property-a-comprehensive-look/

For a full reference of what you can control about the look of the Health Regions, see the document at https://developers.google.com/maps/documentation/javascript/3.exp/reference#Data.StyleOptions .

Your map should now look like this:


Styling the Health Regions to highlight when moused over

The next step is to add a dynamic behaviour to our HRs – where each time your mouse hovers over one, it’s name and “Sense of Community Belonging” value is displayed.

First, we add the below line after the above block of code. This code says that whenever we hover over a HR with the mouse, its border will become thick and black. Hovering away will revert the style to our default settings from the previous block of code.

map.data.addListener('mouseover', function(event) {
		{strokeWeight: 2, strokeColor: 'black', zIndex: 1}
map.data.addListener('mouseout', function(event) {

When you hover over an area, the result will look like this:


The next step is to display the name of the Health Region, and the Community Belonging attribute on mouseover. We grab the HR name and attribute from the GeoJSON file using the getProperty method. We display the information inside an InfoWindow object that appears at the location of where our mouse meets the Health Region’s boundary.

Use the following code. Note that the scope of the variable “infoWindow” is outside the two event functions.

// a popup with the Health Region name and the score for Sense of Community Belonging
 var infoWindow = new google.maps.InfoWindow({
 	zIndex: 2
 map.data.addListener('mouseover', function(event) {
 	map.data.overrideStyle(event.feature, {strokeWeight: 2, strokeColor: 'black', zIndex: 1});
 	var healthRegionName = event.feature.getProperty('ENG_LABEL');
 	var communityBelonging = event.feature.getProperty('CCHS-com_1') === null ? "(data missing)" : event.feature.getProperty('CCHS-com_1') + "%";
 	infoWindow.setPosition( event.latLng );
	infoWindow.setOptions( {
 		pixelOffset: {width: 0, height: -3}

 		"Health Region: <b>" + healthRegionName + "</b><br />" + 
 		"Community belonging: <b>" + communityBelonging + "</b>"

 map.data.addListener('mouseout', function(event) {

Mousing over will now look something like this:


Note that the pages at https://divideandconquer.se/2011/09/15/marker-and-polygon-tooltips-in-google-maps-v3/ and https://developers.google.com/maps/documentation/javascript/infowindows were instrumental in creating this code. My code isn’t perfect – it flickers when the mouse hovers over the InfoWindow, and the “pixelOffset” setting is a workaround to make the flickering less noticeable.

Creating the Choropleth coloration

At this point, you are ready to give your choropleth that characteristic coloration that fills each Health Region with a color that corresponds to the percentage of residents who feel intense community belonging.

We will rely on QGIS to tell us which “community belonging” values should correspond to which colors.

Starts by opening the QGIS file that contains the original, unsimplified vector map and the .CSV join. This is the file that ends with the extension “.qgs”. Right click on the layer with the vector map and click “properties”.


Click on the “Style” selection on the left, and select “Graduated” in the dropdown that appears. Now we will be able to set the color of each Health Region according to discrete categories that we’ll define. Next, in the “Column” dropdown, select the field that contains the numeric “Community Belonging” score.


Next, go to “Color ramp” and select the graduated green ramp called “YlGn7” – this will be our chosen color for the HRs. Follow this up by going to the “Classes” field and choosing the number of groups you’d like to break the HRs into. In the example, I chose 7. Finally, in the “Mode” field, we choose the method of grouping – I chose “Quantile (Equal Count)” method. The quantile method tries to distribute the groupings in such a way that there is an equal number of Health Regions in each one of the 7 groups.


A list of colors and intervals will appear in the large blank area underneath the settings. Take a screenshot or record these intervals – you will need them shortly.

For now, press OK. Notice that now, in QGIS, the health region areas are colored according to their Community Belonging values:


Adding color to the Google Map

Now that we have the numeric intervals which will split our HRs into 7 color groups, we can begin coloring the polygons on our Google Map.

Remember our “map.data.setStyle” function which we used to style each Health Area? Let’s modify it to look like this:

map.data.setStyle(function(feature) {
 	var belongingScore = feature.getProperty('CCHS-com_1'); 
 	var fcolor = ""; // polygon fill color
 	switch (true) {
 		case ( belongingScore == 0 || belongingScore === null ): // in case of no value
 				fcolor = '#d4d4d4'; break;
 		case ( belongingScore <= 62.1 ): fcolor = 'one'; break;
 		case ( belongingScore <= 67.3): fcolor = 'two'; break;
 		case ( belongingScore <= 69.8 ): fcolor = 'three'; break;
 		case ( belongingScore <= 71.5 ): fcolor = 'four'; break;
 		case ( belongingScore <= 74.6 ): fcolor = 'five'; break;
 		case ( belongingScore <= 77.9 ): fcolor = 'six'; break;
 		case ( belongingScore <= 87.3 ): fcolor = 'seven'; break;
 		default: fcolor = '#d4d4d4'; break;
	return {
 		fillColor: fcolor,
 		strokeWeight: 1,
 		strokeColor: '#afafaf',
 		fillOpacity: 1,
 		strokeOpacity: 1,
 		zIndex: 0

What is happening is that this function gets called for every HR. We fetch the community belonging value that’s associated with it, and we use the ranges that we got from QGIS to color the polygon with color “one”, “two” and so on. For HRs that do not have a community belonging value, we set the default color to #d4d4d4′.

Now, we need to set valid HTML colors for each of our HR groupings. We can’t just use values like “one” and “two”.

There are two great tools for generating a valid list of 7 colors. My favourite is “Colorpicker for data” ( http://tristen.ca/hcl-picker/#/hlc/7/0.68/233B30/F4EB89). Another very popular tool is ColoBrewer (http://colorbrewer2.org/). Why are there specialized tools for choosing choropleth colors? The reason is that breaking out color gradations by their numeric HSV colors does not result in colors that are perceived as being evenly spaced out. This article explains the concept thoroughly: http://vis4.net/blog/posts/avoid-equidistant-hsv-colors/#picker .

In the “Colorpicker for data” tool, I requested 7 discrete colors and chose the colors that are shown in this screenshot:


You can also preview how your chosen colors look on a sample choropleth by clicking on the “Visualized” tab at the top.


Enter the HTML color values into your code:

	case ( belongingScore <= 62.1 ): fcolor = '#F4EB89'; break;
	case ( belongingScore <= 67.3): fcolor = '#C4CE7B'; break;
	case ( belongingScore <= 69.8 ): fcolor = '#99B16E'; break;
	case ( belongingScore <= 71.5 ): fcolor = '#749361'; break;
	case ( belongingScore <= 74.6 ): fcolor = '#547553'; break;
	case ( belongingScore <= 77.9 ): fcolor = '#395842'; break;
	case ( belongingScore <= 87.3 ): fcolor = '#233B30'; break;

Save, and your map will now appear something like this:


Have a look at the final result as this full webpage with the finished Google Maps Choropleth.

Or, interact with this small version of the map:

Extra Notes

If you are thinking of visualizing the actual Canadian Community Health Data

The instructions in this document were meant to explain how to create a general choropleth. There are a few discrepancies between what you read, and what you need to actually visualize the CCHS data.

For starters, there are several hierarchical levels of Health Region. There are situations where a parent region contains full data, but is not found on the map. Instead, its child regions will appear on the map – but will have incomplete data in CANSIM. (See NS region 1230 and its 2 children as an example: http://www23.statcan.gc.ca/imdb/p3VD.pl?Function=getVD&TVD=139942&CVD=139944&CPV=1230&CST=25022013&CLV=2&MLV=3 )

Next, there is the matter of child Health Regions (like individual large cities) which appear in the Excel CCHS data, but do not have a boundary defined in the MapInfo file from Statscan. This has been discussed earlier, and you will have to remove regions that can’t be visualized. If you leave these un-visualizable regions in your CSV attribute file, then those “Graduated” color ranges will be incorrect. This is what happens in our example – our color groups are broken into 7 buckets with an equal number of HRs, but some HRs can’t be visualized, so the map will not actually show an equal number of regions in each color range.

If you’d like to hide distracting map areas

In our example, we are focusing on Canada and seeing the USA on our map is distracting. It turns out that it is a herculean challenge to gray out the regions we are not interested in, in Google Maps.

There is a tool that can help you outline a region, and gray out everything around it: http://maps.vasile.ch/geomask/

There is a great article with examples as to how to do this another way, by Will Cadell: http://www.sparkgeo.com/labs/focus/

And finally, a discussion that might help: http://stackoverflow.com/questions/26538676/how-to-invert-kml-so-that-area-outside-of-polygon-is-highlighted


Thanks go to Michael Kuzmin (kuzmin.ca) for fact-checking this post.


Copyright © 2021 Jacob Filipp

Theme by Anders NorenUp ↑