As a data analyst for the CEO in an e-commerce company, what kind of reports are expected of me?

Standard

Someone asked this on Quora and here’s my reply:

As a data analyst, you should work with the CEO (or other decision makers) on a quarterly (or more frequent if possible) and learn about #1 Strategic objectives and initiatives — #2 after that, you should work together and figure out how analytics could help these initiatives.

So why is learning about strategic initiatives from the executives important?

  1. Because analytics could be applied to lot of problems but you and your team might just have limited bandwidth.
  2. Also, executives want to stay focused on what’s important now and so if your priorities align then you are much likely to succeed in the role.

Let’s take an example:

Scenario 1: As a data analyst, you create bunch of reports from let’s say Google Analytics and throw them at the CEO! It has everything like visitor stats, acquisition stats, retention stats, behavior stats, conversion stats among others! Now by doing so, executives might get what they asked for but then they will still have to go through the reports and map it back to their strategic initiatives and figure out the recommendations on their own. Also, executives might not have the time to do this and may miss critical insights.

Scenario 2: You know that the one of the strategic initiate for the quarter is to improve the conversion rate from landing pages to order-complete page from 1.25% to 1.40% — so your analysis that you send to the executive would not only be focused on just that but also include “recommendations” — like it seems that there is a significant drop-off after customers learn about shipping cost. Then the executive could use those recommendations to drive actions. There’s also another benefit: Any ad-hoc data request that doesn’t align with the strategic objectives can be postponed (or de-prioritized) and let’s you focus on what’s most important for the company.

I prefer scenario #2. And try to create this culture wherever I am working. Executives should be open to sharing strategic initiatives at high-level with everyone in the company and help align everyone’s priorities.

Note: This doesn’t mean that you don’t create reports, you still do that for broader consumption — especially the Key Performance indicators that are key for success but you should look at automating most of that and focus on data analysis and find recommendations that the executives could take some action on.

VIEW QUESTION ON QUORA

How to remove line feeds (lf) and character return (cr) from a text field in SQL Server?

Standard

I was doing some data cleaning the other day, I ran into the issue of text fields having line feeds (lf) and character returns (cr) — this creates a lot of issues when you do data import/export. I had run into this problem sometime before as well and didn’t remember what I did back then so I am putting the solution here so it can be referenced later if need be.

To solve this, you need to remove LF, CR and/or combination of both. here’s the T-SQL syntax for SQL Server to do so:

SELECT REPLACE(REPLACE(@YourFieldName, CHAR(10), ' '), CHAR(13), ' ')

if you’re using some other database system then you need to figure out how to identify CR and LF’s — in SQL Server, the Char() function helps do that and there should be something similar for the database system that you’re using.

How do I pursue career in data warehousing?

Standard

Someone asked this on quora, and here’s my reply:

In the data world there are two broad sets of jobs available:

  1. Engineering-oriented: Date engineers, Data Warehousing specialists, Big Data engineer, Business Intelligence engineer— all of these roles are focused on building that data pipeline using code/tools to get the data in some centralized location
  2. Business-oriented: Data Analyst, Data scientist — all of these roles involve using data (from those centralized sources) and helping business leaders make better decisions. *

*smaller companies (or startups) tend to have roles where small teams(or just one person) do it all so the distinction is not that apparent.

So, it seems like you are interested in engineering-oriented roles — the role that focused on building data pipelines. Since you are starting out, I would suggest that you broaden the scope to learn about other tools as well. While data warehousing is still relevant and will be in some form or another for next few years, Industry (especially tech companies) have been slowly moving towards Big Data technologies and you need to be able to adapt to these changes. So learn about data warehousing, may be get a job/internship as a ETL/BI engineer but keep an eye out on other data engineering related tools like Hadoop ecosystem, spark, python, etc.

VIEW QUESTION ON QUORA

What’s a good chart making software that can pull online data?

Standard

So essentially you want to build a *live* chart that pulls data from some online data-store (which changes often).

To do that you can do one of three things:

  1. See if they have an API that you can use — if so, you should be able to use that. If not, continue reading…
  2. Build a web scraper on your own. There are tutorials out there that would help you do so in the language of your choice.
    Chart web scraping data
  3. Use a software service like Import.io | Web Data Platform & Free Web Scraping Tool or Web Scraper — or you could find something else. I have used Import[dot]io and was able to build an API using their service — which i used a data-store for my “charts”

Side note: just make sure you are not violating any terms by scraping the website.

VIEW QUESTION ON QUORA

 

How to create a Histogram in Excel?

Standard

Histogram is a powerful data analysis technique — it let’s you quickly see the distribution of the data you have. So in this post, I am going to list the steps to create histogram in Excel.

It’s a two-step process.

  1. Install “Data Analysis Tool Pak” (free Excel add-in)
  2. Format the data and build the histogram

Step 1: Install Data Analysis Tool Pak.

One of the most useful data analysis add-in in excel is not available by default! It’s called “Analysis ToolPak”

To activate it. Go to File > Excel options > Addins > For the manage field, select Excel add-ins

Histogram Manage Excel add-insMake sure that ToolPak is activated and click OK.

Histogram analysis toolpak excel(Also, Solver is a great add-in as well! It’s not in the scope of this article to discuss that add-in but it’s a powerful add-in as well. For instance, it let’s you work on optimization problems)

Step 2: Format Data and build the Histogram

So now let’s format the data.

You need two things to create a Histogram. 1) Data 2) Range

Here’s an example: (I have about 3000 numbers and need to see the distribution)

You could have other fields on the sheet as well but you need at least the data field. Range is optional but I recommend that you specify the Range so that your histogram would have the bins that you specified — otherwise you could have just used a bar chart!

Note that both of them are numerical.

Data Histogram

Now go to Menu Bar > Data > Data Analysis

Data Analysis HistogramOut of the options available, click on Histogram and select the Input Range and Bin Range > after you’re done, click OK.

Data Analysis Histogram ToolpakYou should see a new worksheet with raw data (ready for charting!). Now, create a Bar chart using the raw data and you have your histogram:

Histogram Excel Data AnalysisConclusion:

In this post I listed the steps you can take to create a Histogram in Excel. Note that there are other options as well — like R (hist function) that let’s you build histogram as well so you do have choice of tools but if you want to stick with excel and it’s good enough then you now know how. Cheers!

Related Post: What is the difference between Histogram & Bar Chart?

What are the reasons why developing a data dictionary is so important?

Standard

data dictionary

Let me first define “Data Dictionary” — It’s a document that lists data fields/metrics and their standardized definition to be used across the org.

The key here is: Standardized.

Imagine this:

Imagine that a management team meeting is going on and you have CEO, VP of Sales, VP of Marketing, CFO, COO among others in the room.

Meeting Agenda: why they didn’t hit the $100M profit goal in the first quarter. So each of them start with the reports they had access to.

VP of Sales says they missed it by $5M

CFO says that they missed it by $9M

COO says that they missed it by $7M

VP of Marketing has three different versions on her report and she is confused!

No ONE talks about the “Why” they missed the goal but instead spends next hour reconciling the numbers!

It was a hypothetical scenario but these things happen all the time! Of course it could be any team meeting and the metric could be something else or it could just that someone is working on something on their own and end up spending a lot of time digging through all the metric definitions and trying to makes sense of it all. This is where data dictionary could help! Let’s take this a step further:

What’s one of the most important characteristic of a good data analysis/science?

It needs to be Actionable.

It needs to help business decision makers take action based on the insights that they found or were shared with them. And before they take that decision, business decision makers need the data they can TRUST!

For data to be trusted, it needs to be understood. It needs to have a definition that everyone agrees upon.

This is what data dictionary is for. It lists data fields/metrics and their standardized definition so that everyone in the org understands what the field/metric means and don’t have to worry about aligning their meaning. They could focus on Analyzing and extracting insights that would change the business and the world!

VIEW QUESTION ON QUORA

What is the title these days for a person that assures data quality?

Standard

Question:

What is the title these days for a person that assures data quality?
(I need to hire a person to make sure my data is as good as it can be. They need to inspect the data for issues, create logic for how it can be found and fixed, and finally, court the project through application development for a robust solution to stop it from occurring in the first place.)

Answer:

Quality of the data shouldnt be a responsibility of just one person — ideally, you want all members of the team (and broader business community) to care and own some part of it. But i like the idea of one person owning the “co-ordination” of how this gets done. It might not be a full time gig in a small org but can see this as a full time role in bigger orgs and enterprises. Some titles:

  1. data co-ordinator
  2. Data quality analyst (or just data analyst)
  3. Data steward
  4. Master data management analyst
  5. Data quality engineer (or just data engineer)
  6. Project manager (data quality)
  7. Manager, data quality and master data management

Read the original question on Quora