analytics

Google Analytics dataset demystified | BigQuery ga_sessions

The first you need to know, I am referring to go google analytics 360. one click and the data is downloaded automatically to BigQuery. Notice this ga_session table is sharded, be sure to understand the differences between a sharded table vs partitioned table.

Documentation and Sample data can be seen here, below is basic examples:

SELECT *

FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170101`

gs session query date range variable suffix

SELECT
  *
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _table_suffix BETWEEN '20160801'
  AND '20170801'

ga session date range which is dynamic (last 2 weeks):

SELECT
  *
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _table_suffix BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 15 DAY))
  AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

The schema of the ga_session table is rather complex as it contains many complex data structures for the hits. the hits records contain all the custom dimensions user define in google analytics. A visual representation of ga_session table is in this link.

Analyzing the ga_session and extraction records and custom dimensions should like along the line:

SELECT
trafficSource.campaignCode AS Campaign_Code,
(SELECT distinct value FROM h.customDimensions where index=4) as cd_4

FROM
  `MyProject.MyDataSEt.ga_sessions_*` t, t.hits h

Notice there may be some CD’s on the user level information (notice CD2 and CD6):

SELECT
visitId AS  visitId,
hits.hitNumber  AS  hits_hitNumber,
hits.time   AS  hits_time,
hits.page.pagePath  AS  hits_page_pagePath,
-- hit scope custom dimensions
(SELECT value from hits.customDimensions where index=2) AS CD2,
-- user and session level custom dimensions
(SELECT value from sessions.customDimensions where index=6) AS CD6
FROM `MyProject.1111.ga_sessions_*` AS sessions, UNNEST(hits) as hits
 
 

I highly recommend the following article, as it is an excellent standard sql BigQuery cookbook by Johan van de Werken with steps by step explanations and examples.

——————————————————————————————————————————

I put a lot of thoughts into these blogs, so I could share the information in a clear and useful way. If you have any comments, thoughts, questions, or you need someone to consult with, feel free to contact me:

https://www.linkedin.com/in/omid-vahdaty/

analytics, meetup

MAKING YOUR ANALYTICS TALK BUSINESS | BI and Analytics Demystified | Eliza Savov, Team Lead, Customer Experience and Analytics at Clicktale

What You Will Learn:

Collecting data is easy; getting real value from that data is more difficult. The first step in creating value-adding data is having a structured business plan- for both analytics and BI – that will guide all future data operations.

This event aims to practically break down the steps in formulating a data analytics and BI strategy. The talks will broaden your own understanding of how to use a strategy to integrate data into your overall business goals, and will provide you with actionable steps to both think about and create your own strategy.

MAKING YOUR ANALYTICS TALK BUSINESS

Aligning your analysis to the business is fundamental for all types of analytics (digital or product analytics, business intelligence, etc) and is vertical- and tool agnostic. In this talk we will build on the discussion that was started in the previous meetup, and will discuss how analysts can learn to derive their stakeholders’ expectations, how to shift from metrics to “real” KPIs, and how to approach an analysis in order to create real impact.

This session is primarily geared towards those starting out into analytics, practitioners who feel that they are still struggling to prove their value in the organization or simply folks who want to power up their reporting and recommendation skills. If you are already a master at aligning your analysis to the business, you’re most welcome as well: join us to share your experiences so that we can all learn from each other and improve!

Bios:

Eliza Savov – Eliza is the team lead of the Customer Experience and Analytics team at Clicktale, the worldwide leader in behavioral analytics. She has extensive experience working with data analytics, having previously worked at Clicktale as a senior customer experience analyst, and as a product analyst at Seeking Alpha.

Structuring a Strategy: Creating a BI & Analytics Business Plan

Sunday, Jul 21, 2019, 6:00 PM

Google for Startups Campus
Ha-Umanim St 12 Tel Aviv-Yafo, IL

154 Members Went

Learn how to make a structured business plan for your analytics and BI operations that will add value to your bottom line. Agenda: 18:00 PM – Networking 18:30 PM – MAKING YOUR ANALYTICS TALK BUSINESS, by Eliza Savov, Team Lead, Customer Experience and Analytics at Clicktale 19:15 – Break 19:30 PM – BI STRATEGY FROM A BIRD’S EYE VIEW, by Omri Halak,…

Check out this Meetup →

——————————————————————————————————————————

I put a lot of thoughts into these blogs, so I could share the information in a clear and useful way. If you have any comments, thoughts, questions, or you need someone to consult with, feel free to contact me:

https://www.linkedin.com/in/omid-vahdaty/

analytics, meetup, Uncategorised

BI STRATEGY FROM A BIRD’S EYE VIEW | BI and Analytics Demystified |Omri Halak, Director of Business Operations at Logz.io

In the talk we will discuss how to break down the company’s overall goals all the way to your BI team’s daily activities in 3 simple stages:

1. Understanding the path to success – Creating a revenue model
2. Gathering support and strategizing – Structuring a team
3. Executing – Tracking KPIs

Bios:

Omri Halak -Omri is the director of business operations at Logz.io, an intelligent and scalable machine data analytics platform built on ELK & Grafana that empowers engineers to monitor, troubleshoot, and secure mission-critical applications more effectively. In this position, Omri combines actionable business insights from the BI side with fast and effective delivery on the Operations side. Omri has ample experience connecting data with business, with previous positions at SimilarWeb as a business analyst, at Woobi as finance director, and as Head of State Guarantees at Israel Ministry of Finance.

Structuring a Strategy: Creating a BI & Analytics Business Plan

Sunday, Jul 21, 2019, 6:00 PM

Google for Startups Campus
Ha-Umanim St 12 Tel Aviv-Yafo, IL

154 Members Went

Learn how to make a structured business plan for your analytics and BI operations that will add value to your bottom line. Agenda: 18:00 PM – Networking 18:30 PM – MAKING YOUR ANALYTICS TALK BUSINESS, by Eliza Savov, Team Lead, Customer Experience and Analytics at Clicktale 19:15 – Break 19:30 PM – BI STRATEGY FROM A BIRD’S EYE VIEW, by Omri Halak,…

Check out this Meetup →

——————————————————————————————————————————

I put a lot of thoughts into these blogs, so I could share the information in a clear and useful way. If you have any comments, thoughts, questions, or you need someone to consult with, feel free to contact me:

https://www.linkedin.com/in/omid-vahdaty/

analytics, meetup

ALIGNING YOUR BI OPERATIONS WITH YOUR CUSTOMERS’ UNSPOKEN NEEDS, by Eyal Steiner, Senior BI Engineer, Alexa Shopping at Amazon

What You Will Learn:

Data is a strategic business function, but due to its heavy technical components, the strategic element is often overlooked by the very people who work with it. However, to achieve success in a data career, it is crucial to zero in on the business value of the data provided and to align all data projects and output with the organization’s business goals.

This event aims to provide you, as a professional working in data, with a broader understanding of how to relate the day-to-day technical aspects of data operations to the business goals, and how you can leverage this business understanding (along with your technical skillset) to drive your career forward.

ALIGNING YOUR BI OPERATIONS WITH YOUR CUSTOMERS’ UNSPOKEN NEEDS

-Learn how to connect BI and product management to solve business problems
-Discover how to lead clients to ask the right questions to get the data and insight they really want
-Get pointers on saving your time and your company’s resources by understanding what your customers need, not what they ask for

Bio:

Eyal Steiner – Eyal is a senior BI engineer at Alexa Shopping at Amazon. Alexa Shopping integrates voice recognition with Amazon e-commerce, allowing users to make purchases with the sound of their voice. With this venture, Amazon is hoping to dominate the emerging field of voice-activated shopping.

Eyal has a broad perspective in both data and business, having worked previously at SimilarWeb as a data scientist and as the CEO and co-founder of DNA-7, a SaaS that uses the power of organizational network analysis for workforce optimization.

BI And Analytics: The Business Connection

Sunday, Jun 2, 2019, 6:00 PM

Google Campus
Ha-Umanim St 12 Tel Aviv-Yafo, IL

114 Members Went

Agenda: 18:00 PM – Networking 18:30 PM – BUSINESS ANALYTICS AT SELINA, by Assaf Levinson, Head of Analytics at Selina 19:15 – Break 19:30 PM – ALIGNING YOUR BI OPERATIONS WITH YOUR CUSTOMERS’ UNSPOKEN NEEDS, by Eyal Steiner, Senior BI Engineer, Alexa Shopping at Amazon 20:15 pm – Wrap-up *Lectures will be in English *(Kosher) snacks will be served …

Check out this Meetup →

——————————————————————————————————————————

I put a lot of thoughts into these blogs, so I could share the information in a clear and useful way. If you have any comments, thoughts, questions, or you need someone to consult with, feel free to contact me:

https://www.linkedin.com/in/omid-vahdaty/

analytics, meetup

BUSINESS ANALYTICS AT SELINA, by Assaf Levinson, Head of Analytics at Selina

What You Will Learn:

Data is a strategic business function, but due to its heavy technical components, the strategic element is often overlooked by the very people who work with it. However, to achieve success in a data career, it is crucial to zero in on the business value of the data provided and to align all data projects and output with the organization’s business goals.

This event aims to provide you, as a professional working in data, with a broader understanding of how to relate the day-to-day technical aspects of data operations to the business goals, and how you can leverage this business understanding (along with your technical skillset) to drive your career forward.

ANALYTICS AT SELINA

-Hear how an emerging global hospitality disruptor uses a structured data analytics strategy to collect valuable data that fuels their worldwide expansion and high valuation.
-Learn how to talk about dev and BI to business people
-Get tips to strengthen the connection between all elements of data collection (dev, BI, and analytics) to increase the value and efficiency of the data collected

Bios:

Assaf Levinson – Assaf is the head of business analytics at Selina, the rising ‘AirBNB’ for digital nomads across the world. With operations in 13 countries, an $850M valuation and a recent funding round in April of $100M, Selina is poised to disrupt the global hospitality industry.

Assaf comes with years of business and data experience, having previously served as a product analytics director at Taboola, director of data science at ConvertMedia, and as a business analyst at Rafael.

BI And Analytics: The Business Connection

Sunday, Jun 2, 2019, 6:00 PM

Google Campus
Ha-Umanim St 12 Tel Aviv-Yafo, IL

114 Members Went

Agenda: 18:00 PM – Networking 18:30 PM – BUSINESS ANALYTICS AT SELINA, by Assaf Levinson, Head of Analytics at Selina 19:15 – Break 19:30 PM – ALIGNING YOUR BI OPERATIONS WITH YOUR CUSTOMERS’ UNSPOKEN NEEDS, by Eyal Steiner, Senior BI Engineer, Alexa Shopping at Amazon 20:15 pm – Wrap-up *Lectures will be in English *(Kosher) snacks will be served …

Check out this Meetup →

——————————————————————————————————————————

I put a lot of thoughts into these blogs, so I could share the information in a clear and useful way. If you have any comments, thoughts, questions, or you need someone to consult with, feel free to contact me:

https://www.linkedin.com/in/omid-vahdaty/