SQL for Marketers and Marketing Analysts



Marketers and Marketing Analysts generally depend on the tools or IT department to help them pull the data for marketing purposes. There comes a time when they can’t just wait around for IT to help them data pulls and manipulations.  They have to know how to do it on their own. This course is for those marketers who would like to know how to use SQL to conduct their marketing analysis.

The course uses MYSQL to show how SQL works but all the leanings and syntax are applicable to other databases as well.  Sign up for SQL for Marketers and Marketing Analysts


Manager, Digital Analytics at MEC, New York City


MEC-Logo.svg_-292x300The Digital Analytics Group is responsible for driving analysis and strategic thinking across all of the MEC client engagements and new business developments. We are looking for people that possess a solid foundation in statistical and economic theory to be applied to the dynamic world of interactive media planning and buying. Although prior experience is not required, a background in media, marketing or consulting is a plus. Digital marketing experience preferred but not required.

Detailed Description:
• Assist clients in defining measurement strategy, vision, and infrastructure requirements to support high level business and measurement objectives and improve client decision making
• Identify effective and innovative solutions to be used for Online Advertising (OLA), Search Engine Marketing (SEM), Direct Marketing (DM), or Website measurement
• Apply various quantitative methods to analyze and interpret information from multiple data sources (database, research and syndicated)
• Create test designs and models, provide insight to analyses, and make recommendations
• Communicate and apply all online and offline capabilities as they relate to other agency disciplines
• Provide functional expertise in database marketing, integrated channel marketing (including web), analytical tools, techniques, and other infrastructure requirements
• Develop, test, and evaluate new tools in one area of functional expertise
• Provide client-team support to ensure high quality deliverables
• Development of customized client reporting models in Excel and Word
• Basic statistical modeling knowledge such as regression and correlation analysis required
• Create logical structures, clear storylines and conclusions
• The Analytics Manager will lead one or more analysts to complete the various levels of work and provide strategic oversight of projects

• MEC places significant importance on qualities such as intellectual curiosity, responsibility, determination, creativity, flexibility, drive, and self-confidence. Must be highly motivated with a strong record of professional achievement.
• Experience in statistical modeling, database marketing, economic analysis, and/or agency/consulting experience a plus.
• Working knowledge of database design, applications, and data flows desired.
• Digital Attribution experience strongly preferred (for example, experience with Visual IQ, Adometry, Convertro)
• Ad serving experience preferred (DoubleClick, DART, Atlas)
• Prior advertising agency experience required
• Prior management experience required
• Basic knowledge of integrated marketing and customer relationship management disciplines desired.
• Possession of excellent written, oral communication and presentation skills.
• Proficient in Excel, Word, PowerPoint.
• Strong working knowledge of relational databases and advanced knowledge with Excel (macros, VBA) and Access; SQL programming experience a plus.
• Candidates with 5+ years of experience will be considered

About MEC
MEC delivers value by creating, implementing and measuring communication solutions that actively engage people with brands.

Media planning and buying : Digital media : Search : Acquisition marketing : Social media : Analytics and insight : Sport, Entertainment and Cause : Multi-cultural : Content : Retail : Integrated planning
Our 4,400 highly talented and motivated people work with domestic and international clients in 84 countries. We are a founding partner of GroupM.

To find out more, visit us at www.mecglobal.com\

Originally posted on http://analyticshire.com/blog/2016/05/manager-digital-analytics-at-mec-new-york-city.html

49 Marketing and Analytics Blog Posts From Around The World This Week


It is hard to keep up with every blog post out there so we have consolidated them in one post. To get this list directly in your mailbox just subscribe to our mailing list.
If you would like to add your blog to this list then just email us at support@optizent.com


Post Title Blogger
3 Blog Posts for Measuring Social Media With Google Analytics Google Analytics Premium
Getting Started with Google Analytics: Part 1 Google Analytics Premium
‘Big’ Still Haunts Analytics All Analytics
New Encryption Process Might Improve Big Data All Analytics
Voice of the Customer With Analytics in the Cloud All Analytics
The New Analytics Experience Takes to the Road All Analytics
An Algorithm Gets Rhythm All Analytics
Where Are Different Languages Spoken? All Analytics
Technology in Healthcare, Chipping Away All Analytics
Database Fundamentals: The First Half of Database Science for Analysts All Analytics
Share Ideas for the Analytics Talent Crunch All Analytics
Bald Eagles Return to the United States All Analytics
Google Tag Manager Lars Johansson
Visualiseringar i realtid Lars Johansson
Office Manager Peter ONeill
Head of Optimisation Peter ONeill
Vectors in R Gunjan
Advanced vectors in R Gunjan
Functions in R Gunjan
NA and NULL in R Gunjan
Data Frames in R Gunjan
Reading file in R Gunjan
Graphics in R Gunjan
Writing R Functions Gunjan
Como as novas tendências de Telecom tem afetado as empresas? Leonardo Naressi
Evento Share 2015 SP Leonardo Naressi
YouTube Tracking In Google Analytics & Google Tag Manager Robbin Steif
Creative Digital Marketing Is Ok, But Test Everything Robbin Steif
Learn to optimize your tag implementation with Google Tag Manager Fundamentals Google Analytics
Remarketing Lists for Search Ads, Powered by Google Analytics Google Analytics
How To Setup Enhanced Ecommerce Impressions Using Scroll Tracking Google Analytics
A Conversation on Google Analytics Integrations Daniel Waisberg
5 questions on Data Storytelling to Brent Dykes, Evangelist for Customer Analytics at Adobe Nicolas Malo
How to Use a Giveaway to Accelerate the Growth of Your Email List Hiten Shah
The Top Five Kissmetrics Reports Every Ecommerce Marketer Needs Hiten Shah
5 Techniques That Will Keep Your Customers from Defecting to the Competition Hiten Shah
5 Essential Ways Marketing Must Change to Support Inside Sales Hiten Shah
How to Use Social Proof to get Better Results from Facebook Ads Hiten Shah
How Brands Can Drive Results with Promoted Pinterest Pins Hiten Shah
A Love Affair: Social Data & Strategy Hiten Shah
Creating an Effective Conversion Optimization Process (Infographic) Hiten Shah
The Top Frustrating Problems with Instagramming for Business – And How to Fix Them! Hiten Shah
How to Find Which Areas of Your Site Need A/B Testing Hiten Shah
Using Tealeaf and Digital Analytics Part 2 – Big Data Segmentation Ryan Ekins
Amazing social experiment in London David Iwanow
Marketing Mobile Apps David Iwanow
Personalization – Challenges Facing Marketers Optimization Today
How To Identify and Avoid Technical SEO Optical Illusions Glenn Gabe
Phantanda – Why The SEO Nuclear Option Is Important For Sites Hit By Phantom 2 and Panda Glenn Gabe

Subscribe to our mailing list

* indicates required

Personalization – Challenges Facing Marketers


This infographic by Experian is based on the survey of Marketers in UK. Lack of resources is the biggest challenge facing marketers today.  It is very surprising to learn that 61% of the UK marketers only use simple data such as First Name and Last Name to do personalization.


Source: https://econsultancy.com/blog/66651-25-most-fascinating-digital-marketing-stats-from-this-week/

100+ Digital Marketing and Growth Hacking Posts That You Might Have Missed

Post Title Blogger
Why You Need to Care about Driverless Cars, AI & Robots All Analytics
Humans Will Keep Jobs, Guiding a Machine All Analytics
Table Constraints and Data Science All Analytics
A2 Radio Wednesday: Analytics Boost Cybersecurity All Analytics
Locating Mount St. Helens With a SAS Map All Analytics
Digital Data World: Living in Your Digital Wake All Analytics
Avoid ‘Big Data Paralysis” – A Guide for Hoteliers All Analytics
Next Challenge: Explain Predictive Analytics All Analytics
Cars Versus Drivers: Who’s Safer? All Analytics
Don’t Mess With Facts, That is if You Have Them All Analytics
Varför Electronic Arts använder en väggmonterad dashboard för att öka intresse och engagemang Lars Johansson
Outfox på partnerdag hos Optimizely Lars Johansson
L3 Analytics announce agreement with easyProperty Peter ONeill
L3 Analytics build partnership with The Consortium Peter ONeill
Possibilidades de análises com a nova API do Facebook Leonardo Naressi
Computadores podem nos ajudar a responder se chocolate faz bem ou mal Leonardo Naressi
Pensando em estratégia para o Dia das Mães Leonardo Naressi
IAB Social Media Insights 2015 Leonardo Naressi
Facebook Track – media & entertainment Leonardo Naressi
O dilema das marcas: retorno às mídias proprietárias ou imersão total nas redes sociais? Leonardo Naressi
Primeiros passos em Digital Analytics Leonardo Naressi
8 Skills CAOs Must Have to Succeed Corry Prohens
To raise money or to bootstrap, that is the question Juan Damia
Understanding what a system is Juan Damia
The meta theory for understanding and measuring systems (Organizations) Juan Damia
Composing parts (Variables) of a Company and their impact in business results Juan Damia
The hidden part of the iceberg Juan Damia
How converting you Startup into a Corporation and not die trying Juan Damia
How to accelerate the flow of money in your company Juan Damia
Top five phrases you hear in the entrepreneur world and their real meaning Juan Damia
The objetive and the real objetive Juan Damia
Eliminating Dumb Ghost Referral Traffic in Google Analytics Robbin Steif
Putting the “Universal” in Analytics: Best Practices Robbin Steif
Identifying and Filtering Internal Traffic from Google Analytics Robbin Steif
Testing the Mighty Mobilegeddon Robbin Steif
Search Marketing Strategies from Game of Thrones Robbin Steif
Tackling Quantitative PR Measurement with AirPR & Google Analytics Google Analytics
DAA San Francisco presents ‘Optimizing Your Analytics Career’ Google Analytics
Supercharge your Google Analytics with SkyGlue Google Analytics
Introducing Search Response and Airings Data in TV Attribution Google Analytics
Supermetrics: Bringing more of your cost data into Google Analytics Google Analytics
Google Analytics Hackathon in London – Retrospective Google Analytics
Marketo Scores 10X Higher Conversion Rate With Google Analytics Google Analytics
New media partnerships and ad solutions for mobile app promotion Google Analytics
Daily Data-Informed Decisions With Google Analytics Premium and Google BigQuery Google Analytics
Paul Muret, VP Engineering, Google Daniel Waisberg
Sagnik Nandy, Distinguished Engineer, Google Analytics Daniel Waisberg
Babak Pahlavan, Director of Product Management, Google Analytics Daniel Waisberg
Justin Cutroni – Analytics Advocate, Google Daniel Waisberg
Avinash Kaushik – Author, Web Analytics 2.0 & Web Analytics: an Hour A Day Daniel Waisberg
Building Google Analytics Powered Widgets Daniel Waisberg
Join Me On My Analytics Journey (Guest Blog) Gabriele Endress
5 questions to Ben Gaines, Senior Product Manager for Adobe Analytics Nicolas Malo
Getting Valuable Marketing Data Out of Google Analytics (Infographic) Hiten Shah
The Retargeting Myth – How Remarketing Could be Sabotaging Your Sales and Conversion Rates Hiten Shah
How to Acquire Customers on a $0 Marketing Budget (Infographic) Hiten Shah
How to Keep Zombies Out of Your Office Hiten Shah
How to Track the Effectiveness of Marketing Campaigns Hiten Shah
Mastering LinkedIn, The Personal Branding Epicenter of the Internet Hiten Shah
7 AdWords Alternatives That Failed, Except For One Hiten Shah
Another Online Marketing Opportunity is Knocking: Hello #HashtagSearch Hiten Shah
4 Common Errors That Kill Data-Driven Decisions Hiten Shah
Infographic: The Science of Brands on Instagram Hiten Shah
7 Habits of highly effective people Raghu Kashyap
B2B – Bathroom to Business Raghu Kashyap
Facebook Like Box Stops 23rd June 2015 David Iwanow
Now We Have Liftoff: The First Examples of Google’s Mobile Friendly Algorithm In Action Glenn Gabe
From SEO Tools To Emulation To Devices, How To Check Smartphone Rankings As Google’s Mobile-Friendly Algorithm Rolls Out Glenn Gabe
Phantom 2 – Analyzing The Google Update That Started On April 29, 2015 Glenn Gabe
Increase the Persuasive Momentum of Your Conversion Funnels Bryan Eisenberg
Mobile Is Not The Next Big Thing – It’s Already the Big Thing Bryan Eisenberg
eMetrics Boston Stephane Hamel
Fascinating Facets Gary Angel
Understanding and Modeling Faceted Search Gary Angel
(Re)Defining the role of the data scientist Gary Angel
Analytic Roles in the Enterprise – What’s with the Taxonomy and Meta-Data Systems? Gary Angel
London’s Digital Analytics Hub Gary Angel
Crikey! What the UK Election Fiasco Tells us about Enterprise Voice of Customer Gary Angel
6 Reasons Why Your Google Analytics Reports Might Be Wrong Google Analytics Premium
How to automatically send the reports via email Google Analytics Premium
A2 Radio: The New World of Predictive Analytics All Analytics
Do I Know You? Personalization Without a Guest Profile All Analytics
Analyzing the Future of Communication All Analytics
When the Business Leader Says, ‘Wrong!’ All Analytics
Who Paid $500K for a US Visa? Over 10,000 People All Analytics
If You Only Knew What Your Doctor Knows All Analytics
Document Your Risk Analytics Governance All Analytics
Paint It Black: A Song Choice or a Background? All Analytics
Applying Analytics to the Industrial Internet of Things All Analytics
Five Keys to Effective Customer Experience Personalization All Analytics
What we learnt at MeasureCamp 6 Peter ONeill
An Employee Owned and Democratic company Peter ONeill
Digital Analytics Consultant Peter ONeill
Google BigQuery Leonardo Naressi
Congresso de Marketing & Vendas 2015 Leonardo Naressi
ADOBE SUMMIT 2015 Leonardo Naressi
Site Search in Google Analytics – With or Without Query Parameters Robbin Steif
3 AdWords Ad Extensions Every Account Needs (And 4 Business-Specific Extensions Too!) Robbin Steif
Safe Browsing and Google Analytics: Keeping More Users Safe, Together Google Analytics
Build a loyal user base with three new Mobile App Analytics reports Google Analytics
Evolving Beyond The Conversion With Neil Hoyne Google Analytics
Solutions Guide for Implementing Google Analytics via Google Tag Manager Google Analytics
Google Analytics Introduces Product Release Notes Google Analytics
Testing Statistical Significance On Google Analytics Data Daniel Waisberg
Guide To The Google Tag Manager API Daniel Waisberg
Best Practices For Table Filters In Google Analytics Daniel Waisberg
Using Google Analytics to Understand Your Customers Daniel Waisberg
Customer Happiness with Google Analytics & FanExam Daniel Waisberg
Adobe Summit 2015: Coming Back Home Gabriele Endress
3 Quick Ways to Make Your App More Lucrative and Loveable Localytics
World Cup Final Shows Strong App Engagement Localytics
4 Must-Try App Marketing Campaigns for eCommerce & Retail Apps Localytics
5 Push Messaging Tips: What Rookies Say vs. Expert App Marketers Localytics
3 Cross-Channel Marketing Campaigns to Try Today Localytics
Is Your App Engaging Enough? [Infographic] Localytics
The 10 Most Telling Quotes About the Future of Mobile Localytics
3 Automated Push Campaigns Every Health App Should Try Localytics
Know Your Users: What Is The Difference Between Profile Data and Behavioral Data? Localytics
6 Surefire Signs You Should Invest in Push and In-App Messaging Localytics
15 Google Analytics Tips to Speed Up Your Website Data Analysis & Optimization Hiten Shah
How to Write Product Descriptions That Will Boost Conversions Hiten Shah
The Four-Step Process For Building a Scalable Sales Machine Hiten Shah
Driving SaaS Growth With Customer Success Hiten Shah
How Onnit Can Use KISSmetrics to Drive Their Growth Hiten Shah
6 Awesome MailChimp Automation Hacks – Lead Scored Emails, Pre-Filled Forms & More! Hiten Shah
How to Never Run Out of Content Marketing Ideas Using AHREFS.com Hiten Shah
Using Cross-sells and Upsells to Increase Revenue (Infographic) Hiten Shah
How to Revive a Dying Social Media Presence Hiten Shah
The Five Fatal Mistakes to Avoid When Building Your Scalable Revenue Machine Hiten Shah
How to create a union between two excel sheets Optimization Today
How To Identify A Mobile Rankings Demotion Using The New Search Impact Report in Google Webmaster Tools Glenn Gabe
Sinister 404s – The Hidden SEO Danger of Returning The Wrong Header Response Code [Case Study] Glenn Gabe
Is Conversion Rate Optimization (CRO) a Dead End? Bryan Eisenberg
50+ Digital Marketing Tools To Grow Your Startup Bryan Eisenberg
Create and Deliver A Killer Presentation: A Conversation with Lea Pica Emer Kirrane
In-Memory Data Structures for Real-Time Personalization Gary Angel
A Data Model for the User Journey Gary Angel
Data Structures for Sequence, Time Between and Pattern Gary Angel
On the Importance of ETL Gary Angel
Digital Analytics – Full Release Jacques Warren
Tipping The Scale – Should You Trust Your Agency with Your Analytics Jacques Warren
Dear Adobe Analytics – please consider these changes in DTM Pradeep SV
TSA: Data Alone Won’t Fix a Broken System All Analytics
How to Make Infectious Diseases Look Better All Analytics
Robots Advancing Into Our Murky Future All Analytics
The Big Data World Lands at Strata All Analytics
Data Science Careers: Plenty of Opportunity All Analytics
Data Scientist: STEM Boys Club Open To Women All Analytics
Analytics Degree or Certificate: Which is better? All Analytics
Lenovo Goes Too Far in a Quest for Data All Analytics
Analytics Claim Some Anti-Fraud Victories All Analytics
An NFL Combine for Data Scientists All Analytics
Äntligen kommer Outfox Your Competition tillbaka! Lars Johansson
A Importância dos Dashboards no Monitoramento de Crises Leonardo Naressi
Como analisar dados de monitoramento em tempo real Leonardo Naressi
IQ Blast – Vol. 8 Issue 20 Corry Prohens
IQ Blast – Vol. 8 Issue 21 Corry Prohens
IQ Blast – Vol. 8 Issue 22 Corry Prohens
Understanding the Google Analytics Cohort Report Justin Cutroni

Don’t Miss A Blog Post, add your email below

Powered by MailChimp

How to create a union between two excel sheets


Tableau does not have a join functionality that works like a SQL Union.  If you have the data spread out in multiple excel sheets and you want to see a concatenated view then you are out of luck as far as default joins are concerned.

Example: If you have
Date and Sales data of Jan in one sheet and Data and Sales data of Feb in another sheet then you can’t join them using date or any other column. Let’s assume that the sheets are so large that you can’t combine them in one sheet, which restricts you to join via tableau only.  How do you create a data view so that you see a combined view of Jan and Feb sales data?  Here is the solution:

In Tableau choose “Microsoft Execl” in connection.  In the next window (the pop up to select the file), choose “Open With Legacy Connection” (it is under “Open” dropdown – see the image).


Next screen you will notice that there is a  new option under the sheets called “New Custom SQL” (see below)

Click on the “New Customer SQL”, you will get an empty popup window. In this window type a SQL shown below, replace [Jan$] and [Feb$] with the names of your sheets. Make sure to add $ to then end of the sheet name.  Make sure both the sheets have same number of column else it won’t work.

Click Ok, and you have your result set:

21 Top Metrics for Online Display Advertising

In this post I am listing the 21 metrics to measure the success of your display advertising.  Most of these are also applicable, with some variation, to other forms of advertising such as Paid Search, Social Media Ads, Print and email. I will cover these other channels and mediums in the future posts.

  1. Impressions – It is the number of times your ad is displayed. The number by itself does not hold much value but it is a metric used to calculate other metrics and KPIs. Keep in mind that an impression does not mean that someone actually saw the ad, it just that the ad was shown on a web page/app.
  2. Reach –This is the number of unique people (generally identified by cookies) that were reached by your ad. This number is always lower than the impressions because your ad is generally shown to same person (cookie) multiple times.
  3. Cost – The total cost of running the ad campaigns.  This is calculated differently by different tools and organizations. Some use actual media cost while other use a fully load number that includes the agency cost, creative cost etc. Whichever number you use, be consistent in your approach. If you are going to do comparisons with CPC models such as Paid Search then I suggest using the actual media cost. Most of the publicly available benchmarks are based on actual media cost and are expressed in CPM (explained later in this list).
  4. Engagement Rate or Interaction Rate– This applies to the Rich Media Ads, where a user can interact with the ad without leaving the Ad unit/widget.  Engagement Rate is the percentage of interactions per impression of the ad unit and is calculated as (Number of Interactions/Total Impressions)*100%.
  5. CPM – This is the cost for 1000 Impressions of the ad unit. Display advertising is generally sold on CPM basis. (For more information on CPM, see  Cost of Advertising: CPM, CPC and eCPM Demystified).
  6. Clicks – Number of clicks on an ad unit that lead to a person leaving the ad unit.  Keep in mind that a click does not mean that a person landed on the intended destination of the banner ad click. There are multiple factors that could lead to a click but not a visit to the destination (I won’t cover those here but am happy to discuss over email or a call).
  7. CTR (Click though rate) – It is the number of Clicks generated per impression of a banner ad. This number is expressed as a percentage. CTR = (click/impressions)*100%
  8. CPC – Cost per Clicks is the cost that you pay for each click.  Generally, display advertising is sold by CMP (see above), you can easily convert the cost in to Cost Per Click to compare it against other channels such as paid search. Cost per click is the effective amount you paid to get a click.  It is calculated by dividing the cost with number of clicks.  CPC = Cost/Clicks. Sometime this number is also referred as eCPC (effective Cost per Click).
  9. Visits – As stated above in the definition of clicks, not every click turns into a person landing on your destination (generally your website). Visits measures the clicks that did end up on your site.  (For more definition of visits, please see Page Views, Visitors, Visits and Hits Demystified)
  10. Visitors – Visitors metric goes one step ahead of the visits and calculates the number of people (as identified by cookies) who ended up on your site as a results of the clicks on the banner ads.
  11. Bounce Rate – Is the percentage of visits that left without taking any actions on your site. It is calculated as Number of Visits with one page view /Total number of visits resulting from the display ads. (Bounce Rate Demystified for further explanation).
  12. Engaged Visit Rate – Generally this is opposite of bounce rate (though you can have your own definitions of engagement).  It measure the quality of the visits arriving from your display advertising. You can calculate Engaged Visits as  (100 – Bounce Rate expressed as percentage).
  13. Cost/Engaged Visit – This is effective cost of each engaged visits. It is calculated as total Cost divided by number of engaged visits.
  14. Page Views/Visit – Page views the number of pages on your site viewed by each visit. With a lot interactions happening on one single page, this metrics is losing its value. However, for now, it is still a valuable metric for ad supported sites.
  15. Cost/Page View – As above, this is valuable metrics for ad supported site to figure out the cost of generating on extra page view.
  16. Conversions – Conversion is defined as the count of action that you want the visitors to take when they arrive from you display ads. Some examples of conversions are – purchase, signup for newsletter, download a whitepaper, sign up for an event, Lead from completions etc.
  17. Conversion Rate  – This is the percentage of visits that resulted in the desired conversion actions.  Conversion Rate = Total conversions/visits*100. If you have more than one conversion actions then you should do this calculation for each one of the action as well for all the actions combined.  In case of Leads, you can take it one step further and calculate not only the “Leads Generation Rate” (Online Conversion Rate) but also Lead Conversion Rate, which is, Leads that convert to a customer divided by total leads generated.
  18. Cost per Conversion – This is the Total Cost divided by the number of conversions achieved from visits coming via display ads.
  19. Revenue – This is total revenue that is directly attributed to the visits coming from display advertising. It is pretty straightforward to calculate in eCommerce but gets a little tricky when you have offline conversions.
  20. Revenue per Visit   – Shows the direct revenue achieved per visit originating from the display advertising. It is calculated as Revenue Generated from Display Ads divided by the total Visits.
  21. Revenue per Page – This is useful for ad supported business models. This is sometimes expressed as RPM (Revenue per thousand impressions of ads) = (Total Ad Revenue/Number of page views) * 1000

Note: In addition to Clicks, you can also looks at View Through and calculate your other related metrics by view through.  View Through is sum of all the cookies that visited a page that showed your ad on it, and then landed on your site. The assumption, in this calculation, is that you landed on the brands site because of that ad exposure.

 Where can you get these metrics from?

  • Impressions, Reach, Cost, Engagement Rate, Clicks, CTR and CPC data is available from your agency or ad server tool.
  • Visits, Visitors, Page Views, Bounce Rate, Engaged Visit Rate, Conversion, and Conversion Rate are available in your Web Analytics tool.
  • Revenue is available in either your Web Analytics tool or other offline sales database.
  • Cost/Conversion, Cost/Engaged Visits, Cost/Page view and Revenue/page are calculated using data from multiple tools.


Originally Posted on http://anilbatra.com/analytics/2014/05/21-metrics-to-measure-online-display-advertising

The Marketer’s Guide to Actionable Data


The Marketer’s Guide to Actionable DataMonetate Marketing Infographics

Understanding Data – Context (Excerpt from Data Points: Visualization That Means Something)

Look up at the night sky, and the stars look like dots on a flat surface. The lack of visual depth makes the translation from sky to paper fairly straightforward, which makes it easier to imagine constellations. Just connect the dots. However, although you perceive stars to be the same distance away from you, they are actually varying light years away.

If you could fly out beyond the stars, what would the constellations look like? This is what Santiago Ortiz wondered as he visualized stars from a different perspective, as shown in Figure 1-25.

The initial view places the stars in a global layout, the way you see them. You look at Earth beyond the stars, but as if they were an equal distance away from the planet.

Zoom in, and you can see constellations how you would from the ground, bundled in a sleeping bag in the mountains, staring up at a clear sky.

The perceived view is fun to see, but flip the switch to show actual distance, and it gets interesting. Stars transition, and the easy-to-distinguish constellations are practically unrecognizable. The data looks different from this new angle.

This is what context can do. It can completely change your perspective on a dataset, and it can help you decide what the numbers represent and how to interpret them. After you do know what the data is about, your understanding helps you find the fascinating bits, which leads to worthwhile visualization.

Figure 1-25

Without context, data is useless, and any visualization you create with it will also be useless. Using data without knowing anything about it, other than the values themselves, is like hearing an abridged quote secondhand and then citing it as a main discussion point in an essay. It might be okay, but you risk finding out later that the speaker meant the opposite of what you thought.

You have to know the who, what, when, where, why, and how — the metadata, or the data about the data — before you can know what the numbers are actually about.

Who: A quote in a major newspaper carries more weight than one from a celebrity gossip site that has a reputation for stretching the truth. Similarly, data from a reputable source typically implies better accuracy than a random online poll.

For example, Gallup, which has measured public opinion since the 1930s, is more reliable than say, someone (for example, me) experimenting with a small, one-off Twitter sample late at night during a short period of time. Whereas the former works to create samples representative of a region, there are unknowns with the latter.

Speaking of which, in addition to who collected the data, who the data is about is also important. Going back to the gumballs, it’s often not financially feasible to collect data about everyone or everything in a population. Most people don’t have time to count and categorize a thousand gumballs, much less a million, so they sample. The key is to sample evenly across the population so that it is representative of the whole. Did the data collectors do that?

How: People often skip methodology because it tends to be complex and for a technical audience, but it’s worth getting to know the gist of how the data of interest was collected.

If you’re the one who collected the data, then you’re good to go, but when you grab a dataset online, provided by someone you’ve never met, how will you know if it’s any good? Do you trust it right away, or do you investigate? You don’t have to know the exact statistical model behind every dataset, but look out for small samples, high margins of error, and unfit assumptions about the subjects, such as indices or rankings that incorporate spotty or unrelated information.

Sometimes people generate indices to measure the quality of life in countries, and a metric like literacy is used as a factor. However, a country might not have up-to-date information on literacy, so the data gatherer simply uses an estimate from a decade earlier. That’s going to cause problems because then the index works only under the assumption that the literacy rate one decade earlier is comparable to the present, which might not be (and probably isn’t) the case.

What: Ultimately, you want to know what your data is about, but before you can do that, you should know what surrounds the numbers. Talk to subject experts, read papers, and study accompanying documentation.

In introduction statistics courses, you typically learn about analysis methods, such as hypothesis testing, regression, and modeling, in a vacuum, because the goal is to learn the math and concepts. But when you get to real-world data, the goal shifts to information gathering. You shift from, “What is in the numbers?” to “What does the data represent in the world; does it make sense; and how does this relate to other data?”

A major mistake is to treat every dataset the same and use the same canned methods and tools. Don’t do that.

When: Most data is linked to time in some way in that it might be a time series, or it’s a snapshot from a specific period. In both cases, you have to know when the data was collected. An estimate made decades ago does not equate to one in the present. This seems obvious, but it’s a common mistake to take old data and pass it off as new because it’s what’s available. Things change, people change, and places change, and so naturally, data changes.

Where: Things can change across cities, states, and countries just as they do over time. For example, it’s best to avoid global generalizations when the data comes from only a few countries. The same logic applies to digital locations. Data from websites, such as Twitter or Facebook, encapsulates the behavior of its users and doesn’t necessarily translate to the physical world.

Although the gap between digital and physical continues to shrink, the space between is still evident. For example, an animated map that represented the “history of the world” based on geotagged Wikipedia, showed popping dots for each entry, in a geographic space. The end of the video is shown in Figure 1-26.

The result is impressive, and there is a correlation to the real-life timeline for sure, but it’s clear that because Wikipedia content is more prominent in English-speaking countries the map shows more in those areas than anywhere else.

Why: Finally, you must know the reason data was collected, mostly as a sanity check for bias. Sometimes data is collected, or even fabricated, to serve an agenda, and you should be wary of these cases. Government and elections might be the first thing that come to mind, but so-called information graphics around the web, filled with keywords and published by sites trying to grab Google juice, have also grown up to be a common culprit. (I fell for these a couple of times in my early days of blogging for FlowingData, but I learned my lesson.)

Learn all you can about your data before anything else, and your analysis and visualization will be better for it. You can then pass what you know on to readers.

Figure 1-26

However, just because you have data doesn’t mean you should make a graphic and share it with the world. Context can help you add a dimension — a layer of information — to your data graphics, but sometimes it means it’s better to hold back because it’s the right thing to do.

In 2010, Gawker Media, which runs large blogs like Lifehacker and Gizmodo, was hacked, and 1.3 million usernames and passwords were leaked. They were downloadable via BitTorrent. The passwords were encrypted, but the hackers cracked about 188,000 of them, which exposed more than 91,000 unique passwords. What would you do with that kind of data?

The mean thing to do would be to highlight usernames with common (read that poor) passwords, or you could go so far as to create an application that guessed passwords, given a username.

A different route might be to highlight just the common passwords, as shown in Figure 1-27. This offers some insight into the data without making it too easy to log in with someone else’s account. It might also serve as a warning to others to change their passwords to something less obvious. You know, something with at least two symbols, a digit, and a mix of lowercase and uppercase letters. Password rules are ridiculous these days. But I digress.

Figure 1-27

With data like the Gawker set, a deep analysis might be interesting, but it could also do more harm than good. In this case, data privacy is more important, so it’s better to limit what you show and look at.

Whether you should use data is not always clear-cut though. Sometimes, the split between what’s right and wrong can be gray, so it’s up to you to make the call. For example, on October 22, 2010, Wikileaks, an online organization that releases private documents and media from anonymous sources, released 391,832 United States Army field reports, now known as the Iraq War Logs. The reports recorded 66,081 civilian deaths out of 109,000 recorded deaths, between 2004 and 2009.

The leak exposed incidents of abuse and erroneous reporting, such as civilian deaths classified as “enemy killed in action.” On the other hand, it can seem unjustified to publish findings about classified data obtained through less than savory means.

Maybe there should be a golden rule for data: Treat others’ data the way you would want your data treated.

In the end, it comes back to what data represents. Data is an abstraction of real life, and real life can be complicated, but if you gather enough context, you can at least put forth a solid effort to make sense of it.

Excerpted with permission from the publisher, Wiley, from Data Points: Visualization That Means Something by Nathan Yau. Copyright © 2013

Author Bio
Nathan Yau
, author of Data Points: Visualization That Means Something, has a PhD in statistics and is a statistical consultant who helps clients make use of their data through visualization. He created the popular site FlowingData.com, and is the author of Visualize This: The FlowingData Guide to Design, Visualization, and Statistics, also published by Wiley.

For more information please visit http://flowingdata.com, and follow the author on Facebook and Twitter


Buy From Amazon: Data Points: Visualization That Means Something

Five Reasons Siegel’s Book “Predictive Analytics” Matters to Experts

My new book — Predictive  Analytics: The Power to Predict Who Will Click, Buy, Lie, or Die  — is a revealing, accessible primer positioned to appeal well outside our industry.
But, if you’re already an expert, here are five reasons to read it nonetheless:

  1. New detailed case studies
  2. Advanced topics (ensembles, uplift, etc.)
  3. An in-depth, startling treatise on privacy
  4. A compendium of 147 mini-case studies
  5. A means to share your field with your family, friends, or  supervisor

I took on a rewarding challenge: sharing with layreaders at  large a complete picture of predictive analytics, from the  way in which it serves actionable value to organizations, down to      the inner workings of predictive modeling. It’s high time the predictive power of data — and how to analytically tap it — be demystified to reveal its intuitive yet awe–inspiring nature. As you and I know, learning from data to predict human behavior is not arcane. Rather, it is a broadly applicable no–brainer. If we  spread the word with an appropriately friendly overview, we’ll readily earn broad buy in, much to the benefit of our blossoming  industry.

More than a string of anecdotes, this book delivers complete   conceptual coverage of the field and places predictive analytics into a worldview perspective, defining its societal and even      cultural context. Although packaged with catchy chapter titles and brand name stories, the conceptual outline is fundamental: 1) deployment, 2) civil liberties, 3) data, 4) core modeling, 5) ensembles, 6) IBM’s Watson, and 7) uplift modeling (aka net lift or persuasion modeling).

Although this pop science, mathless introduction is readable by everyone, you as an expert will also benefit from reading it. While some endorsers proclaim it is “The Freakonomics of big data”    that “reads like a thriller!”, others speak to the    practitioner:

“The definitive book of this industry has arrived. Dr.  Siegel has achieved what few have even attempted: an  accessible, captivating tome on predictive analytics that is a  ‘must read’ for all interested in its potential — and peril.” —Mark Berry, VP, People Insights, ConAgra Foods

“Written in a lively language, full of great quotes,  real-world examples, and case studies, it is a pleasure to  read. The more technical audience will enjoy chapters on The          Ensemble Effect and uplift modeling — both very hot trends. I highly recommend this book!” —Gregory Piatetsky-Shapiro, Editor, KDnuggets; Founder, KDD          Conferences

Here’s a bit more on the five reasons this book matters to you:

1. New case studies. Find detailed stories you have  never before heard from Hewlett-Packard, Chase, and the Obama Campaign. And did you know that John Elder once invested all his  own personal money into a blackbox stock market system of his own design? That’s the opening story of Chapter 1.

2. Advanced topics. Dive into ensemble models, crowdsourcing predictive analytics, uplift modeling (aka net lift or persuasion modeling), text analytics, and social media-based financial indicators. Plus, enjoy a fun yet fairly deep chapter on IBM’s Jeopardy!-playing Watson computer.

3. Privacy and other civil liberty concerns. This ethical realm is so intractable and inconstant, no one is a true expert, in a sense. My treatise on it, a chapter entitled “With Power Comes Responsibility,” addresses the questions: In what ways does predictive analytics fuel the contentious flames surrounding data privacy, raising its already-high stakes? What civil liberty concerns arise beyond privacy per se? What about predictive crime models that help decide who stays in prison?

4. A cross-industry compendium of 147 cases. This comprehensive collection of mini-case studies serves to illustrate just how wide the field’s reach extends. This color insert includes a table for each of the verticals: Personal Life, Marketing, Finance, Healthcare, Crime Fighting, Reliability Modeling, Government and Nonprofit, Human Language  and Thought, and Human Resources. One PhD-level technical book reviewer complimented me by saying, “The tables alone are worth the price of admission.”

5. Share your field of expertise. Would you like your colleagues and manager to better understand the value and potential of your work? Would you enjoy seeing your loved ones        not only learn what the heck it is you do and why it’s so  important, but enjoy it and get excited? Give this book to your  family, friends, and boss.

Author Bio        

Eric Siegel, Ph.D., founder of Predictive Analytics World  and Text Analytics World, and Executive Editor of the Predictive  Analytics Times, makes the how and why of predictive analytics      understandable and captivating. In addition to being the author of   Predictive Analytics: The Power to Predict Who Will Click, Buy,  Lie, or Die, Eric is a former Columbia University professor      who used to sing to his students, and a renowned speaker, educator  and leader in the field.

For more information please visit http://www.thepredictionbook.com