Tools

Here is a listing of some tools and programs that I am skilled with.

Microsoft Excel

I am a big fan of Excel because it is so flexible. I use Excel at an advanced level, and have even taught Excel tricks to a team of colleagues. My experience includes using functions, Excel scripting with VBA macros, coding custom

functions, and using pivot tables. I am also familiar with using "Solver" in order to approximate solutions to difficult problems. Specific projects include:

  • An ad-network performance forecast that included unique forecasts for 18 ad categories running across 19 types of publishers.
  • A series of macros that produced aesthetic weekly reports for a large publisher, starting from a flat CSV list of raw data.
  • Custom macros that cut down 30 minutes of daily manual report work to a 5 minute routine.
  • A prototype for a web analytics model which calculates the influence of certain search keywords on engagement. This model is unique because it squeezes more insights out of a limited set of data, by extracting individual words from long search phrases.
  • A relational database bult using Excel lookup functions and custom coded functions. This data repository drove a mandatory monthly report that drew on 12 different data sources.
  • A marketing dashboard that involved automating 3 different systems (Excel, WebTrends and the Windows interface). Excel was heavily used to build both the visual presentation of the dashboard, and a model that showed weekly tracking of performance vs. goal.
    marketing-dashboard

    Marketing dashboard produced through 3 kinds of automation

  • Various web analytics models. Especially ones that break down user behaviour according to the kind of keywords they used to find the site (branded vs. nonbranded terms, educational vs. purchase intent etc.)
  • PowerPoint reports that had dynamically embedded Excel graphs – changing the data in the central Excel repository would automatically push the changes to PP, thus reducing report authoring time.
  • Basic financial modelling (discounted cash flow; tying together IS, BS and Cash Flow statements), forecasting, seasonailty adjustments and correlation analysis.

 

Google Analytics

Google Analytics is a very powerful tool that is easily accessible to everyone – because it is free. Many companies implement Google Analytics when they set up their site, and that means that a web analyst will have data to work with since day 1. The downside of Google Analytics is that sometimes not enough planning goes into its implementation ( ex: just putting a tracking code into the sitewide header).

As a result, too much data / irrelevant data is collected, and this makes it difficult to cut through the noise and get to the insights.

My technical work with GA has included:

  • Using Javascript to get clicks and other special events tracked in Google Analytics.
  • Custom dashboards that present relevant information at a glance and speed up reporting.
  • Data filters that reduce irrelevant information.
  • Filters that expose additional domain information to the analysts.
  • Custom segments that answer questions like "how does the behaviour of my most loyal customers differ from that of the average customer".
  • Conversion funnels and split tests to determine the best performing design/offer/messaging.
  • Scheduled reporting to make analytics more "self serve" and predictable.
  • Setting up tracking from the media source down to the conversion (for performance attribution and accountability).

PHP

PHP is a programming language used mostly for web development. At least 22.9 million websites use PHP (according to builtwith.com). I have programmed in C, C++, Perl and Java before and PHP allows me to take my ideas from concept to production faster than any of these other languages. I have been programming in PHP since 2007.

Over the years, my PHP projects have included a custom addon to the Prosper 202 tracking platform, a dictionary site, a web app that allows users to trace all the redirections a link goes through, programs to find unregistered domain names corresponding to English words and many, many others.

One significant coding project was a network of 193 datafeed-driven eCommerce sites that I created as an independent venture. Coding included:

  • An eCommerce front-end with the ability to show products, categories, subcategories and related products.
  • A backend that allowed 1 person to quickly control hundreds of sites. This resulted in features that were focused on bulk-scale changes and efficient product searching.
  • Datafeed integrations with Shareasale.com and CJ.com, and a modular structure that allowed for easy integration with other affiliate networks.
  • Features that automated the process of creating categories for a flat list of products. The main challenge was making an algorithm that created categories that resulted in a fairly even product distribution to each category.
  • Code that sped up the process of evaluating datafeed partners (on metrics such as keyword competitiveness, presence of duplicate sites etc.)
  • Code that simplified the selection of relevant domain names, automatically checking ~800 domains at a time for availability.
  • Modifications to the open-source Open Web Analytics system that tracked performance across all sites.
  • Coding A/B split tests into the system. This involved traffic splitting, analytics customizations and setting cookies (so the same person saw the same page version on different visits.)

WebTrends and OWA

WebTrends is an analytics package, very similar to Google Analytics and Omniture Catalyst. For half a year, I was the sole administrator of an on-site installation of WebTrends (a version that is hosted locally, not SaaS). During that time, I have set up custom reports, custom profiles, optimized profile structure to take care of some of WebTrend's rarely inherent problems, and created automated reports. As for OWA – it stands for Open Web Analytics and is a free, open source analytics system. I have used OWA to track my network of 193 sites – it was chosen for its capacity to track many sites without a lengthy setup for each new site. I used the system for tracking split tests and for coding in custom reports.

Javascript, HTML and CSS

My first website was a personal site back in 2003 – built before blogs and WordPress came on the scene. Since then, I have used CSS, HTML and Javascript to create the interfaces for an educational physics site, a dictionary definition site, a site that showed instant previews of custom t-shirts and front-end code for numerous paid projects. More recently, I have been using the jQuery framework for quicker and more powerful work with Javascript.

Visually, my designs are not as beautiful as those of a professional graphic designer (like Mike Hutchinson, who I recommend) – they are simple and practical. I mostly apply my CSS and HTML knowldege towards fixing misalignments and errors in packages such as WordPress and in in-house software. Javascript is very useful when it comes to customizing analytics packages – through special events, timeouts and access to browser objects it is possible to track data that is invisible to analytics systems out of the box.

Adobe Illustrator and Photoshop

When producing both online and offline marketing materials, I find it important to be familiar with at least one image manipulation program and one illustration program. My programs of choice are Photoshop and Illustrator. When it comes to Illustrator, I have created logos, print ads, flyers and have even set up patterns that were laser-cut in wood. In Photoshop, I am able to manipulate images to script Photoshop using Javascript (similar to how Excel can be scripted with VBA.)

Wordpress

I am experienced with setting up WordPress installations from scratch, and in making them look like blogs, regular sites or magazines. My experience with WordPress includes creating a basic theme, developing a plugin for a client, and various troubleshooting and customization at 2 other client sites and 3 personal WP-based sites. My favourite source for WordPress themes is a small site called WPshower.

SQL / MySQL

As part of site development, I have learned to work with databases and compose SQL database queries. I have been using the MySQL database system since 2008. In addition to common database tasks, I can also do some query profiling and speedups (databases can be incredibly complex when you start tweaking their internals, and it is good to have a certified DBA for these purposes . Even big sites like Reddit have difficulties keeping their DB in line.)

AutoIt

AutoIt is a tool for automating Windows. Anything you can do as a person, you can automate and repeat using AutoIt. This tool is a favourite of mine for speeding up tedious/repetitive tasks when a program does not have a built-in way to automate itself. In one project, I used AutoIt to speed up the creation of a list of international airports and the creation of "alt tag" comments for thousands of images in a SiteCore installation – this saved our migration team several hours of manual work. In another project, I used AutoIt as the "glue" that joined together automated routines running on WebTrends and Excel (ie. passing data between the two, when it wouldn't have been possible otherwise). You can read a bit more about AutoIt on their official site.

Comments are closed.