Gordon Choi breaks down web analytics and tips for data capture, extraction, manipulation and presentation.
As a web analyst, your role requires you to perform four major tasks:
- Trend and data reporting
- Analyzing current online marketing acquisition strategies and exploring new opportunities and/or new strategies
- Understanding on-site visitor behavior and experiences
- Staying connected with the trends and the details
Let’s go through the three phases for the modern day web analyst:
- Data capture
- Data extraction
- Data manipulation and presentation
Phase #1: Data Capture
Assuming your online business mostly happens on your company’s websites, most of the online marketing, search marketing and user behavior activities can be captured with:
Web Log Files – You must be very familiar with the principles of how web log files capture data and what data is available. Web log files give you the ability to “record” all the files that were loaded by the user when they accessed your websites, and you can easily see which “components” of your websites aren’t responding to user requests.
Web Analytics – Most websites globally use some analytics tools. A typical web analytics tool provides graphical user interfaces (GUI) and allows you to quickly see the data trends of your users. Reports can be downloaded as spreadsheets, text files or sometimes even as PDF files.
- Free & global: Google Analytics
- Free & local (in China): Baidu Tongji, CNZZ Tongji, (Read more...) Analytics
- Paid & global: Adobe Site Catalyst
- Paid & local (in China): 99Click
Search Marketing Platforms – Some websites make use of third-party search marketing management and tracking platforms such as Kenshoo, Marin Software, or Adobe AdLens (formerly called Efficient Frontier). You are required to implement the platform’s tracking scripts onto your websites in order for the data capture to work.
Phase #2: Data Extraction
Once the data is collected, the next phase is to extract the data for the end users. Raw data collected in phase #1 should be converted into reports that are for two major purposes:
- Regular Data Reports: These reports need to be received on a regular basis, whether that's once per day, per week, per month, depending on the report. These reports are categorized into different levels depending on the receiver, so an executive would need high-level reports showing key revenue numbers for each major division of the company. Operational managers would be looking at mid-level data reports that allow them to track “potential problems” in the products that team is responsible for.
- Ad Hoc Data Reports – These reports won’t be processed regularly with any fixed intervals. Normally, ad hoc reports are required for review purposes for any once-off online campaigns. Ad hoc reports are also required when you need to dive deep into the data in order to figure out problems such as why certain KPI numbers have decreased over the past two weeks.
The business intelligence (BI) team may already have processed the raw data and have it converted into reports which are readable. The reports can be obtained under some BI data warehousing systems, for example Cognos or other similar data cubes. These reports can form a large part of the regular reports to a company. Ad hoc reports often require quicker turnaround time.
It is time for you to utilize your SQL query ability in order to extract data directly from the databases whether they are MYSQL databases, Oracle databases, or other databases. Not all the raw data you have captured goes straight into your databases or data warehouses. For this reason, a large of amount of time goes into then working on post data manipulation. For example:
- If your choice of web analytics is a free tool, such as Google Analytics, then your web data is all sitting on Google’s servers. Your options are to either download the data reports onto spreadsheet formats through the Google Analytics online interface, or extract the data reports through Google’s APIs.
- The data from the search marketing platforms may be sitting on your vendor’s servers, and you can only extract the data into some spreadsheet formats.
Phase #3: Data Manipulation & Presentation
Excel – Before you can present pretty graphical reports, the data extracted through ad hoc SQL queries will need to be manipulated. Creating pretty reports through Excel can take a lot of time, therefore, having expert knowledge on how to get the most out of Excel features and formulas will only improve efficiency. These could include: aggregating data with Pivot tables, merging data with Vlookup, manipulating dates with functions like “day”, “month”, etc.
*Image via Shutterstock
This entry passed through the Full-Text RSS service - if this is your content and you're reading it on someone else's site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.