import sys
print("Python version:", sys.version)
Python version: 3.8.5 (default, Sep 4 2020, 02:22:02)
[Clang 10.0.0 ]
import sys
!{sys.executable} -m pip install urllib
ERROR: Could not find a version that satisfies the requirement urllib (from versions: none) ERROR: No matching distribution found for urllib
import os
import pandas as pd
import openai
def retrieve_password(row,column=1, print=False):
file=os.path.join(os.path.expanduser("~"), '.secret')
secret=pd.read_csv(file, sep="=", header=None).iloc[row,column]
if print=='True':
print('\n \n password obtained: ', secret)
return secret
#retrieve keys
def retrieve_environ_key(key_name, row):
'''
function that retrieves environmental keys. takes in two variables:
1)key_name, as stored in environ
2)row, of key name stored in password master record
If current session has keys stored, it retrieves names requested
if keys ARE NOT stored in current session it calls the 'retrieve_password' function with row provided
'''
if key_name not in os.environ:
print("Environment variable does not exist in current session, loading variables")
passwd=retrieve_password(row,1)
os.environ[key_name]=passwd
try:
open_api_key=os.environ.get(key_name, 'Does not exist')
except:
print('Unable to find key')
else:
print(key_name, 'found' )
def message_chat_gpt(message):
openai.organization = os.environ.get('OPENAI_API_ORG_KEY')
openai.api_key=os.environ.get('OPENAI_API_KEY')
completion = openai.ChatCompletion.create(
model="gpt-3.5-turbo",
messages=[
{"role": "user", "content": message}
]
)
response=completion.choices[0].message.content
return response
company='Autodesk'
retrieve_environ_key('OPENAI_API_KEY',3)
retrieve_environ_key('OPENAI_API_ORG_KEY',5)
message='what information about a company should I review before interviewing?'
print('user: ',message, '\n')
response= message_chat_gpt(message)
print('chatgpt',response)
user: what information about a company should I review before interviewing?
chatgpt As an AI language model, I don't have the context about the job role for which you are interviewing. However, here are some general pieces of information about a company that you should review before attending an interview:
1. Company history and background: Familiarize yourself with the company's history, values, mission, and culture.
2. Industry information: Research current industry trends and news related to the company's field.
3. Products and services: Learn about the company's products or services, and how they benefit customers.
4. Competitors: Look into the company's competitors, their strengths and weaknesses, and how the company differentiates itself.
5. Financial information: Study the company's financial statements, annual reports, and any recent investments or mergers.
6. Company structure: Understanding the company's structure, departments, and potential career paths is essential.
7. Company Reputation: Check if the company has received any awards, accolades, or has made any significant contributions to society.
8. Current open positions: Review the job description for the position you are applying to and take note of all requirements mentioned.
The information can vary depending on the job role and other factors, but reviewing these pieces of information will help you prepare yourself better for the interview.
message='can you provide key stats on '+company+'?'
print('user: ',message, '\n')
response= message_chat_gpt(message)
print('chatgpt',response)
user: can you provide key stats on Autodesk?
chatgpt As an AI language model, my training data only goes up to 2021. Here are some key stats on Autodesk as of 2021:
- Market capitalization: $63.14 billion
- Revenue: $3.3 billion (Q1 fiscal year 2022)
- Net income: $172.4 million (Q1 fiscal year 2022)
- Employees: 11,400
- Headquarters: San Rafael, California
- Top products: AutoCAD, Revit, Maya, Fusion 360, 3ds Max
- Customers: Over 200 million in more than 190 countries
- Industry: Computer software, design and manufacturing
- Listed on: NASDAQ (ADSK)
- CEO: Andrew Anagnost
message='What is the company history of '+company+ '?'
print('user: ',message, '\n')
response= message_chat_gpt(message)
print('chatgpt',response)
user: What is the company history of Autodesk?
chatgpt Autodesk was founded in 1982 by John Walker and 12 others to develop software for the emerging personal computer market. They started by creating AutoCAD, a computer-aided design software that quickly became popular among architects, engineers, and designers.
In the 1990s, Autodesk expanded its product line to include software for multimedia, animation, and games. It also expanded internationally, opening offices in Europe, Asia, and Latin America.
In the 2000s, Autodesk continued to acquire companies to expand its software offerings. It acquired companies such as Alias Systems, makers of 3D animation software, and Revit Technology, makers of building information modeling software.
In recent years, Autodesk has focused on developing software for the construction and manufacturing industries, including products such as BIM 360 and Fusion 360. The company has also been a leader in promoting sustainability and using technology to address global environmental challenges.
Today, Autodesk is a multinational software company with over 10,000 employees and offices in more than 30 countries. Its software is used by millions of users in industries ranging from architecture and engineering to media and entertainment.
message='Tell me everything useful about '+company+ ' that is relevant for interviewing for a job in data analytics position. Limit your answer to specific company information'
print('user: ',message, '\n')
response= message_chat_gpt(message)
print('chatgpt',response)
user: Tell me verything useful about Autodeskthat is relevant for interviewing for a job in data analytics position. Limit your answer to specific company information
chatgpt Autodesk is a multinational technology company that produces software for architecture, engineering, construction, manufacturing, media, and entertainment industries. The following are some key points about Autodesk that might be useful in a job interview for a data analytics position:
1. Data-Driven Company Culture: Autodesk maintains a strong data-driven culture, which not only drives the development of new products and services but also guides the company's overall decision-making process. The company relies heavily on data analytics to understand customer behavior, identify emerging trends, and optimize business processes.
2. Products for Data Analytics: Autodesk produces a range of software products that are specifically designed to support data analytics in engineering and construction environments. These products include tools for data visualization, simulation, and analysis, which can be useful for businesses that need to make complex data-driven decisions.
3. Revenue Growth: According to Forbes, Autodesk has experienced consistent revenue growth over the past few years, with a total revenue of $2.57 billion in FY 2020. This growth suggests that the company is performing well in a competitive tech market, which can be a positive factor for potential employees.
4. Cloud-Based Solutions: Autodesk has been investing heavily in cloud-based solutions, which can offer scalable and cost-effective data analytics solutions. The company's cloud-based products provide a platform for businesses to collect and analyze large amounts of data, which can be important for organizations that deal with complex engineering and construction projects.
5. Focus on Sustainability: Autodesk has a strong focus on sustainability and has set a goal to completely transition to 100% renewable energy by 2022. This sustainability focus could be viewed positively by employees who share those values and may be looking for a company with a strong sense of social responsibility.
message='Which are the key competitors of '+ company+'?'
print('user: ',message, '\n')
response= message_chat_gpt(message)
print('chatgpt',response)
user: Which are the key competitors of Autodesk?
chatgpt The key competitors of Autodesk include:
1. Trimble Inc.
2. Dassault Systèmes SE
3. Siemens AG
4. Adobe Systems Inc.
5. PTC Inc.
6. Bentley Systems Inc.
7. Ansys Inc.
8. Aveva Group plc.
9. Graebert GmbH
10. Hexagon AB
business_model='a software licenses and subscriptions model'
message='what is the key business model for '+company+'?'
print('user: ',message, '\n')
response= message_chat_gpt(message)
print('chatgpt',response)
user: what is the key business model for Autodesk?
chatgpt The key business model for Autodesk is the sale and subscription of computer-aided design (CAD) software for various industries such as architecture, engineering, construction, manufacturing, media, and entertainment. They also provide cloud-based services, support, and training to enhance the user experience and enable collaboration among teams. Autodesk's business model revolves around providing comprehensive solutions for design and automation that enable customers to streamline their workflows and improve productivity. They primarily generate revenue through their annual subscription model, which allows customers access to the latest software updates and cloud services. Additionally, Autodesk provides customized plans and enterprise solutions to cater to the specific needs of large businesses.
message='what are the most important KPIs for '+ business_model+'?'
print('user: ',message, '\n')
response= message_chat_gpt(message)
print('chatgpt',response)
user: what are the most important KPIs for a software licenses and subscriptions model?
chatgpt 1. Customer Acquisition Cost (CAC): Measures the cost of acquiring new customers vs. the revenue generated from those customers over a defined period.
2. Churn Rate: Measures the percentage of customers who cancel or do not renew their subscription over a given period.
3. Monthly Recurring Revenue (MRR): Measures the amount of revenue generated from recurring monthly subscriptions.
4. Annual Contract Value (ACV): Measures the total value of all annual contracts signed, or the average value of each contract.
5. Lifetime Value (LTV): Measures the total value of revenue generated from a customer over their lifespan as a customer.
6. Gross and Net Revenue: Measures the total revenue generated and revenue after accounting for discounts, returns, and refunds.
7. Customer Satisfaction: Measures the level of satisfaction of customers using the software or service.
8. Renewal Rate: Measures the percentage of customers who renew their subscriptions after a period has passed.
9. Average Revenue per User (ARPU): Measures the average amount of revenue generated for each user of the software or service.
10. Average Selling Price (ASP): Measures the average price of each license or subscription sold.
#clean list provided
list_kpi=[]
kpis=[]
formatted_response=(
response.split('.')
)
for line in formatted_response:
if line.find(':') != -1:
list_kpi.append(
line.split(':',1)[0]
.lstrip()
)
for ele in list_kpi:
if ele.find('(') != -1:
kpis.append(ele.split('(')[0].rstrip())
else:
kpis.append(ele.split('(')[0])
message='Can you provide me the mathematical formulas for this ' +str(len(kpis))+ ' metrics: '+str(kpis).lstrip('[').rstrip(']')+'?'
print('user: ',message, '\n')
response= message_chat_gpt(message)
print('chatgpt',response)
user: Can you provide me the mathematical formulas for this 10 metrics: 'Customer Acquisition Cost', 'Churn Rate', 'Monthly Recurring Revenue', 'Annual Contract Value', 'Lifetime Value', 'Gross and Net Revenue', 'Customer Satisfaction', 'Renewal Rate', 'Average Revenue per User', 'Average Selling Price'?
chatgpt 1. Customer Acquisition Cost (CAC) formula: CAC = Total cost of marketing and sales / Number of new customers acquired
2. Churn Rate formula: Churn Rate = Number of customers lost during a period / Total number of customers at the beginning of a period
3. Monthly Recurring Revenue (MRR) formula: MRR = Total value of all recurring subscriptions or monthly contracts
4. Annual Contract Value (ACV) formula: ACV = Average monthly recurring revenue x 12
5. Lifetime Value (LTV) formula: LTV = Average revenue per customer per year x Average customer lifespan
6. Gross Revenue formula: Gross Revenue = Total revenue earned from sales before deducting any expenses
7. Net Revenue formula: Net Revenue = Gross Revenue - Cost of Goods Sold - Operating Expenses - Taxes
8. Customer Satisfaction formula: Customer Satisfaction = Total number of satisfied customers / Total number of customers surveyed
9. Renewal Rate formula: Renewal Rate = Number of customers renewing subscriptions / Total number of customers at the end of a period
10. Average Revenue per User (ARPU) formula: ARPU = Total revenue generated / Total number of customers
11. Average Selling Price (ASP) formula: ASP = Total revenue generated from sales / Total number of units sold.
message='Can you provide me examples in SQL code for these ' +str(len(kpis))+ ' metrics: '+str(kpis).lstrip('[').rstrip(']')+'?'
print('user: ',message, '\n')
response= message_chat_gpt(message)
print('chatgpt',response)
user: Can you provide me examples in SQL code for these 10 metrics: 'Customer Acquisition Cost', 'Churn Rate', 'Monthly Recurring Revenue', 'Annual Contract Value', 'Lifetime Value', 'Gross and Net Revenue', 'Customer Satisfaction', 'Renewal Rate', 'Average Revenue per User', 'Average Selling Price'?
chatgpt Sure! Here are some SQL code examples for each of the listed metrics:
1. Customer Acquisition Cost:
```sql
SELECT SUM(cost) / COUNT(DISTINCT customer_id) AS customer_acquisition_cost
FROM marketing_campaigns
WHERE year = '2021' AND channel = 'paid advertising';
```
2. Churn Rate:
```sql
SELECT COUNT(DISTINCT customer_id) / COUNT(DISTINCT initial_customers) AS churn_rate
FROM customers
WHERE registration_date BETWEEN '2020-01-01' AND '2020-12-31';
```
3. Monthly Recurring Revenue:
```sql
SELECT SUM(monthly_revenue) AS monthly_recurring_revenue
FROM subscriptions
WHERE start_date < '2021-01-01' AND end_date >= '2021-01-01';
```
4. Annual Contract Value:
```sql
SELECT SUM(amount) * 12 AS annual_contract_value
FROM contracts
WHERE signed_date BETWEEN '2020-01-01' AND '2020-12-31';
```
5. Lifetime Value:
```sql
SELECT AVG(value) AS lifetime_value
FROM (
SELECT SUM(amount) AS value
FROM transactions
WHERE customer_id = '12345'
GROUP BY customer_id, month
) subquery;
```
6. Gross and Net Revenue:
```sql
SELECT SUM(amount) AS gross_revenue, SUM(amount) - SUM(costs) AS net_revenue
FROM orders
WHERE order_date BETWEEN '2021-01-01' AND '2021-01-31';
```
7. Customer Satisfaction:
```sql
SELECT AVG(rating) AS customer_satisfaction
FROM surveys
WHERE survey_date BETWEEN '2021-01-01' AND '2021-01-31';
```
8. Renewal Rate:
```sql
SELECT COUNT(DISTINCT customer_id) / COUNT(DISTINCT renewing_customers) AS renewal_rate
FROM customers
WHERE renewal_date BETWEEN '2020-01-01' AND '2020-12-31';
```
9. Average Revenue per User:
```sql
SELECT SUM(amount) / COUNT(DISTINCT customer_id) AS average_revenue_per_user
FROM transactions
WHERE transaction_date BETWEEN '2021-01-01' AND '2021-01-31';
```
10. Average Selling Price:
```sql
SELECT AVG(price) AS average_selling_price
FROM sales
WHERE sale_date BETWEEN '2021-01-01' AND '2021-01-31';
```
SOURCES:
- Credit of code goes to [The PyCoach](https://medium.com/geekculture/a-simple-guide-to-chatgpt-api-with-python-c147985ae28)